Oracle Database Introduction & Basics of Tables

A free video tutorial from Imtiaz Ahmad
Senior Software Engineer & Trainer @ Job Ready Programmer
Rating: 4.6 out of 5Instructor rating
12 courses
398,147 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 November 2022

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, we're going to go over what is a database and more specifically, how is an Oracle database set up? Okay. 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. All 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 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 an a picnic. Right. That's not that's 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 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. 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 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 six row and then seven onwards. All right. 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 a let's say this is a table for per 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 DOB. 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 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 going horizontally. These are referred to as records. I'm going to 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 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 date of births for the particular person's right. That's all it should contain. It shouldn't contain any numbers or addresses or phone numbers. This column is purpose. Its reason for existence is to store date of births for the particular records. 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. 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 DOB 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 that don't have any parentheses or dashes or anything like that. These are just pure, 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 hallo 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 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, right, of particular date format. So the data type of the DOB 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 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 just 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, 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. All 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. 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 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 table for departments. We could have a table that represents HR policies and procedures. We could have a table for 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 HR database, it would contain a 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 going horizontally. Rows are horizontal columns of 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 SQL. So what is SQL really? I mean, it's a language used to interact with a database, but what really what happens behind the scenes? Well, basically, a SQL 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 SQL, but it actually is an acronym SQL, 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. All 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 in the SQL language and the database takes that those instructions and performs some task. 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 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 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 SQL, 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 a 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. 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 going to be learning how to perform all of these tasks using the SQL language. 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 those are referred to as queries. When we write the SQL statements in a in a way that we're asking the, the database to give us some data, add some information back, those are queries. And majority of this course, a huge chunk, I would say almost 60% write 60% of the Oracle SQL exams is about writing queries and getting that right. And this could be pretty tricky. You might want to slice and dice your data on 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. All right. 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, PostgreSQL and my SQL. That SQL is SQL and it works on 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 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. 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. And this belongs in something called. A computer. Right. Let's say there's 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 CD ROMs and 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 in 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 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. 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. 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 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. 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 running your browser, and it will make changes in the database. 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. They 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 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, you know, 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 editor. All right. I'm going to 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 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'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.