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 • 245,019 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:22:14 of on-demand video • Updated November 2020

  • 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 when I go over what is a database and more specifically how is an Oracle database set up. OK. We gonna 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. Right might be saying oh thank you Mr. Obvious. That's really what it is. It's a place where you store data. All 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. All 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 we have a picnic right. That's not that'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 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. 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. All 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 or so. So far you see six rows in this table. All 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 want to interpret this is each of these is an attribute for a particular record. Right. This first record the let's say this is the let's say this is a table for pert persons. All right there's a persons 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. He was born on September 17th nineteen eighty one his address. We don't have much space 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 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. And 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 the date of births for the particular persons. Right. That's all that your contention containing 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. Right characters English words. That's what this column contains. And then the date of birth column it 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 right. That's the only kind of information that can be stored in the Dobie 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 that is 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. I'm 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 right 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 you know unless we put them in quotes and sort of you know put like 1 2 3 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 and 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. All right. Of a particular date formats or the data type of the deal we call them is date data type. Address has the same data type as name you know character information. And then again phone numbers a 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 Rose Rose is basically the actual data with the respective attributes. OK 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 a 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 right. A database. I'm just going to draw that out here and typically 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. All 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 say if we have a human resources department right and we have a database for that well we're going to have tables for employees. We could have table for salaries we could have a tip for departments we could have a table that represents you know H.R. 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 a table 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. All right. And the rows These are the rows. OK. Going horizontally rows are horizontal columns or 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 make the most use out of sequel. So what is sequel really. I mean it's a language used to interact with a database. But what really what happens behind the scenes. Well basically a sequel is a set of commands and these commands. I'm just going to write some I'm just gonna write out. It's pronounced sequel but it actually is an acronym s q well 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 sequel 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 gonna do something right. So these commands are literally instructions to the debut database on what to do. Right. And we basically declare the instructions in a in the sequel 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 tape the database is going to pass our sequel 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 and sequel The database is going to interpret those commands and execute them and perform tasks. We could create new we 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 to this database. It could create all types of different objects. OK. 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 sequel 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 sequel 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 or 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 sequel 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 an and were hired on on this date and that those are referred to as queries when we write the sequel statements in a in a way that we're asking to 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 sequel exams is about writing queries and getting that right. Okay. And this can 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. 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. Sequel is a database independent language actually. And if you use the standard ANSI referred to as ANSI sequel standard in in the Oracle database or any other database it's going to work. All right. So sequel the knowledge again here about this language you can use that knowledge to possibly pass other database exams such as Microsoft Postgres and my sequel The sequel is sequel and it works on majority of the databases out there. That might be just slight differences from vendor to render and the syntax but otherwise the ANSI standard the ante standard of sequel works in all databases and that's what we're going to be focusing on for the most part 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 OK a hard drive looks like this as well OK. Sorry. This destroying Gene didn't come out that great as the others so that they 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 is a desktop monitor here. This this harddrive 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 city roms and and the start button down here and then bunch of ports in the back. That's essentially what a server is as well. All 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. I'm just going to draw these many disks here. It could be several disks and the Oracle is a software that's installed on this server and the data is stored in these persistent disks right here. OK. Same thing up 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 know they can install an entire infrastructure in a company where they give the servers as well as a 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 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. So from 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 Web site where you can get access to the database and you can type sequel 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 in Asia in 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 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 Web site 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 Web site. 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 not right that here Apex sequel command Ed and it's basically a web site where you know you're right in your browser you can type sequel and those commands when you hit the run button. They're going to 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 can be mastering sequel and we can practice all those commands in the Apex tool which is the Web site 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.