[MUSIC PLAYING] CARTER ZENKE: Well, hello, one and all, and welcome to CS50's Introduction to Databases with SQL. My name is Carter Zenke. And in this course, you'll learn how to represent, how to organize and manage, and how to ask questions of the data that's around you in your everyday life. But why learn those skills? Well, you may have heard we're living in the information age, where we generate so much information, so much data by virtue of interaction with computers and with each other over the internet. 

You might think of, let's say, Google keeping track of the sites you click on or the sites you search for. You could think of maybe the smartphone in your pocket or the smartwatch on your wrist keeping track of health information, emails, text messages, and so on. You might even think of YouTube, where you might be watching this same video keeping track of all the videos on their platform, the creators of those videos, and even the comment you might leave on this video. 

So although, we're living in this information age, where there is so much data, so much information, we can use these new tools, like database and SQL, to interact with that information to store it and manage it. And although we're using some of these new tools, some of the other concepts we'll learn aren't actually so new. So here is a diagram from literally a few thousand years ago. And notice how this diagram has rows and columns. And this seems to store the stipends for workers at a temple some few thousand years ago. 

So given what you know based on your prior knowledge, what kind of name might you give this diagram with rows and with columns? What can we give a name to for this? So I'm seeing some ideas of a table, perhaps a spreadsheet as well. For our purposes, we'll call this a table, where a table stores some set of information. And every row in that table stores one item in that set, where every column has some piece of information about that item, some attribute of that item. 

So here, for example, we do have a table of workers at a temple. Every row is one worker. And every column is their stipend for a particular month. So I could take this idea of a table, this very ancient idea, and apply it to a more modern context. So let's say I'm a librarian, for instance. I want to organize my library. Well, here I have book titles and authors. And I could certainly use a table to store this information. But how might you propose I store this information? What could I do with my rows and with my columns if I have book titles and book authors? 

I see one thing I could do is probably organize my titles and my authors next to each other like this. I could take my titles to my authors, put them right next together. So I have Song of Solomon by Toni Morrison, Goodnight Moon by Margaret Wise Brown. And notice hear how each book is one row. But every row has two columns worth two pieces of information for each book. I have a title for one column and an author for the other. And so together, I have a table of books where every column tells me one piece of information. And every row tells me one book in this data set. 

So thankfully, now that we're living in this information age, we no longer have to use stone tablets or perhaps pencil and paper to store our tables. We have software now like Apple Numbers, Google Sheets, and Microsoft Excel. But this isn't a course on Apple Numbers or Microsoft Excel or so on. It's actually a course on databases and on SQL. So feel free to raise your hand if you'd like, but why would we decide to move along from these spreadsheet softwares towards a database? What might that database give us that a spreadsheet might not give us? 

So I'm seeing a few ideas here and among them are some simplicity, some ability to organize some data. But there are a few other ideas which I think about too for why we might move beyond spreadsheets and go towards databases. Now one of these is this idea of scale. So let's say you are a Google or an Instagram. You're trying to store not just tens of thousands of users or hundreds of thousands but literally millions of users or billions of users. And with that kind of scale may be better served by a database to store that much information. 

Another reason to move from spreadsheets to databases is the idea of being able to update data more frequently. Maybe you're a Twitter of the world, and you're trying to have others tweet multiple times per second. Well, a database can handle that kind of capacity much better than a spreadsheet could alone. And a third reason to move beyond this might be speed. Let's say I'm trying to look up some piece of information in my database. Well, I could do that much faster with a database than I could with a spreadsheet. 

You could think of yourself using Command-F or Control-F to find a piece of information in your spreadsheet, going one by one through the rows. A database gives you access to more kinds of algorithms you could use to search this data much faster, ultimately. So these three reasons, among others, are those might want to move beyond spreadsheets and start using databases. 

Now, it's worth thinking first, what is a database? We'll be talking about databases throughout this course. So what is a database? Well, a database is simply some way to organize your data such that you can actually create data, update data, read data, and delete data. And often these our for interactions that we'll do with a database-- like adding some data, looking at data, deleting data, and even updating it along the way. But the database isn't the only thing in our picture here. 

We also have a database management system, a way to interact with our database. So you might think of perhaps writing a program on your computer. You have some interface with to write that program, like VS Code for instance. Or you might think of your own desktop on your own computer. You have icons that you interact with the underlying operating system. In the same way, we can use this software called a database management system, or a DBMS for short, to interact with a database perhaps using a graphical interface or using a textual language too. 

Now, there are a few varieties of database management softwares. And these are a few of them here. MySQL, Oracle, PostgreSQL, and SQLite. But this is a non-exhaustive list. So let me also ask again, what kinds of other database management systems have you perhaps heard of in this case? I'm seeing one for Microsoft Access, perhaps, MongoDB. There are other kinds of softwares, other companies out there that make these ways to interact with a database. And this is, again, a nonexhaustive list. 

Now, if you are a database administrator, or maybe you're somebody who's making a choice of which software to use, you have a few trade-offs to consider. Let's say you might think of one being proprietary, for instance, costing money to work with. What you get for that money is additional support to actually implement your own database. On the other hand, you might have open source software or free software to use, stuff like PostgreSQL, MySQL, and SQLite. But the downside is you are then responsible for actually implementing that database. 

Another thing to consider too is that maybe some are going to be heavier weight than others, more fully featured as a consequence but perhaps heavier weight requiring more computation to run. You can think of those like MySQL or PostgreSQL being a little bit heavier weight but being more fully featured, whereas SQLite down below will be a little lighter weight, as the name might imply, but allow you to do most of the same work that these other softwares could allow you to do as well. 

And in this course, we'll actually be using SQLite for you to work with your own databases. But gradually, we'll move on to MySQL and PostgreSQL too. So let me go ahead and talk about then SQL in this case. You might notice that in each of these, MySQL and PostgreSQL and SQLite, each of them have this idea of SQL in them. And SQL is that language that we'll use to interact with our database. 

Now, let me ask, what does SQL stand for, perhaps? We talk about S-Q-L or SQL, but what might SQL stand for? So I'm saying it stands for Structured Query Language, which is good if you already know this, but not to worry if you don't. So SQL does stand for Structured Query Language. As we'll see in this course, it is structured. It does have some keywords you can use to interact with the database. And it is a query language-- it can be used to ask questions of data inside a database. 

We'll see that this is the language we can use to create data, to read data, to update data, and delete data all with SQL in this case. And our next thing will be to talk about this idea of querying. So SQL is a query language, but what can we do with SQL? Well, the first thing we can do, we'll focus on first in this course is writing queries-- trying to ask questions of data using SQL. Well, what kinds of questions could we ask? 

Well, you might imagine perhaps working at an Instagram or a Facebook trying to work as an engineer to figure out what kinds of posts are the most liked on your platform. That's a question you can answer with databases and with SQL. You might also think of whether your numbers of daily users are growing or shrinking if you work at a startup for instance. Even maybe you might be working for some a company like Spotify that could ask, how could we play songs that are like those a user just played? This too is a question you can answer with databases and with SQL. 

Now today, we'll be focusing on this database of books. And in particular, books that have been longlisted, quote, unquote, "for the International Booker Prize." The International Booker Prize, if you're not familiar, is an award given to books written around the world by authors from many countries. And it's designed to award books of fiction particularly good in some cases. And every year, the committee selects 13 books to include on a longlist for consideration for this prize. 

And our database then has five years worth of longlists for the International Booker Prize inside of it. We could use this database, perhaps, if we were a librarian trying to find books for our library or even as a book reader, an avid reader myself trying to find books to read that I could put on my own shelf overall. So we'll look at this database. But we'll need a few tools in our toolkit, metaphorically, to actually interact with this database. And one of them is going to be Visual Studio Code. Visual Studio code is an IDE, integrated development environment, to write code and to edit files with. It's also often called VS Code. 

Now, in VS Code, we'll also be able to use SQLite, this database management system, or a DBMS for short, to actually interact with that database. So we'll be using these two tools combined to work with the database of longlisted books for the International Booker Prize. And although we'll be using it here, SQLite is not just used in this course. It's used in a variety of applications. You could think too of phone applications, where SQLite is often used on those devices have much lower memory. 

You could think too of it being used on desktop applications to simplify the process of storing data there too. You could even think of it being used on websites to help store information that user submits via a form, for example. So we'll jump into using SQLite, but keep in mind that not just in this course you'll use it, but also, it's used in a variety of applications here too. So why don't we just jump right into things and start using our environment and start using SQLite? So I'll go over here to my computer and will open up, let's say, VS Code. 

We're here. You can see I have my terminal environment. And if you're familiar, you can type things like ls to see the files that are in your current folder. So I'll type ls right here. And I'll see this database called longlist.db. Again, working with books that have been longlisted or considered for the International Booker Prize. So if I want to open up this file, I can use this command. Then this command is going to be called SQLite 3. Or I can take some file that I have, like longlist.db, and open it using this program called SQLite 3. 

Well, it's called SQLite 3 because this is the third version of the SQLite software. So let's try this in our terminal. I'll go back over here, and I'll say sqlite3 longlist.db. And now, I'll hit Enter. And notice how my terminal prompt changes. It's no longer dollar sign. It now says sqlite in front. This means I'm inside of my SQLite environment. So to clear things up, let me just clear my terminal. I can use Control-L for this. And now I have just that prompt up top. 

And now a question I want to answer in this case first is, what data do I actually have in my database? What data is actually here for me to look at and to ask questions about? Now, for this question, I can use my very first SQL keyword, which will be called SELECT. So SELECT is a way for me to select some rows in a table inside of my database. Using SELECT, I can get back certain rows or, in this case, perhaps all of them just to get a taste of what's inside. 

So let's try using SELECT on this database to understand what rows we have in our table here. Let me go back to my computer and to my SQLite environment. And I will try this very first SQL keyword. I'll say SELECT. And I can use this star operator here to say select everything. I want every row and every column from this table. Now, it's not enough for me to simply say select star and end my query. I had to tell SQL which table do I want to select rows from. 

In this case, I know I table was called longlist. So I'll say SELECT star from "longlist," quote, unquote. And to end my query, I'll say Semicolon. And then finally, I can hit Enter. And notice how I get a lot of data back. This is a lot of data all at once. But it's because my terminal is a little bit small, there's a lot of rows and columns here. So I could probably simplify this just a little bit. And instead of saying SELECT star, I could also select a particular column from my table. 

I could say, for instance, SELECT, let's say, just the title column from my database for my table like this. I just know already that there is a column called title. So now, I'll try this instead-- not select star, but select title instead. I'll hit Enter. And now, this looks a little bit better. I can see the titles inside of this table from top to bottom. Now, the neat thing here is I can select more than one column too. Let's say I don't want just the title-- I want titles and authors in my search. Well, I could do that as well. 

Let me try this. SELECT not just "title," quote, unquote, but then I'll say comma and some new column to select. I'll select also the authors from this table. And I'll select them from the longlist table like this. Now I'll hit Semicolon to end my query. Hit Enter. And now I'll see a variety of columns here, in particular the title and the author column. Now, this is going to be like all of my columns so far. And there is a way for me to get back just to some of them later on that we'll see. But for now, let's ask, what questions do you have on this SQL SELECT statement and how we're getting back these rows? 

SPEAKER: Do I need to use the quotes around the words like you are? 

CARTER ZENKE: Yeah, great question. Do I have to use quotes around the words like I am? In general, it's a good practice to use these double quotes around your table names and your column names. These are called SQL identifiers. Later on, we'll see we'll also have strings in SQL-- strings being collections of characters we can use. For those, we'll simply single-quote them to note the difference between a string and an actual column name. So good style convention here to use-- double quotes for column names and single quotes for string names. Other questions too? 

SPEAKER: OK. I wanted to know, where did we take all this information from. Like, this data, I don't know this list of books, where did we take all this information from? 

CARTER ZENKE: Yeah, a great question. Where do we take this information from? So some of this data is publicly available. In fact, if you look at the Booker Prize website, you can find a set of longlisted books over the years. In this case, we have books from 2018 to 2023. We'll also see later on this table has data on the ratings of those books and the number of votes that were given to those books. That data is from Goodreads, the site that aggregates reviews from people like you, people like me who rate books online. So we've taken data from a variety of sources and combined it into one here. Let's take one more question to from Vinayak. 

SPEAKER: Yeah. So I wanted to ask regarding the syntax of what you used in the terminal. So is the whole SQLite 3 is case-sensitive because while using the syntax you used capital letters, whereas can we use small-case letters as well? 

CARTER ZENKE: A great question. So here, I used capital letters for SQL keywords and lowercase, my table names and column names. Do I have to do that? I, in some cases, do, some cases don't. I think I could use lowercase for these SQL keywords, but it's not very good style, for instance. So let me just show you an example of this while I go back to my computer. So the question again was, can I use lowercase for SQL keywords? I think I could, but the question is, should I? And probably not. So let me try this. I'll say select, let's say, title from-- I'm in the habit of uppercase it. So I'll say from in lowercase longlist like this Semicolon. Hit Enter. And that still works. 

But the problem you might run into is someone who's reading your query particularly, a long one, might want to know, what are the SQL keywords? And what are your column names and other identifiers here? By capitalizing your SQL keywords, you can make it clear that this is a SQL keyword and not some other name overall. OK. So let's keep going. And we saw just a little bit ago that we could select "title" from "longlist." And we would get back a whole list of titles, literally all the titles that are in this database. 

But it's often maybe good practice for me to not look at all the data. Like, imagine if we had millions of rows in this column, but only to give back some. Take a peek of what's inside this database. And for that, I could use this other SQL keyword, this one called LIMIT. So LIMIT, as its name might imply, limits the number of queries or the number of rows I get back from my query. I could say LIMIT 3, for instance, or LIMIT 5 to get back only the top 3 or the top 5 rows from my table. And let me ask folks, if I wanted to peak in this data set, how many rows should I try to limit it to? 

I could say SELECT title from, let's say, longlist, but limit to what number? I'm seeing 10. So let's try 10 first. I'll go back to my computer. I'll come back over here, and I'll say, why don't I select "title" from "longlist?" But now, instead of hitting Semicolon, I will instead say LIMIT 10 Semicolon. And I'll hit Enter. Now I see only the first 10 rows in my data set. Handy for peeking in at the top of my data set here too. Let me try not just 10 but 5. So I'll say SELECT "title" from "longlist" LIMIT 5 Semicolon. 

This then gives me just the top 5 titles in my database. Just in whatever order they were added to my table, I'll see them in that order here too. So using LIMIT, we can actually try to get back a certain number of rows. But this isn't quite that interesting. It's good for peaking in your data set. I think we've answered that question of, what data do we have? But let's say we want to make more advanced queries. We want to find the books that were nominated in 2023 or perhaps books by a certain author. 

Well, for that, we could use this next SQL keyword, this one called WHERE. So WHERE allows me to get back not all rows, but only some rows where some condition is true. So WHERE is often combined with other conditions to make sure I only get back some rows, where that condition is true. Let's try looking at that in SQLite to get a feel for what it can do for us here. So I'll go back to my WHERE. And I will then go back to SQLite here. Hit Control-L to clear my terminal. And I'll then try this query. 

I'll say SELECT "title." SELECT "title." And why don't we also select author along the way? Two columns here. And I'll select them from my longlist table. But I don't want all rows. I only want, let's say, those titles and authors that were longlisted in 2023. So I'll do this. I'll say WHERE the "year" column is equal to 2023. And notice here how 2023 is not in quotes because it is an actual number, an integer. So I don't need to quote it like I would a string some collection of characters or a table or column name. 

So I'll hit Enter here. And what do I see? Well, I see only those books that were nominated in 2023. Let's try this again. I might try not just 2023-- I might try 2022 like this. Hit Semicolon. Now, I'll see those books nominated in 2022. I could keep going. I could say why not 2022? Why not 2021? Now, I have all those books nominated in 2021. So this is handy. We can set things equal to or not equal to make some condition here. And we also have others we could use. 

We saw equals just now. But we similarly have not equals. And we have this kind of obscure operator down here. This one being also equivalent to not equals as we'll see in just a minute. But let me first ask now, what questions do we have on how to use WHERE or using SELECT so far? 

SPEAKER: Why are the subsets of SQL? 

CARTER ZENKE: A good question. Are there subsets of SQL? So there are. In fact, S-Q-L or SQL was defined by the, I believe it's the ANSI, like standard corporation. They have a whole set of the SQL language that is like the official version of it. You might be able to use some subset of that version with the database manager system that you actually use. So for SQLite, we're using a subset of SQL that works with SQLite. Similarly, if you were using another software like PostgreSQL or MySQL, you could use another subset there too. Let's take another question from Tayas, perhaps. 

SPEAKER: Then I want to know that, can we add 2022 and 2021 in a terminal? 

CARTER ZENKE: Yeah, good question. Could I, perhaps, try to filter by 2021 and 2022? I could do that. And we'll see that in just a moment here. So let's keep going and exploring some other options with not equals. And then we'll see how we can combine conditions using WHERE too. So let's go back, and let's focus first on trying to use these not equal operators. We saw the exclamation point equals and this greater than, less than sign put together. So let's try a few of those. 

Let's say I want to find books that are written by a certain author. Well, I could use equals for that. But let's say I also want to find books that are not written in the hardcover format, like they tend to be more expensive and so on. So I don't want hardcover books. Well, I could try a query like this. I could say SELECT "title" and "format," where format is either hardcover or paperback, FROM my "longlist" table. And now I'll say, WHERE the "format" is not equal to hardcover Semicolon. 

So notice here I'm using single quotes for hardcover. This is a string. It's not a table name or a column name. It is just a string. So I'm using single quotes here. Everything else, though, like format, longlist, title, et cetera, those are all table names or column names. So again, I'll hit Enter. And now I'll see that these are all in paperback according to my table. I've omitted those that are hardcover. Well, I could also use in this case, the greater than or less than sign put together to say not equals as well. 

Let me just hit the Up Arrow on my computer to reveal what I just previously typed. I'll then tab back over and say not exclamation point equals, but less than and then greater than. Hit Enter now. And I should see the very same results. But all I did was change this operator from exclamation point equals to less than and greater than. It tends to be that the exclamation point equals is the more common operator in this case, but they do the very same thing. 

Now, one more keyword I could use here that's worth mentioning is this keyword called NOT. So here, I was able to use exclamation point equals or the less than or greater than sign to say not equals. But I could also negate a condition using NOT. So let's try using this one in our SQLite terminal too. I'll go back over here. And I'll bring back my SQLite terminal. I'll say let's do not this operator here, but instead use NOT. So I might, in front of WHERE, say WHERE NOT "format" equals "hardcover." 

So I have a condition, "format" equals "hardcover," but now I'm going to negate it, take the opposite of it and get back the very same results here too. OK. So to the question earlier, how we can combine these conditionals. So let's try that here in just a minute. So let's say I wanted to find the books that were not just written in 2022 or 2023 alone, but all the books together. Well for this, I could use a few other SQL keywords that might be a little familiar to you too. Let's try looking at some of these over here. 

So here, we have these called AND, OR, and these parentheses here too. So using AND and OR, I can change conditionals. I can put them together to make a more complex conditional, a compound condition. And I could also use these parentheses to symbolize that this condition should come first and then some condition should come afterwards as well. So let's try these in SQLite as well. I'll go back to my computer. 

And again, our goal is to find not just books in 2022 or 2023, but books that work across those years as well. So I'll, in this case say, SELECT-- let's go for a "title" and "author" from my "longlist" table. Now, I'll say WHERE the "year" is 2022, as we did before, OR perhaps the "year" is 2023. And notice how my query is kind of wrapping around my terminal. I could leave it like this, or if I backspace just a little bit, I could hit Enter. And now I'm on a new line to continue my query. So here, I'll say OR "year" is 2023. 

And now my query is done. So I'll hit Semicolon. And I should see those books published in or nominated in 2022 or 2023. Let's try a few more here. Let's try using our parentheses as well. Maybe I want not just these books but also those that are formatted in a hardcover format. So I'll say-- I'll clear my terminal, Control-L again, and SELECT "title" as well as, let's say, "format" from my "longlist" table. And now, I'll hit my new line to extend my query without wrapping it on my terminal. 

I'll hit Enter. And I'll say WHERE the "year" is 2022 OR the "year" is 2023. That's one condition. And I can denote that with a single set of parentheses here. I also want it to be true that the "format" is not "hardcover." So now, I'm adding another condition in here. Now, I'll say Semicolon, hit Enter. And I'll get back only those books that are published in the paperback format in 2022 and 2023. OK. So let me pause again and ask if there are any questions so far in how we've been using WHERE and SELECT and other conditions as well. 

SPEAKER: I would like to know about the-- we can have a list of the top titles available in the database. Like you have mentioned, the title, author, where we can know about that. What are the titles available in the database using command below? 

CARTER ZENKE: Yeah. So here I've been using these column names called "title" and "author." And I think your question is, how would I know that I have these columns? Well, as we'll see in future times together, I'll be able to actually look at the schema of my database, what columns are inside of it. For now, just take it on my own word that I knew what was inside the database before I actually started querying it. We'll see later on how you can take a database and understand the columns you have there too. A good question. 

Let's jump into some more queries then. I'll go back to my computer. And let's see what else we could do with these conditions. Well, not only could I try to make compound conditions, I could also try to find, let's say, which data is missing. So I know in this table, I have not just authors of books, but translators of those books. Often, books for the International Booker Prize were translated from some other language into English-- but some weren't or at least they didn't have a translator that was separate from the author. 

So to think about what data is missing from our table, we should introduce this new idea-- this one called NULL. So I'll walk over here. And we'll see that we have this type called NULL, where this means that this value doesn't exist. It's not in our database. We can actually put together a condition around this idea of null, something not being there. We could use IS NULL to figure out if a value is null. It's not there. It's missing from our database. Or IS IT NULL, meaning that it actually is there. 

So I'll go back to SQLite and show you what we could do with some of these concepts here. Let me go back to my terminal. And let's say I do want to find those translators that don't exist in my database. Well, I could use SELECT, let's say, "title" and "translator" from my "longlist." And I want to make sure that these translators are null-- they don't exist. So I'll say WHERE "translator" IS NULL Semicolon. Now, I'll hit Enter. 

And I should see two books. Titles are The Perfect Nine and the indictment of The Enlightenment of The Greengate Tree. But notice how over here, this value is null-- it doesn't exist in my table. I could conversely find those books that do have translators using IS NOT NULL. And I will try this one again. But in this case, I'll say, WHERE "translator" IS NOT NULL Semicolon. And I'll hit Enter. And now, let me just zoom out a little bit. I can see that I have both titles on the left-hand side and translators on the right-hand side. All of these actually exists. 

These are books that did have translators in this case. So a good way to find data that's missing in your table using NULL or IS NOT NULL. So let's come back over here and figure out what more we can do with some of these queries. We've kind of exhausted our work with some of our conditions, like chaining them together and using NULL and so on. But one more thing we could do is trying to use this idea of matching some kind of pattern in my database. 

So maybe I'm a book reader. And I want to find a book with the word "love" somewhere in the title. Well, for this, I could use another keyword-- this one called LIKE. So LIKE is a good keyword to use when I want to roughly match some string in my database. Let's say I want to look at book titles and find if some word exists in that title. I could use LIKE for that. And LIKE becomes powerful when you combine it with these other operators, namely this percent sign and this underscore. The percent sign can match any character around a string I give it. And the underscore can match any single character that I pass in with my string. 

It's probably best shown with an example. So let me show you some in my terminal here. I'll walk back. And, again, we'll try to find these books that have "love" somewhere in the title. So I'll say in this case, SELECT, let's say, "title" from "longlist." But I don't want all titles. I only want those that have "love" somewhere in this title. So I'll say WHERE "title" LIKE, let's say, percent love percent Semicolon. Now before I run this, let me explain what this is doing. 

I have here a SELECT query asking for the "title" column from my "longlist" table. But I'll only get back those rows where "title" is LIKE percent love percent. But what does that mean? Well, the percent, remember, matches any string of characters. It could match a, b, c, 1, 2, 3. As long as any string of characters comes after and has "love," I could match that value here. Similarly, the percent sign after says anything that comes after "love," as long as "love" is somewhere in the middle. So anything before, anything after, but so long as "love" is just somewhere in there, I'll get it back. 

So let me try that running this query then and come back over here. I will hit Enter on my query. And I'll see I get back four books-- Love in the Big City, More Than I Love My Life and so on. So notice how if I come back over here that each of these titles has "love" somewhere in it. For this one, I match love up front and then had any string of characters coming after it like this. For this one, I had More Than I Love My Life. I got some string before it. And then afterwards, any string after it, "love" is somewhere in the middle. 

Let me show you another example too, where we use percent in a different way. Let's say I want to find only those books that have "The" at the very beginning of the title. Let me try this. I'll say a SELECT "title" from "longlist" then WHERE, let's say, the "title" is LIKE the percent Semicolon. Now, I've changed something up. I have not percent in front and behind, but only after the "The." So in this case, I'll get back not anything that has "The" the title wherever, but now at the very beginning of this string. 

And I see perhaps a style mistake. Let me ask the audience, what style mistake did I just make when I typed in this query? So I'm seeing maybe I used double quotes when I should have used single quotes. So let me come back and fix that first. I'll come back over here. And, again, by convention, we tend to use single quotes for our strings. So let me fix that right here. And now let me run this query to see what we get back. I'll hit Enter. And I'll see only those books that begin with "The." 

Now, let me show this query again, though. This was our query here. Knowing what we know about the percent sign, what other titles might I accidentally get back by running this query? I have "The" percent. But what other words actually begin these book titles if I were to run this query here? We saw only those with "The," but if I had other book titles, what might I get back? So I might get back those book titles that have not just "The" the beginning but also, let's say, "There" or "They" or so on. 

There are many words beginning with T-H-E. And if I had the percent sign right after it, I might match one of those words like Y or R-E or so on. So I didn't have any of those titles in this database. But you can imagine a different database where I have that kind of data. Let's fix this then. I say, I want to match not just "The" but "The" and a space, and match any characters after that to make this query better designed in this instance. OK. So let me pause here then and ask what questions we have on using percent along with LIKE. 

SPEAKER: Can we use two percent signs between two words? 

CARTER ZENKE: Yeah, I think so. So let me go back to my terminal here and let me try to answer this question. So can we use two percent signs to say something's between two words? So let me say this. I'll go with SELECT, let's say, "title" from "longlist" WHERE maybe the "title" has something like-- maybe it begins with "The." So I'll say WHERE "title" LIKE "The." And then I'll take any set of characters afterwards. But I want to have "love" also in here too. So I'll say "love." And then I'll take any other characters after it, percent again, single quote, Semicolon. 

Now, I don't know if I have any books that actually fit this search. But I could say the percent love percent to mean give me back any title that has "The" at the beginning then any words or characters then "love" then any words or characters after that. I'll hit Enter. And I see I don't have any books that fit that kind of description. But if I did, I would see them here with "The" at the beginning and "love" somewhere in the title. 

All right. So let's keep going. Let's focus not just on this percent sign, but also on this underscore. So if I want to find-- let's say, I don't know what a particular character is in my title. I could use this underscore to match any particular character. Not any string of characters, but any single character too. So let's try this in our terminal. And there is a book, this one called a Pyre. And I actually keep forgetting how it's spelled. I don't know whether it's P-I-R-E or P-Y-R-E. It could be either one, but I want to find it in my database. 

So let me try this. I'll say SELECT, let's say, "title" FROM "longlist," WHERE, in this case, "title" is LIKE-- well, I know that it starts with a P. And I don't know if this is an I or a Y. But I'll at least leave it as an underscore now to say it could be any character here. Then I'll say R-E single quote, Semicolon. And now I could try hitting Enter. And I'll see I get back this title called Pyre. So notice in this case that the underscore is matching literally any single character. This could be a Y. It could be an I. But in this case, I have this Y here. OK. Let me go back. And let's actually ask in this case what questions we have on using LIKE with these single underscores if any. 

SPEAKER: Yeah. So Carter, I wanted to ask you, that as you use the underscore sign here, so for multiple characters, can we use multiple underscores in order to find something in the database? 

CARTER ZENKE: A great question. Could we use more than one underscore to try to find some characters in our database? You absolutely could. So let me try that myself. I'll go back to my terminal. And let me try to find a book title this could work with. I'll say maybe SELECT "title" from "longlist" to get back all the books in this table. I'll hit Semicolon. And maybe I will go with-- let's try this one called Tyll. Well, maybe I want to just find the titles that have a Y or an I in here, but I also don't know if it's one L or maybe two L's, for instance. 

Let me go back and try this. I'll say SELECT, let's say, "title" FROM "longlist" WHERE my "title" is LIKE-- I know it begins with a T. I don't know if this is a Y or an I. And maybe I know that it has maybe one or two characters after it. So I'll try this one. Now I have three underscores, single underscore. So match any book title that has T and then any three individual characters after it. I'll hit Enter. And I'll see I get back Tyll. This is the only title that has T and then three characters after it. 

I could try to get better with this. I could say maybe I'll accept five or six characters like that. Hit Enter. And I'll see-- whoops, I didn't compute my query here. Let me just try it from the top again. I'll say SELECT "title" from "longlist" where title is like, let's say, T-Y underscore, underscore, underscore, underscore. Hit Semicolon. And now I get no matches. So there is no book in the database that has T-Y and then, let's say, three or four underscores for any character after it. 

OK. So this covers our use of LIKE, but let's keep going and building more complex conditions to find even more answers to questions we have about this data over here. Let me think what we should show next. We've seen LIKE. We've seen some compound conditionals. Well, let's go back to trying to find books that are in a certain year. So we saw earlier we had this kind of query. We could say SELECT "title" and "year" FROM, let's say, our "longlist." Now I can try to find those books that are written or nominated in 2022 and 2023. But let's say I want to go further. I want those from 2019 to 2022, a span of multiple years here. 

So I could try it like this, WHERE "year" equals-- let's go ahead and say 2019-- OR "year" is 2020 OR "year" is 2021. And let me make a new line again. OR "year" is 2022 Semicolon. And before I run this query, let me ask our audience, what strikes you as being not very well designed about this query? What could I be doing better here? So I'm seeing maybe one improvement is that I don't need to write out OR "year" is this, OR "year" is that. I could probably do better with this. And let's introduce some new keywords for working with ranges in terms of our conditions. 

So here, we can see some new operators to use. We have this greater than sign, this less than sign, greater than or equal to, and less than or equal to. And we can use these to build ranges inside of our queries to say, I want something to be greater than this number or less than this number too. And we can combine these with AND and our OR we saw before to get back in this case some set of rows that match what we intend to find. 

So let me go back and try some of these out. I'll try to improve the design of this query. Let me first run it, and we'll see we do get back 2019 to 2022. But I could probably do better. So let's try using our new operators here that can give us some range capabilities. I'll say SELECT "title" and also SELECT "year" from "longlist." But now I want those rows where the year is greater than or equal to 2019, and the year is less than or equal to 2022 Semicolon. I'll hit Enter. 

Notice I'll get the very same results. So I get all those same rows. But now my query is much smaller. It's making use of these range operators I've seen so far. I could even further improve this. I could make this a little better designed to. Let me go back to some slides and show you we could use these keywords BETWEEN blank AND blank, where this can be some condition or some number in this case. I could say between, let's say, 2019 AND 2022. This is inclusive. So if I say 2019 AND 2022, I'll get back a query that includes 2019 and 2022. 

So let me try this one. I'll go back over here. And I will now try SELECT "title" and "year" from "longlist," WHERE the "year" is between 2019 AND 2022. Same results now, which is a different way of writing this same query. Now what else can we do with these ranges? Well, as we've said before, these books actually have some ratings involved. These ratings are crowdsourced from Goodreads, a site you can review books online. And I want to find maybe the books that have a rating of 4.0 or higher. 

Well, I could do that now with my ranges I could say SELECT "title" and "rating" from my "longlist." And I could say WHERE the "rating" is greater than 4.0 Semicolon. I'll hit Enter. And I'll see now only those books that have a rating of 4.0 or higher like this. I could even combine conditions. So I know that these books have a certain rating, but how many votes do they really get? Well, let's take a peek. I'll come back over here. And let me try this one. 

I could say SELECT "title." Oops, let me clear my terminal again so it's back up top. SELECT "title" and "rating" and the number of votes that these books got from, let's say, our "longlist" table. Now, I want to find those that have a rating of greater than 4.0, and, let's say, a number of votes-- a number of votes which is greater than at least-- let's go with 10,000. So at least we know a good number of folks actually voted on these books to find the best among them. 

I'll Enter. And now I'll see we're only down to a few books, four in fact, where each one has a rating higher than 4.0. And indeed, every vote row has a vote total greater than 10,000 in this case. So a good way to try to find the top books in our data set here. Let's keep going with these ranges. And let's think about one more thing we could do. Maybe I want to find books that are less than a certain length. So I'll try that as well. I'll say SELECT, let's say, "title" and "pages" from my "longlist." And now I can make a condition based on pages. 

I'll say WHERE "pages" is less than 300. Hit Enter. And now I should see that I have all these books that are less than 300 pages long when they were first published. So let's pause here and ask what questions we have on these range conditions. 

SPEAKER: I just wanted to check if for a proper query in this case to be able to run operations, they have to be integers in the database. And my second question is for when we're matching a string, is it case-sensitive or not? 

CARTER ZENKE: Yeah, two great questions. So the first one here is going to be, do I have to use integers in this case? And what types, maybe, should I use? And the second one being, could I match strings like case insensitively? So for the first one, in this case, it'll depend on the design of your database. So we'll see later on in the course, how we can choose the types for our columns. And how that might impact the types we actually use in our queries. 

For now, I made this database. So I just know that my year column is an integer, my ratings column is a real number or a float, if you're familiar, and my votes is an integer. So I just know to use those numbers there. To the question of matching things case-insensitively, let's actually revisit LIKE just briefly here to show you what that can do. So I go back to my terminal, and let's say I want to find just a book title. And I want to type it in kind of sloppily. I don't want to capitalize it like capital books are. 

So I'll say SELECT let's say "title" from "longlist." And maybe, I'll want to find that book Pyre again. So I could say WHERE "title" is LIKE 'pyre', but all in lowercase. Now I'll hit Enter. And I'll see I do get back Pyre. So even though I said WHERE "title" is LIKE lowercase 'pyre', I got back capital Pyre. Now this is in contrast to saying WHERE "title" equals lowercase 'pyre'. Let's try that. I'll come back over here. And I'll say again SELECT "title" from "longlist," but now WHERE "title" equals, quote, unquote, 'pyre' Semicolon. I'll hit Enter. And now I see no results. So in this case, the equal is going to be case-sensitive. Case matters in this case, but LIKE is case-insensitive. 

OK. Why don't we keep going then? And let's take a look at a few other things we can do with these SQL keywords for querying. Well, earlier, we were trying to find a way to find the best books in our data set. And we did that by filtering them based on some ranges. But we could probably do that a little bit more methodically in this case using a new keyword, this one called ORDER BY. So ORDER BY allows us to take the results of our query and order them, as it suggests, by some column itself. 

So we could put them in alphabetical order or in order by a number of votes or in order by number of ratings. And let's just try an example of this to see how ORDER BY works for us. But in the end, we'll see it can arrange columns, arrange rows for us in our resulting query. So I'll go back to my computer. And let's try this question here. I want to try to find the top 10 books in my table. So I'll say SELECT "title" and "rating" from "longlist." Enter. Not only query yet though. Now I'll say ORDER BY the rating. 

And let's only take the top 10. So I'll say limit 10 in this instance Semicolon. So now I've combined some of my prior keywords. I'm using SELECT. I'm using ORDER BY. And I'm still using our old friend LIMIT. So let me hit Enter here. And I'll get back, well, not quite the top 10. I see rating of 3.05 here and rating of 3.42 down here. So based on this, what do you think the default ordering of ORDER BY is? So you might be from least to greatest. So we saw here that we have rating being pretty small, but we said ORDER BY our rating. 

So it starts from small and goes down to large. So we need to fix this in some way. And let's introduce a new addition to ORDER BY to have us fix this query overall. So let me show you that ORDER BY does by default sort from least to greatest. But let's try some addition here. We have not just ORDER BY but ORDER BY some column and then ascending or descending. So ascending is the default. It means from least to greatest. Descending, we can specify meaning from greatest to smallest. So let's try using ORDER BY but now with this other keyword called DESC for descending here. 

I'll go back to my terminal. And let's rewrite this query to include DESC. I'll say SELECT "title" and "rating" from "longlist." And let me-- before I run this query, let me just clear my terminal so it's back up at the top. I'll backspace this. And then a moment here, I'll press Control-L. Now I'm back at the top. I'll say SELECT "title", SELECT "title" and "rating" from "longlist" WHERE-- actually, not WHERE. We're not filtering yet. I'll say ORDER BY rating but not by ascending by default-- going from least to greatest. I want greatest to least. So I'll say DESC here. 

Now, I can say LIMIT 10 Semicolon. Hit Enter. And now I'll see the top 10 books. Here, I have The Eighth Life coming in at 4.52 and The Books of Jacob coming in at 4.06. So now we're going from greatest to smallest. Well, I could order by not just these ratings, but also by the number of votes. It seems there's a tie to break here. If I look at Still Born and When We Cease to Understand the World, those both have a rating of 4.14. But presumably, one book has maybe more votes than the other. So I could try to break this tie by ordering not just by rating but also by votes, the number votes this book actually received on Good Reads. 

So let's try that then to break this tie. I'll come back over here. And I'll try this query now. I'll say, again, SELECT "title" and "rating" from "longlist." Now I'll order by, first, the rating column in descending order. But I also want to order by the number of votes after I order by rating. So I'm saying first order by rating, but afterwards, followed by a comma, let's order by the number of votes also in descending order-- from greatest to smallest. 

Now I'll just continue my query on the next line. And I'll say LIMIT 10 Semicolon. This then gives me, let's see, the books but now they're going to be in the order that allows us to see the number of votes. Let me just actually refine this. Let me say not just title and rating, let's make sure we can see the votes here too. So SELECT "title" and "rating" and "votes" from "longlist." Hit Enter on my query. Now I'll say ORDER BY "rating" and "votes." Then I'll say LIMIT 10. 

And here, I'm just getting the Up Arrow on my computer. I'll hit Enter. And now I'll see the votes included. So let me show you this on the big screen. Here, we see that the tie is broken. So when we cease to understand the world, these both have 4.14 along with Still Born. But here, this book has more votes and so is higher in our order now that we've ordered by multiple columns. So let me pause here and ask what questions we have on ordering with data. Ordering by one column or multiple, and how we can sort data like this. 

SPEAKER: Sir, I want to know that can we write rating to 4.93 to 4.9? 

CARTER ZENKE: Yeah good question. I think if I understand you correctly, how can we select a rating or try to find a rating that's like equal to 4.92 or things like that. Let's try that here. So if I want to find a particular rating, I could simply use my WHERE friend from before. I could say SELECT, let's say, "title" and "rating." And maybe I could try to find a particular rating for a book from longlist. I could say then WHERE this rating is equal to, let's say, 4.932 Semicolon. If this book exists, it will get it back here. So I'll hit Enter. And I see there's no book with this particular rating, 4.392. But good question for how to find particular ratings for our books here. OK. Other questions too on how we've been able to sort our data and use ORDER BY? 

SPEAKER: Will descend work on a string on an alphabetic basis? Or do we need to have special conditions for alphabetic characters? 

CARTER ZENKE: Yeah, a great question. So how could we use ORDER BY with some characters or strings or some text in our database? Let's try that one out too and see how that works with ASC for ascending and DESC for descending. So I'll go back to my terminal. And I'll demonstrate here how we can use this for some text. So let's try to simply sorting our books alphabetically for, let's say, our library. I'll say SELECT "title" from "longlist," Enter. And I want to order by title, just plain and simple. And then hit Semicolon. Let's see what happens. 

I'll hit Enter. And now I'll see that these books are ordered. But they seem to be ordered alphabetically. So here, we have some titles lower in the alphabet. And up here, we have titles earlier in the alphabet. So by default ORDER BY seems to order alphabetically. If I change that default, though, from ascending to descending, let's see what happens. I'll go back over here. And I'll try the same query but now using DESC. SELECT "title" from "longlist." ORDER BY "title" now in descending order. Hit Enter. 

And now I'll see these titles in reverse alphabetical order. So notice how earlier on, we have titles that are lower in the alphabet. But down below, we have titles that are earlier in the alphabet here. So you can use ORDER BY with these texts. But you then have to specify whether you want it in alphabetical order or in reverse alphabetical order. OK, so let's show a few other concepts here we can use alongside of these orderings. One thing we could also do is try to find more information about the ratings of these books. 

So let's say I want not just to order these books but try to find the average rating, or to try to find the number of books, or try to find let's say maybe the sum of my total votes on each of my books. Well, for this, we could introduce some new concepts, these ones called SQL's aggregate functions. These allow us to take a whole set of rows and return not each of those rows individually. But instead, in this case, one number based on the values in those rows. You could imagine trying to count the number of rows you have or take the average of the number of rows, or take the average of let's say a rating, for instance. 

Finding the minimum rating of the maximum rating or finding the sum of some votes. And we'll see each of these in action here. Let's go back to our terminal, try some of these out. I will try, in this case first trying to find the average rating from my longlist. Well, I just from experience, and as you now know too, I can try to find the average of some column by using the AVG aggregate function. So I'll say SELECT not just rating in this case but select the average rating FROM "longlist." 

Notice how in this case, I'm using this kind of syntax, where I take rating, my column I want to aggregate or to sum up or to average like this. And I apply the function by saying its name followed by some parentheses around that column name. So this will return to me not all of the rating rows but the average of the rating rows in one single cell. Let me try this. I'll come back, and I will then hit Enter. And I'll see this is the average rating. We have 3.7537179471795 is our average rating for all of these books. 

But of course, this isn't great. What might I want to do if I was going to show this to somebody else? I could probably improve the presentation of this in some way. So I could probably round this result. I have 3.75371, we can probably stop after two decimal points, right? Just simply like 3.75. So I could introduce some new keyword here, this one around the results. Let me show you this one in action. I'll come back, and I'll try not just select average rating but select the rounded average rating. 

So I'll say SELECT ROUND and then take average of "rating" and round to 2 decimal points FROM "longlist" Semicolon. So now this query decides to first find the average of the rating column. Then take the result and round it using two decimal points. Notice how round takes two inputs or two arguments, the first one being the rating, the average rating, the second one being number of decimal points to round, 2. And we complete our query in the way we usually do by saying FROM this table. So let's try this one to figure this out. 

I'll come back. And I'll hit Enter. And now I'll see we do get back 3.75. But there is still one thing to improve here. When I write this query, I see this ugly title name-- ROUND average "rating" comma 2. I wouldn't send this to my boss or somebody else who I work for or maybe even a friend, right? I want to make sure it's pretty so they can read it correctly. So what could I do then to try to make this prettier? I could maybe rename this column. I could try to take this and make it not just this ugly mess of SQL keyword but to give it some name I could use instead. 

So for this, we'll introduce a brand new one-- new brand new keyword called AS. Let's try this one too. I'll come back, and I'll say SELECT, again, ROUND average "rating" comma 2. But now, I'll select it AS, let's say, average rating. And now before I actually finish this query, let me try to bring it up to the top my terminal so we can see it all in one go. I'll backspace this. And I'll say SELECT the rounded version of the average rating rounded to 2 decimal points AS-- let's call this one "average rating." Now hit Enter. And I'll say FROM my "longlist" table Semicolon. 

Now, I see it's much prettier overall. I have no longer these SQL keywords but instead just average rating as my column name. OK. So let me pause here and ask questions then on using average or using ROUND or using AS in these cases. 

SPEAKER: I'm wondering, do these sorts of commands have a funct-- are these commands encode something like data types or just encode something? Do these commands also have a name? 

CARTER ZENKE: Yeah. And can I ask are you referring to the AVG like COUNT like SUM those kinds of things, or? Yeah. So these functions do have a name. They are called aggregate functions. And aggregate means to combine, to bring together. So they're called aggregate functions because they take some number of rows, like all my ratings for instance, and bring it down to one single cell, like the average or the sum or the count. So if you look up or read more about SQL aggregate functions, you'll see all of these and perhaps some more depending on the software you're using. 

OK. So let's keep going then and try to start counting some other rows and use our other aggregate functions here. I'll go back to my terminal. And so far, we've seen average, as well as we have seen ROUND and so on. But why don't I try to find the maximum or the minimum rating in my table? I'll say SELECT, let's say, the MAX "rating" FROM my "longlist" Semicolon. Hit Enter. Now I see the highest rated book had a rating of 4.52. Well, what about the minimum rating? I could use MIN here too. 

I could say SELECT, let's say, MIN of my "rating" column FROM my "longlist" table. I'll hit Semicolon. And I'll see it 3.05. It is the lowest-rated book I have in this set. Well, as we've seen, let me try to view this for you all. I could say SELECT "title" and "votes" FROM my "longlist." SELECT "title" and "votes" from my "longlist." Here, I have many books with many user-generated votes. Maybe people on the internet decided to rate this book out of 5 and maybe Go, Went, Gone got about, let's say, 592 votes. 

So I'm curious then, how many total votes do I have in my data set? Well, for that, I could use the SUM aggregate function. Try to count up each one of these rows and/or turn it back to me in a single cell. So I'll use SUM here. I'll come back and I'll say I want to find the sum of my votes column. I'll say SELECT, let's say, the SUM of my "votes" column FROM my "longlist" table. And then I'll just Enter-- Semicolon Enter. And I'll see over 600,000 people offer to vote for each of these books that were longlisted for the International Booker Prize. 

Now there'd be a few more here. Let's check out what else we have left to do and our aggregate functions. We could also try to count up just the number of books in our data set. So why don't I try to find the number of rows I have? For that, I could use count. And often to find the number of rows in your data set, you might use count and star as we saw a little earlier. I could say SELECT COUNT star from "longlist" And this means, again star means give me every row and every column. Give me basically my whole table, right? 

And if I say COUNT star, that means count up the number of rows that I have in my database. So I'll say COUNT star from "longlist." And I get back 78 books in this database. Well, let me try counting up the number of translators here. I'll say SELECT, let's say, COUNT of "translators" FROM "longlist" Semicolon. Hit Enter. And now I see 76. So I have 78 books, but if I count translators, I have 76 of them. So why might that be? Free to raise your hand and try to guess at this. Why do I have 78 rows but 76 translators? 

SPEAKER: Hi. I actually had raised my hand for the question. 

CARTER ZENKE: Yeah, go ahead. 

SPEAKER: Also, I wanted to know whether the MAX and the MIN functions can be used for finding the longest or the shortest string as well? Or do you have a different command for that? 

CARTER ZENKE: Good question. Could we use MAX and MIN to find the longest or shortest string? That's a good question. So let's actually pause on this counting here and try that out real quick. So I'll come back to my terminal. And let me try to use MAX and MIN with some book titles. So I'll say Select-- let's select the MAX title. And at the same time, why don't we select the MIN title as well? And I'll select these from my "longlist" table. I'll hit Semicolon. And now, let me try this out. I'll get back Wretchedness and A New Name-- Septology VI-VII. 

Now, there's a few hypotheses here. It does seem that our MAX "title" is shorter than our MIN "title." So it's probably not that MAX gives us the length of the string, but what do you notice? Well, I see MIN is really early on in the alphabet. It has an A here, whereas MAX has a W, pretty low in the alphabet. And I would bet if we ordered these book titles, we would see a new name up at the very top and a Wretchedness, the book here, down at the bottom. 

So MAX seems to give us the lowest alphabetically, which is kind of contradictory with titles here or strings. And MIN gives us the earliest in the alphabet using this A as well. OK. So a good question. Let's come back to our counting here. Let's go back to my terminal. And again, we had, in this case, 78 rows, but only 76 translators. So, again, if I did SELECT COUNT star from, let's say, "longlist" then Semicolon, I get back 78. But if I do SELECT COUNT of "translator" from "longlist," I get back 76. And let me ask again, why do we have 78 rows but 76 translators? Feel free to say it. 

OK, so I'm seeing maybe we have some number of rows, 78. But for our translators, you remember, two of those were null values. They didn't exist in our table. So it seems like if we use COUNT star, we're counting all the rows. But if we use COUNT "translator," some column that has null values, we're only getting back those rows or those values that aren't null. So COUNT, when given a column, counts only those that are not null that exist in our table. 

OK, let's look at one more example here for counting. And let's try this. Let's say I want to find all of the publishers in this database. I'll say SELECT COUNT of "publisher" from my "longlist." And I'll hit Semicolon. So you might think that I have 78 publishers in this long list. But would it be accurate if I were to say I have 78 different publishers in this longlist? Could I say that? I'm seeing no, right? I couldn't try to count up these publishers and then say I have 78 different ones. I might double count the publisher along the way. And let me show you what we mean here. 

So I'll go back to my table. And let me try to select from publishers or select the publisher column from longlist. I'll select "publisher" from "longlist." Hit Semicolon. Oops, and now I see something a little odd. Let me scroll back up and maybe ask for a raised hand here. Why might I get this odd result? 

SPEAKER: Because of the quotes? 

CARTER ZENKE: Yeah. So I think I mistyped some of my query here. I said it looks like "pubsliher" instead of "publisher." And in this case, SQL will give me what I asked for. I said SELECT "pubsliher" from "longlist." And says, OK, here it is. But that column doesn't exist so it creates this data for me. So let me try this again. I'll go back. And I'll hopefully type this correctly now. I'll say SELECT, let's say, "publisher," this one, FROM "longlist" Semicolon. And now I'll see all of the publishers that I have in my table. 

But what do you see? Well, some repeat, right? I have Harvill Secker multiple times here. I have similarly MacLehose Press multiple times as well. So if I count it up, these publishers, I would get each one counted one time, which I want to find the distinct ones the different ones. I need a new keyword for this. And for this, we'll use this keyword indeed called DISTINCT, trying to find unique values from our column. So let's try this. I'll go back over here and I will now select not just publishers, but distinct publishers. I'll say SELECT DISTINCT publisher from "longlist" Semicolon. 

Now, if I scroll through here, I should see each publisher in here only one time. If they have the same name, they've been filtered out. And now they're only the same publisher here too. So I will then try to say SELECT, let's say, COUNT of "publisher," SELECT COUNT of "publisher" from-- COUNT of DISTINCT "publisher," for instance. COUNT distinct publisher-- oh, typo, "publisher" FROM "longlist" Semicolon. And I'll see I have 33 distinct publishers. 

OK. So this just about brings us to the conclusion of all of these new SQL keywords here. We've seen so far that we have several here to use. But let's figure out how to actually exit this prompt. So you might it be in your SQLite prompt right now. If you want to leave it, you could also use this command, dot quit. Dot quit is not a SQL keyword. It's a SQLite keyword to leave your terminal and go back to where you started. 

So just to review then, what we've seen so far is how to select data from our table. We can use select column to take some column from our table and give us back all of those rows from that table for that column. We've seen we can apply some aggregate functions to take maybe the count of our columns or the average or so on. And we can get back not just all of our rows, but only some of them using our WHERE clause here along with a condition. 

We could have multiple conditions, having not just one but perhaps two. Like, let's say, here, condition 0 and condition 1. And we could also use, we saw before, this idea of equals and LIKE to match some pattern or to make something exactly equal over here. We could, again, use AND and OR. And we saw later on how we could order our data and use our LIMIT function to get back only some number of rows. Now, this then is our interaction to querying. 

And so far, we've seen this world of books. And the table we've had so far really just has books inside of it. But next time, what we'll see is how to take this world of books and split into multiple tables. How do we find information on publishers or books or authors too? And how do we try to put that in a table that can present the relationships among all of these different entities? We'll talk about all that and more when we come back next time. And we'll see you there.