SQL Masterclass: Data Analysis in One Month

Hello, my name is Vinay Doneja. I'm going to talk about relational databases. What is a relational database? Relational database is where we store structured information, structured data. RDBMS stands for Relational Database Management System. Why do we do use databases? We use databases to store structured information. And how to retrieve and work with that data? We use structured query language to work with those databases. Some of the common day databases are SQL Server, IBM DB2, Oracle, Microsoft Access, MySQL. The data in the database management system is stored in tables. What is a table? Table is a collection of rows and columns. We're going to take a look at customers table. We are going to assume we already have a database ready and we have a table in the database named customers. Now, FOSS tells you we want to get all the records from the customer table. Simply, you go in SQL view of the database and you type in this query which is SELECT char FROM customers. Customers is the table name. Where did I store? You are char means you're selecting all the records from that table. We're going to run that query. There you go. We have all the records from customer table. 91 records and 91 records are displayed here. Database table. This is the structure of the database table. Example of a customer table that is currently stored in our system. It has a customer ID, customer name, contact name, address, city, postal code, country. These are all the columns that are available and we have multiple record sets available. SELECT is the statement that we use to retrieve information from the customer table. Now, if you don't want it to retrieve all the records, we just want to retrieve two columns, we will type in the name of those columns, customer name and city. Enter in the query. It will still give us all the records, only show us customer name and city. We want to go back and get all the records. We will replace this with a star and it'll give us all the columns. Thank you. All right. Welcome back. This is the next. What are the learning objectives? We learned about relational database management systems. We learned about the introduction to structural query language. We learned about how to query the databases using SELECT statements. Today, we are going to understand how to get information retrieve information using the WHERE clause. It can also be referred to as a filter. Let's say, give an example, you don't want to see all the records in the customer's table. You just want to see records from a certain country. You just add the name of that country and you will have that information printed out for you. Here, we have an example. We are going to try that. It's a SELECT star from customer's table where country is equal to Mexico. It has to be in single quotes, acting as a string. It'll give us all the records that are all the customers where the country is Mexico. Try it. We're going to run the statement with customer's table. We'll see we get five records and you will see the country here is Mexico. What does WHERE clause means? It's a way of filtering records. Why use the filter? Well, you have 91 records and you don't want to see all the 91 records in the customer's database. You only want to see records from the country Mexico. That's where the answer is. You filter it down by Mexico. Let's say you wanted it filtered by Spain. You could add the Spain clause and it will filter those records for you. Hello. Welcome back. Today, we're going to talk about structural query language, ORDER BY clause. What is ORDER BY clause? ORDER BY clause is when you want to sort something. You want to sort it ascending. You want to sort it descending. You want to sort it by a particular column. We're going to take a look at that. What you're doing is you're selecting all the products on the products table and you are sorting it by price in this example. The syntax is SELECT start FROM products ORDER BY price. We're going to take a look at the example. We're going to run the example. You'll see the result sets here are sorted in ascending order by the price column. If you look at the price column, it is 2.5, 4.5, 6, 7, and it's increasing. If you wanted to sort it by descending, you add the descending clause and you'll see the price is sorted by descending order. If you would like to add some other column to it, let's say CATEGORY ID in ascending order and we'll run it. You'll see it's sorting the products table by CATEGORY ID in the ascending order. 1, 2, 3 and onwards. Thank you. Hi, welcome back. This is Vinay. Today we are going to talk about the group by clause. What is group by clause? Say you wanted to get a count of certain customers in a certain country. Give an example. Let's say you wanted to see all the customers or count of customers that are in Argentina. Given this example and using the group by clause, you will be able to calculate that. We'll take a look at the example and run that example. We're going to add a column name to it as count by country. This is going to give us the count by the country and it's going to group it by each country that we have in the system. We're going to run it. It gives us 21 records. It shows us that Argentina has 3 customers, Austria has 2 customers and going onwards. Let's say we wanted to filter it by Argentina. We can put a where clause like we learned in the previous chapter. Let's say where country. It will only give us records for Argentina. Hi, welcome back. Today we're going to talk about a trickery language, having clause. Why do we use having clause? We use having clause when we're doing aggregate functions. When we use aggregate functions like count, min, max, average, we cannot use that in where clause. We utilize having clause to make that happen. Let me take a look at that in a second here. Here is our demo database. This is how we are going to use the having clause. It's a select count of customer ID from customers table. We group them by country and we say having count customer ID greater than 5, which means we want to see all the customers. If we remove the statement having for now, this is what we studied in the last class. I'm going to run it. We get 21 records here and we'll see we get 3 records for Argentina. We only want to see the records that are greater than 5. We are going to add that statement and say where count customer ID is greater than 5. We'll see all the records where the customers in that particular country are greater than 5. We'll run that SQL and we'll see Brazil, France, Germany, UK, USA. We're just going to, for readability sake, we're going to name the column count customer ID as count by country. We'll run it again. Obviously, the label of the column name changes as per we described it. We are getting all the records by the country because we see a count of customers by country. They are grouped by the country and we're also looking at where the count is greater than 5. Hello. Welcome back. This is Vinay. We're going to talk about databases. This is our given database and we have some tables here. What is a database? Database is a collection of tables. Orders table, order detail table. We have 196 orders in the order table and we have some details about the order. We have 500 rows. What is a table? Table is rows and columns. Order ID is a column. All these are rows. Rows and columns. How do we get information from one table? Select. Start from orders table. It's going to give us all the information from the orders table. If you want to select information or get information, retrieve information from the details, order details, what the customer ordered. All we have over here is the order ID and customer ID and the employee ID who took the order and the customer who gave the order. Now, we want to collect details, what kind of products they ordered. We can say, we can use joint statement to get information from there. I'm going to say, let these particular columns from orders table in a joint order detail. What do we want to join on? Order ID is common between both of them. We want to join both these tables on order ID. This will provide us all the information. We're going to just add order ID, order date, order details ID, product ID, quantity from order and order details table. We are getting two columns from orders table. We say orders.orderID, orders.orderDate to get those particular columns. We want to get the different columns from order details page. We say order detail ID, product ID, and quantity. This will give us information from order details, joining the order. Let's see, we got the order 10248, the ID. We have 518 records. The order was made on 7-4. This is the detail, unique number from the order details, what kind of product they ordered and what kind of quantity they ordered. This is repeated three times because same customer, it's the same order. 10248 is the order ID and they ordered three different products. These are the product IDs, 42, 72, 11. If we wanted to get more information about what kind of product they ordered, we could do another join on products table and collect that information. At the moment, we're just going to take a look at how to retrieve information from two different tables. We use inner join to do that. Hello, welcome back. This is Vinay here. We covered a little bit about what we call joins. We talked a little bit more in detail about those joins. There are different kinds of joins. When you have to retrieve information from two or more tables, you use joins. Different kinds of joins in SQL are simple join, which is also inner join. There is left join, right join, whole join. Inner join is also seen as join, which is the first diagram here. It's in the section of two tables where both those records are equal, where you want to join them on. That's going to give you join. Left join, you want to get all the records from the left table of the join as well as the common records that are in the right table. I'll give an example of a join, order table and order detail. You have order table on the left. You have order detail on the right. You want to get all the orders that have a record in the order detail table as well. You can use join. You want to get all the records that are in the order table that have a common record in order detail as well. You want all the records from order table, even if they don't have a record in order detail, that you can get it from using left join. Right join, you want to get all the records from order detail table regardless if they have information available in order table. That's right join. You want to get all the records from order table as well as all the records from order detail table. You will use full join. Hi, welcome back. Today, we are going to see the examples for all the different kinds of joins and see the results of differences in different kinds of joins. Start with inner join. We'll run a query for inner join. You'll see we'll get 196 records. We're getting all the records from orders table joining the customers table where there is a record in both customers table as well as the bridge and they match on customer. That's inner join. We will use a left join. We want all the records from the order table regardless of if they have something in the customer table related to them. It still gives us 196 records. It means that all of these orders have a customer related to them. That's all it means. Otherwise, we would see an out customer name. Go down, you see all the customers. They do have a name. Now, we will do a right join. That means we want to see all the customers regardless of if they have placed the order. If you are a customer, but you did not place an order yet, you're a new customer, you will not see the order number in the order table. Hold on this query. You'll see number of records are 213. You'll see customer on the top did not place an order. Another customer that did not have an order. Likewise, there's another customer who did not place any orders yet. It's getting all the information from the customers table regardless if they have an order. Now, we covered the inner join, left join, right join. We are going to cover outer join. It's going to give us all the records from order table and all the records from customer. We're going to do a full join. Let's look again. Join. What are operators? It's minus, multiply, divide, mod. There's other operators as well. It's AND operator, OR operator, NOR operator, and the comparison operators which are used to compare information, equal to, greater than, less than, greater than or equal to, less than or equal to, not equal to. There's compound operators. Add equals, subtract equals, multiply equals, divide equals, mod equals, bitwise and equal, bitwise or equal, not equal, bitwise or equal. There's logical operators. These are commonly used. You want to use ALL operator, AND operator. Any operator between operator exists in like, not, or, some. I'm going to go back and try some of these. Let's say you want to add. You'll get 30 plus 2. Get that. Subtract. You'll get 10. You could similarly get some abstract data as well from the field. Here you are doing a mathematical operation. You could also use these operators to add numbers or columns. Given an example, let's say you have a star on products table. Get all the information from products table. Let's say you wanted to add all the products in order. You wanted to add this column plus some number 1 to it. You wanted to add something to, some other column to it. You wanted to add some price as well as some price. We'll double it. You could do that. You could add two columns. Get this. Similarly, we have comparison operators. Let's take a look at that. You're saying you want to get all the products where price is equal to 18. You want that. It'll give you where the price is greater than all the rows where it matches. If you wanted to say greater than 18, it'll give you all the records that are greater than 18. There's other comparison operators as well. There's compound operators. There's some logical operators. We should definitely try those. We wanted to get all the records. Select product ID from order details where quantity is 10. There's a subquery. You want to get all the records that are part of the query number 2, second query. They also belong to the first query. It did not match. Let's say you want to remove the real cost out of it. Come back to that. We'll use n-clog. You could use the n-clog here. Let's start from customers where city is equal to London and country is equal to UK. It'll give you city is London as well as country is UK. If you wanted to change that to an or, it'll give you whatever city is London or country is UK. It did give you one record that where country is UK as well as the city is London. In this case, city is not London but it does match the country as UK. It'll give you the result there. You want to change it, match it and get the records. If you want to try n-clog, if you have where city is equal to Paris, it gives you all the customers that are in Paris. You have multiple criteria. You can use them in statement. Paris, you want to use from London. Paris as well as London, you could use n-clog. It records all the customers that are in Paris or London. City is Paris or London. If you wanted to add others, you could just add others. Take a look at what other collective you have. Berlin, you include in as well. Berlin, London, Paris, etc. This will include mostly the other operators and between operators. Thailand, you want to see records with prices between 50 and 60. From that, it'll give you 60 and 70 and 60 to 70. Thailand, I exist. We have this query. Let's see and run this query. It gives us all the records, the product name. Run this query separately. We can understand it. We're going to get all the product names on product table. It's a sub-query. All we're doing is we are collecting all the product names from products table where there is a match in the supplier table. It has to exist and the price is less than 20, which is a sub-query. First, this query runs. Then, when you find a record in the supplier table, it will display this. These are all the suppliers that are also in the product table. If you wanted to change this query, you could do join as well. If you want to rewrite this query, go to records and make sure you get the same number of records. You could join it. Let's go join product. This is this and this. We are saying join suppliers. We come back to that. See how we can do the join instead of this. Similarly, we have rediscussed in as a tool to run the SQL queries that we learned. Oracle provides a tool named Apex that you can sign up. It's just a workspace. It allows you to run your SQL queries once you have the workplace set up. You can sign up for an account at apex.oracle.com or you can copy and paste the link below in your browser. It'll take you to sign up for sign up page. If you already have an account, just log in below. I have provided a link to sign up or log in. If you already have an account with Apex Oracle, you can sign in here to run the queries, SQL queries in Apex Oracle. Once you have the Apex Oracle workspace ready, you'll be able to utilize that to run your SQL queries. Just go to SQL workshop. Once you're signed in, go to SQL commands, type, select, paste one from this dual semicolon. Dual is something like a temp table and select one like you learned in the previous queries. You just ran your first query in Apex Oracle. Thank you. Hi, welcome back. This is Vinay. We are going to talk about Microsoft Access as a tool to run those SQL queries that we just learned. We're going to first talk about to download Microsoft Access. You can buy the Microsoft Access from this link that is provided in the resource file or you can copy paste the second link that I have also provided. You can copy paste it in your browser and it'll take you to the site where you can buy the Microsoft Access. You can also buy Office 365 or sign up for a subscription, which will come along with Microsoft Access as a plan. That'll include Microsoft Access. Once you sign up, it might have a cost associated with it. The Microsoft Access, it's a small database of used for smaller applications. Once you get Microsoft Access and download it on your machine or get the subscription, this is how it will look like. Access app. Once you have Microsoft Access on the PC, you will type Access from your Windows search and then open Access. Click New to create a new database. You can name it as SQL tools and then click the Create button. It'll create the database. It looks something like this. This is your home page to the database. You will click the Create button and then click the Query Design in Access tab. Then on the left side, you will see SQL view. Click that SQL view. It'll bring you to this window. Hello. You can write your first SQL statement now in that query window. Just type in select one semicolon in the query window and click Run on the left hand corner. It'll give you the results set. Hi, welcome back. This is Vinay here. We're going to talk about Microsoft SQL Server Management Studio. Run your SQL queries that we had learned in the past few lessons. You can download Microsoft SQL Management Studio on the link that I have provided in the resource file. You can download the Microsoft SQL Server Express Edition using the above link or you can just copy paste this second link into your browser. Please do check for hardware requirements that your system meets the minimum hardware requirements to run this SQL Server Express Edition. It's a lightweight version. It's also used for simple lightweight applications, smaller applications, how to install Microsoft SQL Express Edition. Once you have it downloaded, just select the basic installation. It'll take you to accept for license terms. Read those and accept the terms. It'll tell you where it's installing the program. It should be defaulted to program files in Microsoft SQL Server. Just click install. There you have it. It should be installed on your machine. From the start of your Windows machine, search for Microsoft SQL Server Management Studio. It should appear. Just click this and select SQL Server Management Studio. What you're going to do next is connect to the SQL Server. You're connecting to the server now. You will select the server name. Server name should be your system's name with the SQL Server next to it or it could be the default instance name that you picked while installing the SQL Express or the SQL Server Management Studio. It could also be localhost followed by SQL Express. Select Windows authentication or easier signup. It'll pick the default username and leave it as is. Just go ahead and click it. Click on the new query window, query button, connect. Once the new query window appears, it looks something like this. On your right-hand side, there is an example. Type select one semicolon. Click the run button or execute button or F5 on the key. There you have it. The result set will appear below. You just ran your first query in Microsoft SQL Server Management Studio. Thanks.