Oracle Database Introduction & Basics of Tables

A free video tutorial from Job Ready Programmer
Senior Software Engineers and Trainers
Rating: 4.6 out of 5Instructor rating
13 courses
479,614 students
Oracle Database Introduction & Basics of Tables

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:27:26 of on-demand video • Updated December 2023

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 [CC]
Imtiaz: Hi there. In this lesson I'm gonna go over what is a database. And, more specifically, how is an Oracle database set up? Okay, we're gonna go over the details of the Oracle ecosystem, and, in general, this applies to all databases, and we're gonna go over those details in this lesson. A database is a place where you store data, right? You might be saying, "Oh, thank you, Mr. Obvious." Well, 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 in 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 these kind of tables that, you know, we have in a picnic, right? That is not the kind of table I'm talking about, right? In a database, there's 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 a 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. Alright, 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, alright? 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, okay, 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, right? 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. And the way you wanna interpret this is each of these is an attribute for a particular record, right? This first record, let's say this is a table for 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, D-O-B, he was born on September 17th, 1981. His address, we don't have much space, so I didn't even write that here, but he has some phone number. I didn't put any parentheses or dashes or anything, it's just a number, and it's a completely fake number. And same goes with Matt. 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, you know, going horizontally, these are referred to as records, okay? I'm gonna write that down. As well as rows, records, rows, same idea. That's what these guys are going horizontally. The columns, these are columns on top, okay? 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, alright? The date of birth column, this should only contain the date of births for the particular persons, right? That's all it should contain. It shouldn't contain any numbers or addresses or phone numbers. This column's purpose, it's reason for existence, is to store date of births for the particular records, alright? That's all this column is supposed to do. That's the information that should be stored in this column. The address, if the person or whatever this record represents, if it doesn't have an address, leave it empty. Otherwise, this only should contain addresses, alright? No other information. Phone number, same thing, right? So the purpose of a column is to store that column's data, whatever is meant to be stored in there, right? Very important, so, keep that in mind. It's a very basic concept, but a lot of people, when they're new to this, I wanna make sure I cover all grounds. So the first column, name, this contains textual information right? Characters, English words, that's what this column contains. And then the date of birth column, the type of data that goes into this column is dates data, right? So you won't see dollar signs or numbers or anything like that. You see literally dates, right? That's the only kind of information that can be stored in the DOB column. The address, this is also textual, you know, 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, alright? So keep that in mind. Every column has a particular data type, and only that type of data can go in that column, alright? So this is a column that should store numbers. Lemme just, gonna write that here. So if we try to put some data in here that is, for example, the word hello, right? If I try to input that into this column, it's not gonna 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 gonna let us do that. If we try to put numbers, some random decimal numbers or whole numbers, it's not gonna let us do that. The date of birth column is only going to accept dates, alright, Of a particular date format. So the data type of the DOB column is date data type. Address has the same data type as name, you know, character information. And then, again, phone numbers, phone column contains numbers. So these are data types. So every column has a particular data type, and only that type of data can go in there. And the rows, rows is basically the actual data with the respective attributes, okay? So Tom is the name, date of birth is this, his address is this, his phone number is 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 gonna clear the screen here and go over at a high-level detail what a table is. So a database, right, a database, I'm just gonna draw that out here. And typically, you know, this is how a database looks, it's the collection of these disks, right? That's just sort of the way it's presented in pictures. And if you've 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, alright? 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 gonna be, you know, records like this, right? And it's gonna have columns like that, right? 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 say if we have a human resources department, right, and we have a database for that, well, we're gonna have tables for, you know, employees. We could have a table for salaries, we could have a table for departments, we could have a table that represents, you know, HR 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, an HR database, it would contain, you know, tables similar to this. And each one of these tables gonna 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, alright? And the rows, these are the rows, okay, going horizontally. Rows are horizontal, columns are vertical. Just keep that in mind, it's very important. I know it's a basic fundamental concept, but when you're first learning this stuff you need to understand it intuitively to make the most use out of SQL. So, what is SQL really? I mean, it's a language used to interact with a database, but what really happens behind the scenes? Well, basically a SQL is a set of commands. And these commands, I'm just gonna write out, it's pronounced SQL, but it actually is an acronym, S-Q-L, 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 this is our database, right? I'm just gonna draw it up again, this is our database, and we type some, you know, SQL commands. And I'm just gonna 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 gonna do something, right? So these commands are literally instructions to the database on what to do, right? And we basically declare the instructions in the SQL language, and the database takes those instructions and performs some task, right? The task could be to, for example, return some data back, right? We may wanna see the actual table in some formatted way or whatever. And the database is gonna parse our SQL query and return us some nice looking data grid, as you'll see soon. Or it could just perform some tasks such as, you know, it could also, you know, we may wanna delete tables in our database, it could perform that as well. So the commands that we give here in SQL, the database is going to interpret those commands and execute them and perform tasks. We could create new users, right? 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, okay? A 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 gonna be learning how to perform all of these tasks using the SQL language, alright? This is able to do all of the things that I mentioned, as well as much, much more. And you're gonna 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 ask 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 this date?" And those are referred to as queries when we write the SQL statements in a way that we're asking the the database to give us some data and some information back. Those are queries, and majority of this course, a huge chunk, I would say almost 60%, right? 60% of the Oracle SQL exams is about writing queries and getting that right? Okay, and this could be pretty tricky. You might want to slice and dice your data in 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, alright? 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 ANSI, they refer to as ANSI SQL standard in the Oracle database, or any other database, it's gonna work, alright? So SQL, the knowledge that you gain here about this language, you can use that knowledge to possibly, you know, pass other database exams such as Microsoft, Postgres, and MySQL. The SQL is SQL and it works on a majority of the databases out there. There might be just slight differences from vendor-to-vendor in the syntax, but otherwise the ANSI standard, the ANSI standard of SQL works in all databases. And that's what we're gonna be focusing on for the most part in this course. So you might be wondering what exactly is this disk 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, okay? A hard drive looks like this as well, okay? Sorry, this drawing didn't come out that great as the others. So let me actually make it more cylindrical. So this is a hard drive, okay? And this belongs in something called a computer, right? Let's say there's a desktop monitor here. This hard drive belongs in this computer, right? And that's the essence of a database. You can have servers and this, you can also think of this desktop as a server, and it looks like these big refrigerators, alright. I'm gonna draw that out right here. If you've ever owned one of these old desktop towers, you know, with the CD ROMs and the start button down here and then 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 server could have many different disks, okay? I'm just gonna draw these many 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, okay? Same thing up here, it's a disk. So typically when someone says an Oracle database, they don't just mean the Oracle brand or this software, they typically mean a combination of these things, 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 pre-installed 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 gonna be using, instead of installing the Oracle database software on our computer, we're gonna be using the Oracle Cloud installation, okay? And I'm just gonna draw a little cloud here, 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 gonna go over how to do that in the next lesson. Apex is a 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, okay? 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, alright? And it could be in multiple parts, multiple regions of this world. There could be databases in Africa, in 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 it's installed. You're just gonna 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 gonna connect me to some database that's somewhere probably close to me. And, you know, I'll be able to execute these commands on this remote database wherever it's installed on some server, in some machine, in some place, alright? So Apex is actually a website, it's actually a tool that you can use to develop Oracle applications, and we're gonna be using the Apex editor, right? I'm gonna write that here. Apex SQL Command Editor. And it's basically a website where, you know, right in your browser you can type SQL and those commands, when you hit the run button, they're gonna be executed on that, wherever that database is installed. So you can use Apex 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 Apex. So that's the great thing about it. For the purposes of this course, we're gonna 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, alright? So hopefully you got a good overview as to the whole Oracle database ecosystem. We're gonna get practical starting in the next lesson, so stay tuned. I'll see you soon.