PostgreSQL - Simple Queries

1. introduction: My name is Will Bunker. I began my career in the nineties, building what has now become known as match dot com with a good friend of mine, Dave Kennedy. A big part of that was using databases. So here, in this course, we're going to start by helping you set up a sample database called North Wind using post GREss. Now Post Dress is one of the most popular open source databases on the Internet is taking the place of my sequel after my sequel got bought by Oracle. So then we'll start by teaching you to learn how to talk to the database and ask questions . This is called querying and database talk. You'll learn how to select from simple tables information and then restrict what kind of information you get back using where clauses from there will go on to learning how to aggregate data by finding things like average Max men. And then we'll also learn logical operators like and or not. I hope you enjoy this course, and it becomes the first part of your journey to becoming a database master 2. Why Learn SQL? : This is the course introduction to sequel mastery from zero to superstar. So why Learned sequel Well, databases store all the world's information and sequel is the standard way to talk to these databases. Almost any idea you can think of involved storing and retrieving data. And so every company on the planet has one or more databases, which are vital. Their operation and knowing how toe query them and talk to them gives you an advantage over people that don't. So who am I? My name is Will Bunker. Dave, Kenny and I founded the largest dating site of the nineties. It eventually became match dot com. I had to teach myself from the ground up how to do everything involved in getting this business off the ground, a major portion of which was learning how to manage databases. We actually had one of the largest databases of the nineties, with millions of profiles and a huge number of searches going at any time, and we were constantly working with the day to try and improve the data flow. Now what? Our databases at the core level, it's a piece of software that allows you to store information and retrieve that information now their two main types of their sequel databases, which is what this courses on. And then there's no sequel and no sequel is an unstructured form of data that's useful if you're dealing with stuff that doesn't have the same structure every time you look at it. My brother sister had a company, and they used no sequel for a lot of their unstructured information about the security settings because they varied so much across different pieces of software. Let's talk about the sequel database universe, and you can roughly break it down into commercial and non commercial and so commercial sequel databases Microsoft and Oracle or the dominant players here. Oracle is one of the original companies that popular allies mainstream databases back in the eighties and nineties. They're still going strong, and they got a lot of certifications and software in this place. The other big player is Microsoft Sequel server, and this is actually the software that we use when we were building our dating site and it was driven by the fact that it was a lot cheaper, I think $5000 versus $50,000 for Oracle plus they gave a free copy to you for 90 days, and so we felt like, you know, we would either have figured it out 90 days or be out of business. So we went with Microsoft Sequel Server. Now there's been a big change in that open source. Sequel databases have become very popular over the last 15 years. It started with my sequel. Then there's Maria DB sequel Light Post Greste sequel. Now sequel Light is a great open source database that gets used a lot when you're building your M V P or sometimes out launch. But it's simple, reliable. It uses sequel. You can stall it on your laptop and run it. Another popular one is my sequel now. This came out back in the two thousands. Now it's since been bought by Oracle, and so that caused a little bit of consternation, and people were worried that Oracle wouldn't continue to develop it so they actually forked it. And now there's Maria DB, which is an open source project around a Fort copy of my sequel, and it's now going on its own direction and becoming very popular. And then that leads us to post GREss sequel now This one was an alternative to my sequel. That became a lot more popular after my sequel was bought by or cold. And this is the one that we're gonna be using today. We're gonna do this work around a typical database called North Wind. Now, this was originally released by Microsoft when they were launching access DB, and it represents a company selling products for multiple salat suppliers to customers around the globe. Now, in this all data is grouped in the tables in North Wind has 14 tables, which includes everything from customers to suppliers to products, two categories. And we're gonna get into how to relate all this data and pull information from it that each table consist of multiple fields, one arm or in these fields or the structure that you give the date. And so you create a field for each piece of information that you want every record to have . And then every record comes back as a row in a table, and so you could see here I ran a query and it's pulled back multiple records, each of which has information in each field. And that's the basic structure of data databases and next step, we're gonna get into installation and show you how to get the software up and running on your system. 3. Installing PostgreSQL: OK, in this video, we're gonna talk about how to install Post Crest sequel Open up your favorite browser type in post GREss SQL. And that's gonna pull up host GREss dot org's, which is the site that hosts this open source project and then go over here to download. Now, here, it gives you a choice of your operating system. So just depends on what your own. There's different versions of Lennox BST, Mac OS Window, Solaris. I'm on a Mac, but pick the one that matches your operating system. Now, here they give you options of different interactive installers. We're going to go with Enterprise DB as the one that we're gonna use to install the software. So click download the installer. That's gonna take you to et Bees Enterprise DB Database site Now, here again, you have to select your version. Now we're gonna go with 965 But, you know, pick whichever version of nine sixes current as you're watching this and then again, you pick your operating system. For us, it's Mac OS, which you can do Windows or Lennox here in 64. Better 32 bit and then click download now. Okay, Once you download the installation program, you go to where it was Dalit in your system. I moved it to the desktop to make it easier and straight. But you start the installer. I want to run it here. It's gonna be slightly different in the windows, but similar workflow. Okay, here it comes up. All right, go next. I'm gonna accept the defaults. Where is gonna put the data? Add now, here you need to put in a password. Is very important that you remember this or you'll have toe reinstall the program all over again. Okay, I'm gonna put in the default port 54. 32. No need to change that. And the default locale, In my case, I'm gonna pick English. US utf eight. But feel free to choose your local language setups. Ready to start right now. I unclipped the stack builder. It has a lot of additional software that we're not gonna be addressing in this two tutorial . So no need to go through that and then click finish. And there we are. You're now installed post Greste sequel 4. Install Northwind Database: and this lecture, I'm gonna show you how to install North Wind database. It's included as a resource on this lecture. So you need to download north wind dot tar And then I'm gonna show you how toe take that file and pull it into your database. Now, when you installed post Greste sequel with the installer, one of the things that added was a PG admin tool. And that's the tool that we're gonna use to interact with the database. So if you go to the directory where Post GREss was installed, you're gonna see this PG admin four file and you want to click yet. And this is the program that we're gonna use to run our queries against the database. So once PG admin four comes up, you're going to see a list of servers over here. This should have server that you just installed. If you click here, it'll show you. OK, it's post Greste 9.6. Then click your databases right now it comes installed with post Greste database. So we're gonna right click on this. We're gonna create a new database. We're call that database north wind. So there you going Once you've created the name here you click save and it's gonna create a new north wind database. So now click that right now. If we go down to ski most public in tables, you're going to see that there's nothing here. We haven't installed anything yet. So what you want to do is right. Click right here and then you want to restore and we're gonna restore using the tar file that you downloaded as a resource. So click restore. Now you're gonna have to find the file. So I clicked here. Now it's in my download directories. Now, you initially you don't see anything, but you have to go over here and change it to all files and then select the North Wind tar file. It's select and then hit, Restore. And then when it finishes, it's gonna come back with this Ah, confirmation screen right here. Now you want to go over and you want to, right? Click your database and refresh. And then if you go down here to tables, you're going to see that you've now got the 14 tables that come with north wind, right? That's it for this lecture and the next one we're gonna discuss how to do simple, select statements. So you then 5. Selecting All Data From a Table: okay. And this lecture, we're gonna cover the simplest select statement possible. Now you're beginning to learn sequel here, which is structured query language. And this is the language used to talk to most modern databases. There's 15 words and then learning how to do things like joins and relationships. Now, the syntax that we're gonna use here is that we're gonna capitalize, are keywords, and then everything else will be in the same capitalization as it is in the database. And this makes it easier to read the statements later, especially as they get more complex. And so just remember, it's not mandatory, but it definitely will help you down the road. Be able to read something that you wrote weeks ago, and now you're trying to throw out what you were doing. Here is the syntax for selecting all data from a table. It's select ass trick or star and then from and the table name. And most times now it's optional. But but it's good habit to get into to end the statement with semi colon that tells it where it stops in case you want to do more than one statement in a row. Now let's see, we want to select everything from the customer's table. So now I'm going to switch over here to PG admin and show you how to do this from within the pro. So here we go. You've got your north wind database. You want to right? Click on it and go to your query tool. All right, so now we're gonna type in the statement here That's gonna be select gastric from customers . Now, there's a short cut key here, which is F five. Or you could hit this lightning bolt up here. I'm gonna use the shortcut key. Here we go. Now, this pulls back all fields in the table. So we've got every field if you go all the way across and if you go all the way down, this is all the roads. So in this particular database there 91 customers in the North wind database. Now it's your turn. So I want you to pause the video and select everything from the employees table. So push pause, and we're gonna do this constantly throughout the course where it's your turn to try it out on your own. And then I'll show you the answer. When you come back. So if we wanna select everything from the employees table, it's select star from, and the only thing we have to change here is the name of the table. And then we hit Run and we've got all nine employees from the North Wind database. Well, that's it. That is the simplest statement you can ride in sequel and the next lecture will cover how to narrow down and only bring back certain fields versus all the fields with Astra. 6. Selecting Specific Fields: in this video, we're gonna talk about selecting specific fields most of the time. You don't want to select all fields and all rose in a working database because the amount of data return will be too big. So the basic syntax is you follow after your select statement, a list of the column names and it could be one arm or separated by a comma. And then after that, you have the from table name. So we're gonna go to a specific example where we're going to select the company name, city and country of all our suppliers. I'm gonna switch over to PG admin to show you how to do this. Would I like to do is go to the table itself. So here we've got suppliers, and then you can get a look at the names of the so feels right there. So we want to select. We'll start off with select company name City Country from he's there. Unless run that. And there we have it. We have a list of those three fields coming back, all the records of our suppliers. Now it's your turn, and you're going to select the name and description of all our product categories. So pause the video right now and go to your PG admin terminal and see if you can pull this queer together. And remember, you can look at the field names by accessing the table on the left hand side of the display . Join? Join me again. When you ran that query, let me show you my example here. And it was select category name, comma description from categories and that pulls back to Phil's that we're looking for. Up next, we're gonna talk about using the state in order to get specific values, see on the next video. 7. Selecting Distinct Values: in this lecture, we're gonna cover selecting the stink values. So what if I wanted a list of my customer countries? The normal instinct would be to go select country from customers. Let's take a look at what happens when we do that. Okay, so we're gonna select country from customers. And if I run that you see, I get back a list, but it's not quite right. I mean, there's duplicates. So basically, it's returning a record for each customer, and it includes the same countries over and over again, depending one our customer pattern. So what's the answer to this? The answer is that estate keywords and the syntax years you select distinct. And then it could be one or more columns separated by a comma from a particular table. Again, let's go back to that question of what countries do our customers come from and let's run this and see what happens. All right, so here we're gonna do distinct were to run that there, and we get back 21 unique countries that our customers come from. Now you can use this for more than one feel. So, for instance, let's find out what cities in specific countries that our customers come from. Okay, so we're gonna add city to this query here, we're gonna run it. And when we look down here, we've got 69 cities slash countries that our customers come from. Now it's your turn. So I want you to come up with what is the query to find out the unique regions, our suppliers, urine. And remember, you can look at the field names on the left there. So go look at your table suppliers, and you want the unique regions that are suppliers come from pause the video until you run it. Okay? The answer is you want to select distinct region from suppliers and that comes back with this list of nine regions, including no, and knows the value. When you don't know the field you put in Nolan, it means unknown. Next step, instead of having to scroll down and manually count it, we're gonna talk about how to find out the count directly in the query. That's it. See you in the next video 8. Counting Results Revised: OK, in this video, we're gonna talk about counting results. So what if I want to know that count? You know, scrolling down the results could be a pain, especially in a production database which can have millions of rose. And so you rarely, if you're just wanting account, want to do select are from table and get back millions of rose. It'll a clog up the database and probably cause your site to crash. If you do that, I know I've done it many a time back when I was building my first business. So there's account statement and the basic syntax is select count. And then in parentheses, you put a column name that you want to count from table name. Now, when you have a column name is gonna count every value that's not know. So if you remember when we were looking at regions, there were certain regions that had no. And if he ran account on that field, you would only count the ones that had results. Now, if you want to count all the rows, you use your friend ass trick to say just count rows. So then that's just select count with ass trick in your parentheses from your table. So the first question we're gonna answer here is how many products do we care? So to answer that, we're gonna go select count. We're Blue Star from products. Let's run that without 5 77 products on the number of products that we carry. Now it's your turn. So how many orders have we had? So pause the video and go run a statement with count to see how many orders air in the database. Okay, so we're gonna run a similar one star, and then we're just gonna change out that table. Two orders here and the answer is 830 orders or in the database discount can be combined with distinct. So if you want to know the number of unique values you use count with distinct and that the column name in the parentheses. So let's answer how many cities are suppliers in? Okay, so we're gonna select count. But remember, we have to do distinct here, So it's just think city from suppliers. Let's run that. And we have 29 different cities that are suppliers. Aaron, not your turn. How many distinct products have been ordered now to get there. If you're not familiar with this database yet, it's in order. Details, order details, has the products. Each line is a product that's in order, so you want to count the number of distinct products they have been ordered. So pause the video until you get a chance to run it and then come back and see the answer. Okay, so we're gonna go with Let's look over here with order details and its product ideas. The name of the field. So we're go product I d from border details. And we've also got to do to speak because if we just count products, we've had 2155. Let's run it here. 77 products have been order in total. Next up is a combining fields and select. See, there 9. Combining fields in SELECT: in this lecture, we're gonna cover combining fields in select. And this is where you do calculations with your columns and so you could do things like add two columns together, multiplied by number, divided by a number and so forth. We're going to start with an example here, and it's gonna be How long did it take to ship? And so we're gonna put a list of our customer I ds and the difference between ship day and order date. This gives us how many days it took us to get the order ship. So here we go. We're going to go here, and we're gonna go select customer I D. And then it's going to be the older or the larger day, which will be shipped day can't ship it before someone order minus order day from orders. Here we go. Must run this guy. And this gives us a list of all of our customers and how long it took to ship each particular order to him. Now it's your turn. And this one I want you to calculate the amount spent on each order details and this is gonna be priced times quantity. So pause the video. Try to formulate the query and come back and I'll show you my answer. Okay, so we're gonna go with order detail. Also, we're gonna do order I d. And what's going to be unit price times quantity from order details. And here we go. We've got a list of exactly how much money each detail cost on the order details. Right in the next video, we're gonna cover where with text fields. See them. 10. WHERE with text fields: in this lecture, we're gonna go over using wear and specifically where with text fields. Now, up till now, we've been selecting all the records within a table. What if you don't want all the records, you know we're gonna learn how to find specific records using where clauses, the basic syntax of where is the first part of it? Similar to what you've already done what you select set of columns from a specific table. And then after that, you put in where in a condition, and we're gonna start with text. And when you match text, you have to put quotes around it. And it could be either single quota double quote example here would be were customer name equal around the horn, and that could be in single quotes or double quotes. Now let's see. What if we want to find all of our suppliers from Berlin? How would we go about doing that? Well, let's start here, and we're gonna go select every field from suppliers where city equal in quotes for a lamp . Unless we're in that real quick and that leaves us with this. Now it's your turn. So let's write a query that returns all customer names and contact names from customers that we have in Mexico. So pause the video. You can look up the field using the left hand side by expanding out the table and looking at the column names. But do a query that returns all these records for our customers and the country of Mexico. Okay, so let's right that now. So we're gonna go with company name and let's look here under customers, there's contact name from customers instead of city. It is country, and we want people from Mexico. All right, let's run that query. And we've got five records that have come back from customers that we have down in Mexico. Our next step is we're gonna look at using where simple wear statements with numeric fields . See in the next video 11. WHERE with Numeric Fields: alright. In this lecture, we're gonna cover using where with numeric fields. So the basic syntax is you've got your wear, your field and then you've got your choice of several operators here, there's equal. There's greater than greater than or equal to less than unless than or equal to. So let's start with an example here. Find the number of orders that were placed with employees that has the idea of three. Which is Janet Leverage. Here we go. So we're going to select Count Store from orders where Employee I d equal three. Let's run that and we have 127 orders that were placed with Janet. The next question we want to look at here is the number of order details. So orders is high article in that you've got an overall order that's with the particular employee and customer. But then their details, which include each item that the person ordered, and we want to know how many line items were there where more than 20 items for order. So let's go with count and you know, here, let's go look at order details and you've got quantity, which looks like what we're looking for here. So we're going to select from order details where the quantity is greater than 20. And that's 911 mine items were ordered with more than 20 items in each one. Okay, now it's your turn. How many orders had afraid? Cost equal to or greater than turning $50 in the freight cost is found in the order table, So pause it until you come to the right answer. Okay. We're gonna find out how many orders are gonna go. Orders. Where we work down here in order is free is greater than or equal to $250. Run. He had 47 orders where the freight was 250 or more dollars. Okay. The next step we take is using where with date. See in the next video. 12. 11 WHERE with Date fields: OK, in this video, we're gonna cover how to do selection with date fields. The basic syntax is similar in America, except that you have to put the day in a certain format. It's year dash, month dash date, but you have your equal greater than greater than or equal less than less than or equal similar to numeric. So let's do an example here. Let's find the number of orders that were ordered before or after January the 1st 1998 and we're gonna select count from orders and its border date. If I look over here, my table list order date is or equal to from 1998 01-1 gonna run that with that five and we get 270 out your turn. So I want you to come up with the query and we're going to switch fields here and it's gonna be when they were shipped. We want to find out how many order shipped before july 5th, 1997. Pause the video and come back when you've ran the queer. Okay, so if we look over here, our field is shipped date and we wanted to be before and our date was July 5th, 1997 in July. Phil, run it. 301. Okay. Next, we're going to start using queries that have more than one selection criteria, and we're gonna start with combining them using and see in the next video. 13. 12 WHERE using AND: this video, we're gonna start building mawr complex queries and we'll start with a logical operator. And so this answers the basic question. How do you select for more than one condition? And you have these logical operators and or not, and we're to start with. And for this one, all the conditions must be true for the record to be selected. So your basic syntax is going to be similar. You got your select with your column list from a table where Condition one and condition two and three and four and so on so you can have to arm or conditions that have to be true for the record to get selected. So let's go with an example here, and we're gonna try to find how many orders were shipped to Germany with the freight cost more than $100. And what we've got is select count for mortars where ship the country equal Germany and afraid is greater than 100 five to run. And we have 32 orders that met that criteria where both of them were true. Now, if you ran this without this condition, you would have 122 And then if you ran it with just the freight, you would have 187. But it both Germany and afraid have to be greater than 100. Then you've got 32 orders that meet both conditions. Okay, now it's your turn. So we want the distinct customers and so we don't want account. We want the unique customers where orders were shipped via United Package, which has an i d called to and the ship country is Brazil, so a ship via idee, too. So let's go over to our list right here. And so the first thing we want to do is let's look at our fields and you've got this ship via which ties back to your shippers table, and I want him looked up. Fact, that United Package has an idea of to and so we're gonna do here we're gonna do distinct. And we're gonna do customer customer I d from mortars where the ship country iss, Brazil, and shipped via equal to what's wrong. That and we've got a list of nine customers where these conditions were true. Okay, The next video we're gonna cover using the logical or condition see you them 14. 13 WHERE using OR: Okay, This lecture we're gonna cover using logical ORs when we're doing a select with where? So the basic syntax is going to be. Select your columns from a table where condition one or condition to three fours. Minasian one. And it will match any record where one of those is true. So start with an example. How many customers do we have in the U. S. In Canada? So they could be in the US or they could be in Canada. So let's take a look. So here we go. We're gonna do select count from customers. Where? Country Equal USA. Yeah, Country equal. Can it go Run that. Okay, that's 16. Now, if we look at these individually so we'll start with country is equal to Canada. We have three. Now, if we just say customers from the U. S, that's gonna be 13. But if we say U S Air Canada's 13 plus three giving us a total of 16 customers, Okay, now it's your turn. How many suppliers do we have in Germany and Spain? So pause the video and see if you can run this query to see how many suppliers are in Germany and Spain, so it could be either country. All right, so here we go. Let's do suppliers. The country is going to be Germany and then staying and is four. And again, if you run these separately, you'll see we have one in Spain. And then there's gonna be three in Germany for a total of four in either place. Now we're gonna do another one with three. So how many orders have we shipped to the U. S. Brazil and Argentina? So pause the video and see if you can run this query for three conditions. Right. So we're going to switch our table toe orders. Now, if you look at orders table, it's Yep. Country we're gonna go. Ship, Country. It's USA. Brazil or country. Equal margin, Tina. Got it. Shipped country. Here we go, Country. Now let's run this again. 221 orders went to the US or Brazil or Argentina. Now, in the next video, we're gonna cover the last logical operator, which is not see you on that one. 15. WHERE Using NOT: in this video, we're gonna cover using the logical, not condition. So the basic syntax is your selecting columns from a table, and it's where, not any of the other condition. So let's do an example here. How many customers do we have that are not in France? So let's go here. We're gonna go select count from customers where? Let's just start with country equal France. Okay, so if we want to find the number that air in France, it's 11. But if we want the customers that are not in France, we had a not in front of it. We have 80 customers that are not in the country of France. Right now. It's your turn. How many suppliers are not in the USA? So pause the video and go see if you can write this query and come back when you're done. Okay, so let's look at suppliers and not country equals U. S. A. And we have 25 suppliers that are not in the USA. All right, Next step, we're gonna show you how to combine multiples and ors and knots all in one statement for more complex selections, see, in the next video 16. WHERE Combining AND OR NOT: in this lecture were to cover combining and or and not statements. So when you want to do more sophisticated searches, you can combine your conditions together. The main advice I have here is to use parentheses generously. There is an order that things will execute in, but you're just better off putting parentheses around things to group them. It's so much easier to understand the logic of what you mean. You know, when you go back to look at this two or three months from now and so you want to combine the condition ALS that are to go together. Let's start with an example here. How many orders are shipped to Germany and they have freight charges that are less than 50 or greater than 175. Let's take a look at this Germany and then freight. Less than 50 are greater than 175. So here we go. We're gonna go select. I don't from Borders where and we're looking over here is gonna be shipped country, country, equal Germany. And then we're gonna combine it with an and but now we're gonna put parentheses around the oars. To group them together Was less than 50 or frayed. Greater than 175. Let's run that. That gives us 82 orders. Okay, Natural turn. See if you can come up with the query for how Maney order shipped to Canada or Spain. And this ship date was greater in May 1st, 1997. Pause the video and come back when you have the answer and I'll show you my solution. Okay, so we're gonna look for a ship. Country is Canada four ship country, equal Spain. And we're gonna group those together using our parentheses to keep it together. So shipped date is greater than may 1st. 1997. So 1997 051 set the run. We have 33 orders that were shipped after May. The 1st 1997 to Canada or Spain. Now, that's it. Next lecture we're gonna talk about using between, see them 17. Using BETWEEN: okay. And this lecture we're gonna talk about using between now between is a short cut for greater than or equal to and less than or equal to. And so instead of riding out freight greater than 50 and freight last center Eagle 200 you can use between 50 and 100. It's easier to read it later. So this operators been introduced toe kind of simplify some of the logic around this small school with an example here. How maney order details. Do we have that have a unit price between $10 in $20. Let's go over here and we'll go from order Details where unit price is between 10. Andi, run this query. We have 803 order details. Now it's your turn. So pause the video and see if you can see how many orders were shipped between June the first of 1996 and September the 30th of 96. Pause the video and then we'll come back and I'll show you my answer. Okay, so we're gonna switch to orders, and this time we're gonna do shipped gate. And it's between June 1st 1996 no. One and in 18 96. September the 30th 61 Orders. OK, in the next video, we're gonna cover how to use the operator in See that? 18. Using IN: Okay, This lecture, we're gonna go over using end. So your basic syntax is in your wear claws, you have the fill in, and then it's a series of values to 3 22 33 88 And this is the same as if you rode out or in between, all of those values. So when you've got a longer list of values using in makes it a lot easier to read and keep track of what you're doing. So let's go straight to an example. How many suppliers do we have that are located in Germany, France, Spain or Italy? All right, here. So we're gonna go select the count from suppliers where country in. And then this is where we do the list. Germany, France, Spain in the last Italy. Run that. And you see that we've got nine suppliers from that list. How many products do we have in category 136 or seven. We're gonna use category I D. Later, we'll be able to join the tables and use the actual category name. But for now, let's just go with category I d of 146 or seven. Stop the video and come back and you see my solution. Okay, so we're looking at products and we're using. We look over here, the category i d. This time instead of strings its numbers for six for seven, and we have 33 products from those categories. Next video we're gonna cover using order by to control the way that fields come back. See that? 19. ORDER BY: okay. And this lecture, we're gonna cover order by now that allows you go change how the results come back. So if you do something like select distinct country from customers, it's hard to find specific customers because the records just come back in a jumbled order , depending on where they're located. In the database, the fixed to that is ordered by the sin taxes. You got your select from table, and then you order by and you could have more than one column in each column. Could be either a sending or descending. And so let's look at an example on this. So let's get a list of our countries of our suppliers in alphabetical order. So we're gonna go with distinct and it's from and we want to know if we run this ahead of it. I gotta say, distinct country. You, you know, you get back the list, but you've got Australia than Germany, that Singapore that France. So there's no particular order, so we're gonna add order by country. The sender there you have Now it's Australia, Brazil. So it's alphabetical order all the way through the list. Now, if you want to see them in the opposite order. You do descending. And there you go. Now it's from Z down to a reverse order. Okay, Now let's add cities to this. So what we can do here is we're gonna do country, and we're gonna order by country ascending. Now you can switch these. So if you wanted, you could do city descending. And so here you would have Australia, which is your first country. But then it's Sydney and Melbourne. But you can do it in any particular or they want. So here. Now, we reversed it to where it alphabetical order. And then we can wanted to We could do this here. You could pull back, you know, USA, but alphabetical order within the USA for your suppliers. Now it's your turn. So let's get a list of product names in unit prices, and we want to order by price. First highest Lois and then product name in alphabetical order in the network. They've got the same price. You'll see it in alphabetical order. So stop the video and come back when you've got the answer and I'll show you how I got there. Okay, so we're gonna go with price. Eight words gonna pull. Let's go here with product name. And then it's unit price. And it's from products and its order by, you know, price descending and then product name acid in. There we go. We've got a thing from the most expensive product all the way down to get tossed. Now, next step, we're gonna learn men and Max functions. See that? 20. Using MIN and MAX: OK, in this video, we're gonna cover using men and Max functions. So the basic syntax is you're going to select men column name from a table with a condition , and you put the column in parentheses and then the same for Max calling. Now, let's answer question here. When was our first order ordered from Italy? Okay, so we're gonna go here with since its first is gonna be the men and we're looking at the order table. So we've got ordered A from borders, each ship, country, equal Italy. All right, let's run that double click. So it's August the 7th 1996 Now, let's find out when was the last order shipped to Canada? So this is going to be last, will be biggest. Or Max, What's gonna be Max? It's not ordered. A It's shipped date and we're shipping to Canada. So if we double click here, that is April The 30th 1998 was the last order shipped to Canada. Okay, now it's your turn. Now we're gonna find the slowest order, and that's gonna be defined as the difference between when it was shipped versus when it was ordered. So you could do column arithmetic, and you want to find the largest one of the largest amount of time it took. So pause the video and come back and I'll show you my answer. So we've got shipped date minus order date, and we're gonna go with France here. Okay, so let's run this query and we have 34 days. So that's the longest it took between when the order was placed in when was shipped it. So this is the kind of queries you're gonna be able to start answering to get actual business intelligence so that you can help determine if you're running your business correctly or not. All right, next up, we're gonna use average and some see them. 21. Using AVG and SUM: this video we're gonna cover using average and some of their sin taxes very similar to men and Max, where you're doing a V G and then parentheses around the column that you want the average from, or some with parentheses around the column. So let's start with the basic example What was the average freight of order ship to Brazil ? So here we go. We're gonna go average and then all in order. It is the freight calm, and then the ship country is going to be Brazil. So if we run that the average is $58 round up to 80 cents. But 79.7 so forth out on the decimal place Now, the next one that we're gonna answer here is how many individual items of tofu, which is product I. D. 14 were ordered. So you've got to go to order details and let's run this equation with product i d 14. So here we go. We've got order details, So we want these some and it's quantity from order details. We're product I d. You call 14 which is for the tofu. So we had 404 tofu is ordered in all. If you sum up across all order details, not your turn. What was the average number of steely eyed stout, which is product I D 35 order. You're gonna take the average of all the order details to see what that is for Steel Eyed stout, which is product I D 35. Pause the video and come back and we'll look at the answer together. Okay, So we're gonna do average and its quantity. And then we've got order details and steely eyed stout, which is number 35. Let's run this here. And it was 24.5 to 777 Now, next, we're gonna use light to match patterns in your text fields. See, then 22. LIKE to Match Patterns: in this video we're gonna use, like to match patterns in text fields. So your basic sin taxes you have your normal select from and then you have where a column is like a pattern. So let's start with our first character, which is your percent sign and percent stands for 01 or more character. So it'll match anything, including nothing. And so some examples here where supplier name is like a percent. And this would be every supplier name that starts with a in any number of characters after it. Now, these air case sensitive So would have to be lower case A. And this is one which ends in the letter e. So it's percent e. It's anything as long as the last character is E. Now here we did one where you got percent on either side. So it's any supplier name that has B o. B in the middle somewhere. Could start with B O. B, actually, because it's zero or more, but it has to have Bob in there somewhere. And then the last one here is it starts with capital. A percent means any number of characters or no characters at all, and then it ends in lower case I OK, so let's do an example here. I want all my customers that have a contact whose first name starts with D. So let's go over here. We're gonna go look at our customer field here, So we're gonna get all our company name and I'm gonna look a contact from customers. Where? Customer name. Like Capital de. It's contact name, not customer name. Here we go. Here we go. There's three companies that have a contact Diego Daniel and Dominique Perry Air. Now the second wild card character that you get is an underscore, and it stands for any single character. So you could do wear supplier named like underscore a percent. And that would be any match with a as the second letter. So it has to have a first letter, then a and then anything after it. Another example here is E underscore percent underscore percent, and this would basically be e with two other letters. At a minimum, it could have mawr, but it has to have at least two other letters there. Now, let's answer this question here. Which of our suppliers has o. R as the second and third letters in the company name. So our is the second and third letters out of our suppliers. So here we go. We're gonna go down and look at our supplier table, so we're gonna go. It is company name there as well. Do suppliers where? Company name like Oh, are so we have underscore. Meaning we have something in the 1st 10 r and that Anything after that. Hope this company name. Here we go. All right, we have columns here. Returns there. Nord. I'm not gonna try to pronounce all these four names that came back from that queer. Now it's your turn. Let's see if you can come over to queries with which customer company names end and e. R. So they begin with anything as long as they end. And, er pause the video When you have the answer, come back and let's look at it together. So we're looking at customers and we're going off of company name. We're company name like and we want it to end in e. R. So percent er let's run that. And we've got two names that into New York. All right. Next up, renaming columns using an alias. See you then 23. Renaming Column With Alias: in this lecture, we're gonna cover renaming columns with an alias so your basic syntax is select some column that can also include, you know, arithmetic or whatever. If you're doing more than one column and you're multiplying or dividing them and then use as and then you give it a name from the table now, once you rename that, you can use it in the rest. The queer If you want to order by later on Group I and or joined by what will cover those later? But right now we're just do your basic renaming the field. Let's give an example. Let's go through and for each order detail. Let's find the total spent but return that as total spent so that it's very clear what we're doing. So here we go. We're gonna go from order details. It is, you know, it price times quantity and then as total spent. I'll see order details, and there we have. We have the total spent on each order detail. Now we're gonna use the alias and the rest the query. So let's order that result that we just had but ordered by total spent descending so that we could see the greatest ones at the top. So now we're gonna go here, and we're gonna go order by and notice instead of doing We could do this whole thing again , which really looks ugly when it comes back. What we're gonna do total spent here we wanted to be descending. There we go. So the number one or the most expensive thing ordered was $15,810 worth of this one particular item, actually, two different order details. Right now it's your turn. So let's calculator inventory of products. If you look in the products table, there are two feels that you need to multiply together to find out the value of the inventory and you want to return. That is inventory own hand, and you want to order it by that column descending. So pause the video, Go see if you can work that out and come back and I'll show you the answer. Okay, so now if we go to products, we have unit price and units and stock. So that's what unit, prize units and stock total and inventory and I also want the product just so it's clear what's coming back from products. And we're gonna order by this field descending. So there we go. We see that we have Cote de Blais has the most value in inventory. $4479.50 work. All right, Next up, we're gonna do limit to control the number of records return. 24. LIMIT to Control: in this lecture, we're gonna talk about using a limit to control the number of records return. So the basic syntax is you have your normal select statement. You could have aware, here, below your from and at the very end you put limit and you give it a number. And this is when you're in a production database. A lot of times, you don't want to return everything because you can overwhelmed the database. Or it could take a really long time to download all the information. So sometimes you just want to put a limit on that. So let's use it here. Let's find the three most expensive order details, and so we're gonna use calculated, filled and order by and limit to do this. All right, so we're gonna go here, and we're gonna go with order details. I'm going to go order i d. And we're gonna do unit price times quantity as full spent and go from order details, and we want to order by total spent descending. But instead of getting everything we just want LTD. Three here. There we go. Those are the three most expensive order details and I looked up product i d 38 it was aniseed syrup. So that seems to be the product that's generating the most volume in terms of revenue, Not your turn. Calculate the two products with the least inventory value in stocks. So the least amount of money tied up in the inventory pause the video and come back and I'll show you my answer. Okay, We're gonna go with products here, so let's go. Product name. And it's unit price. And we're gonna do units and stock. The This is our inventory, and it's from products. And we're gonna do by inventory and were to do a sending. We'll start with least inventory due to here, So let's hit. Run. It looks like we have no Alice mutton or gumbo in our inventory currently. All right, Next up, we're gonna talk about no values and how you handle them in your database. See them 25. NULL Values: right in this video, we're gonna cover no values now, what isn't no value. It's a special value that databases used to say. It's unknown. They don't know what that field it's, so it doesn't mean zero or empty. It just means unknown. And so when you insert records, if you leave off of value for particular field, it will put Noah in there as the default so you can search for no values or the absence eternal values with two phrases where some column is no or where some column is not know, with spaces in between the different letters there. So let's answer basic questions. How many customers don't have a region value? So here we go. We're gonna go select counts as work will know how many. Oh, from customers. Where Region. Let's make sure we've got the right region. Yet Region is no. So we're gonna find all the customers here where the region is No. And so there's 60 of our customers. I don't actually have a region value now. How many suppliers have a region by? So this is gonna be that is not know. So we're gonna do this from suppliers and instead of is no, we're gonna go is not know. And so we have nine suppliers that have a region value in the record. Right now it's your turn. So how maney orders did not have a ship region. So pause the video and run a query to find out how many orders have a no value in the ship region. Okay, so we're gonna go from Borders and its ship Region is no. Let's see how Maney. There's 507 orders where we don't have a ship region value. Okay, Next step we're gonna talk about diagramming table relationships is our first step. Before we start showing how to join tables together, see them. 26. Conclusion: thanks for joining me for this introductory course on post Greste sequel. So let's go over what we've covered in this course. First, we started by installing Post Greste, SQL and North Wind Database. Then we went to simple selects where you're selecting all the information from a given table. We followed that by introducing where which you used to narrow down the queries. And then we learned about ends and orders to get to more complicated, where clauses that was followed by ordering and limiting the rose that came back. And then we finally we covered aggregate operators like average Max and men. Well, this just scratches the surface, but I hope you've enjoyed it. And I hope you'll continue with our second course where we go further in depth on how to do more sophisticated queries with post Greste sequel. Thank you