Oracle Database Introduction & Basics of Tables

Imtiaz Ahmad
A free video tutorial from Imtiaz Ahmad
Senior Software Engineer & Trainer @ Job Ready Programmer
4.6 instructor rating • 12 courses • 285,272 students

Learn more from the full course

The Complete Oracle SQL Certification Course

Don't Just Learn the SQL Language, Become Job-Ready and Launch Your Career as a Certified Oracle SQL Developer!

16:23:53 of on-demand video • Updated June 2021

  • Become Job Ready to Start Contributing as a Database Developer Day 1
  • Program in the SQL Language to Solve a Variety of Database Problems
  • Code along with me to PRACTICE and IMPLEMENT everything you learn
  • Become a SQL Ninja and Understand How the Oracle Database Works
  • MASTER the Content Required to Pass the Oracle 1Z0-071 Database SQL Exam
  • Obtain the Skills that are Necessary to Land a Job as a SQL Developer
  • Have the Ability to Solve any SQL Problem
English [Auto] Hi there in this lesson, I'm going to go over what is a database and more specifically, how is an Oracle database set up? OK, we're going to go over the details of the Oracle ecosystem. And in general, this applies to all databases. And we're going to go over those details in this lesson. A database is a place where you store data might be saying, oh, thank you, Mr. Obvious. That's really what it is. It's a place where you store data. Right. And once, for example, if we have a database running on our machine and we put data into it and then we shut our machine down and we open it up maybe a year or two years from now, that data, as long as our machine is still living, that data will still be on our hard drive. Right. And that's the idea behind a database. You put data into a database to save it and information stored in a database is saved in something called tables. And I'm not talking about I'm not talking about these kind of tables that, you know, we were having a picnic, right. That's not there's not the kind of table I'm talking about. All right. In a database, there is a special kind of data structure with columns and rows. And that's the kind of table that I'm referring to. If you've ever used an Excel spreadsheet, that's the structure of how the data is stored. So let me draw that out here for you and then we'll go over some details. All right, so I took a pause to draw this table out and sorry for my handwriting, I'm not the best drawer, but this is the structure of a database table. Right. And the columns, these are the columns. One, two, three, four. These are the columns going down. Right. That's what's referred to as a column. And the rows, these are the rows right here. OK, going horizontally so this table can have one, two, three, four, five, six, seven, whatever, however many rows there are, I guess this would be the sixth row and then seven onwards. And so so far you see six rows in this table. Right. Not all of them have data. For example, this fifth row with the name Jim, he doesn't have the rest of the information filled out. I was too lazy to fill this out, but this is basically the structure of the table in the way you want to interpret this is each of these is an attribute for a particular record. I write this first record, the let's say this is let's say this is a table for purp persons. Right. This is a person's table. So it stores information about people. And the first record, that person, his name is Tom. He was born, you know, date of birth. Dobbie, he was born on September 17th. Nineteen eighty one. His address, uh, we don't have much space, so I didn't even write that here. But he has some or some phone number. I didn't put any parentheses or dashes or anything is just a number and it's a completely fake number. And same goes with that. He was born on this date and his address is not listed. And his phone number is this Sam. He was born on August 1st, 2001. His address is not listed and this is his phone number and so on. Right. You get the picture. So the records, these are going horizontally. These are referred to as records. Okay, I'm gonna write that down as well as Rose Records. Rose, same idea. That's what these guys are going horizontally. The columns. These are columns. On top. OK, from from the first one, all the way down to the fourth. What's important to keep in mind is that each one of these columns contain a particular kind of data. The name column should only contain names. It should not contain any other information. All right. The date of birth column. This should only contain the dates of birth date of births for the particular persons. All right. That's all it should contain. It shouldn't contain any numbers or addresses or phone numbers. This column's purpose, its reason for existence is to store data of births for the particular records. All right. That's all this column is supposed to do. That's what it's that's the information that should be stored in this column. The address, if the employee doesn't if the person or whatever this record represents, if it doesn't have an address, leave it empty. Otherwise, this only should contain addresses. All right. No other information. Phone number. Same thing. Right. So the purpose of a column is to store that columns, data, whatever is meant to be stored in there. Right? Very important. So keep that keep that in mind. It's a very basic concept. But a lot of people, when they're new to this, I want to make sure I cover all grounds. So the first column name this contains textual information, write characters, English words. That's what this column contains. And then the date of birth column in the data type. The type of data that goes into this column is dates data. Right. So you won't see dollar signs or or numbers or anything like that. You see literally dates. That's the only kind of information that can be stored in the Dobb column. The address. This is also textual character, English words and sentences. That's the kind of information that should be stored in the address column. The phone number column. I don't have any parentheses or dashes or anything like that. These are just pure, you know, whole numbers. That's what this column is supposed to store is numbers. All right. So keep that in mind. Every column has a particular data type and only that type of data can go in that column. All right. So this is a column that should store numbers. Let me just going to write that here. So if we try to insert if we try to put some data in here, that is, for example, the word hello. But if I try to put that into this column, it's not going to allow me to do that, right? This is not acceptable. This column is restricted to numbers. That's all in the name we could probably get away with putting the word hello or some other word, but because that's the data type of the name column. But we can't store numbers. That's, you know, unless we put them in quotes and sort of, you know, put like one, two, three, and then just put some words. That's the kind of data that could go in names if we choose to. But again, the purpose of names is to store the names of people. Right. And the date of birth column. This is only going to accept dates. So if we try to put words in here, it's not going to let us do that. If we try to put numbers in random decimal numbers or whole numbers, it's not going to let us do that. The date of birth column is only going to accept dates, write of a particular date format. So the data type of the Dobbie column is date data type address has the same data type as name character information. And then again, phone numbers, phone column contains a number. So these are these are data types. So every column has a particular data type and only that type of data should be able to it can go in there. And the rows rows is basically the actual data with the respective attributes. OK, so Tom, is the name date of birth is this is address is this is phone number, this and so on. That makes up one row. Second row has a totally different set of data. Third row has a totally different set of data. But this is the structure. Each row is cooperating with the respective types that can be placed in each one of these columns. So I'm just going to clear the screen here and go over at a high level detail what a table is, so a database, a database. I'm just going to draw that out here. And typically, um, you know, this is how a database looks. It's the collection of these disks. That's what that's just sort of the way it's presented in pictures. And if you ever seen the Oracle headquarters, it looks like this. Right. And it's made out of these disks, which I think is a cool, really cool building structure. Basically, this is a database and it's a collection. It's a collection of these things called tables. All right. And that's what goes in a database and each one of these tables has columns and rows, right? So the structure of each one of these is going to be, you know, records like this. All right. And it's going to have columns like that. That's a table, so a database can have hundreds and hundreds or even thousands of tables, and each one of those tables must have some purpose, right. Let's see if we have a human resources department. Right. And we have a database for that. Well, we're going to have tables for, you know, employees. We could have a table for salaries. We could have a tab for departments. We could have a table that represents, you know, our policies and procedures, we could have a table for, you know, vacation planning and so on, so all types of tables can go in a particular database. And let's say if this was a human resources and H.R. database, it would contain, you know, table similar to this. And each one of these tables is going to look like this structure that we just went over. And remember, at a high level, it's columns on the top. These are referred to as columns going down vertically. All right. And the rows. These are the rows. OK, going horizontally. Rows of horizontal columns are vertical, just keep that in mind, it's very important and it's a basic, fundamental concept. But when you're first learning this stuff, you need to understand it intuitively to to make the most use out of school. So what is school really? I mean, it's a language used to interact with a database. But what really what happens behind the scenes? Well, basically, a school is a set of commands and these commands. I'm just going to write some I'm just going to write out it's it's pronounced sequel, but it actually is an acronym, Ezekial, which stands for Structured Query Language. And basically this is a set of commands that can be sent to the database. So let's say let's say this is our database. Right. I'm just going to draw it up again. This is our database and we type some SQL commands. And I'm just going to sort of put these lines here. Let's say this is a set of commands. When we send this to the database. Right. The database is going to interpret what these commands are and if they're formatted correctly, if the syntax is correct, it's going to do something right. So these commands are literally instructions to the database on what to do. Right. And we basically declare the instructions in a and the school language and the database takes that those instructions and performs some task. Right. The task could be to, for example, return some data back. Right. We may want to see the actual table in some formatted way or whatever. And the the database is going to pass our school query and return us some nice looking data. Great, as you'll see soon. Or it could just perform some tasks such as, you know, it could also, you know, we can we may want to delete tables in our database. It could perform that as well. So the commands that we give here in school, the database is going to interpret those commands and execute them and perform tasks. We could create new we create new users if we want new users to get access to the database, we can send commands and it may create user passwords and access rights to the database. It could delete users right. So that the person is no longer authorized to access this database. It could create all types of different objects. OK, database is a collection of objects and the most important kind of object is this thing right here. Tables, right tables are the most important kind of object and tables are where all of the information, all of the data is stored in a database. So using SQL, you can create new tables, you can delete old tables, you can search the database, search the tables for some information that you might be looking for. And in this course, we're going to be learning how to perform all of these tasks using the skill language. All right. This is able to do all of the things that I mentioned, as well as much, much more. And you're going to get a good handle of how to query the database, how to create tables, how to delete tables, how to modify the columns. But a big portion of this course, a big portion of this course, as well as the Oracle SQL exams, is queries and queries are basically it's a question that we asked the database, hey, could you give me all of those employees that work in departments such and such that have a salary, such and such, and were hired on on this date and that and those are referred to as queries when we write the sequel statements in a in a way that we're asking the the database to give us some data, some information back. Those are queries and majority of this course a huge chunk, I would say almost 60 percent. Right. 60 percent of the Oracle SQL exams is about writing queries and getting that right. OK, and this could be pretty tricky. You might want to slice and dice your data and so many different ways to ask so many different kinds of questions and do analytics on the data that's stored in the database and queries is where it's at. All right. If you can get good at writing queries, you can pretty much do anything in the Oracle database, right. Or any database for that matter. SQL is a database, independent language actually. And if you use the standard and see they refer to as ANSI SQL standard in in the Oracle database or any other database, it's going to work. Right. So SQL, the knowledge that you can hear about this language, you can use that knowledge to possibly pass other database exams such as Microsoft Postgrads and MySQL. That sequel is SQL and it works on a majority of the databases out there. That might be just slight differences from vendor to vendor and the syntax, but otherwise the ANSI standard, the anti standard of SQL works in all databases. And that's what we're going to be focusing on for the most part in this course. So you might be wondering what exactly is this disc looking thing, right, and it's actually a way to represent a database in a picture format, but really it's a set of hard drives or hard drive. Looks like this as well. OK, so destroying John didn't come out that great as the others, so let me actually make it more cylindrical. So this is a hard drive, OK? And this belongs in something called. A computer, right, let's say there's a desktop monitor here. This this hard drive belongs in this computer. All right. And that's the essence of a database. You can have servers and this you can also think of this desktop as as a server and it looks like these big refrigerators. All right. I'm going to draw that out right here. If you've ever owned one of these old desktop towers, you know, with the with the CD-ROMs and and the start button down here and a bunch of ports in the back. That's essentially what a server is as well. Right. And Oracle is a software, right. Oracle is a software that's installed on this machine on any server. Right. It could be one server or it could be a collection of servers. But basically, this is a software that's installed on this machine and it uses the hardware of this machine. So this this server could have many different disks. OK, I'm just going to draw these mini disks here. It could be several disks. And Oracle is a software that's installed on this server and the data is stored in these persistent disks right here. OK, same thing right up here. It's a disk. So typically when someone says an Oracle database, they don't just mean the Oracle brand or or this software, they typically mean a combination of these things. All right. The Oracle infrastructure, it's a combination of the software as well as the hardware. And Oracle has these packages where they you know, they can install an entire infrastructure in a company where they give the servers as well as the preinstalled software on those massive refrigerators. And they have a tremendous amount of power. And you can use that as the Oracle database system. But how this is relevant to our course is that we're actually going to be using instead of instead of installing the Oracle database software on our computer, we're going to be using the Oracle cloud installation. OK. And I'm just going to draw a little cloud here. I'm sorry for my drawing. Again, it's not that great, but basically Oracle provides a cloud instance of the database and we can access this using this application called Apex. And we're going to go over how to do that in the next lesson. Apex is actually a website where you can get access to the database and you can type SQL commands right in your browser and it will make changes in the database. OK, so what is meant by the cloud Oracle installed on the cloud? Well, basically, this is a database installed somewhere in some remote location, in some server, somewhere in the world. All right. And it could be in multiple parts, multiple regions of this world. There could be databases in Africa and Asia, in, you know, North America all over the entire planet. There could be these servers and they can have this Oracle database installed. And the whole idea behind Cloud is that you don't know or care much about where exactly is installed. You're just going to be using the one, the instance, the database that's closest to where you are. Right. So right now, I'm actually in New Jersey. So when I log into this website, which you'll see soon, this is going to connect me to some database that somewhere probably close to me and I'll be able to execute these commands on this remote database wherever it's installed on some server and some machine in some place. All right. So Apex is actually a website. It's actually a tool that you can use to develop Oracle applications, and we're going to be using the apex, Ed.. All right. I'm going to write that here, Apex SQL. Command, Ed. And it's basically a website where you write in your browser, you can type school and those commands when you hit the wrong button, they're going to be executed on that wherever that database's installed. So you can use APACS on Windows as well as Mac or any other operating system because you actually access it using your browser, Firefox, Mozilla, Internet Explorer. It all works with APACS. So that's the great thing about it. For the purposes of this course, we're going to be mastering SQL and we can practice all those commands in the Apex Tool, which is the website that I'll show you in the next lesson. All right. So hopefully you got a good overview as to the whole Oracle database ecosystem. We're going to get practical starting in the next lesson. So stay tuned. I'll see you soon.