Learn SQL in 1 Hour - SQL Basics for Beginners

Learn SQL in 1 Hour – SQL Basics for Beginners

A crash course in SQL. How to write SQL from scratch in 1 hour.

In this video I show you how to write SQL using SQL Server and SQL Server Management Studio. We go through Creating a Database, Creating Tables, Inserting, Updating, Deleting, Selecting, Grouping, Summing, Indexing, Joining, and every basic you need to get starting writing SQL.

TABLE OF CONTENTS
Skip Intro/Discuss Management Studio 3:27
Create Database 5:10
Create Table 7:26
Insert Data 10:29
Select Statement 17:43
Where Clause 19:00
Update Statement 22:25
Delete Statement 24:33
Adding Comments 27:41
Adding Columns 29:33
Drop Table 32:38
Add Primary Key 33:17
Create Products Table 36:00
Create Orders Table 38:54
Foreign Keys 45:37
Joins 50:30
Functions/Group By 56:50

READ THE ORIGINAL ARTICLE WITH SQL SCRIPTS HERE

YOUTUBE NEWS UPDATES

VISIT SQLTRAININGONLINE.COM FOR TONS MORE VIDEO NEWS & TIPS

SUBSCRIBE FOR OTHER SQL TIPS AND NEWS!

SUBSCRIBE TO OUR EMAIL LIST!

LET’S CONNECT!
Google+:
Facebook:
Twitter:
Linkedin:
SQLTrainingOnline:

hey guys joy blue here today I'm putting together a training that will teach sequel from the very beginning to give you basically some knowledge that you can build on and use other training videos and go search after this so this should give you a good framework I'm setting this up to be hopefully around 30 to 40 minutes so that we can you can get as much as possible out of that time and so here are a few of my contact information on this first slide if you need to get a hold of me if you want to find other trainings that I have out there cuz I have some other videos hanging around so anyways let's get started so the first thing I want to do is give you an overview of this training a quick we're gonna have a quick introduction of sequel then we're going to go straight into creating a database and table we're going to insert some data select some data we're gonna do some where clauses update the data delete it and then take the table and make some modifications to it I'm going to teach you about the primary keys talk a little bit about index create a few more tables and then we're gonna start joining some tables up and get into a couple of advanced features like functions and group buys so really if you learn these techniques you'll have what you need to get started to build on you can start working with this so let's start with just this definition of sequel so sequel stands for structured query language and really you can think about that like PHP c-sharp those are other Java there are other examples of programming languages SQL is the programming language used to talk to databases so what types of databases are out there we're going to be using sequel server in this tutorial sequel server is one of them that uses sequel we have Oracle it's been around a long time very popular in corporations we have my sequel which is an open-source one we have post-grad sequel we have sequel Lite db2 and a lot of the big data structures are starting to standardize on sequel also so if you learn SQL and the techniques I'm going to teach you you'll be able to advance and use it in some of these other newer technologies also so you need a tool to write this sequel and we're going to be using sequel server management studio it comes with sequel server there's also people are breve 80s SMS and then there's Oracle's tool which is called sequel developer you also have sequel workbench which which comes with my sequel there's a pert one that people purchased that they like is called toad and there's quite a few others and those are the ones that I've worked with the most and when you're in a corporate environment you work with a lot of those ok so that's the introduction we're gonna go straight into sequel server management studio and I must start teaching you how to do this sequel stuff so I have just clicked on the sequel server management studio tool and I opened it up and this is the first screen I come to it wants me to connect so sequel server is a server and it wants me to connect to it and the normal way you would connect is by typing in localhost or you can just simply type a dot which you see there or sequel server has something called named instances so I have multiple sequel servers installed on my machine and the one of them connect to is called SQL 2016 there's lots of tutorials out there to show you how to get one of these connected up and installed and so I'm not gonna go through that right now because I'm trying to teach you as much as I can in a little amount of time so I'm just going to go ahead and connect up when you do that in management studio you end up with something called the object Explorer and so you can see up here that I am connected to my sequel server that is called sequel to 2016 then you see a tree of things going on here we're going to touch a few of them today the main one I want to talk about is this databases so if you expand that there's some system databases those are automatically installed and they come with the installation we don't need to do anything with those today there's database snapshots and that's a newer feature don't need to do anything with those today and then you have all these other databases that I've already created and we're going to create our own database today so I'm just gonna click on databases I'm gonna go to new query and so that opens up your query analyzer window right here and we can start typing in something the first thing we need to do is say create database we gotta give it a name and so we're going to call this the customer database so in order to run that and I didn't show you that but I clicked f5 you can actually come over here and there's this execute button right there and see how underneath the executor says f5 after you do this a lot you'll start hitting f5 every time instead of clicking that button and so basically what I did is I hit f5 we've got a customer database but notice you don't see it over here and that's because this tree of folders has not refreshed so I'm gonna go to databases right click and go down to refresh and now I have a customer database so that was step one let's create in a database the database holds all the tables so now we need some tables to put data into so I expanded the customer database and there's a bunch of other folders in here and what we want our tables it comes with system tables which we don't see any right now file tables and external tables what we want to do is create our own user tables but before we do that we need to make sure we're in the right spot so we open this query window from just the databases folder and there's this area right here that says master let me show you what that's connected to if I go to system databases it's connected to this database we don't want to create a table in that database so there's a couple ways to change over to the customer database and so one we could just hit this drop down and you can see customers in there and we could switch to it but there's also this other thing where you say use and when you say customer if I go over and run that now look at switch that over to the customer database so now we're in the right spot let's create our table so we're going to do what's called a create table statement so we say create table and we're gonna say customer and so that will create a table and within tables we have columns so let's go ahead and put some parentheses in and I'm just tabbing out to four so we can read it better and I if we're gonna have a customer table we're probably gonna want a first name and then we have to put in what type of data goes in that column so in this case I'm gonna put it in a varchar' and then how long should that column how much is the maximum that we want to store and I'll put in 50 so that means we'll have a first name column and it can have up to 50 characters and we're gonna want more than one column this column sum up at a comma hit a carriage return I'm gonna do last name and that's going to store characters also not numbers the character someone put a varchar' and I'm gonna put 50 and then let's store their age okay and so age is going to be a number and it's not a decimal number where we're gonna have a decimal point and store digits it's just going to be an integer so I'm just going to type int and so now I've got three columns and so we can simply run that and down here so whenever you run something I haven't said this yet but there's a messages area at the bottom this messages area tells you if it runs successfully which in this case it says command complete it successfully so we know that the table was created so let's look back over here to the left and our object Explorer at the tables and let me refresh that we'll give it just a second here see if it finds our table and so there's our customer table now once we have a table we can right click on that table and do a few things and so one we can edit which we can put in we can cheat basically and put in some extra data and two we can select the top one thousand rows so if I do that it's gonna write a select statement for us but notice down here in the results area we don't have any data so we have to put some data in so I don't want to cheat I want to teach you guys how to actually do an insert statement so let's do that next I'm gonna keep this tab because I want to keep the scripts and you can actually even in here you can save the scrip so I could do a file and save as and save the scripts so I can bring them back up later in this case I'm just going to go back to my customer database and I'm gonna go up to this new query button click that and I've got a brand new query and then I'm gonna notice that the database contacts this customer so now what I want to do is what's called the insert statement so insert inside this table I click the plus and go down into columns I can see my column names so our first-name lastname age so when I do an insert statement there's multiple parts for it so I say insert into and that's how you start it and we're going to say the table name if I say customer you can see that well that's actually the database see how that says database right now and that's not what we want we already know we're in the customer database and so really we could say if you notice over here says dbo customer we can say DB ODOT customer now that should help us out but inside of management studio has this thing called database cache or what it does is it gives you a intellisense and it needs to be refreshed so let's go down into a-tellin sense so I might edit intellisense and says refresh the local cache and some will click that now when I go back and I type B Bo dot now it knows customers there and that's because we created that table it didn't know that it was there yet into that cache so we say insert into and then after that you want to say the columns you want to insert into and you can type them so I can say first name and see how it finds that if I hit the down arrow then I hit the tab key it'll finish that up for me and I can say last name I can go down arrow to select it hit the tab key you can also use your mouse if you want and I hit comma or I can go over here and grab age and drag it on there now notice when I drug it on it put brackets around the column what brackets do is allow you to have like a space in the name of your column and you put brackets around it and it would still work in this case I did not put a space in between like first name if first name was like that let me spread that out if first name was like that then we would have to put these little brackets around to make it actually work but it's not like that so let's go ahead and take that back to where it was okay so we said insert into table and we don't really need that DB I'm gonna take that off to simplify it and then we said the column names then there's this thing called values so you just type the word values and then we're going to put two more parenthesis and so for the first name won't fit Joey now whenever you put a string in inside of sequel server and most of the sequel languages you'll want to put a tick some other sequel not sequel server you may put a double quote in this case you put a tick that signifies I'm putting a string in there so now last name up at blue comma and then age I'll put 40 notice I'll have to put a string in there for the age now whenever you're working in sequel server you don't have to put this in and you didn't used to be able to put it in but nowadays this good convention to go ahead and put a semicolon to end your statement let's run this so I just executed it and it said one row effected so we should have one row in the table so let's go back over here to the customer table and say select thousand rows and there we have somebody in there me okay so let's add a couple other people in there so we have more data what I'm gonna do is take this statement or ctrl C copy it you can also right-click and go to copy I'm a ctrl C I'm gonna copy it I'm gonna come down ctrl V control V control V control V I am going to shrink this down a little bit so we can see more of these there we go so get them all on one page here and at this point what I'm gonna do is I'm gonna change some of these names Barry Bonds will make him 50 and then who else we want Mike Schmidt these are baseball players oops I didn't do that right take that out push mitt here and then what I'm gonna do is actually to speed this up copy that and I'm just going to put and put some numbers whoops let me grab this again and we'll just dump me some data so Mike Schmidt's gonna be here multiple times we're gonna have him as one two three four five okay so notice how this insert kind of got cobbled into the same line now what I want to show you is you can either run the whole screen worth of stuff by just hitting execute or you can highlight what you want to run and execute it by itself so if I run that it says one row affected if I grab these two and I hit execute now it shows two times right I'll go ahead and scroll down and grab the rest of these and run them and notice how I didn't put a new line on these the semicolon says we're adding we're stopping the statement so let's see if this runs so it's still run so the semicolon in nozbe knows that we're starting a brand new statement so now if we go to the customer table select the top 1000 we've got some data and I should have changed their ages that would have helped okay so let's go back to our agenda make sure that I'm covering everything so we had the quick introduction of sequel we created a database in a table we insert some data what we need to now is select it back out so we cheated and we use this you know over here we right clicked and it created a select statement for us okay but I want to not cheat and I want to start from scratch so you can learn it so I'm going to go back to my customer database I'm gonna select a new query we got a blank screen so the basics of selecting data out is what's called the Select statement so you say select and then you can say star that basically means give me all the columns then you can put I'm gonna put a carriage return you don't have to you can say from and then you say what table you want I'm gonna say customer okay that's it now all we have to do is execute it and now we've got data so let's take it a step further and instead of star you can actually specify the columns I can say first name and notice how management studio finds that for you comma last name comma H so I just selected all three columns I'm gonna hit f5 this time and I've got the data out so you can also not specify all of them and we can say we don't care about age we can run that and we got just the first two columns and what I want to do though is I want to put on what's called the where clause because we're about to use that for an update statement so I'm gonna go ahead and grab H and run it and I want to grab all of the Mike's I want to grab actually I want to grab first of all everything that starts with Mike so to do that I'm going to put another carriage return and I'm gonna say where and that's the where Clause the where is where you filter and you can filter on any column that you have in there we're going to filter on the first name and so I typed the column name and I say equal to and I say Mike now again Mike is a string not a column name but it's a string so we have to put ticks around it so that it knows it's a string what if we don't let's try it without real quick I'm gonna hit f5 in Bali Colony thinks Mike's is a column but no it's a string that we want to compare to so let's go back here hit f5 again and now we have everybody whose first name starts with Mike okay so next up I want to just get the one that has Schmidt one so now I can use an and operator makes a and and I can go after the last name I can say equals Schmidt one so if I hit a five and run this I'm down to one row okay and that's the basics of the where clause and we can do some other really cool stuff like we can say we're last name like and you know with the like operator you can put in wildcards so I can say Schmidt and I can put in a percent and that's going to give me everything that starts with Schmidt and ends with anything else so let's go ahead and run that and notice how we we picked up Schmidt without a one on it well there's a secondary one that's an underscore let's see what happens with that we lost that Schmidt down here the underscore means it has to match some character and then the percent and it didn't have to match anything and so there's uh there's other stuff like between and we can get into more that if we have time so next I want to talk about an update so if I think this Mike Schmidt actually this go to me so let's query if I highlight just this top part and hit f5 then I've just run the top part without the work laws and then you can see me in here so I'm going to type that Joey blue I'm gonna say equal to and I'm gonna run that and that's got me so now let's switch over and say if I'm not 40 what if I was 20 again let's update my age so to do that let me just semicolon and I'm gonna come down here and I'm going to write what's called an update statement so the update statement says you start with update and then you tell what table you're gonna update so you say customer and then on the update statement you always want to put a where clause unless you're trying to update the whole table which isn't usually the case you put in a where clause where and since we already did the Select I'm gonna grab this right here and drop it in there but now we have to say what columns we want to update and I want to update age and we use what's called the set statement so you say set age equal to 20 there's semicolon let me run that so that just update it I'm gonna highlight just the whole table again not do the work laws up here run that and you can see now I have on page 20 and if I want to change it to 35 I just set the age to 35 I rerun it and my age is 35 now what happens if I forget the work laws I just do this yep and that's gonna update the whole table let's do it I've dated 8 rows that's a problem let's go look at the whole customer table so everybody is 35 now so I don't like that so now let's go into what's called a delete statement so we've got data here that's all jumbled up and I just want to delete the whole thing I'm gonna start a brand new query I'm gonna say delete and I'm gonna say customer I'm will forewarn you the delete statement without a where clause is delete all the data in the table so this can be very fatal if you do this on accident so I'm gonna do it eight rows were affected now that didn't delete the table the tables there there that deletes the data in the table if you want to delete the whole table you'd hit draw you type drop and that would drop the table this deletes the data so if I come back over to this one note I'm going through my tabs now to go back between scripts and I run just that script there's no data in there but luckily I just took out my insert statement I can come over here and I can update some ages to Mike Schmidt six now and then let's go ahead and change some ages here 61 62 63 64 65 okay so we gave him all kinds of ages so let's rerun this this time I'm not going to highlight anything and I'm just going to execute it and see how it went ahead and and ran all those let's see if it all those rows went in there and those rows are in there so that were inserting we're updating we're deleting all kinds of good stuff and remember when we did the where Clause where we said Mike and then we said like Schmidt I'm gonna go do that again and I'm gonna use the underscore I'm gonna get all those that have numbers on them and let's just delete those how's that sound so I'm gonna copy that where clause I'm going to my delete statement get rid of my semicolon and I'm putting the where clause in there so now I'm gonna delete all customer records where the first names Mike and last names like Schmidt but it includes an extra character in there so let's highlight that and let's execute it that got rid of five rows let's see if it got really rows we wanted to get rid of boom so now we're sitting there with the three rows that we should have had from the beginning so now we've run that delete statement so let's go back and check out our agenda see how we're doing we're almost thirty minutes in and we've done the quick introduction created the database create the table insert it select it we did a few where clauses we updated the data we deleted the data now we need to change and add some columns so let's go do that so in here we have first name last name in age what else should we add to that table I think we should add city so before I add city I do want to tell you something about comments as you're creating scripts you you want to put a comment in there and say this deletes Mike Schmidt okay so when I say that see how it turned green well this is a one-line comment if I go to the next line and start typing it's no longer a comment and so a lot of times you'll put this at the top of a script but then you can also have multi-line comments where you put a slash in the star and then the comment remains until you end with a star and a slash I do this around delete sometimes when I'm testing things so that you can highlight in case I come over here and I just run something so let me just run so execute it says command complete successfully but nothing ran okay to prove that I want to go in here I'm gonna highlight all those and run them now we should have our Mike Schmidt's back and I'm gonna come over here I'm just gonna hit f5 and say complete successfully and we still have our Smits so I can't accidentally delete something but I can still highlight it hit f5 five rows are deleted and now we're back to know Mike Schmidt's so that's the commenting so now let's go ahead and add our column and we want to add city so I'm gonna do another new query to add a column you can say what's called alter table and we say customer then we can say add and you tell the column you want to add I must say city and city is just going to be another string of stuff so I'm just gonna say varchar' 50 so if I f5 commence complete successfully so let's go back over here execute our query and in this case notice how I don't see city but it also knows how I specified the actual columns normally when I write queries initially I just put a star I'll go back in here and do star and there's our city and notice how nothing's in it we didn't insert data into it yet so we can go ahead and we know how to do an update so let's do an update to the city column notice how city isn't showing up in our intellisense we can go back here to edit intellisense refresh local cache and now when I type city in it should show up and so I just want to say city and I want to put dot and let's put let's put New York okay and I don't want to just do Joey blue so I'm gonna take that off and that's just update and see what happens so if I come back here and select from customer there you go they're all New York now if we just want to update one we could easily go back and hit control Z I use this a lot that's an undo there's also undos up here and if I want to just update my city Goddard it updated one row if we go back now Goddard is updated you can also if we want to update Barry Bonds we can update him san and Cisco I know I'm not spelling that right yeah I need to put a see here some update it one more time so here where I misspelled it I'm updating the same record with setting city to San Francisco I'm a rerun the select f5 and San Francisco ok so now we have our customer table so the next thing we need to do is how I need to add one more column so let's go add one more column and I'm going to add a primary key column ok so what I want to do after all that work I want to go ahead and drop this table so you see how you can drop the table so if I type drop table customer come back hit f5 and then I try to select from that table it's no longer there so we have lost our table so if you all kinds of queries in here my and this is if you name them that kind of helps out so let's start with the first one or a create table and this time I'm not just going to have age we'll go ahead and put the city in let me know that's a bar char 50 and then I want to go ahead and put a customer ID and I'm just going to call it ID and it's going to be integer and I'm going to do couple clauses here I'm gonna tell it it's the primary key so the primary key is means that that column by itself will uniquely identify a row so if two people have the exact same first and last name and age the ID will identify them so that's going to be unique and secondly in sequel server omlette you can say identity it's an identity column and you want to start with one and increase by one every – every row so now that ID column what we'll fill in for itself we don't have to fill it in every time you insert a record it will fill that in put a comma there I'm going to recreate this table by hitting f5 and now that's there so let's go back and so lect now we have ID first name last name age and city let's go insert our records again see if I can find them and now what we want to do is I want to add city here because now we're going to add city in and then we want to put in Mike Schmidt and let's just say Kansas City again I'll put KC for short okay so now I need to do that with all the queries just drop those in there drop those in there so I'm hitting ctrl-c and ctrl-v which is something you'll want to use a lot for this for anytime you're programming we'll put goddard we're gonna pass and and that's go okay so I'm gonna insert those three rows done let's go back and select them so we've got those back in now notice when it inserted I did not put in the ID but it went ahead and automatically increment it okay so now what we need to do is this is our customer our customer is going to buy products from us so we need an orders table and we need a products table so let's create a products table so we're gonna do a new query say create table product and we're gonna want a product ID and I can just say ID again and I can say int primary key and I can say identity so that's going to give me an and the primary key identity column and we could say product underscore ID some people do it that way some people just say ID we're just going to put ID right now and then all I want is a product name for this training so I'm just going to say product name and I'm going to say varchar' 50 again okay so now we've got some products and let's go down here and select from them okay so now we need to insert into them so when we say insert into products we'll give the column product name values and then we need to give a product since we're talking about baseball players I'm going to say a baseball and then and now I'm thinking about it we probably one more thing in here and so I'm going to alter this table products and I'm going to say add price we may want a price on it and in this case the price is going to have decimals in it so we can't just be an integer so we're going to say in in sequel server you can have floats and numeric and decimals and we're just going to do a float and that's going to give us a number that can have floating point decimals so I'm going to put that in there select back from it and there's tossing a price and so baseball we're going to put at 595 notice how we don't need ticks around it because it is a number and instead of baseball let's go bat that's probably like 195 99 okay you put rice here and so let's go ahead and insert both of those rows highlight them in f5 let's select back from the projects table so now we have some products and then let's go ahead and do and I'll just keep going down this page great table order and inside the orders table we're going to need to put in who bought it and what the order was so we're going to put in the customer ID it's an int and then we're going to put in the product ID miss an int and then we need an order ID in this case I'm just going to put ID I'm put order ID so there's two different ways to different rules of thought there if you have the same columns that say ID you know that that can be good for some things sometimes you want to put order ID I'm put order ID int and we'll put primary key and I'm going to put identity 1 comma 1 okay and then let's go ahead and put order date in here give you give you a date type of variable so here we're gonna do an order date and if there's a date time and that will store a date and a time all in one place okay so now we can order there's a date of the order there's the ID and the product okay run that now let's select from the order table okay so we got the table now we need to insert some data into the table so let's go ahead and do a few insert statements before we can do that though we've got to select some data out and figure out what data this is where you when you write your interfaces your web interface or your your programs they'll do all these inserts we need to see what IDs to put in there so I want to select from all of our tables select star from products and select star from customers okay it must just be customer sorry ok notice how I ran all three of those queries together and so I got three result sets we got the orders here we got the products here we got the customers here so let's go ahead and what I'm going to do is actually well let's create one of these and then I'm going to just insert a bunch of orders so if we want to insert into the orders table we say insert into and into is an optional keyword you don't have to put into in there and it would still work I say orders and then just like everything else we're gonna put the columns we don't need order ID when I say order date I say customer ID or say product ID and then we say values and order date I want to put in nine actually I'm gonna use something called the get date and this is specific to a sequel server it'll get the date right now I'm so you'll see exactly when I'm making this training so forget date in there and then we want the customer ID let's have Barry Bonds if you look down here his ideas number two so I'm put two in there and then he's gonna buy a bat then we'll put two in there okay so let's run that and let's see what's in the orders table so on September 13th customer to purchase product to and let's insert a few more things so let's go ahead and do product one and let's do customer one product one and let's have customer one keep buying a few products and then let's have customer two or its customer three and the reason I'm just remembering what these customers are here so let's pull these back so you can see we've got a whole bunch of orders now and let's see customer three does have Mike Schmidt bison bats so he's gonna buy some bats um uh put a bat is Heidi to remember product I these two here okay so let's buy some bats he's gonna buy a bunch of that so I'm sitting at five a bunch of times okay so now we have some orders so we have order IDs and the way way we designed this may not be the best because one order can only have one product to purchase so not the best so there's other ways to design this and basically have one order with multiple lines but we don't want to make over complicate it so right now we're leaving like this so you can see we've got twenty orders now so that's great so how would you see what it is because this table by itself it's very hard to see well who bought it and what did they buy and what was the total amount right so that moves us into the next step where we've got primary keys on the tables we really what we need to do is we've created more tables a primary key is an index let's talk about indexes for just a second we don't need one here but if you wanted to on the cliff you end up having a million customers in this table it could get slow to query and if everybody always queries off the last name you can create an index on the last name and it would give you it would give you better speed if you're doing a where clause against the last name in this case we only have three so there's no need so let's go back here the foreign keys we do need to create those we need to make sure when somebody creates an order that there's actually a customer and a product for it so let me show you what I mean by that so if we create an order and if I put customer four in here well if you remember there is no customer for there's only three customers so if I put customer four and insert it in here what went in just fine right now but that is an invalid customer there is no customer four so this is bad and in relational databases now we have multiple tables they all relate to each other and that's where you get the relational day well we need to make sure these relate relationships are good and to do that we have to put foreign keys on them and so I'm gonna create a brand new query and what we need to do is alter our orders table so when I say alter table orders and we're going to say add a foreign key and you see as these turn blue that they're reserved words and then we got to say what column in the orders table and so in the orders table first we're going to do the customer ID and then we're going to say it references the customer table and then in customer table we called it ID so let's take a look at that over here so you can see it real quick table refresh so in the orders table we have columns it's called a customer ID here it would have been nice if I would have went ahead and consistently named these but I didn't it's called the ID table ID column over here so that's where we need to get these synced up so we have the customer ID from the orders table references the customers table and we want the ID column so let's run that so now it says alter table statement conflicted with foreign key constraint the cont conflict occurred in the customer with the column ID well that's exactly our problem so let's go back here we have this bad record order ID 21 let's delete that we know how to delete now so I must say delete the orders table I don't one delete the whole thing where order ID equals 21 so let's go ahead and run that so now when we look at the order table that records gone come back here we run this and now complete it so you can't have a bad day when you run it so the it's good to actually just create these foreign keys as you create the tables so you need to know your structure beforehand we're kind of creating as we go and so that's causing this so let's make copy of this we need one more we're off alter the table orders table and I want to grab the product ID make sure the product relationship is good so I'm going to go to the product table and it's called ID there so this gave me a foreign key relationship invalid table product okay what did I call it products products okay there we go do that again okay so we got it in there so now we got our two foreign keys now what's cool let's go back up here and refresh our tables and let's take a quick look at the orders table and let's go to the keys so notice you have a primary key and you have some foreign keys going to customer and product ID you also have the primary key so let's go here and look at customer and it just has a primary key and products has a primary key so now that we put these relationships and let's go try that insert again that was really a bad insert it's for two let's do that so now it will not let us insert bad data and so this is where you get the whole relational database because all these tables are now related to each other and this is the referential integrity that that you need to put on your tables to make sure the data stays good okay so let's go back to our agenda okay so we've got primary key indexes we credit more tables we created off foreign keys now we need to join our tables together let's do that so I'm going to start a brand new I'm gonna grab these three queries here I want to start another new query and we got a blank screen here we've got our three tables now remember the orders table it's hard to tell what products ordered so let's create a new query that joins that back to the product table I say select star from orders and you can actually join tables by using the join clause and I'm going to say inner join and so inner join means everything has to equal up in both tables we want the orders table to join up to the products table and we want the we want to say on so this is what the do things are going to join together on so let's look at those two tables together real quick where'd they go okay they jumped down here because I got so many orders okay so the customer ID let me pull this up a little more in the order table we have a product ID wherever we see a one we want to join over to the product table and get that record 1 we want the name and the price to come out that's what we want to show up so what we're going to do is we join on this product ID column to the ID in the product table so let's do that so we're going to say we're orders or we say on orders dot and we're going to say the column name and you know what before I do that let's refresh the cache I'm not liking all these red lines okay so now they give me some help okay so when we say orders dot product ID equals to the products dot ID so now in this language and since equals server when you do this star it's going to grab all the columns from both tables so let's do that so now you can see we've got the product name and the price and the order date so we really don't need product ID anymore and we'll get rid of that in just second I want to show you another thing called aliasing we can call the orders table we say as oh and now we can reference every where we set orders we can just reference oh it shortens up your syntax for the products table we can say as P and then we can reference just pee everywhere and that's the exact same query that you just wrote and to further simplify it as is a optional word so I can actually get rid of it so let me get rid of that and so now we still at least our tables as oh and P and it still runs okay now you can do the same thing on columns so what I want to do first I'm gonna say Oh dot star that grabs everything out of the orders table I'll run that and then I want to say P dot star right there and so I just grabbed everything out of the product able to but I really don't want to I start I really just want actually I'm gonna leave that for just a second let's go ahead and go get the customers all we do is we do another inner join and we do customer the customer table and I Melissa this see on and I want to join the orders table ODOT customer ID this time equals to C dot ID okay if I run this will I get the customer data I just yet because the way I did this up here I need to put a comma and then go get the C dot star okay so now if I scroll to the right now we've got the customer data so now let's put this all together I want to just clean it up so in the order table I really just want the order date and the product table I think I want everything in the customer table I want everything so let's see what that looks like oh I don't want everything I just want he dot product name and he dot price okay so we could sit here and clean this up a bunch but we've now joined our data back together and when you see Barry Bonds it's been a hundred ninety five dollars on a bat he spent five ninety-five on a baseball and then we can also see that the city that the product was bought in is San Francisco four five ninety five so let's take a step back for a second I minimize that get my screens up here and I actually want to edit the city for Barry Bonds I'm forgot it okay so we're updating that customer now let's go back here and look at what happens to our order so Barry Bonds did have a city of San Francisco now it has a city of Goddard so we can see that the city changed as soon as the person changed you may or may not want that and you have to design your databases differently and that gets in the database architecture but that's just something I want you to see so the final things that we need to talk about our functions and group buys and we're going to do that right now because we have this all summed up what I want to do is I want to just look at the total money spent so far so I'm gonna get rid of this and I'm just gonna grab that price column and I want to sum it so imma type some right there highlight it f5 two thousand seven hundred seventy nine dollars 56 cents has now been spent through the order system and you can do that with the sum now notice how there's no column name there the same way I a least those tables I can say as total I can alias the column so if I rerun this now it says total there and again as is a is an optional word so you can just put a space in total and it still works now what if I want to do this by customer so I just sum the whole table I'm gonna do it by customer I've got to put a customer filled there so I'm put a comma I'm gonna go back a space and I'm gonna put my customers see dot and I'll let's do it by last name okay which this isn't exactly right you probably do buy ID because people could have the same last names but for this case we're gonna do that if I run this is not quite going to work as soon as I put this in here and I tried to run a sum it wants to group it up and let's know how we want to group it so there's another clause called group by and you put the same thing up here that you had the last name so we were group by last name we're going to sum up the price and so now we know that the Joey blue spent twenty nine dollars Barry Bonds spent two hundred and one dollars Mike Schmidt spent twenty-five hundred dollars okay we could also get the total instead of my name we could also go by name and the product they bought so we do TP dot product name and that's not going to run because we need to group a little more so let me come in here and put the grouping down here so one group by last name and product name I like that so now we know that Joey blue spent twenty nine dollars on baseball's bonds bought baseball and bats and then Schmidt bought just bats okay and so we've grouped by we summed we could also put an average so let's take product name back off and let's do location so C dot city okay because we know that we had me and Barry Bonds and Goddard right now so we've got two cities and then instead and we have the sum I want to go ahead and put in one more thing on put an average and on average price okay in this case I'll have to put any more group buys because I'm just putting another aggregate okay not a column dime grouping by but an aggregate so let's go in here and you can see that the total the average for the Goddard area per order was $33 the average for the Kansas City which we know is Mike Schmidt is 195 his all he bought was bats so it's the average is gonna be the exact same as the bat prices so let's look at our agenda make sure that we covered everything we did a quick introduction of sequel create a database and tables insert it select it from the table did some where clauses did some updates some deletes we changed the table we even drop the table we had it primary keys we talked about indexes we created more tables made relationships with foreign keys we joined with those relationships and then we ultimately summed and averaged and group by so I think we got through everything that's as much as I can cram into here I actually have another training that's on you to me that usually goes for about 10 bucks that's about 10 hours worth of this so if I went too fast in this you can always go out there for about 10 bucks you can see 10 to 10 hours of listen to 10 hours of me talking basically and doing the same stuff but getting in deeper slowing down a little bit and trying to get you know a little more in depth and some of this stuff so hopefully it's helpful let me know with any comments you have I hope that I covered everything you need for to get started other than that you can look at once you've gone through this and done some some of this yourself you can look at my other videos I go into a little more depth and some other features some of the functions and different things like that I'm so anyways let me know leave some comments we'll talk to you later

Tags:

  1. Thanks alot for the clear instruction. I've been struggling with SQL for some while but somehow while watching your video(s) I noticed I'm becoming more and more handy with SQL and it's actions. Much appreciated! Greetings from The Netherlands.

  2. Thank you so much for this video. I have watched other videos and this one explained in clear English how the SSMS works. It was also very clear where the explanation is needed for beginners who do not know how this works. This video is a lifesaver.

  3. * now we have twenty orders… nnnnice XD you'll become rich really soon)

    thanks for the lesson, I'm a complete noob but it doesn't seem hard with your explanations

  4. Thank you for the tutorial.

    I found it confusing that you are not consistent with your syntax. Sometimes you use uppercase for your commands, and sometimes you use lowercase.

    Other than that, is was great…

Leave a Reply

Your email address will not be published. Required fields are marked *