PostgreSQL - Joins, Modifying Data, and Defining Tables

1. Introduction: Hi. My name is Will Bunker, welcome to my second course or post GREss joins modifying data and building tables. So in this course, we're gonna build on what we learned in the second course by going to more complicated queries, joining multiple tables together so that you can create rich sources of information and answer really sophisticated questions about what's been going on in your company's data. And then from there we're gonna go to modifying the data with insert, update and delete statements. And then for the final part of the course, we're gonna teach you how to actually build your own tables using sequel and including putting in constraints so that people put in the right kind of data and you end up with good data sources. I hope you enjoy this course. It's the second step to your path to becoming really good. It database administration. Thank you 2. Drawing Table Relationships: in this lecture, we're gonna talk about diagramming table relationships. Now, this diagramming is a tool to help you understand how the tables joined together with each other. So what I do when I generally approaching new databases, I will draw out all the relationships between the tables. And the standard form is that you create a box with the table name at the top and then all the field names down the row here, you list him out. So take a piece of paper out right now, pause the video and draw out customers here with all the field names on it. And then we're gonna show you how to hook these together. OK, so let's take the orders table, and I'm showing you here where expanded it in PG admin and draw a second box on that same piece of paper with orders and all the fields that it has in a pause the video and draw at that table on the same piece of paper. Okay, now you should end up with something that looks like this where you've got customers on one side, orders on the other. Doesn't matter where they're laid out on the paper. now what you want to do is you want to look for fields that connect the two tables. So in this case, orders has a customer I d field that is also there in the customer table. And so these two fields, or what link these tables together? And so what I do is I'll draw a line in between tables that have these links. Now, on some of the diagrams you'll see on the side where there are many orders with customer ideas, you may put on infinity. Sign here to say that this is a one too many relationship. So, you know, in creating tables, you typically have naming schemes where the table name is the plural off the types of records that it holds. So for customer data, you call it customers order data. You caught orders and then generally within each table, there's an I D field that you need for each record in the table. So in customers you have customer I D and orders you have order i d. And those become he feels that link together other tables where the information is related . Now pause the video and go, you know, use PG admin to expand out the tables, look at the fields, write them out, and then try to draw to relationships between the different entities on there. Now, I've included in this lecture PNG file, which has a very nice diagram that's done with a piece of software showing all the relationships. But it helps you get familiar with the relationships to draw him out by hand. And this is something that I almost always do when I'm working with a new database. And this is the type of feel that you should end up with. These are all the relationships mapped out between the different tables. Years won't have the same structure, but the lines connecting them should be the same. All right, next step, we're going to start working on joining tables together. We're gonna start with inter join. So you got 3. Grabbing information from two tables: okay, This lecture, we're gonna talk about grabbing information from two tables. So far, all the queries I've shown you are just using a single table. You know, the real world doesn't work that way. Users are gonna want information in a single query that comes from multiple tables more than one table. And there's some complexity around how to join those two tables. And it's trying to answer questions like, Do you only want records that have information in both tables? So, for instance, do I want Onley cussed orders that have ordered details? So if in order didn't have order details that wouldn't show up in this, Queria would have only have the ones that matched on that order I d field. Or do you want all records from one table, regardless of whether they have any records in the second table, and then any matching records in the second table? So, for instance, I only want I want all customers, But then I also want any orders that the customers have had, Or do you want a combine the tables with each row in one table, being combined with another Rohan another table, so Row one would go Throw one row, one with row to row one with throw three wrote to Withrow one, just like a giant join together that creates a matrix of all the roads in both tables. So these kinds of questions lead to different ways of joining the tables together. We're gonna start with the simplest tight, which is in the default type, which is give me the records on Lee. If both tables have matching fields, this is called an inner join. That's a technical word for it. And it pulls back records that have an i d that that air in both tables. So to do this, you've got to use the full name syntax where you put the table name, period, field name. And this is because generally the I d. Fields have the same name in both tables, and you've got to tell it Hey, this is the customers customers dot customer i d versus the orders dot customer i d. So the basic syntax for joinus select normal Select with your column names from table one inter join table to own, and then you match up the I. D fields. So you know, you also can do it without interviewing, because enter is the default. So you can just have from table one joint table two. And that's gonna be the way that I do in this course is used the default joint. Now, the way you know that you need to do this is the user's gonna ask for something. Like, I need a report that gives me the customer name, order, date and ship. Country. Now you in the back. Your head. No. Wow. Customer name is in the customer's table in the order date and ship Country is in the orders table. Ding, ding, ding. I'm gonna need do a joint here. So to do that, let's go over here and I'm going to show you this. It's select company name ordered, eight ship country from orders and you're going to join customers own. And then this is where we use the full table name syntax customers dot custom Ryan D equal orders dot customer. I d. Now don't fear later, we're gonna get into how to do this without all this typing. But this is the basic syntax here. And so when we run it, it pulls back the records from both tables joined together here. And so here we go. We've got all of it put together in one nice, neat package. Now it's your turn. Take this new syntax and pull back the first name and last name of our employees and the order date for all the orders that that employee has done. And here's what you're going to see if you get the query. Right. So give you a hint here. So pause a video. Go build a Korean to you Get this data back. Okay, so here we've got select first name, last name order date from orders. Join employees, own employees dot employee I d equals orders. Don employee i d When you run it. This is the information that you get back. All the employees combined with all the orders that they placed. Now, if you had an employee that had no orders, they would not show up in this report. Okay, let's practice this again. Pull back the company name, unit costs, and units and stock that come from suppliers. So that's where the company name is gonna be. And then unit costs and units and stock is gonna come from your products table. So pull back that information and here's what you're going to see if you get it right. Okay, so here's the query. Select company name unit price units in stock from products. And again, you can flip these in anywhere that you want. It doesn't matter. I just picked products first. But if you if you did from suppliers, it would work just as well. Join suppliers on products dot supplier I d equal to suppliers. That supplier i d. So that's it for this lecture. I would highly recommend that you pull out that chart showing all the connections between the tables and just start practicing connecting one table to another in these simple to table joins because it gets more complex from here. But if you get the basics down, it will make a lot more sense. It will make a lot easier for you to do this. Hope you enjoyed it and see in the next video 4. Grabbing information from multiple tables: in this lecture, we're gonna talk about grabbing information from multiple tables. So the basic syntax is very similar of what we did when we join two tables and you just start stacking your joint statements, you could spell it all out with inter, join table three, and then you have your own claws with the fields that connect him or you just do join. But regardless, you just start stacking joint after joint until you've built together the relationships that are gonna come back from the data. So let's start by an example of bringing back the company name the order, date the product i d the unit price and quantity. And so we're gonna need to connect customers orders and order details to pull all that information back. Let's go return database, and what we're going to see here is we've got select and we put the company name ordered eight unit Christ quantity. So these air the column names, I'm gonna start with order again. You don't have to do it in the same, uh, order that I do. But, you know, you've got to connect them one at a time, and then I'm gonna join order details and in our own claws, I'm gonna use the order. I d using the full table name syntax. So its own orders dot order i d. Equal order underscored details dot order i d. And then finally we joined customers. And you're using the customer. I d field there. So if we run this, we come back with a desired information all put together. Okay, So let's stop. And you need to take time to practice this. Hopefully, you practice last time joining sable single tables. But here, you know, take the previous query that we just did and add product name to the results, Which means you're gonna have to connect it to the products table to get back the product names. So pause the video. Put that query together, come back and I'll show you my answer. Okay, so we added another lying here, Another join which is joined products on. And then we're using product ideas the field to join the tube. And then in the top part, we added product name to come back with the results. And so here we go. You pull it back, and that's what you have. Okay, that's your turn connect categories to the previous thing and bring back the category name . So notice how we're just building this up and we're adding more information below. You'll see the results. So pause the video and go connect in and pull back category name. Okay, so here we go. So again, we just added another joint clause at the bottom, and we joined categories and we used in our own claws a category i d. And it's categories dot category i d equal products dot category i d. And if you run it, sure enough, you pull back. And now we've got company night product name, category name for each detail in the Order and Order Details Field. All right, So finally, let's Once you've joined it all together, you can use where, like you would in a normal ah, select statement that just pulls back information from one table. And so at a where clause that selects the category name of seafood with an amount spent greater than $500. Well, 500. So pause a video ad that query on their come back and I'll show you my answer. Okay, So the final one is here, and after the joint we just slapped in a where clause in its category name Equal seafood and in order details unit price. Now notice. I spelled out the table name. Why did I do that? I did that and you probably ran into this where? Because products also has a unit price. So any time there might be confusion between the two fields, you have to spell the spell out which table it's coming from. So it's and order details dot unit price times quantity is greater than 500. So let's run that. And there you go. That pulls back all the records where it's seafood, and they spent more than 500 on a particular order detail, and there were 97 records for that. So that's it again, I would just really recommend that you take out and start playing with the data connecting it together, seeing how you pull tables together and get information from That's it for this lecture. Hope you enjoyed it and see in the next one 5. Left Joins: But in this video we're in a cover left joins So left joins pulls back all records in the first table listed and then any matching records in the second table. So what this allows for is if there are no records in the second table, you can still pull back records from the first table and show that they're missing data in the second table. So the syntax is very similar to join your inner joy. Except this time you have to explicitly call left join. So if you think about it, Table one is to the left of table two. So this is the one that all the data will come back from table one, regardless of whether their records in table two or not. So let's look at customers toe orders, and we're gonna bring back to company name and order I D. But we'll do a left join instead of inter joint or just regular joy. So we're gonna get rid of this complex one here. And so we're going to start with select company. No, you order i d from customers. So this time the order does matter because it depends on which one you want to include. So we're gonna do left, Joined orders, Born borders, Daud Customer. I d equal to customers. Customer I d. And so let's just pull that back here. Now what? We're gonna notice if we go all the way down to the bottom, you're going to see there are two customers that haven't placed in your words yet. Now we can look for just those customers if we use our is No. So let me show you the syntax on that. So now we're trying to find, you know, Do we have any customers that haven't placed orders? We don't look to the whole less than you would do where order I d is. No. And there we go. We pulled back that to customers that have no orders. Okay, Now it's your turn. So let's do a left. Join between products in order details. So pause the video and come back when you built a left, join between products and order details. Okay, So we're gonna go products. Let's go with the product name. Well, we could do order I d, because it has it in their from products left joining order details on products. Just gonna be product. I d product R d Are you aware yet? Let's take a look. So here we go. We've got all of the product names connected to the order details, regardless of whether it has an order, detail or not. Now uses null to see. Do we have any products that haven't been ordered? Let's find out by using where with is no. Pause the video and come back when you think you've got it. Okay, here we go. Where border R D is. No. Nope. Every product has been ordered at least one time. Right In the next video, we're gonna cover right joints. 6. Right Joins: right in this video, we're gonna cover right? Joins. So right Joins are the opposite of left joins. They pull back your matching records in the first table and all records in the second table . So, in essence, you're just flipping the order in which you pull the or the not the order. Would you pull the records but the table that you're going to retrieve no matter what is the 2nd 1 instead of the 1st 1 and so clearly, you know, right? Join and left Join. You could switch him up by switching the order of the tables. So let me show an example here. So it's the same sin taxes as joined and left joined, except you just spell out right join instead. So let's connect orders to customers. So we're gonna flip it. We're gonna call orders first, but then do right joint on customers so that we're getting back all the records of our customers regardless of whether they have orders or not. But we're gonna reverse the order of the tables. And what this does is that way you don't have to worry about the order when you're doing it , you know is use a left doing or right? Join. Just opinion Which table came first, But let's take a look here. So we're gonna go from Borders first. Do company Name and border. I d. Okay, so now we're gonna do a right join to customers, orders customer I d. And we're not gonna do that one. So let's press it here. And here we go. So now we've duplicated it, but this time, with the right join if you go down to the end there, the same two companies that don't have any orders as before, But this time with a right, you instead of a left join. Now we could do the same thing we can add. The is no to eliminate all the noise. So we're gonna go back and we'll do where order i d is No. And now we're left with two that don't have orders as we had before. But this time, with the right join. Right now it's your turn. So let's do right joining between your customer demo and your customers. So we're gonna start with customer demo first and customers second. So let's go look at customer. It's actually customer customer demo and we want the company name and we're gonna do customer customer demoed customer D. So we'll get the customer I d out of there. Then we're gonna come here. We're gonna join. It's gonna be customer demo first, and we'll write. Join it to customers. And it is customer I d. And so let's not doing aware yet, so here we go. And it looks like none of our customers have been set up for a customer demo yet. Right next step. We're gonna do full joints. This is the last one of the joints that we're gonna cover, So you that 7. Full Joins: Okay, This lecture, we're gonna cover full joints now, Full joins pulls all records in both the first table and the second table, even if they don't have a linking field or there's no on one side of the other side. So it's almost like a left and right join together. So they call it a full joint and sin taxes Exactly the same. It's full join written out instead of left, right? Or just regular join. So now we're gonna connect orders to customers. Now, this would be useful if you were looking for customers that didn't have orders. And also orders that didn't have customers may be the wrong customer idea. He got put in our customer got deleted, and you want to clean up your database. And so you wanna do a full join to make sure that you don't have any orphan records is what they call it. All right, so we're gonna start over here. We're gonna go Company name, border are the from customers? Cool. Join Borders customers. God, customer d equals orders. Got customer. I d must run that. And there we go. If you scan through this and you get to the bottom. You remember We've got to customers that don't have orders, but we don't have any orders that don't have customers. All right, so now do a full joint between products and categories to see if there's any orphaned records on either side. So do a full joint between products and categories. Pause the video and come back when you have it. We'll look at the answer together. OK, so we're gonna go Product name in category from products will join categories, category ideas. The linking field. Let's run this there. We dio. Now, I'm just gonna scan through here, but I feel sure that they all have records on either side. Okay. Next, we're gonna cover self joins where you join a table back to itself, see them? 8. Self Join: all right. In this video, we're gonna cover self joints now, self joins. Or when you connect a table back to itself, you know, actually end up doing this more than you would think. Looking for patterns in the data. So the basic syntax is you need to use aliases for the table name because you're gonna have to tell it which version of the table you want to pull the records from. So because the fields are the same and, you know you're connecting the table back to itself, you have to use an alias when your spelling everything out. So it's from table one, Team one, and it's gonna be the same table and then you name it T two here. So it's gonna be orders, orders, customers, customers. Where condition? Let's let's give an example here. Let's find all of our customers that are in the same city. Let me show you how to do that. Using a self joint. Okay, So what we're gonna do is we're gonna match up customers in the same city, so we're gonna go customers, see one. Customers see to and in the fields. We want a full pull back. Our company name from the 1st 1 the company name from the 2nd 1 Fill it out and then finally, city name. All right. And then this is gonna be where city is equal. And to keep from selecting duplicates works the same company mashed up to itself. And also, we don't want the reverse case off. You know, company a company be than company B company A. We need to make sure the i d. Fields are differentiated. So we're gonna do customer name. Customer I d is greater than C two dot custom ride. So let's run that. And there we go. We have a list of our customers that are in the same city right now. I want you to do the same thing for suppliers less. Except this time, suppliers from the same country, Not not necessarily the same city. So pause the video. Come back when you've had a chance to try to do this, and I'll show you what happened. Okay, so we're gonna go with suppliers, and it's gonna be I'm just gonna do s one and s to, And we want s word country to be equal toe s to country, and it's gonna be supplier. I d this time. One has to. There we go. Now we want that's one company name. That's two company name. And then here, we're gonna do s to less used to us one country when I would do him in order Country so that it's a little dangerous evil list. We're gonna tell it. Which one? Okay, let's run that. And there we go. So now we have a list of all the suppliers that are in the same country. The payers. All right. In the next lecture, we're gonna do group by. See that? 9. Group By: all right. In this lecture, we're gonna cover group by now. The basic syntax is your normal select. But after your where you're gonna have this group by column names and this is gonna aggregate all of the data together within that group, And then you can also add an order by if you'd like. Let's start with an example. How many customers do we have in each country? So let's go over here and we want to go select. And then we're gonna do count. And just the rose from customers were doing all of them. So there's no where group by country, and we want to see him in order by the count descending. All right, let's run that. And here we have it. Each country with the USA, with the most customers, followed by Germany, France, in Brazil. Now you could do them with joins, so it's not limited to just one table. So what is the number of products for each category? Let's take a look at how to get that. All right, so we're gonna start with product name, no country, and then the count. Okay, We're gonna start with select category name double check on that to see what column it is. Yes. Category name count from categories. And then we'll join that, too. Products on products dot category i d equal to categories category I d. And they will do our group by category name endless order. Fine account descending. Let's take a look. And here we go. We've got 13 confections, 12 seafood all the way down to five produce products. Now you can use any aggregate function. So we've been doing count, but we're gonna switch and take a look at average, which the average number of items ordered. Four products ordered by the average amount. So we're gonna connect products to the details to see what the average order sizes. All right, so here we're gonna go select, and we'll do product name. And then instead of count, we're gonna do average and then less look and our order details call him. And so it's quantity. So we're gonna do the average quantity, and it's gonna be from we'll start with products and will join it to border details. And that is gonna be on the product. I d. From products dot product. I d equal to details. Uh, product all right, D. And then again we're selecting everybody. So we're in a group by chronic name and order by the average quantity descending. Let's run that. And there we go. Now it's hard to read this because of the way yet rounds it off. So let's just round it here. He's got too many digits to show in the display. So here we go. So sco gee, Skakel aid, which is a word I do not know how to pronounce is number one with 41 the next below it is 36. All right, now it's your turn. How many suppliers do we have in each country? Pause the video. Go work the query up, Come back and I'll show you my answer. Okay, so here we go. We're gonna go with select country and then the count from suppliers group by country, border by count descending and USA is number one with four, followed by Germany with three. OK, what was the total value of each product sold for the year of 1997. So this is going to involve summing instead of counting. So stop the video, see if you can get this one come back and I'll show you my answer. So we're going to start with product name, and then we want a some And we could do math in here. So we're gonna do the unit price times the quantity that gets the total amount of the each water from products join to order details, and we use the products product I d. Order details the product i d. And then the where clause. It's going to be the so to get to the date we have to join with orders. Quarter. I d. Order I d. Now the look here order date is between. It's the year of 1997. So we start with a 101 all the way through 1997 12. 31. Okay, that gets us the data we want. Now we're going to group it group by We wanted for each product. So we're grouping by the product name, and we want toe order. Fine. Just some here descended. All right, so we've got the ambiguous meaning we've got to tell it. It's not the products unit price, but the order details. All right, let's run this again. And of course, we have to do it Down here is well, all right, there we have it. Cote d Blais had the most cells for that year of $51,962 followed by 37 in the next one. So there was a pretty big jump between the second and first product. Right? In the next video, we're gonna cover having which you used to filter groups talk to them. 10. HAVING to filter Groups: okay, This video, we're gonna talk about using having to filter groups. So the basic syntax is you've got your select statement from you can have aware condition you always use having after group by and then it's having some sort of condition. And then you can also do optionally in order by now the difference between having and where where filters records before you group them. So if you put something in the where clause and if the record doesn't match that where it never makes it into the group I statement having filters the records after you group them. So let's find products that sold less than 2000 in the last video, we had created a grouping of products by the amount they sold. So we're gonna find products that have sold less in 2000 total. Okay, so the query that we did last time was we selected the product name and then we did the sum of quantity times the order details unit price is total body. And so we had to also join order details, toe orders and products in order to make that work. Now, this time, instead of where we're not going to be where we're selecting all records. There were grouping it. Buy the product. Name what we want to do. Having a total What greater than 2000. Okay, let's run that. Okay, so you can't. This is an interesting point here. You can't use your re named field name there, so we're gonna have to grab the original statement and put it there and let's run that. And here we go. We have a list of 74 products that have sold more than $2000 total. Now, if we wanted to reverse that and let's find out everything that's sold less than we've got three products that didn't sell more than $2000 work, that's thes three here. Now it's your turn. So create a query that shows all the customers that have bought more than $5000 of products , so they have a total amount bought greater than $5000. Pause the video, see if you can construct your query, come back and I'll show you my answer. Okay, so we're gonna go with company name. It is gonna be the same quantity. Times unit pricings is total bought. Now we're gonna go from order details. But instead of joining products, we're gonna join customers. So we joined order details, toe orders that gets us to our customer. Now we need to change this cause here, go join customers. Oh, customers died customer. I d equal to Borders customer Heidi, and we're gonna group it by company name. And we've got that. Some here are conditional is greater than 5000. So we're gonna go with that. And then let's order it by your total bought descending. We'll see if we have done it correctly, and we have. So here you go. Quick Stop has bought the most $117,000 worth of product and all the way down to Morgan Stern, which is only bought 5000 and $42 worth of product. OK, now we're gonna add aware claws back in here. So let's do the customers that have bought more than $5000 a product. But let's put an order date, which is the 1st 6 months of the year 1997. So stop the video, see if you can add that where in there and then come back and I'll show you the answer. Okay, so the wear goes after you're from and joins, But before your group, I So we're gonna put it in here and its order date. I'm gonna do between the first day of January and 06 30. Let's run this. And here we go. We have 21 customers that bought more than $5000 worth a product of 1st 6 months of 1997. All right, in the next video, we're gonna cover union, see them. 11. UNION: all right. In this video, we're gonna cover Union. So what is the purpose of union intellect? You take two or more queries and combined the data back in one returned set. So the syntax is you have basically to select statements, and in between them, you put union. Now they must have the same number of columns and the column types must line up so you can't have a number and one and text in another. And this union will remove any duplicated rose. So let's look at an example here. Let's get a list of all customer and supplier company names. So here we go. Let's start over again. So we're gonna do so. Left this company name from Customers union and then below that we're gonna do select the company name from suppliers. And there we go. That is a list of all companies that are either suppliers for customers. Now there's a second iteration of this, which is union all And what union all does is it doesn't remove duplicates. So if you had, let's say we're trying to get cup country names, it would have only one record for Argentina or U. S. A. If you did union. But if you did union all it would have a record for every company that had a country of USA would have a duplicate in there. So let's show an example here, Fine cities. So I want all the cities, including duplicates of our customers and suppliers. So here we go. So instead of company name, we're gonna do city and city. Now, if we ran it with just union, you would get a list of 93 unique cities. But if we do union, all run that we've got 120 total cities, including duplicates, not your turn. Find all the distinct countries of our customers and suppliers and put it in alphabetical order. So you need in order by clause at the end. So pause the video, see if you can come back with the distinct countries of customers and suppliers. Okay, so we're gonna go with country from customer, and since its unique I'm gonna do union in its country. And they were at the very bottom. We're gonna do order buying country descendants. There we go. There's an alphabetical list of countries and you can do ascending if you want to the other order. Now try this one. I want a list of all countries from our suppliers and customers with a record for each one . So include duplicates. Pause the video and come back when you have it. So the only difference between this one in the last one is this union all do it here. And C, We've got duplicate Argentina's Australia's Brazil so forth. All right, In the next video, we're gonna cover doing sub queries using exist. See you then. 12. Subquery Using EXISTS: alright. In this video, we're gonna cover sub querying Using exists now a sub Queary is where you have another select statement within the where clause of your 1st 1 So this takes a little bit of getting used to, but once you're familiar with it will become second nature. But it's with exist. You've got select your columns from a table where exist and basically it looks to see if this condition is met in the sub queer. And if that's met, then it will pull Pull the record from the first table out. Now, this is very abstract. So let's jump into a concrete example here. So I want to find all my customers that had an order in April of 1997. So here we go. We're going to select, and I'm just gonna do company name from customers where exist. All right. And then this is where the magic happened. So we want to see if the customer Haddon order April of 1997. So we're gonna do within here. We're gonna do select for this to customer I d from borders. Where now this is the interesting part. So you have to basically cross over from your first table to your second table so stood of adjoining. You're gonna have the condition within the sub query. So let me show you how that works. So it's where borders dot customer I d equals and you do the table name from the outer one customers dot customer i d And then the second clause is the order state and we're gonna go and put this on another line were ordered date between and it was April. So it'll be 0401 and 04 31. That's from that query. Now, what I like about this is it will tell you if your dates are off. So there is not there not 31 days in April. But if you put in 30 hit run again. Here you go. So we had 27 customers that had an order from April. Right now, you could have done that with a joint, you know, See, you could have joined it, but the thing that the joint won't let you do is find out customers who didn't have an order in April of 1997. So let's go back and look at that so if we said where not exist, we can now pull back. 64 customers did not have an order in April of 1997. Now you could have joins in your sub query. So let's look at one like that. What products didn't have an order in April of 1997? So we're gonna go with product name. It's from products and where it does not exist. Product I d. And we'll start with our order details. And then we'll join that toe orders because the date is in the orders and the product ideas in the orders. Details from orders on orders dot order I d equals order details that order I D. And then we've got where and here we're gonna have to join, so we know that it's order details. Sort of customer idea is gonna be product i d products in the order date. April of 1997. So let's run and see what happens here. Okay, Got a spell it right. So it's product name and there we go. We had 23 products that did not get a single order in April of 97. Now it's your turn. So find all the suppliers with the product that costs more than $200 pause the video, do that with an exist claws and find all suppliers with the product that costs more than $200. Okay, let's go with product name from products where exists and sub query we're gonna do we need company name from suppliers and then we're gonna select product I d from products where products dot supplier I d equal to suppliers Supplier I D. And And let's look here over in our products category, check out our columns. So it's unit price greater than $200 and we have one a company that has products that have a higher unit price in $200. All right, now we want to find all our suppliers that don't have an order in December of 1996. So pause the video tryto come up with the query that uses exist. In this case, it will be not exist. And all suppliers that don't have an order in December of 96. Okay, this one's a little more complicated, but it does start off with from suppliers, and we're gonna do exist now. I'm gonna do a series of joins here. So we're gonna go from products. We're gonna have to join that to order details, and it's gonna be joined with the product I d. And then to get the day, we're also gonna have to join, too, Borders. That's gonna be order i d. Now we we relate the two through supplier i d. So it's products dot supplier I d equal to suppliers Die supplier I d. And instead of the price we're looking for order day in its in December if I was to put this on another line and make it easier to read And it's between between december So the 1996 12 1 in 1996 12 31 and well, to exist first, which wow a product I d. So we need to do products, so we give it a little more structure. So these are the companies that did have orders. Now we want to win over ones that did not. So there we go. These companies sold no products. Christmas of 1996. All right. Next we're gonna do some queries using any and all see them 13. Subquery Using ANY and ALL: art. In this lecture, we're gonna cover doing sub queries using any and all operators. So the basic syntax of any is you're going to select column names from a table and it's where and it's any column you use an operator, and that could be equal, greater, greater than or equal less than less than or equal or not equal. And then any. And then you select of value that that operators gonna be compared to. And if any of the ones that come back are valid and it selects the record. Now let's show you with an easy example Here, we're gonna find our customers who have ordered one product that with more than 50 items on a single product. Okay, so let's start clear this out. So we're gonna select company name from customers where customer I d. Is equal to any select customer I d. From We start with Borders, but we have to join it to order details. Course we use order. I d there, Right. This has to have a underscore. Tell stop order i d. Where order details is quantity. So where quantity is greater than 50 all right. And we have 25 companies that have ordered something with a quantity greater than 50. Now it's your turn. Find all the suppliers that have had an order with exactly one idol. So pause the video and come back and I'll show you my solution. Okay? So instead of customers is going to be suppliers and it's going to be supplier I d equal. And now we're gonna have to do some joints here. So we're gonna do supplier I d. From products joined to quarter details. He tells product I d. And then the conditional is where quantity be cool one. And so we've had 11 companies that have had a single item order in a given order detail. All right, now all is the exact same syntax you replace any with all. And what this will do is it will Onley pick the records where all of them match. So, as an example here, we're gonna find our suppliers which had order amounts that were higher than the average of all the products. So in order to do this, we're going to select the product main products, and we're gonna join it to order details. One tells the product i d equal to products dot product i d. Where. Details. You have to give it the table name because there's two fields called Christ one in products and one in order details, times quantity. So that gives you the amount spent is greater than it's gonna be the average of this same quantity. Here, someone cut and paste this to save typing. So it's the average of that from order details. You know, we're gonna group it all right, Product ID's. So what this says is that we want every product name where it has orders that are greater than the average order size of all the products. So let's see how many products we have that fit that bill. We have 16 records now, if we want distinct, we go with distinct here. And thats three products which had 16 orders that were higher than the average of all products in the records. Now it's your turn, and we're gonna find all the distinct customers that ordered Maurin one item than the average order amount per items of all customers. Now, this is a pretty hard query, so don't get discouraged. I'm gonna show you how to do it. But pause the video using the previous one as a templates. See if you can work through this and if not, come back and I'll show you how to do it. Here we go. So we want distinct It's gonna be company name from. And first we're going to join, then toe orders when customers died. Customer I d equals borders. The order i d. They were to join order details, but instead of product ideas going to be order, I'd because we're coming at it from the other direction and where and again, it's this unit price times quantity is greater than all. It's gonna be the average of that unit price and quantity from order details. But instead of product, I d. We're gonna go the other way. So we're gonna join orders on it's the same. Is this up here? And we're gonna group by customer idea. Here we go equal to Okay, that makes sense. Gotta switch it over. Company in his company name running again. And here we go. We have 53 customers that have an order that's greater than the average order of all the customers put together. All right, we're gonna go to something a lot easier next one. It's using the in clause with a sub query see in the next video. 14. IN Using Subquery: video we're gonna cover using in with the sub queer. So in this, you have your select from and then where? Column in. Instead of listing out the values, you're going to select value. So the values that you select have to match the column that's in your in statement. But if you do that, then you can dynamically build the list versus statically, typing them all in. So let me give you an example here. We're gonna find our customers that are in the same countries as our suppliers are in. All right, so let's go over here. So we want to select company name from customers where a country is in. And then instead of listing them out here, we're going to select country from suppliers. All right, let's run that query. We have 69 customers that are on the same countries. Are suppliers are now it's your turn. Find all the suppliers that are in the same city as a customer so very similar to the last one. Cause the video and come back. Okay, so we're gonna list from suppliers instead of country a city. Let's run that query and we have five suppliers that are in the same city as our customers . Now, in the next video, we're gonna cover insert into so, you know 15. 38 INSERT INTO: okay, in this lecture we're gonna cover insert into. Up until now, we've only selected and looked at records that exist in this section. We're going to start talking about how to alter the records in the database, and we're starting with putting a new record in a table. So the basic sin taxes insert into you name the table and then improve the seas. You list the columns that you're gonna have values for that his values and in parentheses, the actual values to insert into the table. So let's go with an example here, we're gonna create a new order for our customer. So first of all, let's take a look at customers here or the company name there. And then if we go down toe orders, since we're gonna create a table a record for this table, we need toe. Have something for all of these field. So I'm gonna initially I'm gonna First of all, look up what this person has ordered already. So we're going to select blank from orders where customer I d equal to inept. All right, And this is gonna give us what I'm mainly looking for is where they're shipping it to so keeping this in mind, it looks like everything they've ordered has been shipped there. So I'm gonna blow here. I'm going to start creating my insert into porters, and then we need the list here. The fields. Now, normally, your order I d would be an auto increment field, so let's try it without specifying order. I d yet. So we're gonna go customer I d employees. I d order date. And I'm just reading down the list here of the fields that are in this table required day ship, date ship. The, uh, afraid Phil name should address ship City. I'm not gonna do region because I think all the regions have been Also, we'll leave that one off, Phil Postal code and then ship country. Okay, so those are all the fields now, we're gonna do our values. And customer, i d. Is the net an employee? I d. It looks like I'm just gonna put an employee number four in the order date. Okay, So, order date. I'm gonna do 2017. So we're restarting the company since all the orders were back in the nineties and they ordered on the 16th are required date is 2017 19 Dash 30. Now, in this case, I'm gonna leave ship date off. Since we haven't shipped this, we're gonna make it and all. And then we're gonna ship the, uh, Let's go with number three now. Afraid I'm just gonna put in $42.50 then on ship name. I'm just gonna copy where they've been shipping this already. And the same with the address now, because they've got a apostrophe in here. You have to escape that by doing a double apostrophe like that so that you haven't fitted already. Then our ships city is going to be Prem's, and I believe that's in France. But let's look here it is. But our postal code is 5100 and then the country is France. So here we g o we're gonna try this right now. We're missing a comma here, So let's get the common and and it says right here that our day is out of Oh, because it's not 19 months. There are 19 months in there. All right, so now what? I noticed here when I was building this is that order I d can't be No. So that means they have not set it up as an auto increments. So before we could do this, we need to see what the current Max order I d. Is from the table orders a few high white something in your window here and run it. It will just run that statement. So it shows that right now our Max order is 11,000 and 77. So we're going to put in order i d. And then we're gonna put that here. But of course we have to. He is stuck. We have to put a comma. But then we want to increase it to 78. Let's run that statement. I'm gonna get rid of this. Select up here and there we go. We have now inserted into orders our first new. Now it's your turn. So you're gonna insert an order detail for that order that we just created, and we want to make it a quantity of 20. And we wanted to be case so Cabrera ias you have to look up the product idea to see what product idea that is, and a price of $14. So pause the video and come back and I'll show you. Okay, so we're going to go here just writing down the order i d. So, first of all, let's select Oh, from products. We're product name. And I'm just gonna do Why? Cause I can't remember the full do you are there. Okay, so we have one product. A case. So Correa's product i d of 11. So then we want to come over here and let's look a order details. So we're going to insert into order details, and we need an order. I d. So I'm just kind of going down the list here and putting them all in my statement product i d unit price, quantity and discount. And then we go with values. Their order was 11 078 Our product I d. Was 11 so we got a unit price off $14 Quantity of 20 and no discount. There we go. It's run that and bam, We successfully inserted a record for order details for that. Right In the next lecture, we're gonna cover update, see them 16. UPDATE: this lecture. We're gonna cover update, and this allows you do change existing records. So it's update the table name that you're going to do it in, then set and column equal value so you can update one or more columns as many as you want. The important thing is to make sure you have aware, cause if you run this without where which I have done, you will update every record in your database, which is probably not what you wanted to do. A lot of times I'll start right by writing a select statement to make sure that I'm selecting the right records and then switch it to an update statement after I know the where column is correct. OK, so we're gonna update the order that we just created. They came back and they said, Man, we don't need that on the 30th. We've gotta have it faster. So we needed on the 20th 0 by the way, the shipping cost for that's gonna increase to $50 so that we get it to you faster. So let's go take a look at how to do that. So, first of all, we need the order. I d which was 11 0 78 So we're gonna update orders and we're gonna set date. What is this year? 2017 09 20. And we're also going to update the freight to $50 Now, Most importantly, put in a wear order rd equal 11 0 78 There we go. And it tells you down here in the bottom, that you did update one record. Now it's your turn. So we're gonna also update the order details that we created last video lecture. So they want 40 instead of 20. And because of that, we're gonna give them a discount of 5%. So 50.5 discount now order details does not have on I d feel, which is really a bad design. So you have to be careful in the where clause to make sure that you update the right order details. Since there's no order detail feel now, this case not is important because we only inserted one record, but in a real database, this would matter. So right here we're gonna do update order details and we know that we need to change. The quantity is now going to be 40 and the discount instead of zero is gonna be 5% or 50.5 And we've got order. I d is equal to 11. 78. Now, before we run this, let's just select blank from order details and this gets back to I'm just gonna highlight it. So just that part runs if you ran this without that, it would update every record in your database, which is not what you want. So we've got here some detail down here, and what I'm gonna do is because normally you would have more than one detail I'm also gonna make and product I d e equal 11. So there we go. So let's run that. And we successfully updated one record. Now, the next video, we're gonna close the loop, and we're gonna talk about deleting records 17. DELETE: all right. In this lecture, we're gonna cover delete. So the delete sin taxes delete from table name and wear condition. Now, the worst feeling in the world is when you run a delete without aware you delete every record in the table. I've done it before. It is terrible. Just be very careful with the lead. And always check to make sure that you have aware condition. So let's start by deleting the order detail that we put in last time. So they canceled their order. So let's delete the order detail to start cleaning up the database. So here we go. Now, first of all, we're going to select to make sure that we know which one were pulling from so select from order details. So let's run that. And sure enough, that order I d. 11 0 78 product I d 11 has one record. So we're going to delete from that notice. I started with the select so that I could double check. And then there we go. We deleted one record. Now it's your turn. Delete the order for the customer using that order idea. So delete the order record with order idea of 11,000 and 78. Pause the video and come back and I'll show you how I did it. Okay, so now we're gonna delete from orders. Where order I d is 11. 78. Now I'm going to just out of good habit. First, I'm gonna start by selecting Blank from orders, but the highlight it so that it doesn't run the delete. And, yes, that is the order we want. So we're gonna delete that record. Bam! We deleted one record. So that's it. Delete is pretty simple. As long as you remember the where clause Next, we're gonna go select into 18. SELECT INTO: all right. In this video, we're gonna cover select Into. So your basic syntax here is you're going to select some columns into a new table. So this syntax creates a new table from an old table and you can add aware condition. So let's jump into it. I'm gonna show you how to use this to create backups of tables. So let's back up all our suppliers in North America. So over here, we're gonna go select. They were gonna select everything we're gonna call it. It requires North America from suppliers where country and USA in Canada. I looked ahead of time, and that's the only two countries that we have and North America. So let's run that. All right. And it selected six records. Now, in order to see that over here, we're gonna have to refresh it. So if we go appear to public schema, we can do refresh. And if we look down here under suppliers, you're gonna see suppliers North America. So it created a table using these fields from supplier. Now, if we go on, we just select everything from just change this to from suppliers. America. Get rid of that. Run it And there we go. That's the six suppliers that they found that air in North America. Now it's your turn. Let's create a backup of your orders in the year 1997 and we're gonna create a new table. Orders underscore 1997. So pause the table, create the select into statement. Come back and I'll show you my solution. OK, so we're going to select all the fields and going appear toe orders into orders in 1997 from orders where border date is between 1997 January. The first in 1997 December 31st scored and run it, and it's selected 408 records to go into the new table. And again, if we go up here and we refresh, you will see that we now have new orders. 1997 able created, right, And the next lecture we're gonna cover insert into select 19. INSERT INTO SELECT: all right. In this lecture, we're gonna cover, insert into using select and your basic sin taxes you insert into table two. So this doesn't create a table. You have to have an existing one. You list the columns, and then you select those columns from table one, where the conditions met. Enough. All the fields match. You leave out this column list and you select ass trick from table one where condition. So we're gonna go back and we're gonna use the tables that we created in the last one, and we're gonna add some more records to him, even though it's, you know, not correct, because these aren't from North America. But we're gonna added to this table to show the syntax here. So we're gonna add all our suppliers in Brazil and Argentina to suppliers North America. So here we go. We're going to insert into suppliers North America because we're using all the fields we don't have toe list them. We're gonna do select from Original Suppliers table, and then we're gonna add country in Argentina, in Brazil. Okay, so it inserted one record. So we had one supplier that was from either of those countries Now it's your turn. We're gonna do the same thing for the orders. 1997 table. I want you to add all the orders from December 2016 to that table. I'm gonna pause the video, come back and I'll show you how I did it. Okay, so we're gonna insert into orders 1997. We're selecting from orders, and we want the border date to be between no parentheses. 1996. 12 was December the first and December the 31st. Let's run that. 31 new records were added to that table. So that's your insert into using select next step. We're going to switch gears and start showing how to create tables and databases and indexes, and we're gonna start with create database, see them. 20. CREATE DATABASE: Okay, This lecture, we're gonna cover create database. The syntax is very straightforward. It's just create database, and then you give it the database name, so we're gonna create North Wind to hear. All right, let's go over here. So it's just simply create database North Wind to gonna run that it looks a little differently, but it says it created it successfully. So if we go back over here to our schema, actually, it's up here on databases. We have to refresh by right clicking and you'll see, Here's our North wind to database that was created are not your turn. Create a database called my DB five. Pause a video, run this statement, come back and I'll show you my solution. Okay, so it's create database, my DB five. Let's hit run again. We have to refresh this, but we end up with my DB five as a new database. Now I'm gonna show you how to do it with the gooey, which we did initially when we set this up. But you right, click, you go create database, and I'm just gonna do my cool TV. We're going to save it. I'm gonna leave all the defaults There's a lot of additional stuff in here that will cover later. But right now we're just gonna do my cool db hit save. And there you go. It added my cool db. Right. Next lecture. We're gonna cover the opposite. Drop a database, see, then. 21. DROP DATABASE: in this lecture, we're gonna cover dropping a database. The syntax is very simple. It's dropped database and then the name of the database. Be aware, once you've done this, unless you've made a back up, you're gonna lose all the information in there. So we're gonna delete the North Wind to database that we created in the previous video. So here we go. We're gonna do drop database, and we're gonna do North went into Let's run that the five. There we go. Now, if we refresh this, you'll see that north wind to is no longer with us. OK, it's your turn. Drop the database. My db five. Pause the video. Drop it. Come back. I'll show you the solution. OK, it's the same statement. It's just we're going to do my db five. Let's run that again. We're fresh over here, and it's gone. Now let's do this using the gooey with this one. So you right click on it. And right here you have delete drop and it comes up and it gives you a chance to say that you're making a mistake. We're gonna delete it right here, and bam, we're back down to our original North wind and post Greste databases. They're our next step is going to be create table. See you then. 22. CREATE TABLE: in this lecture, we're gonna cover, create table. So we're gonna cover the basic syntax. You can add constraints and indexes and default values. But right now we're just gonna focus on talking about the fields and the data types. So the basic syntax is create table. You have your table name and then in parentheses you have a list off column names followed by the data type separated by comments. Now let's go over your energy data types, which is you have. Let's go over your basic energy data types. So you have small in it imager and Big Aunt. And the big difference here is the number the size of it in the range. So for small it you go from negative 7 32,068 to positive for imager. You have a much bigger number. It's in the let's see here billions and then big get is on enormous number. So if you have something that requires more than big in it, you'd have to go to float. Now there's another set of data types. They're exactly the same as your manager types except a called cereal and what these air for If you've got an I d filled. And every time you insert a record, they will automatically increment it so that you don't have to look it up to see Okay, what's the biggest number I've already put in? It will automatically just add the next one to it. So small cereal goes from 1 to 30 17 2067. Then you have serial, which will get you up to the two billion and then big cereal, which is the largest available one in the system. Now you've got another set of them which allow you to tell it. You know, how much precision do you want in these data types and its decimal in numeric and decimal, you put in total digits and the digits in the fractional parts was the port after your point. So if it's 2.31 two would be the normal part, and then 31 would be your digits in your fraction. So you've got to that you can use for this and they're essentially the same. It's decimal in numeric. Now you have floating point, and this is not precise numbers. It's in exact numbers, and you've got really, which is four bites, which gives you six decimal Ah, digits, precision. And then you've got double precision, which is eight bytes, and it goes up to 15 decimal digits. Now you've got character data types, and so you've got var char, which is it has a variable number of character stored in the database. You've got char, which even if you don't use up all the space in the database, it will use up exactly in digits every time are in character spaces every time you put it in. And then you've got a final type called tax, which is variable, unlimited linked data that you can put in Now you've got date time data types. Time stamp has both date and time, and it goes from 47 13 for the common error, all the way up to 294,276 a. D. A very long time. The future. I don't think I'll be worried about the fact that it may roll over at that point now, date or for dates on Lee, it has a similar ranges timestamp, but you don't have the time part of it. Now, with time, you've got, uh, starting with 000 up to 24 minutes and seconds. Interval is something that allows you to record differences between times and it can store a tremendous difference. Ah, here, with 178 million years negative and positive between the two time stamps. Now you've got one bullying type which is just bullion, and it's true or false. Now there are other data types. I put a link to the documentation in the resource section of these videos and it, you know, go through there. I've listed the most common ones, you know, they've got other data types, including, you know, I P addresses and all sorts of things. If you're curious, go take a look at them. You may run into them depending on the kind of data that you're in a store in your project . Now, let's go over here and look at the gooey, and we're going to see how tables are built in the goop. So if you're curious about how a particular table got built, you could always right click on it. And then down here under script, you can look at the create script and it will give you the statement that created that table, including the data types and what not. And so this is, you know, is you're learning about this if you're curious. Okay. How did they create a particular table? You can always right click and get a view of the script. Now, we're going to start by creating a table, and this table is gonna be for our newsletter subscribers. So we're gonna call it subscribers we're gonna do first name, last name email, sign update frequently in a bullion on is a customer. All right, so let's start with create table. Call it subscribers. We've got our parentheses, and then we've got first name, and I'm gonna make it a bar chart. 200 and then comma. Last name. Similar bar chart 200. Go email. This one. We're gonna make 250 characters. We've got Sign up date, which is gonna be our time stamp. Then we've got frequency. So this is how often they wanted. I'm gonna make this manager and then finally is customer is going to be a brilliant All right, let's run this. And it successfully worked. Now it won't show up in the gooey until you refresh your screen. Once you've refreshed it, then you can look over here. You'll see that we have subscribers showing up. And if we go down, we'll see The columns have all gotten put in there, and now it's your turn. Let's create a table for returns. We want an I D field, which would be returned. I d customer I d. Since we're gonna link it to the customer, you'll need to go look and see. How is the customer I d defined in the customer table. Date returned product. I d quantity in order I d. So pause the video, See if you can come up with your create table statement, Come back and I'll show you how I didn't mind. Okay, so we're gonna go returns. I'm gonna delete thes fields here. All right? We're gonna do return. I d. We're gonna make that a serial. What we're gonna do, customer I d. It's gonna be char five day returned every time stand. We've got product. I d which is it? Quantity, which is small. And and then finally order i d. Which is also gonna be manager. Now if you're wondering how I found Custom right, Ito be five. I actually opened up. Let's open up another query here. And I just did select customer I d. From customers. And they all were five characters. Exactly five characters for all of them. And so then I knew that when I created it over here, I needed to make it a char five so I could link the two. All right, let's run, and we're gonna refresh our schema. And then we look down here and we have a new returns table with her columns. All right, In the next video, we're gonna cover altar table, which would be how you change tables after their created. See that? 23. ALTER TABLE Part One: All right, this lecture, we're gonna cover altar table. We're gonna break this. It's pretty complex because there's a lot of things you can change about a table, and so we're gonna break it down into different scenarios. So scenario one is going to be rename a field. So the basics in taxes, altar table, table name, and then you re name you put in your old column name, too, and then followed by the new column name. All right, so we're gonna rename a field in the subscribers table, and we're gonna change. First name to first. Underscore Name. Case Ellis. One of this here. It's going to be altar table and we're doing subscribers gonna rename first, thanks to cool First Underscore name. There we go. That your term on the returns table? Rename Return date to return. Underscore date cause the video come back and I'll show you the answer. Okay, now on returns. So we're gonna do altar table returns. We're gonna do date returned to date. Underscore. I returned. Here we go Now, in order to see that it worked, you have to run. Refresh when you're doing and you'll see that. Yes, it did. update the field name. Now, you could do this in the gooey also. So let's take a look here. I'm gonna find Sign update. You can right click Goto Properties and then in here. You can fix it by doing underscore and then hit safe. All right, now, scenario to we want to rename the whole table. And the syntax for that is you alter table old name rename too new night. So let's rename subscribers to email subscribers. So we're gonna go altar table subscribers, and then we're gonna rename that too. Email subscribers hit, Run! And if we go up here and refresh our view, we'll see Viola. We now have email subscribers that your turn remain your table from returns to bad orders. Pause the video. Come back. I don't show you the answer. Okay, so we're gonna rename returns. Too bad borders. And if we were fresh, you'll see bad orders instead of returns. Now, you could do this in the gooey as well. So I would follow along with this because we're gonna name it back to returns. So go to bad orders. After you've refreshed it, right, click goto properties, and then you can change this back to returns. Here. It's safe. And there we go. Now we're gonna goto altar table, part two in the next lecture. See there. 24. ALTER TABLE Part Two: are in this lecture. We're gonna cover altar table, part two. So the next scenario that we're gonna look at is at a field and the sin taxes, altar table, table name, ad column and data type. So let's add a field to our email subscribers table and let's make it last underscore Visit underscored Date is gonna be We want dating times. It'll be time stamped. So let's take a look at that. All right, So we're gonna alter e mail subscribers, and we're gonna said every name we're adding last visit gate, there's gonna be a time stamp field. It's run it. And then if we go over here and refresher columns, we'll see. Last visit dates showed up. Not your turn on the returns table at a text field called Reason. Calls the video. Go give it a shot and I'll show you my solution. Okay. On the returns table, we're going to add reason. It's gonna be text. So it's unstructured, an unlimited amount of tax for a reason. Run it. And there you go. We take a look. There we go that we now have reason down here. Now you can also use the gooey tool. You can right click, create column, and then you give it a name. I'm gonna make one back order, and then you have to click on the definition and pick a type I'm gonna do bullion for true false click save. All right, Scenario four is deleted. Field in the syntax. Here's altar table, table name, drop column, and then the column name. So let's remove the field that we just put in their last visit. Date from email subscribes. Okay, so we're gonna go, you know, subscribers. And this is going to be drop. Call him Blast visit date. You run it over here. Refresh. And that column is now gone. All right. Now, the last way you can do this is using the PG admin gooey. We're gonna go here and we're gonna Well, if we were fresh, we'll see that reason is gone, and I'm going to right click on this field. Back order and hit. Delete drop. It'll last me to confirm, and that'll remove that field from the table. Right In the next lecture, we're gonna cover altar Table Park three c, then 25. ALTER TABLE Part 3: okay. And this lecture, we're gonna cover altar table, part three. So the next scenario will deal with is changing the data. Type the sin taxes, altar table, table name. You do Alter column column name set data type to the new data type that you want it to be. So now we're gonna change. As an example, the email filled and email subscribers two of our char with a length of 2 25 So here we go . Altar table email subscribers, and it's falter. Call him and the column is Let's take a look here. Email. We were set data type. I'm going to go hard Shar to 25. All right, let's run that. And it was successful. Right now it's your turn on the returns table. Change the quantity field to small end. All right, stop the video. Come up with this statement and then come back and I'll show you the answer. Okay, so we're gonna go altar table. I returned altar table quantity. I want to set the data type two small, and that was successful right now. You could do the same thing with the PG admin. Gooey. So let's pick a filled here doesn't really matter Which one. Let's go with order, i d. And if you right click on it, you can see properties. If you go to the data definition, you could change this right here by picking new data type big and small and whatever you want to do that he had saved. Cancel here. Now what? We're not covering or constraints and triggers. There's a lot of altar table statements for dealing with that. We're gonna cover that later. We'll get into constraints in defaults and indexes and everything else. Right? Next step. We're going to create index. See there. 26. CREATE INDEX: Okay, This video, we're gonna cover create index the basic sin taxes create index unit, given a name index name on a specific table and then in parentheses. You list the columns that you wanted to be, and it can be one or more columns, and you could also create a unique index, which is the second syntax here. And then what that means is that it will only allow values into the table that air unique. So if there's another record with that value that will reject the data and not let it enter the table now, indexes air really, really important when it comes to tables. If your searches start slowing down, usually it's because you're searching with aware clause. That's on a field that doesn't have an index. I found that this typically happens around 10,000 or more records per field. If you add index sicken, search can go from 10 seconds to a fraction of a second. Now let's create our first index, and we're gonna do it on the email field of email subscribers table, and we're gonna make it a unique index. So here we go. So we're gonna create index now, typically in the naming scheme. I do a combination of field plus table name email, and then I would do email subscribers on email subscribers and then in parentheses will do email. It is supposed to be unique, index. So what do you remember that? Add that up here is Well, let's run it. Uh, we didn't It's gotta be chloral running again. There we go. Now you can tell that you haven't index because there is an index is down here, So if we refresh under our indexes will see email email subscribers. Now it's your turn. So create a single index with two fields a customer i d. In order I d on your returns table. Pause the video and come back when you've gotten it. Okay, so it's not a unique index, and it's going to be on customer i d. Border Rd returns and the table is returns. But now it is two fields the customer. I'd be in the order, i d Let's run that. Then again, if we go look, one returns after we refresh it, you'll see that we have this index here. Now you could do the same thing using the gooey so Let's take a look at that. You can go down here and indexes, and you could do create a new index. Now you have to give it a name. And so let's see. I'm gonna do it. Date returned for returns, and then in definition. Now there's a plus down here, and you wanna hit? Plus, and then I'm gonna pick there. I'm gonna leave everything as the default will get into that in an advanced course that I'll do later. But if the defaults will work just fine table space. So you've also gotta pick your default table space. There we go. Said save and it creates it right there. Now, the next video is going to be drop index, which is the flip side of create index. See there. 27. DROP INDEX: all right. In this lecture, we're gonna cover drop index on the drop index. Syntax is very straightforward. It's just drop index and index name. If you don't remember the index name, you can get it by looking in the gooey at indexes. It's right here, and you can refresh to make sure you see all of them. But that gives you a list of the names because sometimes it's hard to remember, although if you'll use a naming scheme where its field underscore table or if it's more than one field field underscore field in the score table. That is pretty easy to remember how you name them, right? So we're gonna start with an example, and we're gonna drop the email. Email subscribers index on the email subscribers table So here we go. Drop index, you know email subscribers. Let's run it. There we go. Successfully dropped it. Now it's your turn. Drop your customer. I d order I d returns Index on your returns table. Pause the video, pull the command together, Come back and I'll show you my example. Okay, so in this one, we're gonna do customer. I d order I d returns. It hit run if we go over here and we were fresh, you'll see that. Yes, it did, indeed. Drop it now there is another way to do this which is through the gooey. And you could just right click on it and hit, Delete, drop and hit. OK, and that will do the same thing. Right? Next lecture. We're gonna cover drop table. 28. 51 DROP TABLE: Okay. This lecture, we're in a cover drop table. This is the opposite of create table. Syntax is very straightforward. It's dropped table, table name. All right, so let's go with an example of getting rid of our email subscribers table. So instead of drop index, it's gonna be dropped. Table E mail subscribers run it. It was successful. If we refresher view over here, you'll see that. No, look, no more email subscribers. Now it's your turn. Drop the returns table, pause the video until you get it done. Come back and I'll show you my solution. Okay, Drop table returns. Let's hit. Run! There we go. We're fresh. And sure enough, it's gone. Now, you could do this in the gulyas. Well, I'm gonna take this one here. Right click, delete drop. And I'm gonna cancel here. But if you wanted to delete it, you would hit. Okay, We're going to get into the world of constraints, and we're gonna start with not know, Next time I see them 29. NOT NULL Constraint: right. This lecture is gonna be on the not null constraint. So first, let's just talk about constraints. In general, a big part of having a good database system is controlling the data that goes into the tables. You start with your data type and it's the most basic kind of control where you're saying I want numbers. I want text. I only want fields to be this big, but beyond that, you then need to get finer control around it. And this is where constraints come into play. One of the reasons you need this is a lot of times the database administrator. You can't control the code that gets written and other parts of the company. But if you put on constraints, then you can reject inundated that doesn't match it. And that will prevent a lot of bad data getting in there, which could take weeks or months before anyone notices and causes a really big mess. All right, so what are types of constraints you've got? Not know which says that a field must have a value you need. The value cannot already be in the table. Primary key, which is a combination of the first to. It doesn't allow nose, and it makes sure that each insertion is unique. And in that way, if you have that primary key, then there's only one record at most in the database for any given value for primary key foreign key checks to see if that value exist in another table. So, for instance, you know, in our returns table, we had customer I D, which is actually the primary key of another table, and so we would make that a foreign key. Then there's the check constraint, which allows you to put logic in there, to say this has to be certain values or it has to be greater than this or less than that. And it won't allow data that doesn't meet that check constraint. And then there's a default constraint which says, If no values provided, this is what I'm gonna put in there now we're gonna start with, not know. And here's the syntax. When you're creating a table, you would say column data type and then not know after the data type, so we're going to start by creating a table. Now we're gonna have two tables. We're gonna drop him and recreate um, adding more, more constraints. As we go along, we're gonna call this one practices, and we're gonna have one field called Practice I D. And we don't want that field to be. No. So let's go check it out. So here we go. We're gonna create table practices. I'm gonna start with just one filled the practice. I d feel that's gonna be a manager and then not know. So let's run that. All right? I've been practicing doing this ahead of time, so I need to drop it first. Here we go. All right, So now we've got our practices table, not your turn. I want you to drop that table that we just created. So we've got the drop table. I put a semi colon, so it runs a separate statement and then recreate it. But with practice ideas, an energy er and practice field, which is of Arch are 50 and both of them not being no. Pause the video and come back when you've got it done. So we already had a drop table up here. We're in Atacama practice field, and this is gonna be march are 50 and it is also not know execute now. Sometimes you want to do this after the fields are the table's already been created. You realize that you need additional constraints. So here's the syntax to do it. You alter table, give the table name Alter column column name and then set. Not know, Let's do an example here. Let's take unit price field in the products table and had there not No constraint. OK, so what I'm gonna do here is I'm gonna open up another window and I'm gonna go alter table products. It's gonna be alter unit, and we're gonna make it set. Not know. Okay, let's run this guy and it was successful. So now we've added, Do you not know constraint to the unit price in the products table? Nurture, turn. Take the last name field in your employees table and make sure that it always has a value. Pause the video. Come back and I'll do it. Oh, my. Okay, so we've got employees and we want to make sure that the last name is set, not know, executed worked. Now, in the next lecture, we're gonna cover the unique constraint 30. UNIQUE Constraint: in this lecture, we're gonna cover the unique constraint. So the syntax is similar to not know. It's when you create the table, it's column data type and then add unique after the data type. So let's create table called practices, and we're gonna drop the old version and then we want practice idea. And we want that feel to be unique because there shouldn't be to practice ideas with the same number in there. And then we're gonna name it field name with var char 50. Okay, so we're back. We're here where we've created drop table practices, I've got create table practices and instead of not know, I want to add unique here. And then we're gonna do this in the same syntax. We do all the other ones without the underscore. Personally, I prefer the underscore, but North wind doesn't have it. There we go now create a table cult pets and this is your turn. So you want pet I d. That has an energy that unique. And then the name of the pet, which is gonna be var chart 25 it must be not. No. So stop the video, go create that table and come back and I'll show you my example. Okay, I'm gonna do this in the second window that we have got practices in one and pets and the other. I'm gonna make sure that pets isn't there. I'm gonna do drop table pets. You shouldn't have to have done this because you're creating it for the first time, We're going to create table pats, pet I d. Which we're gonna make an imager. It's gonna be a unique manager. And then we have name which we're gonna do far Char 25. No, nos so not know. There we go now. You could do this after the tables been created. It's altar table, table name. And then you've got add constraint. You give it a name, and then it's unique. And then the call. So let's add a unique constraint to the region Description field in the region table again . I don't want to mess up. Since we've got pets, open up a query tool here. You have as many of these open as you want. We're going to Baltar. Table Region. We're gonna add a constraint region description region. The in this region description it's running. It was successful. Now it's your turn. Make the company name field of your shippers. Make sure that that field is unique. Pause the video and come back. OK, so we've got shippers right here, and we're gonna add a constraint. Call it company name and it's gonna be unique. Born company today. Let's run it. There we go. Now, in the next lecture, we're gonna cover the primary key constraint. 31. Primary key constraint: OK, in this video, we're gonna cover the primary key constraint. Now, the syntax is similar to the other ones. It's column data type and then primary key. Now, primary key is equivalent toe having it be both unique and not know. And generally it's used for some sort of I d field where your incriminating it. So you have a unique key to identify every record in the technical to identify every record in the table. OK, so we're gonna go back and we're gonna create our practices table again. And this time we're gonna have practice. I d be a primary key and filled name. Be not know. Let's go over here. We're gonna go to our practices and currently the last time we did unique we're gonna change that and make it primary key right here. Let's run it. Okay, now I'm gonna show you the effect of that for practice. I d. So let's do on. Insert into practices and we're gonna do practice de infield. Thank you. And then the values. Let's start with trying to insert a no value there so we'll go. No, and they will call the field. Name something. Now, if you highlight it in here, you can run just this statement. It says right here. Can't do it. No value in practice. I d All right. So let's try putting no one in here and the first time we run. This is totally fine. I'm gonna name the field Something differently. Read. We're gonna leave the I d to be the same. Run it. And you see there again is going to say, Hey, can't do it. The unique constraint isn't valid. Now, if we change this to to and run insert, it will be just fine. Not your turn. Go back to the pets table. Dropped the old one unless create pets with pet I d. That's the primary key and will keep name to a bar chart 25 that can't have no values. Pause the video and come back after you've created that, and I'll show you my answer. Okay, so again, we're just changing this unique over the primary key, and we'll run it. And that was successful. And you contest in starting to see the effect that it has or what kind of data you can put in there. The altar table syntax. So if you wanna make something a primary key? After you created the field, you add primary key with a certain calm in your altar table table name. Now you condone Rappe it as well, by dropping the constraint and the syntax is, you have to know the name of the constraint. Now, by default, it calls it column underscore. Peaky. So we're going to do that on the practices table. So now if we go over here and let me refresh this to make sure that we're up to date and if we go to, let's find it here practices you can see under constraints it did create that default key right there. So let's drop it. So we're gonna drop world alter table practices and we're gonna drop the constraint and it's practices peaky Primary key. All right, let's run that. And let's refresh here and the constraints gone. Now, if we want to run the flip of that, so we're gonna alter table practices. So then it's and primary key and then in parentheses, we're going to do, uh, practices. Well, it will be practiced. I d. All right, let's run that. And if we refresh over here, you will see that we now have a constraining their practices. Primary key. Alright, your turn dropped the primary key for pets and then recreate Pause the video. And when you come back, I'll show you how I did it. Okay, so let's look at it pets to make sure that it's got the default key. But I'm sure that it does. Pets peaky. So it's alter table pets. Drop constraint. Pets underscore. Pinky, Let's run it and we'll refresh to make sure it's gone. And then we're gonna add it back with and primary Key. It's gonna be pets. I d I d. We go and it's back in. And again, if we refresh it over here, you'll see the keys been added again. Right In the next video, we're gonna cover the foreign key constraint. See them 32. 55 FOREIGN KEY constraint: foreign key constraints. So your basic syntax is a little different in that you do it at the end of the table after you defined all your columns. Then you come in with foreign key, the column name, and then it references a second table in a column name there. And what this does is it won't allow you to put in data in which that I d filled isn't also in the second table, so you won't have any orphan records. Okay, as an example here, we're going to drop practices and recreate it and add in ah, foreign key constraint that the employee I d references the employees table. Let's take a look here. So we've got employee I D. And then you type in Foreign Key and its employee I D. And then we go references, employees, second table employees idea. Let's run that. I forgot to make it plural. Here we go. Now let's take a look at our practices table right here. So first of all, let's refresh it so that has got the latest oh information. And let's look at our and strength here in here. We've got this employee i d. Foreign key constraint that was put in there. Right now it's your turn. So drop the old pets table unless create pets table again. And this time, the customer I d is gonna be a foreign key that references the customer's table. So pause the video and come back and I'll show you my answer. All right, so here we've got the drop table pets we re created and then I'm gonna add are foreign key here, which is gonna be the customer i d field. And this is going to it. References, customers. It's the customer idea. Let's run this guy And there we go. And that's how you add a foreign key. Now you can also do this to an existing table or get rid of one in existing table in the sin taxes, altar table, table name. You add the constraint, you give it a name and it's the foreign key column references table to call him so similar syntax. It's just this altar table. Add constraint and then to drop it, you have altar table, table name, drop, constraint, constraint, name, and you can find the constraint in the gooey there under constraints. Okay, so let's drop and we re create our foreign key constraint for the practices table. All right, so we're gonna go over here to this window, and first we're going to alter table practices. They were gonna drop constraint, and then we could see the name over here and our column, which it creates. Ah, table name, field name, F Key. Because that was the one created by default. But we're gonna drop it here, so well, Practices, Boy, I d f key. And let's just highlight that. So just that runs and it was successful. Now let's re fresh here. And sure enough, that could strikes gone. So now we're gonna do the opposite of it. We're gonna alter the table practices we're gonna add, and straight anybody use the default constraint. That way it remains consistent. And it's the same syntax here, Foreign creamy. We I d. It references. Employees come toy ideas. So let's highlight this. Run it and then let's go over here and refresh. And we should see that constraint come back and it does. All right, now it's your turn. Practice the same thing on the pets table, drop the foreign key and then recreate it. Pause the video. Go give it a shot. Come back and I'll show you my answer. Okay, here we are. So first, let's go get pets up. Unless look at the refresh it. All right. And it currently has that constraint. So we're gonna alter the table. Pets drop constraint. It's pets. Customer I d f. Let's try that out. Run it fresh. And sure enough, the key has been dropped. Now we're going to put it back on. So that's alter table. Pets had constraint, and we're gonna call it the same thing to make it consistent. There we go. And it's going to be foreign E customer, I d. References, customers, Customer idea. Let's highlight it. So we just run that one. Okay, well, let's go back here and make sure that it put it back in. There you go. Right In the next video, we're gonna talk about adding a check constraint 33. CHECK Constraint: Okay, this lecture, we're gonna cover our check constraint. So there two ways to add a constraint. One is at the column level, the field level, and that's you do your column, your data type, and then you constraint, given a name check and then in parentheses, the condition that it's going to check on to see if it's valid before it lets the data into the table. Or you could do it at the bottom. After all of your field definitions, you can have additional constraints on separate lines, and it's still the same constraint. Name check condition. So let's jump into it. Now. We're gonna go back to our practices table. We're going to drop it and re add it with a new field cost, which is an imager, and we're gonna make sure that that imager is between zero and 1000. All right, let's jump to it right here. We are on practices, we're gonna drop it, and then we're gonna created and I've left in all the definitions that we've had up to this point and they do cost imager. And then it's constraint. We'll call it practices cost. And then it's check. And then we're gonna do. Cost is going to be greater than zero. Greater than or equal to zero, and cost is less than or equal 2000. Right? Let's run that. We gotta have a comet. The end. Put that in. Here we go. Now, if we were to insert a record here, so I'm gonna show you. Insert practices. Do practice. I d practice year old employees are the it could cost. And then let's go with values. So we'll do one for this practice field was do some some name and then here will go employees idea. I'm just going to use employees one. Let's say we tried to put in a cost that was too high. Let's run that statement insert into you have to get the rights and tax. Okay, let's run it again. All right, so right here. You see you get an air back that it says it violates that check constraint practices cost. And so this is a great way, you know, programmer can then insert data while you're not looking. Your database will rejected and give them the air so they can go fix the code. It's your turn. So let's go back to the pets table, and we're gonna recreate it. But this time we're gonna add a weight energy field, and it has to be greater than zero. So it can't be zero has we greater than zero in less than 200. So here's our pets. Now we keep all the constraints that we put in so far, but we're gonna add the weight field. It's an imager, and we're gonna do constraint, and we'll give it a name of pets. Wait, we will do check and wait greater than zero and wait less than 200 and we have to put a comedy. And here. All right, let's run it. There we go. Now you can add these retroactively or delete them, Rector. Actively the sin taxes, altar table. And it's add constraint checks, condition and then on the to delete it, it's just dropped constraint and then constraint name. So let's add a constraint to the orders table. That freight must be more than zero. All right, so we're gonna go over here and we're gonna go to orders. It's ad constraint. That's gonna be you name. It orders a freight, and it is check great. Greater than zero. And now, if you go over to your orders table, you can refresher constraints and you'll see this orders. Freight constrain has been added. Not your turn at a check constraint. That unit pricing your products table must be a positive number. Stop the video and come back when you've done it, not show you my solution. So we're gonna goto products and it's products unit price where unit price has to be greater than zero. Next video, we're gonna cover the default constraint. 34. DEFAULT Constraint: okay, This lecture, we're gonna cover the default constraint. So the syntax similar to some of the others, is column data type than default in the value or function. Now you can give a default value that's a fixed value. Or you could use any of the built in database functions to generate something like the current time stamp. So let's go back and create practices all over again. This time, we're gonna add a cost imager, which must be between zero and 1000. But it has a default of 50. So we're gonna add the default to 50 to the definition. So here we go. We've got our check and strain on here, and before that, we're gonna do default. 50. Let's run it. And there you go. Now we have a default to 50 on our cost. If nothing else is put in there, right, it's your turn. Let's go back to the pets table, then on your weight imager, which has to be between zero and 200 added default of five. So pause the video and come back and I'll show you my solution. So here we go. We've got the weight manager. I want to add here. Default five. There we go. Now, if you've already got a table, you've got an altar syntax, which is alter column and then set default in the value. And then if you have a default that you want to get rid off, it's alter column and then just drop default by itself. So let's go to the orders table and change shipped via to a default of one. So let's go over here. So we're gonna alter orders we're gonna alter. Just get rid of that right there. We're gonna alter Call him. Shipped via it's set default. They weren't added toe one here. There we go. It's your term. Set the default value of reorder level in the products table toe five. Pause the video. Set that up with an altar table statement and I'll show you my answer. Okay, so we're gonna goto products, and this time it's re order level and we're gonna set the default. 25 Let's run that. And now we have a default of five on that field 35. Conclusion: joining me for this second post GREss sequel course. Let's wrap up. We've covered. So we started by joining multiple tables together. That way you can pull data from all across the database. We follow this by group by for aggregate functions. Then we showed you how to use the having clause to filter that aggregate data. Finally, we looked at sub queries where it's a query within a query. After that, we covered how to change the data in the database by using insert, update and delete statements. And for the final part of the course, we showed you how to build tables and also included how to add data constraints for those tables so that you keep your data nice and clean. I hope you've enjoyed it. There's still more to learn about Sequel, and hopefully we'll be following up with the further courses to show you more sophisticated ways to become better at post Greste sequel