PostgreSQL commands - SQL Tutorial

- [Instructor] Let's review some of the fundamentals of using Postgres. If you're familiar with SQL in general most of this will be similar information but we will note some differences in the syntax of Postgres. Postgres offers other commands in the query that are useful to know. We're going to go through them fairly quickly. First, we're going to look at the fetch statement. The fetch is similar to a limit clause, which in my experience is used more often, but limit is actually not considered SQL standard so Postgres allows both. So we're going to do something. So this is how we would do the limit statement. If we run this, we'll see we pulled from our customer's table and there are 10 rows. Now we're going to write the same thing using fetch. We also need to learn how to spell select. We get the exact same results, the first 10 rows of customers. Cool. Next we're going to look at intersect. So intersect returns data that is only available in both result sets. It's similar to an inner join. So let's look at how you would do the same dataset with an inner join. So we do a select distinct of let's just look at customer's ID and we're going to do an inner join. The inner is implied when you just write, join. Okay, we write that. We see we got 15 rows and these are all the customers in the orders table and in the customer's table. Now let's do this using intersect. So same thing. And the key to note is that we're not putting the distinct in front of the ID here. And that's because we don't need to. The intersect will basically do the distinct for us. It's only counting where the customer ID occurs once. So we check that out. We get the same 15 rows of the same customer IDs. So these two queries give you the exact same results. Now we're going to look at a full outer, which not every database offers. So a full outer join pulls all the data from both tables regardless of whether or not they're joined together. What that means is even though we're joining on we're going to use the same example from up here, customers and orders, who are joining on the customer ID even if they're customers with no orders or there are orders but no customers we're going to see both in our results set. So we're going to do a select star from our customers and we're going to do a full outer join. We need to put our (indistinct). Great. So this is from the customer's table and then we have the order table here. So we can see here where the order ID is null, those are customers with no orders. You can see here where we have orders but there are no customers. That's where we're seeing where the customer table is null where we have orders and a full outer join will bring all the data together regardless of whether or not it conjoined everything. Next we're going to look at checking constraints. So let's create a fake table and we're going to call this table Christmas products. We're going to put an ID for the product ID, call it a text. We're going to say it can't be null. We're going to have a price in there. It's going to be an energy integer, also not nul but we're going to add another constraint on top of the not nul which is we need to check that the price is greater than zero.. So effectively it has to be a real price. And we're going to add Christmas and we're going to make that Boolean to check that it is a Christmas special product. Cool, so let's create our table. And now we're going to check if this price constraint works. So we're going to try to insert some values into this table. Insert values into Christmas products to see if constraint works. Cool. So let's do insert into Christmas products. We want to put the values of the ID. We give it, A-B-C, price. You can see that's able to connect that we're filling in this table. So it's like highlighting it. It's put $23 and yes. All right, let's see who this inserts awesome. It ran. So we can check if values... So now we're going to check if the value's actually in there And we can see that value is in there. Awesome. Now we're going to add another value in the Christmas products and we're going to do insert into Christmas products, values, and we're going to do the same thing. Let's give a value of XYZ price. We're going to go with negative one. And yes. Now you see, it immediately returned an error because it fails the Christmas products price check which we had placed over here. So it won't allow us to insert this. The next thing we're going to check our Booleans. So you can already see we've added a Boolean up here to check if it's a Christmas product. And we were able to say yes, to confirm it is in our values. However, you can also, instead of saying yes or no, if you wanted to put in a different Boolean you can also use true or false. Some databases only take true or false. You can do yes or no here. Some you can do zero and one. There are different ways to look at it. So even though we do true, we are still able to add that in. And if we look inside our Christmas products table you can see that they'll both say true even though the original one we had inserted was with a yes. The last thing we're going to look at is cascade. And cascade just means you are able to drop everything that is dependent on the table. So if I'm dropping a table but there is a query or a function somewhere that's referencing it it will also drop that so that it doesn't create errors in the future. And we're just going to do a drop table. We're going to drop the Christmas products table and we just write cascade to make sure anything dependent on it will drop, which since we just built it there actually aren't that many dependencies. Cool. Now let's try to run this again, and the table doesn't exist and that's the end of our quick demonstration. No one's checked, there are a lot of other cool little tricks you can learn. You can check out the documentation on their website.