SQL Crash Course – Beginner to Intermediate
![*](https://i0.wp.com/allprowebdesigns.com/wp-content/uploads/2023/12/1703116430_maxresdefault.jpg?resize=840%2C430&ssl=1)
Video Title: SQL Crash Course – Beginner to Intermediate
Hey guys welcome to the free SQL course in this course we’re going to focus on the actual structured query language syntax so we’re going to put away the fancy graphical user interfaces and we’re going to get into the actual programming so we’ll be using PHP myadmin which is a graphical tool but
We’re going to be using the SQL shell to do pretty much everything except look at the actual rows that are being returned all right so before we get started I just want to remind you to subscribe with us follow us on Twitter and Facebook and if you’re feeling really
Generous donate a couple bucks so that we can keep making these free courses all right so let’s take a look at what we’ll be doing what you’ll learn so we’ll take a look at kind of an overview of SQL talk about what it is what it’s
Used for and then we’ll start to look at how to create alter and delete databases and tables how to query a database using multiple operators we’ll take a look at some of the various data types that are available I’ll show you how to insert update and delete
Records how to join tables together use aliases we’ll look at primary keys and foreign key constraints and also indexes alright among some other stuff that’ll get thrown in the mix so what is SQL it stands for structured query language and it’s a special purpose programming language that was created to work with
Relational databases all right SQL has been around for a very long time but it’s still relevant it’s the main it’s the main method of interaction between an application and a relational database all right and SQL is used anywhere from just small individual blogs to you know enterprise corporation
Level all right so it’s very very flexible and also very scalable so some of the popular databases that use SQL this is by no means the entire list there’s a lot more but these are some of the more popular ones that you may have art of alright first we have MySQL which
Is extremely popular that’s what we’ll be working with in this particular course but you also have postgrads which is another really popular relational database Oracle Microsoft SQL Server SQL Lite and then you have D base Hadoop max DB Moorea DB which is drop-in for MySQL and open base okay so these are just
Some of the many relational databases that use SQL alright so when we talk about SQL tools there are dozens and dozens of tools some are dedicated to one specific relational database system others are cross-platform so these are just a few of the many so the command-line client when you download
Let’s say MySQL you get you get a shell program that you can log into through the command line and you can start to create your tables and do all that all right then you have graphical tools like admin error which will be using that give you an easier way through a
Graphical user interface to do that stuff but also give you the option to use some kind of SQL shell alright and did I say we’re using that we’re not using that were using PHP myadmin which I’ll get to in a second admin error and Firebird the next one are both
Cross-platform so you can use these with MySQL Postgres and a bunch of other ones as well MySQL workbench is very popular that’s it’s actually offered free from the official MySQL website you can do diagrams and some really nice graphical stuff with that PHP myadmin is a web
Tool that is offered on on many cPanel hosts so if you if you get a hosting account with Hostgator or Bluehost or something like that they usually offer PHP myadmin with that with their packages all right PG admin 3 is a really nice desktop tool
For postgrads I do have a video on that if you want check that out sequel Pro is a Mac tool I don’t know too much about and in high D SQL I’ve used a couple times but never really got got too much into it alright
So these are just some of the many many tools that are available alright so before we can actually dive in and start looking at SQL you need to have some kind of SQL database installed on your server or on your local machine whatever it may be now what I would suggest is if
You’re just using let’s say Windows or Mac and you don’t have a server that you actually want to set up an easy thing to do is to something like exam or xampp what that does is it gives you an apache server mysql PHP and a bunch of other
Tools available on your local machine alright so you don’t have to connect to a special server or anything like that all right that’s what we’ll be doing if you want to download zamp you can go to Apache friends org and it’s a cross-platform it’s available for
Windows Linux and Mac so I already have it installed I’m not going to go through it but I do have a video on how to get examp set up and running on Windows alright it’s actually very very easy so once you do that you’ll be able to
You’ll be able to open up PHP myadmin which is a graphical tool to manage your server here are databases so you can get to that with localhost / PHP myadmin now when you first install zamp you probably won’t have a password for your your root
User and you want to fix that so what you can do is in PHP myadmin if you click on user accounts and you look at where it says root for the username and localhost for the hostname you probably have an O right here if you if you have
A yes then it should be fine then you know you have a password if you have an O then let’s click Edit privileges and then you want to click Change Password and then just put in the password here and click go alright now if you get booted out of PHP myadmin because you’re
Not logged in they what you need to do is go to your your zamp folder look that’s not it we want to go to zamp and then go to PHP myadmin and then this config file right here config Inc PHP you want to open that up and then just whoops just put the
Password that you chose in this field right here this should be empty okay you want to put it in there and save it and close it go back and you should be okay so creating a database is extremely easy okay so what we’re going to do is we’re
In PHP myadmin and we could simply create a database through here but that’s not what we want to do we want to focus on the SQL programming language so let’s click on the SQL tab up here and this is where we can actually run queries alright let me make this a
Little bigger okay so what we’re going to do is say create database and then we want to name it okay I’m just going to say test for now and we’re going to end all of our statements with all of our lines with a semicolon
So let’s click go and you can say see we got an OK message but just to check we can click on databases and there’s our test database alright and while we’re at it I’ll show you how to delete a database to do that you want to say drop
Database and then whatever the name okay if we run that we get an OK message and if we look test is now gone alright so that’s how you can create and drop a database now I’m going to create one just to work with throughout this course so again let’s say create database and
This is going to be just a fictional company I’m just going to call Acme alright so click go now we have our Acme database okay so we have an empty database now which isn’t very useful because we have no tables all right so let’s create our first table to do that
We’re going to say create table and this I need to make this a little bigger all right that should be good so we want to say create table and then we want to name it so let’s create one for customers all right and then what we
Want to do is in here we want to specify the fields that we want are the columns so I’m just going to space this out a little bit and we know that we want an ID so to create a column you want to put the call name and then the data type in
This case it’s going to be an integer so we want int all right and then we also want to make sure that the ID cannot be null okay now we want to separate these with a comma and the next field that I want is first-name okay now as far as
The naming conventions for columns this here I like to use camel case which means that the first letter is lowercase and then every word after that the first letter will be uppercase okay often you’ll also see something like this you’ll see that we separate the words with an underscore all right
So it’s all preference you could you could do whatever you’d like some people use partial case which the the first letter would be capital as well but I’m going to stick to camel case okay so we have a first name now for the data type that’s going to be
What’s called a varchar or a VAR car and that is a just a string of characters it could be letters numbers characters whatever and then we have to specify the max length which is going to be 255 all right and you could do lower obviously a first name isn’t going to be 255
Characters but just to be safe I use 255 for pretty much everything okay next we’re going to have a last name same thing I believe that the rest of our fields are going to be far car oops okay then we’ll have an email I might as well just copy this okay so
Email then we’ll have address and let’s do city state and let’s do code okay so this here will create the customers table for us but I want to do a couple things one we need to specify a primary key okay a primary key is unique and is
Usually attached to the ID so all we need to do to create this is just add on to the end here primary key and then we want to specify the which column we want and we just want to put that in parentheses so it’s going to do the ID
All right now there’s one more thing I want to do and that’s setting the ID to auto increment okay and what that means is that let’s say we enter we enter a row and it has the ID of five well the next time we go and create a new row a
New record the ID will be six it’s going to increment by one by default all right you can change it to something else but one is the default and that’s what usually used so what we want to do is just add on to this we want to say auto
Increment just like that alright so we’re ready to create our table let’s go ahead and click go what’s this no database Oh alright so we’re actually not inside of the Acme database so what I’m going to do is just I’m going to copy this with
Ctrl C and then go to databases click on Acme and then click on SQL and now you can see we’re in the Acme database okay so let’s go ahead and do that okay so now if we go to our Acme database you can see now we have a customers table
Okay if we click on that not going to see anything here because we have no records in here no rows but if we click on structure you can see everything that we just created and you see the little key icon next to the ID because that’s a primary key all right
It’s also set to auto increment and you can see all the data all the data types here as well so we created our first table now I’m going to show you how to insert data into the table alright guys so now we’re ready to insert some data
And you can see here that we’re in the Acme customers table all I did was went to the screen and then click SQL okay actually let’s click on customers and then SQL and when we’re in this view you can see we have all the columns over
Here that we can use for reference okay so let’s go ahead and insert some data so to do that we’re going to say insert into customers alright and we want to specify the fields that we want to enter so let’s say first name basically this is going to be everything except the ID
The ID is auto increment and we’ll create on its own okay so then what we want to do is say values and then some more parentheses and we want to put in our values okay so we have let’s say for first name we’ll do John lastname doe email we’ll say J
Doe at gmail address we’ll say 55 Main Street and we’ll say Boston okay so this should insert the customer let’s go ahead and click go okay so notice it says one row inserted if we click on browse there you go we have our John Doe customer okay so what
I want to do is insert a few more so I’m just going to paste this in alright so this is how we can insert multiple records at once so we say insert into customers and then we specify all of the columns but we only specify these once
Okay and then we can add multiple records and we’re just separating them with a comma and the last one gets a semicolon alright so let’s run go ok so you can see it says five rows inserted and if I click browse you can see we now have a
Bunch of customers so now I’m going to show you how to update a record ok so let’s say that one of our users wants to change their email address so what we can do is say update and then whatever the table name update customers and then
We want to use set actually I’m going to put these on their own lines just to make it a little more readable so we want to set email equal to whatever we want to set it to let’s say we’ll just a test at gmail.com okay so
We want to set email to that and then we want to say where which this part is very very important because if you don’t include the where then all of the customers emails are going to change to this so you want to specify this we’re
Going to say we’re ID we’re ID is equal to three okay so let’s go ahead and run that okay so we get one row affected if we go to browse you can see the the customer with the ID of three which is Cathy her email is now test at gmail.com
All right guys looking at our table I want to show you how to delete a customer all right so let’s say that Derek is not a customer anymore and he is the ID of six so what we want to do is go to our SQL and we’re going to say
Delete from customers again the where part is extremely important if you don’t have this it’s going to delete all your customers so we’ll say we’re ID is equal to three alright so let’s go ahead and click go okay we get one row affected if we look
Now you’ll see oh did I say three oh okay I deleted the ID of three I’m at six but that’s fine same thing so you can see that we deleted a customer alright guys so before we get into selecting data and fetching data I want to show you alter table alter table is
Used to do things like add columns to to tape delete columns change the data type things like that alright so let’s go to our as well actually let’s figure out what we want to do first so the first thing I want to do is show you how to
Add a column so we’re just going to add one called test or test call so let’s go to our SQL and what we want to do is say alter table customers and we want to say add test call okay and we also want to specify the data type let’s just say
Varchar’ 255 alright so let’s go ahead and click go okay so we got an OK message here let’s click browse and you’ll see we have test call now everything is null because we haven’t done any inserts with the test call included ok and if we click structure
You’ll see that test call is a varchar’ ok so the next thing I want to show you is how we can change the data type for column so let’s go and say alter table customers all right now the syntax varies depending on what database you’re using we’re using MySQL so what
We’ll do is say modify alright but with other databases you might see alter column okay so let’s say modify a modify column what we changing test call and we want to change that to an int and let’s just give it a max of 11 characters okay
Put a semicolon there and go alright now if we go to our table and test call if we click structure you’ll see that it’s now an integer with 11 max characters ok so last thing with alter table is I want to show you how to actually delete a
Column so we’re going to say again alter table customers and we’re going to say drop column test call okay and now if we go back now you’ll see test call is gone alright guys so now we’re going to get into selecting data and there’s a lot to it
There’s a lot of different types of queries select queries that we can make so we’re going to start simple and then just move up ok so first thing to do is the easiest select statement would be select all from customers ok the asterisk is used as a placeholder for
All all columns ok so let’s click go and you can see it just gives us all of all of the columns ok now if we want to limit that we can say let’s select first name and last name from customers and we click go and that just gives us the
First name in the last name now let’s say we want to select a specific customer so to do that let’s say select all from customers and then we just want to use the where clause so we’ll say where ID is equal to 3 ok actually I don’t think we have a 3 anymore
Alright now we’re using the ID because that’s the primary key and because it’s unique ok so if we were to say select from where name or first name equals Bob or something like that then it’s going to find all of the Bob so if you have
Two customers named Bob then you have a problem all right so you want to use something unique in your where clauses so let’s go ahead and run that and you can see that that gives us Mike he has the ID of two all right so that’s how we can select
A specific customer now what if we want to sort it okay if we want a certain order so what we can do is let’s say select all from customers and then we’re going to say order by and let’s do last name okay run that and you can see we
Get all rows and it’s by last name okay it starts with the D goes down to W we can also change the sorting order okay if we want we can say descending des see and click go and now you can see it starts with Williams okay and same thing
You can also do a sending which does the same thing as the default okay now let’s say that we want to return all of the the different states all right so we could do select state from customers okay we click that go and now you can
See it gives us the states but what if we don’t want duplicates okay what if we just want one of each state all for that we could use distinct okay so we just put right here select distinct okay we click go and now you can see it just
Gives us one of each now there’s some other select statement too that I want to show you but some of them have to do with integers with numbers so what I’m going to do is I’m going to add I’m going to alter the table and add an age
Column all right so we’ll go to SQL and let’s say alter table customers and then we want to add column age which will be an int okay so if we go to browse now we have age and just really quickly I’m going to add their ages through here okay we’ll say
2345 33 20 and say 64 okay so now our customers have ages so let’s go back to the SQL window and let’s say select all from customers where age is is less than let’s say 30 okay so this is an operator the less than and I’ll get to some of
The other ones in a minute so if we click go you can see it’s returning us John and Lillian which are age 23 and 20 so these are the customers that are under the age of what was it 30 okay so there’s different operators that
We can use as I just showed you we have we use the less than and of course we’ve used equal to but there’s actually a whole bunch okay so you can see we have equal to this here is not equal to which you can also use in many different
Database systems you can use exclamation equals which means not equal to alright we have greater than and less than we have greater than or equal less than or equal between which will give us a range which I’ll show you in a minute like which will match a certain pattern
This is often used for searches okay if you have a website and you want to be able to search articles or something like that in will be equal to one of multiple possible values we can use is or is not – compared to null is not distinct from
Which is is equal to value or both are not and then we have as used to change a field name when viewing results alright we have some examples over here but I’m going to show you some examples all right so let’s say we want the customers
That are an aged between let’s say 22 and 40 all right so what we can do is say select we want to say select all from customers where whoops age and then we want to say between what I say 22 and 30 all right so let’s go ahead and run
That and it looks like the only one we have is John there’s 23 um let’s let’s actually do 40 ok we click go and we get Stephen who is age 33 okay so that’s how we can use between so another one we can use is like okay like we’ll match some
Kind of pattern so what we’re going to do here is let’s say select all from customers and then we want to say we’re City like and I forget what we have I know we have Boston so let’s do % n and let’s see what that gives us
Okay so what that’s giving us is all of the customers where their city ends with n ok because if we look at it the % this is a wild card okay so that means that anything could be here and then we needed to end with n okay which Brooklyn
In Boston obviously that makes sense ok if we put o N and click go now we’re not getting Brooklyn anymore because it doesn’t end with Oh n okay now let’s say that instead of just percent n let’s do percent N and then another percent and let’s see what that gives us okay so
That’s giving us three records Boston Brooklyn and Yonkers and the reason for that is that now we’re putting a wild-card on this side of it so basically this is getting any city that has the letter n in it and you’ll notice Yonkers has it it’s not at the end but
It’s still showing up because we have a wild-card on on both sides all right and then at the same time we can also use not like okay so if we put here instead of like we put not like and then click go okay now it’s giving us Amesbury and exeter because these
Cities don’t have the letter n in them okay so that’s it for like now I want to show you in okay in is also very helpful what that does is it allows us to specify multiple values in a where clause which can really shorten it up
All right so let’s say select all from customers and then we’re going to say where state in and then we want to open up parentheses let’s say New York and New Hampshire okay and we’ll click go and you’ll see that we’re getting all of the customers that are from either New
York or New Hampshire okay the Massachusetts customers aren’t coming back all right now imagine if you if you wanted to specify you know nine or ten states if you were to use just aware you would bu it would be a long long query and by using in we can just specify them all
Inside of our parentheses alright so we’re going to talk a little bit about indexes all right indexes are used to speed up queries alright it is it’s a pointer to data and a table so an index in the database is very similar to an
Index in the back of a book ok it’s it’s used to find data more quickly and efficiently alright users do not see indexes they are just used to speed up search queries or searches if in your website you want to be able to look up let’s say users
Buy an ID you would put an index on that ID field and speed up things alright you only want to create indexes on columns and tables that will frequently will be frequently searched against okay so you don’t want to put them on every single column or table
Okay so let’s say that in our application we’re going to want people to search customers or your admins to search customers by city so we want to add an index on the city column so let’s go to our SQL here so let’s say creates
Index and then we want to give a name to it let’s just call it see index and then we want to say on customers and let’s say city now we’re going to ignore that X for now so let’s click go and now we should have an index on the city column
So now if we go to structure and we look at City you see we have this little key icon which means that we have an index on it all right let’s make sure we can still select data from it okay so if we say select city from
Customers all right and the search is going to be much more efficient in faster all right to remove an index is very easy as well so let’s say drop index C index on customers okay if we go ahead and run that and we go back to structure you’ll see that City no longer
Has an index all right so we’ve gone over quite a bit pretty much all of the fundamentals of selecting data from one table okay so what we want to do now is create some other tables and create a relationship between them and then I’ll show you how we can actually join tables
Together for select queries all right so what we’ll do is I’m going to paste this stuff in because it’s stuff that we’ve already done so what this is going to do is it’s going to create a table called products we’re going to have an ID which
Will be auto increment it has a name and a price and then a primary key of ID okay so let’s go ahead and run that okay so now you can see we have a products table so we’re going to create another one called orders so the orders table is
Going to have an ID primary key auto increment and then you’ll see we’re also going to have an order number which is just going to be just a made-up number that we create and then product ID and custom ID these are going to be foreign key fields all right what that means is
That this Product ID column is going to match up to the products table ID and then this one will match up to the customers table ID okay now we haven’t defined our foreign keys yet but I’ll do that in a second all right and then you’ll see we just have age we don’t
Need don’t know why that’s there order date which is going to be set to the date time datatype and then we’re just saying the primary key is going to be ID so let’s go ahead and add the foreign key restraints okay so what we want to do is
Say foreign key let’s say customer ID and then we want to specify its reference so we want to say references will be to the customers table and then to the ID field okay and then what we can do is copy that we want to do the
Same thing for products so this will be foreign key on product ID references is going to be to the products table to the ID field now before we run this is one more thing I want to do for the date the order date I don’t want to have to
Manually put that in so we’re going to set a default so what we can do is just say default and we want to say current timestamp okay so that’ll that’ll happen for us we don’t have to manually include the date and time all right so let’s go ahead and
Run this we’ll click go okay it looks like it went okay we’ll click structure and you can see we now have orders okay if we click in orders and go to structure notice that the product ID and the customer ID have a key icon that’s telling us that these are foreign key
Fields now the purpose of a foreign key field is so that we can prevent data corruption within our database or just create an unstable database so let’s say we have this orders table and we have we have an order that has a product ID of three okay well we don’t want to have
This Product ID of three and then in the products table not have that specific product with the ID of three all right so it won’t let us delete certain records that are needed for other records if that makes sense hopefully all right so that’s why we need foreign
Key constraints all right so now we’re going to take a look at another very important part of using relational databases and that is table joins all right joins are used to combine rows from two or more tables based on a common field between them alright and there’s different types of
Joins we have an inner join left and right joins and a full join all right so let’s take a look alright so before we can do any joins we need to actually have some data in our orders and products table so let’s go ahead and go
To products and we’re going to just add some data here all right so we’re inserting some products here okay we have a name and a price and I just call them product one two three four and five and then just some integers for price all right so let’s go ahead and run that
Okay we look at the structure actually want to go to browse and you can see we have our products alright so now what we want to do is you want to go into orders and I’m going to create some orders alright so let’s take a look at this
We’re sitting insert into orders and we have three things we want to put in an order number product ID customer ID okay now notice we don’t have the order date because that has a default of the current timestamp all right if we look at this we’re just giving the order
Numbers 0 0 1 and then just going up by one alright and then we have our product IDs you want to make sure these actually exist same thing with the customer IDs alright so let’s go ahead and click go alright and if we go to our orders table
Now you can see we have all of these orders now if you want to fetch an order along with its information if we were to just get from the orders table this is all we could get and this this obviously means nothing to us as far as readability so what we need
Do is do some table joins so let’s go to SQL and we’re going to start with an inner join which is probably the most popular and what that does is it will return all rows when there is at least one match in both tables alright so let
Me give you an example so we’re going to say select now we’re using two different tables so we need to specify the table name along with the column name so let’s say we want to select customers dot first-name okay we want to get the customers dot last name and then we also
Want the orders say orders dot ID and let’s also get orders dot order number okay so the goal is to get the customer info along with the order number okay so we want to say from customers all right but we want to join in the orders table
So we’re going to say inner join orders okay then we want to say on and then this is where we want to match them together so we want customers dot ID to be equal to orders dot customer ID okay these should match and then let’s just
Do order by let’s see we’ll order by customers dot last name all right so let’s see what that gives us alright so let’s look down here and you can see we have our first name last name we also have the ID of the order and the order
Number all right actually you know what we probably don’t want the ID eat so let’s take that out and let’s run go and now we’re just getting the actual order numbers all right and you may want to order it by the order number okay so
If we run that now you can see it’s ordered by the order number now for a left join that’s going to return rows from the left table with the matching rows on the right table so let’s say select first name and we want orders dot order number all right actually you know
Let’s also get the date so orders dot order date all right and then we’re going to say from customers and then we want to left join orders customers dot ID should be equal to orders dot customer ID okay and then let’s just do order by last name ok let’s go ahead and
Run that okay and I see same thing we’re getting the first and last name with the order number and we’re also getting the order date and time so now let’s do a right join all right so actually what we’ll do is let’s specify borders dot order number we want
Customers and let’s see this time we’re going to say from orders okay and then this will be a right join will say right joining customers on I’m just going to switch these and then let’s do order by orders dot order number okay so now you can see we have our order numbers and
Then the first and last name and this is probably how you would want to do it now another thing you might want to do is bring in the product okay we might want the product name so that means that we have to join in products and orders so
Let’s go back to our SQL we want to select orders dot order number we want customers dot first-name customers dot last name products dot name ok so that’s what we want and then we’re going to say from orders ok then we want to inner join products
On orders dot product ID and products ID okay then we want to also join customers okay for that we’re going to say on orders dot customer ID equals customers dot ID all right and then we’re just going to say order by order number orders dot order number all right so
Let’s go ahead and run that and there we go so now our result here is pulling the data from three separate tables okay we have the order number which is coming from the orders table first name and last name is coming from the customers table and name is coming from the
Products table so that’s how you can join three tables and of course you could do more than three if you wanted alright so now we’re going to take a quick look at a li is’s okay aliases are used to give tables or columns a temporary name and make them more
Readable all right so the column names for this table aren’t too bad but we may want first and last name to be formatted a little differently all right so to do that lets go to our SQL and we’re going to select let’s say first name what you
Want to do is use the ads keywords so we’ll say first name as now if you’re going to use two two or more separate words then you need to have quotes around them so we want first space name okay so that’s that and then we also want last name as
Last name okay and then we’ll say from customers so let’s go ahead and run that and now you can see that the column headings here have changed to a more read readable format now another thing you may want to do is you may want to combine columns all right for instance
First and last name you may want that to be returned in one column instead of two separate ones so what I’m going to do here is I’m going to say let’s just get rid of all this now this is where we need to use concat okay so we need to
Say select concatenate I first name and then comma then we want a space in between them and then last name alright then we can do has actually let’s just say name as name and then we also want to grab the address city and state from customers all right so let’s run that
Okay so now you can see we have a name column and it has their first and last name all right now we may want to also join the address fields together and put them in a single column called address so let’s go back up here and we’re going
To do concat and then we’ll wrap these up okay between each one let’s do that okay let’s try that oh okay so the heading isn’t correct oh I didn’t do as I’m gonna do as address there we go okay so we’ve turned it into just two
Columns name and address so you can see this can be very helpful now we can also use this for tables so let me give you an example of that okay so let’s do select will select ODOT ID and then let’s do Oh dots actually let’s do order
Date okay so this will be from the orders table you’ll see why I’m doing this in a second and then let’s do C dot first name C dot last name and then we’re going to say from customers as C and then also orders as o so let’s go
Ahead and run that and there we go we get the order ID and the date and then the first and last name okay so we can use aliases for tables and columns alright so now we’re going to look at aggregate functions in SQL okay so
There’s a few of these the first one I’m going to show you is average or AVG okay so for this we can take a column and we can get the average so let’s do it with the age in the customers table actually do we still have an age yes we do okay
So let’s go to SQL and we’re going to say select AVG and then we need the column name in this case it’s going to be age from customers okay and if we run that you can see that the average age is 37 so that’s average or AVG now let’s take
A look at count so what we’ll do here is we’ll say select count and let’s say age from customers okay if we run that it gives us five ok there’s five customers with an age in the customers table okay we also have max so if we want to find
The maximum age we can do that which is 64 and we also obviously can do min for the minimum age which is 20 another one is sum so if we want to get the sum of all ages we can do that which is 185 not sure what what that could possibly do
For us but you can do it now another thing we could do is we can use the group by statement and what that does is it’s used in conjunction with an aggregate function to group the result set by one or more columns so what I’m going to do is just very quickly I’m
Going to just make one of these ages the same as another so let’s say 33 we’ll go ahead and change 45 to 33 all right and then let’s go to our SQL and what we’re going to do is say select age and let’s use count age from customers and we’ll say where
Whoops where age is greater than 30 and then let’s do group by age okay so if we run that what it’s doing is it’s finding all the ages above 30 and then it’s giving us the count of each one so remember we have to with 33 so we get
Two here okay and one for 64 and we can also we could take out that we’re if we wanted to and that gives us everybody’s all the ages in the amount of records with that age okay so that’s group by and another thing we could do is let’s
Say we only want the ages that have two or more results okay so what we can do is take the same thing but just add on to it having okay so we’ll say having account that account for age that is greater than or equal to two
All right so if we run that you can see we only get 33 because that’s the only age that’s seen two or more times okay so back to some of the more simple aggregate functions we have you case so let’s say we’ll say select you case and
Let’s pass in here first name from customers and you probably know what this is going to do okay it’s going to put all the names in upper case and of course we could select other things here as well if we wanted to say last name
Okay it’s going to give us the last name too but only the first name is uppercase all right now we also have lower case so we can do L case and let’s put this one as u case and there we go so this is L case this is u case all
Right so I think that that’s going to be it guys I mean that is that’s the fundamentals of SQL now there are good there’s going to be some stuff that I didn’t cover some more advanced stuff but I really wanted to keep this video under an hour and basically just give
You what you need to be able to you know administer a database so if you liked this video please leave a like please subscribe if you’re not already subscribed and thanks again and I’ll see you next time
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99
Helpful tutorial! I just wanted to note that I initially had trouble getting into phpMyAdmin after installing XAMPP. Apparently I needed to start the Apache and MySQL services first. At that point I was able to click on Admin next to MySQL in the XAMPP window and it took me to the localhost/phpmyadmin dashboard.
Amazing work !
This is by far the best training video I've see anywhere. THANK YOU!!
Thanks dude!! Really helpfull
Hi. Thanks for video. AUTO_INCREMENT doesn't work in my work. I can't understand why
I never comment on videos but this one deserves it 100% – I learnt a lot in this hour! Thank you
Thank you brad for another great crash course.. This a good tutorial to start with sql
My Question is;
As IDE and writing tool for SQL codes for the developing environment ; do you suggest
– Xampp control panel and PHPmyadmin
Or
– Microsoft SQL server management studio
an amazing explaination as usual Brad !
I love learning from mark wahlberg
tks!
Hello
If it is possible please make and put your updated " SQL " tutorial in your channel.
Thanks a lot .
You and your all toturials are the best in the world
After id 3 was deleted, how would we go about re-entering values at ID 3? If we were to go back and add in a value like we did for all of the values at once with INSERT INTO tableName (columnNames) VALUES(……), would that create a new ID, or would that replace the value of 3?
Thank you, this was very helpful. I'm hoping I can learn SQL and get out of retail and hopefully work from home some day.
Before getting to your video, I had a very barebones understanding of SQL. At my job, before our application switched to cloud-based, we were utilizing SQL Server to manage individually for customers (customers would install our application and manage their data associated with that application in their own independent SQL Server Database that we setup). When things would go wonky and required a trip into their SQL, we would go in and fix various things, some of which required running queries and/or scripts. From this I gained a very basic knowledge and as I was more and more exposed to it, I picked up more and more pieces. However I was never learned enough to pick up anymore than some basics because I struggle with syntax, and book learning simply isn't how I learn (because my brain is dumb like that).
Your video is the first video that has actually been able to provide leaps and bounds in my understanding, coupled with W3 school courses. I'm certainly no SQL whiz kid, but that bit of light at the end of that tunnel is now visible. Much appreciated!!
Fantastic crash course. Many thanks!
understandable, have a nice day
Should i learn sql or mysql first sorry im new to this so idk
Its been this Long Brad
this channel is the fucking BEST!
Great video, thank you !
At 40:15 it says:
Cannot add or update a child row: a foreign key constraint fails (`acme`.`orders`, CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`customerId`) REFERENCES `customers` (`id`))
does anybody have the code written somewhere ?
Brother, Thank you so much from my Heart. Very Good Work.
Good work
if anyone needs to enter data
INSERT INTO customers(firstName, lastName, email, adress, city, state, zipcode) VALUES
("Mike", "Smith", "msmith@mail.com", "22 Brich Lane", "Amesbury", "MA", "01913"),
("Kathy", "Morris", "kmorris@mail.com", "74 Willow St", "Haverhill", "MA", "01860"),
("Steven", "Samson", "ksamson@mail.com", "12 Gills Rd", "Exter", "NH", "01283"),
("Lilian", "Davidson", "liliand@mail.com", "7 Whitter St", "Brooklyn", "NY", "34883"),
("Derek", "Williams", "dwill@mail.com", "445 Madison Ct", "Yonkers", "NY", "34993");
thanks you
thank youuuuu 😀 where is advanced SQL? thank you so much for this, my students love it.
Aliens when creating a male human specimen 18:57
coool
The site: http://localhost/phpmyadmin no longer exists.
I wanted to ask do the union functions make a temporary change or a permanent change to the tables? This video is quite popular & well done for making this video.
52:39 you are missing a join? this will just multiply every row from both tables together right?