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

0:00Why don't we take just a couple of minutes

0:01to make sure that you and I have a baseline understanding

0:04of what a relational database is or what, at least, I mean

0:07and what most people mean when they talk

0:08about a relational database.

0:10It can be a little bit confusing because it's

0:12one of those words, it's one of those phrases that's

0:14tossed around a lot by experienced database

0:17administrators and database programmers.

0:19Relational database, oftentimes, known

0:21as an RDBMS, which stands for Relational Database Management

0:25System--

0:26interchangeable terms.

0:27I just simply use the term relational database.

0:30And if you don't know what it is, or you're

0:32brand new to databases, it can be a little bit confusing.

0:34So let's break it down.

0:36We'll talk first about the database

0:37portion of the relational database phrase.

0:40A database-- and you might know this already.

0:43This is a bit more of a common phrase

0:44or is a bit more widely used vernacular.

0:48But a database you can think of as a permanent store

0:51for data that exists beyond the lifetime of a session

0:54application or power cycle.

0:57So a database is a place to permanently store your data.

1:00And when I say beyond the lifetime of a session, what

1:02I mean is, imagine you are sitting down for your work day,

1:05and you key some information about a sales

1:06order into an application.

1:09And then you close that window, or you

1:10move on to a new sales order.

1:12You get up, and you go to lunch and come back later.

1:14Well, you start a new session.

1:15But you want that data that you already keyed in to remain.

1:18If you were to go look it up tomorrow, or later in the day,

1:20or next week, you still want that data to be there

1:23and to be visible.

1:24So it has been stored beyond the lifetime of your session.

1:27It's also been stored on the lifetime of application.

1:30A lot of data in a programmed application exists in memory.

1:33And when you shut down that application,

1:35that data evaporates, essentially.

1:37It's gone; it is not remembered.

1:38But a database is a way to store it

1:40even after you shut down the application beyond the lifetime

1:43of the application.

1:44So if you shut down the application,

1:46you go home for the day, you come in the next day,

1:48and start the application back up, that data is still there.

1:52It's also intended to be stored beyond a power cycle.

1:54And I mean that exactly what it sounds like.

1:56If you reset your computer or if the server where

1:58the database is stored is shut down and started up later,

2:02the data was written to fixed storage on the hard drive.

2:04So the data still exists.

2:05It persists beyond the lifetime of even a power cycle.

2:09That is the idea behind a database.

2:11And that is what a database is intended to do.

2:13It's also intended to do this in a way

2:15that we can easily and efficiently store that data,

2:17retrieve that data, search it, analyze it, aggregate it,

2:20and do a lot of other things.

2:21But at its heart, at its core, what a data is meant to do

2:24is permanently store your data.

2:27Now the relational part is a little more complicated.

2:29And that's where, actually, the trickiness comes in.

2:31A relational database-- and this is a sort

2:34of a loose colloquial definition--

2:36is the data stored in a structured format

2:38with recorded relationships between the elements

2:40or entities.

2:41It's a little more difficult to explain or understand.

2:44But the idea here is that your data

2:46is stored in a highly structured format,

2:48and it relates to one another.

2:50So imagine if you had a bunch of Word documents, 1,000 Word

2:53documents with a bunch of sales orders typed

2:55into them just saved in a folder on your hard drive.

2:58Well, that wouldn't be very useful.

2:59How would you, for instance, look up

3:00the average price of the sales order

3:02or the total for a given month?

3:04It's all in a bunch of text documents.

3:06You couldn't do that.

3:07Conversely, imagine if that data was all

3:08typed into a bunch of Excel worksheets

3:10or a bunch of spreadsheet worksheets.

3:12Now you could easily summarize a column or group

3:15together that data.

3:16You could imagine you could write functions

3:17in Microsoft Excel in order to get that data out.

3:20And that is what you should think of a relational database

3:22as being.

3:23In fact, I use that analogy a lot.

3:24A relational databases a lot like a spreadsheet.

3:27Data is stored in individual little cells,

3:29and the cells relate to one another.

3:32All of the data that is in column B

3:34is the same type of data.

3:35Maybe it's someone's last name, maybe it's the sale price,

3:38maybe it's the quantity ordered.

3:39And all of the data that is in row x, row 5, or row 7,

3:42or whatever relates to each other as well.

3:44That's all the data that relates to a given sales

3:46order, for example.

3:48So a spreadsheet is actually a form, a very good example,

3:50of a relational database.

3:52And I would encourage you to keep that in mind

3:54if you are new to databases as a way to understand

3:57how a database is structured.

3:59Now relational databases are not the only type of databases.

4:02And when we use the phrase relational databases,

4:04we're actually talking about a pretty specific type

4:06of databases.

4:07You're likely familiar with some of these

4:09or the names of some of these.

4:10These are databases like MySQL, SQL Server, Postgres,

4:13and the one that we are going to talk about

4:15instead of this Nugget series, Oracle database.

4:17These are commonly used relational databases.

4:20And they share a few features or traits

4:22that make them reasonable to group

4:23together as alternatives or stand-ins

4:26or competitors for one another.

4:27For one, they use SQL to interact.

4:30We'll talk a lot about SQL over the coming weeks

4:33here inside of these Nuggets.

4:34SQL is a standardized language for interacting with databases.

4:38And all of these databases listed here,

4:40and most relational databases, it's

4:42sort of implied or assumed that SQL

4:44is the language you are going to use to interact with them.

4:48They are also, like I said before, they're

4:49highly structured.

4:50These are incredibly well-structured,

4:52formed databases.

4:53Data has got to conform to the structure

4:55when it is saved in a database.

4:57They have fixed schemas.

4:58We'll talk about what schemata are i n another Nugget.

5:00But they don't change very much.

5:02They're fixed and set in stone.

5:03And they have these things known as foreign key relationships,

5:06which allows you to, again, relate data to one another.

5:08So data over here that is orders might have

5:11a foreign key for customers.

5:13And that relates back to the customer data,

5:15so you know which customer was related to that order

5:18inside of that database.

5:19That's an example of a foreign key relationship.

5:23And this is used in comparison with a brand

5:25of popular databases that are today called NoSQL databases.

5:30There are a lot of NoSQL database options

5:32out there, things like Mongo and Cassandra.

5:34They're very, very popular.

5:35NoSQL databases may or may not use SQL.

5:38Contrary to the name NoSQL, NoSQL actually

5:41stands for not only NoSQL.

5:43So they may use NoSQL; they may not.

5:44They're very loosely structured.

5:46They have mutable schemas, so you can change and update

5:49the schema as you go.

5:50And there's often very few relationships between the data.

5:53The reason you would use nonrelational databases,

5:55NoSQL databases is essentially a trade for speed.

5:59Usually, they take up more space or they

6:01are less efficient for rights, but they allow you

6:04to read data much more quickly.

6:06And the only real takeaway there is

6:08that NoSQL databases are not better or worse

6:10than relational databases.

6:11These are two sides of a similar coin.

6:14And they both are better and different use cases.

6:16So you should make sure that you're always

6:17using the right one.

6:18Now, of course, this Nugget series

6:19is focused on Oracle DB, which is a relational database.

6:22And that is the type of interaction,

6:24the type of database that we are going to be learning about.

6:26So that covers what is a relational database.

6:28I hope you have a better understanding

6:30of A, what a database is, and B, what makes it relational.

6:33I hope this has been informative for you.

6:34And I'd like to thank you for viewing.

0:00Pretty much universal to the concept of relational databases

0:03are these objects: tables, views, and queries.

0:05These are the components inside of your database

0:07that are most frequently used to store and access your data.

0:11And since that's sort of the core functionality

0:13of a database, storing and accessing data,

0:15these are the objects that you're most frequently

0:17going to interact with.

0:18Now, there are a lot of objects in a database, potentially.

0:20An Oracle database, just like all databases,

0:22can be very, very complicated.

0:24You don't just have tables, views, and queries,

0:26you can have procedures, functions, schemas, users,

0:28permissions.

0:28There's a whole host of different objects

0:30that can be in your database, but we

0:32are focused, in this Nugget, on tables, views, and queries.

0:35And we want to talk about what they

0:36are because you are going to interact with them

0:37so frequently, it's really important that you just kind

0:40of understand intrinsically what these are,

0:42what this vocabulary means.

0:44A table is the object inside of your database

0:46that is used to store your data.

0:48It is a logical structure in the database

0:50in which you store data.

0:51I have an example of something that looks like a table right

0:54here.

0:54And if you're looking at this saying,

0:56actually that kind of looks like a spreadsheet,

0:57well, you're not wrong.

0:58I took this screenshot out of a spreadsheet

1:00and that's because I think that a spreadsheet is

1:02the perfect analogy, the perfect metaphor,

1:04for a table in a database.

1:06You probably are already familiar with a spreadsheet,

1:08you've got a bunch of rows, and a bunch of columns,

1:10and you can put data inside of the intersection of each

1:13of those rows and columns, called a cell.

1:16That cell is referenced by the row and columns

1:18so you would say, B5 would reference this value

1:21right here, whereas D7 would reference this number down

1:24here.

1:25Well tables are the same way, you declare a table

1:27and it's got a name just like your spreadsheet has a name,

1:30and it's made up of columns and rows which in a table

1:33we call fields and records.

1:35So each one of these columns is a field,

1:38and each one of these rows is a new record.

1:41And this is where the relationship

1:43in a relational database comes from,

1:44that's why we call it a relational database,

1:46because every piece of data inside one of these columns

1:49is related to one another in some sense.

1:51Here in the third column, column C,

1:53those are all the state where maybe these people

1:55work or live, whoever they are.

1:57So all of that data in column C is the state,

2:00it all relates to one another because it reflects

2:02the same type of information.

2:04And all the data in a given row is related to one another

2:07as well.

2:07If we look at this row down here, number seven,

2:10this is all the data about Amanda Wilson.

2:12I can assume that every data inside of this record

2:14is related to one another, because it all

2:16relates to the same individual or the same piece

2:18of information inside of this table.

2:21That number right there, 43500, I

2:23know is Amanda Wilson's number, it's not Jill Davis's number.

2:26We are going to work with, and talk a lot about, tables

2:29throughout this Nugget series.

2:30So I don't want you to focus too much on what's going on here,

2:33just try to think of a table as like a spreadsheet

2:35in a spreadsheet file.

2:37Now views and queries on the other hand,

2:39are ways that you can look at, or manipulate

2:41the data inside of your table.

2:43And early in your database career

2:44you can get views and queries mistaken pretty easily,

2:47because they perform sometimes similar functions.

2:49But what you ought to think about for a view,

2:51let's start with views, views are like a window

2:53into your database.

2:54They're preset, possibly filtered lists

2:57of data out of one of your tables.

2:59So the window looks at your table

3:00and maybe it's got a filter on it

3:02that's going to limit you to just these rows right here.

3:05And the view allows you to repeatedly extract

3:08that information.

3:09Not do anything with it, not delete it, or remove it,

3:11or get rid of it, but simply look at that data,

3:13look at that information in your database over,

3:15and over, and over.

3:16It's great for repetitive tasks.

3:18Imagine a 30 day sales report that gets run every afternoon,

3:21it's always pulling the last 30 days of sales.

3:24You set up that view to pull the data that

3:26is needed for that report, and now the engine, the Oracle

3:29database engine, can run that view over, and over,

3:31and over whenever that report needs

3:33to be executed, which is every afternoon, like we said.

3:36Now conversely, a query is a request,

3:38or an update to, specific data from one or more tables.

3:42So you can have your data spread out

3:43across multiple tables, that are then joined together in a query

3:47so that you select the data all in one.

3:49Included in your query can be filters, aggregations,

3:53or even sorting, and all of these things

3:55are called a query.

3:55And the basic idea is you write this query,

3:57and it gets executed by the database engine,

3:59and if it has results, those results

4:01are sent back out to you.

4:03Now queries can be used to look at data, just like the view,

4:05but queries can also be used to update the data.

4:08It's called a query when you insert data

4:09into your data, when you change data in your database,

4:11all of those things are considered

4:13queries in the general sense.

4:15A lot of times people are talking

4:16about just select queries, selecting data out,

4:19so that you can see the results.

4:20Which is again, similar to a view,

4:22but a query can be any of these things

4:24and it's important to understand that.

4:25So those are queries, views, and tables, inside

4:28of your Oracle SQL database.

4:30I hope this has been informative for you,

4:32and I'd like to thank you for viewing.

0:00Databases, like all things in IT,

0:02can get really complicated really, really quickly.

0:06Rarely is it the case that you've just

0:08got one or two tables and a couple of views

0:10inside of your database and that's the end of the story.

0:12Even for a really simple deployment,

0:14you are likely going to have dozens of tables

0:16and maybe hundreds of views and queries

0:18to look at all that data in different ways.

0:20This is not unusual.

0:22So consequently, you need a good way

0:24to map out or chart out the database even

0:27by hand, even just with pen on paper.

0:29And that's where any entity relationship diagrams come in.

0:32I like to consider ERDs the flowcharts of databases.

0:35But of course, this does have a problem--

0:37databases don't have any flow.

0:38They don't have any process associated with them.

0:40Yes, you can add code to a database,

0:42but that's code secondary from the database itself.

0:44The database is just a fixed store of data.

0:47So we are not actually recording the flow of any data

0:50or the flow of any processing.

0:52Instead, our entity relationship diagrams

0:54are modeling or are drawing out our entities

0:57and their relationship to one another, just like it says,

1:00ERD.

1:01So first, what are the entities?

1:02Well, the entity is a single item

1:04or a single thing in the database,

1:06and it's typically a single table.

1:09When we talk about an entity in an entity relationship diagram,

1:12we are almost always talking about a table.

1:15So, for example, the customer table

1:17is an example that I've used that kind of looks

1:19like a list of names and states.

1:21Well, we might draw that out like this,

1:23and I am physically going to draw it.

1:24We would say, all right, this is my customer table.

1:28And I'm just going to make a square.

1:29That's all it is.

1:30That's all the entity in a entity relationship diagram

1:32is-- it's just a square with the name of the table.

1:35And then I indicate the field-- so there's first

1:38and there is last and there is state here.

1:41And I indicate what data is being stored in these fields.

1:44So these are all character fields

1:46because they have characters in them.

1:47But if there was some kind of numeric column down here,

1:49then I would list that I would write numeric here or whatever.

1:53So that's the entity.

1:54That is the table here listed as a square.

1:56If we have additional entities inside of our database,

1:58we would model them with additional squares.

2:04And so on and so forth.

2:05So every entity, every table that it

2:07is going to store data inside of our database

2:09can be modeled by drawing out one

2:10of these little squares, one of these little entities

2:12inside of our ERD.

2:13And by the way, the point here is not necessarily

2:16to create a perfect future prediction of our database,

2:19although a lot of times, you are trying

2:20to map out a database before you develop it.

2:22You may also be trying to map out

2:23an existing database, a database that is developed and changed

2:26over time.

2:27You may need to sit down and get an overall view of it.

2:30Well, this is how you start.

2:31You start by mapping out the entities themselves,

2:34and then you move onto the relationships.

2:37And the relationships inside of your database

2:39that you consequently want to draw out

2:41or model inside of your ERD are how the entities

2:44relate to one another.

2:45It's a little bit confusing at first,

2:47but here, let me try to explain.

2:48Imagine my entity, again, this is my customers table.

2:52And imagine I have my second entity, which

2:55is my orders table.

2:57Well, the way these two relate is

2:59that one single record inside of my customers entity

3:03can, well, have multiple different records inside

3:06of my orders table.

3:08And that's because customers can have multiple different orders.

3:11Now one order never has more than one customer--

3:13it's only a customer that has multiple orders,

3:16so we call this a one to many relationship,

3:19and we model that out exactly as I just did there

3:21with that pitchforked triad looking

3:23stick to a straight line from customers

3:25to three different little ticks here on the orders.

3:27That doesn't indicate that there's actually three orders

3:30for every customer.

3:31It just means that there is possibly more than one order

3:33for a customer.

3:34There may be zero orders for a customer.

3:36Maybe you're just tracking customers

3:38inside of your database that haven't

3:39ordered anything but only thought

3:41about ordering something.

3:42In which case, you would do something like this.

3:43You'd put a little zero on there and that

3:45says that it's one or zero to many orders.

3:49So you can have one or zero or two or three or however

3:52many orders that you have per customer

3:54listed in your customer entity.

3:56So we are modeling the relationship

3:58between the customers table and the entities table,

4:00and this can get really crazy really quick.

4:03I mean, here I just have customers and orders, right?

4:05Well, imagine we have something like this.

4:08Now I've got my customers entity,

4:12I have my orders entity, which is nothing more than a customer

4:16ID and a date, and then I have my order items entity.

4:21This might be a third table, and what does that got?

4:23Well, that's got the order number,

4:25the quantity, the price.

4:26And same thing, we've got one to many

4:29because one customer can have many orders,

4:31but each order can have many line items on it.

4:33So a given order might have multiple different line items.

4:37Here order number 17, which is that very first one,

4:40has one, two, three different items that that person ordered

4:43inside of that same order.

4:45It's like they added three items to their shopping cart

4:47on the website.

4:48So the customers to orders is a zero to many,

4:51but the orders to order items is a one to many.

4:53And the reason for that is because an order

4:55has to have at least one order item,

4:57otherwise it's not an order.

4:58So here is how we would map out or we would model these entity

5:02relationship diagrams.

5:03So that's just a very quick example

5:05of what a simple ERD might look like.

5:07Like I said, it can get complex quickly.

5:09This is one that I just searched for on the internet and found.

5:11Even this is not an overly complicated ERD.

5:14This is a pretty typical relationship diagram

5:16for a database that you might find in a production system.

5:18So, yeah, they can get really crazy,

5:21but they are very, very helpful and when it comes

5:23to understanding your database.

5:24I hope this is a been informative for you,

5:26and I'd like to thank you for viewing.

0:00Database normalization is one of those topics

0:03that you could spend hours talking about.

0:05Many, many college courses are devoted to exactly this topic.

0:09It can get incredibly complicated

0:10and incredibly in-depth.

0:11We want to avoid a lot of this inside of this Nugget.

0:15I want to introduce you to the concept of database

0:17normalization in the first three normal forms,

0:20give you enough material that you can go and research

0:22on your own and learn more about it if you need to,

0:24but hopefully you get a basic understanding of what it is.

0:27First of all, a paraphrase of the Wikipedia definition

0:31of database normalization.

0:32We are structuring a relational database in accordance

0:36with a series of normal forms in order

0:38to reduce data redundancy and improve data integrity.

0:43Let's just back up from that a little bit.

0:45So we'll go in reverse order here.

0:46Why do we normalize the database?

0:47To reduce data redundancy and improve data integrity.

0:51When you have the same data in multiple different places,

0:53there's this kind of Murphy's law for database programmers

0:56administrators that it's never going to agree.

0:58It's never going to be the same.

0:59To the extent possible and simple,

1:02you want to reduce the number of times,

1:04the number of duplications of a given piece of data.

1:06You don't want data redundant, over and over and over.

1:09You want it stored in one place.

1:11You also want to improve your data integrity,

1:13and that means making sure that, for example, orders

1:15don't exist for customers when there's

1:17no customer, no matching customer

1:19record in the database.

1:19Database normalization is intending

1:21to solve a lot of that.

1:22And it does that by structuring the database into normal forms.

1:26Now normal forms can get pretty complicated.

1:28There's 11 or so normal forms, I think,

1:31but they are progressive.

1:32And what I mean by that here--

1:33I've copied this from Wikipedia as well--

1:35is a database that is in, say, the second normal form

1:38is also already in the first normal form.

1:41If it's in the third normal form,

1:42it's in the second, which means it's also in the first.

1:44You get the idea.

1:45So it's progressive down the line here.

1:47You cannot be in compliance with a higher normal form without

1:50already being in compliance with all of the lower normal forms.

1:55One last thing to understand about normalization--

1:57and we're only going to talk up to the third normal form

1:59in this Nugget--

2:00is that the third normal form is typically what

2:02we call a normalized database.

2:04This is just by convention there's

2:06no rule that says third normal form databases are normalized.

2:09That's just when someone says, oh yeah,

2:10the database has been normalized,

2:12it means that database has been structured in accordance

2:14with the first three normal forms.

2:18So first normal form, each attribute

2:19has atomic data in it, i.e. you don't

2:22have any multi-valued fields.

2:23Take a look at this example table right here.

2:26Everything looks good until you get to the orders column.

2:28Now look at that.

2:29It's got dates, it's got order quantity,

2:31it's got order item in it, it's got all this different data

2:33inside of one column, inside of one field.

2:35This is bad.

2:37This violates the first normal form.

2:39A better version of this would look something like this.

2:41Here each individual point of data

2:44has been broken out into its own column.

2:46This is fairly common sense.

2:47If you've ever worked with spreadsheets,

2:49I'm sure that top example kind of gave you

2:51a little bit of a shiver.

2:52You would much rather see it in this bottom example,

2:54just for the simple fact of the matter

2:56that if you want to, say, add up the order quantity, now you

2:59can put a summation column at the bottom of that quantity,

3:01whereas here, how would you get the sum of the order

3:03quantities out of that column?

3:05It would be really hard and really difficult to do.

3:07So first normal form means that every attribute

3:09has atomic data.

3:11Every field has one and only one value,

3:14or one and only one piece of data in it.

3:17Second normal form, every non-prime attribute

3:20is dependent exclusively on the primary key.

3:23So taking that example we just looked at, now

3:25this is a bad example, because while it was reliable

3:27or while it complied with the first normal form,

3:29it now violates the second normal form.

3:32A better version of the above would look like this.

3:35We would split it up into two entities.

3:37Notice what's happened up here in the top.

3:38I've got Sam Jones listed twice.

3:40I have Joe Smith listed twice.

3:42What's going on up there?

3:43Is this the customer table or is it the order table?

3:46Which one does this ID, this unique ID, represent?

3:49It's difficult to tell.

3:51And we have a problem here, right?

3:52We could have Joe Smith listed at the top,

3:54and then his name could be misspelled down here.

3:56Is that a different customer, or is it the same customer

3:58with a misspelling?

3:59That duplication of data introduces the possibility

4:02of having bad data.

4:03So instead, what we'd rather do is simply have a customer

4:06table, a customer entity, and have a one to many

4:09relationship-- there's our entity relationship diagrams

4:11again--

4:12within orders entity and orders table.

4:14And those orders would simply have this customer ID

4:16field that would reference back to the customers table.

4:19And now we can see for every customer, the individual order

4:22date, the quantity, and each of these orders

4:24has their own unique identifier.

4:26So I know that the order number four went to customer number

4:29two, Joe Smith, on this date for 20 picks at a price of $12.5.

4:33So this structure here now complies

4:34with the second normal form.

4:36And then lastly, to ensure our database is

4:38perfectly normalized--

4:39I say that with a bit of sarcasm--

4:41we want to meet third normal form, where

4:43all the attributes are determined only

4:45by the primary key.

4:46This one can be a little bit difficult

4:47to get your head around, but I think the example will help.

4:50So again, I have my good example from the previous slide that

4:53complied with the second normal form

4:55but it violates the third normal form.

4:58Why is this bad?

4:59Well, look over here.

5:00Order item and order price kind of go together,

5:02but they don't necessarily go with order ID,

5:04because there could be another order down here

5:06that had widgets at that price.

5:08And what if this listed widgets at a different price?

5:10What would we be trying to say there?

5:11Was it a different product or was a different price given?

5:14So how do we make this better?

5:15How do we make this a compliance with the third normal form?

5:18And the answer is, we pull the order data

5:20out into a separate entity as well.

5:22So now I've got my customers entity,

5:25I've got my items entity, and then

5:28finally, I've got my third entity, my orders.

5:31And the orders simply use an item

5:33ID to reference the items here so that now I

5:36know that slips always cost $36, and if someone has item ID

5:40number one right here, I know that they have paid

5:43$36 for the slip, we can multiply that by the quantity

5:46if we need to, and learn what the total price of the order

5:48was.

5:49What have we done?

5:50We've ensured that all of the attributes

5:52inside of this table, inside of this orders table,

5:54are dependent only on the order ID,

5:57and everything else that is not is inside

5:59of its own entity, either customers or items.

6:02This database here is compliant with the third normal form.

6:06So that is the very quick rundown

6:08of database normalization.

6:09Some of it is common sense, I think.

6:11Some of it takes a little bit of work to get your head around.

6:13If you want to understand more, you

6:15can look up and research a lot about normalizing databases

6:17and what it means and why you do it on the internet,

6:19and I'd recommend that you do that if you're

6:21unfamiliar with it.

6:22I hope this has been informative for you

6:23and I'd like to thank you for viewing.

0:00If you are planning to work with databases

0:01in any capacity as an administrator

0:04or as a programmer or as simply someone who

0:06wants to analyze some data that's stored in a database,

0:08you're probably going to need to know about structured query

0:10language, SQL.

0:12If you are taking this Nugget course in order

0:14to pass the Oracle DB SQL exam, well, it's

0:17right there in the title, SQL.

0:19Obviously, you're going to need to know what SQL is

0:21if you want to pass that exam.

0:23SQL is a standardized language that

0:25was developed for interacting with relational databases.

0:28It's either pronounced SQL like I like to do or a lot of people

0:31pronounce it sequel.

0:32They're interchangeable terms.

0:34And it's pretty much assumed when

0:35you're talking about a relational database

0:37that SQL is the language that is used to manage the data

0:41and these structures, the things, inside

0:43of that database.

0:44SQL is a very standardized language,

0:46and it was developed expressly for managing databases

0:50or managing RDBMS systems.

0:52And because it was developed expressly for that purpose,

0:55it separates itself from what I'll

0:57consider traditional programming languages, things

0:59like C or Java or Python.

1:02Those languages are generally procedural,

1:04and a procedural language means each line of code

1:07is executed one after the other, right?

1:09It just walks down the line.

1:10If you type 10 lines of code, the interpreter or the compiler

1:14executes each one of those lines of code in order,

1:16starting with 1, then 2, then 3, and just like that

1:19until it reaches the end of the code.

1:21SQL, on the other hand, is mostly, although importantly,

1:23not entirely, a declarative language.

1:26This is a pretty esoteric description

1:27of functional languages and declarative languages

1:29and procedural languages.

1:31But the point here is that SQL is

1:33kind of interpreted holistically, the entire SQL

1:35statement.

1:36I've got an example of an SQL statement right here.

1:39And don't worry if you're brand new to SQL.

1:41It's meant to be fairly easy to understand, fairly

1:43legible for SQL statements.

1:45You don't really need to know how this works,

1:47but you can look at it and I think you can figure it out.

1:49We're going to update something called scores.

1:51We're going to set the home score to 35,

1:53the away score to 17, but only where the game number equals

1:5622.

1:57So I only want to update one game

1:59with this particular score.

2:00If this were a procedural language,

2:02the first thing it would do is it would say, OK,

2:04we're updating scores.

2:05Set every score to home 35, away 17,

2:07and oh, oh, wait, I wasn't supposed

2:09to do it to every score, just this one particular game score.

2:14So interpreting this command procedurally

2:16would be a problem, whereas SQL, this command

2:18is interpreted holistically.

2:20The entire command is looked at, and so the database engine,

2:22the SQL engine, is going to look at this and say,

2:24yep, I already know that I need to limit to game number 22,

2:27and it will never make the mistake

2:28of updating every game in the Scores table with that score.

2:32So it will look at and understand the entire language.

2:35Effectively we are telling the SQL engine or the database

2:39engine what we want to do.

2:41We're not telling it how to do it.

2:42We're letting the SQL engine handle that itself.

2:45We're simply telling it what we want to do

2:46and the how is really unimportant to us.

2:49And that statement that we just looked at, by the way,

2:52more or less conformed to the American National Standards

2:55Institute definition of SQL.

2:56There is actually an ANSI SQL, which

2:59is the formal SQL, the real--

3:02I'm making air quotes with my fingers there, SQL.

3:05Unfortunately, every RDBMS implements its own variation

3:09of ANSI SQL.

3:11It's kind of like dialects of a language.

3:13So every RDBMS system, whether it's

3:15Microsoft SQL server, My SQL, or, yes, Oracle,

3:18has its own little nuances, its own little tweaks,

3:20its own little variations from ANSI SQL that

3:24make it unique to that system.

3:25And there's a lot of different reasons

3:27that they do this, not really worth getting into.

3:29You should just understand that knowing

3:30ANSI SQL is kind at the beginning, is the start,

3:33and if know ANSI SQL, you're going

3:35to be able to read and recognize SQL from any of these systems.

3:38But being able to write SQL that works accurately

3:41in those systems is always going to require

3:44some specialized knowledge, just like being

3:46able to speak Southern American English is

3:48different than Northern American English,

3:50but you'll probably be able to understand anyone

3:52who is speaking either dialect if you know English,

3:55as long as you know English.

3:57Another important thing to understand about SQL

3:59is that it is intended to be written

4:01and then handed off to the database engine,

4:03to the SQL engine, to be processed.

4:05So you might have an application or a program

4:08that compiles and builds this particular SQL statement,

4:11and then that application simply passes that statement down

4:14to your database engine.

4:15And the database engine runs that code,

4:17it figures out the most efficient way.

4:18Like I talked about before, it's declarative.

4:21We describe what we want to do, the database engine

4:23handles the how.

4:24So it will figure out the fastest and easiest

4:26and most efficient way to accomplish

4:28what you've described, and then it will do that.

4:31It will also hand back a data if that's appropriate as well.

4:34So a simple SQL statement here called

4:36a select statement, because it starts with the word select,

4:39is a query, and this query is intended to return data.

4:42When you pass this SQL statement over to the engine,

4:45you are expecting a response.

4:47So whatever program or code you're working with

4:49to send this data has to also be programmed

4:52to handle a response of data, has to handle the results.

4:56And the results might be zero records,

4:57maybe nothing matches our game date of 1/1/2017.

5:01Maybe there was just one game that day.

5:02We might only get one result, or we

5:04might get two or three or however many results.

5:07We're getting a zero to many set of data in return from a query,

5:12and so whatever handles the results of this query

5:15has to be able to handle a collection of data

5:17that might be empty.

5:18It might have 1 row in it, or it might have 2 or 3 or 5 or 500

5:22rows in it.

5:23We never quite know.

5:24We have to be prepared for that eventuality.

5:26So those are the basic elements of SQL,

5:29structured query language, a way to easily and smartly

5:32interact with your database files in your database engine.

5:35I hope this has been informative for you,

5:37and I'd like to thank you for viewing.

0:00More famous than the siege of Troy, bloodier

0:02than the battle of Stalingrad, and more

0:04tragic than Tupac versus Biggie Smalls,

0:08it's ANSI SQL versus Oracle SQL.

0:11A battle between two titans who seek out

0:13to define what it means to be a structured query language

0:19for manipulating databases.

0:21What is ANSI Al and how does it differ from Oracle SQL?

0:24Let's take a few minutes to figure that out.

0:27ANSI stands for the American National Standards Institute.

0:30ANSI SQL was developed in 1986 as a way

0:33to standardize SQL as a programming language for use

0:37across any RDBMS, any relational database management system.

0:40It was last revised in 2011, so it's fairly up-to-date

0:44and fairly new.

0:45And most importantly, ANSI SQL is

0:47intended to be a vendor-m neutral baseline SQL language.

0:52The idea with ANSI SQL is that it is generic enough that it

0:56can work on any database.

0:57It is vendor-neutral, does not care who

0:59or which database is implementing it.

1:02Oracle SQL, on the other hand, was developed, of course,

1:05specifically by Oracle itself.

1:07It has been revised with each revision of the database

1:10software.

1:10Each revision of Oracle database has come with some new updates

1:13and changes to Oracle SQL.

1:15And Oracle SQL is not the same as ANSI SQL.

1:19Specifically, Oracle SQL has a lot of extra features

1:21that only work on Oracle.

1:23It is vendor-specific.

1:24If you really learn Oracle SQL, you

1:27are absolutely going to learn some things about SQL

1:29that will not work, for example, on MySQL, or on Microsoft's SQL

1:33server.

1:34So which one wins?

1:35Who comes out on top in the battle between the widely

1:37accepted standard or the power of some vendor-specific

1:41language?

1:41Well, the unsatisfying answer, unfortunately,

1:44is that they both win.

1:45Both of them come out on top, and there's not really all that

1:48much of a battle to begin with.

1:50Yes, Oracle SQL extends and expands ANSI SQL

1:53and is not always compatible with ANSI SQL,

1:56but the simple fact of the matter

1:57is that if you are learning Oracle SQL or really

2:00any vendor-specific SQL, you're kind

2:02of learning both ANSI SQL and that specific SQL

2:05at the same time.

2:06Take this SQL statement right here.

2:08You don't really need to understand how it works.

2:10It's pretty simple.

2:11What I want you to pay attention to

2:12is this statement right here, join and on.

2:17This is an ANSI SQL standard statement.

2:20It creates a simple query that selects data

2:22from two different tables and it joins those tables

2:25on a field called customer_ID and just ID.

2:29Now the Oracle version of this looks very, very similar,

2:32but it's slightly different.

2:34Notice there is no word join.

2:35It's just customers, comma, orders.

2:37Instead of the join in there, we just have a comma.

2:39And instead of the clause on, we have it embedded right inside

2:42of the where clause.

2:43We say where a customer ID equals ID.

2:46So these are obviously very similar.

2:47You can look at these and probably

2:49figure out that they are doing the same thing.

2:52They're just slight different variations on the same syntax.

2:54And actually, as of Oracle9i, Oracle

2:58supports either statement.

3:00So Oracle will actually implement

3:02ANSI SQL standards as well as Oracle-specific SQL standards.

3:06Throughout this Nugget series we're

3:07going to be using both pretty much interchangeably.

3:10It's important to know that you may be learning

3:12some Oracle-specific stuff, but most of what you learn

3:15is going to be recognizably standard ANSI SQL.

3:18And more importantly, for the exam,

3:20you will not need to know the differences.

3:21You don't have to identify ANSI SQL versus Oracle SQL.

3:25You just have to know that Oracle

3:26has their own unique implementations sometimes,

3:28and for the most part, this is the version

3:31that you need to know.

3:32The Oracle SQL is the version that you really

3:35need to know in order to pass the Oracle database SQL exam.

3:39Now there's this other thing that I

3:41mentioned briefly, or really didn't mention at all.

3:43And that's SQL*Plus.

3:44I don't know, it's like the worst name for a product ever.

3:47And it's compounded by the fact that it actually

3:49means two different things.

3:51Oracle SQL*Plus-- I think it's just pronounced SQL Plus--

3:54is both an additional set of commands for Oracle SQL.

3:57But it's also a command line utility for running SQL

4:00and SQL*Plus commands.

4:02SQL*Plus is both things.

4:03It has some extra additional functionality

4:06that they have tacked on to SQL that only works on Oracle

4:09databases, and it's a utility that you

4:12can run from a command line instead of the Oracle Developer

4:15graphical user interface.

4:16You can run from a command line to execute SQL statements

4:19against your database.

4:20It's both things.

4:21So you have to specify which you're talking about if you use

4:24the phrase SQL*Plus.

4:25Now again, for this exam you don't really need to know

4:28anything about Oracle SQL*Plus.

4:30There are only two elements of SQL*Plus that we're going

4:33to learn about, and that is the DESC command and something

4:37called substitution variables.

4:38Those will be covered in later Nuggets.

4:40Those are the only SQL*Plus-specific things that

4:42we are going to learn.

4:43Everything else is going to be Oracle SQL,

4:46and by extension, pretty much just the same as ANSI SQL.

4:48So don't worry too much about that battle,

4:50or what's the difference between ANSI SQL and Oracle SQL.

4:53If you're learning Oracle SQL, and that's

4:55what we're going to do, you are mostly

4:56going to also learn ANSI SQL at the same time.

4:59I hope this has been informative for you

5:01and I'd like to thank you for viewing.

0:00You can't have too much of a good thing.

0:01That's what I always say.

0:02Actually, I think that might be the first time

0:04I've ever said it.

0:04But regardless, the people who came up with and have

0:07developed SQL over the years agree with that sentiment.

0:10They believe that SQL can do a lot of different things

0:12besides it's original intended purpose, which

0:15was just to work with data inside

0:16of a relational database.

0:18And to that extent, over the years

0:20SQL has grown into a lot of different categories

0:23and a lot of different types.

0:24And we want to take a look at a few of those different types

0:27here inside of this Nugget.

0:28The primary purpose of these categories

0:30is going to be to help you understand what

0:33the different SQL statements do inside of the broader

0:36context of SQL programming.

0:38And also to help you understand how you can manipulate

0:40both your database, as well as the data inside of it,

0:43using SQL statements.

0:45So SQL, as we've talked about inside of another Nugget,

0:47is really the industry standard language

0:50for interacting with your relational database management

0:52systems.

0:53But the problem is that databases are complicated.

0:56You may not think so at first--

0:57just a database is a couple of places

0:59to house some data, some information, right?

1:01But of course those little houses, those tables

1:03themselves, you can have multiple different tables.

1:05They have individual fields inside of them

1:07with different data types and constraints and indices

1:09associated with them.

1:11The tables get grouped up into schemas,

1:13they get permissions assigned for users,

1:16you have to be able to manage access and control and flow

1:19and process inside of these databases,

1:21and-- all right, yeah, sure.

1:22My head already hurts.

1:24And SQL can handle a lot of this, so we break it out

1:27into some different categories so that we

1:29can see the different ways that it handles it.

1:31And there's a lot of different types of SQL out there.

1:33But the three that we want to learn about

1:35and we need to know about for the Oracle Database SQL exam

1:38are the three primary ones--

1:40DDL, the data definition language;

1:42DML, the data manipulation language;

1:44and TCL, the transaction control language.

1:48We can take a quick dive into each one of these.

1:51DDL, the data definition language, is just like it says.

1:55It's used to define the structures in the database that

1:58house your data.

1:59DDL is the SQL statements that are

2:02going to be used to do things like create a table

2:04or add rows to your table, drop tables, rename them.

2:07Even grant and revoke permissions

2:09is considered part of DDL.

2:12So there's a couple of different statements

2:13that allow you to build up and define

2:15the structure of your database without ever actually inserting

2:18any rows.

2:19Not inserting or reading out any data from your database,

2:22that's covered in DML.

2:23That's data manipulation language.

2:24DDL is the data definition language.

2:28So DML are the statements that are used to get data

2:30into and out of your database.

2:32That's things like select, insert, update, delete.

2:36Delete does not delete a table from your database

2:38if you have a place to house employees

2:40and an employees table inside your database.

2:42Delete does not get rid of that table.

2:44Delete deletes the data out of that table while

2:46leaving the database table itself intact.

2:49Drop is the statement that would get rid of the table itself

2:52because the table is part of the database structure.

2:55So DDL is used to create or remove that table.

2:58Delete is used to create or remove

3:00data inside of that table.

3:02DDL versus DML.

3:04The third one you run into less often,

3:06but it's actually a really important part

3:08of database transactions, and that is TCL,

3:10transaction control process.

3:13A lot of times-- and you'll see this over and over throughout

3:15this Nugget series--

3:17we are using our DML and our DDL inside of something

3:20called a transaction.

3:22We are giving ourselves the ability

3:23with a transaction to either commit the changes that we make

3:27or roll them back.

3:28And TCL are the statements that allow us to do exactly that.

3:31Commit and rollback are the actual statements

3:33that we talk about.

3:34And they control the process flow for DDL and DML

3:36so that we can test out, we can try out, a transaction

3:40or we can try out some data insertion.

3:42And if it doesn't work right, if it's not what we want to do,

3:44we can roll back the whole transaction to the point

3:47that the database was before we started.

3:49So the two statements, commit and rollback,

3:51that we use to control that process are considered TCL,

3:54transaction control language.

3:57So very quickly that is the three types of SQL

3:59that we are going to be studying throughout this Nugget series--

4:02DDL, DML, and TCL.

4:04I hope this has been informative for you,

4:06and I'd like to thank you for viewing.

0:00Hello, everyone.

0:01This is Ben Finkel.

0:02And in this Nugget, we're going to walk

0:03through installing the Oracle database on our local system.

0:07Now, Oracle makes this easy to do now with installers.

0:10In the past, that was not always true.

0:11But if you come up to this URL right here--

0:13I've got it onscreen, you can see.

0:15And I'm loading up a short URL as well.

0:18You can scroll down to Oracle Database 12c.

0:20That is what this exam is testing on, release 2.

0:23And you can see the different installers for whatever system

0:25you happen to own.

0:27Now, I'm installing on a Windows 64-bit system.

0:29So I'm going to download this top link right here, just

0:31by clicking the File 1 link.

0:32It's 2.8 gigabytes.

0:33It takes a little while to download.

0:35And what you get is a dot zip file.

0:38The installer for each of the other systems

0:40more or less follows the standards and conventions

0:42of installers you would expect on those operating systems.

0:45So you should be able to follow along with this,

0:47even if you are not using Microsoft Windows.

0:50And by the way, if you're concerned about licensing,

0:52you don't need to be.

0:53Oracle offers a developer license, free of charge.

0:56You can download and use these database files

0:59to play around with, educate yourself, and even prototype

1:02software without having to worry about paying for licensing.

1:05You are only charged for licensing

1:07when you want to actually deploy the database into a production

1:10system.

1:11Now, for Windows, when your file is finished downloading

1:14you'll see the zip file right here.

1:15And if you open it up, you can see

1:16that there is a database folder, which I have simply

1:19extracted out here into the folder where

1:22I downloaded my installer.

1:24And if we look inside of that folder,

1:25there is a setup.exe that we execute.

1:27And this is going to launch the Oracle database installer

1:31program.

1:32Funnily to me, it says we have to have at least 256 colors

1:35in our display, which is crazy.

1:37I can't imagine any display that doesn't have 256 colors.

1:41Now, at this point, a lot of steps

1:42are going to take a while.

1:43We'll walk through the initial setup.

1:45And then Oracle will start installing.

1:47And it can take a long time.

1:48It's a big software product.

1:50Unfortunately, you can't walk away from it

1:52and just leave it go, because a lot of times,

1:54Windows access control is going to ask

1:55you to validate some things, or allow it to install.

1:59So it's kind of an install process that

2:01doesn't require much from you, but you

2:02do have to sit around and babysit it on your screen.

2:06The first thing they'll ask you to do

2:08is sign in with either your Oracle Support username

2:11and password.

2:12If you don't have an Oracle Support account, that's OK.

2:14Just uncheck this box and it'll create a guest account for you,

2:18so that you can still get any important security

2:20updates downloaded over the internet in the installation.

2:24Now, depending on where and under what

2:25circumstances you're installing it,

2:27you have a couple of different options here.

2:28We're going to choose the first one,

2:30the default-- create and configure a database.

2:32This will create a basic database for us

2:34that we can start working in right away.

2:36If you already have a database or you want some more advanced

2:39installation options, you can install the database software

2:41only and work from there.

2:43But like I said, we'd like to choose the default option

2:46there.

2:47You can also choose if you're installing this on your desktop

2:49or on a server.

2:51The desktop is going to install everything

2:52with all of the client tools that we want pre-installed

2:55and configure a lot of things for us.

2:56It's definitely the best way to go for playing around.

2:59Obviously, in a production installation,

3:01you're going to want to install the server class.

3:03I'm going to use a virtual account.

3:05This is the account that Windows will execute the Oracle

3:08database processes in.

3:10So that's what will have the different permissions

3:13and the different ownership over the different objects

3:16inside of the operating system.

3:18At this page here, it's going to default

3:21with some folders and some file locations

3:23on your system to install into.

3:25You can change those around.

3:26I'm going to leave mine as the default option here.

3:29The really important things, right here,

3:31is this global database name and the passwords.

3:34This is going to be the initial database.

3:35It's ORCL.

3:36That's really important.

3:37Write that down-- ORCL.

3:39Or if you change it, write down what you change it to.

3:41And the password-- this is also really important--

3:43this is the password for the system

3:46account, the administrator for that database.

3:48And you're going to need to remember that to connect

3:50to the database the first time.

3:52There are no users that you can connect to,

3:54other than the system.

3:55So you have to know that it's the username system

3:58and the password that you chose there during installation.

4:01Before it actually executes the install,

4:03it gives you a sort of summary overview

4:04of the different options and what it needs to do.

4:07You click the Install button, down here in the lower right,

4:09and it begins installing.

4:11Again, this is going to take a long time--

4:1320 minutes, maybe half an hour.

4:15And it is sometimes going to ask you to accept Windows access

4:18control.

4:18So you do need to babysit it a little bit.

4:20But you don't have to pay super close

4:22attention to what it's doing.

4:23And once that's finished it is all installed, including

4:25the database-- the sample database

4:27that we asked it to install-- and the client tools.

4:29We can close this.

4:30And the easiest way to check it out and see what's going on

4:33is to simply launch a tool called SQL Developer.

4:37SQL Developer can be found in your Start menu, right here.

4:40It's under my "Recently added," because I just installed it.

4:43There is the SQL Developer tool, so we

4:45can launch that very easily.

4:47So there is the Oracle database and client tools

4:50installed on our local system.

4:52I also want to show you something called Live SQL.

4:55This is found at livesql.oracle.com.

4:59And Live SQL is a simple virtualized database

5:03that you can access through a web browser

5:05right here at this URL.

5:06If you are not in a position to download and install

5:09Oracle tools on your local desktop,

5:12this might be a very good way for you to walk through and try

5:14out all of the different things that we are discussing inside

5:17of this Nugget series.

5:18I'll be honest-- I have not used this a lot.

5:20But I believe that it will be able to accomplish everything

5:22that we do.

5:23Every demonstration that I perform on my local system

5:25with the local Oracle tools can be replicated here

5:28in Oracle Live SQL.

5:30Well.

5:31So keep that in mind.

5:31You simply launch the SQL worksheet.

5:33And this is going to allow you to key in SQL, see the output,

5:37and access it and understand how it

5:39works without having to have anything installed

5:41on your local system.

5:42So that covers the installation of the Oracle database

5:45software.

5:45I hope this has been informative for you.

5:47And I'd like to thank you for viewing.

0:00Well, we've talked and talked about databases.

0:02We installed the Oracle Database and client tools

0:04on our machine.

0:05Why don't we take a moment and actually write and execute

0:08some SQL statements we'll see reflected in the database using

0:10the Oracle client tools.

0:12I'm going to do this from my local desktop machine here.

0:15You can see I've got the Oracle SQL Developer tool opened.

0:18This was installed as part of the database package

0:20that we installed in my Nugget on installing Oracle software.

0:22Go back and watch that if you don't have this.

0:24You should be able to also more or less follow along

0:27with what I'm doing here in the Oracle

0:28live website in the virtual server

0:31that they create for you.

0:33To begin with, I have my ORCL connection open.

0:35And why don't I right click and delete this connection.

0:38We'll just recreate it real quick

0:39to make sure we know what we're doing there.

0:41With connections highlighted, I'm

0:42going to click the green plus over here,

0:44and I will give the connection a name.

0:46ORCL is fine.

0:47My username is going to be system.

0:48I don't have any additional users in this database yet.

0:51Type in the password.

0:52That's a password that I chose when I installed the Oracle

0:55software and when this database was configured,

0:57so I did that during the installation process.

0:59And the SID is ORCL, that was again the default service

1:03name that Oracle installer chose for me.

1:05I could have changed it, but I'm going to leave it as is.

1:08Click connect, and now this connection is over here

1:10on the left hand side.

1:12Why do we have to create a connection

1:13if the tool in the database or on my local PC?

1:16Well in a normal situation, in a normal scenario,

1:21you're not always going to have the database

1:23and the tools in the same PC.

1:24A lot of times a database will be out on a server

1:26somewhere else on the network, and there

1:28will be multiple databases that you might connect

1:30to that will be the production database and the development

1:32database, and maybe a test database as well, plus there

1:34will be different products, so there

1:36might be a database for your HR system

1:38as well as a database for your retail front end web page,

1:41and they might be separate databases requiring

1:43separate connections.

1:45So this allows you to use this client utility to connect

1:47to those different databases just by selecting them

1:49on the left here.

1:50If I expand that connection, you can see that there's already

1:53a lot of different things here.

1:54For example, if I expand the tables window,

1:57there's a whole bunch of tables already in my database.

1:59Most of them are labeled log, but these

2:01are all databases, or all tables rather,

2:03that the database uses to support the function

2:06of the database itself.

2:07We don't really want to work with these tables directly.

2:10The system will do that.

2:11The database engine is going to do that.

2:13Instead, why don't we create our own table?

2:16I'm going to do that right over here inside of my worksheet.

2:18I'm going to type create table and give it a table name.

2:23Mine will be customers.

2:24From there, I'm going to open parentheses,

2:26and I will type F name varchar open paren 20.

2:32And by the way, I'm just asking you to type right along with me

2:35exactly what I type in.

2:36You don't need to understand it yet.

2:37We are going to spend a lot of time going more in depth

2:39on each of these commands and what these different statements

2:42mean.

2:42For now, just echo back what I'm typing in

2:44so that you can see the same results that I'm seeing.

2:47Comma L name varchar open paren 20 comma,

2:53let's go state space varchar open paren two,

2:59and then close that original parentheses that I

3:02opened at the very top there.

3:03So something like this right here.

3:05Now this is an SQL statement.

3:07Specifically it is a DDL SQL statement.

3:09How do you know?

3:10Because we're using the create command.

3:12You can see it right there, the very first word.

3:14There is my create command.

3:15What am I creating?

3:16I'm creating a table.

3:17Notice that the SQL developer tool highlighted in blue

3:20so I know that those are keywords, the reserved

3:22keywords for SQL.

3:23Then I indicated my table name.

3:25I have an open enclosed parentheses,

3:27and in-between those I've listed my three columns, my three

3:31fields that I want inside of my table.

3:33And it looks like state is highlighted in blue.

3:35That's probably a reserved word.

3:37Instead of state let's go st. Now that'll work.

3:39OK.

3:40Now that I've got three columns and the create table statement,

3:43I'm going to click this green button right here.

3:45That's the run statement.

3:46I can also hit control enter as it tells me.

3:49And that executed and created the table inside

3:51of my connection over here.

3:52If I expand tables I don't see it yet,

3:55but if I right click the tables folder

3:57and simply say refresh, open it up,

4:00now there is a customers table.

4:02And if I select it, I can see it has three columns in it.

4:04F name, L name, and state.

4:06What did I do here?

4:07I created a table with three columns.

4:09You can think of it as like a spreadsheet that has an A, B,

4:11and C column, except they're not named A, B, and C,

4:13they're named F name, L name, and ST.

4:16Next I would like to insert some data into that table.

4:19So I'm going to get rid of this and I'm

4:20going to go with insert into customers

4:28values, open parentheses, and let's say

4:32Ben in quotes Finkle in quotes, a comma, and then NY,

4:38because I live in the state of New York.

4:40Close parentheses.

4:41So very similar structure to the DDL statement,

4:43the create table statement that I just made.

4:45I have to put a semi-colon on the end of it.

4:48Now I am calling a DML statement, a database markup

4:51language statement, because it's an insert statement.

4:53And what am I inserting?

4:54I'm inserting data.

4:56Specifically I'm going to insert one row into my table

4:58with a value of Ben in the F name field,

5:01Finkle in the L name field, and NY into the state field.

5:05We can execute this as well with the green button,

5:08and that has been inserted.

5:09And finally we can take a look at that data.

5:12If we call select, F name, comma, L name, comma,

5:18ST from customer's semi-colon.

5:24One more DML statement.

5:26This is a select statement, also called a query.

5:28I went to see these three columns from the customers

5:31table.

5:32I'll click run.

5:33Down here in the bottom I get a new window opened

5:35called the query result, because the select statement returns

5:37data, and there is a row, Ben Finkle NY,

5:40returned from my table.

5:42So what have we done?

5:42We have created a table.

5:43We've inserted a row into it, and we have queried that data

5:46out of it.

5:48Pretty simple, straightforward, and easy utility to use,

5:50and it's pretty simple and straightforward

5:52to write these basic SQL statements.

5:53I hope this has been informative for you,

5:55and I'd like to thank you for viewing.