Oracle Database 12c: SQL Fundamentals (1Z0-071): Relational DBs, Oracle, and SQL

0:00Why don't we take just a couple of minutes to make sure that you and I have a baseline understanding of what a relational database is or what, at least, I mean and what most people mean when they talk about a relational database. It can be a little bit confusing because it's one of those words, it's one of those phrases that's tossed around a lot by experienced database administrators and database programmers. Relational database, oftentimes, known as an RDBMS, which stands for Relational Database Management System-- interchangeable terms. I just simply use the term relational database. And if you don't know what it is, or you're brand new to databases, it can be a little bit confusing.

0:34So let's break it down. We'll talk first about the database portion of the relational database phrase. A database-- and you might know this already. This is a bit more of a common phrase or is a bit more widely used vernacular. But a database you can think of as a permanent store for data that exists beyond the lifetime of a session application or power cycle. So a database is a place to permanently store your data. And when I say beyond the lifetime of a session, what I mean is, imagine you are sitting down for your work day, and you key some information about a sales order into an application.

1:09And then you close that window, or you move on to a new sales order. You get up, and you go to lunch and come back later. Well, you start a new session. But you want that data that you already keyed in to remain. If you were to go look it up tomorrow, or later in the day, or next week, you still want that data to be there and to be visible. So it has been stored beyond the lifetime of your session. It's also been stored on the lifetime of application. A lot of data in a programmed application exists in memory. And when you shut down that application, that data evaporates, essentially. It's gone; it is not remembered. But a database is a way to store it even after you shut down the application beyond the lifetime of the application.

1:44So if you shut down the application, you go home for the day, you come in the next day, and start the application back up, that data is still there. It's also intended to be stored beyond a power cycle. And I mean that exactly what it sounds like. If you reset your computer or if the server where the database is stored is shut down and started up later, the data was written to fixed storage on the hard drive. So the data still exists. It persists beyond the lifetime of even a power cycle. That is the idea behind a database. And that is what a database is intended to do. It's also intended to do this in a way that we can easily and efficiently store that data, retrieve that data, search it, analyze it, aggregate it, and do a lot of other things.

2:21But at its heart, at its core, what a data is meant to do is permanently store your data. Now the relational part is a little more complicated. And that's where, actually, the trickiness comes in. A relational database-- and this is a sort of a loose colloquial definition-- is the data stored in a structured format with recorded relationships between the elements or entities. It's a little more difficult to explain or understand. But the idea here is that your data is stored in a highly structured format, and it relates to one another. So imagine if you had a bunch of Word documents, 1,000 Word documents with a bunch of sales orders typed into them just saved in a folder on your hard drive.

2:58Well, that wouldn't be very useful. How would you, for instance, look up the average price of the sales order or the total for a given month? It's all in a bunch of text documents. You couldn't do that. Conversely, imagine if that data was all typed into a bunch of Excel worksheets or a bunch of spreadsheet worksheets. Now you could easily summarize a column or group together that data. You could imagine you could write functions in Microsoft Excel in order to get that data out. And that is what you should think of a relational database as being. In fact, I use that analogy a lot. A relational databases a lot like a spreadsheet. Data is stored in individual little cells, and the cells relate to one another.

3:32All of the data that is in column B is the same type of data. Maybe it's someone's last name, maybe it's the sale price, maybe it's the quantity ordered. And all of the data that is in row x, row 5, or row 7, or whatever relates to each other as well. That's all the data that relates to a given sales order, for example. So a spreadsheet is actually a form, a very good example, of a relational database. And I would encourage you to keep that in mind if you are new to databases as a way to understand how a database is structured. Now relational databases are not the only type of databases.

4:02And when we use the phrase relational databases, we're actually talking about a pretty specific type of databases. You're likely familiar with some of these or the names of some of these. These are databases like MySQL, SQL Server, Postgres, and the one that we are going to talk about instead of this Nugget series, Oracle database. These are commonly used relational databases. And they share a few features or traits that make them reasonable to group together as alternatives or stand-ins or competitors for one another. For one, they use SQL to interact. We'll talk a lot about SQL over the coming weeks here inside of these Nuggets.

4:34SQL is a standardized language for interacting with databases. And all of these databases listed here, and most relational databases, it's sort of implied or assumed that SQL is the language you are going to use to interact with them. They are also, like I said before, they're highly structured. These are incredibly well-structured, formed databases. Data has got to conform to the structure when it is saved in a database. They have fixed schemas. We'll talk about what schemata are i n another Nugget. But they don't change very much. They're fixed and set in stone. And they have these things known as foreign key relationships, which allows you to, again, relate data to one another.

5:08So data over here that is orders might have a foreign key for customers. And that relates back to the customer data, so you know which customer was related to that order inside of that database. That's an example of a foreign key relationship. And this is used in comparison with a brand of popular databases that are today called NoSQL databases. There are a lot of NoSQL database options out there, things like Mongo and Cassandra. They're very, very popular. NoSQL databases may or may not use SQL. Contrary to the name NoSQL, NoSQL actually stands for not only NoSQL.

5:43So they may use NoSQL; they may not. They're very loosely structured. They have mutable schemas, so you can change and update the schema as you go. And there's often very few relationships between the data. The reason you would use nonrelational databases, NoSQL databases is essentially a trade for speed. Usually, they take up more space or they are less efficient for rights, but they allow you to read data much more quickly. And the only real takeaway there is that NoSQL databases are not better or worse than relational databases. These are two sides of a similar coin.

6:14And they both are better and different use cases. So you should make sure that you're always using the right one. Now, of course, this Nugget series is focused on Oracle DB, which is a relational database. And that is the type of interaction, the type of database that we are going to be learning about. So that covers what is a relational database. I hope you have a better understanding of A, what a database is, and B, what makes it relational. I hope this has been informative for you. And I'd like to thank you for viewing.

0:00Pretty much universal to the concept of relational databases are these objects: tables, views, and queries. These are the components inside of your database that are most frequently used to store and access your data. And since that's sort of the core functionality of a database, storing and accessing data, these are the objects that you're most frequently going to interact with. Now, there are a lot of objects in a database, potentially. An Oracle database, just like all databases, can be very, very complicated. You don't just have tables, views, and queries, you can have procedures, functions, schemas, users, permissions. There's a whole host of different objects that can be in your database, but we are focused, in this Nugget, on tables, views, and queries.

0:35And we want to talk about what they are because you are going to interact with them so frequently, it's really important that you just kind of understand intrinsically what these are, what this vocabulary means. A table is the object inside of your database that is used to store your data. It is a logical structure in the database in which you store data. I have an example of something that looks like a table right here. And if you're looking at this saying, actually that kind of looks like a spreadsheet, well, you're not wrong. I took this screenshot out of a spreadsheet and that's because I think that a spreadsheet is the perfect analogy, the perfect metaphor, for a table in a database.

1:06You probably are already familiar with a spreadsheet, you've got a bunch of rows, and a bunch of columns, and you can put data inside of the intersection of each of those rows and columns, called a cell. That cell is referenced by the row and columns so you would say, B5 would reference this value right here, whereas D7 would reference this number down here. Well tables are the same way, you declare a table and it's got a name just like your spreadsheet has a name, and it's made up of columns and rows which in a table we call fields and records. So each one of these columns is a field, and each one of these rows is a new record.

1:41And this is where the relationship in a relational database comes from, that's why we call it a relational database, because every piece of data inside one of these columns is related to one another in some sense. Here in the third column, column C, those are all the state where maybe these people work or live, whoever they are. So all of that data in column C is the state, it all relates to one another because it reflects the same type of information. And all the data in a given row is related to one another as well. If we look at this row down here, number seven, this is all the data about Amanda Wilson.

2:12I can assume that every data inside of this record is related to one another, because it all relates to the same individual or the same piece of information inside of this table. That number right there, 43500, I know is Amanda Wilson's number, it's not Jill Davis's number. We are going to work with, and talk a lot about, tables throughout this Nugget series. So I don't want you to focus too much on what's going on here, just try to think of a table as like a spreadsheet in a spreadsheet file. Now views and queries on the other hand, are ways that you can look at, or manipulate the data inside of your table.

2:43And early in your database career you can get views and queries mistaken pretty easily, because they perform sometimes similar functions. But what you ought to think about for a view, let's start with views, views are like a window into your database. They're preset, possibly filtered lists of data out of one of your tables. So the window looks at your table and maybe it's got a filter on it that's going to limit you to just these rows right here. And the view allows you to repeatedly extract that information. Not do anything with it, not delete it, or remove it, or get rid of it, but simply look at that data, look at that information in your database over, and over, and over.

3:16It's great for repetitive tasks. Imagine a 30 day sales report that gets run every afternoon, it's always pulling the last 30 days of sales. You set up that view to pull the data that is needed for that report, and now the engine, the Oracle database engine, can run that view over, and over, and over whenever that report needs to be executed, which is every afternoon, like we said. Now conversely, a query is a request, or an update to, specific data from one or more tables. So you can have your data spread out across multiple tables, that are then joined together in a query so that you select the data all in one.

3:49Included in your query can be filters, aggregations, or even sorting, and all of these things are called a query. And the basic idea is you write this query, and it gets executed by the database engine, and if it has results, those results are sent back out to you. Now queries can be used to look at data, just like the view, but queries can also be used to update the data. It's called a query when you insert data into your data, when you change data in your database, all of those things are considered queries in the general sense. A lot of times people are talking about just select queries, selecting data out, so that you can see the results.

4:20Which is again, similar to a view, but a query can be any of these things and it's important to understand that. So those are queries, views, and tables, inside of your Oracle SQL database. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00Databases, like all things in IT, can get really complicated really, really quickly. Rarely is it the case that you've just got one or two tables and a couple of views inside of your database and that's the end of the story. Even for a really simple deployment, you are likely going to have dozens of tables and maybe hundreds of views and queries to look at all that data in different ways. This is not unusual. So consequently, you need a good way to map out or chart out the database even by hand, even just with pen on paper. And that's where any entity relationship diagrams come in.

0:32I like to consider ERDs the flowcharts of databases. But of course, this does have a problem-- databases don't have any flow. They don't have any process associated with them. Yes, you can add code to a database, but that's code secondary from the database itself. The database is just a fixed store of data. So we are not actually recording the flow of any data or the flow of any processing. Instead, our entity relationship diagrams are modeling or are drawing out our entities and their relationship to one another, just like it says, ERD. So first, what are the entities?

1:02Well, the entity is a single item or a single thing in the database, and it's typically a single table. When we talk about an entity in an entity relationship diagram, we are almost always talking about a table. So, for example, the customer table is an example that I've used that kind of looks like a list of names and states. Well, we might draw that out like this, and I am physically going to draw it. We would say, all right, this is my customer table. And I'm just going to make a square. That's all it is. That's all the entity in a entity relationship diagram is-- it's just a square with the name of the table.

1:35And then I indicate the field-- so there's first and there is last and there is state here. And I indicate what data is being stored in these fields. So these are all character fields because they have characters in them. But if there was some kind of numeric column down here, then I would list that I would write numeric here or whatever. So that's the entity. That is the table here listed as a square. If we have additional entities inside of our database, we would model them with additional squares. And so on and so forth.

2:05So every entity, every table that it is going to store data inside of our database can be modeled by drawing out one of these little squares, one of these little entities inside of our ERD. And by the way, the point here is not necessarily to create a perfect future prediction of our database, although a lot of times, you are trying to map out a database before you develop it. You may also be trying to map out an existing database, a database that is developed and changed over time. You may need to sit down and get an overall view of it. Well, this is how you start. You start by mapping out the entities themselves, and then you move onto the relationships.

2:37And the relationships inside of your database that you consequently want to draw out or model inside of your ERD are how the entities relate to one another. It's a little bit confusing at first, but here, let me try to explain. Imagine my entity, again, this is my customers table. And imagine I have my second entity, which is my orders table. Well, the way these two relate is that one single record inside of my customers entity can, well, have multiple different records inside of my orders table.

3:08And that's because customers can have multiple different orders. Now one order never has more than one customer-- it's only a customer that has multiple orders, so we call this a one to many relationship, and we model that out exactly as I just did there with that pitchforked triad looking stick to a straight line from customers to three different little ticks here on the orders. That doesn't indicate that there's actually three orders for every customer. It just means that there is possibly more than one order for a customer. There may be zero orders for a customer. Maybe you're just tracking customers inside of your database that haven't ordered anything but only thought about ordering something.

3:42In which case, you would do something like this. You'd put a little zero on there and that says that it's one or zero to many orders. So you can have one or zero or two or three or however many orders that you have per customer listed in your customer entity. So we are modeling the relationship between the customers table and the entities table, and this can get really crazy really quick. I mean, here I just have customers and orders, right? Well, imagine we have something like this. Now I've got my customers entity, I have my orders entity, which is nothing more than a customer ID and a date, and then I have my order items entity.

4:21This might be a third table, and what does that got? Well, that's got the order number, the quantity, the price. And same thing, we've got one to many because one customer can have many orders, but each order can have many line items on it. So a given order might have multiple different line items. Here order number 17, which is that very first one, has one, two, three different items that that person ordered inside of that same order. It's like they added three items to their shopping cart on the website. So the customers to orders is a zero to many, but the orders to order items is a one to many.

4:53And the reason for that is because an order has to have at least one order item, otherwise it's not an order. So here is how we would map out or we would model these entity relationship diagrams. So that's just a very quick example of what a simple ERD might look like. Like I said, it can get complex quickly. This is one that I just searched for on the internet and found. Even this is not an overly complicated ERD. This is a pretty typical relationship diagram for a database that you might find in a production system. So, yeah, they can get really crazy, but they are very, very helpful and when it comes to understanding your database.

5:24I hope this is a been informative for you, and I'd like to thank you for viewing.

0:00Database normalization is one of those topics that you could spend hours talking about. Many, many college courses are devoted to exactly this topic. It can get incredibly complicated and incredibly in-depth. We want to avoid a lot of this inside of this Nugget. I want to introduce you to the concept of database normalization in the first three normal forms, give you enough material that you can go and research on your own and learn more about it if you need to, but hopefully you get a basic understanding of what it is. First of all, a paraphrase of the Wikipedia definition of database normalization.

0:32We are structuring a relational database in accordance with a series of normal forms in order to reduce data redundancy and improve data integrity. Let's just back up from that a little bit. So we'll go in reverse order here. Why do we normalize the database? To reduce data redundancy and improve data integrity. When you have the same data in multiple different places, there's this kind of Murphy's law for database programmers administrators that it's never going to agree. It's never going to be the same. To the extent possible and simple, you want to reduce the number of times, the number of duplications of a given piece of data.

1:06You don't want data redundant, over and over and over. You want it stored in one place. You also want to improve your data integrity, and that means making sure that, for example, orders don't exist for customers when there's no customer, no matching customer record in the database. Database normalization is intending to solve a lot of that. And it does that by structuring the database into normal forms. Now normal forms can get pretty complicated. There's 11 or so normal forms, I think, but they are progressive. And what I mean by that here-- I've copied this from Wikipedia as well-- is a database that is in, say, the second normal form is also already in the first normal form.

1:41If it's in the third normal form, it's in the second, which means it's also in the first. You get the idea. So it's progressive down the line here. You cannot be in compliance with a higher normal form without already being in compliance with all of the lower normal forms. One last thing to understand about normalization-- and we're only going to talk up to the third normal form in this Nugget-- is that the third normal form is typically what we call a normalized database. This is just by convention there's no rule that says third normal form databases are normalized. That's just when someone says, oh yeah, the database has been normalized, it means that database has been structured in accordance with the first three normal forms.

2:18So first normal form, each attribute has atomic data in it, i.e. you don't have any multi-valued fields. Take a look at this example table right here. Everything looks good until you get to the orders column. Now look at that. It's got dates, it's got order quantity, it's got order item in it, it's got all this different data inside of one column, inside of one field. This is bad. This violates the first normal form. A better version of this would look something like this. Here each individual point of data has been broken out into its own column. This is fairly common sense. If you've ever worked with spreadsheets, I'm sure that top example kind of gave you a little bit of a shiver.

2:52You would much rather see it in this bottom example, just for the simple fact of the matter that if you want to, say, add up the order quantity, now you can put a summation column at the bottom of that quantity, whereas here, how would you get the sum of the order quantities out of that column? It would be really hard and really difficult to do. So first normal form means that every attribute has atomic data. Every field has one and only one value, or one and only one piece of data in it. Second normal form, every non-prime attribute is dependent exclusively on the primary key.

3:23So taking that example we just looked at, now this is a bad example, because while it was reliable or while it complied with the first normal form, it now violates the second normal form. A better version of the above would look like this. We would split it up into two entities. Notice what's happened up here in the top. I've got Sam Jones listed twice. I have Joe Smith listed twice. What's going on up there? Is this the customer table or is it the order table? Which one does this ID, this unique ID, represent? It's difficult to tell. And we have a problem here, right? We could have Joe Smith listed at the top, and then his name could be misspelled down here.

3:56Is that a different customer, or is it the same customer with a misspelling? That duplication of data introduces the possibility of having bad data. So instead, what we'd rather do is simply have a customer table, a customer entity, and have a one to many relationship-- there's our entity relationship diagrams again-- within orders entity and orders table. And those orders would simply have this customer ID field that would reference back to the customers table. And now we can see for every customer, the individual order date, the quantity, and each of these orders has their own unique identifier.

4:26So I know that the order number four went to customer number two, Joe Smith, on this date for 20 picks at a price of $12.5. So this structure here now complies with the second normal form. And then lastly, to ensure our database is perfectly normalized-- I say that with a bit of sarcasm-- we want to meet third normal form, where all the attributes are determined only by the primary key. This one can be a little bit difficult to get your head around, but I think the example will help. So again, I have my good example from the previous slide that complied with the second normal form but it violates the third normal form.

4:58Why is this bad? Well, look over here. Order item and order price kind of go together, but they don't necessarily go with order ID, because there could be another order down here that had widgets at that price. And what if this listed widgets at a different price? What would we be trying to say there? Was it a different product or was a different price given? So how do we make this better? How do we make this a compliance with the third normal form? And the answer is, we pull the order data out into a separate entity as well. So now I've got my customers entity, I've got my items entity, and then finally, I've got my third entity, my orders.

5:31And the orders simply use an item ID to reference the items here so that now I know that slips always cost $36, and if someone has item ID number one right here, I know that they have paid $36 for the slip, we can multiply that by the quantity if we need to, and learn what the total price of the order was. What have we done? We've ensured that all of the attributes inside of this table, inside of this orders table, are dependent only on the order ID, and everything else that is not is inside of its own entity, either customers or items.

6:02This database here is compliant with the third normal form. So that is the very quick rundown of database normalization. Some of it is common sense, I think. Some of it takes a little bit of work to get your head around. If you want to understand more, you can look up and research a lot about normalizing databases and what it means and why you do it on the internet, and I'd recommend that you do that if you're unfamiliar with it. I hope this has been informative for you and I'd like to thank you for viewing.

0:00If you are planning to work with databases in any capacity as an administrator or as a programmer or as simply someone who wants to analyze some data that's stored in a database, you're probably going to need to know about structured query language, SQL. If you are taking this Nugget course in order to pass the Oracle DB SQL exam, well, it's right there in the title, SQL. Obviously, you're going to need to know what SQL is if you want to pass that exam. SQL is a standardized language that was developed for interacting with relational databases. It's either pronounced SQL like I like to do or a lot of people pronounce it sequel.

0:32They're interchangeable terms. And it's pretty much assumed when you're talking about a relational database that SQL is the language that is used to manage the data and these structures, the things, inside of that database. SQL is a very standardized language, and it was developed expressly for managing databases or managing RDBMS systems. And because it was developed expressly for that purpose, it separates itself from what I'll consider traditional programming languages, things like C or Java or Python. Those languages are generally procedural, and a procedural language means each line of code is executed one after the other, right?

1:09It just walks down the line. If you type 10 lines of code, the interpreter or the compiler executes each one of those lines of code in order, starting with 1, then 2, then 3, and just like that until it reaches the end of the code. SQL, on the other hand, is mostly, although importantly, not entirely, a declarative language. This is a pretty esoteric description of functional languages and declarative languages and procedural languages. But the point here is that SQL is kind of interpreted holistically, the entire SQL statement. I've got an example of an SQL statement right here. And don't worry if you're brand new to SQL.

1:41It's meant to be fairly easy to understand, fairly legible for SQL statements. You don't really need to know how this works, but you can look at it and I think you can figure it out. We're going to update something called scores. We're going to set the home score to 35, the away score to 17, but only where the game number equals 22. So I only want to update one game with this particular score. If this were a procedural language, the first thing it would do is it would say, OK, we're updating scores. Set every score to home 35, away 17, and oh, oh, wait, I wasn't supposed to do it to every score, just this one particular game score.

2:14So interpreting this command procedurally would be a problem, whereas SQL, this command is interpreted holistically. The entire command is looked at, and so the database engine, the SQL engine, is going to look at this and say, yep, I already know that I need to limit to game number 22, and it will never make the mistake of updating every game in the Scores table with that score. So it will look at and understand the entire language. Effectively we are telling the SQL engine or the database engine what we want to do. We're not telling it how to do it. We're letting the SQL engine handle that itself.

2:45We're simply telling it what we want to do and the how is really unimportant to us. And that statement that we just looked at, by the way, more or less conformed to the American National Standards Institute definition of SQL. There is actually an ANSI SQL, which is the formal SQL, the real-- I'm making air quotes with my fingers there, SQL. Unfortunately, every RDBMS implements its own variation of ANSI SQL. It's kind of like dialects of a language. So every RDBMS system, whether it's Microsoft SQL server, My SQL, or, yes, Oracle, has its own little nuances, its own little tweaks, its own little variations from ANSI SQL that make it unique to that system.

3:25And there's a lot of different reasons that they do this, not really worth getting into. You should just understand that knowing ANSI SQL is kind at the beginning, is the start, and if know ANSI SQL, you're going to be able to read and recognize SQL from any of these systems. But being able to write SQL that works accurately in those systems is always going to require some specialized knowledge, just like being able to speak Southern American English is different than Northern American English, but you'll probably be able to understand anyone who is speaking either dialect if you know English, as long as you know English.

3:57Another important thing to understand about SQL is that it is intended to be written and then handed off to the database engine, to the SQL engine, to be processed. So you might have an application or a program that compiles and builds this particular SQL statement, and then that application simply passes that statement down to your database engine. And the database engine runs that code, it figures out the most efficient way. Like I talked about before, it's declarative. We describe what we want to do, the database engine handles the how. So it will figure out the fastest and easiest and most efficient way to accomplish what you've described, and then it will do that.

4:31It will also hand back a data if that's appropriate as well. So a simple SQL statement here called a select statement, because it starts with the word select, is a query, and this query is intended to return data. When you pass this SQL statement over to the engine, you are expecting a response. So whatever program or code you're working with to send this data has to also be programmed to handle a response of data, has to handle the results. And the results might be zero records, maybe nothing matches our game date of 1/1/2017. Maybe there was just one game that day.

5:02We might only get one result, or we might get two or three or however many results. We're getting a zero to many set of data in return from a query, and so whatever handles the results of this query has to be able to handle a collection of data that might be empty. It might have 1 row in it, or it might have 2 or 3 or 5 or 500 rows in it. We never quite know. We have to be prepared for that eventuality. So those are the basic elements of SQL, structured query language, a way to easily and smartly interact with your database files in your database engine.

5:35I hope this has been informative for you, and I'd like to thank you for viewing.

0:00More famous than the siege of Troy, bloodier than the battle of Stalingrad, and more tragic than Tupac versus Biggie Smalls, it's ANSI SQL versus Oracle SQL. A battle between two titans who seek out to define what it means to be a structured query language for manipulating databases. What is ANSI Al and how does it differ from Oracle SQL? Let's take a few minutes to figure that out. ANSI stands for the American National Standards Institute. ANSI SQL was developed in 1986 as a way to standardize SQL as a programming language for use across any RDBMS, any relational database management system.

0:40It was last revised in 2011, so it's fairly up-to-date and fairly new. And most importantly, ANSI SQL is intended to be a vendor-m neutral baseline SQL language. The idea with ANSI SQL is that it is generic enough that it can work on any database. It is vendor-neutral, does not care who or which database is implementing it. Oracle SQL, on the other hand, was developed, of course, specifically by Oracle itself. It has been revised with each revision of the database software. Each revision of Oracle database has come with some new updates and changes to Oracle SQL.

1:15And Oracle SQL is not the same as ANSI SQL. Specifically, Oracle SQL has a lot of extra features that only work on Oracle. It is vendor-specific. If you really learn Oracle SQL, you are absolutely going to learn some things about SQL that will not work, for example, on MySQL, or on Microsoft's SQL server. So which one wins? Who comes out on top in the battle between the widely accepted standard or the power of some vendor-specific language? Well, the unsatisfying answer, unfortunately, is that they both win.

1:45Both of them come out on top, and there's not really all that much of a battle to begin with. Yes, Oracle SQL extends and expands ANSI SQL and is not always compatible with ANSI SQL, but the simple fact of the matter is that if you are learning Oracle SQL or really any vendor-specific SQL, you're kind of learning both ANSI SQL and that specific SQL at the same time. Take this SQL statement right here. You don't really need to understand how it works. It's pretty simple. What I want you to pay attention to is this statement right here, join and on.

2:17This is an ANSI SQL standard statement. It creates a simple query that selects data from two different tables and it joins those tables on a field called customer_ID and just ID. Now the Oracle version of this looks very, very similar, but it's slightly different. Notice there is no word join. It's just customers, comma, orders. Instead of the join in there, we just have a comma. And instead of the clause on, we have it embedded right inside of the where clause. We say where a customer ID equals ID. So these are obviously very similar.

2:47You can look at these and probably figure out that they are doing the same thing. They're just slight different variations on the same syntax. And actually, as of Oracle9i, Oracle supports either statement. So Oracle will actually implement ANSI SQL standards as well as Oracle-specific SQL standards. Throughout this Nugget series we're going to be using both pretty much interchangeably. It's important to know that you may be learning some Oracle-specific stuff, but most of what you learn is going to be recognizably standard ANSI SQL.

3:18And more importantly, for the exam, you will not need to know the differences. You don't have to identify ANSI SQL versus Oracle SQL. You just have to know that Oracle has their own unique implementations sometimes, and for the most part, this is the version that you need to know. The Oracle SQL is the version that you really need to know in order to pass the Oracle database SQL exam. Now there's this other thing that I mentioned briefly, or really didn't mention at all. And that's SQL*Plus. I don't know, it's like the worst name for a product ever. And it's compounded by the fact that it actually means two different things.

3:51Oracle SQL*Plus-- I think it's just pronounced SQL Plus-- is both an additional set of commands for Oracle SQL. But it's also a command line utility for running SQL and SQL*Plus commands. SQL*Plus is both things. It has some extra additional functionality that they have tacked on to SQL that only works on Oracle databases, and it's a utility that you can run from a command line instead of the Oracle Developer graphical user interface. You can run from a command line to execute SQL statements against your database. It's both things.

4:21So you have to specify which you're talking about if you use the phrase SQL*Plus. Now again, for this exam you don't really need to know anything about Oracle SQL*Plus. There are only two elements of SQL*Plus that we're going to learn about, and that is the DESC command and something called substitution variables. Those will be covered in later Nuggets. Those are the only SQL*Plus-specific things that we are going to learn. Everything else is going to be Oracle SQL, and by extension, pretty much just the same as ANSI SQL. So don't worry too much about that battle, or what's the difference between ANSI SQL and Oracle SQL.

4:53If you're learning Oracle SQL, and that's what we're going to do, you are mostly going to also learn ANSI SQL at the same time. I hope this has been informative for you and I'd like to thank you for viewing.

0:00You can't have too much of a good thing. That's what I always say. Actually, I think that might be the first time I've ever said it. But regardless, the people who came up with and have developed SQL over the years agree with that sentiment. They believe that SQL can do a lot of different things besides it's original intended purpose, which was just to work with data inside of a relational database. And to that extent, over the years SQL has grown into a lot of different categories and a lot of different types. And we want to take a look at a few of those different types here inside of this Nugget. The primary purpose of these categories is going to be to help you understand what the different SQL statements do inside of the broader context of SQL programming.

0:38And also to help you understand how you can manipulate both your database, as well as the data inside of it, using SQL statements. So SQL, as we've talked about inside of another Nugget, is really the industry standard language for interacting with your relational database management systems. But the problem is that databases are complicated. You may not think so at first-- just a database is a couple of places to house some data, some information, right? But of course those little houses, those tables themselves, you can have multiple different tables. They have individual fields inside of them with different data types and constraints and indices associated with them.

1:11The tables get grouped up into schemas, they get permissions assigned for users, you have to be able to manage access and control and flow and process inside of these databases, and-- all right, yeah, sure. My head already hurts. And SQL can handle a lot of this, so we break it out into some different categories so that we can see the different ways that it handles it. And there's a lot of different types of SQL out there. But the three that we want to learn about and we need to know about for the Oracle Database SQL exam are the three primary ones-- DDL, the data definition language; DML, the data manipulation language; and TCL, the transaction control language.

1:48We can take a quick dive into each one of these. DDL, the data definition language, is just like it says. It's used to define the structures in the database that house your data. DDL is the SQL statements that are going to be used to do things like create a table or add rows to your table, drop tables, rename them. Even grant and revoke permissions is considered part of DDL. So there's a couple of different statements that allow you to build up and define the structure of your database without ever actually inserting any rows.

2:19Not inserting or reading out any data from your database, that's covered in DML. That's data manipulation language. DDL is the data definition language. So DML are the statements that are used to get data into and out of your database. That's things like select, insert, update, delete. Delete does not delete a table from your database if you have a place to house employees and an employees table inside your database. Delete does not get rid of that table. Delete deletes the data out of that table while leaving the database table itself intact.

2:49Drop is the statement that would get rid of the table itself because the table is part of the database structure. So DDL is used to create or remove that table. Delete is used to create or remove data inside of that table. DDL versus DML. The third one you run into less often, but it's actually a really important part of database transactions, and that is TCL, transaction control process. A lot of times-- and you'll see this over and over throughout this Nugget series-- we are using our DML and our DDL inside of something called a transaction.

3:22We are giving ourselves the ability with a transaction to either commit the changes that we make or roll them back. And TCL are the statements that allow us to do exactly that. Commit and rollback are the actual statements that we talk about. And they control the process flow for DDL and DML so that we can test out, we can try out, a transaction or we can try out some data insertion. And if it doesn't work right, if it's not what we want to do, we can roll back the whole transaction to the point that the database was before we started. So the two statements, commit and rollback, that we use to control that process are considered TCL, transaction control language.

3:57So very quickly that is the three types of SQL that we are going to be studying throughout this Nugget series-- DDL, DML, and TCL. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00Hello, everyone. This is Ben Finkel. And in this Nugget, we're going to walk through installing the Oracle database on our local system. Now, Oracle makes this easy to do now with installers. In the past, that was not always true. But if you come up to this URL right here-- I've got it onscreen, you can see. And I'm loading up a short URL as well. You can scroll down to Oracle Database 12c. That is what this exam is testing on, release 2. And you can see the different installers for whatever system you happen to own. Now, I'm installing on a Windows 64-bit system. So I'm going to download this top link right here, just by clicking the File 1 link.

0:32It's 2.8 gigabytes. It takes a little while to download. And what you get is a dot zip file. The installer for each of the other systems more or less follows the standards and conventions of installers you would expect on those operating systems. So you should be able to follow along with this, even if you are not using Microsoft Windows. And by the way, if you're concerned about licensing, you don't need to be. Oracle offers a developer license, free of charge. You can download and use these database files to play around with, educate yourself, and even prototype software without having to worry about paying for licensing.

1:05You are only charged for licensing when you want to actually deploy the database into a production system. Now, for Windows, when your file is finished downloading you'll see the zip file right here. And if you open it up, you can see that there is a database folder, which I have simply extracted out here into the folder where I downloaded my installer. And if we look inside of that folder, there is a setup.exe that we execute. And this is going to launch the Oracle database installer program. Funnily to me, it says we have to have at least 256 colors in our display, which is crazy.

1:37I can't imagine any display that doesn't have 256 colors. Now, at this point, a lot of steps are going to take a while. We'll walk through the initial setup. And then Oracle will start installing. And it can take a long time. It's a big software product. Unfortunately, you can't walk away from it and just leave it go, because a lot of times, Windows access control is going to ask you to validate some things, or allow it to install. So it's kind of an install process that doesn't require much from you, but you do have to sit around and babysit it on your screen. The first thing they'll ask you to do is sign in with either your Oracle Support username and password.

2:12If you don't have an Oracle Support account, that's OK. Just uncheck this box and it'll create a guest account for you, so that you can still get any important security updates downloaded over the internet in the installation. Now, depending on where and under what circumstances you're installing it, you have a couple of different options here. We're going to choose the first one, the default-- create and configure a database. This will create a basic database for us that we can start working in right away. If you already have a database or you want some more advanced installation options, you can install the database software only and work from there.

2:43But like I said, we'd like to choose the default option there. You can also choose if you're installing this on your desktop or on a server. The desktop is going to install everything with all of the client tools that we want pre-installed and configure a lot of things for us. It's definitely the best way to go for playing around. Obviously, in a production installation, you're going to want to install the server class. I'm going to use a virtual account. This is the account that Windows will execute the Oracle database processes in. So that's what will have the different permissions and the different ownership over the different objects inside of the operating system.

3:18At this page here, it's going to default with some folders and some file locations on your system to install into. You can change those around. I'm going to leave mine as the default option here. The really important things, right here, is this global database name and the passwords. This is going to be the initial database. It's ORCL. That's really important. Write that down-- ORCL. Or if you change it, write down what you change it to. And the password-- this is also really important-- this is the password for the system account, the administrator for that database. And you're going to need to remember that to connect to the database the first time.

3:52There are no users that you can connect to, other than the system. So you have to know that it's the username system and the password that you chose there during installation. Before it actually executes the install, it gives you a sort of summary overview of the different options and what it needs to do. You click the Install button, down here in the lower right, and it begins installing. Again, this is going to take a long time-- 20 minutes, maybe half an hour. And it is sometimes going to ask you to accept Windows access control. So you do need to babysit it a little bit. But you don't have to pay super close attention to what it's doing.

4:23And once that's finished it is all installed, including the database-- the sample database that we asked it to install-- and the client tools. We can close this. And the easiest way to check it out and see what's going on is to simply launch a tool called SQL Developer. SQL Developer can be found in your Start menu, right here. It's under my "Recently added," because I just installed it. There is the SQL Developer tool, so we can launch that very easily. So there is the Oracle database and client tools installed on our local system. I also want to show you something called Live SQL.

4:55This is found at livesql.oracle.com. And Live SQL is a simple virtualized database that you can access through a web browser right here at this URL. If you are not in a position to download and install Oracle tools on your local desktop, this might be a very good way for you to walk through and try out all of the different things that we are discussing inside of this Nugget series. I'll be honest-- I have not used this a lot. But I believe that it will be able to accomplish everything that we do. Every demonstration that I perform on my local system with the local Oracle tools can be replicated here in Oracle Live SQL.

5:30Well. So keep that in mind. You simply launch the SQL worksheet. And this is going to allow you to key in SQL, see the output, and access it and understand how it works without having to have anything installed on your local system. So that covers the installation of the Oracle database software. I hope this has been informative for you. And I'd like to thank you for viewing.

0:00Well, we've talked and talked about databases. We installed the Oracle Database and client tools on our machine. Why don't we take a moment and actually write and execute some SQL statements we'll see reflected in the database using the Oracle client tools. I'm going to do this from my local desktop machine here. You can see I've got the Oracle SQL Developer tool opened. This was installed as part of the database package that we installed in my Nugget on installing Oracle software. Go back and watch that if you don't have this. You should be able to also more or less follow along with what I'm doing here in the Oracle live website in the virtual server that they create for you.

0:33To begin with, I have my ORCL connection open. And why don't I right click and delete this connection. We'll just recreate it real quick to make sure we know what we're doing there. With connections highlighted, I'm going to click the green plus over here, and I will give the connection a name. ORCL is fine. My username is going to be system. I don't have any additional users in this database yet. Type in the password. That's a password that I chose when I installed the Oracle software and when this database was configured, so I did that during the installation process. And the SID is ORCL, that was again the default service name that Oracle installer chose for me.

1:05I could have changed it, but I'm going to leave it as is. Click connect, and now this connection is over here on the left hand side. Why do we have to create a connection if the tool in the database or on my local PC? Well in a normal situation, in a normal scenario, you're not always going to have the database and the tools in the same PC. A lot of times a database will be out on a server somewhere else on the network, and there will be multiple databases that you might connect to that will be the production database and the development database, and maybe a test database as well, plus there will be different products, so there might be a database for your HR system as well as a database for your retail front end web page, and they might be separate databases requiring separate connections.

1:45So this allows you to use this client utility to connect to those different databases just by selecting them on the left here. If I expand that connection, you can see that there's already a lot of different things here. For example, if I expand the tables window, there's a whole bunch of tables already in my database. Most of them are labeled log, but these are all databases, or all tables rather, that the database uses to support the function of the database itself. We don't really want to work with these tables directly. The system will do that. The database engine is going to do that. Instead, why don't we create our own table?

2:16I'm going to do that right over here inside of my worksheet. I'm going to type create table and give it a table name. Mine will be customers. From there, I'm going to open parentheses, and I will type F name varchar open paren 20. And by the way, I'm just asking you to type right along with me exactly what I type in. You don't need to understand it yet. We are going to spend a lot of time going more in depth on each of these commands and what these different statements mean. For now, just echo back what I'm typing in so that you can see the same results that I'm seeing.

2:47Comma L name varchar open paren 20 comma, let's go state space varchar open paren two, and then close that original parentheses that I opened at the very top there. So something like this right here. Now this is an SQL statement. Specifically it is a DDL SQL statement. How do you know? Because we're using the create command. You can see it right there, the very first word. There is my create command. What am I creating? I'm creating a table.

3:17Notice that the SQL developer tool highlighted in blue so I know that those are keywords, the reserved keywords for SQL. Then I indicated my table name. I have an open enclosed parentheses, and in-between those I've listed my three columns, my three fields that I want inside of my table. And it looks like state is highlighted in blue. That's probably a reserved word. Instead of state let's go st. Now that'll work. OK. Now that I've got three columns and the create table statement, I'm going to click this green button right here. That's the run statement. I can also hit control enter as it tells me.

3:49And that executed and created the table inside of my connection over here. If I expand tables I don't see it yet, but if I right click the tables folder and simply say refresh, open it up, now there is a customers table. And if I select it, I can see it has three columns in it. F name, L name, and state. What did I do here? I created a table with three columns. You can think of it as like a spreadsheet that has an A, B, and C column, except they're not named A, B, and C, they're named F name, L name, and ST. Next I would like to insert some data into that table. So I'm going to get rid of this and I'm going to go with insert into customers values, open parentheses, and let's say Ben in quotes Finkle in quotes, a comma, and then NY, because I live in the state of New York.

4:40Close parentheses. So very similar structure to the DDL statement, the create table statement that I just made. I have to put a semi-colon on the end of it. Now I am calling a DML statement, a database markup language statement, because it's an insert statement. And what am I inserting? I'm inserting data. Specifically I'm going to insert one row into my table with a value of Ben in the F name field, Finkle in the L name field, and NY into the state field. We can execute this as well with the green button, and that has been inserted. And finally we can take a look at that data.

5:12If we call select, F name, comma, L name, comma, ST from customer's semi-colon. One more DML statement. This is a select statement, also called a query. I went to see these three columns from the customers table. I'll click run. Down here in the bottom I get a new window opened called the query result, because the select statement returns data, and there is a row, Ben Finkle NY, returned from my table. So what have we done?

5:42We have created a table. We've inserted a row into it, and we have queried that data out of it. Pretty simple, straightforward, and easy utility to use, and it's pretty simple and straightforward to write these basic SQL statements. I hope this has been informative for you, and I'd like to thank you for viewing.