SQL Tutorial for Beginners (and Technical Interview Questions Solved)

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 skills, and this will be part of your daily job. 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 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 Jupyter 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 relational database and how SQL is helpful. Classic Models Incorporated is a manufacturer of manufacturer of small scale models of cars, motorcycles, planes, ships, trains, etc. 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. 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 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 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 customers 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, tables 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-to-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 forms, 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 ERDs 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 offices table, 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 code 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 field. 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 orders, 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 understanding an 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 relationships—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, MariaDB, 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++, 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 these 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.

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 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 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 CRUD 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 CRUD operations on the data.

And before we start writing some SQL code, a quick note on the syntax: 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 3 sales.productID`. You can write `SELECT` in lowercase; you can maybe write `S` as a `s` and lowercase and `E` in uppercase, `L` in lowercase; 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 that at certain places we will use these back ticks or back quotes. So this is called a back quote, 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 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/downloads/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 `/usr/local/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 `/usr/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 `/usr/local/mysql`, there is a folder called `bin`. So I'm just going to type `/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 `/usr/local/bin/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 setting—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 `-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 `-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 an 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 on Ubuntu also I think it's `/usr/local/mysql/bin/mysql`. On Windows, it might be slightly different, and then log in into MySQL. So log in 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—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 team's 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: `classicmodels`, `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 `classicmodels`. 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 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: `sys`, `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 DATABASES` 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 `classicmodels`, 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 `classicmodels`, and now the database is created, and I can check `SHOW DATABASES` once again, and now you see you have `classicmodels` 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 ticks and just in lowercase, 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, great, and if we try to create it again, MySQL will give us an error. So you can see here we run `CREATE DATABASE classicmodels`, 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 classicmodels`, 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 OK, 1 row affected`, but there was no real change. Like if we say just `SHOW DATABASES`, you can see that we have `classicmodels`, `information_schema`, `mysql`, `performance_schema`, and `sys`. So no change here. On the other hand, if we had typed `CREATE DATABASE IF NOT EXISTS classicmodels2`, 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 EXISTS classicmodels2`, you will see now that we have `classicmodels` and `classicmodels2`, 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 `classicmodels`, 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 classicmodels`, and once you say that, then MySQL is going to change the database to use—or is going to point to `classicmodels` 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 `classicmodels` 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 classicmodels`. 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 `classicmodels`, `classicmodels2`, `information_schema`, etc. So I'm just going to do `DROP DATABASE classicmodels2;`, and now if we check `SHOW DATABASES`, you can see now we no longer have `classicmodels2`; that database was deleted. I can also go ahead and `DROP classicmodels`, and then you will see that now we no longer have `classicmodels` 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 tables` 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 `varchar`. 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: `varchar` 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 `varchar(50)`, so it can be a string. `varchar` 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 be `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. 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, 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 OK, 0 rows affected`. So whenever you see `OK`, 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 `varchar`; if most of these are `varchar`, 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 `tinyint`. I think that is going to be one byte, which means it can take values from 0 to 64. A `smallint`, I think that would be four bytes; a normal `int`, I think that would be—a `smallint` would be two bytes; a normal `int` would be four bytes; a `bigint` 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; `bigint` 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 `bigint` just to be safe. A lot of companies have had to change from `int` to `bigint` 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 `int` to `bigint` 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 `datetime` and then a `datetime` or a `timestamp` data type for working with dates. Then you have the character types. So you can have a `char`, which is a single character, or you can also specify when creating a `char` 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 `char` or `char(5)`. So just as we have written `varchar(50)`, you can write `(5)`, and then you always have to put in five characters, or you can put in `varchar`, and `varchar` means that you can put up to 50 characters. So if you type `varchar(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 `varchar`? 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`, `addressLine1`, `State`, `country`, `postalCode`, `territory`. All of these are `varchar`; 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—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 `+1`; 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 `addressLine1`; and maybe I won't provide `addressLine2` 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 `postalCode`; 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 `+91`, 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 560010. 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 OK, 1 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, 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 `*`. So `select * 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 * from offices`. So now when I say `*`, 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 * 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 column1, column2`, so on, `from table_name`, and you can also view the data from all the columns using simply saying `select * 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 columns. 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—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 Bengaluru 2., you will see that now it says that there's a duplicate entry 'AAA' for key 'offices.PRIMARY', and that data is not inserted. Okay. 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 table. So we've created one table, and now we are creating a second table, and we will connect this table to the first table.

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. 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 table before, and there's no employee table 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 a 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 varchar 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 varchar of a hundred; and then we have a reports to.

Two interesting things here: one is office code, so we have now an 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 Biraj reports to Siddhant, then reports to in the entry for Biraj should be the employee code or the employee number of Siddhant. 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 int 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—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 to 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 1002, so that's the employee code; Murphy, Diane; x5800; and so this is what—is this employee number, last name, first name, so Diane Murphy 1002, 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 1002 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 1002. 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 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—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 to. Okay, that is something that we haven't tried, so reports to 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 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 exists, so this is another way to go about it: create a table only if it—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—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; it 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.employees, 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—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 SQL 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—buy 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 star 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 two, we have Julie Farrell who's a sales rep; at office code three, we have George Wanu who's a sales rep, and so on. So the where clause supports a bunch of operations; where supports 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. With 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 some 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 3. 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 star 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. 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 1002. Again, we should put in quotes here: update employees set reports to to 1002, 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 to to 1002 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 to. 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 1002. 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 1002. 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 a 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 Spain; 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 1370, 1313, 37, uh 1702, 1401, and 14. 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. 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. 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. Percentage represents zero or more characters, and then underscore represents exactly one character. Okay, 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, trucks, 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: column one, column two. 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, 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.

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 an 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.

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. 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 1002, and let me change this to an actual date: 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 .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 /user/local/MySQL/bin/MySQL. So this is the MySQL command line tool, and then I say -U root, so I want to log in as the root user, and then I also say -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.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/user/local/MySQL/bin/MySQL. So I'm just going to open this first: -U root -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 -U root -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 provide the name of the database. So we provide the name of the database after -U or even at the beginning. So MySQL dump; the database that we want to dump is called classicmodels. 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—add drop table classicmodels -U root -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, dbdump.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.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 classicmodels database into a SQL file; then drop the classicmodels 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 classicmodels 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 * from employees, and just use classicmodels. Okay. Select—select count * 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 * 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(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 by 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 classicmodels 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 Jupyter. 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 W3Schools 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 db-hyphen-book.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 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 db-hyphen-book.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 classicmodels 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 an 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—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 Jupyter and get the results immediately. In fact, Jupyter—just like Python—there is a way to type SQL directly into Jupyter 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 Jupyter 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 MySQL's 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 CRUD 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 SQLAlchemy 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: /user/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/downloads/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 is click plus, and here you can then provide—this is a connection name that you can set, but you have to provide—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 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 classicmodels, 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 separation is clear to you. So here's the MySQL Workbench; we have that running. And in this tutorial we will use the classicmodels database from the previous tutorial. So classicmodels 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 classicmodels are sold in toy and gift stores around the world. So here are some of the products they sell, and classicmodels has offices around the world with dozens of employees. And the customers of classicmodels are typically toy or gift stores. So each customer has a designated sales representative who is an employee of classicmodels 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—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 officeCode, which is the unique identifier. Then we have the employees table; so we have an employeeNumber, lastName, firstName, extension, email, etc. And there are a couple of relationships here: there is an officeCode in employees. So for every employee there is an officeCode, which is the officeCode of a certain office. So this is called a foreign key, because an employee cannot have an officeCode which is invalid. And then you also have reportsTo. So reportsTo tells you which employee—an employee reports to. So this is a foreign key from the employee table to itself, right? So reportsTo must be a valid employeeNumber of another employee. Then you have customers, and you have a bunch of information about customers. customerNumber is the primary key, and every customer has a salesRepEmployeeNumber, 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 customerNumber foreign key here. But apart from that, orders can have a lot of items. So there is an orderdetails 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.

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 an 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, 189 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 classicmodels.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, 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 10 28. So that gives us all the payments that have occurred before 2004 10 28. 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, distinct 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 10 28. Okay, so that's how you chain 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 10 28, 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, 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 payments 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 sum 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 sum amount. Okay. So these functions are generally work all work in the same way: sum, 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, sum 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.

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. It turns out that in this case customer number 103, the largest num the largest payment is fourteen thousand dollars and the smallest is six thousand, but for 112 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. Great, 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 by 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 ucase. So ucase is going to make it uppercase, and lcase is going to make it lowercase. So I just call ucase on first name, and let me maybe do lcase for now on contact name, and you can see here the first name becomes 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 ucase 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 substring 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 lcase 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: 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 desc 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.

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. There's a lot going on here; let's go step by step.

First thing is we want to get some information out of the products table, right? So let's go `select * from products`. Yep. So now we select a bunch of products, all right? And what are we interested in? Product code, product name, Buy 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 MSRP—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 `round` 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, multiply, 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.

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 going to go here and first of all, just to `select * 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 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. 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 * from select * 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 year 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 year, so I `select *`, 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 year 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: `, *`. 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—of course we don't—like this is still showing us every individual payment; like if I put a comma `*` here, you will see that we are still looking at—yeah, if I just instead of the amount if I put `*` 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 year 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 year, 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, 1, and the sum is 26,000, and then 2003, 2, 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 00003 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,384.36 just by adding this `format` keyword here, and of course now it's no longer a number—nowadays become a string—and let us also rename this column as Revenue. So now we have revenues: 144,384.36. 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 144,384.36, 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—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 year, 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 YYMMDD format, then you put something like `%y-%M-%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 `%B` here, so I can take this `%B`, and if I just take this `%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 `%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 `%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 `%B` can give us the abbreviated month name; if you want the full month name, then we can use `%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 year` and we have `payment month`, but now because we are ordering by `payment year` 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 year`; that's fine, and we have created a `payment month`, great, and we've created a `month number` because we also want to track—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—sum 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—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. It will make more sense. Show the 10 most recent payments with customer details: 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.customer number equals customers.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.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: 609, 603, 525 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 an 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 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.office code equals offices.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.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.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 10100. 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 join, right join, and full joins. 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 join 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 1, 2, 3, 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.customer name, comma orders.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.customer ID equals order.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.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 an 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 customers 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 to. 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 join of course, by default. So first let's do an inner join, and then we'll see if this needs to be a different join. 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 E for employees and M for 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 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.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 to, 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. because this is going to get confusing otherwise, or SQL, and we want to select m.first name, and we want to select m.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.employee number, and we have e.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. So we say we have an employees table joined with the employees table; one is called E, one is called M. So we have e.employee number, e.last name, e.first name, reports to, m.first name, m.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.first name, m.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 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 1002, Murphy Diane, and for them, reports to is null, and of course, a 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—left join and not an inner join—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 them. So let me run that, and let me show you the result. So it seems like the result hasn't changed. So one thing that has happened is Diane Murphy has gone away, but if I scroll down here, you will see that now I have Leslie Jennings, Leslie Thompson, Julie Farrelli—all these people who do not have any reportees still show up. This is because we are doing a right join; we are saying that every row from the M table should be represented even if they don't have any matching information in the E-table. So that's the—that's the issue with the right join. And if we did a full outer join, or I think it's just called an outer join, okay, I'm not sure what the outer join in MySQL is; I guess it's just called full outer join. Okay, I think I'll have to look it up. But if we did the full outer join, then we would see this; we would see Diane Murphy, and we would also see all this information, Larry Bolt, etc. Okay, we'll check this out; what—how do you create an outer join in MySQL? Okay, never mind. So that's joins, and here are some exercises for you to try out to become familiar with SQL joins: Report the account representative for each customer; report the total payments for the Atelier Graphic—this is one of the—I think this is one of the customers; report the total payments by date; report the products that have not been sold ever; list the amount paid by each customer, and so on. So each of these will require a certain join; not all of them may require joins, and for some of these, it's possible that you can do them without joins. In some cases, you will also have to do some other things like arithmetic operations, etc. For example, list the names of products sold at less than 80 percent of the MSRP.

There are two ways to improve query performance in MySQL, especially when you're working with really large data sets. One is to create an index. Now, if you often search or order data by a particular column that's not a primary key, then you can add an index to make the query performance faster. For example, if you search by customers' last name a lot, or if you order by customers' last name or not, then you should do this; you should run this command: create index customer last name, and give it whatever name you want. So you say create index so and so on the table customers using the column contact last name. So if we do this—if we just run create index customer last name index on customers—what we're telling SQL is that we want SQL to track the contact last name information, the contact last name column in the customers table and set up a way for us to efficiently do queries on that column. So what does this mean? Well, the next time we do a query on the last name—let's say we do something like this: select * from customers where contact last name equals Lee—because contact last name has an index, this query will be very fast. If the contact last name did not have an index, then this query would be fairly slow, and the difference would be: if the contact last name did not have an index, then SQL would have to search through all the rows, but if the contact last name does have an index, then SQL can actually avoid searching because it has set up some ways to efficiently track information and search information from the contact last name column. In fact, it is using a data structure called a B-tree, and you can see what kind of index is created, or what kind of indexes you have on a table, simply by typing show index from table name. So you can see that we have the primary key, which is tracked using an index, because typically you extract information using find primary key; we have a foreign key, so every foreign key also creates an index, but then we also have this customer last name index, which we just—just created, and this index is represented as a B-tree, which is a data structure—an efficient data structure optimized for querying. So whenever you query by last name, that is very efficient; not only querying, but ordering by last name is also efficient. So you could do something like this: select * from customers order by contact last name limit 10. Again, if you have an index, this is going to be fairly efficient; if you don't have an index, it's going to be slow. It's not going to make a difference for this database that we are working with right now, but when you have a really large data set, a really large database with millions of rows, then this will start to make more sense. So if you are doing some queries and those queries seem to be taking a long time, maybe consider creating an index, or maybe consider asking the database administrator to create indexes on the columns that you are ordering by or searching by most frequently. Okay, and indexes can also be created on multiple columns if you put conditions on multiple columns frequently, so you can check that out.

Then another thing that you can do is create something called a view. So if you perform a query, or if you perform a query very often, or if you very frequently join tables for querying, then you can create a virtual table out of that query, and this virtual table is called a view. So here is how a view is created. So we'll see how views created and how it's used, and then you probably see more—learn more about how it works. So to create a view, you say create view USA customers as—you type the name of the view, and you type as, and then you type a query like you normally do. So instead of doing select * from customers where country equals USA—so this is all—this is the information about all the American customers—what we want to do is we want to take this query and we want to give it a name, USA customers. So let's say—let's say create view USA customers, and that is now going to represent this query, the word USA customers. How do we use it? We use it just like a normal table. Now, let's say we want to get just the customers who are in California. So we can do this: select * from USA customers. So now we refer to this view, which we have created already; it's saved in the—in the database—that view. So select * from USA customers—customers who are from the USA—where State equals CA. And when we run this, you can now see that we only have customers from USA, of course, that's because this—the state is CA, but you can see that USA customers actually used information from this query that we had defined earlier. Okay. So conceptually, what's happening is wherever the SQL Server sees USA customers, it's simply going to replace this with this: select * from customers where country equals USA. And in fact, you can also run this. Okay, this is not going to work directly, but yeah, uni—once you create a view and use the view—so SQL is going to replace the view name with the actual query that you have provided. Now, typically when you create a view, no new data gets created; it's simply that information that is tracked—which query—view represents. But certain relational databases also support creation of materialized views. So let's say you're doing joins between certain tables very often, and then you are getting information about the—from that joined table—from that joined uh query, then you can create a materialized view, which is going to contain the joined version of the two tables, so that whenever any of those two tables is updated, the view gets recreated, and that is uh like a separate table that is always tracking the latest version of the joins of the two tables, and that takes up more space, but that makes it very efficient to query. So now, whenever you make queries on that view, instead of re-running the query that creates the view, it—it will have the view already as a table saved within the database, and all that information will be cached, so you can get that—you can get the result out of the view very easily. Okay. So right now, by default, we are not actually creating a table USA customers, but if you make this a materialized view, then the database is actually going to set up this table call USA customers somewhere, and that is going to be kept up to date with the list of customers from the USA. Okay. So that's materialized views; not something you'll have to worry about or work with often, but in case you come across the term, that's what it means.

Let's talk about executing SQL queries using Python. One way we've seen how to execute SQL queries is using the MySQL command-line interface. Another way we've seen how to execute SQL queries is using the MySQL Workbench, and the common thread here is all you need is to connect to your SQL Server, your database server—that could be running locally, that could be running on the cloud—and once you have connected to that server, you need to be able to send instructions to the server, the queries that you wanted—that you want to send—that you want to perform, and then the database server is going to give you back the results, and you should be able to understand the results. So this is something that you can do from any language; every language has some libraries for working with SQL relational databases. In Python, one of the most popular libraries is the SQLAlchemy Library. So you can look it up; this is SQLAlchemy, or SQL Alchemy, and it's a very powerful toolkit for Python that pretty much everybody who uses SQL databases and does some programming in Python uses this library. In fact, we use it on Jovian; when you open your profile page to get the information for your profile, we use the SQLAlchemy library to get information out of a MySQL database, and then we display that information to you on the profile page. Okay. So it's a very powerful and a very efficient library as well. As it says here, it provides enterprise-level persistent patterns designed for efficient and high-performing database access adapted into a simple Pythonic domain language. Okay. So that's the library we are going to use. Now, here's the other thing: there is not just a single relational database; there are many. In fact, here are some: MySQL is one that we've looked at. Apart from that, there is something called PostgreSQL. So this is sort of a slightly more advanced version of MySQL, where it also supports latitudes and longitudes; it also supports like a JSON column type; it has more column types. Um, then there is something called SQLite. So SQLite is a less—is a limited version of MySQL, and SQLite is typically used for situations where you can possibly—you possibly cannot set up an entire server, and you just have to maybe work with something very light. So SQLite, instead of setting up a proper database server, etc., simply uses a file on the disk, and that file just works as a database. Of course, everybody uses a disk, but SQLite just makes it very efficient and very fast and very small. So typically this is used in, let's say, in—in your phones. So any Android app or any iOS app that you use is—if it stores any information on your device—typically that store—that information will be stored in an SQLite database because it has very low memory and CPU requirements. Of course, it is—it does not implement the entire SQL language; it only implements parts of it, and it sort of cuts corners here and there to make it possible to use SQL in a very—a low memory setting. In fact, in your—in your assignment, you will be using SQLite, so that—that is something that can run easily on Binder; setting up MySQL and Binder is a big challenge, so we're just going to use SQLite. Apart from that, you have some proprietary databases like Oracle. So Oracle has its own database; again, that has its own flavor of SQL. Then you have the Microsoft SQL Server, so that has again its own version of SQL. There are some changes; not all the commands work—work exactly the same way. And in fact, if you look up any SQL tutorial, they will tell you what the differences between the different flavors of SQL are. Right. For example, w3schools is the one that I would recommend, and here on the side, you can see all the possible different um things that you do—that you can do with SQL. And if you open any specific example here, like ALTER TABLE, you will see how these are different across different SQL databases. So ALTER TABLE, which is to change the column name, etc., this is how it works on SQL Server; this is how it works on MySQL; this is how it works on Oracle. In this case, I think they look the same; there's no change here, but there are other things, probably with constraints, where things would be different. Yeah. So you see here, this is how it works with SQL Server; this is how it works with MySQL. So if you're putting some sort of a unique constraint in SQL Server, you put it here; in MySQL, you put it at the end, and so on. So just look things up; don't worry about knowing all the different variants of SQL, but the way it is relevant for us here is SQLAlchemy does not include all of these connectors to all of the different types of relational databases. So what you'll have to do is maybe you'll have to use some kind of a connector, a connecting library. So for example, if you are connecting to MySQL, then MySQL Connector/Python is a commonly used connector. So you will have to either use MySQL Connector/Python, or there's another connector called PyMySQL. So there are several connectors for MySQL, right? So SQLAlchemy gives us the Python interface for writing SQL queries, and then internally, it uses one of these libraries to connect to the specific kind of database that you want to work with. So you'll need to install both. So now we are going to install SQLAlchemy, and we are going to install PyMySQL. So let's install both of them. SQLAlchemy handles the Python side; PyMySQL handles the MySQL side. And I'm just going to create—clear output here. And let's import—so here's how you connect to a SQL database. The first thing you do is you say from SQLAlchemy import create_engine. So import create_engine, and create_engine takes a string, a connection string. So what you do is you say create_engine, and then you give it this string, this database connection string, which contains all the information required to connect to the database. Now, remember MySQL Workbench; here, when we try to connect to a new database, we would have to provide a hostname; we would have to provide a port; we would have to provide a username and a password. So it works exactly the same way here. We say uh what kind of database we want to connect to, so we want to connect to a MySQL database, so that's why we have MySQL here. We say what connector we are using. Now, even for MySQL, there are several connectors that people have written for SQLAlchemy, so we—we are using the PyMySQL connector. If we were using the MySQL Connector/Python connector, then this value would be different, but in any case, we want to use the MySQL database, so this is the kind of database that we are using, plus this is the kind of connector we are using, PyMySQL. Then we say colon dot dot, and this is just a standard syntax. Then we provide the username, and after the username, you provide the password. So normally you shouldn't type passwords out, but let's say if you wanted to provide the password directly, you would say username:password, then you pro—then you type @, and then you type the server, so the hostname that you put here, 127.0.0.1, which means the local computer—this is just short for the local computer—is a standard hostname, um, and that is also known as localhost. So this could be 127.0.0.1; this could be localhost; both of those mean connect to a database on this computer. But if you are connecting to a database somewhere on the cloud, then you should get the username and the password and the hostname for that cloud database. Next, you provide the port. So for every database, there are certain ports that you connect at; 3306 is the default port that is used in most databases, but if there is a different port, then you can ask your database admin to give you the port. So once again, you get the type of database that you're connecting to, the type of connector you're using.

colon// the username password at localhost: the port/ the name of the database that you're connecting to. Okay. This is not something you have to remember; you can always just look up SQL Alchemy connection string. Let's say you want to look it up for SQL Server; so you can just search "connection string for SQL Server," and you can see what that connection string will look like. So for SQL Server, this is what it will look like: myseek Ms SQL plus pyo DBC. So the pyo DBC is the connector, and then username password add server. Server would be localhost:3306 or some other online server/database.

Okay. Now, one thing I've done here is instead of putting the password I want—I don't want to type my password into a Jupyter notebook—so I have just imported getpass, and I'm going to run getpass. I'm going to type the password here. So now the variable password contains the value of the password that I just typed in, but I'm not going to display it in front of you because I don't want you to show you—I don't want to show you—I don't want you to see my password. So what I'm then going to do is use string formatting to insert the password into this string. So now I have the connection string inside which I have this gap using these quotations, using these brackets, and then I'm calling .format(password), and that's going to put the password into that gap. Okay. So never put your password directly into your Jupyter notebook, especially at work, because that's going to be very bad because then anybody who has your Jupyter notebook will be able to use your account. Always use getpass. So just put the password into a variable and then pass that variable using string formatting into the connection string, and that then creates this engine.

Okay. So now we've created this engine. So what has happened so far? Nothing really; it we've just set up this object, and we've given all this information to SQL Alchemy. Then we actually connect to the engine; we say `with engine.connect() as con` (c-o-n-n), and then we can execute SQL queries. Okay. So we connect to the engine as `con`, and why do we have this `with`? Well, this automatically takes care of closing the connection once we get the result. So we say `conn.execute()`, and now I'm connecting to my local database. So make sure that if you are running this on your computer, you have the SQL Server MySQL server running, and if you're running this on Binder, obviously it's not going to be able to connect to your local computer. So make sure that you're running this locally on your computer. Okay. So now we say `connection.execute()`, and we have a query. So we have `select office code, City, phone from offices`. Let's see what this query gives us in the SQL workbench. Okay, maybe first I should do `use database classic models`, or just `use classic models`. Yep. And now I can `select office code, city, phone number`. So expect to see information like this: seven office codes, and then the city and phone number. So I get the result from `con.execute()`, and let's look at the result. The result is this Legacy cursor result; it doesn't give me much, but you can convert that result into a list, so you just call `list()` on it, and that gives you this list of offices or list of results. Let's check that. So here now you get: One San Francisco +1 650 2192, Boston +1 215, and you can match that information up here.

Okay. Quick recap: install SQL Alchemy, install the connector, import `create_engine()`, then create the engine using this connection string, which contains information about the kind of server, the kind of database you're using, the kind of connector you're using, the username, password, hostname and port, and database name. Of course, don't type your password directly; get it using `getpass()`. Once the engine is created, connect like this, and then get the result, and now you have this result here. So now once you have this result, this is a Python list, and it's a list of tuples. The idea I wanted to take away is that you can create this engine, create a connection, and then get data out of a SQL database using Python. Now, of course, this is a lot of work, and that is where you have this nice neat little Library called IPython SQL, probably one of the most wonderful libraries I've come across because it takes away so much of the complexity that you have to take care of when you're connecting to SQL databases.

So the IPython SQL Library provides something called Magic commands to write raw SQL queries in Jupyter. So you just type raw; you just type a SQL query within Jupyter, and that will execute, and that will get you the results, and it'll give you the results in a nice tabular format, something that you can use within Python. And in fact, it uses SQL Alchemy under the hood. So you want to have SQL Alchemy installed, and you want to have the right connector installed. So for example, in this case, I'm using PiMySQL. So I have SQL Alchemy installed, I have PiMySQL installed, and now I'm going to install IPython SQL. So install the IPython SQL Library, and now it really is Magic because—and these are called Magic commands—the first thing you do is you have to load this extension within the Jupyter notebook. So you say `load_ext`. You type this percentage symbol; you type `load_ext`, and then the extension that we're loading in is called SQL. Once you type this—once you run this cell—`load_ext SQL`, then you will be able to start using this new command called SQL. So anything that starts with a percentage is called a magic command in Jupyter. This is no longer plain Python; now this is some instruction that we are sending to Jupyter.

So here's what you can do: you can type `%SQL`, okay, and then you give it a connection string. So for example, if you give it `MySQL+PyMySQL root:`, and then you put in the password here—let's say the password is just password—at localhost:3306 and /classic models. Okay, and I'm just going to reveal my password here for a second. So I'm just going to—the password is—well, it's akash123. So if I run this classic models—yeah. So if I run this, now I am connected to the—now I'm connected to them—to the database, right? I have just typed `MySQL+piMySQL:root`, so username/password. So this is a SQL Alchemy collection string. Now, instead of having to do `import engine`, `create_engine()`, etc., etc., I can just type `:SQL`, give it the connection string, and that is just going to connect to the—to the database. Now, what to do if I don't want to reveal my password? I would do `from getpass import getpass`, and then I would run `getpass()`, and okay, maybe I should put the result into password. Okay, and then I type my password. So now this variable password has my password, and then I can take this and just put it into a string. So I create a normal `connectionStr`; I create a normal Python variable, and I just insert the password here. So I do `.format(password)`, and now I've created this connection string. So now I want to—instead of typing out the full connection string—I want to pass the variable `connectionStr`, and we do that by—in Magic commands—we pass value from variables using these brackets. Okay. So when we type this—when when we have—when Jupyter sees this bracket, it says, "Okay, let me take the value of `connectionStr`, which is all of this with the password replaced here, and let me replace this with that value—uh, sorry—with with all of this stuff." Okay. So this is all a roundabout way of hiding my password, but in any case, the—the point being you just provide the connection string; either you type out the entire string including your password, or you put the connection string into a variable, and you pass it using this bracket. But just with this one line of code, we are now connected to the database, and we can now execute queries. And here's how you execute queries: you type `%SQL`, and then you just type the query like `select office code, city, phone from offices`.

So there you go. So now you can see that it's telling me that I'm connected on using this connection root, and it hides a password thankfully. So yeah, so we've typed this query, and here is the result of the query. So the result is office code, city, phone: 1, 2, 3, 4, 5, 6, 7, San Francisco, Boston, etc., and the phone numbers. If you have a multi-line query—something that you need to split across multiple lines—then just put a double percentage, and when you put a double percentage, then this entire cell is passed to this magic command. When you put a single percentage, that just works for a line; the double percentage works for an entire cell. So let's run that. So now here we are doing the same thing; remember, for every year and every month, we want to get the total payments. So that's what we have here: year, month, total payments, by—as a result of this complex SQL query—`%%SQL`, and then you type the name—they type the query—and that's the data. Here's when it gets even more interesting: you can actually combine Python code and SQL code. So here's what I'm doing: I'm saying `earnings_result =`, and then from this point on, once I type `%SQL`, Jupyter detects that this is a magic command, and it's going to read all of this as SQL code. Okay. So what's happening is that all of this is being passed into this magic command `%SQL`, and that magic command is going to—under the hood—use SQL Alchemy, create a connection using the connection string that we had set up, and pass this SQL query to that connection, and from that connection get some results, and then take those results and convert that into this format. Okay, and then that data frame kind of format is going to be put into this variable `earnings_result`. So you type the variable name, you type `=`, and then you type `%SQL`, and then you simply type the query, and now you have `earnings_result`.

That was the topic for today. As I've said, W3Schools is a good resource, and I would just go through all of these just to get a sense of everything SQL has to offer and use this notebook to learn how all of these things fit together. So we looked at aggregation, grouping, pagination in SQL queries; we looked at mapping functions, arithmetic, and working with dates; we looked at how to combine data from different tables using SQL joins; we looked at improving query performance with indexes and queues; we looked at how to use Python to execute SQL queries, which is to get SQL Alchemy, get a connector, created connection string, pass—put the password into it securely—create an engine, runs queries; or we could just use the IPython SQL magic commands and simply use direct raw SQL statements inside a Jupyter notebook.

So what should you do next? You should definitely check out some of these other challenges on the classic models database. This is from this professor; his name is Richard T. Watson. It's a great database, and he has also given you a lot of questions to solve. So there are—what—about a 100 or 120 questions that you could um try and solve. So the more—it's all about exercise SQL; there's no other way to become an expert at SQL, and I wouldn't say I am because I haven't done enough exercises. Here's one more set—a different database that you should look at—is the Database System Concepts book on this book page on dbbook.com. You have a bunch of exercises. So you can check here: Sample Lab Exercises and Term Projects. So here you can figure out there is a basic SQL file just like the classic models database, and there are a bunch of exercises like: here are some Advanced SQL exercises, and this is all based on a university database. So here you have all these questions: "The university allows an F grade to be overridden by any pass grade. Create a view that lists information about all fail grades that have not been overridden, and the view should contain all attributes from all the tech—all the takes—from the takes relation." Right. So there's a lot going on here, and of course, this is more advanced, and then you have some more basic and intermediate questions as well, right? So these are two good databases: the classic models database and then the student University database. You also have solutions for the classic models database. So if you want to check out Solutions, this person is very kindly put up their Solutions, so you can look up the solutions to the different Channel challenges here. Yeah, so these are some of the solutions for the classic models database. Try that out. Then you have these interview questions with SQL. This is a—this repository has been created by Alexi Grigurev. He actually did a webinar recently—a few weeks ago—with us—webinar on machine learning, but he has created this repository called Data Science Interviews, and specifically in this repository, you have under Technical Questions—yeah—under technical.md, you have SQL. So just check out SQL, and here you have a bunch of questions—I think probably over 50 or 60 questions—and in—you can see some of these get pretty big, but the concepts are the same: you see select, order by, join, and then you're taking the result of one query and then using that as a table in another query and so on. Okay. So that's SQL for you. Yeah, I think there are about—what's this—11 questions here, but they cover a lot of different use cases. Then W3Schools is a great place to learn. SQL Alchemy is again—if you want to learn more about SQL Alchemy, the object relationship mapping—here's one tutorial that I found and went through; it seemed good. Otherwise, reading the official documentation is always the best and most comprehensive resource. I wouldn't worry too much about this right now, as long as you can just connect to databases and make some queries, you should be fine. And finally, if you really want to get a book, then this is the book I would recommend. This is the book. So one of the authors is the professor who I learned SQL under; he was my college professor, but yeah, this book overall is still great, and even if you don't want to get the book, I would definitely check out the slides whenever you want to get deeper into SQL. I would definitely encourage you to check out the slides on this book for sure.

Hello everyone. This is Samantha, and welcome to the series "Solving SQL Interview Questions." In this series, we'll be covering four SQL interview questions that were asked at Amazon, Netflix, Uber, and Google. Now, before we go ahead and start solving these SQL interview questions, this is something that I'd like to introduce you to first, and that is a three-step approach for solving any SQL interview questions. Generally, in an interview, you're not always expected to give the correct answer, but the interviewer would like to know what is the approach that you have tried for solving a particular problem, and this is where a three-step approach might help you. Step one of our approach is understanding the inputs and the outputs. Do not jump into solving the query as soon as you see the question. Understand what are the inputs that you should be using, and what are the outputs that your final result should be containing. Step two: articulate your solution verbally. Once you've decided what are the inputs and the outputs, explain to your interviewer what is the approach verbally. Get a confirmation of your understanding, and then start writing your query. Step three: construct the query step by step. Once you've decided what are the inputs and the outputs, once you've gotten a confirmation of your approach, break down your SQL query into steps and print out the results of these intermediate queries, and then improve your final result. So now that you've gotten an idea on how to approach an SQL interview question, let's go ahead and solve one. As you can see on the screen, the platform that we'll be using in the entire series for solving the interview questions is known as StrataScratch, and if you look at the screen on the left side, you can see the questions and all the required information that you need to know, and on the right side you can start writing the query. Now, make sure that you're signed into stratascrash.com before you actually run the code, or else you will not be able to run the code. Unfortunately, you'll not be able to check whether the solution is correct or wrong, but we'll be sharing all the required links for practice and to check whether your solution is correct or wrong down in the description, so do not worry much about it. In this video, we'll be solving an SQL interview question asked at Apple. As you can see on the screen, the problem that we'll be solving today is "Customer Details." Find the details of each customer, regardless of whether the customer made an order. Output the customer's first name, last name, and city along with the order details. You may have duplicate rows in your results due to a customer ordering several of the same items. Sort records based on the customer's first name and the order details in ascending order. As you can see, we have two tables: Customers and Orders. Let me just quickly have a look at all the columns that are present in both the tables. Customer table contains ID, the first name, last name, city, which we want, and the Order table contains ID, Customer ID, which I believe this Customer ID is the same as the ID present in the Customers table, and Order date and Order details, which again is something that we want according to our approach. The first step of solving an interview question is understanding the inputs and the outputs. Now, what are our inputs? Since they said find the details for each customer, regardless of whether the customer made an order, I am going to use the Customer ID as one of the inputs, and they have mentioned what the output should contain, and they've mentioned that it will contain some duplicates. Now, they also said to sort records based on the customer's first name and the order details, so my inputs will also contain customer's first name and Order details to perform the sorting, and the output should contain customer's first name, last name, and the city along with the order details. So these are going to be my inputs and the outputs. The second step of our approach is articulating the solution verbally to the interviewer. In our approach, we have established that the output should contain first name, last name, city, which is coming from the Customers table, and it should also contain order details, which is coming from the Orders table, which means we have to combine both of the table results to get the final output, and for that I can perform a join. Now, what join do I have to perform? Given that I am joining Customers table on the Orders table, I'll have to use a left join so that I'll get all the customer details present in the Customers table irrespective of whether they've made an order or not, and that's what the question states. So I'll be performing a left join on the Customers table with respect to the Orders table to get the final output result. Finally, they've also mentioned that we have to order it by first name and also order details, so this is going to be my approach. Now, the third step of our approach is constructing the query step by step, and the first part of our query construction is performing the join. `SELECT`, since my output should contain first name, last name, City, and the order details, these are the column names that will be present in the `SELECT` statement: `first name, last name, City, order details FROM customers LEFT JOIN orders ON customers.ID = orders.cust_ID`, because we have established that Customer ID is the ID that is common and similar to the ID in the Customers table. So let me just run this. As you can see, the left join has been performed, and whenever there is a customer who made an order, the order details value is filled, and if that customer has not made any order, that order detail section is empty. Now, the second part of our query construction is ordering this by order details and the customer's first name. So I'm just going to say `ORDER BY first name, order details`, and I'm going to run this. As you can see, this has been ordered according to the first name and then followed by the order details. So yeah, that's what they've been asking us in the question. Thank you so much for watching. Welcome to the series "Solving SQL interview questions." In this video, we'll be solving an SQL

Interview question asked at LinkedIn: The problem that we are solving today is salaries differences. Write a query that calculates the difference between the highest salaries found in the Marketing and Engineering departments. Output just the absolute difference in salaries. The tables that are given to us are `DB_employee` and `DB_Department`.

According to our approach, the first step of solving the SQL question is understanding the inputs and the outputs. For that, I quickly just want to have a look at all the columns that are present in these two tables so that I'll know what inputs I have to give to the table to get the salaries difference output. As you can see, it contains ID, first name, last name, salary. So since they asked us to calculate the highest difference between the salaries, I'm going to use the salary column as my input and this department ID. In the `DB_Department` table, I have ID and the department. I believe this ID is similar to the department ID present in the employees table. And as they mentioned in the question, they want specifically for Marketing and Engineering departments; department is going to be my other input. What is going to be my output? Output just the absolute difference in salaries. So these are my inputs and the outputs.

Now, the second step of our approach is articulating a solution verbally to the interviewer. As we've discussed, the columns that we'll be using are salary and the department, and what they've asked us to calculate is the highest difference of the salaries between the departments Marketing and Engineering. So when they're saying highest, you probably are expected to use the aggregation function `MAX` in the each department and calculate the difference between them. So my approach is probably going to be: first thing, I'll perform a join between the `DB_employee` and `DB_Department` so that I can get the department name. The first thing I'll do is write a `SELECT` statement which will get me the maximum salary from the department Marketing, and I'll write another `SELECT` statement which will get me the maximum salary from the department Engineering, and then I'll calculate the difference between this `SELECT` statement and the other `SELECT` statement, get the absolute difference, and that's what they're looking for. There's also another way that you can approach this problem without having to use two `SELECT` statements, and that is by using a self join with the help of table aliases. Now right now we are performing a join of `DB_employee` with `DB_department` so that we can get the department name. Right, what if we know what is the department ID of the department names Marketing and Engineering? We can simply just use this department ID within this table, calculate the maximum salary of that particular department and minus it with the maximum salary of the other department by performing a self join.

Now, the third step of our approach is constructing the query step by step. In the first part of our query construction is calculating the maximum salary for the departments Marketing and Engineering. So for that I'm just going to say `SELECT MAX(salary) FROM DB_employee JOIN WITH DB_Department ON DB_employee.Department_ID = DB_department.ID`. I'm going to give the `WHERE` clause for the department so that I'll get that specific department maximum salary `WHERE DB_department.department = 'Marketing'`. So let me just execute this, and as you can see, I got the maximum salary for the department Marketing. Similarly, I'll also be calculating the maximum salary of the department Engineering, and then I'll get the absolute difference between these two `SELECT` statements. So for that what I'm going to do is I'm going to put this entire statement in one bracket, and I'm going to do minus, then I'm going to copy paste the same syntax here because it's almost the same thing except for the Marketing name; instead I'm going to say Engineering. So these are the two `SELECT` statements, and I'm going to perform absolute difference between these `SELECT` statements so that I will get the difference between the highest salaries of the departments. So I'm going to say `SELECT ABS(difference of these)` and I'm going to give this an alias name as `salary_diff`, and then I'm going to execute this. As you can see, we now have got the absolute highest salary difference of the departments Marketing and Engineering. So this is one way of our approach. As we've discussed, there's another way of approaching the same problem, which is by using a self join. First let me just see what are the department IDs for the department names Marketing and Engineering. So I am just simply going to run this, and as you can see Engineering is having the department ID 1 and Marketing is having the department ID 4. So using these numbers in the department ID column of the `DB_employee`, I will perform a self join to calculate the highest difference. So for that I am going to say `SELECT MAX(a.salary) - MAX(b.salary) AS salary_difference FROM DB_employee a JOIN DB_employee b ON a.Department_ID = 1 AND b.Department_ID = 4`. One is for Engineering and four is for Marketing, and I'm calculating the maximum salary of the Engineering and maximum salary of the Marketing, and I'm performing the difference. And since they said they want the absolute difference, I'm going to say `ABS(this)`, and then run the score. And as you can see, we got the same answers. So yeah, that is how you approach these kind of SQL interview questions. Thank you so much for watching.

In this video, we'll be solving an SQL interview question asked at Facebook, which is a part of Meta. Let's go ahead and start solving. As you can see on the screen, the problem that we'll be solving today is popularity percentage. Find the popularity percentage for each user on Meta Facebook. The popularity percentage is defined as the total number of friends the user has divided by the total number of users on the platform, then converted into percentage by multiplying by 100. Output each user with their popularity percentage; order records in ascending order by user ID. The `user_1` and `user_2` columns are page of friends.

According to our approach, the first step of our query construction is understanding the inputs and the outputs. Let's go ahead and take a quick look at the table, and we can see that it is `user_1` and `user_2`, and these `user_1` and `user_2` columns are pages of friends. So we have to use both `user_1` and `user_2` as our input columns, and the output should be each user along with their popularity percentage.

Now, the second step of our approach is articulating a solution verbally to the interviewer. According to our approach, they've asked us to calculate the popularity percentage, right, which is basically total number of friends a user has divided by total number of users on the platform into 100. Before we actually multiply with 100 and get the percentage, we first need to figure out how to calculate the total number of friends a user has and also the total number of users on the platform. Before I figure out what method or function I need to use to calculate the total number of friends a user has or the total number of users, I just want to have a quick look at how the data is in the table. For that, I'm just going to come back here and see `SELECT * FROM Facebook`. I'm going to run this, and as you can see there is `user_1` and `user_2`, where `user_1` is friends with `user_2`. One and one is friends with three, and four is friends with one. This seems to be a little confusing for me to calculate a total number of friends a user has because seems like one is spread across both the columns, and I can understand that the rest of the, you know, users are also spread across like that. So for my better understanding, what I want is if I can have all the ones on the left side and their friends on the right side, and all the twos on the left side and their friends at the right side; it is easier for me to calculate the total number of friends a user has. And for that, the thing that comes to my head is `UNION`. `UNION` is basically a method used for you to combine the results of two `SELECT` statements and removes any duplicates present in it. So that's how `UNION` works. So that is going to be my approach for calculating the total number of friends a user has. Now, the second step is calculating the total number of users. So if I'm having all the users on the left side and their friends on the right side, I can just simply say `DISTINCT` of `user_1`; right, that should probably give me the all existing users on the platform.

Now, the third step of our approach is constructing the query step by step. And the first part of our query construction is calculating the total number of friends a user has. Let me just go back to my query editor to write that query. I already have `SELECT * FROM Facebook_friends`. I'm going to say `UNION`, `SELECT user_2 AS user_1, user_1 AS user_2 FROM Facebook_friends`. I'm going to order this by `user_1` because they wanted the final result to be ordered by the user. I'm just going to run this query. As you can see, we are now having it in the format of all the one on the left side and its friends on the right side; all twos on the left side and its friends on the right. This is how that `UNION` is working. But what exactly is happening here? So when you see the first part of a query was `SELECT * FROM Facebook_friends`, right, and this is the result. Now the second part is we are saying use `user_2` as `user_1` and `user_1` as `user_2`. So now this column will become `user_1` and this column will become as `user_2`. So the `UNION` basically combines these two results and removes if there are any duplicates present there. So let's just see if there are any duplicates or not. As I can see, is there any barrage on Akash? No. And then we are saying Biraj and Himani. So there's already a Biraj and Himani here, so we can remove this. And I'm also looking at Akash and Himani; is there any Akash and Himani somewhere here? No, it's going to stay. And there's also something called as Himani and Biraj; there's Himani and Biraj already here. I'm just simply going to exclude this. So it is now in the format right on how we wanted it, which is all the users on the left side and the respective friends on the right side. Now this explains the first part of our approach which is calculating the total number of friends a user has. Now all I have to do is just group it by the `user_1` and do the count of the `user_2` to get how many friends each user has. So the second step is of our query construction is calculating a total number of users on the platform. So for that what I can do is I can just simply calculate the unique entry of each `user_1` in this table, right? All I have to now do is use this as the input for `SELECT DISTINCT user_1 FROM this subquery`. I'm going to give this table alias. I run this. As you can see now, these are all the distinct users. I just have to say, and oh, you can see there are a total of nine distinct users on the platform. I just now have to divide the count of each user's number of friends by this into 100 to get the popularity percentage. But as you notice here, the total number of users on a platform is using this as the input for it to calculate the total number of uses. Now for me to divide this by this, I have to store this result in something so that I can use this as the input for this to calculate the popularity percentage. So for that I'm going to use a Common Table Expression, `WITH` is a keyword which indicates that you're writing a Common Table Expression, and I'm going to give this Common Table Expression a name `WITH friends AS (this)`. Let me just… I'm going to remove this. This is my Common Table Expression, and I'm going to use this as the input for my next query which is calculating the popularity percentage. I'm going to say `SELECT user_1`, which is basically the one for each user, the popularity percentage. So I'm going to `SELECT user_1`, and I'm going to say `COUNT(user_2)`, because `COUNT(user_2)` is the one which will tell you the total number of friends a user has, and I'm going to divide this by `SELECT COUNT(DISTINCT user_1)`, because that is the section which will give us the total number of users on the platform, and this entire data is um the `friends` common table expression. Group it by `user_1` because we want it for each. As you can see, we now have the popularity. Now I just simply have to write this `* 100`. This is the popularity percentage of each user. So yeah, that is basically how you solve this popularity percentage question. Thank you so much for watching. I will see you guys next time.

In this video, we'll be covering an SQL interview question asked at Uber. So let's go ahead and start solving. The problem that we'll be solving today is distance per dollar. As you look at the screen, you're given a data set of Uber rides with the traveling distance (`distance_to_travel` column) and cost (`monetary_cost` column) for each ride. For each date, find the difference between the distance per dollar for that date and the average distance per dollar for that year-month. Distance per dollar is defined as the distance traveled divided by the cost of the ride. The output should include the year-month and the absolute average difference in the distance per dollar; absolute value to be rounded to the second decimal. You should also count both sexes and failed request status as the distance and the cost values are populated for all the requests. Also assume that all the dates are unique in the data set. Order your result by the earliest request date first. So this is our question.

According to our approach, the first step is understanding the inputs and the outputs. So for that I'm just going back to the question again, and they're saying for each date find the difference between the distance per dollar for that date and the average distance per dollar for that year-month. So I see that they're asking us to calculate something called as the distance per dollar per each date. So I see that we have to use a date-time column, which I know by looking at the table it has something called as the `request_date`. So my input is going to be `request_date`, and for calculating the distance per dollar I need to divide the distance traveled by the cost of the ride. So I'm going to use the columns `distance_to_travel` and `monetary_cost`. So my inputs are going to be these three: `request_date`, `distance_to_travel`, and `monetary_cost`. Now my outputs are going to be as they explicitly mentioned in the question: the output should include year-month and the absolute average difference in the distance per dollar, and the absolute value to be rounded off to two decimals.

Now the second step of our approach is going to be articulating a solution verbally to the interviewer, which is basically explaining our approach and getting a confirmation of our understanding. From the question we understand that they want us to include the year and month, which means we are basically expected to extract the year and month from the date-time column, which is `request_date`. So for that I'm going to use the `DATE_FORMAT` in this way so that it will extract the year and month for me. And the second thing they want from us is the absolute average difference in the distance per dollar. So for actually calculating the difference, first you need to calculate the average, right? So I'm going to use the `AVG` window function so that it is calculated for each and every row. Then I'm going to use the `ABS` function so that I'll get the absolute value of the difference that I'm getting. Then I'm going to use the `ROUND` with 2 as the input because they've mentioned that they want the final output to be rounded off to two decimals. Then finally I'm going to group by the year and the month because they specifically said that they want the output for each year and month. And also they finally mentioned that order your results by the earliest request date first. So I'm going to order this by this `DATE_FORMAT` that we are creating for year and month. So this is basically going to be my approach.

Now the third step of our approach is constructing the query step by step. And the first part of our query construction is extracting the year and month out of the date-time column. So I'm going to say `SELECT DATE_FORMAT(request_date, '%Y-%m') AS year_month FROM uber_request_logs`. And since they want this for each year-month, I'm going to `GROUP BY year_month` and I'm going to `ORDER BY year_month`. So this is our code. I'm going to execute now, and as you can see on the screen, we can now see the output for each and every year and month. Now the next step of our query construction is calculating that difference between the average distance per dollar, which is absolute value rounded off to two decimals. So for that, as we've established in our explanation that we'll be using the `AVG` window function, so it has partitioned for that each year and month. So I'm going to say `AVG(distance_to_travel / monetary_cost)`. So they have mentioned in a question that uh the distance for calculating the distance per dollar, it is `distance_to_travel` divided by the cost. So I've given that. Now I'm going to minus this by `AVG(distance_to_travel / monetary_cost) OVER (PARTITION BY DATE_FORMAT(request_date, '%Y-%m'))`. Since they want this to be partitioned by the year and month, which is our `DATE_FORMAT(request_date, '%Y-%m')`, so the reason why we are using this `year_month` column directly instead of giving it a column name which is `year_month`; window function will not be able to identify this prior, so we need to specify the entire thing. So this is done. Now I'm going to give this entire column name of function which is probably called as the `average_difference`. This entire thing is again coming from the `uber_request_logs`. `ORDER BY year_month`. `GROUP BY year_month`. So I'm simply going to run this query now. As you can see, it, you now have the year-month and the average difference. But in the question that specified that they want it explicitly as the absolute value, right? So you now have to use the `ABS` function, and that's what I'm going to do now. So I'm going to say `ABS(this entire thing)`. And I'm going to run it. `differences`. As you can see, this has been rounded off to absolute value, which is basically it's returning your positive value. Now they also want this to be rounded off to two decimals. So I'm now also going to use `ROUND` method. So I'm going to say `ROUND`, and then I'm going to come back here and say `2`, and then I'm going to run the code. As you can see now, this is rounded off to two decimals. So that's basically what they're looking for: the output should contain the year-month and the average difference between the distance per dollar, the absolute value rounded off to two decimals. So yeah, that's how you solve these kind of SQL interview questions. Thank you so much for watching. I'll see you guys next time. Bye.

In this video, we'll be covering an SQL interview question asked at Amazon. So let's go ahead and start solving. The problem that we'll be solving today is monthly percentage difference. Given a table of purchases by date, calculate the month-over-month percentage change in revenue. The output should include the year-month date in this particular format, and percentage change rounded to the second decimal point and sorted from the beginning of the year to the end of the year. The percentage change column will be populated from the second month forward and can be calculated as: this month's revenue minus last month's revenue divided by last month's revenue * 100. So they're also giving an idea on how to calculate this percentage change column. According to our approach, the first step is to understand the inputs and the outputs, and we can do that by reading the question clearly once again. Since they're saying given a table of purchases by date, calculate month-over-month percentage change in revenue, there is some date-time column involved here. So for me to…

Confirm whether we have a date-time column or not. I need to have a closer look at all the columns in the table. So if I scroll down, I can see all the available columns in that particular table, which is named as the SF transaction. So I see ID, created at, Value, purchase ID. So my input is going to be the date-time column, which is "created at," and since they are wanting the percentage change in the revenue, I'm guessing the Value is the one which contains the revenue number. So my inputs are going to be "created at" and Value. They say the output should include the month, year, date, and percentage change. So my outputs are simply going to be that month-year column and also the percentage change column that will be calculating using the formula that they have mentioned in the question.

The second step of our approach is articulating a solution verbally to the interviewer, which is basically explaining our approach and getting a confirmation of our understanding. Specified in our question, the output should contain the daytime column in a particular format, which is basically in the year and month. I'm going to use the DATE_FORMAT function in MySQL, which will help me extract the year and also the month for that particular output column. They are also asking us to calculate the percentage change in the revenue. Before we calculate the percentage change in the revenue, we first need to calculate the revenue, right? And for that, I'm going to use the SUM for the entire Value column and group it by the month and year column that we are extracting, so that we can get the month-wise revenue. This will generate the revenue for us. Once the revenue is generated, I am going to now calculate the percentage change in the revenue using the formula described in the question. So in that question, they are using the terms such as previous month and current month, and one function that comes to my head for calculating the percentage change is the LAG, which is a window function, and I'm going to use the LAG for that. Now they've also explicitly mentioned that they want us to round off the entire percentage change to two decimal points, and for that, I'm going to use the ROUND function with the input as 2, so that the decimal points are rounded off at 2. And finally, they want us to order this as per the starting year, so I'm going to order it by the month-year column that we are generating. So this is basically going to be my approach.

Now the third step of our approach is constructing the query step by step. The first step of our query was extracting the month and the year from the date-time column using the DATE_FORMAT function. So SELECT DATE_FORMAT from the created_at column; it has to be in the format %Y %m, and I'm going to give this column a name as year_month. This data is coming from SF_transactions, so I'm going to now run the query. As you can see on the screen, we have extracted the year and month column using the DATE_FORMAT. Now the second step is calculating the total revenue. So for that, I'm just going to check SUM of Value and give this a revenue name, and I'm going to group it by this column year_month so that I get the monthly revenue. So I'm now going to run the query again. As you can see in the output, we now have the monthly revenue. Now our next step in constructing our query is calculating the percentage change in the revenue using the LAG window function, and we are going to use the formula that has been described in the question. So as described in the question, I am going to first use the formula: this month's revenue minus last month's revenue. Which is for this month's revenue, I'm simply just going to say SUM of Value, and for last month's revenue is where I'm going to use LAG of SUM of Value. Okay, and since this is a window function, to indicate to the SQL that this is a window function, I'm going to use the OVER clause, and I'm going to put this entire thing in brackets and divide this entire thing by last month's revenue again, which is where I'm going to use the LAG(SUM of Value), and I'm going to indicate that this is a window function and I'm going to multiply this with 100. I'm going to give this column a name percentage_revenue. I'm going to run this. As you can see the output, now we have the year_month, revenue, and percentage_revenue. Now this next step is rounding up this entire thing to two decimals. So I'm now going to use the ROUND function and then commit by 2, and then going to run this code again, and you should be able to see that this is now rounded off into two decimals. Now the last step that we need to do is basically ordering this by the year_month. I'm now just simply going to say ORDER BY year_month, and then I'm going to run, and you can see that again this has been ordered. So this is our output. Since our question says that the output should only include the year, date, month, and the percentage change, I no longer need to show this revenue column, so I'm just simply going to remove this, or you just simply have the year and the percentage_revenue. So yeah, that's how you approach this particular SQL problem. Thank you so much for watching. I'll see you guys next time.

Hello everyone, welcome back to the series solving SQL interview questions. In our last video, we've solved an SQL interview question asked at Google using our three-step approach. In this video, we'll be solving an SQL interview question asked at Netflix. So let's go ahead and start solving. As on the screen, the problem that we'll be solving today is top percentile fraud on the same platform, status crash, that we'll be using throughout this series. Let's take a look at the question now. ABC Corp is a mid-sized insurer in the U.S., and in the recent past, the fraudulent claims have increased significantly for their personal auto insurance portfolio. They have developed an ML-based predictive model to identify the propensity of fraudulent claims. Now they assign highly experienced claim adjusters for the top five percentile of claims identified by the model. Your objective is to identify the top five percentile of claims for each state. Your output should be policy number, state, claim cost, and fraud score. But before we get into that, I'm seeing terms such as person type. So what exactly does this percentile mean? Percentile is simply a value under which a percentage of data falls into. So if you look at the screen, we have data points such as 40, 50, 60, so on, total 10 data points, and we are being asked to calculate the 30th percentile. So the first step of calculating a percentile is arranging the data points in ascending order, that is from lowest to the highest. The next step of calculating that particular value under which 30 percent of the data falls is you have to count the numbers from left to right. 482 is the number under which 30 percent of the data falls into. Now coming back to our question where they asked us to identify the top five percentile, what they meant is basically finding all the values that fall under the top five percent of the fraudulent claims.

The first step of our solution is identifying the inputs and the outputs. So let me take a look at all the columns that are present in the table to see which will give me the fraud claim. So I can see that there's a column called as fraud_score. So I'm going to use this as the input for calculating the fraud claim, and since they're saying that they want it for each state, I'm going to use the column called as state as mentioned here. So my inputs are going to be fraud_score and state. What are my outputs going to be here? They explicitly mentioned that it should contain policy number, state, claim cost, and fraud score.

Now the second step of our approach is articulating a solution verbally, which is basically explaining your approach to your interviewer and getting a confirmation. So in our questions, since they are asking us to identify the top five percentile of claims from each state, the thing that comes to my head for calculating the percentile is a window function NTILE, which will help you calculate percentiles, quartiles, etc. So my input for the entire function is going to be 100, so that it divides the entire data into 100 equal parts, which is what percentile is, and then I'm going to use a filtering technique for filtering out the top five percentage. Now when they said from each state, I am using a window function here, right? And in a window function, you have a clause called as PARTITION BY, which will help you create windows as per the column that you mentioned in it, and since the question says from each state, I'm going to say PARTITION BY each state in my window function, so that for each state I'm going to get the top five percentile.

Now the third step of our approach is constructing the query step by step. I'm going to say SELECT * since they want almost all the columns in the output, and NTILE is the function that I'm using to calculate the percentile with the input as 100, so that the data is divided into 100 equal parts. I'm going to use the OVER clause used to indicate that you're using a window function, and I want each of this windows to be partitioned by state since the question states they wanted from each state. Then I'm going to order this by fraud_score, the column which contains the fraudulent claims, and since they want the top five percent, I'm going to order this by descending order, and I'm going to give this entire entire function column a name which is percentile since that's what we are calculating, and this entire data is coming from the. So I'm running it. Okay, great. I now can see that it is ordered by the fraud_score and divided into 100 equal parts. Now the next step is for me to filter out the top five percentile from this entire query. So this table has to be an input for another table so that I can filter it out using the WHERE clause. So that's what I'm going to do now. This table is going to be one, and I'm going to SELECT * from this table. I'm going to give this table a name called as percentile_table and filter out WHERE percentile <= 5 because we are looking for the top five percentile. So I'm going to run this query now, and there you go. You now have, as stated, it should contain policy numbers, state, claim cost, fraud score, percentile, and then you're containing top five percentile for the each state. Since they said the output should only contain these and you don't want it to show the percentile, you can explicitly mention here what are the column names that you're looking for. So I can simply say policy_num, state, claim_cost, fraud_score from this table, and then I'm going to run it. So there you go, policy number, state, so these are the top five percentiles of fraud cases from each state here. That's how you go ahead and solve this particular kind of question. We will see you guys next time. Bye.

Let's take a look at the question now: Find IDs of companies that have more than two users who speak English, German, French, or Spanish. So according to our approach, our first step is to understand the inputs and the outputs clearly. Now for me to understand the inputs clearly, I need to know what are the columns present in the table playbook_users. If you scroll down, you will see the inputs that are given to you, which is user_ID, created_at, company_ID, language, activated_at, and state. Now among these, what should be my inputs and how will I know that by reading the question clearly, which is find IDs of the companies. So I need to use the company_ID that have more than two users. So when they are talking about users, which means I'll also have to use user_ID who speak English, German, French, and Spanish. So I don't see any columns with respect to this, but I can see that there is a column known as language. So I now need to filter out these languages from the column language. So what are my inputs? Company_ID, user_ID, and the language. Now what are my outputs? Since the question only simply states find the IDs of the companies, my output will only simply be company_ID. So my inputs are company_ID, user_ID, language, and my output is only simply company_ID.

Now step two of our approach is explaining your approach verbally. Now that you've understood what are the inputs and the outputs, you now have to explain to your interviewer what is the approach. If you look at the question, it says find the IDs of the company, so they want you to find for each company, which means you have to use a GROUP BY clause known as company_ID, and then since they're talking about that have more than two users, they want to perform some aggregation function, in this case, which is COUNT. So you're going to have to perform an aggregation function COUNT for finding out more than two users, and since they want it for each company, you'll have to do something called as GROUP BY for each company who speak XYZ languages. So you now need to filter out these languages from the column language. So one operator that comes to my mind as soon as I'm checking for a string value is IN. So I'm going to check whether this particular language is present in the language column or not for that particular user. Now that I've explained my approach to my interviewer verbally, the third step is basically constructing my query step by step, and that's what I'm going to do now. The first step of constructing my query is filtering out the users who are speaking the languages XYZ. SELECT, since my output should only contain company_ID, I'm only going to give company_ID from the table playbook_users WHERE language IN I'm going to give English, German, French, and Spanish. So I'm just going to run this code. Okay, now it's showing me all the company IDs where these languages are presenting, but since I want for each company_ID, I'm going to do GROUP BY company_ID. Great. Now the clauses, they want the users who are more than two in number. This is where I'm going to use my HAVING clause HAVING COUNT or user_ID > 2, and now a company might contain like multiple entries. I want distinct user_ID, unique user_ID. I'm going to say since this side it has to have more than two, I'm saying > 2, and then executing my query, and it says there is some error which says HAVING COUNT. Oh, seems like I have a typo here. So I'm going to say DISTINCT and then I'm going to run my code, and there you go. So these are the companies where there are more than two users who speak the languages English, German, French, and Spanish. So this is how you approach in solving an SQL query. So keep three things in mind, which is basically understanding your inputs and outputs, explaining your approach verbally, the third thing is constructing your query step by step. That's it for this video, guys. I will see you next time. Bye. Will you come back?