Coding Exercise in PostgreSQL

So let's see this put into practice. I'm going to do a little bit of live coding here. And then afterwards you're going to go through, technically there's an exercise that has you do the first couple of steps, but it's mainly just to open up the file and get connected to our database.

So I'm going to close this file because I don't need it. And I've got a prepared file for you where I've saved some code and we're going to go through and look at the code. So I don't expect you to remember all the code off the top of your head right away.

So I'm going to open up a prepared code file. I'm going to open up a file from our class files. And I've got these ordered in numerical order.

We're going to start with 1,1.0. Now there are solutions like answers for these. We're not going to open up the solutions file. We're opening up the regular file and the exercise will tell you which file to open up and everything.

So I'm just going to open this up. No need to follow along at this point yet. This is kind of how the class is going to go; there's going to be some warmups and reference at the top.

And I'm going to walk us through understanding how all this code works. And then down below, there's going to be some challenges, the exercises, and those are going to be where you use what you learned up above. We're going to give you a challenge to say, do this.

And you're going to use what you learned up above to answer this question so that you can see, do I understand this? Can I put the code together myself? Up here I'm giving you the code so that you can run it, and you don't have to remember it off the top of your head. But let's say I start to type in this. And notice I'm not getting any code hinting or anything.

No code hinting. That's kind of weird. If I try to run it, it's going to say no active connection.

So what's happening here is over here on the left, you are looking at a server, but here I open up a file and on this computer, it does not know what this file is associated with in terms of a database. So let's say you were connected to multiple databases over here. Maybe there was a live database and maybe there was a testing database.

Sometimes when we're querying, we don't want to test on the live production server because maybe we're going to run a big query, which is going to take some time to process and we don't want to slow the live server down. And so maybe we work on, let's say a testing server. And so they want you to choose which server you're running this on just because you're looking at it.

Isn't enough. When I open up a file that's never been associated with a server before, up here, notice how it says active data source is not available. And the catalog or schema is not available.

It does not know which database I'm connecting to. Now you might say, but Dan, there's only one over here. Well, what if you just never created a database connection? What if this is for a different client? This is to make sure that you consciously connect to the correct server.

And if you look over here and say, oh, I never defined that server. You might have to go and create a new database connection, but they want you to make sure that you choose the correct server. They never want to accidentally connect to the wrong server and give you the wrong data.

Initially, this kind of annoyed me. I just was like, why don't you just connect over there? And I think it is because they want to ensure accuracy. The worst thing in SQL is to be fast, but wrong.

I don't care how fast you are if you're giving me a wrong answer. If it assumes to connect to a server and it's the wrong server, that could give you a wrong answer because it just assumed to make a connection to the wrong server.

So that they intentionally make you choose your server is a safeguard to make sure that you're connecting to the correct server to get the correct answer. So what annoyed me in the beginning, because I was like, why do I have to do this? I finally realized it was a safety protocol to make sure you don't choose the wrong server; you have to choose it. So up here, notice there's a little database platter.

And when I click on this, it lets me choose the server. Now, if you've not renamed your server, your server will still be called company underscore data, but it's technically the server that you're connecting to. And when I double click on that, it then assumes to connect to the default database.

Now that I've told it that, this query will run.

Now the query runs, and also now I'm getting code hinting as I type. See how the code hinting works? So the lack of code hinting is a clue.

You forgot to choose the correct server, right? So that's something we always want to do. Now, in the future, if I ever save this file and close it, DBeaver remembers for that particular file. It says, "Oh, in the past, you've connected to that server." So in the future, it'll remember it for that file.

But anytime you open a brand-new file someone gives you, like we're giving you these files, until you've connected it to a server for DBeaver to remember, the first step will always be to make sure you connect to the correct server. When you create a brand-new file yourself and select "New Script, " because you're on a server, when you create a new script, it will automatically connect to that server you're looking at. That's how it remembers for that file.

You're connecting to that server, but it doesn't automatically do that for files you open. So just once, you do have to tell it which server you're connecting to. Okay,

So here, if I'm just saying, I want to see all of the columns from the products table, when I run this, I can see all the rows, all the columns. I can see all the information, but most of the time we don't want to see everything. If I go in and say, instead of all of the columns, which columns can I see? Well, I can either look down here, or I could look over here.

This is why we browse our database in a separate column, so I can see that while I write my query. So I can see these names while I'm coding my queries.

So I might just want the title and the price. Now, if I just want the title, I type in title, and I would only see that one column here, or if I also want price, I have to put in a comma, and I can add price. The order you put these in defines the order in your result set.

This is called a result set. It's the results of your query. You define this.

If I put price before title, I can reorder my columns in this result set. What's very important to understand about this result set down here is that this is just a thing created by your query. This is not the database itself.

This is a result set created by your query. You choose what you're putting down into here. For example, if I just said, select the word, "hello."

And actually I need to put quotes around that because it's a string. Actually, they don't like that. There we go; I put the numbers one, two, three down here. I'm just saying, create a result set that has the numbers one, two, three; just put them down there.

Is this coming from my database anywhere? Did I say from my database? No. So what you're putting down here into this result set, you can just make stuff up and put it down there. Now, normally you're trying to extract data from your database.

And put it into the result set because you want to choose what you're putting there. But the idea is that you can choose what you want to put down here. It is not just a view into the database as it is right now.

You can do more stuff. If I want to put them in a different order than they're in within the database, that is fine. I'm not restricted to the order they're in within the database.

Also, what if I wanted to say the price twice? I could say, put the price, the title, and then the price again; I can repeat my columns. That's actually pretty cool. Have you ever been in Excel, and you've got a really wide spreadsheet, and you scroll side to side, and you're like, "Oh, I wish I had the column again over here."

So I didn't have to scroll back. For example, what if you have a lot of columns? Let's say for the users. If I have all of those columns, and I want the user name again when I'm over here and thinking, "Oh, I wish I could see the name over here."

I could just add a column for name; comma means show all of the columns that are in there and show the name again. So here are all the columns currently in users, and show the name again. So I don't have to scroll back over here to see the name.

I can stay over here and see the name again. Also, I like to show this early on because I always thought in the beginning, when I first got started, that it was asterisk or a list. And when somebody said, "No, you can do more with the asterisk, " an asterisk just means all of the columns in a table. Who said you can only list them once?

Like you could also add the column again! I get all the columns that are currently there, and I can add the name again.

You could put it in the list. That blew my mind. The first time I saw that, I'm like, "Oh, I thought it was one or the other!"

I didn't think about combining the two. So all you're saying is which columns you want to see and the order in which you want to see them. So you can have as many or as few columns as you want.

Just want the name, just show the name. Okay, now that's all just the columns, but what about the rows? If I were to say, show me all the columns from a table, by default, that's going to show me all of the rows.

Now I only have 52 rows, which tells me I have 52 products because each row is a different product, but maybe I only want to see the first 10. Okay, if I say to limit 10, it's going to show me the first 10 rows in this result set.

So it limits it to just 10 rows. Now I just see 10 rows. Right.

And then anytime you see PostgreSQL, that's what we're doing. Anytime you see SQL Server, just skip over that part. Just imagine it's not there.

So don't worry about the SQL Server parts. Now, if I take this that I'm working on here and I go down to the next one, we're going to talk about ordering by. So instead of limit, let's say I just have my products table, all the rows in my products table. If I want to order by a certain column, let's say by price, I can say order by the price, which is ascending order by default.

You know, 1,2, 3, ABC, or old to new. I could say to order by the price, which I think is the most intuitive way to do it. Or another way you can also do this is let's say I'm just showing the title and price columns.

Okay, so I'm just going to run this query by just clicking into this query here. If I only see these two columns, of course, I think the most understandable code is to say order by the price column.

But if I want to be really quick, I could just say two, as in the second column. Title is the first column; price is the second column. I can use the number in the result set—not the number in my original table, but the number of the columns in my result set. What I don't like about this, although it is quick, is if you reordered these columns—and this is why I don't like it as much—if I put price first.

Now, what column number is that? Number one. Now, if I'm ordering by column number two, I'd be ordering by title, not price, because if you reorder your columns—see now how it's ordered alphabetically—I'd have to switch this to order by column number one in my result set, because price is now column number one. So I like using the names whenever possible because then it doesn't matter the order of my columns.

It'll always get the correct column by its name, but it's good to know that if you are doing something really quick, you could say the first column of the result set or the second column of the result set. Maybe you wouldn't choose to do this, but you might encounter code from someone else who did. So I at least want to explain the code so you can understand it. Even if you wouldn't choose to do it, you might get code from somebody else who did choose to do it.

You're like, "But it didn't save me that much typing." Well, yes, whenever possible, I like to use the name.

I just think it's clearer when we do it that way. Okay. So it's just good to know.

Now notice the prices are going small to big. If you want to flip it so they go big to small, you would add descending. Now, this is also where you can put things on different lines, and that code will still run.

This is the whole query here. And I like to put each kind of keyword on its own line, just because I think it's easier to understand. As you get longer queries, I don't like one-line queries because they just get really hard to read.

So you can break things onto multiple lines. If you add descending, now it reverses that code, the sort order. So the default is ascending, but you never need to say ascending because it's the obvious default.

That is ascending. If you don't like the default sort order, just add descending and then it'll reverse it. Those are the only two orders you can have, ascending or descending.

So I never bother writing out the word ascending because it's the obvious default. We're almost done here, and then you guys can do this yourself.

Now, one thing I like to do is I like to go in and put in the table first, and then I come back in because as I start to type, I like to use code hints because if you use code hints, you don't make stupid little typos. I make stupid little typos. I'd rather use the code hints.

I see that it's there and I can hit TAB to accept those code hints. I just want this one column here from my products table, just this one column here. Now out of here, notice that there are some duplicates.

Oh, what if I don't want to see those duplicates? I can say distinct. So distinct will say if there are two rows or more that are the same, get rid of those duplicates. And if I want to order them in alphabetical order, I could say to order by, and I can be really quick by just saying the first column because I only have one, or I can say what I think is clearer, order by tags, which would be going from A through Z. So let's go down to T. And we should only have one Turquoise because we said distinct.

So it eliminates those duplicate rows. The order of this stuff matters because it is a structured query language. We must put things in the order that we're seeing things.

Now, as far as getting used to that order, with time, you will remember that order. Until you remember that order, there's reference in each of the Level 1, Level 2, Level 3 class files. I have the same "Written and Execution Order" file here. And this PDF is a two-page PDF.

It's a really great reference for you. This is the order in which you're writing things. Later, we'll talk more about the execution order, and this is the order in which it executes or processes your code. But for right now, let's just focus on the writing order. Select will come first.

If you have distinct, you'll put distinct—ignore the SQL Server parts—and then the FROM, and then notice ORDER BY, and notice LIMIT. If you're including these features, they must be put into this order. You cannot reorder them the way you want.

You must put them into this written order. So it's not just enough to have the correct keyword. It has to be written in this specific order.

You think just having the keyword would be enough. No, it's got to be written in this order. So you might say, "But my code looks okay."

Yes, but if the order is wrong, your code won't work. It has to be put into this order.

Your next step will be to complete the exercise challenges below. So here it'll say, "Write a query to view the entire users table, " and then you have to use what you've learned to write that query. So that you'll do.

And then we'll come back. And afterward, I'll go through these challenges to make sure we all understand the code, and we'll talk about the answers.

This is exercise 1C in the book, but basically, all 1C is going to tell you is to open up this file and make sure you connect to the correct database up here. Afterwards, from now on, we won't have written exercises. We'll just be opening up the file to work with.

But let's go through Exercise 1C; it'll have you basically go through the warmups, just like I did, just for practice. So you can type them in yourselves. By the way, these are comments. So this code technically doesn't run because it's been commented out.

The double-dash means don't run that code. Anything after two dashes says, ignore that code. It's been commented out.

So that way you can see it, but you can't actually run it. So you'll type it out yourself down here and run your code. You'll want to type it out.

So you get into practice. You need to ingrain this into your brain. The more you type it out, get used to it, the more you drill it into your brain.

Exercise 1C; let's go ahead and do that.