SQL Tutorial for Beginners (and Technical Interview Questions Solved)

Key Takeaways

welcome to this comprehensive sequel tutorial course designed for beginners and also those looking to prepare for technical interviews this course was developed by the team at Jovian who have created many popular courses for our channel in this course we'll cover the basics of relational databases and SQL including setting up MySQL inserting data and working with aggregation grouping and pagination and SQL queries the course also covers Advanced topics such as combining tables using joins executing SQL queries using Python and SQL Alchemy and solving technical interview questions by the end of the course you'll have the knowledge and confidence to excel in SQL is it SQL or SQL well it doesn't really matter because you need to learn it no matter what it's called hello and welcome to this tutorial on SQL where you will learn the basics of the structured query language which is used to fetch data from almost every database in the world no matter what you're doing in data science you will need to learn these skill and this will be part of your daily job and this course guides you step by step and you can follow along with everything that we do in this course to learn the basics of SQL and even learn how to crack interview questions from the top companies in the world so I hope you enjoyed this course leave a comment and subscribe to our Channel if you'd like to join more of these courses and let's get started the topic for today is relational databases and the structured query language or SQL and here's what we're going to talk about today first we will talk about some of the use cases and the design of relational databases and SQL what they are and why they are used we will see how to set up a database locally using the MySQL server we will create modify and delete databases and database tables we'll see how to do that we learn about the data types in SQL and some constraints like primary key foreign key Etc we will look at crud operations you will see this term a lot in relation to databases so create read update and delete operations on data stored in tables and then we'll also look at how data can be exported and imported from relational databases this is part one of two lessons on SQL so the next week will be an advanced lesson where we will look at aggregations we look at joins we look at indexes and we will also see how to use how to use Jupiter to get data out of a SQL database and how to work with it in Python so maybe how to do some analysis and how to do some visualization but today we are going to work in a more traditional setting where we are going to use some commonly used software like a MySQL server for working with SQL databases now we learn about relational databases in SQL by working through this problem this is a hypothetical scenario that we've set up and we'll try and understand in the context of this problem why we want to use a regulation database and how SQL is helpful so classic Models Incorporated is a manufacturer of manufacturer of small scale models of cars motorcycles planes ships trains Etc and products manufactured by classic models are sold in toy and gift stores around the world so this is what some of their products look like they are small scan models of various types of vehicles and classic models has offices around the world with dozens of employees and these are primarily sales offices so the customers of classic models are typically toy or Gift Stores and each customer typically has a designated sales representative uh in the same region who's also an employee of classic models and they interact with that sales representative directly then customers typically Place orders requesting several products in different quantities and then they pay for multiple orders at once via checks so this is typically how a business works you have you manufacture some products and then you have offices and then you sell your products to people and you have transactions with them and you have employees involved who are involved in managing these relationships and managing the offices that you run right now for an international organization like this it is important to record all of this information all of the activities in a single central place right so your task is to create a database some sort of a system where you can record and manage all of this above information so where do we have our offices who works at these different offices who are our customers how who is the sales representative for every customer what kind of orders are customer placing and what what are the payments from them what do the payments from them look like so we want to record and manage all of this information in a database in a central location and this database will also be used for day-to-day operations for example adding new customers placing orders recording payments hiring employees and things like that and also just querying information and getting analytics about how the business is doing so while there are many ways of storing data on a computer for example we could put this into a bunch of text files we could maybe open notepad create a few files and put in all this information there but looking it up is going to be very difficult we could use Json files we could use CSV files we could put maybe have a CSV file for customers one for products one for employees we could use spreadsheets things like Excel which a lot of businesses use but spreadsheets become a bit Limited in functionality as you deal with really large data sets and as you want to do some Advanced forms of querying and also as you want to integrate this with these systems with other systems for example you want to create forms for customers to sign up you want to create order forms you want to create a bunch of different things connecting forms with spreadsheets and writing custom business logic can be a bit tricky so this is where a relational database comes into picture a relational database is a data storage system typically which has the following properties so the first thing about a relational database is that data is stored in tables so each kind of entity that we are concerned with you have a table for it for example you have a table for customers and this is a snapshot of some data stored in a relational database so you have a table for customers and each customer has a customer ID they have a first name they have a last name they have a date created which means the date at which or the exact time at which that customer was added to your database and then you have a bunch of information maybe where they live etc etc right so that's one important piece that data stored in tables second each table has a set of columns as you can see here each stable has a set of columns and each column is used to store a specific type of data for example customer ID is going to be a number and it's in fact going to be a unique identifier for the customer first name and last name is their first and last name and then date created is going to store the date or the time exact time when they join and so on next data in the tables is stored as rows so each row of this data after this header row represents a customer and sometimes these rows are also called records so data stored using rows within database tables next table support crud operations on rows which means create read update and delete so you can add new rows into the table that's the create operation you can delete rows from the table you can delete rows from the table that is the delete operation you can get data out of the table let's say you want to figure out which customers you have in the United States then you can query that you can just type out some code and you will get back that information and you can update information because if a customer changes their address or their phone number or their email you should be able to change that then one important piece so a lot of databases support this but one important piece is relations between tables so tables can be connected to other tables using relationship constraints for example you have offices and you have employees we can say in the employees table that an employee works at a particular office so we can make a relationship between an employee record and an office record and there can be one-to-one relationships there can be one too many many-to-one relationships and we will talk about these in more detail when we talk about joins but at the moment the just the fact that you can link records across tables is an important piece to understand then relational databases typically allow you to retrieve data from the database using the structured query language or SQL which is what we will learn today and these databases can either be hosted locally on your computer or more commonly these are hosted somewhere on the cloud for distributed access for example for classic models it's possible that they have set up a database on the cloud and all the employees from all the different offices use this database through some browser-based application maybe they have Farms or maybe they have some kind of access-based system where different employees have different permissions and they use this database but everybody's ultimately making changes or getting information out of the same database right and this is how most modern applications work for example on Jovian we have a relational database sitting somewhere on the cloud and all of your account information is on in the relational database and when you open your profile we get data out of that relational database and we show it to you okay so that's relational databases the important pieces are that data stored in tables a set of columns each column has a specific type and then tables can be conducted using relationships and the data is queried using SQL or the structured query language now when you are setting up a relational database which you most likely won't have to do because in most cases whatever Corporation you work with or whatever client you work with in a project they will already have a database but when you're setting up a database it is common to First create an entity relationship diagram or it's called an ERD to describe all the tables within a data within the database and the relations between them so ERD this is what an ERD looks like an entity relationship diagram and they can be created using drawing tools like lucidchart so typically this is something an image like this is what you might be given when you join a company they may give you an image like this and they would tell you that this is what our data model looks like or this is what our database looks like and sometimes you may have multiple ersds because different teams or different or orgs or different departments when this in the same company may have their own separate database and sometimes you may have data duplicated across different databases and so on but let's just take a look at this ERD and understand what it represents so the first thing you will notice is we have these boxes we have this one box called payments we have this called offices we have employees customers product clients products orders and audits so each box in an entity relationship diagram shows you a table okay so for example offices is a table and then inside each box this is the list of columns in the table now the ERD does not contain any actual data it is sort of a blueprint for the database on what tables it contains and what are the fields within each table and how the tables are connected so the office is stable for example will contain an office code and you will see that this is you can see a key icon here if I zoom in a little bit so this is a key icon so that is simply to indicate that the office score is a unique identifier for the office and this is also called a primary key so the office code is the unique identifier or the ID just as on Jovian you have a unique ID which is your username so similarly office code is a unique ID and then the office is based in a certain city it has a certain phone number it has a certain address address line one address line two a state country postal code territory and maybe even a location a geo location for plotting on a map okay and different ways in which erds are created may or may not contain this additional information for example here what this blue square represents is that this is a required detail if it is filled in and if it is not filled in then that means that this is optional so you can have address line 2 may not be present for some offices because it's enough to summarize the address in one line but yeah you may not always have these details and this ERD does not contain information about the types of data you can probably guess office code would probably be either a string or a number a city would probably be a string phone would be a string all of these would mostly be strings then you have employees so for employees we have an employee number so that's the primary key and I think that is going to be a number then there is a last name first name extension so this is probably employees work at an office so at the office what is the extension to reach this person via phone you have an email for the employee and interestingly there is this reports to feed so most employees in most companies report to other employees of the company so this reports to is some sort of a link from employees to the same table of employees so reports to would probably contain an employee number of another employee in the company then you have job title and then you have something called office code so office code again is going to be the office code of the office where the employee works so you can already start to see these relationships employees work at an office so there is an office code in employee and that office code should match with the office code of a particular office and then you can do interesting things like find all the employees who work at a particular office or maybe given an employee find the full phone number which is the phone number of the office combined with the extension for this employee and similarly you will now see other relationships you have customers so customers have all this information and then in customers you have the sales rep employee number so this is going to be a reference from the customers table to the employee table the employee number should match up with an existing employee then you have payments done by customers so customers pay by check so you can see here there's a customer number which is a reference field then customers place order so there is a table where we are recording orders where we have an order number order date some information about the order some comments and there is a customer number indicating which customer has placed the order but of course an order can contain a lot of information so you can place you can have many products that you would have or that you can include in an order so we have a table for products like product code product name product code is the unique ID product name product vendor Etc and product code there is also a product line which itself is another table so you may have four or five product lines and in each product line let's say the product lines are cars motorcycles ships Etc remember this company creates small scale models of vehicles so product lines could be the different kinds of vehicles and the products could be then individual vehicles or individual actual products that they create and then there is this order details so an order can potentially have many products that were ordered so in order details so for one order you may have several records in order details and you may have the order number you may have the product code and then you may have the quantity ordered so let's say in order number one the customer customer number 10 creates order number one and in order number one they request product number five product number six product number seven and each with a different quantity so order details is used to track all that information okay so this is how all of this fits together you have tables with columns and then you have relationships between tables and uh understanding and entity relationship diagram is the first step towards understanding what a database contains and all these symbols have certain meaning which we will talk about the next time when we talk about joins in one to many and many to one relationships okay now there are several relationship there are several relational database software packages and some of them are free and some of them are open source While others are paid and proprietary and the most common ones are these MySQL is an open source and free option they also have a paid option but the free option does the job for most people then you have postgres you have sqlite you have Microsoft SQL Server Maria DB Oracle IBM db2 so some of these are proprietary so for this tutorial we will use MySQL a free and open source relational database software now the concepts that we learned today will be applicable across any of these but the syntax the actual code that you need to write May differ so just keep that in mind and just try to understand the the key Concepts and we'll talk about how you can look up the differences in code Maybe by comparing let's say with the postgres database and primarily it's simply a matter of looking it up on the documentation or finding a stack Overflow answer over time once you join a company most companies will only use one type of database so over time you will become comfortable in the flavor or in the kind of in the software package that your company uses and the variations in SQL in the SQL language that software package requires but your skills should be transferable across different SQL variants Okay so we've been saying SQL a lot or SQL what is it so SQL stands for structured query language and it is a programming language for interacting with relational databases now we've set up this database somewhere and now we need to put data into it and then we need to get data out of it maybe we need to change some data within it for doing all of those things you use the SQL programming language and unlike general purpose programming languages like python Java C plus Etc SQL has a very limited syntax and a very specific use case the specific use case is to interact with a relational database it's you cannot use SQL for data visualization for example you cannot use SQL for building software building web applications you cannot use SQL for creating scripts you cannot use SQL for machine learning it's that is not the purpose of SQL the purpose of SQL is to put data into tables into relational database tables and get data out of relational database tables and make changes so it's all about working with a database without a database there is no SQL and this is what its syntax looks like so this is all one single SQL statement it's split across multiple lines but you will see at the end there is a semicolon indicating the end so here what we're saying and SQL is a very readable language so here what we are saying is select top three so we want to select the top three products so we want to select product ID from the sales table and you wanna select the product name and you want to select the total quantity of products sold from the table sales and then you want to do a join so again there is some joining merging involved and then you're doing some kind of a grouping and then you're doing some kind of an order by and then you're doing some kind of a sum and then you are sorting it in descending order so you have all of these things that you're putting together there are many pieces that are coming together here and we will learn some of these pieces this week and some of this pieces next week but by the end of these two weeks you will be able to understand exactly what this statement does and you will be able to write statements like it and it's not too difficult it's just that you have to understand what each of these parts represents okay and that's what we will go over step by step so you type in a SQL query and you send it to the database and then the database gives you a result so typically it is some sort of a subset of the table like here we are selecting a certain set of rows so that so the database is given just three rows of data and it has given just three columns of data the actual table or tables that this information has been drawn from can contain several thousands or millions of rows and can contain several columns as well so you don't always want to see all the data sometimes you just want to see the data that you are concerned with and sometimes you want also want to summarize data by taking the sum Etc okay now a quick note on SQL statements there are three types of statements in SQL one is called the data definition language so there are three parts to setting up a database first or using a database first is to set it up which means taking the entity relationship diagram and converting it into an actual database with actual tables in your local copy of the software or somewhere in the cloud okay so here we are concerned with generating a table generating a database or modifying the structure of a table making connections between tables that is called the data definition language or that is one of the parts of SQL then you have the data control language the control language is primarily about managing user access rights so for any database system you have a way to assign permissions to users so you can create users and then you can give users permissions and some users may have read permission some users may have write permissions sometimes you can also control information per table so you can control who can write to let's say the employees table so only the administrator should be able to create new employees but maybe all employees should be allowed to create new customers right so you have all these rules and that is also something that you can do with SQL not something that we will cover extensively because this is not something that you will have to deal with maybe the database administrator will already set this up for you but the most important thing that we will deal with is the data manipulation language which talks about which is the most frequently used subset of SQL and it is used for searching data so if you have a database getting some data out inserting data into a database updating a database updating some information that's already there and deleting data right so the crowd operations create read update and delete those are the operations covered in the data manipulation language now you don't need to really worry about these terms ddl DCL DML you never have to deal with them but it's just that you have three there are three ways in which we use SQL statements to create a databases and set up the structure to manage permissions and most importantly to actually do crowd operations on the data and before we start writing some SQL code a quick note on the syntax so SQL syntax is case insensitive which means that you can type statements in uppercase lowercase or a mixture of both like here we have select top three sales dot product ID you can write select in lower case you can maybe write S as a a s and lowercase and E in uppercase l in lower case it won't matter same is true with all the column names most of the time in most SQL distributions they are case invariant so you can use uppercase lowercase whatever seems more convenient to you okay and one thing that you will notice is at certain places we will use these back ticks or back codes so this is called a back code especially for database names and column names and table names now these are optional so wherever you see this character it is completely optional the reason it's there is primarily just to differentiate between the SQL syntax and keywords and actual table names and and such can we see this in just a moment then SQL statements can span over multiple lines so you can write three four five ten lines in a sec in a SQL statement and you have to end it with a semicolon that's the important piece if you don't put in a semicolon then your statement may not get executed or the system is still keep waiting for some output and then finally all the statements that we are going to execute in today's tutorial can be executed on MySQL now this MySQL can be running on your computer it can be running somewhere on the server and it can be accessed in two ways which is the command line or the workbench and we look at both in just a moment okay now the SQL syntax as I mentioned for every relational database software package is slightly different so if you're using postgres or you're using a Microsoft SQL server or Oracle it's going to be slightly different so just check the official documentation of your database for details what we will learn is the kind of operations that you can do on database tables and the specific syntax is just a matter of looking it up all right so with that let us set up MySQL server locally now you don't need to do this right now but whenever you're following along with this notebook a definitely set it up locally on your computer and the download takes a while that's why I'm recommending you don't do it right now but do set it up locally on your computer and experiment with all of this code all of the things that we are doing to see the effect that these statements have okay so you can download you need two things you need to set up the MySQL server so here's the MySQL server you can go to dev.mysql.com download slash MySQL and select your operating system in my case it's Mac OS and then select the kind of archive you want to download you can see that this is a 415 MB file so it takes a while to download now I've already done the download so I don't need to do this again but if you're on Linux for instance then you may want to either download a tar archive or if you do download a tar archive then you may have to follow some additional instructions as well if you're on Windows I think you should be able to download yeah you should be able to download a zip archive that you can directly open up an Exe on I think on Lin on Ubuntu you should be able to download some kind of installable bundle so whatever operating system you're using you should be able to download one version now we just need the MySQL server we don't need anything more than that but and the MySQL server will set up a database for us and allow us to create tables within databases and query databases and so on and you can run it and we are going to run it locally but it can just as well be hosted on a server as well right so but we are going to run it locally to interact with the MySQL server one way is to use the command line interface so now in my case I have installed it on Mac so I'm just going to open up a terminal and let me zoom in this terminal a little bit yeah so now I have opened up a terminal and when I installed MySQL after downloading it created this folder slash USR slash local slash MySQL so it created this folder and you can see this folder here if I do LS and where did I find this well I just looked it up where does MySQL get installed on Mac OS and I got this response where it's in slash USR slash local MySQL in your case in your operating system this location may be different this may be in C program files or this may be in somewhere else on Ubuntu so you would have to look this up but in this folder slash user slash local MySQL there is a folder called bin so I'm just going to type slash bin here and then there is a folder called let me just check LS on bin so you have a lot of things inside bin but the one we should be concerned with is MySQL the command line interface so I'm just going to run slash user slash local slash bin slash MySQL so now this is a command line tool that I'm going to run okay and to run this the first thing that you will need to do is you will need to provide a username and by default when you install MySQL it will have the username root and then you also need to provide a password when you're installing so when you download this file and then you're set doing going through the installation steps it will ask you to set a root username and a root password in my case I set my root username to root that was the default option and I did set a password as well so here is how you specify the username and the password so you say you give the path to the MySQL binary or the MySQL command line tool and you specify using minus U which user is trying to access it and root was the default user that they had that it had set up and you specify minus p and it will prompt you for a password so I'm just going to type in the password Here yeah so I've just typed in the password now and now what has happened is MySQL was already running on my computer after I installed it but now I have logged in into this database server which is running on my computer as the user root okay and if MySQL is not already running on your computer then you will not be able to log in so if you get an error here that MySQL is not running then you would have to go and check in my case what happens is on Mac OS yeah so on Mac OS it creates a it creates this MySQL option in settings so if I go to my system preferences it creates this option MySQL and here I can actually stop and start my server here I can also just set up some configuration about where MySQL is going to store all of its data right so by default MySQL will start up whenever my computer starts up so I can set all these configurations right so the MySQL is is a application that is always running on my computer and it starts up when the computer starts up it shuts down when the computer shuts down you will have to set it up separately on Linux or on Windows and when you do install it normally it all of this should be taken care of automatically okay so make sure that MySQL server is running and find the path to this MySQL binary which is on Ubuntu also I think it's user local MySQL bin MySQL on Windows it might be slightly different and then login into MySQL so login into the server as the root user and once you've logged it into the root user you will see a welcome message and you will see the version of the MySQL server that's running on my computer and now I can start interacting with it on this prompt so as you see here it says MySQL and then I can start typing SQL commands here okay so that's one way to interact with this database and I am currently running on my local machine but if there was a MySQL server that was running somewhere on the cloud and I had the URL to that server so I could also put in the URL there's a way to specify the URL when running this tool and you can use this tool to connect to any mySQL database anywhere in the world as long as it is publicly accessible and you know the username and password for it okay so if you have the URL to a to mySQL server you can log in into that MySQL server as well like for example I from my computer can log in into the Jovian production MySQL server and maybe make changes to my username and things like that okay all right so that is one way to access MySQL that is one way we will look at today but another way to access MySQL is using the MySQL workbench so there is another installation link that we've included here this is to download the workbench which is basically this graphical user interface so you can download the workbench this is a again 100 MB download and the workbench also let me open it up the workbench also connects to a MySQL server so when you log in for the first time you will see that it automatically detects that you have a MySQL server running locally but if you want you can also connect to something that is running somewhere on the cloud so again if you have the IP address or if you have the host name if you have what port it is running at if you have the username and password for a database server running on the cloud then you can get those details and connect to that database so typically in a company setting what will happen is the team that you're working with or the person that you're reporting you that you're reporting to will tell you that here is where our database lives so you should set this IP for our database you should set this port you should use this username we've created a username for you you should set this password and then you should then you can click connect or you can even test the connection first and then you can click connect and then you will automatically get connected to the company database and then you can work on the company database or your teams database and get data out of it or write data into it do queries on it in our case today we are going to just use the local installation that we have of MySQL so I'm just going to connect MySQL workbench to the local installation all right so this is what it looks like and the kind of queries that you type here for example I can type a query so for example I can type a query here on the MySQL command line tool show databases and I can type the same query here in the MySQL workbench show databases and then I can run it so this is the way to run it or you can also press Ctrl enter or command enter to run it and it shows the same information so here it shows the information classic models information schema MySQL performance schema Etc and here it shows the same information as a table okay so it's just a little nicer here everything is shown as text here you can interact with it a little bit you can click on things and such so those they are just two different ways of interacting with the same underlying database that's running locally right now and both of these can also connect to things on the cloud okay so with that out of the way we can finally start working with databases so let us set up a database for classic models in my case I have already set up a database let me just remove that database for now okay all right so once you are connected either through the command line or using the MySQL workbench to a SQL Server so the server code again could be running locally or on the cloud you can start writing SQL statements so the first thing that you want to do as soon as you're connected to a database server is to just list the databases that are present on the server so I can just type show databases and put a semicolon and that is going to show me the list of databases that are present on the server so by default right now I've just set up this MySQL server on my computer so these are all some of the default databases that MySQL already has so we definitely don't want to disturb any of these switches information schema MySQL performance schema and sys okay but once you create more databases you will start to see those databases showing up here so that's the first thing show database is going to show you a list of databases then the next thing you can do is create you can create databases so here is how you create a database you type create database and then you type the database name so I'm going to create a database classic models and let me just run the statement so I've typed create I've typed database and I've typed the name of the database classic models and now the database is created and I can check show databases once again and now you see you have classic models here as a new database that has been created now in this notebook what you will see is the statements are typed in uppercase create databases uppercase and one other thing you will notice is that all database names column names table names all of these are lowercase and they are indicated with these back ticks this is called a back tick this is just for visual separation so that you know that this is this is a SQL keyword and this is the name of a column or a database or a table so this is just for that visual separation so that when you're going through this notebook it's clear to you and you can also run it like this you can take this code and you can run it exactly the same way as it is typed here or you can type it without any of the back text and just in lower case and this is also perfectly fine except that it's a bit difficult to read okay so now that we've created the the database we can now show the database as well grade and if we try to create it again MySQL will give us an error so you can see here we run create database classic models and it says it can't create a database because a database already exists so we can also specify a condition to create the database only if it or doesn't already exist so if we say create database if not exists classic models so this is when we don't know if the if the if there's already a database with that name on our server so then we can create that database if it does not exist if it does exist then nothing happens so if we type this create database if not exists you can say you can see that it says query okay one row affected but there was no real change like if we say just show databases you can see that we have classic models information schema MySQL performance schema and sys so no change here on the other hand if we had typed create database if not exist classic models too and by the way I'm using the up Arrow key to cycle back to older commands so up and down is how you cycle between older and your commands so if I type create database if not exist classic models 2 you will see now that we have classic models and classic models too which got created okay so now we have several databases on our server and typically for every project that you're working on you would create a database or sometimes every team would have a database on the database server so there are many different reasons for why you may want to have different databases but for classic models the company we're just going to create this one database okay and when you are connected on a server and you want to work with a certain database let's say you want to get data out of the database you want to add tables remove tables add rows remove rows you have to tell which database you want to use so we can say use classic models and once you say that then MySQL is going to change the database to use or is going to point to Classic models as the default database to which all operations will be applied so let's say now if we do something like a create table operation which we'll see now and then we type the name of a table and the columns that it has then that table will get created inside the classic models database okay so whenever you get started always make sure that you are running the use statement to select the database that you want to operate on typically MySQL remembers this so you won't have to do it over and over but whenever you log in it's always a good idea to just run use classic models all right so that's how you use a database and one last thing I want to cover is how to delete a database to delete a database you just type drop so we have currently classic models classic models two information schema Etc so I'm just going to do drop database classic models too and a semicolon and now if we check show databases you can see now we no longer have classic models too that database was deleted I can also go ahead and drop classic models and then you will see that now we no longer have classic models either okay now be careful about drop because as soon as you run the drop statement the data will be deleted permanently there is no trash and there is no warning there is no confirmation and typically your company will not give you access to drop databases for good reason but regardless you should be very very careful about running the drop database command okay so as an exercise you can now just try creating deleting using some databases using the commands that we've covered and also try and figure out how to rename a database so now you can try and look it up on the MySQL documentation or just by searching online how to rename a database okay next just before we move forward let's just recreate the database classic models because we are now going to create some tables with it so I'm just going to run create database if not exist classic models and use classic models all right so now our database is created and we can start using it okay let's now talk about how we can work with tables now we remember the entity relationship diagram that we created for the classic models company we can now start adding some tables based on the ERD and there are a few statements there are a few four or five statements that we need to understand to work with tables the first one is to list the tables in a database now once you select a database using the use command you can list the tables in a database using the show table statement so if we go here and we say use classic models and then we say show tables you can see that currently it says empty set because we've just created the classic models database so it does not have any tables but we know that it is empty so we know that much then to create a table we can use the create table statement and here's what the create table statement looks like you type create table and then you type the name of the table and then you have this you open these brackets or these parentheses and then you type the name of a column and then you type the data type of the column and then below it you also have some constraints about the tables or some other information that you want to specify about the table so I just want to show you an example of what a create table statement looks like so here is the ERD diagram for the offices table now for the offices table we have this office code which is the unique identifier for each office or this is also called the primary key in SQL then we have all this information City phone number address line one this is all required and then we have address line two State this is optional information then we have country postal code territory this is required and then we have office location which is optional so this is what the create table statement for office for offices looks like so we say create table offices and again you can skip these backticks these are just for convenience sometimes column names can have spaces so to handle things like spaces we have backticks but here you can just use you can just use it to visually identify what is a SQL statement and what is a name of a database or a column or a table so we say create table and then we type create table with the name offices and then we open up this bracket and we say office code so that is the name name of the first column and office code is going to be aware care so this is the data type and we'll talk about the data types and so it is going to be a character list or a variable number of characters that's what it means where care of up to size 10. so office code can be one one character two character three character up to 10 characters then we are specifying here a constraint that it is not null which means that it cannot be empty then we have City again which is a wirecare 50 so it can be a string where care simply corresponds to a string essentially it can be string up to 50 characters long again not null then it will have a phone number again it will be a string not null it will have an address line it will have a second address line and this time it will this can be null so we don't necessarily need to have a second address line it can have a state again this information can be null it can have a country this should be should we not null a postal code a territory okay so territory would typically be I think it would probably be country or maybe the territory which the office operates in okay so we'll see an example of this and finally at the end of specifying all the columns we are also specifying this constraint or this additional piece of information that we want office code to be the primary key for this column so what this does is the primary key constraint uniquely identifies each record in the table so the office code value in the office code column will uniquely identify each office and what that means is that you can only have one row with a specific office code you cannot have two offices or two rows of data with the same office code and it also cannot be null and that's why we have specified not null here already okay and a table can have only one primary key constraint so you you can select multiple columns you can say that the primary key is office code comma City although in this case it will not make sense sometimes it does so you can select multiple columns as the primary key so a primary key can consist of multiple columns but you should only have the statement written once so if you want to select office code comma City so both of those together as a primary key then you you should write primary key office code comma City and not primary key office code and in another line primary key City okay that's a more special case that doesn't really happen so for now let's just take this code and let us run it here in our MySQL command line okay and we run it and as soon as we run it it says query okay zero rows affected so whenever you see okay that means that it has had the intended effect so now if we run show tables again you will see now that there is one table in classic models the table is called offices okay now talking about the data types you see wire care if most of these are bad care but there are several supported data types in SQL so here are some of them you can have numeric data you can specify that a column is a bit bit means it's going to be a zero or one or a tiny integer I think that is going to be one byte which means it can take values from 0 to 64. a small integer I think that would be four bytes a normal integer I think that would be a small integer would be two bytes a normal integer would be four bytes a big integer would be eight bytes etcetera so the differences between all of this is simply in the range of values that they can hold bit Can Only Hold zero to one big end can probably hold from minus 2 billion to 2 billion so depending on what range you think that your numbers can take you can choose the right data type and if you're unsure just go with int or begin just to be safe a lot of companies have had to change from int to begin once they hit maybe a billion users so not many such companies but that is what that was a real problem they probably didn't expect to hit a billion users and they had to change from in to begin for user IDs numeric user IDs then you have the decimal data type so if you're sure about how many digits of decimal you want then you can use the decimal data type as well or you can also use a numeric data type I think this allows you to put in any kind of data something that you would have to look up you can use the float data type to work with floating Point numbers here you can specify how many digits of precision you want and then you have the real data type as well so again not all of these data types are supported by all SQL software because every all companies have implemented their own version and they have their own optimizations so you would have to look up whichever package you're using like for MySQL you would have to look up which of these numeric types MySQL supports and then there are date related types so you typically have a date time and then a date time or a time stamp data type for working with dates then you have the character types so you can have a character which is a single character or you can also specify when creating a cad data type how many what is going to be the exact length so if you have if you are sure that you will always have five characters in a column you use care or care five so just as we have written wirecare 50 you can write five and then you always have to put in five characters or you can put in where care and wear care means that you can put up to 50 characters so if you type wirecare 50 that means you can put up to 50 characters and then you have the text data type which is typically used for very large strings so if you want unbounded if you don't want a limit on the number of characters then you can use the text data type now what is the benefit of not using text maybe using wire care it is efficiency when you can specify the number of characters your data can take based on that the database server can perform some optimizations to make queries faster but if you want to use the text data type then you are giving up on some performance then you have these Unicode characters which is a special case of characters you can also store some binary data and then you can also store something called a blob which is like a large binary object so you can store XML Json and these are more specialized types that are not supported by all SQL servers but numeric date and characters or strings are the most common data types okay so that was data types and next Once a table is created you can also check once the table is created you can also check the table that the table is created using show tables here we've typed show tables and we were able to see the table and if you want to get information about what columns the table contains you can use the describe command so let's type describe and let's type the name of the table the name of the table is offices and when you type describe offices you can see here these are all the fields office code city phone address line State country postal code territory all of these are where care some of these are nullable which means some of these can be null and some of these cannot should not be null and office code is the primary key okay so still this is still information about the table this is not information in the table it's just information about all the columns of the table and you can also specify default values for some of these columns let's say you want to specify a default value of phone number to the phone number of the headquarters so that there is always a number listed on your website so you could have specified that the way to specify your default is to say let's say phone number not null and you could instead say here phone number default and then you can give the phone number that you want to have as the default phone number okay maybe plus one that's the default phone number so that will be the default phone number that will be put in for any Row in the data okay so now we've created a table we have seen what its Fields look like we have seen that that table got created let's put some actual data into the table so to insert data into the table we use the insert into statement so here's how the insert into statement works you type insert into and then you type the name of the table so the name of the table is offices and then you type the columns the names of the columns that you want to specify so I want to provide the office code I want to provide the city I want to provide the phone I want to provide the address line one and maybe I won't provide address line 2 because I don't have that information I am not going to provide state I'm going to provide country I'm going to provide postal code and I'm going to provide territory okay so we say insert into offices and then we give the list of the column names that we are going to supply and then we say values and then we provide the values for these column names so okay now I'm going to insert create this new office with the office code AAA and it is going to be in Bengaluru and the phone number is going to be plus 9 1 that and then the address line is just going to be well let's just put it as 1 mg Road and then we have a postal code let's just put that as five six zero zero one oh we also have to specify the country so the country is India and finally let us put in a territory here so I guess that this will probably serve the territory of Asia right so let's then put the semicolon here so what have we done insert into name of the table list of the columns that we want to supply information for put in then type values then give a list of the actual values for each of these columns and then we can hit enter and you can see query okay one row affected and now this is going to insert the data into the table how do you get data out of a table well the way you get data out of a table is by typing select you can type select and then after typing select you type which columns you want so let's say I just want to get the office code and I want I want to get the city from the name of the table the table name is offices and then I put in a semicolon yeah sorry no no brackets here so you type select and then you type the name of the columns that you want to select and then you say from offices okay and you can see that it starts to get a little bit confusing now because everything is written in lower case and that's why we often write the column names in lower case and we write the SQL commands in uppercase okay and sometimes we also put backticks just to make it super clear and especially if you have names in if we have spaces and column names okay so select office code comma City from offices so now it is giving us the office code and it is giving us the city for all the rows of data in the database in the table offices so we have inserted one row we inserted a row with all this information and then we viewed it using select now if we had inserted multiple rows we would be looking at the data for multiple rows of data how do you insert multiple rows inserting multiple rows is the same as inserting one row you just say insert into offices and then names of columns and then type values and then you give one set of values then you give a comma and you give the second set of values and so on so let's insert multiple rows I'm going to copy insert into offices and then I have a bunch of rows of data here so I'm going to copy all those rows of data okay it's going to be a bit difficult to copy this all right let's just copy it this way now this is a lot of code there so I am going to instead of using the MySQL command line which I can do I can just paste the command paste on the command line here I'm just going to show it to you on the workbench okay so I'm just going to type it here into the workbench you can always just click new query and oh sorry yeah you can just always click this new query here and then just type it so here now we have insert into offices we have all these columns that we want to insert and then the values we have on we have one one set of values two set of values three four five six all the way up to seven so we are inserting seven values and at the end you will notice a semicolon here at the end of the last statement and we can run this command and now we get a success result here oh we need to select a database first so let me cut this and let me say use classic models and let me run that so that has been selected the database is selected then let us paste it back okay now we have insert into offices and now we run insert into offices and you can see that it it executed successfully so now we have inserted into offices and I can create a new query here I can yeah so I can now remove this and I can type select and if I want all the columns I just type star so select star from offices and then I run it and that is going to now show this data in this tabular format okay so now you can see that we have all these offices San Francisco Boston NYC Paris Tokyo Sydney London Bengaluru and this is the same information that we had shown that we had inputted here that is also available so through the command line and through the workbench we are connecting to the same database and we can run these commands in either place and I can type it here as well select star from offices so now when I say star it is going to select all the columns and here's what that looks like it's a bit messed up the output because it's trying to display it all on one line but if I zoom out a little bit you should be able to see the output yeah so you can see the output now when I do select start from offices it is giving me the same table that I am able to see here on the workbench okay so that is insert and select just a quick reminder on how these work so you can say insert into and then provide the table name provide the names of the columns you want to insert provide the values and then provide lists of values to insert so list of rows and if you're providing data for all the columns then you can skip this part which is the column names so you can just say insert into table name values and then you can give lists of values and that will work fine too for example here when we are inserting offices we are using all the columns here so I can actually just completely skip this last thing I can skip this names of columns and I can just say insert into offices values and give it the list of values then to view the data the simplest way to view data is using the select statement so it has the syntax select column one comma column two comma so on from table name and you can also view the data from all the columns using simply saying select star from table name okay so that's how we put data and get data so now we've covered C and R create and read and we'll look at update and delete as well and here are some exercises for you try adding some more entries into the offices table using just the required column so an example that we typed out on the MySQL command line try typing it out yourself try doing it from the command line try doing it from the workbench and see what happens see what happens if you don't provide a value for a column marked as not null okay maybe here if I go back to one of these and if I try and change this let's say I changed the office code to aab and I provide I don't provide a value for territory which is supposed to be non-null let's see what it's going to give us so it's going to give us an error but you want to do you want to make these mistakes and you wanna understand what kind of Errors you get like here it says field territory doesn't have a default value so look through it and try to break things and try to become familiar with errors so that when you encounter them you will be able to debug them more easily so try adding an entry with the primary key matching an existing entry so maybe let's try that let's try putting in AAA again and let's just make it Bengaluru 2. and let's just put in territory Asia here and let's add back territory here so you will now see that okay I probably have yeah there's probably a syntax error here okay I made a syntax error here somewhere but if I just try to insert the same data again like a Bangalore root 2. you will see that now it says that there's a duplicate entry AAA for the key officers.primary and that data is not inserted okay so test these out the more you try the more the better you will understand how some of these things work and try and retrieve and display just the city and phone number information for each office that has been created so just do a select statement and try that out too and that's the first table that we've created we've put some data into the table we have looked at the structure of the table and we've retrieved some data from the table as well next is the employee stable so we've created one table and now we are creating a second table and we will connect this table to the first table and a quick note if you want to first delete a table then you can use drop table so if you type drop table that will delete the table with all its data like if I type drop table and then I type Offices here that's going to delete the offices table I don't want to do that right now because I've put some data into it and you can also have something like this that if the table exists you can drop it so if we type drop table if exists employees this is not going to make any change because if I do show tables there was no employee stable before and there's no employee stabled now but whenever you're creating a table you can always it's a good idea to First maybe drop the previous table if you're sure that you don't need the old data if you want to Fresh table to be created so you can just drop the previous table if you if you want right so that's why we have this statement here for you as well so okay let's talk about creating the table employees so let's copy this code and here we have an employee number so that's an integer our first encounter within teacher and here we're saying 11 so I think what we are specifying here is I I think it might be 11 bytes or 11 digits I'm not sure I think it's going to be 11 bytes I'd have to look it up then we have the last name which is a wire care or a string a first name is a string extension this is their phone number extension so they will be at a certain office and that's why we have office code here and then we have an email which is again a wire care of a hundred and then we have a reports too so two interesting things here one is office code so we have now A office code column and this should ideally contain a value of a valid office code how are we going to ensure that we will see and second reports to and this can be null so office code should not be null every employee belongs to an office but reports to can be null because obviously there will be somebody in the company who does not report to anybody but otherwise reports to and maybe some people may not have been assigned a manager or a reporting person so reports to can be null but reports to should be somebody's employee number let's say you have siddhant and biraj and if barrage reports to siddhant then reports to in the entry for biraj should be the employee code or the employee number of Siddharth okay how do we ensure that we look at that then we have the job title this is just a simple string and we are setting here that primary key is employee number that makes sense we want to uniquely identify each employee with a primary key and this was going to be not null so that's fine too and then we have these two in two statements foreign key so here we are saying that reports to the column reports to references the table employees and in the table employees it references employee number okay that's one and then we have another foreign key office code so here we are saying that the tape the column office code in this table references the column office code from the offices table okay so there are two references here one is a one is a reference to another table one is the reference to this table itself but probably to a different record so what we are saying through this or through a foreign key in general is that we want to ensure that the office code column contains a value which matches a value in the office code column from the offices database now from the offices table right so for every employee there should be an office code and that office code should contain a valid value from the offices table and that is what the foreign key constraint ensures and we will test it out in just a bit and then similarly we have foreign key within the same table we want to say that the reports too for any employee should be a valid employee number from the employees table and it can be the employee number of a different employee so that is or enforced using a foreign key a foreign key is simply you're saying that this number or this column has a special property that it refers to a value from another table and there is one important constraint with foreign key the value that you reference to must be the primary key in that table so for example here we are referencing office code of offices so office code must be the primary key in the offices table now again this is not a criteria that all SQL software or all relational databases have sometimes they do sometimes they don't but in general just to be safe you should always assume that you should only reference primary keys okay so let's try it out let us insert a value into employees so let's just let me just grab this insert into employees the values one zero zero two so that's the employee code Murphy Diane x5800 and so this is what is this employee number last name first name so Diane murphy1002 and their extension is 5800 and their email is D Murphy at classicmodelcars.com then their office code is one and do we have an office code one we do have an office code one one is San Francisco so that means they belong to the San Francisco office then reports To None So they do not report to anybody and in fact Diane Murphy is the president so they're the boss so let's run it oh it says employees doesn't exist or that's because I dropped the employees column I never created it in the first place all right so let's first create the employees column A create table employees let's take this and let us run it here so now the employees column is created and then let us insert some data into the employees column I am just going to insert this one person right now Diane Murphy and that's it and let me just put a semicolon here and insert the person so now if I do select star from employees you should be able to see sorry about the formatting but you should be able to see this information about this person Diane Murphy with the employee number one zero zero two as the office code 1 and reports to nobody let's add another employee maybe let us set their yeah let us set their office code let's set their employee code to 1002 or 1003 and let's call it Daniel and let's give an invalid office code here maybe let's give a hundred and let's see what happens so here you will see that it cannot add the child row so this is called a child so the employee is now called a child of office because it has a foreign key constraint and the foreign key constraint fails you can see that constraint that this should be a valid office ID fails and that's why you cannot add that row okay that's how the whole thing works all right so maybe let's add all these employees and then we will move forward but to add all these employees there are going to be some issues because I've already created an employee with the primary key one zero zero two so before that I'm just going to delete the employee that I've just created you see right now I have this one employee Diane Murphy so I'm going to delete the employee with employee number uh 102. so I can say this I can say delete well actually let's come back to delete later for now I'm just going to drop the table into entirely so drop table employees okay so now the table employees is gone if I do show tables once again and now I can create the table once again I will go create table employees and then let us insert all this data so we have a bunch of employees here I'm just going to capture cap copy all of this and paste this command if you want to do the insertion here so maybe let's just do the insertion here so we're just going to insert all of these employees how many of these we are going to insert about 23 employees using the MySQL workbench and now these should be created so now if we check select star from employees you should now be able to see all the employees that we've created okay now here are some exercises for you try inserting an entry into employees with an invalid office code you saw what happened but try it maybe from the workbench try inserting an entry into employees with an invalid value for reports too okay that is something that we haven't tried so reports too can either be null or it must be an existing employees ID or employees number and try this try creating a new office location first and then try adding some employees to the new location so when you open a new office first you will have to add a new office object or a new office record and then you will have to add an employee add a bunch of employees for those office records and maybe you will have one office manager and that office manager will probably report to the president and then you will have a bunch of employees at that office and they will report to that office manager okay do try out that exercise okay moving ahead now we have the customers table so now at the by this point you are probably getting the idea there is a primary key there are a bunch of required columns there are some optional columns and then there are there is some there is this foreign key so every customer has a sales rep optional sales rep so you can take this foreign key sales rep employee number and that should reference in the employees table some employee number and you can see here that sales rep is an integer but it can be null so I can just copy paste this I can take this create table if not exist so this is another way to go about it create a table only if it not it does not exist all of these are slightly different if you try to create a table without this so if you just say create table customers if there's already a table customers it's going to fail if you drop the table first then it's going to delete all the data if you say create table if not exist then if the table exists it won't do anything if the table does not exist it is going to then create the table so think about what is the right thing you want to do and based on that use these drop and not exist and does exist and play around with them and see what they do okay but I'm take I'm going to take this create table put it into my workbench and just execute it and this should create another table yeah this should create another another table customers and on the left if you just check the schemas tab you should be able to see all the tables that are present so now you see here you may have to refresh it a couple of times but you have this customers table you have employees and you have offices and when you click on a table you will also be able to see the information about that table like on customers you have customer number customer name last name first name so this is our MySQL workbench makes it a little easier for you you don't have to type a lot of commands like describe and such you can just browse the tables that are there you can also browse the information about tables you can in fact even check values within the table I believe if you just click this button it is going to automatically run this select star for you so if I just click this button here it's going to show all the employees by running a select star from classic from employees right and this is one other way to write something you will see often you can also type database name dot column name or sorry dot table name and then put a query so if you're not already set a default database using the use command like use classic models you can just put select star from classic models.employes so this is database dot table name okay so here's an exercise add some customers to the database using data from a SQL file so somebody asked what to can you get data at data programmatically can you add data which is already there somewhere else so let me open up the SQL file and this equal file contains code for everything that we are doing today of this classic models SQL file and in this SQL file you will find some rows for inserting data into the customer table so let's find it let's see so here we have a bunch of create table statements then you have these insert statements for customer for products and then you have an insert statement for offices employees and okay here is where we have an insert statement for customers so I'm just going to grab this this seems like a whole bunch of insert statements here so these are all the insert statements for customers I'm just going to go back to the workbench and I am going to do a new query think not sure which one it is never mind I'm just going to edit it here I'm just going to go to query one paste this all right so it seems like there's a lot of insert statements I'm not sure why they wrote it like this they could have written it all in a single insert statement too but in any case we have 125 customers you can see here you can see the details of these customers they are so these are typically customers are stores remember Gift Stores and toy stores by the products from classic from plastic models so customers are stores and then each customer has a point of contact if I go back here you will see you have a customer name and then you have a customer number customer name is the store name contact last name first name is the information about who to contact their phone number Etc so all that information is captured here you will also see an employee ID so this one is probably the employee ID and then you have okay so here there's also some kind of a location data you see sdg geom from text Etc so you can also put location data into MySQL that's pretty interesting so let's run this let's see if this gives a correct result yeah seems like it was able to insert data into the customers table all right so now we've inserted data for about 120 customers and we should be able to see that here in the response yeah so you can see we have data for over 100 customers and that's great and some of them have a sales rep some of them don't have a sales rep customers also have a credit limit and such okay so now when you start getting to this point where you have a lot of rows in your database then it may not make sense to look at all the data at once that is where you may want to select some data you may want to query some data you may want to filter out data based on a particular condition and that is where the where clause in the select statement becomes useful so you have this where clause so you have this Clause called where that you can include after your select So when you say select star or select some columns from the employees table you can say where job title equals sales rep now if you go back and check the employees table here you can see that employees table there is a job title here and let's say if you only want to get a list of the sales reps so we say select star from employees where job title equals and then whenever you want to put an actual value for comparison you use double quotes very important sales rep okay so select start from employees where job title sales rep and maybe I can also select specific columns so let's say I want I just want to select the last name and I want to select the extension actually let me just select the office code okay so last name first name last name and office code from employees where job title is sales rep and now it starts to become more useful so now we know that at office code one we have Leslie Jennings who is a sales rep at office code 2 we have Julie Farrell who's a sales rep at office code 3 we have George wanu who's a sales rep and so on so the where Clause supports a bunch of operations where supposed to equal to which is what we have used here it supports less than and greater than so both of these work with numbers and with strings and with dates so pretty much all data types with numbers they expect as expect they work as expected 3 is greater than 2 and 3 is less than 5. which strings they compare the alphabetical order so a is less than b and any string that starts with a is is less than any string that starts with b and any so let's say a a is less than a b and you can have this lexicographic or alphabetical order from A to Z is lower to higher you can also have less than equal to or greater than equal to and you can have not equal to now sum variations use this less than greater than some relational database software use this not equal to so let's see what MySQL uses how about we first try not equal to okay MySQL seems to support this but you could also have in some places this kind of a statement so seems like MySQL supports both right if I do star here we can verify if that is the actually the case yeah so we are now getting the list of people who are not sales reps by using less than greater than or we can also do are not equal to here all right so here's what that looks like you can also combine expressions in the where Clause using and and or so let's say you can say things like select column one column two comma from table name where condition 1 and condition 2 and condition three so for example if we want to select the sales rep in San Francisco so what San Francisco let's first open up offices and in offices let's check San Francisco that's office code one so if you want to get the sales reps in San Francisco so we say select start from employees where job title equals sales rep and we can put in the additional condition office code equals one and again we should put this in quotes ideally okay just a single equal to yeah so now you see here we have the data for Leslie Jennings and Leslie Thompson their extension and their email and both of them are in office code one which is San Francisco so that's how you filter data out like I can I'm just showing showing you here it's going to be a little bit bigger here so that's how you filter data out from a database table you use the where clause in where Clause you can have one of these conditions you can also have you can also do something like this you can use in between and like and then you also have this option to negate things so you can also negate a condition by saying where not condition that's another thing to just keep in mind and a where Clause can be used with select but they can it can also be used with update and delete statement so we were talking about delete earlier so maybe let's check out the delete statement once and now from this point on mostly you would just have to look up how certain statements work so here we say delete from table name where condition now remember we had this one office that I had inserted initially called Bengaluru and this side the office code AAA now if you want if I want to delete this office then here's one thing I can do let me go back here I say delete from offices where office code equals AAA and that should have deleted the Bengaluru office let's see so you know you can see that we no longer have the Bengaluru office anymore because we've just deleted it now here I've deleted exactly one column but I can delete multiple columns as well let's say if I wanted to delete if I want to fire the sales reps in San Francisco so then I would do delete from employees where job title equals sales rep and office code equals one right instead of Select star I would just type delete and then instead of just showing this information it would simply remove these rows of data from the database so that's that and let's also check update similar just like delete we have update so let's check update to now here's how update works you say update table name and then you set the value of column column one to Value one column two to Value two column three to Value three where a condition holds true so let's try update maybe let us change the reporting person for Jenny Leslie Jennings and Leslie Thompson so who are we going to set them to report to maybe let's set them to report directly to the president maybe Diane Murphy directly wants to talk to the sales people in San Francisco so we want to get these employees where job title sales rep and the office code is one and we want to update their values so let's see we say update what does that code look like update set column one equals value one column two equals value two okay so we want to set update employees and we want to set their reports to to one zero zero two again we should put in quotes here update employees set reports two to one zero zero two and then we want to have the WHERE and in the where Clause is where we want to add the selection where we are only selecting the sales reps from San Francisco so where job title equals sales rep and office code equals one okay all right so the good thing about SQL is that it's very readable so update the employees table and set reports 2 to 1 0 0 2 where job title is sales rep and office code is one so only for people in the San Francisco office or sales reps you want to update their employee we want to update their reports too and let's just do a select star and let's make sure that that was updated select star from employees yeah so now you can see with Leslie Jennings and Leslie Thompson they now report to one zero zero two okay if we remove this we can also see that no other rows were affected you can see that there are very few other people who actually report to one zero zero two all right so that is the select Clause the where close the update command and the delete command so here are some exercises for you try and list customers in the United States with a credit limit higher than a thousand dollars so you'd probably start out something like this you would say select star from customers where country equals I think it's USA you can check this out so these are all the customers in in the USA quite a few of them but we want customers with a credit limit higher than a thousand dollars maybe we probably want even an even higher credit limit but let's see let's try a credit limit higher than thousand dollars so here you would say country equals USA and then you would say and credit limit greater than a thousand or let's try ten thousand let's see if there are any more than ten thousand more than a hundred thousand yeah so there are nine rows with more than hundred thousand but with a thousand there are 35 rows so you can see that changing the credit limit is going to change the number of results all right then here's one more list the employee codes for the sales representatives of customers in Spain France and Italy okay how would you do that well you would say select sales first let's describe customers so let's say describe customers so we have the sales rep employee number so now we would say select sales rep employee number from customers and then we can put in a where and now we want the location of the customer or the country and we can put in an in and then I think we can given a give it a list of values so we are concerned with we are concerned with Spain France and Italy so we can now put in pain I think we have to use double quotes here so Spain France Italy okay so here is the list of all the sales reps who work with customers in Spain France and Italy okay now I can probably just put put together a list of these so there's one three seven zero one three one three three seven uh one seven zero two one four zero one and one four yeah that seems to be all so there are only about four or five such sales reps and then the question is make another query to list the names and email addresses of those employees so now we can do select let's say first name last name email from employees where employee number in and then give it this list okay so now we have Louis bondor Gerard Hernandez Pamela Castello and Martin Gerard so these are all the people who are selling to customers in Italy Spain and France so this is the kind of q a that you can do using SQL queries here's one more change the job title sales rep to sales representative I'll let you work on this you can use update to do this delete entries for the sales representatives working in London so first you find out the sales rep first you find out the code for the London office how do you do that well you can use a select statement to get the data for London then you use that code that office code to delete the entries for those sales representatives so you'll have a where Clause with delete then you have show list of employees who are not sales representatives I think we've already done that and here's one interesting one show a list of customers with toys in their name so this is where you would have to use the like statement so if I just click through here you can see this is how the like statement works so you can say select columns from table where a certain column is like a certain pattern and the pattern can be a string or the pattern can be something like this you can have a percentage or percentage a so whenever you have a percentage that means 0 or more characters so if you want to just check for a somewhere in between like if you just want to check for or somewhere in between then you have percentage or percentage so percentage represents zero or more characters and then underscore represents exactly one character okay so check out this reference on how exactly it works what I'm going to do is I'm going to just try and solve this answer show a list of customers with toys in their name so let me see select star from or maybe just select the customer name and the country from customers where customer name like and here is where we can put in percentage and then we want toys and percentage and let's run that oops I think I had to close it so let me just undo that percentage twice percentage and let's run that okay so it seems like you have land of toys Incorporated in the USA you have toys of Finland in Finland and you have toys for grown-ups in USA and boards and toys in USA so it's a very basic selection you can't do a lot of advanced regex kind of selection but a very basic selection can be done a very basic search can be done using like okay then you have product lines and products so you have a bunch of product lines like motorcycles flights uh planes cars Etc and then you also have products so you may have different models of motorcycles different makes for which you offer small scale models so here is what they look like product line is a very simple table it contains the product line it contains some description so it seems like it contains a text description here for some reason there's also an HTML description maybe this is going to be used on the website and there's also an image yeah so let's just take create the product line stable once again I'm just going to paste it here and that will create the product lines table and similarly we have the products table as well which has a product code product name product code is the primary key it has a product line which is a foreign key and it has a product vendor description it has a quantity in stock so that's a small integer then it has a Buy price so which is the price at which we are buying let's say the raw materials for the product or sort of the manufacturing price then we have the retail price so this is the price at which we are selling the product so that can be used to just determine the profit on the product so here's our product right and then once again you can use this link to download some products so here we have the SQL file and then from the SQL file let's see let's find some product lines and some products so let me get all these product lines I am going to come back here and I'm going to just put all these product lines in and I'm going to just run this so we have put in all the product lines classic cars motorcycles planes ships trains truck vintage cars and let's also put in a bunch of products let's get all of these products from this page okay quite a few products and let's put it in there there you go so now we should have a bunch of products and a bunch of product lines as well okay now once you have such huge tables and such huge such a large number of products and product lines and such you may want to do some sort of ordering and you may want to also limit the number of results you may not want to look at thousands of results all the time or even hundreds you may just want to look at the top five or top 10. so if you want to order data then you just include order by at the end of a select statement and you just mention which columns you want to alter by so column one column two so first it will try to order by column one if the values in column one are equal then it will try to order by column two and so on and then you have the ascending and descending for example if you want to list customers by country you would say select let's say customer name comma country from customers order by country and now you can see that you have customers ordered by country Australia Austria Belgium Canada Etc you can also then use a limit Clause if you just want to then see maybe just one page of data something that you can display it once so you can also type here limit 10. and now you just see 10 and of course you don't have to use order by with limit you can use a limit without order by as well so if I just do limit 10 so this is now the natural order or the order in which the data was inserted that is the most that is the default order that is used so this is how you combine order by limit and you then you can also combine it by where Clause maybe you can also only check for customers which have a credit limit greater than a thousand dollars so let's say select customer name country from customers where credit limit greater than a hundred thousand and order by country limit five right so maybe also get the credit limit here so that's how you combine all of these here we are combining select we are combining where we are combining order by and limit and this is what that looks like okay so here are some exercises for you identify the five most expensive products from the plane's product line identify the products that are about to run out of stock so products which have a quantity in stock less than 100. and list 10 products in the motorcycle category with the lowest Buy price and with more than a thousand units in stock okay so there are a lot of things you'll probably have to put together everything that we have covered so far order by and not order buy but you will have to put together a limit you will have to put together an order buy as well because the lowest Buy price and you have to put together a couple of conditions which is the category product line and for the product line you will have to figure out what the product line code for motorcycles is and you will also have to add a check on the units in stock okay then you have these orders order details and payments tables so I'll let you build these as an exercise you have the code you have all the information here so you can create the order order details and payments tables using the create table statement and include proper primary key and foreign key constraints so for example order has a customer number order details has a order number and a product code and then payments also has a customer number and the check number is the primary key and then you can use the same sample data that we've been using so far to populate this too okay next I just want to briefly touch on how you can modify the structure of a table not something that we will do right now but if you want to add a new column to a table here's how you do it you say alter table table name and then add and then the column name and the data type and that's going to add the column maybe let's do it very quickly for employees so alter table let's see employees the let's let's check the employees table first employees so describe employees and now let us maybe add the date that they were hired on so we will add we will say altered table employees what's that add yeah add and then the column name hired on and we want it to be a date okay seems like that was added so now if we check describe employees you should see that there is a hired on column and if I want to insert an employee insert into employees if I insert an employee now I can probably then I can probably then add some information let's say set employee reports to etc etc so we can probably set the hired information right so hired on to one zero zero two and let me change this to an actual date so 2020 12 0 1. okay so now if you check employees again let me just get these employees so select star select star from employees where job title is such and data search so you can see now with these employees we have a hired on column and in the hired on column the value is 2020 1201 and for the other employees you will see that the hired on column does not have that value so that's how you add data nothing too special nothing too complicated it's just one more thing to know you add a column and similarly you can drop columns using the drop column command you can modify column using the modify column command you can rename column using the rename column command and here you say rename old name old column name to new column name after alter table so as an exercise try adding removing and modifying one column in each of the tables created and what happens check what happens when you remove or rename a column that is a primary key because that is the primary key which is also used as a foreign key yeah so do try that out so if you change the employee code for example the employee number now that is used in a bunch of different tables like the customer table so what happens when you change the employee number column try it out that's the best part about interactive environments like this you can try out things and you can break things and figure out what happens now you can also place a bunch of SQL commands into a DOT SQL file like this one that we've been looking at so far and then you can execute all of these commands together you can see that here we are dropping the database we are then creating a database we are then using that database then we are creating a table offices we are creating a table employees we are creating a table customers product clients products orders order details payments and then we are inserting data into each of these tables right so this SQL files is basically what's called a database dump that somebody had this database and then they exported it and we'll see how to export it as well but now you can take this data and import it into or just run all these SQL commands and automatically import the data into your SQL Server into your SQL database and here is how you do it so you there are two ways to do it one is through the command line now through the command line let's say go back here I exit I exit the actual command line and let me come to my desktop which is where I have this file database.sql so I have this file here database.sql and I can check this file it contains exactly what I was looking at I have just downloaded this file here so I find my MySQL binary which is in slash user slash local slash MySQL slash bin slash MySQL so this is the MySQL command line tool and then I say minus U root so I want to log in as the root user and then I also say minus P because I want to use a password and then this is the magic part where you use this less than symbol and I'll tell you how to think about this so you have this less than simple database.sql so what you're indicating here is take the data in database dot SQL and passes at pass it as inputs to this command so now we're no longer opening up the console but we are taking all of these commands inside the SQL file and passing it as inputs to mySQL which we are logging into as the root user so when I run this let me just just to make things very simple what I'll do is I'll first just open MySQL slash user slash local slash MySQL slash bin slash MySQL so I'm just going to open this first minus U root minus p I'm just going to open this first and I'm going to drop database classic models okay and let me just do show databases and you can see that we don't have classic models here so now we have we run this code MySQL minus U root minus p database.sql and this should take all those lines of code and it should execute them against this server yeah I'll take a minute or two but it's done and now if we come back here into this or if we just open up MySQL now and just log in and do show databases you can see that we now have classic models and it is now showing a showing up in a upper case because in this script file the database when it was created it was created with this uppercase in uppercase M but as such you can still just say use classic models without upper case we can just say classic models and it should work just fine and now you can do show tables and you have all these tables customers employees offices Etc and now we can do select star from offices and you can see that these are all the offices that we have right so even though it shows up as uppercase here because that's how it was created but you can still access it as lowercase uppercase or this title case or whatever right so we have taken all that information and then we have taken all these lines of SQL code and we have executed them on the database so that's how you execute data one other way to do it is you can also do it this way from MySQL workbench you can click on this or you can see open SQL page it open SQL button so you have this open SQL file button here the second button click on it and go to desktop and just open it up now depending on which database you're using the UI will be a little different but the concept here is that you can do this you can take a bunch of SQL code and then you can execute all at once and it should do the exact same thing so here it drops the database so it's going to empty the database and then it's going to add all this information and now if you just refresh here you should find classic models and inside it you should find these tables and then you can maybe click on employees and you can see all the employees so you have all the employees just fine one other thing you can do is you can export data out so there are a couple of ways to do this one is to use the MySQL dump utility so the same place where you have the MySQL utility which is at let me just exit here so we have at user local MySQL bin again this will be different on Windows or probably different on Linux as well but you can find it quite easily just look it up so user local MySQL bin MySQL right so this is how you open the command line this is what we have been looking at so far but you have a MySQL dump utility so you can call MySQL dump and I want to use the root user and I want to use a password and what is how does it work so we have to provided the name of the database so we provide the name of the database after minus U or even at the beginning so MySQL dump the database that we want to dump is called classic models and then there are certain options that you can specify that do you want to have these drop table lines so if you see here before every table creation there's no doubt there's no drop table statement but we can add that drop table statement as well by specifying this option and again you would just have to look up the documentation online to figure out how exactly this all these options work but yeah MySQL dump draw add drop table classic models minus U root minus p okay and that has created this SQL file oh but we forgot to write it to a file we want to actually write it to a file so right now it is just printed it out so what we want to do is instead of printing out all this information we want to write it into let's say DB dump dot SQL okay and just like that this should be now there should be now this file dbdump.sql we should have all the information that we're looking for so it has it has this drop table if exists customer then create table and then below it it has the data for customers so insert into customers and you have all this customer data and then you have data for employees then you have data for offices and so on okay now there seems to be some encoding error here for some reason this is treated as binary I think it's probably the office location that is causing an issue but as such everything seems to be just fine and then you can take this and then you can use that to reload the data now here it does not have the commands to actually create the database I'm sure there is some option that you can specify in SQL dump to create the database if it does not exist and drop an existing database as well so I'll let you figure that out but yeah but this is what we are interested in and this is something you can do if you connect to a remote database right now we are connecting to a local database but if you connect to a remote database on some server you can export all its data into a SQL file and then there are converters in in pandas what you can do is you can actually load up SQL files maybe let's try that maybe let's do file open here and let us upload I'm not too sure but I think this might work so let us upload database.sql yeah and you should be able to do something like this import pandas as PD and then PD dot read SQL and let's see oh I think this needs to be a SQL command and a connection so it's not going to work with a SQL file you need to actually connect to a database so we look at it the next time but yeah but there are ways to read that SQL file there are ways to convert SQL files into a bunch of CSV files I know that there are ways to convert that into a bunch of different formats so exporting it is useful because you can then load back that data into a new database server or you can also convert it into different formats so try this out on your own download the SQL file import the data from your local installation of MySQL server and then export the classic models database into a SQL file then drop the classic models database and then recreate it using your exported file so make sure that your exported file can actually recreate the data that's an important thing you should be able to do okay so with that we reach the end of today's lesson there's not much more that we're going to cover today there is a lot more that we need to cover in SQL but we'll talk about the next time but here are some challenges for you to try out so this classic models data set is taken from this website richardwatson.com this is I think this is a professor and they teach a database management course so you can and there are a bunch of exercises here so today what we've covered are mostly the single entity exercises but you also have these other exercises which require some joins and some other things which you have not covered yet so you may just want to try out this prepare a list of offices sorted by country state and city this should be straightforward how many employees are there in the company okay this will require some sort of aggregation or you can just look at the list of employees but yeah but one thing you should check out is you can check out things like count where's count yeah count average in sum so you can do something like this select count column name from table where condition and you can use that to answer this question how many employees are there in the company so here is how I would do it I would say select count star from employees and just use classic models okay select select count star from employees and it tells you that there are 23 employees then here is a question what is the total of all the payments received so let's see select star from payments and you have all these payments and now among these payments there is this amount column so if you just want to get the total amount we say select amount and then we can do a sum how do we do a sum well I think it should be there here somewhere so we can just do a sum on the column so we do select sum of amount from payments so this is the total payment that we have received now you can do things like what is the total payment we have received from customers based in the USA or from a particular location and you can do like a group buy as well and we'll cover Group by and all the next time then you have questions like list of product lines that contain cars and Report total payments for October 28 2004 so here's a where Clause report those payments greater than a hundred thousand dollars list of products in each product line how many products in each product line okay so this might be worth figuring out you may have to use this as a reference w3schools is a good reference here you can check out and see which one of these might be useful for you then what is the average percentage markup on MSRP on the Buy price so now you are comparing you're doing arithmetic between two columns again something that we'll try and cover the next time how many distinct products does classic models sell report name and city of customers etc etc so a whole bunch of questions and whichever you are not able to figure out right now using what we have covered today just try and look through this documentation so this is everything that MySQL supports is listed here most likely you will everything that you need will be covered in this MySQL SQL section if you just go through most of these you should be able to answer these questions and then wherever we have more one-to-many relationships or any relationships between multiple tables we will look at the next time so the next time we will look at using functions for advanced querying on strings dates timestamps we will look at aggregating data using count average sum min max we will look at using joins to combine and query data from multiple tables at once we'll also see how to improve query performance with keys indexes and transactions and finally we will look at how you can connect to a SQL database from python for querying and visualization in Jupiter so we'll look at how to do it using pandas and we'll also briefly try and look at orms so there's a bunch of things to cover here but the more important thing in SQL is practice so you wanna definitely try out these challenges and there are solutions for these challenges as well you can click the solutions link and use that to check the solutions for example there was this query about what is the average percentage markup of the MSRP on Buy price this is question number 11. so let's see question number 11. okay so seems like we are doing some sort of a calculation here so average of MSRP minus Buy price divided by MSRP multiplied by 100 as the average percentage markup from products okay so we'll also talk about as the next time now a few resources for you one is the SQL challenges for sure then the other one is the W3 schools reference it's a great guide to just quickly look through all the different SQL all the different SQL commands and all the different SQL statements and keywords that are present then there is this book called dbbook.com so this is dbhyphenbook.com It's called database systems Concepts uh you can get this book if you wish it's a great book but you should also check out these slides so they have made the slides available for free online and these slides are a great comprehensive introduction to SQL and relational databases and pretty much everything to do with a relational databases so you can use these slides just whenever let's say you're preparing for interviews or if you want to get a deeper theoretical understanding of how the whole thing works do check out these slides I think you'll find them useful now you just need to look at part one relational languages where we talk about relational models introduction to SQL intermediate SQL Advanced SQL Advanced is already beyond what you will need to know but if you cover these four or five slide decks and maybe also the introduction then you will pretty much Master SQL at least you will know everything there is to know then you'll just need to practice then you also have these slides on database design which is about the entity relationship diagrams and models you don't need to know this as a data science practitioner generally somebody else is going to do the database design but if you want to interpret entity relationship diagrams correctly then you should check this out this might be useful for you the rest of it is not really that important not something that is relevant here it goes into the internals of databases but definitely check out chapters 2 to 5 of dbbook.com so you want to go to dbhyphenbook.com go to the seventh edition and click on slides and here you have um all these slides that you can access here they also have some sample tables just like we had this SQL file for creating the classic models data set similarly you have sample tables for creating this University data set so where you have departments and instructors and students and courses and prerequisites and students take courses and instructors teach courses and then each course is taught in a section with a certain time slot and then instructors are also advisors for students so it's a interesting data set it has about 2000 rows of data and then there are a bunch of questions that you can try and answer on this data set as well I think they are mentioned somewhere here in the yeah exercises so you see there are some exercises here on accessing the database basic SQL intermediate SQL Advanced SQL yeah so here so there's a a SQL is all about exercising all about trying out these exercises so dude maybe we'll try and put all of this together into a single notebook and we will also see we'll also find a way to do it so that you can type all the code in Jupiter and get the results immediately in fact Jupiter just like python there is a way to type SQL directly into Jupiter and interact directly with the database so we'll try and set that up for the assignment but in the meantime do check out dbbook.com you also want to get familiar with the workbench or whatever tool your company is going to use so don't lean too much on Jupiter for this particular course especially and SQL in particular become used to this workbench become used to working with the command line and typing commands there because that is how you will be working with SQL databases in the real world okay and we will touch on how to access them using python 2. more SQL exercises on w3resource.com you have more exercises hundreds of exercises really and that's all SQL is about exercises and more and if you really want to read through my sequels reference manual then you can check that as well although you would almost never need to do that and that's it so we've just covered the use cases and the design of relational databases in SQL we've looked into the installation of MySQL and how to set up a database locally we looked at creating modifying and deleting databases and database tables we looked at SQL data types and constraints like primary key and foreign key we looked at the create read update and delete operations on database tables and we looked at exporting and importing data from relational databases the topic for today is aggregation and joins with SQL last time we looked at some basic SQL commands we saw how to create databases we saw how to create tables we saw how to put data into tables how to get data out of tables all the crad operations which is create read update and delete and we also looked at basic querying using select with some conditions today we are going to look at aggregation grouping and pagination in SQL queries then we will look at mapping functions arithmetic and how to work with dates we will also combine data from different tables using SQL joins and we learn how to improve query performances with indexes and views we will also talk about how we can execute SQL queries using Python and SQL Alchemy towards the very end and as I've said this is a continuation of the previous tutorial so do check out the previous tutorial if you haven't done that already this contains all the basics of SQL and we will be setting up a MySQL server locally so typically how a relational database works is you have to download this software called the server in a server in computer science is simply a program which runs in the background and listens for instructions so the MySQL server is something that you can download from dev.m.mysql.com this is just a package that you download to your computer and then you install it on your computer and once you install it on your computer you can start up a database server on your computer so that software is running now in the background and you can then issue instructions or send instructions to the database server in the form of SQL queries like creating databases creating tables putting data in taking data out Etc now depending on your operating system the way to start and stop this server is going to be different in my case what MySQL does is when I install MySQL Community server it adds a system preferences pane so inside my system preferences this is the Mac system preferences there is a MySQL page that gets added but in your case this could be different so make sure that you have downloaded MySQL server the software and then it is running on your computer and then anytime you have a server running you need a way to connect to that database server so there are multiple ways to connect to a database server one of them is to use the MySQL command line which is typically installed at some kind of a location like this slash user slash local MySQL this is on Mac again the MySQL command line tool based on your operating system will be at a different location so check out the documentation or search online on where you can find the MySQL command line tool once the community server is installed so one way you can connect to the server and issue commands to the server is by running the MySQL command line and here you specify that you want to log in as the user root so when you install the server you're asked to set up a password for the database server and a username so the default username is root and you have to set a password so in my case I have set up a certain password and now I am connected to the server and now I can issue commands to the server for example I can say show databases and that's going to show me the databases I have but what we're going to do is use software called the MySQL workbench so this is the second piece of software you'll have to install and I have given you a link here dev.m.mysql.com download slash workbench so go to this page and download the MySQL workbench for your computer for your laptop for your operating system and then open it up so now we have a server that's running on our computer and then we have this workbench which can connect to a server and gives you a graphical user interface to connect to that server so one way you can connect to your service click plus and here you can then provide this is a connection name that you can set but you have to provide some parameters by default this is set to connect to a local machine a local database server on your computer but again if you have a database server that is somewhere on the cloud you can simply ask for the host name Port username password and then you can connect to that server as well so MySQL workbench does not just collect connect to your local database it can connect to any database on the cloud if you have the credentials and when you join a company they will give you the credentials to the server in my case I already had the local instance set up here so I just clicked on it and now I'm connected to my local instance for example if I just type show databases that's going to show me the databases that I have here which is classic models information schema MySQL and performance schema and then I can run use database in all my select queries okay so that's the setup you need to do that's the software you need to have installed and let us know if you face issues and I hope the server and the workbench a separation is clear to you so here's the MySQL workbench we have that running and in this tutorial we will use the classic models database from the previous tutorial so classic models is a hypothetical company it's a distributor of small scale models of cars motorcycles planes ships trains Etc and the products that are manufactured by classic models are sold in toy and gift stores around the world so here are some of the products they sell and classic models has offices around the world with dozens of employees and the customers of classic models are typically toy or Gift Stores so each customer has a designated sales representative who is an employee of classic models that they interact with now customers typically Place orders requesting several products in different quantities and then they pay for multiple orders at once via checks all right so that's the setup here we have this com this hypothetical company which does all of these activities and we want to track all their activities in a relational database and to do that an entity relationship diagram has been given to us and this simply tells us what the different entities or what the different tables in the database are and what the different columns within those tables are so we have an offices table here it contains information about different offices or the primary key is office code which is the unique identifier then we have the employees table so we have an employee number last name first name extension email Etc and there are a couple of relationships here there is an office code in employees so for every employee there is an office code which is the office code of a certain office so this is called a foreign key because an employee cannot have an office code which is invalid and then you also have reports too so reports to tells you which employee and employee reports to so this is a foreign key from the employee table to itself right so reports too must be a valid employee number of another employee then you have customers and you have a bunch of information about customers customer number is the primary key and every customer has a sales rep employee number which is the employee number of a certain employee who's a sales rep and then for customers a customers can place orders so every order is placed by a customer that's why you have a customer number foreign key here but apart from that orders can have a lot of items so there is an order details table for each item each line item in the order let's say you've ordered five products you have five line items and you can have the quantity Etc of each one and then you have products as well so there's a table of products and there's a table of product clients because you need to track information about the product somewhere as well finally you have payments customer main customers make payments at separate times so this is a slightly different flow where you don't pay per order but customers have a credit limit which means they can make Place orders up to a certain amount and then they can clear all their pending payments at once using a check so that's the setup they have here and depending on how your company operates this system may be different it's possible that if you take payments up front then maybe the order details is connected to payments or the order is connected to a payment rather than a customer okay so that's the entity relationship diagram here and that's the sort of the overview of the database that we're working with now we will use we will set up this database locally with some sample data using a script so let me just set that up and then we will take some questions and by the way if you have any questions please post them in the zoom chat so to set up this database we first need to download the SQL script so I'm just going to open this in a new tab and that's going to try and download it and I'm just going to download it on my desktop here and you can see that now the SQL script is downloaded on the on my desktop and then once you have downloaded it you can go to file open SQL script in your MySQL workbench so open up MySQL workbench connect to the local database and click on this open SQL script and then here you can see the contents of the SQL script so this contains all the information to set up the database that we just saw the ERD for the entity relationship diagram for and it also contains a lot of sample data for example it's going to drop the database classic models if it exists it's going to then create a new database and it's going to use that database then it's going to create tables one by one for example here it's creating the offices table with all this information and then it is going to create employees customers etc etc once tables are created it is going to insert data into these tables like product lines products customers employees Etc okay so it's going to set up a database with a lot of information for us now once you have this SQL script loaded up you will see this run SQL so execute button which is like a lightning button here so you can just run a press that or you can just press command enter or control enter on windows so I'm just going to run command enter and now my previous classic models database has been removed and this new database has been set up so if I go to the left sidebar if your sidebar is not open you can click here and open up the left sidebar and just go to the schemas tab in the left sidebar I should be able to find a schema let me just connect again here okay let me just run that once again yep all right so now if I check the schemas here so I should be able to find in the schemas once I reload the schemas the classic models database now in the classic models database I can browse all the tables here you can see that we have tables customers employees offices order details payments product lines Etc and if you double click on a table or if you just click on this table click on this button which shows information from the table then it is you can view some information from each table all right so MySQL workbench gives you an easy way to browse the schema see different tables and actually look at information from different tables very easily okay so now we have just to recap we have downloaded the SQL script from this URL and then we have loaded it into MySQL workbench using file open SQL script then we have executed the script to create and populate the database this part can also be done using the MySQL command line tool it's simply a matter of passing the script to the command line tool and you can look up how to do that but once executed you should be able to view and browse all the tables in the schema section of the sidebar and if you face an error the most likely issue is that your MySQL server is not running so just because you have workbench open does not mean the server is running so make sure that the server is running and then you will be able to communicate with the database server all right so with that we have our database set up now ready to go and we have our MySQL workbench connected so let's get started the first topic we look at is aggregation grouping and aliases SQL provides several functions like count average sum Min and Max for aggregating the results of a query what do you mean by aggregating well it's combining the results that you get typically into a single number or maybe a one or one or more numbers so here's an example report the total number of payments received before October 28 2004. okay so this is the kind of question that will be posed to you or the kind of information that you will have to figure out now we know from the ER diagram and we know from let's maybe just copy the question and put it here yeah so this is the way to create a comment in a SQL file or a SQL query you just put in hyphen hyphen space and that's going to be a comment that is going to be ignored by the uh by the MySQL server okay so the question is report the total number of payments received before October 28 2004. so we have a table called payments so let me just do a select star from payments all right and once I run that I will get back a list of results yeah here's the list of results so these are all the payments in the database you have a check number you have a payment date you have an amount and you have a customer number the customer who made the payment all right but we are not interested in all the payments we are interested in payments received before October 28 2004. so there is a payment date here and it seems like this payment date is in this year month date format and in fact if you want to know the data type of this payment date you can just spin up a new query here and just type describe payments and you can see that the payment date is a date time okay now a date time is an ordered field obviously dates have a intrinsic order so here's what you can do you can say select start from payments where payment date is less than because we want payments before October 28 2004 and then you provide the date that you want to check here so that's 2004 and then you have October that is the 10th month and then you have 28. so this should be enough you don't need to put in the time here so I'm just going to run that and if you see now we only have payments that have occurred before October 24 October 28 2004. and I recommend just going through this list manually if you want to verify this but that's that's how you get that all right but we don't want every single payment we want the total number of payments now if you want a total number of payments it's very simple to we simply want to count the number of rows that were returned by this query and the way to do that is just type count so count is a function in SQL and you can see it highlighted in green and typically this is what we do we put all the SQL commands in so typically this is what we do we put all the SQL keywords on the SQL functions in capital letters or uppercase and we put the column names in lowercase or whatever is the actual column name whether it uses lowercase uppercase or combination of both and this is just to visually separate actual keywords with column names but remember that SQL is completely case insensitive so you could type all of these in lowercase and you could type all of these in uppercase and it would work just fine okay and this is just for visual separation in any in any case we have the count function so you just put instead of Select count star uh use instead of Select star you just type select count star and run that and now you will see here that there are 189 payments that have been made before October 28 2004 and if you did not have this where Clause this filter then you can see that there are total of 273 payments so out of 273 payments 100 and 89 payments were made before October 28 2004. so that's how you count the number of rows returned from a query just put in account located and there's a quick question here about payments where this payment stable come from so this is the table that we've just populated when we downloaded the SQL file classic models.sql and then we executed it that gave us this table and that gave us all the data inside this table okay so that's count just put count around the selection criteria and that will count the table that will count the rows for you next another common use case involves counting the number of distinct values in a column so here's a question report the number of customers who have made payments before October 28 2004. so now we are no longer interested in the number of now we are no longer interested in the number of payments that were made we are interested in the number of unique customers who have made payments and one customer can make multiple payments uh so number of customers who have made payments before October 28 2004. so once again maybe let's open up a new uh let's just continue typing here so here's my select query select star from payments and I can write payments or I can write payments or I can write payments and it will all be the same thing okay this is the name of the table so anything you see after from is a table name so select star from payments that gives us a list of all the payments and once again we want to select them and I'm just going to mix cases here but don't worry about it so let's just say select start from payments where payment date is less than 2004 10 what's that October 28th so 2004 1028 so that gives us all the payments that have occurred before 2004 1028. now we are interested in the customer number we are interested in the unique customers who have made payments before the state so here's the first thing I'll do maybe I'll just select the customer number first okay so if I just select customer number now I have ignored all the other columns I have just selected the customer number column so now I have 2299 172 242 Etc but of course there are probably going to be repetitions here and you can go through this list and verify that there are some repetitions okay so if I did select count customer number this is giving me 189 which is actually the total number of rows returned but we want simply the number of unique customers who have made payments so what you do is you type distinct okay now when you type distinct repetitions from this list will go away so if I type distinct here now you can verify that this list is a bit smaller this list is not that big anymore and in fact if I type count now you will see that the count is only 98. so what distinct does when you type distinct before a column name it is going to just select distinct values of that column and if you type multiple column names after distinct it is going to just select all the distinct combinations but in this case we first get the list of all the distinct customer number which is a customer ID and then we count the number of rows okay so count is a function but distinct is still a keyword which is part of the SQL syntax okay so once again I'm just quickly going to repeat that we have select star from payments where payment date is less than October 28 2004 now instead of selecting all the columns I just select customer number and that gives me the customer number for each payment but I'm only interested in the distinct customer numbers I don't want to show I don't want customer numbers to show up again and again so I type distinct and then I want to count them so here is the count function which is going to count the customer numbers for me and there are 98 of them okay so that's how you solve any SQL query or solve any SQL question you just go step by step you start by looking at the table and then you simply keep adding in criteria one by one of course as I said District can be used can also be used without count so if you just wanted the list of customer numbers for customers who have made a payment before the 28th of October 2004 then you just type select distinct customer number from payments where payment date is less than 28th of October 2004. okay and you can use distinct with multiple columns as well so if you want to try out learn more and try out what distinct does check out some documentation here on w3schools and you can also practice this online okay now if I look back at this query just a distinct customer numbers this is just giving me a list of customer numbers right and this is not very informative because ideally what we want is details of those customers I want to know which people which stores have made payments before the 28th of October 2004. so how do you do that well here is one way to do that we know that this is a list and you can write a query which uses the result of this query okay and here's what we want to answer we want to retrieve the details for all the customers who have made a payment before October 28 2004 okay so how do we do that let me just put this query aside for a second and let me start typing and let's just comment it out here and let me start typing select star from customers okay I guess I'm just going to use lowercase here but select star from customers so now I have details of all the customers but I'm not interested in all the customers I am interested in simply the customers where the customer number is in this list that I had earlier and I'm just going to run this again here in a as a new query so I just want to get the details of customers who are in this list about 98 or so customers so what I can do is I can take this query without the semicolon and I can come back here I can come back here select start from customers where customer number in and then here I have to give a list so instead of giving this actual list I can give a query which generates that list so I can just put in a bracket here and type select distinct customer number from payments where payment date okay so we take the result of this query select distinct customer number from payments where payment date and then we run a second query which is going to select details from the customers table where the customer number lies in this list okay so if we run that now so now you will only get details of customers where the customer number is in this list and what is this list this list is simply then distinct customer numbers from the payments column where the payment date is less than 2004 1028 okay so that's how you change SQL queries you can take the result of one SQL query and use it in a where clause or you can even select from it again so you can treat a treat the result of a query as another table essentially and then write SQL queries on top of the results of other SQL queries okay so that's how you chain queries okay so here's an exercise for you retrieve the details of all the customers in the United States who have made payments between April 1st 2003 and March 31st 2004. so how would you do this you would first think about it the way you would do this is first you would just look at all the payments so you just do select star from payments and then you would look at payments between April 1st 2003 and March 31st 2004. so you would put some kind of a where Clause where you would combine two conditions using an and keyword and once you get the results for payments just between these two dates then you simply retrieve the customer ID for those payments and then once you have that list of customer IDs or the distinct list of customer IDs you can use that to get the details of customers and you add another where Clause where you will check whether the customer is in United States or not so it's going to get a little complex here but I will let you figure this out and if you are unable to do it uh do ask on the slack group and we'll help you out okay so let's talk about grouping then so here's a question find the total number of payments made by each customer before October 28 2004. all right let's go back and let's try and solve this so here's the question total number of payments made by each customer before October 28 2004. so we say select star from payments again so we were selecting we want to show all the columns from the payments table all right and we just want to look at payments before October 28 2004 so where payment date is less than 2004 1028 and now we want to count the total number of payments so we could do this count but this is the total number of payments across all customers we want to count the number of payments made by each customer so here's what you can do you can provide a you can provide a group by Clause so you can type something like this you can say Group by well I believe it shows up after where so yeah this this can sometimes be confusing which clause comes after which but let's try here well actually let's start with that and then we'll see if that works so Group by so you type select count start from payments and group it by customer number so now you're just looking at count star of payments and grouping by customer numbers okay I think this this comes after yep so you type select count star from payments where payments date is less than to 20 2014 28 and you Group by the customer number so what happens here SQL first creates or groups of groups of rows for each customer number and then it computes the sum or it computes the count of the number of rows for each customer number so here it seems like for a certain customer there are two rows for a certain customer there are three rows for another customer there is one row Etc but of course we also want to see the customer number so what we can do is we can just take this customer number column and select that too okay so anytime you Group by a certain column you can also show that column in the result so here's what that looks like select count star from payments where payment date is less than a certain value select customer customer number comma count star from payments where payment data is less than a certain value and group this data by customer number first so first SQL creates groups for each customer number then it performs this count star and then it gives us a customer number and the count star okay now here's one other thing we can do this is not very informative because we're looking at count star here what we can do is we can just say uh number of payments we can rename this count the output of this count as number of payments okay this is called aliasing so now you have this table where it says customer number and it shows you the number of payments and you can see that the customer number 112 had two payments before the state customer number 114 had three payments and for each customer in the database you get how many payments they've made only the customers who have made payments before this particular date so this is how you use Group by grouping is very commonly used with aggregation in functions like count okay now apart from the count you can also compute the sum of values in a column so here's one thing you can try and figure out find the total amount paid by each customer which is the sum of all their payments before October 28 2004 so let's see if we can do that I'm just going to once again select I'm just going to select start from payment s here and just see what columns we have so we have a payment date which we have been looking at we've been looking at this customer number but there is also this amount so what we want to do is we want to total up the amount for each customer so let me go back here so now I have select customer number and instead of count star which is going to count the rows I am going to call sum and I'm going to sum the values in the amount column so count you generally put in Star because you're just counting the number of rows so you don't have to specify a column but when you want to compute the sum you specify the column for which you compute the sum so sum of amount and I'm going to call that as total amount okay so select customer number and some amount which I'm calling total amount from payments where payment date is less than 28 October 2004 and group by the customer number and that now gives you for each customer the total amount so all we've done is we've replaced count star with some amount okay so these functions are generally work all work in the same way some count and you can also use them together so here's an example find the total number of payments and the total payment amount for each customer for payments made between uh before October 28 2004. so how would we do that well we have select customer number uh and then we have the total amount already I'm just going to add another column here so I'm saying I'm going to say count star as number of payments comma yeah so we have select customer number count Star as number of payments some amount as total amount from payments where payment date is less than such and such and then we Group by customer name so now we have for each customer what is the number of payments they've made and what is the total amount that they've paid before this particular date and you can try and remove this where clause and see that the result actually changes and you see now that the result is different right so try and understand each part of the SQL query the same SQL queries mentioned here so try and understand each part of the query and if you can make sense of it then you're getting familiar with SQL and here's an exercise for you determine the total number of units sold for each product so there is a products column and then there is a so there is a order details column I think let's take a look at that quickly select star from order detail so there is an order details table and this contains the details of all the orders that have been placed and then here you have the product code and you have the quantity ordered of that product in that particular order and what we want to do is we want to find the total number of units sold for each product so if I simply do Group by product code and here if I do product code and I do a sum and in the sum I put in the quantity order so now what we're saying is we want to group the rows in order number in order details by the product code and for each product code we want to take a sum of the quantity ordered column and if we do that and run this query you should see here that we have the product code and then for each product code we have the quantity ordered okay and that's how you answer this question again becomes easier to understand once you try it yourself so here's how I would go about solving working through this notebook just read the question don't look at the answer just immediately go to your SQL workbench and then start typing out and start figuring out how to solve the question and if you're able to solve the question great if you're not then you can look at the answer and again don't copy paste the answer look at the answer maybe for a couple of minutes try and understand what it does and then go back and try to type it out again typing out code is a very important part of becoming familiar with SQL or python or coding in general okay now apart from sum and count you can also do the min max in average for example if you want to figure out what was the maximum payment made by each customer before a certain date you can do that all you need to do here is in this table you can maybe just add max amount as let's just call it largest payment so now you have for each customer what is the largest payment they've made and you can see this here the largest payment that they've made is a for this person has made is a fourteen thousand dollars they can also you can also add the minimum amount that they've paid so smallest payment so the smallest payment they've made is 32 000. so already we're getting some insights in it turns out that in this case customer number one zero three the largest num the largest payment is fourteen thousand dollars and the smallest is six thousand but for one one two the largest payment is thirty three thousand and the smallest is 32. so maybe this customer is just clearing uh bills as soon as they hit a certain amount right you can also then look at the average payment so what is the average order amount and think it is average okay so what is the average amount and this is the average payment okay I think there's a an issue here I'm not sure what the average is called but let's see average my sequel AV it's called AVG so let me just change that to AVG yeah so AVG is a function name here and you can see that the average payment for each customer is also shown here right so this is how you can do basic analysis directly within SQL you don't even have to export the data although if you want you could export it to a CSV file uh that's an option from the MySQL workbench but a lot of analysis can be done directly within SQL okay so let's talk about sorting and pagination now so far we've been doing all these queries and through these queries we get a lot of information but in a lot of cases we are looking we are interested in finding the a top 10 or top five or the maximum value so here's an example retrieve the customer numbers for the 10 customers who made the highest total payments in 2004. or actually let me change that to before October 28 2004 okay so retrieve the customer number for customer numbers for 10 customers who made the highest payments total payment before October 28 2004. so let's try and answer this step by step we want to retrieve the customer number so we can say select star from payments and once again we are interested in payment date before 2004 October 28th grade we have that information but now we want to retrieve uh we were interested in the highest total payment so before that we are interested in the total payment per customer so if we do this if we do a group by customer number and then we do uh we select the customer number for each group and then we do a sum of the amount column so now what that gives us is the total the total amount paid by each customer before October 28 2004 so we have that but now we are interested in the people who made the highest total payment so here's what we can do we can type at the end order by so we type order by and then we tell the column name that we want to order by so we want to order by this column which is the sum the total amount so let me just rename this as total payment so I'm going to call this column the sum column I'm going to call it total payment and I want to order it by total payment so let's run that and Order bike generally comes at the very end so now you have customer number 456 they have a total payment of 1679 customer number 381 has 2755 and so on but of course we are interested not in the lowest so this is ordered in ascending order but we are interested in the highest so to do that you type desc descending so a ASC for ascending and desc for descending and let's run that so now you have the maximum value 31 33 sorry 331 256 dollars were spent by customer number 141. okay and we have this information for all the customers but the question asks only for the top 10 customers and typically anytime you issue queries like this in a very large database you probably only want to look at maybe the top 10 the top 50 or the top hundred so here's how you can do that you just call at the end you type limit and then you tell the number of rows that you want to see in the result okay so if I type limit a 10 at the very end so that's going to just give us 10 results you can see these are the 10 customers uh 141 124 148 151 and 320. okay and by the way limit Works without order by as well it you don't need an order you can do a limit just like this too so these are both independent but in general whenever you're doing an ordering it's common practice to also limit the number of results so as to not put a very heavy load on the database so that's what we created select customer number and some amount as total payment from payments where payment date is less than this value and we Group by customer number order by total payment descending and limit the results to 10. now if you want to see the next 10 results because we've just limited the results to 10 all we need to do is add an offset so let's say we want to skip ahead we want to skip the first 10 rows and you want to see the next 10 rows we type offset 10. and just notice this number here 320 and 93565 if I type offset 10 you will see that now we have 386 Which is less than the previous value so now I'm no longer looking at the top 10 I'm looking at the next 10 so customer number 11 to 20 when they are sorted by the total payment and if it's let's say if I set the offset to 5 if I if I set the offset to 5 so I'll just skip this query will just skip the first five rows and it'll show me from the sixth row onwards right so limit and offset can be set independently limit tells you how many rows you want offset tells you how many rows you want to skip now here I've skipped the first 50 customers so I'm looking at customer number 51 to 60. okay and I could maybe even if I wanted I could look at just five of these so play around with limit and offset they are used to limit and paginate the queries SQL also provides mapping functions and these mapping functions are applied to individual values so now we're no longer combining multiple rows of data now we are just applying them to individual values so here is a question display the full name of the point of contact for each customer in the United States in upper case along with their phone number and we want this information to be sorted by the alphabetical order of customer name okay so there's a lot going on in this question and these are the kind of questions that you will see in interviews often and don't feel intimidated just go step by step the first thing here is we want information about customers so we go here and let's clear all this and we just type select star from customers great now we have information about customers next we want customers in the United States so we have what's that we have this country column and in country we have USA so if I just type where country equals USA so now I'm just looking at customers in the United States and you can check here then all of the all of them have Country USA okay great one more thing done next we want to display the full name of the point of contact for each customer so if you see here we have contact first name and we have contact last name so we could do this we could just say let's just print a customer name I think that is something that we should display and let us display contact first name and let us display contact last name okay all right so now we have the customer name we have the contact first name and contact last name only for those customers where country is set to USA okay so display the full name it's still not the full name we are showing two separate columns but we'll figure out that piece but here it says we want to display these in uppercase for some reason or maybe we want to print them somewhere or copy paste this information somewhere so how do you convert a string into uppercase well there's a function for that you just call you case so your case is going to make it uppercase and L case is going to make it lowercase so I just call you case on first name and let me maybe do L case for now on contact name and you can see here the first name becomes upper uppercase all capital letters or uppercase letters and the last name has become lowercase let me turn that into uppercase as well so now we have both an uppercase and notice that these are not aggregation functions they did not combine all the rows into a single value they applied this function uppercase to every individual value so this was applied to Susan it was applied to this row and this row and this row and so on okay so that's why these are called mapping values they take a column and they map that column onto a based on a function so that's why these are called mapping functions now last bit here is or maybe a couple more things left one is display the full name so we want to take the first name and last name and we want to combine them so there are certain functions which can combine columns and the combination that we want to use here is called concat concat is short for concatenation so I'm just going to call concat here c-o-n-c-a-t and let me drop this for a moment let me just get rid of these and I'm just going to do customer first name and oh sorry this is contact so contact first name and contact last name so now we've combined contact first name and contact last name but there's a problem here you see Susan Nelson this is there's no space between them well that's easy to fix you just add a space here and now you have Susan Nelson and we want to make this uppercase so now concat first name Etc is the uh string that we're dealing with or it's a column that we're dealing with so we just called you case here and that is going to now make that uppercase now you could do it two ways you could apply to each column first and then concatenate them or you could apply it outside both will do the same so now we have that one last piece of cleanup is let me just get this on a new line is to Simply get is to Simply rename this column so I'm just going to call this contact name okay so we concatenate first name and last name with the space between them convert that to uppercase call it concat contact name and list it out one last thing we also want to display their phone number and we want to sort by the alphabetical order of the customer name so let's display phone number here let's just put in comma phone number I think it's just called phone if I'm not mistaken yeah so now we have customer name contact name phone and we want to order them so we just put in order by and here we put in what was that number customer we want to order by customer name in alphabetical order so we can even just put in ASC here or ascending to mean increasing alphabetical order so now you see we have this customer name Americans souvenirs Automotive Classics board and toys Cambridge Collectibles classic gift ideas all of these are customers in the UK in the USA we have that contact name listed in uppercase we have the phone number of the contact and we have sorted this list in alphabetical order that's how you answer SQL queries and that's all we're going to do over and over for the rest of the session essentially here's another example display a paginated list of customers sorted by customer name with a country code column and the country code is simply the first three letters in the country name they should say country code so the country code is simply the first three letters in the country name in lower case so here's how you can do that we want to show customer so just paginated list of customers so let me just do let me open a new query here let me do select star from customers and we want to show a paginated list so we want to show where um sorry we want to limit to let's say 10 customers so these are 10 customers then we want to sort them by customer name so let's do order by all right it is sorted by customer name just fine and then we want to show country code column so maybe let's just show customer number and customer name and Country so now we have customer number name and Country but we are interested in something called a country code and what's a country code the country code is simply the first three letters in the country name in lower case so we want to get the first three letters out of every country name and that can be done using this substring function so instead of country we put in sub string country and in substring you specify where you want to start now in SQL numbering starts at one not at zero so just be careful about that and we want to take three values I think so we just say one comma three and yeah so that gives us just the first three characters of each country name and let me just clean that up here so that gives us the first three characters from each country name but we are interested in we want to make it lower case so we just type L case here and that's going to make it lowercase for us and let's just rename that using as to country code okay and there you go so now for each customer name we have a country code for whatever reason we need the country code all right so that's another function here's one last function display the list of the five most expensive products in the motorcycles product line with their price rounded to Dollars and the price is the MSRP column okay let's try and answer this to the five most expensive products in the motorcycles product line so let's get rid of some of these okay so let's see select star from products there's our products a list of all our products and here one thing you will see is a product line so we can we simply want to look at motorcycles so where product line the column product line has the value motorcycles okay now we're just looking at motorcycles here and we want to check the five most expensive products so we let's order them let's order by MSRP maximum sale retail price I guess so yeah and we ordered that by MSRP and we can also just type MSRP should not be a problem here okay now they're ordered by MSRP but of course we want the most expensive so we type the ESC descending so now you can see that we have only motorcycles and we have ordered them in the decreasing order by MSRP and we just want to see the top five products so I'm just going to add a limit here so let me just move this down yeah so let me just add a limit order by MSRP descending limit five so now we have the top five products and maybe we don't need all the columns mostly we just need let's say the product code and product name and MSRP all right this is the required information there is one last condition here we want to round the price to dollars maybe we're creating a report where we don't want to show the full price we just want to round it to Dollars and the way you do that is just type round so round function will do that for you so now this number is rounded to Dollars you also have a ceiling and a floor function so if you want to round down or round up you can use those but round does the job and in round you can also specify the number of decimal points that you're interested in for example if you want to see one decimal point just specify round one okay so that's how you answer this question five most expensive products in the motorcycle category so that was about the mapping functions you will not yeah there are a lot more mapping functions so you generally don't need to remember all of them although it will help again for interviews for exercises and as you solve more exercises you will automatically start memorizing them but there are a bunch of different functions that you can apply to dates and you can apply to numbers and you can apply to strings all of them and one other kind of function or operation we should look at is arithmetic you will often have to combine data from multiple columns using arithmetic operations so here's a question display the product code product name Buy price sale price and profit margin percentage so what's profit margin percentage will you buy the product or maybe you buy the parts for that product at a certain price and then you sell it at a certain price the difference is called the profit and or the margin and the percentage is basically the sale price MSRP minus the Buy price multiplied by 100 divided by the Buy price okay so that's the profit margin percentage sale price minus Buy price divided by Buy price and convert that to a percentage by multiplying with 100. for the 10 products with the highest profit margin so we want to show the 10 products with the highest profit margin and we also want to round the profit margin to two decimals so there's a lot going on here let's go step by step here first thing is we want to get some information out of the products table right so let's go select star from products yep so now we select a bunch of products all right and what are we interested in product code product name by price sale price so let's get product code product name Buy price sell price uh okay is there a sale price I don't think so there's just an MSRP yeah okay so now we have the information that we're interested in but there is this last column that we need which is profit margin percentage and fortunately I have the formula here so here's what I can do I can simply put in the formula here comma um MSRP minus Buy price let's get these on different lines so MSRP minus Buy price and let's get that down as well yeah so this is the profit and if you divide that by Buy price so these are all column names right so you you're saying we want to take values in the MSRP column from them subtract the values in the Buy price column and then divide by the values in the Buy price column and we want to multiply that by 102 so let's just put in a multiplied by 100 and let's put the entire thing within a bracket okay so now we have MSR oh yeah so now we have MSRP minus Buy price multiplied by 100 divided by Buy price and let's just call that profit margin so let's just rename that so that's our profit margin and you can see that the profit margin for this product since we buy it at 48 dollars and sell it at 95 is 96 percent that is the amount of money that we've made on top of the Buy price and what do we want to do with the profit margin well we want to show the 10 products with the highest profit margin so let's add that so that will be a sort or an order by order by and now once we've created this column we can use it here so we can call ordered by profit margin and we want to limit it to 10. and we want to see the highest profit margin so we say desc descending limit 10. let's check that great so now we have this product the Chevrolet Impala on this product we are making a huge profit margin we're buying it for 32 dollars selling it for 80. so 150 percent profit actually maybe let me just call this profit margin percent so that it's not confusing with the actual number yep now the last thing we want to do is round the profit margin to two decimals and that's easy we just type around here and another bracket here well I guess we don't Let's see we probably don't need that bracket and we want to round it to two decimals so there you go now we have the profit margin rounded to two decimals and these are the 10 products with the highest profit margin percentage so these are the kind of queries that you should be able to answer by using arithmetic operations by using sorting grouping and aggregation and mapping functions you can check out what other operators are supported in SQL it's not just a Johan arithmetic you have add subtract multiplied divide you can divide and you also have this modulo function which is to compute the remainder so you can do that as well and then you have some bitwise operations and you're already familiar with comparison operations as well so comparison operations are typically used in the where Clause as we saw with the date talking about dates SQL also provides several functions for extracting information like the year month Etc out of date columns so let's look at an example list the largest single payment done by every customer in the year 2004 ordered by the transaction value highest to lowest okay again seems complex but let's break it down we are interested in payments that's what I can gather here list the largest single payment so I'm just gonna go here and first of all just to select star from payments okay we have payments now next we want the largest payment by each customer okay so we want basically the maximum payment by each customer so we can probably do a group by customer number and then for each group of rows for each customer we want the maximum payment that they've done so let's call amount max amount and let's rename that to largest payment okay all right so it's start starting to make sense now so we have we select customer number we select the maximum amount so for each customer we look at all the payments we've made and pick the one with the maximum amount or pick the maximum amount among those and of course for that we have to do a group by customer number but we want to look at this in the year 2004. how do we do that well we have a payment date and we could check if the payment date is greater than Jan first 2004 and less than January 2005 and things like that or we could simply do where and we could use this year function so here is all here is a function just like other mapping functions and into the year function pass in payment date and just check if the year of the payment date is 2004. okay so that's our query right here select customer number max amount as largest payment from payments where year of the payment date is 2004 and group by customer number and ordered by transaction value right so we want to order that by transaction value so we should also probably put in order by largest payment and let's run that so it turns out that customer number 161 it turns out that customer number 161 made the largest payment in the year 2004 and that payment was 2434 dollars oh well no we want to sort it in descending order yeah so customer number 141 the loyal customer made 116 000 200 in a single payment and that was the highest payment in 2004 by a huge margin actually you had to 84 410 after that okay so that's how you pick out information from a year from a date here's another example show the total payments received month by month for every year okay this is interesting so you want to show the total payments month by month for every year so you want to see January 2003 February 2003 uh March 2004 and so on so let's once again start with payments select start from select start from payments we have the data now we are interested in year and month so we have a payment date if we can just create a new column for ear and a new column for month I think that should just get us started so I'm just going to create a new column for here so I select star which means I select all the columns but apart from that I also select year of payment date as payment year and let's see that so now we have a payment here over here and let me just put that at the beginning and then we also want the payment month so we just type month and we type payment date so from the payment date we get the month and we call that payment month so let's put that on a new line here comma star so we still want the other columns or you know what maybe let's just keep the amounts now or maybe we don't need the other information so select payment date year as payment year month of payment date as payment month and then the amount from payments yep so now it tells us for every single payment we now have a year and a date in the amount but of course we don't want of course we don't want uh like this is still showing us every individual payment like if I put a comma star here you will see that we are still looking at yeah if I just instead of the amount if I put star here okay never mind yeah so you can see that we're still looking at every single payment even though we've created a payment here column and we have created a payment month column we are still looking at every single payment individually what we want is the totals for each month and for each year so how do we do that well you guessed it we want to total up the amounts so let's get rid of these other columns and let's just do a sum and we want to get the sum of the amount but of course just doing a sum is not enough because we also need to do a group by right because we want to First Define groups so for each month comma year we want to get a group of columns you want to get the group a group of rows so you want to get all the rows which were which belong to January of 2003 all the rows which belong to February of 2003 and then sum up each group so we type here Group by Group by and in group by you can actually provide multiple columns so we have this payment year column and we have this payment month column so now we have a payment here we have a payment month and we have the total sum so this is the sum of the amount of all the payments made in that particular month of that particular year and maybe we also want to order by these same criteria so that we can see it in a more understandable format so we also order by payment year and payment month and you don't need this comma here so now we have 2003 one and the sum is 26 000 and then 20032 which is February of 2003 and the sum is 144 000 and then 2003 March is 199 000 and so on seems like what is this August September of 2003 was pretty good so this is how you can now start doing some basic analysis and interpretation so this is your Revenue growth or this this is your Revenue table so if your boss asks for a month's wise Revenue table this is what you give them but of course you probably don't want to show these numbers like this like it doesn't make sense to look at this zero zero zero zero three here and it's probably a floating point or a rounding error somewhere so let's make this a little prettier and here again it's a bit difficult to guess the month from month number going to the actual month can be a bit difficult so let's make this a little prettier now for the above query the query just that we just looked at let's format the amount properly with a dollar symbol and comma separation maybe show it something like this 26 267.62 and just two two decimal places and let's also show the month as a string okay so now we are in the territory of printing things nicely of making things look good so that's where you need these formatting functions there are two numbers two functions one is the format function for numbers and then the date format function for dates so let's first look at the format function for numbers now instead of showing the sum just the sum of amount I am going to call format here and I am simply going to put in I think you can to format you can mention how many decimal digits you want so I'm just going to put in 2 here and format is automatically going to add commas here so now you can see that you have 144 comma three eight four point three six just by adding this format keyword here and of course now it's no longer number nowadays become a string and let us also rename this column as Revenue so now we have revenues one four four three eight four point three six maybe let's go a step further let us add a dollar symbol here how do we add a dollar symbol well this is a string and we want to add a dollar symbol at the beginning of the string how do we combine two strings that is done using concatenation so let's call concat the concat function that we saw earlier but this time instead of concatenating two columns we are concatenating a string the dollar symbol with the column so now when we call concat we have dollar one four four three eight four point three six and that is called the revenue so that's how we format the numbers a little other a small cleanup that you can do here especially if you're sending it out to somebody this result is maybe you want to just clean up these call column names so if you want to just call it payment year but of course that's going to be a problem so whenever you have spaces within column names you can use these back codes so this is the code that is available on your keyboard just below the Escape key and let's call this payment month okay and then let's call this uh Revenue I think this should be fine just like this and once again let's just changed it here just cleaning it up a little bit to make it look nicer and then you can export it and send it as a CSV or something so now you have payment here you have payment month you have Revenue with dollars looking good but of course we still missing this piece we want to show things like January February Etc here so if you want to show Jan Feb Etc here's what we can do we can call date format so there is this function called date format and date format is something you will most likely have to look up so date format takes a date as an input and then it takes a certain format in which you want to see the date for example if you want to see the YY mmdd format then you put something like percentage y hyphen percentage M hyphen percentage D or or something and here are all the variables that you put in so inside these codes as the second argument you provide all the variables that you want to show and based on that it is going to show you that information so we are interested in we have a month here and we want to show that month um actually we just want to show the payment date as a month so I'm going to remove this month I'm just going to call date format here and how do I want to format it let's see let's see what options we have so there is this percent B here so I can take this percent B and if I just take this person B here let's see what happens so you see here now this has become August and in fact if I want to get the year name I think the year would be percent y so if I want to get the year name I would maybe just put this and now you can see August 2003 December 2003 Etc but I just want to keep the month name here so now we have the month name so we took the date called date format on it directly on the date and we just extracted out the month using this person B and that is something that I looked up in this table there's no way I can remember all of these so I just have to look these up each time and we saw that person B can give us the abbreviated month name if you want the full month name then we can use percent m that can give us the full month name let's try that too maybe okay so yeah so now you have the full month name but now we've run into a problem because now we have payment here and we have payment month but now because we are ordering by payment here and then payment month the ordering the ordering becomes alphabetical so we want to display the number we want to display the string but we want to order by the number how do we do that well here's one idea I create another column let me just call that let's use month and payment date so let's get the month from payment date and let's call this is called that as month number and I'm just going to instead of ordering by payment year and payment month I'm going to order by month number okay and let's try to run that so that gives us an error because now since I have this new column month number I should also be grouping by this column otherwise anything other than sorry anything other than the aggregation so here is where we are doing the aggregation anything other than the aggregation should show up in group by so let me just put month number here okay so now we have three columns which we have created we've created created a payment here that's fine and we have created a payment month grade and we've created a month number because we also want to track uh we also want to track numbers here and then we are grouping by payment year payment month and month number of course payment month and month number represent the same information so the same groups will be formed for every month of every year we will get a group of rows and then we are taking the sum of those rows uh some of those rows to get the sum of the amount then we are formatting it to add commas and show two decimals and then we are adding the dollar symbol and we are calling that column Revenue okay so this is looking good this is a pretty complex query at this point but it's looking good the only problem I have is that there is this last month number column here which I don't like this is not something I wanna send to the person who's asking for this information so how do we remove that so in some sense we want to use this column month number for grouping and for ordering but we don't want to display it here's how you can do it it's a very simple trick here just remove it from select so you select payment year payment month payment you select Revenue but you remove this from here remove the month number column and just put that here so just put it in group by and put it in order by okay so now we're saying we select year and we select this payment month which is nicely formatted and we select this sum which is nicely formatted as revenue from payments but in group by we are grouping by this additional condition this sort of hidden column which is the month payment date and we are also ordering by this additional hidden column which is a month payment date okay and now we finally have 2003 January 2003 February 2003 March 2004 January February March 2005 January February March okay now this is the perfectly formatted Revenue column Revenue table we exported put it into Excel or send the CSV to whoever needs it okay so that's the query there's a lot going on here so spend your time with it take go through each step and as I said what you want to do is you want to start with the table that seems the simplest uh just get all the data and keep adding in keep embellishing the query step by step to cover all the requirements and maybe also just make it look good so that's all about aggregation and mapping functions and arithmetic operators and dates so let's talk about combining tables using joins a join Clause is used to combine rows from two or more tables based on a related column between them so here for example you have table one which has three columns and you have table two which also has three columns and one of the columns let's say this is some sort of a shared column so one of the columns has is related and has the same or similar set of values so table one has one two and table two has one three four so what you can do is you can combine the two tables such that you have this one column and then for this Row one and then these two values you also get the information from this table these two values and that creates like a one big row so you can think of this like the result of the join so for the column for the value 1 we get the information from table one and we get the information from table two for the row with the value 2 we get the information from table one here and there is no information in table two so now what happens here whether we put empty values or we don't show this row depends on the kind of join so if it is an outer join then we just show empty values here for these columns from table two and then here there are no more values left in table one so we go through table two we pick these values and we just show empty values here so we show empty values for three for table for these two columns from table one and for the columns from table two we show these values and finally we have four for which we again show empty values from table one and we show these two values here for table two okay so that's outer join and then we have inner join which picks just the intersection of the values and then we have the left join and right join so let's maybe look at some examples and this will become a lot clearer the first thing we'll talk about is the inner join and this is the default join in MySQL so whenever you type join whenever you're trying to join two tables on a certain column then by default it is going to be an inner join and let's see it in the context of a problem and it will make more sense show the 10 most recent payments with customer details so name and phone number okay so let's go select star from payments these are all the payments that have been made now the trouble here is that in this result we only have the customer number what we'd like to show is the customer details as well who is this customer number 471 and we all look at the most recent payments so let me just grab the most recent payments ordered by payment date or maybe let's do that part at the end maybe first let's get the customer details so we have select star from payments and we want to show customer details name and phone number how do we do that I know that there is a customer number field in the customer's table as well so if I do select star from customers there is a customer number table there's a customer number field or column so what I really want to do is I want to fetch the information for customer number 471 from the customer table how do I do that I type select star from payments join customer number okay so what did we just do we said that I want to get some information from the customer number table as well but how do I decide which how do I decide which rows are connected well I want to use the customer number right so by default SQL doesn't guess SQL requires you to provide how you want to match up the rows of the two different tables so you say join on on which column you want to join and then you provide the column from this table and the column from this table so you say payments join customer number on payments dot so now because there are two tables involved so that's why you have to specify the the table name before you specify the column name so payments Dot customer number equals customers Dot customer number okay so let's just try and make sense of this and maybe let's put this on different lines so we are saying that I am not happy with the information that I have from the payments table I also want to get information from the customer table I want to augment some information here for the customer using the customer table and how do I want to augment that information I want SQL or my database server to look at the customer number field in the payment table here so for this row look at the customer number field and find the row in the customers table which has the same customer number so find the row which has the customer number 471 get all the information from that row and add it to this row okay similarly we want the server to look at the second row and see that we have customer number 299 here find the row in the customers table which has the customer number set to 299 so because we're comparing the customer number columns get all the information from that row and add it here and let's run that okay did that lead to an error let me just check this um maybe I have a maybe I have a typo here somewhere but yeah sorry I think I had a typo there but yeah just repeat select star from payments join customers so you want to join payments with customers on the column customer number and now what SQL is doing is it looks at a row in payments it sees a customer number there and when it sees a customer number from the payments column it looks for a row within the customers table with the same customer number so this customer number comes from payments and this customer number comes from customers and then it takes all the information for that row and it adds it to this row so now we have not just the payment information but we also have the customer information attached with each payment so that is join okay now we probably don't want all the rows here so that's why we may want to just select some rows so I'm just going to select check number payment date amount um customer number customer name and the phone number so I just keep the check number payment date amount and I just want to keep one of these columns because these columns are a bit ambiguous they come from both so I'll just keep customers Dot customer number which I know is going to be equal to equal to payments.customer numbers and then I'm just going to keep the customer name and phone number okay so now we have check number payment date amount customer number customer name phone number and then we can Now cover the additional criteria which is we want to show just the 10 most recent payments so we want to order by order by payment date descending and limit it to 10. okay so now we have just 10 now we have just 10 payments the 10 most recent payments and you can verify this that these are the most recent payments six zero nine six zero three five twenty five and so on and then we have the amount customer number customer name and the phone number okay now the key thing for you to understand here is the join how it is how it how it works it looks at payments looks at the value of customer number and it picks a row from customer number which matches it okay inner join what does that mean well there are several customers who may not have made a payment in the last 10 days or who may not have made a payment at all so if I remove this for a moment order by Etc there are several customers who may not have made any payments so I probably don't want to show the information for those customers who have not made any payments right now if I do this inner join here or if I do this normal this is the inner join I can also type inner then what I'm telling SQL is don't show me customers for which I don't have any payment information but if I change this to Outer join now we are telling SQL okay maybe it's like a full outer join or let me look up the syntax but if we change this to a outer join then we would get information for all the customers right so we would not only get information for customers who have made payments but we would also get information for customers who have not made payments and what is going to be present in the check number payment date and amount columns empty it's just going to be empty so just going back here if table 1 is payments and table 2 is customers then when you do an inner join we only look at those rows where these values are present in both the columns for for example if customer number one and two occur in payments and customer number one three and four occur in customers then we will only pick one because that's the common column and that's what happens when you do an inner join it just picks the common values on the other end if you do an outer join then we're going to pick we're going to have a row of row for customer one we're going to have a row for customer two these two are in payments and we are going to have a have a row for customer three and four which don't really show up in the customers column in the payments table at all so this is what it's going to be and there is just going to be empty data here okay then there is the left join and right join so we look at these in a second too okay but before we look at left right and outer joins here are a couple of exercises for you show the full office address and phone number for each employee so once again you can guess what this will look like you will say select star from employees and of course now you want the full office data so you have an office code here so you want to do join with offices on employees DOT office code equals offices DOT office code and maybe let's just put the employee number here let's just put employee number and let's put maybe the last name first name and offices Dot address let's just put City right now offices.city so now you have employee number last name first name let's maybe also put office code so offices DOT office code okay so now we have employee number last name first name office code city city San Francisco and then we also want to show the full phone number so office has a phone number so office has a phone let's check what that is Select star from so office has a phone um so we want to show the offices offices.phone information as well so now we have the phone and then every employee has an extension so we can get the EXT column from employee that is the extension let's just check that select star from employees yeah extension so every every employer has an extension so now we have employee number last name first name office code we have the phone number we have the office City and we have this extension we can combine these two so we can say concat let's get that here so we can say concat and we can concatenate the office phone with the extension so now we are concatenating one column from the offices table and one column from the employee table and put those together and let's just call that um employee phone number or employee phone okay so now we have the employee number last name first name office code and employee phone number maybe let's just put that here okay so now we have an employee number last name first name and then we have the employee phone number which is a combination of the phone number from office and the extension from the employee table and then we have the office code and office City and we've been able to do this because we've done a join between employees and offices okay and that's what this looks like here's another example show the full order information and product details for the order number one zero one double zero so here you would have to look up the order details common or the order details uh table and then from the order details table you would have to join it with the orders table to get information about orders and you would have to join it with the product table to get information about the product and then you would simply have to select the data for a specific order number so here you are doing two joins or you're joining three tables so I'll let you figure that out it's not too difficult it is just it is just one more step removed from what we've done so that's basic joins and specifically that's inner joins now let's talk about left joint right join and full joints unfortunately in this data set there's not enough of an opportunity to demonstrate all three so we're just going to look at some examples online and specifically I recommend W3 schools if you're just looking at some SQL syntax or need some help with SQL they have pretty good documentation on all of these so let's talk about left join okay so here's how left joint works I'll you can look at this table and try look at this picture and try to understand what's happening but we have a demo database here so we have this customers table and in the customers table we have a customer ID one two three and then we have a customer name contact name Etc you have an orders table so the customers table will be on the left the customer's table will be on the left and the orders table will be on the right and what we want to do is we want to join customers table with the orders table on the customer ID column so we say select customers Dot customer name comma orders dot order ID so we want to look at for each customer what are all the orders they've placed right so we want to see customer name and we want to see the order ID from the customers table left joined on orders on customers Dot customer ID equals order Dot customer ID so we want to just combine on the customer ID column and we want to order by the customer name so let's run this on you can actually run this online and check so here's our database we have three customers and then we have three orders of course and okay actually there's there's more than that there are a bunch of customers and a bunch of orders so let's just run this and let's see the result okay in our database we have 91 customers and we have 518 orders and not every customer has placed an order so when we do a left join between customers and orders here's what we get we get a customer name and then we get the order ID so it turns out that Alfred F hasn't hasn't put hasn't created any order so that's why we have null here and similarly this person hasn't created any orders that's why we have a null here so for every customer and every order we could find for every customer and Order combination we could find where the orders customer ID matched with the customers customer ID we have created a row and because this is a left join wherever there were certain customers for whom there were no orders there is still a single row that has been kept so Alfred has not ordered but we still have this null here now if we did not have this left join if we just had join here and let's run that you would see that now you no longer have those null columns so now you no longer have a customer name so if a customer does not have a matching order so if a customer column customer row does not have a matching order row then that will be skipped if you're doing a left join you are telling SQL that you want to keep all the rows from the left table which is the customers table and you don't care about all the rows from the order table but you do care about all the rows from the customers table so even if there is no matching Row from the orders table just show that customer name and then show a null there okay so that's left join then maybe let's look at right join so here's an example of right join here we have orders so we have one table of orders and then we have one table of employees okay now for every order that is placed by a customer there is also an employee that is assigned and then we have a table where we have details of employees so let's say we want to get for each order the names of employees who is assigned to that order and let's check it out so this time we are saying select orders dot order ID and employee last name employee first name from orders and we're saying right join with employees so let's run that and let's see what what happens we are matching on the employee ID column so we get the order right we get the order ID and we are also keeping the employee last name and first name so we for every order ID we get the last name and first name of an employee but of course there are certain employees who may not have been assigned any orders maybe certain employees just don't deal with orders they are into their managed people because we have done a right join we are telling SQL that you don't want to miss any rows from the right table from the employees table so you want to show employees even if they do not have any matching order columns so now west Adam West does not have any orders but we're still showing it here we're just showing a empty or null value here instead of course if we did not have joint right join here then you can see okay let's just do an inner join here yeah if we just did an inner join which means we did not do a right join if we just did an inner join here then we would only get those employee names who have valid orders right so just try to remember this picture where inner join means that the row must that that value must exist in both columns in both tables left join means that all the values that exist in the left table show up right join means all the values that exist in the right table show up an outer join a full outer join tells you that you want to maintain all the rows from the left left table you want to maintain all the rows from the right table as well and you want to fill in nulls wherever possible so here's an example we have a customer's table and then we have an orders table and I believe we are doing a full outer join on customers and orders and this time you will see that there are certain order ID table certain order IDs that may be null and sometimes a certain customer name may be null right so that's how an outer join works so just keep this picture in mind this one outer join means you want to retain all the values inner join means you want to retain just the intersection of the common values left join means you want to retain all the values from the left table right join means you want to retain all the values from the right table okay and one last thing a table can also be joined with itself you don't have to do a join between multiple tables and each instance of the table can be given a temporary Alias so you need to rename those tables because otherwise if you're just joining a table with itself it may get confusing when you are explaining which columns to join on so here's an example remember employees employees have this reports to field which reflect refers to another employee so let's say we want to show a list of employees with the name and employee number of their manager so let's do select star from employees okay so now we have all this information we have employee number employee last name employee first name and then a bunch of information and then we have this thing called reports too so every employee on most employees except the president I believe most employees report to another employee so now if you want to show who they report to we want to get information about who the report to what do we do well first let me just simplify this output a little bit let me just put in employee number and let me put in last name and first name and reports to so that we just have this information that we really need okay now I want to take the reports to number and get information from the employee table and insert that information here as new rows so we say from employees join employees but of course and this is an inner joint of course by default so first Let's do an inner join and then we'll see if this needs to be a different joint so we say employees join employees and now this can get confusing because your both of these are called employees so let's call up call one of these e so you just give a space and then type a temporary name you don't put as here you just put a space and let's call this m so E4 employees and M4 manager okay so now we are joining and how are we joining we want to join now this gets interesting here from E from the first from the left table we want to look at reports to and from the right table M we want to locate the employee number so from the employees table we want to take the reports to information and we want to find the corresponding column where the employee number matches this reports to a number okay so think of employees and managers as different tables they are the same table but think of them conceptually as different tables we capture this number and we get that information from the M table so that's why we have reports to equals employee number and let's grab some information here from the other side as well so from m let's grab the first name and let's just call that manager first name and from M let us grab the last name as well m dot last name and let's call that manager last name and from M let's also grab the employee number well actually we have that already we have reports too so that's that there we go so we have select employee number first name reports to and we want to select okay now reports to let's just put in E dot because this is going to get confusing otherwise or SQL and we want to select m dot first name and we want to select m dot last name as manager last name from the employees table I think I may have a typo here let me just grab this okay I think I may have a typo here so I'm just going to fix this right now but yeah so we have e dot employee number and we have e DOT first name and we have I see what the issue is because we have all these columns in each of the tables so we just need to add e Dot so we say we have an employees table joined with the employees table one is called E1 is called M so we have e dot employee number e dot last name e DOT first name reports to m dot first name m dot last name from employees join employees on the reports to column joined with the employee number column okay so this is probably the most confusing part of it all so now for every single employee who has a manager because this is an inner join remember so for every single employee who has a manager we get their employee we get their manager information as well so we get the name and last name of their manager maybe we can concatenate this so let's maybe just do concat m dot first name and m dot last name as manager name so now we have this information about each employee they we have their last name first name employee number and then we have the employee number they report to and we have the name of the their manager let's just add a space here as well okay now one last thing here that we would want to just cover is there is a certain employee the president who does not report to anyone and we have not shown that information here because we are doing an inner join so because the president Diane Murphy does not report to anybody so that row gets skipped but instead if we do a right sorry if we do a left join what we're telling SQL is we want to maintain the list of all the employees from the left table e even if they do not have a person that they report to so now if you check you have an employee number one zero zero two Murphy Diane and for them reports to his null and of course null value doesn't show up in the other table so their manager name is also null okay so now you see that this is supposed to be a left join and why this is supposed to be a left turn left join and not an inner joint because you want to show all the employee numbers even if they don't have a manager now suppose instead of a left join you had done a right join what would happen now so now we're telling SQL that only show me employees from the left table who have a manager but on the other side on the right side if there are certain employees in the M table which do not feature in the reports to column you should still show