Harvard CS50’s Intro to Databases with SQL – Full University Course

Category: N/A

Transcript

00:00

This course is CS50's introduction to databases using a language called SQL. This course is from Harvard University and is taught by Carter Zeni with help from Dr.

David J. Men.

In this course, you'll master the fundamentals of SQL,

00:15

including how to create, read, update, and delete data. You'll learn to model real world scenarios by designing tables, normalizing data to reduce redundancy, and joining tables using primary and foreign keys.

The course also covers how to automate queries with

00:31

views, accelerate searches, with indexes, and connect your database to languages like Python. The curriculum begins with the portability of SQLite before introducing the scalable power of PostQL and MySQL all through assignments

00:48

inspired by realworld data sets. [Music]

01:07

Hello world. This is CS50's introduction to databases with SQL or SQL.

My name is David Mailen >> and my name is Carter Zeni. >> CS50 itself is an introduction to computer science and along the way you learn about SQL and more.

This course dives all the more deeply into SQL

01:23

specifically and you can take it before, during, or after CS50 itself, whether you're a computer scientist, data scientist, programmer, or simply someone who's interested in databases. Now, we'll begin with a software called SQL light, which you can use to query a table of data, maybe a table of books,

01:40

for instance. You will then graduate and work not just with one table, but multiple and see how to define relationships among them.

Afterwards, you'll figure out how to design your very own database from scratch, learning about schemas and data types. And then

01:56

you'll see how to add data to that database, how to insert, update, and delete some data all together. Soon after, you'll see how to write alternate views for your data, new ways of simplifying your data for yourself and for others.

And then towards the end of

02:11

the course, you'll see how to optimize your queries, how to reduce the time that they take, although at the cost of some space. And then finally, to cap it all off, you'll learn other DBMS's, database management systems like MySQL

02:26

and Postgress that you can use to do all of this and more at a much bigger scale. Now, this is CS50.

[Music]

02:53

Well, hello one and all and welcome to CSA's introduction to databases with SQL. My name is Carter Zeni 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

03:11

those skills? Well, you may have heard it.

So, 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. Uh, you might think of, let's say, uh, Google keeping track of the sites you click on or the

03:26

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

03:43

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

03:58

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

04:14

columns. And this seems to store the uh stipens 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 could

04:31

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

04:48

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

05:06

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, and I want to organize my library.

Well, here I have book titles and authors, and I could certainly use a

05:21

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 kind

05:39

of next to each other like this. I could take my titles and my authors, put them right next to each other.

So, I have Song of Solomon by Tony Morrison, Good Night Moon by Margaret Wise Brown. And notice here how each book is one row, but every row has two columns where two

05:54

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

06:10

set. So thankfully now that we're living in this information age, we no longer have to use no longer have to use like stone tablets or perhaps pencil and paper to store our tables.

We have software now like Apple numbers, Google Sheets and

06:26

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

06:42

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. Um but there are

06:57

a few other ideas we think about too for why 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

07:14

of users. And with that kind of scale, you might be better served by a database to store that much information.

Another reason to move from spreadsheets to databases is this idea of being able to update data more frequently. Maybe you're a Twitter of the world.

You're

07:31

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

07:46

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, kind of 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

08:02

faster ultimately. So these three reasons among others are those you 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

08:18

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 are our four interactions that we'll do with a database, like adding some data, looking

08:35

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, way to interact with our database. So you might think of perhaps writing a um program on your

08:52

computer. You have some interface with which to write that program like VS code for instance or you might think of your own desktop on your own computer.

You have icons such that interact with the underlying operating system. In the same way we can use this software called a database management system or a DBMS for

09:09

short to interact with the database perhaps using a graphical interface or using a textual language too. Now there are a few uh varieties of database management softwares and these are a few of them here my SQL Oracle Postgress SQL

09:26

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 um MongoDB

09:41

there are other kinds of software other companies out there that make these ways to interact act with a database. This is again a non-exhaustive 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

09:58

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 Postgress SQL, MySQL, and SQLite. But

10:15

the downside is you are then responsible for actually implementing that database. Another uh 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

10:31

run. You could think of those like my SQL or post SQL 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

10:49

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 Postgress SQL too. So let me go ahead and talk about then SQL in this case.

You might notice that in

11:06

each of these MySQL and Postgress SQL 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 SQL or

11:22

SQL but what might SQL stand for? So I'm seeing it might stand 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

11:38

does have some keywords you can use to interact with a 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

11:54

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

12:10

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

12:27

liked on your platform. That's a question you can answer with databases and with SQL.

You might also think of um whether your numbers of daily users are growing or shrinking if you're working at a startup for instance. Even maybe might be working for something company like Spotify that could ask how could we

12:43

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 long listed quote

13:00

unquote for the International Booker Prize. The International Booker Prize, if you're not familiar, is a award given to books written around the world by authors from many countries.

And it's designed to uh award uh books of fiction are particularly good in some cases. And

13:15

every year the committee selects 13 books to include on a long list for consideration for this prize. And our database then has 5 years worth of long lists for the International Booker Prize inside of it.

We could use this database

13:31

perhaps if we were a librarian trying to find books for our library or even as a 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,

13:46

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

14:05

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 this database of long listed books for the International Booker Prize.

And although

14:21

we'll be using it here, SQLite is not uh just used in this course. It's used in a variety of applications.

You could think too of phone applications or SQLite is often used on those um devices that have much lower memory. You could think too of it being used on desktop applications

14:37

to simplify the process of storing data there too. You could even think of it being used on websites to help store information the user submits via a form for example.

So we'll jump into using SQLite but keep in mind that uh not just in this course

14:53

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 start using our uh environment and start using SQLite.

So I'll go over here to my computer and I will open up let's say VS Code where here you can see

15:11

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 long listed or considered

15:28

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 long

15:43

list.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 SQLite 3.

SQLite

16:00

3 long list db. And now I'll hit enter.

And notice how my terminal prompt changes. It's no longer a 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

16:17

my terminal. I can use 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

16:34

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

16:51

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 into my SQLite environment and

17:06

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

17:22

say select star and end my query. I have to tell SQL which table do I want to select rows from.

In this case, I know a table is called long list. So, I'll say select star from long list quote unquote.

And to end my query, I'll say

17:40

semicolon. And then finally, I can hit enter.

And notice how I get a lot of data back, right? 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. Instead of saying select

17:57

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 from my table like this.

I just know already that there is a column called title. So

18:15

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

18:32

more than one column, too. Let's say I don't want just the titles.

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

18:50

from this table. And I'll select them from the long list 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.

19:07

Now, this is going to back all of my columns so far, and there is a way for me to get back just 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.

19:22

>> Do I need to use the quotes around the words like you are? >> 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

19:40

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

19:56

single quotes for string names. Other questions, too?

Okay, I wanted to know uh where did we take all this information from? Like uh this data I don't know this list of book where did we take all this information

20:12

from? >> Yeah, 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 long listed books over the years. In this case, we have books from 2018 to 2023.

We'll also see

20:28

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, this site that aggregates reviews from people like you, people like me who rate books online.

So, we've taken data from a

20:43

variety of sources and combined it into one here. Let's take one more question too from Vinak.

>> Yeah. So, I wanted to ask regarding the syntax of what you used in the terminal.

So is the whole SQL light tree is case

21:00

sensitive because while using the syntax you used capital letters whereas can we use like small case letter as well? >> A great question.

So here I used capital letters for SQL keywords and lowercase for my table names and column names. Do

21:15

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

21:31

lowercase for SQL keywords? I think I could but the question is should I and and probably not.

So let me try this. I'll say select let's say title from I'm in the habit of uppercasing it.

So I'll say from and lowercase long list like this semicolon hit enter.

21:48

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

22:04

make it clear that this is a SQL keyword and not some other name overall. Okay, so let's keep going.

And we saw just a little bit ago that we could select title from long list and we would get back a whole list of titles.

22:20

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 in this column, but only to get back some take like a peak of what's inside this database. And for that I

22:37

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 three for instance or limit five to get back only

22:52

the top three or the top five 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 long list but limit to what number?

23:10

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, select title from long list.

But now, instead of hitting semicolon, I will

23:25

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 five.

So I'll

23:41

say select title from long list limit five semicolon. This then gives me just the top five titles in my database just in whatever order they were added to my

23:57

table. I'll see them in that order here too.

So using limit we can actually um try to get back a certain number of rows. But this isn't quite that interesting.

It's good for peing your data set. I think we've answered that question of what

24:12

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

24:31

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

24:47

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 uh control L to clear my terminal and I'll then try this query.

I'll say select title. Select

25:04

title. And why don't we also select author along the way.

Two columns here. And I'll select them from my long list table.

But I don't want all rows. I only want let's say those titles and authors that were long listed in 2023.

25:21

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

25:37

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.

25:55

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

26:13

equal to or not equal to 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

26:30

minute. But let me first ask now what questions do we have on how to use where or using select so far?

>> What are the subsets of SQL? >> A good question.

Are there subsets of SQL? So um there are in fact SQL or SQL

26:47

was defined by the I believe it's the ANZI 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 management system that you actually use. So for SQLite, we're using a subset of

27:03

SQL that works with SQLite. Similarly, if you were using another software like Postgress SQL or MySQL, you could use another subset there too.

Let's take another question from Teaos perhaps. >> Sir, I want to know that can we add 2022

27:19

and 2021 in a terminal? >> Yeah, good question.

Could I um perhaps try to filter by both and 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

27:34

we can combine conditions using where to. 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

27:50

those. Let's say I want to find books that are written by um 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 a hardcover format.

They kind of tend to be more expensive and so on. So, I don't want hardcover

28:05

books. Well, I could try a query like this.

I could say select title and format where format is either hard coverver or paperback from my long list table. And now I'll say where the format

28:21

is not equal to hard cover semicolon. So notice here I'm using single quotes for hard cover.

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

28:38

format, long list, title, etc. 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 hard cover. Well, I could

28:54

also use in this case the kind of 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 sort of reveal what I just previously typed.

I'll then tab back over and say not exclamation point equals but less

29:11

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

29:27

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

29:43

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

29:58

do not this operator here but instead use not. So I might in front of where say where not format equals hard cover.

So I have a condition format equals hard

30:13

cover. But now I'm going to negate it.

Take the opposite of it and get back the very same results here too. Okay.

So to the question earlier how we can combine these conditionals. Let's try

30:29

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

30:45

to you, too. Let's try looking at some of these over here.

So, here we have these called and, or these parentheses here, too. So, using and and or I can change condition.

I can chain

31:00

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

31:16

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 title

31:31

and author from my long list table. Now I'll say where the year is 2022 as we did before or perhaps the year is 2023.

31:46

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 the year is

32:04

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

32:20

using our parenthesis as well. Maybe I want not just these books, but also those that are formatted in a hard coverver format.

So I'll say clear my terminal, control L again, and select title as well as let's say format

32:38

from my long list table. And now I'll hit my new line to sort of extend my query without wrapping it around my terminal.

I'll hit enter and I'll say where the year is 2022 or the year is 2023. That's one

32:57

condition and I can denote that with a single set of parenthesis here. I also want it to be true that the format is not hard cover.

So now I'm adding another condition in here. Now I'll say semicolon, hit enter,

33:14

and I'll get back only those books are published in the paperback format in 2022 and 2023. Okay, so let me pause again and ask if there are any questions so far on how we've been using where and select and

33:34

other conditions as well. I would like to know about uh the uh we can able to list out the top titles available database like you have mentioned the title order where we can know about it

33:51

uh what are the titles available in the database using command to know >> yeah so here I've been using these column names called title and author and I think the 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

34:07

my database. What columns are inside of it.

For now, just take it on, you know, you know, my my own word that I knew what was inside this database before I actually started querying it. We'll see later on how you can take a database, understand the columns you have there, too.

A good question. Let's jump into

34:22

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

34:38

know in this table I have not just authors of books, but translators of those books. Often books for the international book of Prize were translated from some other language into English.

But some weren't, or at least they didn't have a translator that was

34:54

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

35:09

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 not

35:27

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 um 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,

35:44

let's say, title and translator from my long list. 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

36:03

should see two books titles are the perfect nine and the enlightenment of the green gauge 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

36:19

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 just a little bit.

I can see

36:35

that I have both titles on the left hand side and translators on the right hand side. All of these actually exist.

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

36:51

or is not null. So let's come back over here and figure out what more we could do with some of these queries.

We've kind of exhausted our work with um some of our you know conditions like chaining them together and using null and so on. But one more thing we could do is trying

37:08

to use this idea of matching some kind of pattern in my database. So maybe I'm a uh you know 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.

37:25

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

37:41

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.

37:56

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

38:12

long list. 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

38:30

let me explain what this is doing. I have here a select query asking for the title column from my long list table.

But I'll only get back those rows where title is like percent love percent.

38:47

Well, what does that mean? Well, the percent remember matches any string of characters.

It could match ABC 1 2 3. As long as it some any string of characters comes after and has love, I could match that uh value here.

Similarly, the percent sign after says anything can

39:04

come 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 running this query then and come back over here. I will hit enter on my query and I'll see

39:21

I get back four books, Love in the Big City, More Than 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 matched love up front and then had any string of

39:36

characters coming after it like this. For this one, I had more than I love my life.

Uh 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

39:54

find only those books that have the at the very beginning of the title. Let me try this.

I'll say select title from long list. Then where let's say the title is like

40:12

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 in

40:27

the title wherever but now the at the very beginning of this string and I see perhaps a 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

40:43

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

41:01

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

41:17

get back by running this query? I have the percent, but what other words might 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

41:34

might get back those book titles that have not just the at the beginning but also let's say there or they or so on. There are many uh words that begin with th e.

And if I have the percent sign right after it, I might match one of those words like y or re e or so on. So

41:51

I didn't have any of those titles in this database, but you could 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' butthe' in a space and match any characters after that to make this query better designed in this

42:09

instance. Okay.

So let me pause here then and ask what questions we have on using percent along with like. >> Can we use 2% signs for between two words?

42:24

>> Yeah, I think so. So let me go back to my kernel 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 um select let's say title from long list where maybe the title has something

42:45

like maybe it begins with 'the'. So I'll say where title like uh 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

43:02

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

43:18

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 I if I did, I would see them here with 'the' at the beginning and love

43:34

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

43:50

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 uh this one called py and I actually keep forgetting how it's spelled.

I don't know whether it's p i e

44:07

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 long list where in this case title is like well I know that it starts

44:23

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 re sing single quote semicolon.

And now I

44:39

could try hitting enter and I'll see I get back this title called py. 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.

44:56

Okay, 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. >> Yeah.

So Carter, I wanted to ask you that as you use the underscore sign here, so can we for multiple characters,

45:11

can we use multiple underscores in order to find something in the database? >> 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

45:27

could work with. I'll say maybe select title from long list to get back all the books in this table.

I'll hit semicolon and maybe I will go with h um let's try this one uh called till.

45:44

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 long list where my title

46:01

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 underscores. So match any book

46:17

title that has T and then any three individual characters after it. I'll hit enter.

And I'll see I get back till 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

46:34

maybe I'll accept you know five or six characters like that. Hit enter and I'll see whoops I didn't complete my query here.

Let me just try it from the top again. I'll say select title from long list where title is like let's say ty

46:49

hit semicolon. And now I get no matches.

So there is no book in this database that has TY and then let's say three or four underscores for any character after it. Okay, so this covers our use of like,

47:08

but let's keep going and building more complex conditions to find even more um answers to questions we might 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

47:24

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 long list. Now, I could try to find those books that are written or nominated in 2022 and 2023.

But let's

47:43

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

48:00

or year is 2021. And let me make a new line again.

Or year is 2022 semicolon. Now before I run this query, let me ask our audience, what strikes

48:16

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

48:31

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

48:47

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 two.

And we can combine these with our and and our or we saw before to get back

49:04

in this case some set of of rows that match our 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.

49:22

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 long list. But now I want those rows where the year is greater

49:40

than or equal to 2019 and the year is less than or equal to 2022 semicolon. I'll hit enter.

Notice how I get the very same results. So I get all those same rows, but now my query is

49:56

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 too.

Let me go back to some slides and show you. We could use these keywords between blank and blank where this can be some

50:14

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

50:32

try this one. I'll go back over here and I will now try select title from year from long list where the year is between between 2019 and 2022.

50:48

Same results now, but just a different way of writing this same query. Now, what else could we do with these ranges?

Well, as we've said before, these books actually have some ratings involved. These ratings are crowdsourced

51:03

from Goodreads, the 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 long list and I could say

51:19

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

51:35

combine conditions. So I know that these books have a certain rating, but how many votes did 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.

51:51

So, it's back up top. Select title and uh rating and the number of votes that these books got from let's say our long list table.

Now, I want to find those that have a rating of greater than 4.0

52:09

you know, and let's say a number of votes, a number of votes 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 hit

52:25

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 each every the vote every every vote row has a vote total greater than 10,000 in this case. So a good way to

52:43

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

52:58

let's say title and pages from my long list. 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

53:16

they were first published. So let's pause here and ask what questions we have on these range conditions.

I just wanted to check if um for a for a proper query in this case um to be able to run operations they have

53:33

to be integers in the database. Um and I my second question is for when we're matching a string um is it case sensitive or not?

>> 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

53:48

maybe should I use and the second one being could I match strings like case insensitively. So for the first one um 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

54:06

queries. Um for now I made this database so I just know that my my year column is an integer.

My ratings column is a 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 uh matching things

54:21

case insensitively. Let's actually revisit like just briefly here to show you what that can do.

So I'll go back to my terminal and let's say uh 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

54:37

are. So I'll say select let's say title from um long list and maybe I'll want to find that book p again.

So I could say where title is like py but all in lowercase. Now I'll hit enter and I'll

54:55

see I do get back p. So even though I said where title is like lowercase p y r e I got back capital p yre e.

Now this is in contrast to saying where title equals lowercase p. Let's try that.

I'll

55:12

come back over here and I'll say again select title from long list but now where oops where title equals quote unquote p semicolon I'll hit enter and

55:29

now I see no results so in this case the equals is going to be case sensitive case matters in this case but like is case insensitive Okay, why don't we keep going then and let's

55:46

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

56:01

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

56:19

could put them in alphabetical order or in order by 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

56:34

us in a resulting query. So, I'll go back to my 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 long list.

Enter.

56:53

Not on my 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

57:09

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.

57:28

So based on this, what do you think the default ordering of order by is? See it might be from least to greatest.

So we saw here that we have rating being pretty small, but we said order by rating. So it starts from small and goes

57:45

down to large. So we need to fix this in some way.

Let's introduce a new addition to order by to have us um 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

58:03

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

58:20

let's try using order by but now with this other keyword called deesc for descending. Here I'll go back to my terminal and let's rewrite this query to include deesc.

I'll say select title and

58:35

rating from long list. 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 ctr L. Now I'm back at the top.

I'll say select title, select

58:52

title and rating from long list where actually not where we're not filtering yet. I'll say order by rating but not by ascending by default for going from least to greatest.

I want greatest to

59:08

least. So I'll say deesc 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

59:23

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. Like it seems there's a tie to break here.

If I look at Stillborn and

59:39

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 of

59:55

votes this book actually received on Goodreads. 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 long

00:11

list. 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

00:26

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

00:45

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 select title and rating. Let's make sure we can see the votes here, too.

So, select title and rating and votes from long list. Hit enter on my query.

Now, I'll say order by rating

01:02

and votes. Then I'll say limit 10.

And here I'm just hitting 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

01:19

4.14 along with um stillborn. 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.

01:37

ordering by one column or multiple and how we can sort data like this. >> Sir, I want to know that can we write rating to 4.93 to uh 4.9?

>> Yeah, good question. So, I think if I understand correctly, how can we select

01:53

a rating or try to find a rating that's like equal to 4, you know, 92 or things like that? Let's try that here.

So, if I want to find a particular rating, I could simply use my uh wear friend from before. I could say select let's say title and rating and maybe I could try

02:11

to find a particular rating for a book from long list. I could say then where this rating is equal to let's say 4.932 semicolon this if this book exists it'll get it back here.

So I'll hit enter and

02:26

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.

Okay, other questions too on how we've been able to sort our data and use order

02:43

by. Will desend work on a string on an alphabetic basis or do we need to have a special conditions for alphabetic characters?

>> Yeah, great question. So, how could we use order by with some characters or strings or some text in our database?

03:00

Let's try that one out too and see how that works with ASC for ascending and DEESC 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 simply sorting our books alphabetically for let's say our

03:16

library. I'll say select title from long list.

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

03:34

be ordered alphabetically. So here we have some uh 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

03:51

happens. I'll go back over here and I'll try the same query but now using DEESC.

Select title from long list order by title now in descending order. Hit enter.

And now I'll see these titles in

04:08

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

04:24

alphabetical order or in reverse alphabetical order. Okay.

So let's show a few um other uh uh you know concepts here we can use alongside of these orderings. One thing we could also do is try to find uh more

04:41

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 in

04:58

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

05:13

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 or the maximum rating or finding

05:29

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 um trying to find the average rating from my long list.

Well, I know just from experience

05:46

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 long

06:02

list. 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

06:18

some parenthesis 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

06:35

average rating. We have 3.7537179471795.

So 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

06:52

probably improve the presentation of this in some way. So I could probably round this result.

Like I have 3.75 371. We could probably stop after two decimal points, right?

Just simply like 3.75. So I could introduce some new keyword

07:08

here. This one to round 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

07:23

and then take average of rating and round to two decimal points from long list semicolon. So now this query decides to first find the average of the

07:39

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

07:55

round to. 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 we'll see we do get back 3.75. But there is still one thing to improve

08:10

here. When I write this query, I see this ugly title name, round, average, rating, 2.

I wouldn't send this to my boss or somebody else who I work for, 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

08:26

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 keywords, but to give it some name I could use instead. So, for this, we'll introduce a brand new one.

new brand new keyword

08:42

called as. Let's try this one too.

I'll come back and I'll say select again round average rating, 2. But now I'll select it as let's say average rating.

And now

09:01

before I actually finish this query, let me try to bring it to the top of my terminal. We can see it all in one go.

I'll backspace this and I'll say select select um the rounded version of the average rating rounded to two decimal

09:17

points as let's go ahead and call this one average rating. Now I'll hit enter and I'll say from my long list table semicolon.

Now I see it's much prettier overall. I have no longer these SQL keywords but instead just average rating

09:34

as my column name. Okay.

So let me pause here and ask questions then on using average or using um round or using as in these cases. I'm wondering do these sort of commands

09:49

have like a function like are these commands called something like data type called something are these commands also do have a name? >> Yeah.

And can I ask are you referring to like the AVG like count like sum those kinds of things or Yeah. So these

10:07

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

10:24

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. Okay, so let's keep going then and try to start counting um some other rows and use our other aggregate functions here.

10:40

I'll go back to my terminal and so far we've seen average as well as um 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

10:56

say the max rating from my long list semicolon hit enter now I see the highest rated book was had a rating of 4.52 well what about the minimum rating I could use min here too I could say I select let's say min of my rating column

11:14

from my long list table I'll hit semicolon and I'll see 3.05 05 that is the lowest rated book I have in this in this set. Well, as we've seen, let me try to um view this for you all.

I could say select um title and votes from my

11:33

long list. It's like title and votes for my long list.

Here I have many books with many user generated votes. Maybe people on the internet decided to rate this book out of five and maybe uh go Went Gone got about let's say 592 votes.

11:50

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 return it back to me in a single cell.

So I'll use sum here. I'll come

12:07

back and I'll say I want to find the sum of my votes column. I'll say select let's say um the sum of my votes column from my long list table and then I'll just hit enter semicolon enter.

And I'll

12:24

see over 600,000 people offered a vote for each of these books that were long listed for the International Booker Prize. Now that' be a few more here.

So let's check out what else we have left to do in our aggregate functions. We could

12:40

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

12:55

saw a little earlier. I could say select count star from long list.

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

13:12

rows that I have in my database. So I'll say count star from long list 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 long list

13:30

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? If you were to raise your

13:47

hand and try to guess at this. Why do I have 78 rows but 76 translators?

>> Um, hello. I actually had raised my hand for a question.

>> Yeah, go ahead. >> Also, I wanted to know whether the max and the min function can be used for

14:04

finding the longest or the shortest string as well or do you have a different command for that? >> Ah, good question.

Could we use max and min to find the longest or shortest string? That's a 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

14:20

try to use max and min with some book titles. So, I'll say select um let's select the max um title.

And at the same time, why don't we select the min title as well and I'll select these from my

14:35

long list table. I'll hit semicolon.

And now, let me try this out. I'll get back wretchedness and a new name.

Septology 6 through 7. Now, there's a few hypotheses here.

It does seem that our max title is

14:53

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

15:09

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 the book here down at the bottom.

So max seems to give us the lowest alphabetically which is kind of

15:24

contradictory with um titles here or strings and min gives us the earliest in the alphabet using this a as well. Okay, so good question.

Let's come back to our counting here. So let's go back to my terminal and again we had in this

15:40

case 78 rows but only 76 translators. So again if I did select count star from let's say long list then semicolon I got back 78.

But if I do select count of translators

15:57

translator from long list I get back 76. Let me ask again.

Why do we have 78 rows but 76 translators? You're free to say it.

Okay. So, I'm seeing maybe we have

16:12

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

16:28

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 or that exist in our table.

Okay, let's look at one more example here for counting. And let's try this.

16:47

Let's say I want to find all of the publishers in this database. I'll say select count of publisher from my long list and I'll hit semicolon.

So you might think that I have 78

17:03

publishers in this long list, but would it be accurate if I were to say I have 78 different publishers in this long list? Could I say that?

I'm seeing no, right? I couldn't try to

17:18

count up these publishers and then say I have 78 different ones. I might double count a 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 long list.

I'll

17:34

select publisher from long list. Hit semicolon.

Whoops. And now I see something a little odd.

Let me scroll back up and maybe ask for a raised hand here. Why

17:50

might I get this odd result? >> Because of the quotes.

>> Yeah. So I I think I mistyped some of my query here.

I said uh it looks like publisher instead of publisher. And in this case, SQL will give me kind of what

18:07

I asked for. I said select pubser from long list.

And says okay, here it is. But that column doesn't exist.

So it kind of 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

18:24

this one from long list 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 uh Harville secker multiple times here. I have similarly uh Macose press multiple

18:40

times as well. So if I counted up these publishers, I would get each one counted one time.

But if 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

18:56

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

19:12

from long list 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

19:28

then try to say select let's say count of publisher select count of publisher from uh count of distinct publisher for instance count distinct publisher

19:44

oh typo publisher from long list semicolon and I'll see I have 33 distinct publishers. Okay, so this just about brings us to the conclusion of all of these new SQL

20:01

keywords here. We've seen so far we have several here to use, but let's figure out how to actually exit this prompt.

So you might be in your SQLite prompt right now. If you want to leave it, you could also use this commandquit.

Dotquit is not a SQL keyword. It's a

20:17

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

20:33

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 could get back not just all of our rows but only some of them using our wear clause here along with a

20:50

condition. We could have multiple conditions having not just one but perhaps two like uh like let's say here condition zero and condition one 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

21:06

here. We could again use and and or 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 introduction to querying. And so

21:21

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 it into multiple tables. How do we find information on publishers or books or authors, too, and

21:38

how do we try to put that in a table that can represent 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.

[Music]

22:06

Well, hello one and all and welcome back to CSG's introduction to databases with SQL. My name is Carter Zeni and last we left off, we learned all about querying, how to ask questions of data in a single table.

Today though, we'll take one step

22:22

forward and we'll have databases that have not just one table inside of them, but actually multiple tables. Tables for authors, tables for books and for publishers and so on to represent all these people who are part of the book industry.

And you too can apply these same skills for whatever you want to

22:39

represent as well. So let's pick up where we last left off, which was with this database of books.

I'll come back to my computer over here. And if you recall, we had this data set that was the books that were long listed for the international booker prize.

To be

22:55

longlisted means to be nominated for some prize in a given year. And the international booker prize is awarded every year to one book, but 13 are nominated.

So in this data set, we have 13 books for the past five years that

23:11

were nominated for this prize. Now, I'll go ahead and open up this database once more.

And if you remember, we used this command last time to open this data set. SQLite 3 space the file name where our file name was long list.

DB. And this is

23:27

one example of our database management system. This one called SQLite and this one being the third version of SQLite.

So let's reopen this database and let me show you what we have now in store. I'll type SQLite.

Oops. So I'll type SQLite

23:43

sqlite 3 and then long list db and I'll hit enter. Now my prompt changes.

I no longer have this dollar sign. I now have the SQLite prompt.

And here's where we saw last time I could write my SQL queries. Well, before we start, let's

24:01

see the changes to this database. So if I want to get a feel for the tables that are inside of this database, I can use a command.

this one called tables. Notice this is not a SQL keyword.

This is a command particular to SQLite to show me

24:18

what tables are inside this database. So I'll type enter here.

And now I see I have more than one table. I have a table for authors, a table for books, for publishers, and even more.

And now that we're working with not just one table,

24:35

but multiple, we're actually moving into this idea of a relational database. So no longer do we have a database with only one table inside of it.

We now have multiple. And these tables presumably have some kind of relationship among

24:50

them. You could think, for instance, of an author writing a book or a publisher publishing a book or even a translator translating a book.

So we'll have these tables and these relationships among them. So let's look at one example of

25:05

this kind of relationship among tables. I could have a table of authors and a table of books.

This simplified for now to one column in every table. Well, my author's table I have names.

In my book uh table I have titles. And we seem to

25:21

be moving in the right direction. I could store more information about authors and books.

But I've also created some problem like how do I know now who wrote which book? Like earlier we saw that you know maybe Eva is right next to

25:36

Boulder in a single table for authors and books and we know that Eva wrote Boulder that way. But now if I look at authors I only see Eva and no information about books.

So we seem to have this problem here. Well one solution uh might be something like the

25:53

honor system. I mean, I'm honest and so are you.

We could say that, well, maybe the first row in authors will always correspond to the first row in books. So, we always know Eva wrote Boulder because Eva is first in the author's table and Boulder is first in the books

26:08

table. Similarly, let's say Han wrote the white book because Han is in the second row and the white book is in the second row in books.

But I have to imagine, I mean, as good as I am at being honest and transparent, maybe I make a mistake. Maybe I add a

26:24

row to books but not to authors. Or maybe I add a row to authors and not to books.

I could change data and not update it to adhere to that standard. So let's actually take a step back and maybe think, well, is it better then to have this kind of situation where we did

26:41

have one table with authors and books? It might be.

But let's think. I mean, this could work for a while.

But let's say Olga is a prolific author. They like to write not just one book, but two.

They wrote not just flights, but also

26:56

the books of Jacob. Well, let's say Olga writes another book or another one too.

What might be the problem with this arrangement now? And feel free to raise your hand too with your ideas.

Why run into some issues here? If we only have

27:12

one table of authors and books, >> there is some redundancy, some repetition. >> Yeah, a good idea.

So, you might notice there's some redundancy here. I mean, Olga is in here twice, which is maybe okay if Olga wrote two books, but again,

27:28

let's say Olga wrote three or four or five. Olga will be in here that many times.

And so, we could probably do a bit better than this. And let's actually just say this isn't going to be the most efficient idea for us, let's go back to these multiple tables here and think

27:44

about how we could relate these two tables one to the other. Now, before we get into the details of how we might do this technically, let's think about the kinds of relationships we could have between authors and books.

Well, maybe in our world, we have one author that

28:02

writes one book. And maybe we could say every author only writes one book.

And similarly, one book is always written by one author. This could be true for some books and some authors.

But arguably, it's not the best assumption, right? We

28:18

probably have authors writing more than one book or books being written by more than one author. So, as it stands now, we could call this kind of relationship a oneto-one relationship.

If this is our idea of authors and books, one author writes one book and one book is written

28:34

by one author. This is a oneto-one relationship.

Well, as we know, authors can write more than one book. Olga, for example, didn't write just flights, they also wrote another book.

And so, we could say,

28:50

well, Olga wrote not just this book up top, they also wrote this book down below. And now we're moving from a one to one relationship to what we'll call a one to many relationship.

There are many books that one author could write.

29:08

But of course, is this the full picture, too? I mean, some books are only written by one author, but presumably some books are written by multiple authors.

So, we need to enhance our picture even further. Let's try that.

Let me add

29:24

another author into the mix here. This author wrote this book along with Olga, let's say.

So notice how here we have one author writing more than one book. And we also have one book being written by more than one author.

Well, this is

29:42

called a many to many relationship. And it's called that because we have many books that could be written by many authors.

So these in general are the kinds of relationships we'll have amongst our tables in a relational database. One to one, one to many, and

30:00

many to many. Now thankfully there are some tools we can use to visualize these kinds of relationships.

We have at our disposal something called an entity relationship diagram. Something a bit like this.

It's also called an ER diagram for short. And

30:19

let me propose just for now that this is the diagram for our database. We have authors and publishers and translators, books and ratings.

These are our entities in our database. The things we're trying to represent.

30:35

Now, you'll notice though that there's something more than just these boxes that have author, publisher, translator, and so on. There are all these lines among them and they have verbs like wrote and published and translated.

And maybe that makes sense to you, but at

30:51

first glance, like there's one line here that has three lines coming off of it, one line going across. This one has a circle on it.

I mean, what does all that mean, right? This is kind of confusing at first glance.

Well, this actually has some order to it, some rules that we can

31:06

use. And let me show you what those rules are.

So here we have what's called a crow's foot notation. A way of trying to represent one to one, one to many or many to many relationships using just lines and in some cases circles.

So here

31:23

if you ever see this line with a circle on it, you could think of zero. This doesn't have to have anything related to it.

You could think of this one with a bar going perpendicular as one. something with this arrow has to have at least one thing that relates to it in

31:38

some other table. And this one down below, the one that looks like a crow's foot, well, this one means that some entity has many that could be related to it in some other table.

So, let's try this with an example. Here we have again

31:54

our authors and our books. And here we can say that an author wrote one book.

We read this left to right. In this case, one author can have one book associated with them.

We know it's one because we see this bar over our line

32:09

here. Now, we know too that books, at least in our world, could be written by one author.

They must be written by at least one author. So, let's try this.

I'll include that arrow on the other side, too. And I'll read this right to left.

A book has to have at least one

32:27

author. Now I could add in more symbols too.

We know that books could have more than one author and an author could write more than one book. So let's try enhancing our diagram even further.

I could say

32:42

adding in these crows foot notation with the multiple multiple lines here. Now I could read it like this.

An author going from left to right can write at least one book but they could write many books. They could write one or many books.

And similarly, a book could be

32:59

written by at least one author, but certainly also multiple too. So this is one example of an er diagram for our database.

And hopefully now you can see uh a bit more of this making sense to you. So we see that authors

33:16

here could write many books. Book could be written by many authors.

Books actually don't need to have a translator. They could have zero to many translators for their book here.

But in general, a translator should write or should translate at least one book or

33:32

possibly many of them too. So let me pause here and ask what questions do you have on these table relationships and these diagrams that we call ER entity relationship diagrams?

33:48

>> Yeah. So K I want to ask that for any given database how can we predetermine whether how the relationship between the tables are going to be because here we know that the author has some relationship to the books but how can we determine whether those relationships

34:04

are existent in the for first place >> a great question if we have some database how do we know the relationships among those entities or those things that are stored inside of it well part of this is really up to you I mean when you're designing designing a database. As we'll see in a future week

34:19

of this course, you have decisions to make. Do you want an author to be able to write only one book or multiple?

For instance, often when I design a database, I might write a diagram like this. So, I could show this to somebody else who can know what tables I have and

34:36

how they're related to one another. So if you're designing, you have the choice.

But if you're taking data from someone else, you could ask for a diagram like this that could show you those relationships among those tables. Let's see what other questions do we have too on these tables and their

34:54

relationships. >> How do we determine that relationship?

I mean we know that it exists but how we assign which author wrote which book or multiple books or which book were written by what authors. >> Yeah, great question.

So how do we try

35:11

to assign then these relationships perhaps in our database? Like we know that an author could write multiple books but how do we present that in our table?

Let's say um well for this let me just transition to the next part of what we'll talk about which is going to be this idea of a key. So keys are this

35:27

fundamental idea in databases that can help us relate tables one to the other. And for this, let's actually do a bit of a an example, a bit of a roleplay here.

So I will be your librarian today. And let's say that you're looking for a book.

This one is called The Birthday

35:44

Party. And I say, "Okay, I'll find you this book.

I'll go to my computer here and I will try to find you the birthday party." Here though, I have two options. I seem to have The Birthday Party by Wendy Danfield and The Birthday Party by Lauren Movenier.

So, let me ask you, if

36:00

we're on the phone perhaps, which one do you want? Hearing feel free to to vote.

Which book should we search for? The one by Wendy or the one by Laurent?

36:16

I'm hearing maybe going for Laurent. So, let's try Laurent over here.

We'll search for the one by Lauren. So, The Birthday Party by Lauren Movenier.

Okay. Okay.

Let me find you that book. But, well, now there are still two books I could choose from.

Do you want it in hardcover or paperback? And so, I could

36:33

ask you again, well, which one do you want? Do you want it in hard coverver or paperback?

And again, feel free to vote. Which book do you want?

Okay, I'm hearing we're going for I think closer to

36:50

paperback. Let's try that one.

So, I'll search for The Birthday Party by Laurent Movenier in paperback. And there are still two of them.

Like, which one do you want? We have first edition and we have second edition.

So, I would ask you again, which book do you really want?

37:06

And you could keep voting here, but for the sake of just keeping this going, I mean, let's pause here and think of a better way to do this, right? My life as a librarian would be much easier if you had given me a simple number.

This number is called an ISBN.

37:25

And it turns out that every book has a unique identifier called an ISBN. Now, what is an ISBN really?

Well, if it's the case that every book has a unique ISBN and I can only find one book

37:41

if I have an ISBN, this is what we call in database terms a primary key, something that is going to be unique for our book or for any item that we have in our table. Now, here's one example of an ISBN.

Notice how we have 978-1-840,

38:00

whatever it is. We could use this to uniquely identify at least our books, but we could extract this away and use this in more than one place.

Not just for books, but for publishers, for translators, for authors as well, giving them each some ID that uniquely

38:16

identifies them. So, here's one example of our table.

Let's say that I have a table of books and I used to only have titles, but now I actually have more than a title. have an ISBN to uniquely identify each of these books.

And this

38:32

is handy for me if I wanted to look up some book and know exactly which one I was talking about. Now, these are all fine and good in tables.

They're very helpful. But we could do a little more with them.

We could get to this question of how do we actually relate these

38:47

tables together? We could use these primary keys for that.

Now a primary key becomes useful for that kind of work when we treat it as a foreign key let's call it. Well what is a foreign key?

Well a foreign key is simply taking a

39:03

primary key from one table and including it in the the column of some other table. So here we have in the ratings table the ISBN column still this is the primary key for the books table but now notice how it's inside the ratings

39:20

table. This is now a foreign key because it is outside of this books table and inside the ratings table.

And what could I do with this? Well, I now see if I want to find all of the individual users ratings for for Boulder, I could look

39:36

what is the ISBN of Boulder and then which ratings correspond to that ISBN in some other table. So this is an example how I could represent these one to many relationships.

I have here a table that

39:52

has an ISBN or a list of ISBNs and I have another table that can tell me which ratings correspond to this ISBN this primary key in this other table here. Well, that solves one picture here.

How

40:07

can I represent these one to many relationships? But we could also think about how we do the many to many relationships as well.

And for this we'll need to think about how do we can improve our primary key here. So let me ask this is an ISBN but if I want to put

40:26

this inside of a computer like over and over and over again as a primary key the unique identifier what problems do you think I might run into here if any? What problems would I get by using this ISBN as my primary key?

you would like

40:43

run out of memory. >> Run out of memory.

It's a good guess. Like if I had a lot of ISBNs, let's say I had not just 78 books, but I had literally millions of books.

I mean, this is a lot of data to store. If you treated each of these as an individual character, that's about one bite, eight

41:00

bits per character. That's, let's say, 13 plus four dashes, 17 bytes per per ISBN, which takes up a lot a lot of space.

So, what if I instead said I'll remove the dashes like we did before and I'll treat it as a number. Well, in that

41:16

case, I might also have a problem. Like, let's say an ISBN begins with zero.

And if I convert that to a number, I'll lose that beginning zero because it doesn't really mean anything in the context of being a number. And now I've lost my unique identifier.

So, we could improve

41:33

this even further and we could actually construct our very own primary key. This one, let's just say, will be called one for this book.

And some other book we could call it uh two. Some other book maybe three.

And it doesn't quite matter

41:48

what number we choose so long as this number uniquely identifies the book that we choose. And we don't use that number for any other book as well.

So let's actually rethink this relationship of one to many here. Let's say let's not

42:03

just use ISBN. This is a lot of space to use.

to someone point earlier. Let's go ahead and use the ID.

Let's make our own primary key in our books table and use that as the foreign key in ratings. So, we freed up a good amount of space here.

42:19

And notice, too, that maybe Boulder has the primary key, the ID of 1. The white book has the ID of 74.

Well, doesn't matter if it's 1 2 3 or four. It doesn't.

As long as 74 uniquely identifies the white book, we're okay.

42:36

So here we have an example of a one to many relationship, but now improved. And with this improvement, we could then think about many to many relationships as well.

I could take the same idea and

42:52

use it to relate authors and books. So let's think about this relationship.

Now here too let's say I have a table of authors and a table of books and notably each has a primary key. So I could use that primary

43:08

key for authors and for books and perhaps put it in some new table. This one let's say is called authored and I'll have one column for author ID and one column for book ID.

And based on the context clues here, what might it mean

43:25

if I looked at the authored table and saw 23 next to one where 23 is in the author ID column and one is in the book ID column to raise your hand. What would it mean for me to see those two numbers next to each other?

43:44

Yeah, it would mean that uh the author that is that has the ID of 21 uh 23 has authored the book one. >> Yeah, a great instinct here.

So if I look at author ID, I see 23. Well, I could consider that maybe the author with the ID of 23 wrote the book with

44:01

the ID of one. And I'll concede there's some extra work here.

Like I have to figure out now who has this author ID of 23. Well, turns out if you look in the author's table, it's Eva.

Similarly, for the book ID, I now have to go through the process of figuring out, okay, well,

44:17

the book ID is one. What then is the book title?

And I'll look for that in my books table over here. So, it brings in a bit more work for me, but ultimately I think we've solved our problem from the very beginning of lecture trying to relate authors and books.

We can now do

44:33

that using both primary keys and foreign keys. So let me ask then what questions do we have on these keys and how to use them?

>> So if the I can the ID of the author and

44:49

the book ID be the same? For example, can there be a author ID of one and a book ID of one or will they be mixed together?

>> A really good question and good thinking here. So let's consider that maybe an author ID matches a book's ID like both

45:07

authors both the author and the book have this ID of one that's actually I would say okay in our case because we know that at least one refers to authors and one refers to books as we'll see in future lectures too when I make this

45:25

join table also called a joint table or junction table an associative entity I could claim that this column called author ID references the primary key column in authors and that primary key only. I could do the same thing for the

45:40

book side. I could say the numbers in here reference the primary keys in the books table and those IDs only.

So I could actually have authors and books with the same ID so long as my tables know that if I have this column I'm

45:56

looking for author IDs. This column I'm looking for book IDs.

Great question. Let's go to one more here.

Um, >> I was wondering, it feels like having more and more tables because once you do this on a big scale, it adds up would use up a lot of space as well. How does

46:13

it uh how do you deal with the tediousness of it adding up? >> Yeah, good question here.

So, if we had a lot of these kinds of tables, wouldn't that take more space? And I think your answer the answer to that question is it would, but we do gain some things by being able to do it this way.

Okay, so

46:29

for example, let me go back to my authors and books table. Um, in our database, we'll see we have not just author names, but also the year they were born, the city they were born in, the place that they were um, living in certain periods of their life and so on.

So, we could have much more information in these tables that we couldn't store

46:46

as one table earlier because of these redundancies we saw a little bit earlier. So I think although this might take up a little more space, use a little more um tables, we can get around that as we'll see later on with some creativeness with our queries too to um address that that downside.

But a good

47:03

question, a good thought for trade-offs here. Let's go to one more here.

>> Uh so my question is that can the ID be updated and if so does it automatically get updated in the one that has both the ids together?

47:20

Yeah, good question. So, maybe we want to change the ID of some book or author.

Could we do that? Uh, I would say you could, but it's kind of a dangerous game.

I mean, if I changed Eva from 23, let's say, to uh let's so we change 27.

47:37

Well, I could get EVA confused with gauze. And I need to be doubly sure that when I'm changing these IDs, I'm making sure that I'm not making an ID nonique.

I'm not making it an ID that somebody else already has. And so for that

47:54

reason, we very rarely change IDs. In fact, we tend to just abstract them away.

So we don't actually know the ID of any particular author or the ID of a particular book. We just use them in our queries as we'll see in just a moment.

So wonderful questions here. What we'll

48:09

do is take a very short break and come back to see how we can use these same tools of keys to not just relate tables, but to query them too and find answers to our questions across these tables. We'll be back in just a minute.

Well, we're back and what we saw before was

48:26

how to relate our tables using keys. We saw that we could have tables of authors and translators and books related to each other using primary keys and foreign keys.

So what we'll do now is figure out how we can query these tables

48:42

using different techniques to that in this case might actually use primary keys and foreign keys within those same queries. Now one technique we could use is this one called sub queries also called nested queries too that basically put one SQL query inside of another.

And

49:00

they're useful in a few different cases but let's say I have one case of a one to many relationship. Maybe I have publishers that are publishing many books, for instance.

Well, I could use sub queries to help me answer questions about these kinds of relationships in my

49:16

table. To make it more concrete, let's say I have a table of books and I want to figure out which of these books were published by Fitz Caraldo editions, my favorite publisher.

Let's say what's the problem here? I want to find the books

49:34

published by Fitz Auto editions, but what am I missing? Feel free to raise your hand and answer.

>> Well, we don't have the name of the publisher in the same table and we haven't made the relationship with the other table to know the name of

49:50

publisher. >> Yeah, good observation.

So, I want to find the books that are published by Fitz Caraldo editions. And as you noted, well, there's no publisher name.

There's just the ID. So, I need to have some way of knowing what these IDs correspond to.

50:06

And and thankfully, I do have a way of knowing that. I have a table called publishers.

And this has a a column called ID and a column called publisher. And what do I notice?

Well, I notice that Fitz Caraldo editions has the ID of five. So, I could probably solve this

50:23

problem. But let me ask again, what queries might I need to answer this question?

And there are two queries that I need to do to answer the question of which books were published by Fitz Carlo auditions. What two queries are they?

50:40

>> I think it's select and where did you mean keywords or queries? >> Yeah.

So keywords would be great. So I need to have some kind of select involved.

I need to probably select something from publishers and something from books. So could I ask you what

50:56

would I select from publishers and what would I select from books then let's take either another hand or following up here. >> Okay.

So we would take the publisher. So select publisher ID from uh where

51:11

publisher equals the publisher name we're looking for. And then uh we would nest this query inside the other one.

So we can find the the name of the book published by this publisher since we found the ID.

51:26

>> Yeah, some good ideas here. And let me propose to do it a bit visually here first.

So I want to figure out first what is the ID of Fitz Caraldo editions? Well, I could write a query to find that and I'll say get back five for Fitz Caraldo editions.

Well, that's one step.

51:43

But my next step is then to say what books fit that publisher ID. So I'll write another query this one to determine which titles have a publisher ID of five and I'll get back let's say minor detail and paradus.

So let's make

51:59

this more concrete as you were doing yourself John Paul and let's write these queries out. Let's say I have this first one to determine what is the ID of Fitz Caraldo editions.

I could say select ID from publishers where the publisher is Fitz Caraldo additions and I'll get back

52:18

from this query the number five. Well, I could use that number in my next query.

As we saw visually here, I could then say select title from books where the publisher ID is five and I would get back many titles here. But here's

52:36

another question. Why is this not very well designed?

At least in the query I have here, what could I improve about it or what kind of smells wrong about it for you? >> They are not connected.

52:51

>> Yeah. So, Jean Paul, as you said too, they're not connected.

And here I have this ID of five, but I mean I don't want to remember this number. I mean, I don't want to have to keep it in my mind and like use it later on in my queries.

I ideally could use some other query to

53:07

find this number for me more dynamically. And so SQL supports this idea of writing a sub query, a query within a query.

For example, I'll take this and I'll convert it to the query we had before. I'll select title from books

53:25

where the publisher ID is equal to well the result of this query here. And notice how I have parenthesis.

This means the query furthest inside the parentheses will be run first. So first this query is run.

I get back five. Then

53:42

I could finish out my query and say select title from books where publisher ID is equal to five. So let me pause here before we do some practice in our SQLite terminal.

What questions do we have on these kinds of

53:58

sub queries so far? I was wondering what would happen with um those records that don't match in the subquery.

>> Yeah, good question. So if the ID doesn't match, is that what you're asking?

54:15

Yeah, I'm hearing yes. So if the ID doesn't match, um that's a good question.

So we can treat it as we would a regular old SQL query that we learned about um in last week. So we said select title from books where publisher ID is equal to let's say some value and we'll

54:32

then filter out all of those that don't have that particular value. So if we have a publisher ID of six or of three those won't be included anymore because we said where publisher ID is equal to five.

And now looking at this query too.

54:48

Well, let's say I might write select ID from publishers where publisher is or equal to Fitz Caraldo editions. Maybe that publisher doesn't exist.

There's no publisher with that name. Well, in that case, this query would return nothing

55:03

and therefore the next would also return nothing. So, we have queries dependent on the results of the other queries here.

So let's actually try this using our SQLite terminal to get a better feel for what we can do with this kind of um structure for our queries. I'll go back

55:19

to my computer and I'll hop into my SQLite environment. Let's say here I want to do a similar query but I want to find those books that were published by Maclo editions or Maclo press rather.

So let me try to attempt this one by one

55:36

going one step of the way and then finally combining my queries into one I could use more dynamically. So to our point earlier we have to first find the ID of Macose press.

Let me try to find that. I'll say select let's say ID from

55:54

publisher where the publisher is equal to none other than Macose press like this. and my quotes semicolon hit enter and hopefully oh I will see see an error

56:09

and based on this error let me ask our group here what did I do wrong >> judging by the error I think you just mistyped the table name

56:25

>> yeah so I probably mistyped the table name and we could probably parse this if I look at the error itself so it says parse error no such table publisher and that's true there is no table called publisher so let me fix this and run it again I'll say select let's say ID from

56:42

the publishers table plural and we'll say um where the publisher is equal to Macly hose press like this and I'll hit a semicolon here

56:57

and now fingers crossed this should work I do see an ID of 12. Okay, so Macose press the ID is 12.

Now I want to find the books that were published by this ID. So let me go to my book table.

I

57:12

will say select title from books in this case where the publisher ID is equal to 12 as we saw before semicolon. Now I'll hit enter and I'll see these are the

57:28

books that Maclost Press has published. But again as we saw before I could improve this.

I could make this better, more dynamic by nesting my queries having a subquery a query inside of a query. So let's redo this.

I'll start at

57:45

the end. Essentially, I'll say select title.

Select title from my book table where the publisher ID is or is equal to what? Well, let's say I don't know it's 12.

I have to write another query here.

58:02

So, to do that, I could open up some parenthesis. Is equal to a parenthesis.

And then I'll hit enter to continue my query. I'll uh indent four spaces.

space space space space just a style convention to keep things clean for me

58:17

to show this query is inside of this other query. What I'll then do is finish out this query.

I want to select I want to select the ID from the publishers table where let me just indent again

58:34

where the publisher is equal to uh not fit scroll auditions is equal to Mac leos press end quote and now let me close my parenthesis I have an open one here that I should close to show this

58:50

subquery is done I'll hit enter close my parenthesis. And now my entire query is done.

I can hit semicolon, hit enter again, and now I'll see the very same results, but more dynamic. No longer do I have to hardcode the ID.

I can make it

59:07

more dynamic, and I can actually find it using my very own query here. Let's try a different context, too.

Maybe I want to find all of the ratings for this book called In Memory of Memory. Well, maybe I'll try it step by step.

And what I'll

59:25

first do is try to find the ID for in memory of memory. I'll say select ID from uh let's go from the books table and let's say where the title is in memory of memory memory of memory end

59:45

quote semicolon. Hit enter and we'll see 33.

So this book has the ID 33. Okay.

Let's now look in our ratings table for this ID. I'll say select let's go for select rating from my ratings

00:02

table where the book ID is equal to 33 I believe it was. So now I'll hit enter.

And now I should see all of the ratings for this book. And there are quite a few that people have left on this book on

00:17

the Goodreads website. So let me again try to improve this and make it just a little bit better.

I'll be more dynamic about it and try to nest these queries. I'll say select rating from ratings.

We'll start at the beginning. We ultimately want ratings.

Well, I want

00:34

those ratings where the book ID is equal to some number, but I don't know what number yet. So I have to write my own nested query.

I'll I'll create a parenthesis like this. Enter.

I'll indent four spaces. Space space space.

00:52

And then I'll say I want to find the ID from the books table. From the books table um where this title is equal to in is equal to in memory of memory.

And

01:07

that is my sub query now. So, I'll close this out.

I'll hit enter. Close my parenthesis.

Now have a semicolon and hit enter. And I see those very same ratings.

Well, I could probably not get back the individual ratings. I want to

01:23

kind of average them over time. So, let's try that, too.

I'll say the same thing. Select rating from ratings.

But now, no longer do I want to select all the ratings. I want to average them.

So, I'll say select the average rating of this book from ratings. And now let me

01:40

go back over to my nested query and I will hit enter again and I will hit the up arrow to get back access to my earlier parts of my um my SQLite environment. I'll hit enter here.

Close it out. Semicolon.

And now I should see the average rating for this book.

01:58

Okay. So this is very handy for us.

We're able to kind of replicate those queries we would have done with a single table but now using sub queries. And so far we've seen this working for a relationship that is one to many.

We could also use sub queries with many to

02:15

many relationships. These tables that have many items over here and many items over there that relate in some way.

Let's try it let's say with authors and books. And I want to figure out let's say which author wrote flights.

Which

02:31

author wrote flights? So, let me ask what should I figure out along the way to get to the author who wrote flights?

Could you walk me through what I might need to know for that query? >> Uh, you need to know ID of author and of

02:49

that book and based based on that relation you have to join all three tables together. Yeah, I have to know ids and I have to kind of walk across these three tables as you're saying.

So, let's go back to

03:06

our tables and I want to know who wrote flights. Well, a good place to start to your point is to say let's start at flights and find the ID.

Well, the ID of this seems to be 78. Okay, let me then look in our authored table and let me

03:22

try to find the author ID that corresponds with 78. Well, I find 78 here.

What's next to it? Well, 58.

Okay, so the author ID who wrote flights is 58. Well, who is 58?

I'll look in the author's table and I'll see this is none

03:38

other than Olga. Okay, so let's walk through trying to make this query happen, but now using SQL keywords.

I'll start first with trying to find the ID of flights. I'll select the ID from the

03:53

books table where the title is equal to flights. Okay, that's fair enough.

Now, I could nest this query, put it inside another query to find the author ID of the person who wrote flights. I'll do a bit like this.

I'll select author ID

04:10

from authored where the book ID is equal to my prior query. Select ID from flights where title is equal to or from books where title is equal to flights.

And now I could go one step further. I could say I want to find the name of the

04:26

person who has this author ID. Let me nest again.

Let me put this query select name from authors where ID equals and put all the rest of it inside of the uh nested query here. So to be clear, I'll start from the middle, find the ID of

04:42

flights, then I'll find the author ID for that book ID, and then I'll find the name of the author who wrote that book. Okay, so a good number of steps here, but let's try it out ourselves in our SQLite terminal.

I'll come back to my

04:59

computer and here maybe I want to find all of those books or maybe not all those books. I want to find the author of a particular book you might be familiar with called the birthday party from our librarian conversation a little earlier.

Let me say I want to first find the ID of the birthday party. I'll say

05:16

select ID from books where title is equal to the birthday party semicolon enter. Now the ID is 8.

Okay, let me

05:32

keep that in mind and actually I don't quite need to know this. Let me try to nest this query inside some other query to keep going towards my goal of finding the author of the birthday party.

So the next thing to figure out as we saw before is finding the author ID. I'll

05:48

say select let's say the author ID from the authored table. That table of authors who have written books and I'll say where the book ID where the book ID is none other than the one I got back

06:04

from this prior query. Let me indent and say select ID from books where title is equal to the birthday party quote not a semicolon yet my parenthesis semicolon hit enter and now I see 44.

06:22

So we're getting there but now I have to figure out who has the ID of 44 in my author's table. Let's keep going.

Let's clear our terminal using control L. And I will then say select the name from the author's table.

We're going to start at

06:38

the be or going to yeah start at the beginning the place we want to actually get to. Then I'll say where the ID or the ID of that author is equal to the result of this query.

What is the author ID associated with the book I want to find? So I'll select author ID from

06:56

authored from authored and I'll say where the book ID is equal to another subquery here I'll indent four times and four again. Now I'm nesting twice.

Right? So I'll then

07:11

say select ID from books and I'll hit enter just to kind of keep me on um some good style here. I'll indent eight times total or and then I'll say um where where let me make sure I'm doing the

07:27

right thing. Where the title is the birthday party.

Okay, that was a lot. Now we'll close out our queries.

I'll hit enter space again semicolon or uh parenthesis enter again parenthesis semicolon. And

07:42

hopefully after all this typing we'll get back what we want. we see our friend Lauren Movenier.

So this is an example of trying to find the author who wrote a particular book although it's a bit long-winded using these kinds of subqueries.

07:57

So let me pose questions then we've seen how to use these subqueries for one to many relationships and also many to many. What questions do you have about how to use these queries?

I remember you talking about books with

08:14

the same name and fact they can have different versions and etc. How would you get the ID for the different versions if they are titled the same as I'm guessing you wouldn't I I'm guessing you would set the for you wouldn't set the foreign key to a title

08:29

instead of be primary or is it done differently? >> A good question here.

So you could imagine a database that has um more than one edition of a book inside of it. like maybe I have two editions of the birthday party or the hard coverver and the paperback and all these other editions, right?

Well, in that case,

08:46

what I might do is try to actually this is a good segue. I might have multiple IDs that I'm searching for.

Like let's say I don't care if it's the um hard cover or paperback version. Well, I could look for the ID of like 55 and 56 and try to find um the author of both of

09:02

those. So, let's actually keep going here.

Let me show you a new keyword to use for those kinds of questions if you don't mind. Um, let me come back to my computer and let's introduce this new keyword that can help us find the author of not just one book but perhaps

09:18

multiple books or different versions of the same book. Let me introduce this one.

This one is called in. Very simple, just i.

And I can use in to say well maybe some key or some column is in some

09:33

set of values not just equal to one but in some set of values. So let's see an example here.

We're back at our authors books and authored tables. And maybe I want to find the books written by these two authors gauze and Olga.

Well I could

09:51

select their IDs. I could say get back 27 and 58.

Now I have two ids. So I could look in authored and I could say give me back all of the book IDs that have an author ID of 27 and 58.

Okay,

10:08

I'll look here and I'll get back both four and 78. So notice how earlier I was asking show me the book IDs where author ID is equal to some single value.

Here I'm asking find me the book ids where

10:24

book ID has where the author ID is in some set of values more than one. Now and similarly let me look at my books table.

Let me say okay I want to find the titles for these books. Well I'll find the titles that have that have book

10:40

ids in this set of ids four and 78. So let's see this in action in our SQL terminal.

I'll come back over here and I'll write this query to find all of the books by a particular author named

10:56

Fernanda Melor. So I'll go back to my terminal and Fernanda is more prolific than most.

She's written actually two books that are on the long list for the Booker Prize. So I'll say select ID from authors where the name is equal to

11:16

Fernanda Fernanda Melor semicolon and this now is Fernanda's ID 24. Okay, let me find the ids of books that Fernanda has written.

I will now say select uh

11:31

book ID. Select book ID.

If I can type like book ID from the authored table where the author ID is equal to the result of this query 1 2 3 four spaces here and I'll say select ID from authors

11:48

where the name is equal to Fernanda Melor parenthesis semicolon and I'll hit enter and I'll get back not one book ID but two. So if I want to find these titles,

12:03

I should say find me the titles where the book ID is not equal to but is in this set of ids. So let's try that.

Let me say select title from books where the ID is in some set

12:20

of values is in those two ids we saw earlier. So I'll say in enter 1 2 3 four spaces and then I'll say select the book ID from authored where let me actually

12:35

um space this again where the author ID is equal to the result of this subquery. We can use equals here because there's only one author ID we care about.

I'll hit enter. Then I'll do space space space four spaces for one indentation

12:53

and now four more spaces 1 2 3 four to indent again to my next nested query. I'll then say select let's say the ID from authors from authors where the name is equal to Fernanda Melor.

And now let

13:11

me close out my queries. I'll hit enter space space again.

And now I'll use parenthesis to close out my middle subquery. I'll do the same.

I'll hit enter and then I'll do another parenthesis and then semicolon here. Hit enter.

And now I should see finally

13:28

Fernandanda's two books. Okay.

So this is one example of using in. What questions do you have on how to use this syntax?

Actually my question was

13:43

not regarding n but actually the indentation you use regarding the subqueries because while you were using the subqueries you mentioned that you are giving four spaces. Is there any necessity of using like such kind of indentation while we are using subqueries?

13:59

>> Yeah great question about style here and let me try to pull up some slides that have um an example of that style so we can talk about it. Let me try to find an example of our nested query.

I'll pull this one up. And as you might remember,

14:14

we had three queries here. And I tried to keep track of them by indenting them more and more and more.

This isn't required. You could um I mean, don't do this, but you could put it all on one line, which would just be crazy long to read.

What we do instead is we'll often

14:31

try to hit enter when it feels nice to have a break in the reading um length and we'll then indent some spaces to show that this query is a subquery of the prior one. The exact amount to indent might vary but the idea of trying

14:46

to break up your lines and trying to indent is going to be a good one for your own sake and for those who read your code. Let's take one more too.

when do we need to use in in wear clause when we are doing you know subquerying I suppose

15:02

that we can also use subquerying without in as well >> you're right so we saw an example using equals and we also saw an example using in general if you want just a you know rule of thumb to follow if you're looking to see if some ID is part of a

15:21

list of IDs or part of a set of ids that is more than one you should use in in is good for checking if something is inside of more than one um item. Let's say equals though is good if you know you have only one ID.

Let's say I have an ID

15:37

equal to 23 or equal to 25. That's a good place for equals.

If though I had 23 or 25, I might use in to match either or of those in that set. But good question.

Let's take one more here too.

15:52

>> We have seen many to one to many relationships. one to one relationships and many to many relationships.

So how would many to one relationships be tackled? Suppose one book has three authors.

Then how would that be represented in the tables?

16:08

>> Yeah, let's look at the example of one book having three authors for instance and let's look at our authored table. So let me pull up that slide for you and we'll talk about it in just a minute.

I'll come back over here and I will pull up our example of having authors and

16:25

books and the authored table in the middle. And to your question of how could I have let's say a book written by three authors.

Well, we could imagine that this table um let's say um let's talk about Boulder for instance. So Boulder has the ID of one.

Let's say

16:41

three people wrote Boulder. Well, in this case we could adjust this.

I could say this is no longer 74. This is one.

And same thing here. This is not four.

This is one. We have the book ID one in here more than once.

It's in here 1 2 3 times. But what we're saying each time

16:58

is that well 23 was an author of one. 31 was an author of one as well.

And so was 27. So I would not be afraid to have multiple um instances of your foreign key inside of this table.

What I

17:14

wouldn't do is have it more than once in your primary key here. Here it actually has some information who authored which book.

Here it is strictly a unique ID that should be only a one to one relationship. Every key has to be unique for the items in that table.

17:31

Good questions. Okay, so we've seen sub queries, but there is one more technique we could use to solve some of these problems of quering across tables.

And this one is going to be called a join. So let's dive into joins.

And the whole idea behind

17:49

joins is trying to take a table and combine it with some other table. And actually for this we have uh some I don't know maybe guests for our class.

We have these very cute sea lions. And we'll use a database of sea lions to understand how joins actually work.

So,

18:07

if you're not familiar, sea lions are sometimes native to the coast of California, and they like to travel. They like to travel really far.

They go from the southern part of California all the way up to Washington State. This is many, many miles of traveling for these

18:22

adorable sea lions. So, there are data sets that involve sea lions and their migration patterns.

Here's one, for example. Here we have a table of sea lions.

And here we have a table of their migrations. A distance column tells us

18:39

how far they went in miles. And a days column tells us how long it took them to travel, of course, in days.

Well, here, if we're researchers, maybe we're keeping track of some number of sea lions. We're keeping track of Ya and

18:54

Spot and Tiger, Mabel, Rick, and Jolie, but we have data on many sea lions, perhaps some we aren't even tracking anymore. This would be our migrations table.

Now, it could come to pass that I want to figure out, well, how far did Spot travel and how far did Tiger

19:11

travel? And what I could do is I could use nested queries for this.

But perhaps better yet, I could try to join these tables to see the data all in one table. Now, to do that, how could I try to take a row from this side and kind of attach

19:29

it to or extend the row here? What idea could I use to put the right rows together in this case?

>> Could you use the uh primary key, the ID to kind of join them together using the

19:44

primary key and the foreign key? >> Yeah, a good idea.

So, you might notice that we actually have two columns here, both called ID, which is handy for us. And notice how we have them kind of sort of matching.

and matching in the sense

19:59

that some of these IDs are actually inside of this column too. So we could use the primary key in really both tables here and try to match it together to figure out how these rows could be extended to add more columns.

We could

20:16

join these tables together to make this table ultimately showing us where ya has um traveled and how long it took them to get there. So the keyword for doing these kinds of manipulations with data in SQL is called join.

Kind of

20:32

straightforward. But let's take a peek at what join can do for us inside of this database.

So I'll go to my computer here and I'll pull up this database of C lines that I prepared for us today. I will clear my terminal.

I'll typ

20:48

and I'll get out of my long list database. To do that I can typequit a SQLite command to leave this environment.

I'll hit enter. And then what I can do is I can type SQLite 3 space C_lions.

21:04

DB. That is just the name of this database.

Okay. I'll hit enter.

Now to make sure I'm in the right place, I could type to see what tables are inside of this environment. Tables.

Enter. I see both tables, migrations and sea

21:21

lions, which is promising for me here. So, we could see what data is inside of these tables.

I could say select star from C Lions, uh, semicolon, hit enter. Now, I see all of these adorable sea lions inside of

21:38

this lovely table. What if I wanted to find migrations?

Well, I could do that, too. I could say select star from migrations semicolon enter.

Now I see my data on migrations. And notice how again some sea lions are in the sea lions

21:55

table. And we have some ids in our migrations table that actually aren't in our sea lions table.

So we're keeping track of only a subset of sea lions. And this might have some uh seions we keep track of in the past.

So not all of them as well. Okay.

So let's try joining

22:10

these tables to determine where have our sea lions been in our sea lions table. I'll say select uh star from sea lions.

But now I don't want to end this query here. I want to expand this table with the data inside

22:28

of the migrations table. So what could I do?

I'll hit enter just as as a style thing here to now consider how I can make a query a bit longer. I'll want to join the migrations table to the sea lions table.

And to do that, I can type

22:44

join. I'll say join then space.

And I want to say the table I want to join. In this case, the table is called migrations.

I'll say migrations. And I have to tell it one more thing.

I have to tell it what we saw visually earlier,

23:01

which is that the ID column in sea lions matches the ID column in migrations. I have to tell SQL which two columns to try to join these tables by.

So let me do that. I'll say join migrations on which is a SQL keyword what two columns

23:19

should correspond. I'll now say migrations.

ID. This means the ID column inside of migrations table is equal to or is going to be joined with this sea lions table

23:34

and the ID column within that. Again, these two columns should have some matching values that we could use to make these rows longer with more columns.

Let me then hit semicolon here and hopefully we'll see all of our data

23:51

now combined. I can tell you how far Tiger went and how many days it took them to do it because I've joined these two tables together.

So in doing this kind of join, just regular old join, what we're really doing is part of this family of joins

24:09

are called an inner join. You may have seen this idea of an inner join or an outer join.

The usual join, at least in SQLite, is going to be an inner join. And what do we mean by inner join?

Well, let's visualize it a little more slowly

24:26

here and see the process of which we might use to understand this join. I'll have again my two tables, but now simplified.

I have name and ID and just distance and ID. And to join these, I could literally kind of nudge them

24:41

together and draw a line down the middle a bit like this. But what do you notice?

I mean, this one seems to match this ID. And this ID seems to match this ID.

But these two, well, they don't match. I mean, poor Jolie down here.

We don't

24:57

know how far Jolie went. And if we don't know this data, if these IDs don't match, if we can't find a match for this ID or for this ID, well, let's sadly just get rid of the row.

Like, it's not part of our join table anymore. It's

25:13

gone from our memory. We now have only these tables or these rows that we could actually join together with the row and the other table here.

So this is our example then of an inner join. Let me ask what questions do you

25:31

have on these joins so far? >> So the confusion for me right now is where were the IDs generated?

Was it on the C lion table or on the migration table? And what kind of problem can that pose like maybe in real world?

So that's the question I have maybe not directly

25:47

to joint itself. >> No, that's a great question and good to clarify.

So um we have sea lions with some ID and we see here that you know one ID is 10484 another ID is 11728 and to your good question which is how do we

26:02

get these IDs? Well, these actually came to us from researchers perhaps who are researching sea lions.

And if you're not familiar, let me go back to our slide with sea lion migrations on it. Researchers might often want to keep track of where sea lions have been.

And to do so, they will aum non-invasively

26:20

apply some tag to this animal that has some unique ID on it. And they'll track where that tag goes to understand where this particular sea lion went.

So they will make up their very own primary keys, their very own unique IDs to identify these sea lions. We can then

26:38

use those same IDs in our table to understand where these sea lions are going. And just to be clear, too, let me find our full data set here.

Here we have those sea lions that we're now interested in. Those were now tracking.

But you could imagine that we also have

26:55

some sea lions that, you know, we no longer track. and you know maybe they've you know gone on they're doing something else now they're not part of our data set so that's why we might see some over here but not over here I hope that helps answer your question about the sources of this data so let's take one more when

27:14

we join together the two migration and seell the ID table is repeating so I just want uh days and the travel distance I don't want to repeat the table of ID is that possible

27:29

Yeah. And I think if I'm understanding correctly, we want to try to um not just join these tables, but maybe remove the duplicate ID in them.

Like if I showed you this joined table right here, ID appears in here twice. There is a way to do this, and we'll see it towards the

27:45

end of our exploration of joins. We'll come back to that in just a minute.

Okay. So, we've seen inner joins so far.

are this way of combining tables and only keeping the data that actually matches between them. Well, let's try a new uh way of joining.

This one that

28:02

might not be so strict. We have a few options here.

We have what we're going to call a left join, a right join, and a full join. And you could imagine based on the names here, what each one might

28:18

do. You could think of our tables, one being on the left, one being on the right, and trying to join them, prioritizing either the left table or the right table, or maybe even both per a full join.

So, let's get a feel for

28:33

what kinds of tables we get back from these different commands, and we'll talk about we'll visualize how this is actually happening underneath the hood. Let's go back to our computer here and let's try to do a left join using our two data sets.

Now, a left join will

28:51

prioritize, so to speak, the data on my left table. Left being quote unquote because there is no left or right in a database, but left here means the first table that I start with.

So, let's try this. I'll say select um star.

Select

29:07

star. And let me just make sure I'm typing the right things here.

Let me try select star from the sea lions table. And now I don't want to just do a regular old join.

I want to do a left join. I want to no matter what always

29:25

have the data on my left table. This sea lions table here.

Left join. And I'll join migrations.

I have to say the same thing. Which column in migrations corresponds to which column in se lines?

I'll say left join migrations on

29:42

migrations id equals sea lions id as well. Now I think that's just about it for my join.

So I'll say semicolon enter. And notice the difference here.

Let me walk to this board so we can see it in its entirety.

29:59

I have all of my sea lions now in this table. where we left off Jolie earlier.

We've now kept Jolie in even though Jolie doesn't have a distance or number of days. We actually haven't tracked that data yet for Jolie, but we still

30:16

see them in this table. Let's now try a right join to see the difference between this and the right join.

I'll come back over here and let's try prioritizing our rightmost table. This one perhaps being the migrations

30:32

table. So I'll say select star from sea lions.

This will again be our left quote unquote table just because it comes first. Now I'll say join uh not just join I'll say write join migrations

30:48

on again migrations ID equals let's say sea lions. I semicolon.

Now let's see the difference here. Well, what have we done?

We've actually left off a few sea

31:04

lions. Now, we only have those whose ids were in the right table.

Again, the right table being that second one we used. And even if we don't have any names for these sea lions, we'll still include them in our data set.

We can see

31:20

though that this data is missing and that as we'll see, it has some special value called null. So, we have a left join and a right join.

A full join though allows us to see the entirety of both tables and which values are

31:35

missing. Let's try that now.

I'll come back over here and I'll do a full join. I'll say select star from sea lions full join migrations on full join migrations on migrations

31:53

migrations. ID is equal to sea lions do ID semicolon enter.

And now what do we see? Well, we see both tables in their entirety.

We have Jolie in here still even though

32:08

Jolie doesn't have a distance or some number of days. We also have our sea lions, our mystery sea lions down below who don't have any name but are still included in our migration table too.

So left join, full join and right join.

32:25

So let's then visualize this to understand what's happening along the way. I'll go back to some slides here.

Let's look first at these um left joins as well. So really all of these left join, right join and full join.

These are all part of this family called an

32:42

outer join. An outer join lets you keep some data even if the join is not going to quite work out for you as much as you would want it to in an inner join.

By that I mean you might have some null or empty values in this join after you run

32:58

it. So let's visualize it.

Here I have a left outer join and I want to combine this left table with this right table. I'll do a left outer join.

I'll put them together. And notice how these two don't match.

Well, because they don't match,

33:15

I'll fill in these values with the value that signifies empty or null. In this case, it is literally null.

There's nothing here. So, because I did a left join, what I should do is prioritize this left table's data.

Even if this

33:31

doesn't have any match here, I'll still keep this row. But notice down below that this ID has no match in the left table.

I'll omit that and in the end I'll delete this bottom row but be left with Jolie who might or may not have

33:47

data in the right table. Let's try the right join.

Right outer join. I'll do the same thing.

Combine them and they'll do the same exact kind of orientation. I have some null values here that I don't quite know how to match up.

What I can

34:03

do instead is say, well, I want to prioritize the data on this rightmost table, even though this ID has no match. I want to keep it because it's inside this right table.

Okay, let me delete this row because it has no match in my right table. And I'll be back to this

34:19

type of join, keeping every ID in the right table. A full join, a bit more simple.

I'll take the same data, join it together, and I'll simply say I don't care if there are null values involved. I'll leave them be.

34:35

Okay, so this is a lot of visualizing. Let me ask what questions do we have on these joins?

Could I somehow work with multiple tables? Uh is there, you know, if I have like three tables, which one is the left

34:51

one, which one is the right one, and what's the third one? >> Yeah.

A good question. So, we've seen two tables so far and this was deliberate kind of pedagogically.

It' be a lot to see three joins all at once. Um, if you're thinking about joining three tables though and you're asking

35:06

which one is your left table, which one is your right table, always it will be that the table that comes first before your join command, your keyword will be the left table and the table that's involved in your join keyword like join migrations, let's say, that will be your

35:22

right table. And so you can imagine three tables um where for like the first two, the first one's on the left, the first one's on the right.

For the next two though, the middle one's on the left and the next one's on the right. If that helps you visualize without having much on the board here.

Good question. Let's

35:38

take one more. When we perform the join function, do we get under the hood, do we have like a separate table that's created which we can reference later down in the code or like do we need to write that entire join command again?

Yeah, good question. And so I believe you're asking, do we

35:54

get back a separate table that we could use? Um, for this I might want to doublech check myself, but just to kind of show you what we've been doing so far, go back to my um SQLite terminal here and I will ask like select star from um let's say sea lions and let's uh

36:14

do a full join with migrations on migrations. ID is equal to um sea lions id.

And I think what you're asking is do we get back like a separate kind of table here. Um we could

36:29

consider it as a kind of separate table like if I hit enter here is this is kind of something we're going to call a temporary table which you could also call a result set. We can use this table for the duration of our query.

What we haven't done though is made this table

36:45

accessible in our database itself. like this is only there for the duration of this query.

So hopefully that helps answer it with a bit of um factchecking if you need to as well. Okay, let's show one final type of join

37:00

here. This one called a natural join.

A natural join kind of allows me to omit the part of the my previous joins where I was saying migrations ID equals sea lions id like natural join says both these columns are called ID. I could

37:17

just assume that I want you to join by these two columns. So, let's try a natural join and see what we get back.

I'll come back over here and we'll try a natural join with our sea lions table and our migrations table. I'll say

37:33

select let's say star from sea lions. Then I'll natural join migrations here.

And I no longer need to say migrations ID equals sea lions do ID. I can just hit semicolon.

And now I can hit enter.

37:50

So we'll see that the join worked just as we wanted it to. And we didn't even specify which ID column corresponded to which other ID column because these were named the same column name ID.

Notice too, we don't get a duplicate ID column.

38:08

In some prior joins, we had an ID column here and an ID column here. Now, we've just combined them into one single column or really only kept one column among this join here.

So, many kinds of

38:23

joins to use, many ways to query our tables. What we've seen so far is how to organize our data, build connections between them, and now how to query this data using both subqueries and joins.

We'll take a quick break here and come

38:38

back to see how we can use sets and groups to again kind of refine our queries and ask more interesting questions too. Well, we're back and now we're going to focus on this idea of sets in SQL.

So, when you run a query,

38:54

you get back some results and those results are often called a result set. Some set of values that you've gotten back from your table.

So let's take a look at what sets are and how we could use them to refine our queries and to find relationships among different

39:10

groups of people, places or even things. So let me ask a question of you.

Maybe in this world we have authors and we have translators at least in the publishing industry, right? If you're in this set, you are an author.

If you are

39:26

in this set, then you're a translator. But what would it be then if you were in this set?

What does this set mean? >> It means that uh you are both the author

39:42

and the other one. So translator.

>> Yeah, you're right. So this means you are both an author and a translator.

You're in the middle of these two overlapping circles. In SQL, we could represent this kind of relationship using our intersect operator, which

39:58

we'll see in just a moment. We'll take authors perhaps and intersect them with those who are translators and find those who are both authors and translators.

Let's do another one then. Let's think of this set.

Who are you? Or at least

40:14

what does the set represent as a whole? >> It means that you're either an author or a translator.

>> Nice. You're either an author or a translator.

You could be both, too, but you're in either camp. You're either an author or a translator.

And for this, we

40:31

could use a SQL keyword called union. I'll take my set of authors and combine them with my set of translators and get back, in this case, the entirety of these two sets.

Let's look at this one, too. If you are in this set, who's

40:49

included there? Only authors will be included.

Authors which are also translators will not be included here. >> Yeah, good.

So here only authors are included. We see that those who are also

41:04

translators aren't included. In fact, if you are an author and only an author, then you are in this set.

And to get this kind of result, we would use our SQL keyword called accept. I'll take my set of authors but subtract or remove or

41:21

accept my set of translators. Similarly, I could say take my set of translators and accept the set of authors.

Subtract them, remove them. And I could do that with translators except authors.

We'll see these keywords in just a moment.

41:38

Now, I have one final question here, which is how could you represent this one? I mean, who are you if you are in either this set or this set?

And how would you get that kind of set? I'll actually save that one for you to do as

41:54

homework, if I may, before we move on to seeing these in our actual terminal environment. So, let's take this idea of sets and apply it to authors and translators this time in our database.

I'll come back over to our SQLite terminal and let me now leave our

42:11

database of C lines. Sadly, I'll typequit and go back to my regular old terminal.

Now, I want to open back up our longlist db database. Long list db.

Hit enter. And now I'm back in the place where I have authors and books and

42:28

publishers inside of this database. I'll type L to clear my terminal.

And let's first try to find all of those who are either translators or authors. Well, what could I do?

I could try this. Let me try select select name from

42:47

translators like this or my translators table. Semicolon enter.

These are all of my translators. Now I want to find authors.

I could then say select name from select name from

43:02

authors semicolon. Hit enter.

Now I have all of my authors. If I wanted though to combine these results, these result sets, I could do so using union.

So let's try it. I'll say select name.

43:19

Select name from translators and not finish my query yet. I'll hit enter just for style sake.

And I'll say union union with some other result I'll get back from some separate query. I'll

43:34

hit enter. Now I'll say select let's say name from let's go for authors now end quote semicolon hit enter and now I'll see both authors and translators and we

43:49

should see here that every author and every translator is in here only once. We can assume that we have unique names for these authors and translators.

And I could scroll through and figure out who is either an author or a translator.

44:04

Actually see them both in the same set. If I wanted, I could distinguish between authors and translators by adjusting my query just a little bit.

I could say select um let's go for select uh author

44:20

the string as profession and also select name from the author's table semicolon. This will give me back not just one column of names but also a another column called profession that has just

44:36

author inside of it. I'll hit enter and I'll see I indeed have this column if I scroll all the way up called profession and I have the value being author.

Okay, let's combine these. Let's union this set with the set of translators and give

44:52

them their profession too. I'll say select author as profession and then also name from the author's table and now I'll hit enter and I'll say union that set with

45:09

the set of translators. I'll say select translator the string as the column profession.

Then I'll say name from the translators name from the translators table quote unquote semicolon. Hit

45:25

enter. And now I could actually see who is a translator and who is an author.

If I scroll through here I'll see translators and now I'll also see author. So I've combined these two sets into one that works for me here.

Okay.

45:41

So this is how to union or combine our sets. But what if we wanted to find only those who are authors and translators?

Kind of a unique niche group of folks. But who is part of this set?

Well, let's try to find out. I could select names

45:57

from the author's table as I did before. Now, instead of union, let me intersect these names with those that will be in the translators column.

I'll say intersect. intersect and select name from

46:14

translators. And now I'll hit semicolon enter.

I'll see one his name is Goi who is both an author and a translator. They translated their own book into English for the international booker prize.

Okay. So

46:31

let's try now to find those who aren't just translators and authors but only those who are perhaps translators or only those who are um authors. So let's try this.

I'll say select name from

46:46

authors. Hit enter.

And now I want to exclude those who are also translators. I'll say except in this case select name from select name from translators.

Hit semicolon. And now I should see if I pay

47:04

close enough attention that Goi is not in this list. They are an author and they were in the author set of names, but because they're also a translator, they're not inside this set of list.

47:19

So this is fine and good for finding who's in one list, who's in some other list, and who's in between them. But it's also nice for trying to find books people have collaborated on, let's say.

Maybe I want to find the books that both Sophie Hughes and Margaret Costa have

47:35

written together jointly. So I could try to find first the books that Sophie Hughes has translated.

Let's say select um select book ID from the translated table. This table that has translated has translators and

47:52

books inside of it. And then I'll say where the translator where the translator ID is equal to this subquery.

I'll find Sophie Hughes's ID as a translator. I'll say select ID from

48:09

translators from translators where the name is Sophie Hughes. Okay.

Semicolon to end. And these are the book IDs that Sophie Hughes has translated because Sophie Hughes is a translator.

48:26

Well, let's see. Maybe they have translated some books with Margaret Costa as well.

I could try to find out. I'll say select say select book ID.

We'll try to find um Sophie's books again. I'll say select book ID uh from trans from translated

48:47

where the translator ID is equal to again Sophie Hughes's translator ID select ID from translators where let's say the name is Sophie Hughes like this now I'll close out this

49:05

part of my query but I want to find the intersection the set of book ID keys that are the same between Sophie Hughes's translated works and Margaret Costa's translated works too. So I'll continue.

I'll say intersect intersect this result with some other query. All

49:22

right. Now let me select the book ids from the translated table where the translator the translator ID is equal to a different kind of query.

This one will get back the ID for Margaret Costa. I'll

49:39

say select ID from translators and where the name is equal to and I believe in this particular case they go by Margaret Ule Costa like this. Now I'll hit enter close out this subquery and finally I

49:56

can hit semicolon. This is the end of my query.

Semicolon here and now I'll see the book ID they have collaborated on. You could imagine going beyond this to find the title of this book ID using another nested query, but I won't spend

50:13

time on that at least for now because I have to write all this text back out for you. So, we've seen union, we've seen except, we've seen intersect.

What questions do you have on these sets and how they work?

50:29

>> You've got three joints. I just wondered if there was a default that you chose if you didn't know what the situation was like which one to use.

>> Yeah. So we saw different kinds of joins.

Um we saw uh we saw left join, right join, full join, right? And the default as we saw a little before is

50:44

just a simple join. And that join is an example of an inner join.

It'll only take those rows where the ids actually match up. You could get more specific and have a left or right join or a full join, but we'll leave that um for you to try out on your own and see the results of as we did before too.

But great

51:01

question. >> You mentioned that the ids from both tables were used automatically to merge the two tables and we didn't have to bother with the duplicate um duplicate column for an ID.

So I was wondering in a situation whereby the primary key are

51:17

actually like foreign keys. Let's say you're trying to join with another table where your ID is actually like a foreign key.

How would you go about doing that? Because it seems we didn't specify the ids where wanted to like make the joins on with a natural join.

So I just wanted to needed some clarification on that front.

51:33

>> Yeah. And let me make sure I'm understanding correctly.

So I think you're trying to think about how you would um join a table that has a different kind of column name from the other one. Um, and for that you could use the equals there and you could say um, we said migrations id equals sea lionsid.

But let's say the sea lions ID

51:51

was actually called um, sea lions ID like the full thing spelled out. I could say join um, migrations on migrations equals sea lions.

Lions ID to spell out the column name for SQL. Good question

52:07

too. Can I use intersect and the union together between more than two tables, three tables or four tables?

Yeah, could you use intersect more than once? Let's say to intersect more than one set.

You absolutely could. And here I don't have

52:23

a good example planned for this, but you could have one query that does one thing. Gives you some set and you type intersect after it.

Then you get back the next query and you find the intersection of that. You could then type intersect again to get the intersection of all of these three sets.

you keep going going as you'd like to.

52:40

The key thing here though is that in your intersects or in your unions or in your excepts, you always have to have the same number and the same type of columns. So if I was trying to combine, let's say, author names with um book

52:56

titles, I can't intersect or union those quite well because they have different kind of meanings, right? They're different kinds of things.

So if you're trying to join multiple thing, trying to join multiple tables or interact multiple tables, make sure you always have those same column names and those

53:11

same column types, the type of data that's stored inside. Okay, so we've seen sets now and we're going to conclude by looking at this idea of groups.

Well, groups will allow us to recreate some of what we tried to

53:27

do a little bit of last week, trying to um understand the average rating of some books. Let's take a look at groups now.

Well, you could imagine that I have my ratings table. And in this ratings table, I have book IDs and I also have

53:44

ratings. Here, I see that the book ID of one has several ratings for people who gave the book a rating.

It has four, three and four. And similarly, book ID with two has two ratings from folks two and three.

Now I want to find the

54:01

average rating across these ratings for each book ID. So let me try that in my terminal.

Come back over here and let me first try to find the average rating for each of these books. So I'll go back and I'll say why don't I select the average

54:19

average rating. It's like the average rating from my ratings table.

And I think this should be good enough. So I'll hit semicolon.

And I'll see well 3.83644. And this seems to be an average, but I

54:35

wanted the average of each book. So what did I do wrong here?

What am I actually seeing in this case? Is that an average of all books in the entire table?

54:51

>> Yeah, good instinct. So, let me try to put back our table here.

We can see what's actually going on. I'll come back over here and I will show us again this table.

So, we had a table of ratings, right? And we had the rating column that has individual ratings inside of it.

55:08

When I say select the average rating from this table, well, what am I getting? I'm getting the average of all of these ratings, not grouped by book ID.

So, if I want to find the average rating for each book, I need to employ

55:26

this idea of groups. And I mean, wouldn't it be nice if I could do something like this?

I could kind of highlight the rows that are part of each group and then try to average those. I could say, take all the books with ID 1 and all the books with ID2 and just collapse their ratings like this.

And

55:42

once you do that, find the average for me and give me that back as a table. That'd be kind of nice.

And luckily, we actually have the capacity to do that with SQL using this group by keyword. So, we'll see this in action.

I'll come

55:58

back over here and let's try to redo this query to find the right answer. I'll come back and I'll say let's select let's select in this case the book IDs and also their average rating.

I'll

56:15

select book ID that's one column I want and I also want the average rating from this column from this table. Now I'll say let me title this perhaps as average rating.

So, I'll get the average rating

56:30

and just title that column average rating. Exactly.

I want to select this from the ratings table. Okay.

But now I don't want to simply hit enter. I'll get back the same thing I did before, which is the average of all

56:46

the ratings in the ratings table. What I want is for these ratings, the average ratings to be grouped by the book ID.

So I'll say group by book ID semicolon hit enter. And now I should be able to see

57:03

the results I wanted. Let me scroll to the top and notice how using group by I was able to take each book ID collapse the rows the ratings were associated with this book and find the average of

57:18

that rating 3.77 3.97 3.04 4 for each book. So, kind of handy here.

Let me try to improve this and show you one additional thing we could do as well. I'll come back and why don't I try to

57:37

replicate what we did last time, which was find the books that have a certain rating or higher. Let's try that.

I'll say select let's say the um book ID and let's get now the rounded rating the

57:53

rounded average rating. I'll round the average rating to two decimal places and I'll call this average rating as a column.

Okay, I'm going to select this from my books table from my ratings

58:09

table, sorry. And now I want to group by the book ID to find the average rating for each book ID.

Now though, let's say I only want those books that are pretty well rated,

58:25

greater than 4.0 out of five stars. To include a wear clause, I could try following up on this group by.

And you might think I could say, well, where the average where um average rating that new column I created is greater than or

58:40

equal to 4.0. But I can't because these are now not individual rows, they are groups.

SQL uses a different keyword for conditions on groups than for conditions on individual rows. And this keyword is

58:56

called having. H A V I N G.

Let me try that. H A V I N G.

I want to group by the book ID and only keep those that have an average rating of greater than 4.0. I will scroll over here and I'll

59:11

hit enter. And now I should see a much smaller list.

I see book ID 5 has a 4.06, 10 has a 4.04 and so on. And now the next step is you could imagine me perhaps joining these book IDs with another table that has titles inside of

59:28

it too to find the average rating given some title as well. So groups allow us to take these columns collapse some rows and find aggregate statistics across each of those groups.

Let me ask then

59:44

what questions we have on groups. What if I don't want to see the rate the average rating of a book, but how many ratings does it have?

>> Oh, good one. So, I want to know not the average rating, but the number of

00:00

reviews. Let's say the number of ratings it was given in my table.

Well, for that, I could just modify my query a little bit. I could change average to count because count counts up the number of rows that I have.

So, let me try that here. I'll come back to my laptop and

00:16

let me try this one. I will select book ID and I will also select the count of book ID um in my no also select the count of ratings how many ratings there are inside my ratings table.

Count of

00:32

rating from let's say the um ratings table like this. Now I want to group this data group the counting by book ID.

So I'll say group by book ID. Give me a count of the ratings for each book ID.

00:49

And now I could just hit semicolon and enter. And now I'll see how many ratings each book actually has.

Let me go to the top here. And I'll see book ID 1 has 2779 ratings.

Book ID 2 has 175 ratings

01:06

and so on. A good question.

Let's take one more too. So considering this new having syntax, how could we couple that with the order by?

>> Yeah, where does it fit in with order by? Like let's say I want to sort this data to at the end of the day.

Well, I

01:23

could do that as well. Let me show you one example.

I'll come back to my terminal and I could try this. Let me come back to uh finding average ratings.

So I'll say select book ID and the

01:38

rounded average rating to two decimal places calling that column average rating. And I'll select this from the ratings table.

Well, now we want not

01:53

just to find the average rating, we also want to sort by that column too. And in fact use having to only find those books that have 4.0 know or higher.

Well, let's try this. I'll say from ratings and I'll still group by book ID.

02:11

Now, I'll say I only want to filter to those groups that have a average rating of greater than 4.0. And finally, after I've filtered out all of these rows, I want to order them.

I want to sort them.

02:27

So, I'll order them by average rating. this column I made up top and I'll say order them descending order.

I want biggest to smallest and fingers crossed I'll hit enter and we'll see our ratings

02:43

sorted for us. So great question here to reimplement much of what we did last time.

And now indeed we've kind of come full circle here. We've taken these ideas and we've seen them in single tables as we did last week.

We this week

03:01

took our tables and expanded them into multiple tables, different authors, different publishers, different ratings and so on. And we learned how to query those tables using subqueries and using joins.

What we'll see next time is putting you in the driver's seat, having you actually create your own tables,

03:18

define your own relationships, and build a database to your own liking. So, all that and more next time.

We'll see you there. [Music]

03:45

Well, hello one and all and welcome back to CS50's introduction to databases with SQL. My name is Carter Zeni and last we left off we learned about relating.

That is how to have multiple tables in our database for people, places, and things and how to have them relate to one

04:02

another in the way we might do in the real world. Now today we'll take a step forward and we'll talk about how to put you in the driver's seat designing your very own database schemas to organize your data.

Now we'll pick up where we last left off which was with this database of books. So we had this

04:19

database that was full of books that have been long listed for the international booker prize. To be long listed means to be nominated for some prize let's say.

So we had the past five years of books in this database and we worked on improving this database over

04:36

time from week zero to week one. Now this week we'll actually take a look underneath the hood and see what commands we had used to create these very databases.

So let's reveal now what we had done all along. I'll go back to

04:51

my terminal here. And if you remember, I could use a command to open up a database file, which was this command here, SQLite 3, and then the name of the file I want to open.

So, let's try this. I'll go back to week zero to my week zero folder like this, and I'll open up

05:08

my longlist database, week zero longlist. DB.

Okay, I'll hit enter. Now I'm in my SQLite prompt.

So I could work on typing some SQL commands, SQL statements or queries inside of this uh

05:24

terminal prompt here. So if I want to get a feel for what's inside this database, we saw I could use a command called select.

So I'll select now some rows from this table. I'll say select let's say the title and also the author

05:42

columns from my long list table semicolon and enter. Now I'll see all the titles and all the authors that were inside of this long list table.

But I only want to peak here. I only want to

05:58

see roughly what kind of data is inside. So I could probably improve this command here.

I could instead say select the author and title columns from long list from long list but limit now to the

06:15

first five rows we saw before semicolon and I'll hit enter on this query and now I'll see only the top five rows. So I'm able now to see what kind of data is inside my database.

06:31

But what I can't yet see is what command was used to create this table and what kind of data could be stored inside of it. So let's reveal now what was going on underneath the hood all this time.

I'll say this new command a SQLite

06:47

command not a SQL keyword but a SQLite command called schema schema. Now if I hit enter, I'll see the following.

The command, the statement, the query that was used to create this long list table. And notice how I have

07:04

many columns inside this table. I have an ISBN column, a title column, an author column, and so on.

And each column seems to have some kind of data that could be stored inside of it like text or integers or real for

07:20

floatingoint values or decimals if you're familiar. So this is how we created the very first version of longlist db.

But let's also see how we created the second. So I'll type doquit to leave this version of

07:36

longlist db. And now let me open up the next version we had created which is more relational.

It had tables inside of it that could relate to one another. So let me type SQLite 3 then long list the SQLite 3 and go to week one and then

07:52

type long list db. I'll hit enter.

Now in my next version of long list db. Well what could I do?

I could type select and look at some of the uh tables in here to see what kind of data is inside. I could perhaps say select maybe the names from

08:11

the author's table here from the author's table and hit semicolon. Now I'll see all the names of authors that are inside of the author's table.

I could do the same thing for books. I could maybe look at the titles of books.

08:26

I could say select title from the books table semicolon and enter. Now I see all of the titles that are inside my books table.

But what I haven't seen yet is the schema of this database. It is the

08:43

way it is organized and the commands that were used to create these tables. So let me work on that.

Now I'll clear my terminal using ctr L. And now let me type dots schema again.

I'll type dots schema to see what commands were used to

09:00

create this database. Hit enter.

And I can see I mean there are quite a lot of commands here. Now this feels overwhelming.

I mean I'd be right there with you, right? This is a lot of commands to parse through and read.

So there's probably a better way to do

09:17

this. And one way to try is to type schema and then give it some table name.

Let's say I want to understand the schema for just the books table like just that for now. So I'll say dots schema and then the table name books in

09:33

this case. Then I can hit enter.

Now I'll see the schema, the organization, the command that we used to create the books table in this case. And notice again we have several columns ID, ISBN,

09:48

title, publisher ID and so on. Each one has their own kind of data they could support or take into this column like integers, text and so on.

So again, what we'll do today is have you all learn how to write your very own create table

10:05

commands to build your very own databases that represent what you want to represent in the real world. So, let me exit this prompt here and let me propose that we'll have a bit of a design challenge today to actually try

10:20

to represent a real world entity with some database here. And if you're not already familiar, uh Boston is perhaps famous for being among the first cities to have a subway system in the United States.

So, here is a picture from the

10:36

late 1800s of a subway being built in Boston's city streets. underneath the streets here.

There would be trolley cars would go and transport people across Boston. Here's another picture of a trolley actually working underneath the streets.

So, people would go down

10:51

underneath. They would hop on a trolley.

They'll be able to go to different parts of Boston, perhaps from Harvard to MIT or downtown up to, let's say, Brainree or down to Brainree, which is more south of Boston, for example. One of the famous stops is the Park Street stop

11:07

which is right down in the middle of Boston, one of the central hubs of this subway system. And now these photos are all from, let's say, the early 1900s, late 1800s, and so on.

But the subway's gotten a lot more modern since then. And actually now we have several lines that

11:24

span the entire city and beyond. So here we have the red line of which Harvard and MIT are a part.

We have the green line which brings you kind of west to east, the blue line, the uh orange line and so on. So many more lines and

11:40

stations have been added to this system. It's a big design challenge to represent all of these stations, all of these lines and all these people who might ride this subway too.

So the question then becomes how can we create a schema

11:58

for this data? And again by schema we mean what kinds of tables should we have?

What kinds of columns might those tables have? And what kind of data should we put in each of those columns for instance?

So let me propose this.

12:14

Let's say we start just first with names and stations. So Charlie here, our very first rider on this system is going to be at the Kendall and MIT station.

So this is what this table represents now. But what more could we have?

Well, we

12:30

might also want to have maybe what Charlie is doing at that station. Maybe he's entering the station, for instance.

And if you're familiar with the subway system, you often have to pay to get onto a a train or get onto the station

12:45

itself. So, let's say Charlie pays some fair to enter into the Kendall MIT station.

Well, back in the mid 1900s, the fair was only about a dime. It was 10 cents.

So, we'll say Charlie paid 10 cents to enter the Kendall MIT station.

13:02

And now, this seems pretty good, but if I am the transit authority, the person who runs the subway system, I probably want to know, does Charlie have enough money to get on the train? And if so, I want to make sure that, okay, well, Charlie actually could get on this

13:18

train. So, let's say not only does Charlie pay a fair, he has some remaining balance afterwards.

So Charlie here has gotten onto the Kendall MIT stop. He's paid the fair of 10 cents and has five cents left.

13:33

Okay. So here's a bit of a table.

We probably add more information to it. Let's say Charlie then leaves at the Jamaica plane stop.

And the fair to leave is about a nickel, 5 cents. And now Charlie has no cents left over.

So

13:50

again, Charlie paid 10 cents to get on, had 5 cents left, paid 5 cents to get off, and now has no remaining balance here anymore. Okay, so that's Charlie's story.

Let's look at Alice, though. Let's say Alice gets on at the Harvard

14:06

stop. They too pay 10 cents to get on at the Harvard stop, and they have a remaining balance of 20 cents.

Alice will go, let's say, to Park Street, get off at Park Street, pay the nickel to leave, and now they'll have a balance of 15 cents at the end. Let's go to Bob.

14:25

Bob enters the alewife station. They pay 10 cents.

They have a remaining balance of 30 cents. And let's say they leave at Park Street and have a fair of 10 cents to leave because it's a further distance.

Now, they'll have a remaining balance of 20 cents overall.

14:42

So this table is okay, I might admit. I mean, last time we learned about having we called primary keys and foreign keys.

So seems like that's missing here. Let's go ahead and add that here.

I'll give each row a

14:58

unique ID so I can know who entered, who exited, and so on. And give that a unique ID here.

But I might even say that this table could really be improved substantially. And I want to ask you, what redundancies or inefficiencies

15:16

do you see here? We're trying to represent riders and stations.

What could we improve about this design? >> Hello.

So, uh probably the redundancy will be the names and the stations too. For example, the Charlie will uh go to

15:34

the train daily. Then he will become most of the names in the data.

Yeah, good point. If I'm hearing what you're saying, Lauren, let me show you some examples that I highlighted here.

You know, one example could be to your point about these names. Like these names seem

15:50

to be telling us the name of a person. But here we have only three names, Charlie, Alice, and Bob.

Well, my name is Carter. And what if somebody else named Carter also tried to get on and

16:05

leave at some stop? Well, I wouldn't be able to know which Carter was which or which Charlie was which or which Alice was which and so on.

So, we probably need a way to represent people and their names a little better here too. What other ideas do we have for how to

16:20

improve the design of this table? >> Yes, I think we can have uh a singular ID for a singular person.

That way, we'll be better able to track their activities. >> Nice.

So, we could probably have an ID for each person. A bit of what we

16:36

learned about last week, right? Putting people in their own table and giving them their own unique ID, a primary key.

Let's show that here. I'll go to some slides and I'll pick out one that shows us just riders.

So, to your point, Sukanya, we could try to have maybe a

16:52

table for just riders. And maybe to simplify, this table has only two columns.

It has a column for ID and a column for name. So here we have Charlie, Alice, and Bob all in their own table.

Well, let me pros too. We could

17:08

do the same thing for stations. Like let's say we have a table of stations now and we give each one their very own ID as well.

Our own primary key for this table. We have Harvard, Kendall, and Park Street.

We can differentiate between them using their IDs here. So a

17:26

few improvements could be made. And as we're making these improvements, splitting one table into many and debating what kind of data to store in each table, the process we're going through is one called normalizing.

We're normalizing our data. To normalize means

17:44

to reduce redundancies effectively to take a table uh take one table for instance, split up into multiple and have each entity be part of its very own table. Some academics in the world have named different normal forms quote

17:59

unquote. There's like first normal form, second normal form, third normal form.

This progression of making your data more and more efficient. You can use those as heruristics.

At the end of the day, a few principles might apply. First, take your entities like in this

18:14

case stations and riders and put them each in their own table. And if you add more data, make sure that if I were to add a column, let's say to riders, it is only a data point about riders, not about stations, not about fairs, only

18:32

about riders. And that process can help us make a data set that is more dynamic, more easy to reproduce and more easy to write queries on.

So that is the process here of normalizing. Okay.

So if we have now riders and

18:50

stations, we want to represent them in our table. Well, we could use what we learned about relating last week to ask how could we actually represent these riders and these stations.

So let's say here I again have riders and stations. I want to make sure that I have the right

19:07

relationship between them. Well, if you're familiar with subways, we might say that a rider goes to one station.

And this big T here is the symbol for a station here in Boston for the T's that we call it for the subway. So a rider might go to one station, but of course

19:25

that might not be the full picture. A rider also gets off at some station later on.

So a rider could be associated with not just one station, but multiple. And if you're familiar, at least with any subway system, really the Boston one too, it can often get pretty busy.

And

19:41

so riders might not just go to of course one station or two stations could also have multiple riders that are on a particular station here. So to recap, one rider might be associated with more than one station.

They might get on at

19:58

this first one and get off at this later one. But each station could presumably have more than one rider.

Each station here could have rider A or rider B, the rider up here or the rider down below and even many more than that as well. So

20:15

to put it in the language of our ER diagrams, our entity relation diagrams from last week, we could look at a bit like this where we have riders and stations. Riders visit stations and they're associated like this.

A rider

20:32

must have at least one station associated with them. That's what this horizontal line means.

If they aren't at a station, they aren't really a rider, right? A rider though could have many stations associated with them.

That's what this three prongs down here means.

20:47

They could have one, two, three, four. They could have many stations they get on and get off of.

Now, a station could have anywhere between zero riders, if it's maybe out of commission or isn't very popular, upwards to many. It could

21:04

have two, three, four, five, even hundreds of riders associated with this particular station. So, here is our entity relation diagram for these particular riders and these stations here.

So let me ask what questions do we

21:21

have on these relationship between riders and stations and how to design this table so far. >> Sir I want to ask that you have used the safe ID for stations and riders.

So uh that maybe uh give us problem in coding.

21:37

>> Yeah a good observation. And so you might have noticed that in the riders table and in the stations table I gave the same kind of ID like I had 1 2 3 for each of them.

And let me just show you that again here. I'll come back to some slides and I'll show you again the

21:53

riders table where we had Charlie, Alice, and Bob ID 1 2 3. Same for the stations.

We had stations Harvard, Kendall, Park Street, ID 1, 2, 3. And to your question, isn't that a problem?

Well, I would argue in this case it's

22:11

not so long as we keep clear that these ids are for stations and these IDs are for riders. And we'll see how to do that using our SQL keywords later on.

But again, so long as we have an ID just for our riders and an ID just for our

22:28

stations, we can keep these separate even if they might have the same values. But a great question here.

Let's take just one more. Uh regarding the entity relationship diagram, how is it possible for stations to have uh a possibility of zero

22:45

writers, but writers must compulsorily have at least one station? >> Yeah, good question.

So, this might be up to you and how you formulate it, but for me, let me show the diagram again. I'll go back to over here.

Uh in my

23:01

mind, to be a rider, you have to have visited a station. If you aren't visiting a station, you aren't really a rider right now.

Presumably, there are stations that were built but aren't really being used right now or aren't really uh in service yet. That could be a station that has no visitors.

So, you

23:18

could argue let's make sure every station has at least one rider and every rider may or not have to visit a station. For that, I would say let's kind of we could probably reasonably disagree there and talk about how we could represent the diagram here too.

But a great observation and a good um

23:34

other critique of this this system here. All right.

So let's now turn to representing this in our database. I'll go back to my computer and we'll learn about this new SQL uh keyword SQL

23:51

statement. This one called create table.

Create table allows us to as the name suggests create a brand new table. So, let's do just that in our new database to represent riders and stations.

I'll

24:06

go into my terminal and I want to make a brand new database. I'll call this one MBTA.

DB because MBTA stands for the Massachusetts Bay Transportation Authority, the people who run the subway essentially. So, I'll do SQLite 3 MBTA.

24:23

DB hit enter and I'll type Y to say yes I want to create this brand new database. Okay.

Now if I type schema I see nothing but that's expected. I don't have any tables yet.

I have

24:40

nothing inside this database. It's up to me to create these tables myself.

So what I'll do is clear my terminal and let's start first with riders. I might create a table for riders.

So I'll say create table and now I have to give that

24:56

table some name. I might call it riders here and then in parenthesis like this I can specify what columns should be part of this table.

So let's start first. I'll hit enter here and continue this

25:11

query. Now I'll by convention just indent four spaces.

One, two, three, four. And I'll give an ID to each of these riders as we saw before.

I'll say ID here is one of my columns. Now to create a new column, I'll follow

25:29

this up with a comma and hit enter. I'll again by convention for style just indent four spaces.

And what's my next column? Perhaps a name for these riders.

I'll give this column the name name and I'll leave it at that. Once I'm done

25:47

adding columns, I no longer need to have a comma. I could simply close out this query, this statement.

I could hit enter here, say close parenthesis to close the top parenthesis here, semicolon, hit enter, and now nothing seems to happen.

26:05

But that's actually a good sign. So let me type dots schema hit enter and I'll see the result of that statement before create table if it doesn't already exist riders and riders has inside of it two columns ID and name.

Okay, let's keep

26:22

going. Let's make one for stations too.

I'll clear my terminal and I'll say create me a table called stations and include actually not station uh if you ever want to uh let's say fix this kind of table here let me try closing semi

26:40

closing the parentheses hit semicolon enter I'll get a syntax error I can restart I'll do L now I'll do create table stations plural open parenthesis enter I'll indent by four spaces 1 2 3 4

26:56

and now I'll similarly include an ID for each of these stations. I'll say ID, comma, and then what else should I have?

Well, stations tend to have a name like the Kendall MIT station, the Harvard station, the Park Street station. So,

27:12

I'll say I'll give each of these their very own name, what else do stations have? Well, they might also have a line that they're on.

Let's say it's the red line or the blue line or the green line and so on. I'll

27:27

have a space for them to write in their line that they're a part of. Okay.

And maybe I'll leave it at that to keep it simple. I'll say stations have an ID, a name, and a line.

Now I'll close this out. I'll say end parenthesis semicolon.

Hit enter. And nothing seems to happen.

27:43

But if I type dos schema, I'll now see my riders and my stations tables inside of my database. Now one more step here.

We have riders. We have stations.

But we have to relate

27:59

them. We have to relate them using this many to many relationship as we saw last week.

So let me try making a table to implement this many to many relationship. And if you remember we might call this kind of table a junction

28:15

table, an associative entity, a join table. has a lot of names, but it looks a bit like this.

I'll create this new table to represent, let's say, visits. A rider visits a station.

So, I'll call this table visits. And inside, I'll make

28:34

sure it has two columns. One for a rider ID to represent a rider and one let's say for a station ID to represent a station.

Now, when I see a rider ID next

28:50

to a station ID in this table, I'll know the rider with that certain ID visited the station with that certain ID. So, I'll close this out.

I'll say end parenthesis here semicolon enter. And finally clear my terminal type dot

29:06

schema and I can see I have riders stations and visits between riders and stations in this associative entity this junction table or a join table up to you what you might want to call it in this case. Now what questions do we have

29:24

>> why we have not use the primary key and the secondary key in this table? >> Uh good question.

So we're going to get there in just a minute. But if I look back at my uh terminal here, my schema, I'll see I really just have column names.

And we saw before when we typed

29:40

schema on our long list db, we had more than just column names. We had column names, we had perhaps data types, we had primary keys and foreign keys.

So we'll get to that in just a minute, but suffice to say for now, we're going to keep improving this over time. Let's

29:56

take one more. Is it required to put spaces the four spaces the indents or that's just for the visual look?

>> Yeah, great question. Is it required to have these four spaces before each column name?

And in fact, no, it's not.

30:13

But it makes the code more readable. So if I I I could put this all on one line.

I shouldn't, but I could. uh if I have um instead this new line followed by four spaces, I can make this more um readable for myself and for my colleagues too.

Good question.

30:30

Okay, so to our earlier point, there are things that are missing from this schema. Like we have column names, but as we saw before, we should ideally specify what kind of data should be able to go into

30:47

each of these columns. And for that, we'll need some new ideas to talk about here.

So let's focus now on this idea of data types and storage classes. Data types and storage classes are two very

31:02

similar but distinct ideas and they're uh distinct in a way we'll talk about in just a minute. Now SQLite has five storage classes five kind of storage five kind of types so to speak of values that can values that can hold.

So let's

31:18

talk about the first one null for instance null in this case means nothing. There's nothing that actually is inside of this value.

It's kind of a sentent value to mean nothing is here. Integer means a whole number like one two three four five.

Real talks about

31:34

decimals like floating points like 1.2 or 2.4 and so on. Text is used for characters and blob kind of a funny one.

Blob stands for binary large object and it represents the data exactly as I give

31:51

it to this value. If I tell it to store 1 0 1 0 1 0, it'll store exactly 1 0 1 0 1 0 in binary.

So useful for storing in this case like images and video files and audio files, things that have some structure we don't want to mess around

32:07

with. Now let's focus on this idea of a storage class.

These I'll say are storage classes and not data types in SQLite. Now a storage class like integer

32:22

can comprise can hold several data types. Notice how there are seven different data types that could be stored under this integer storage class.

We have a six byte integer two byte integer uh eight and zero and so on. It

32:39

could be any of these particular types but each of these types is under the umbrella of this integer storage class and SQLite itself will take care of making sure uh it uses the appropriate data type. Like if I give a very large

32:56

number like let's say 4 billion or 6 billion or even bigger than that it'll probably use a longer that is a bigger bite integer to store that kind of value. If I give it a smaller one like 1 2 3 or four, it'll probably use a one

33:12

bite or a two byte integer for that. But SQLite's idea is that I as a programmer shouldn't have to care if I use an 8 byt or a one bite or a two byte integer.

I just care that I'm using integers, whole numbers. And they give me a storage

33:28

class to use any of these up to their choice here as well. Now let's look at a few examples of values in SQLite that we could store.

Well, we have perhaps the red line as some text. And because this is

33:43

characters, it's quoted. We could use the text storage class to represent this particular value here.

We could have maybe an image. To the earlier point, we could say, well, this image might be best represented using a blob, a binary

33:59

large object to keep all of these pixels exactly as they are in this image. But we do get some choice, some interesting design challenges.

We look at the idea of fairs. So let's say to our point earlier, fairs are 10 cents back in the

34:17

1950s or so. Well, 10 cents we could store as an integer, which seems just fine.

But this could get confused. I'm talking about dollars here or cents.

Maybe it would be better, let's say, if I did this. A dollar sign 0.10.

34:36

And what might that be stored as? Well, probably text, right?

I could say this dollar sign isn't really a number, but now I have to include it. So I'll say this will be quoted essentially as dollar sign0.

Now there's some downsides here too.

34:52

Like let's say I have uh I want to add these up. Well, I can't add up text.

Like what does it mean to say dollar sign plus dollar sign.20. I can't do math with text.

So maybe it would be better after all if I used a

35:07

real or a decimal like this 0.1. But I mean even here you run into some problems.

If you are familiar with um how these are represented in binary you might know that decimal values or floatingoint values can't be perfectly

35:24

precisely represented. And if I talk talk about 010 the computer might store 0.100000005679 it could get very wonky out to the many many decimal digits down below here.

So tradeoffs and challenges overall. Let's

35:42

look at these three though. I have the first one to store as an integer.

I'm trying to store fairs here. Second one as text and the third one as a floating point or a real in this case.

Let me ask for some opinions here. Which one would

35:59

you use and why? We're trying to represent fairs in this case.

>> Thank you. I prefer using integers because of course I need to get the calculation very accurately.

Uh that's my point of view. Well, sometimes I can use float

36:16

but you know like you said before it can get very wonky if I get you know if I really need that kind of precision I don't really recommend using floats. >> Yeah, good point.

So let me go back to some slides here. You might argue for

36:32

the integer because you know you can precisely represent integers. And let's say I want to add up fairs over a lot a lot of riders.

This might be useful for me because I know that each number will be perfectly precisely represented. I can do lots of big kind of math with

36:47

this number here. To your point, this decimal might kind of as you should get wonky later on towards later decimal points.

I might get some unexpected results if we add up these overall. Let me ask though are there any proponents of this floating point value or a real

37:06

value? >> Okay.

So I think the uh I think this float is uh in the the number of for example for each pair like the uh like the answer that like uh truncation

37:24

probably suggest one of the comments there. >> Yeah good point.

Right? So if we talk about using this float value, I mean one thing we could say for it is that this decimal could be, you know, it's it's more accurate to say like we're working with dollars now and we could have maybe 10 cents which is only 0.1 of a dollar.

37:41

I totally hear that point as well. And the point we're making here is that they're really just tradeoffs among these data uh storage classes to use.

Whether you're using integers or real values, it just depends on your use case and what you're designing for. So be sure to read up on trade-offs among

37:58

these data types and determine for yourself which one should you best use. Okay.

So we have now these storage classes to store values. And it turns out that columns can be made to store

38:13

certain storage classes or prioritize certain classes. And the key distinction here is that columns in SQLite don't always store one particular type.

Instead, they have type affinities,

38:29

meaning that they'll try to convert some value you insert into a given cell or given row to the type they have the affinity for. Now, there are let's say five type affinities in SQLite.

text

38:45

columns can be of the type affinity text meaning they store just characters at the end of the day. There's also numeric which stores either integer values or real values depending on which one seems best to convert to.

You have integer type affinity which means it'll store

39:01

whole numbers real to store floating points or decimal values and we have blob here again our old friend to store binary exactly as we get it. So whereas before we were talking about storage classes, those are associated with individual values, type affinities are

39:16

now associated with individual columns. So let's see some example of might how this might work in SQLite.

Let's say I have this table of fairs and we've decided to store fairs as integers. Well, if I said that this column called

39:34

amount has the affinity for the text storage class, what would happen is if I insert this integer 10, it would look a bit like this later on. It would be converted to text be quoted in a sense to represent is now been converted to

39:50

some set of characters. Let's say I insert this value 25.

Well, 25 has a storage class right now of an integer. It is a whole number.

But if I insert this into a column that has the text

40:06

affinity, it'll be converted into in this case text at the end of day. Let's do the opposite.

Let's say I have my fair as a string, some text. In this case, I want to insert it into this

40:21

column called amount. But now amount has the integer type affinity.

Well, if I insert 10 quote unquote into the column amount, I'll get back not 10 the text, but 10 the integer because again amount

40:39

this column here has an affinity for the integer storage class. Let's try this 25 some text again.

I'll insert it into this table. Now I'll have 25 as an integer.

So this is how SQLite allows us

40:57

to give certain columns an affinity for certain types that they'll try to store values of that type so long as we insert values that could be feasibly converted to that type here. So let's go back to our schema and try to improve it now to

41:14

use not just column names but also type affinities to store certain data of a certain type. go back to my computer here and let's improve this once more.

So I'll go over to my table. And now I

41:31

probably want to improve the design here. And often if I want to improve this, I might just need to erase what I've already done.

So let me introduce this new keyword, this new statement uh called drop table. To drop a table means to delete it, to remove it as

41:47

effectively. So let me try doing this for riders stations and visits.

I'll type drop table riders semicolon enter. Nothing seems to happen.

But if I type dot schema or riders is gone. I'll try drop table

42:04

stations. Then semicolon hit enter and type uh let's see dots schema again.

No more stations. I'll try drop table visits semicolon.

enter and then dots schema and our table our database is

42:21

really gone. There are no more tables inside of it.

So let me propose that instead of working inside of the SQLite prompt like typing out again and again and again create table riders, create table stations, create table visits, let me be a bit more efficient about this

42:38

and create myself a schema file that I could reuse throughout this lesson and also wait it on while I'm working on this database on my own. To do that, let me quit my um SQLite prompt here and let me type something like code schema.sql.

42:55

I'm just creating this file called schema.sql. Now, aSQL file allows me to type in SQL keywords and have them be syntax highlighted so I know what's going on inside of this file.

So, let's just try

43:11

this once more. I'll type create table riders and inside I'll say it has the ID column of what type ofity?

IDs are whole numbers. So perhaps integer in this case

43:26

I could say ID has the integer type affinity. Now let me say that writers also have a name.

And how could names be best represented? Maybe text, right?

Characters here. So I'll say name and text.

Now I'll include a semicolon to

43:43

say this is the end of my create table statement. And before remember how I had to kind of error out or I had to like backspace and so on to improve the design.

Here I can literally just point and click and edit this file to improve

43:58

my schema. And I'll later on apply this in my um database using a command that we'll see a bit later.

So let's keep going here. I'll say create table stations and inside of this stations table I'll make sure it has an ID column

44:14

of type integer a name column that probably stores some text and a line column that also stores some text where name is the name of the station like Kendall MIT Harvard and line is blue

44:30

line or green line what line it's part of in our subway let me try now visits I'll say create table visits and then I'll do rider ID which has what type affinity probably integer and then I'll do station ID which has this same type

44:47

ofinity it's we're talking about integers whole numbers here for ids semicolon to finish this statement now this is my schema as a whole I have riders stations and visits but now I want to apply this schema to my database

45:05

so what could I I could reopen it. Let's say I'll do SQLite 3 mbta.db in my terminal.

And now I want to read in this schema.sql file. That is run the commands that are inside of this file.

So what could I do? I could say read

45:23

schema.sql where readad is a command that says take whatever file you give me like schema.sql SQL and read it into this database running any SQL keywords you come across there. I'll hit enter and now nothing seems to happen.

But if I

45:41

type schema, I now see my schema improved. And this is helpful for me because I what I could do now is I could just edit my schema.sql file and rerun it and rerun it to make sure I now have these tables being improved over time.

45:58

Okay, so this then is our new representation of our database. We have riders, of course, their own entity, and stations.

They have an ID and a name, and stations have an ID, a name, and a

46:15

line. We've also now included these type affinities integer text integer text to tell SQL what kinds of storage classes could be put inside of each of these columns.

Now before we keep improving this, let

46:31

me ask what questions we have on these storage classes and type affinities. Sir, you were using uh when you were creating the table, the table was not uh in the line.

So when uh we search for

46:48

the authors or books, so it comes with a perfect table. So how can we make a perfect table in the SQL?

>> Yeah. Do you mind um clarifying what you mean by like the the perfect table?

>> Uh sir, I mean that uh it was arranged in something like in boxes block boxes.

47:05

>> Ah good question. So um before we're able to see the results of our queries inside some boxes in our terminal and that is actually a mode of SQL light.

I think you type like do mode table to see results in that version. Um here though

47:20

we have no data inside of our tables. So we can't really select anything from them.

Like if I go to my terminal here and I try to select some data from this riders table. Let me say select star from riders semicolon.

I won't get

47:37

anything back. Next week though, we'll see how to insert and add and update and delete data inside of these tables.

At which point you could write select star from riders and see some data you've inserted yourself. Great question.

Let's

47:53

take one more here. >> Yes.

Uh I would like to know if you have a column data type uh boolean. >> Yeah.

Do we have a a boolean type affinity? Let's say so here we don't at least in SQLite.

Some other DBMS's

48:10

database management systems might have bool or boolean true or false. Right?

Let me show you this instead. If I go to my terminal, I can see if I type schema, I haven't used boolean.

There's no need for me in this case, but I have used

48:25

integer. And integer for SQL I can kind of for uh serve the same purpose.

I could have the integer zero or the integer one to be true or to be false or true respectively. Zero being false and true being one I believe in this case.

But good question.

48:42

Okay. So to the earlier question like we've improved our tables.

We now have type affinities for our columns but we don't yet have this idea we talked about last week which was primary keys and foreign keys. this idea of trying to

48:59

uniquely represent each item in our own table using primary keys and trying to reference that primary key from some other table using foreign keys. So let's try to work on that now.

And for this we'll need this new idea called a table

49:16

constraint. In SQLite you can apply what's called a constraint to your entire table.

A constraint means that some values have to be a certain way. Like let's say for a primary key, primary keys must be unique.

They can't

49:33

repeat and they must be at least in our cases going to be integers to be able to uh for quickly like add on to them over time. Similarly for foreign keys, well a constraint is that if you have a foreign key, you better find that value in some

49:49

other table. Otherwise you violated this constraint of having a foreign key.

So we have two kinds of table constraints among others but two of these are primary key and foreign key and we can apply these to our table by applying

50:05

them underneath the columns we tend to say will be inside of our table. Let's try these two here now.

So let me come back to my terminal here so we can implement our very own primary key and foreign key constraints. We'll go back to my SQLite terminal and clear my

50:20

screen. And let's then pull up our schema.sql file.

So we can keep modifying our schema. I can now see I have the writers table stations and visits.

And I have some columns that could be primary keys or foreign keys, but I need to declare them as such. So

50:37

here in the riders table, what was our primary key? Well, it was ID.

Every rider should have their own unique ID that should not be duplicated across any two riders. So to ensure that constraint is applied, I could follow this up with a comma and then say primary key ID just

50:56

like this. Now ID is the primary key of this rider's table.

I can go down to stations and ask what was my primary key? Well, similarly, it was ID, this ID column on line 8.

So I'll type a comma followed up with primary key ID. And now

51:14

that ID column is a primary key. It has that constraint applied in stations.

But now if I get down to visits, I have a few more options. Visits here actually doesn't have its own ID column that I created.

I instead have a rider ID and a

51:32

station ID column. So a few options here.

One option is actually to make a joint primary key. I could have a primary key composed of two columns, both rider ID and station ID.

If I applied that constraint, that would mean

51:49

that if I were to ever insert a row that had the same rider ID and the same station ID as another row, I would trigger a constraint violation. Every row that has a rider ID and a station ID has to be unique in their combination of those two values.

to write that kind of

52:06

scenario. I could follow this up and I could say similarly the primary key of this table is not just rider ID like this but it's also station ID.

Now this is a joint primary key constraint. But if we think about this logically I mean

52:22

it kind of stands to reason that somebody would visit a station more than once and I don't want to make sure that every combination of rider and station ID should be unique. I want people to be able to visit a station more than once.

So maybe not the best design for this table, but I could certainly use it in

52:38

other cases or other contexts. One other option would be to uh do what you did before and to have maybe the ID column here of type integer and then down below make that our primary key a bit like this.

And now visits has its own ID

52:54

column. But actually SQLite by default will give me its very own primary key one called row ID.

It's implicit. I can't see it.

But I actually could query it. I could query for row ID all one

53:09

word and get back a unique primary key for this table. SQLite is automatically created for me.

Now we have seen the primary key options. What are our foreign key options?

Well, it seems like rider ID and station ID are the foreign keys of this table where rider ID

53:26

references the ID column in riders and station ID references the ID column in stations. So to codify that to make that a reality in our schema, I could follow this up with the foreign key.

The foreign key of this table is rider ID

53:42

and it references it references the riders table and the ID column inside of it using this syntax here. Now I could keep going.

I could say I have more than one foreign key. I also have a foreign key foreign key called

53:59

station ID and that references as we said before the stations table and the ID column inside of it. So now here I have my completed schema.

I have a primary key for the tables I've declared an explicit column for a primary key and now I also have foreign key constraints

54:16

for those columns that should be foreign keys. So now let me ask what questions do we have on the schema or on primary keys and foreign keys.

>> Uh so yeah I just noticed that whenever before we hadn't added the affinities

54:33

and the keys we were not applying commas after each column name. So what is the difference there?

>> Yeah, a good catch. Let me um kind of show you what this looks like in my terminal so you can see it live.

Um you I think had noticed that before we had

54:48

let's say this primary key ID um constraint in writers we had done something like this. Let me just copy paste that.

And we had removed this last column or comma from the name column. Is that right?

And if that's the case well it's um just convention just style here.

55:06

So if I want to keep adding some constraint or like a new line to my table, I should include a comma here. This name column was the last um portion of my table I had specified.

I have a column called name that has type

55:22

affinity text, right? But now if I add this new constraint, we'll have to follow it up with followed up after a comma from this new column here.

Notice now this constraint primary key ID is the last um let's say attribute of my

55:40

table I specified. I no longer need to include a comma at the end of it.

So whatever is the last portion I should have a uh I should not have a comma after but everything else I should. Let's take one more question here too.

>> Would it be okay for the visits table to

55:56

have an ID column as well? >> Yeah, a good question.

It would be okay for the visits table to have an ID column as well. It certainly would be.

We could define our very own primary key for this table, too. So, let me go back and show you how that could work.

I'll go to my visits table here, and I could

56:14

try to add my own primary key to this table. I could say ID, make a new column here, make it of type affinity integer like this.

Let me scroll up. And now, let me add some new constraint.

I could say because I've made my very own

56:30

primary key, I'll say primary key id. Now this table has a primary key that I've created called ID.

And this will be in place of SQL lights that it would have made called row ID itself, but

56:46

hidden from my own view. Okay, let's keep going then.

We've seen table constraints. We've seen type affinities, but we could probably do more to improve the design of this table or this database.

So let's introduce one more

57:02

kind of constraint. This one called a column constraint.

So whereas a table constraint applies to the table as a whole, a column constraint applies to a particular column. Let's say maybe I want a column to have certain uh data

57:19

inside of it. I maybe I want to make sure it doesn't have null values and so on.

I could do that with column constraints. There are four in SQLite.

Check, default, not null, and unique. And each one does something different.

57:36

Check allows me to make my very own check. Like check to be sure that this amount is greater than zero.

Or I could use default. Default means if I don't supply a value, when I add a new row, it'll just use a default value instead.

57:52

Not null means I can't insert null or empty values into this column. In fact, it's required.

Unique means I want to make sure that every row in this column is a unique value. It doesn't appear

58:08

twice in my data set. So, let's try applying a few of these to our schema here.

Go back to my terminal. And now, let me check out this.

Well, I could try applying the notnull constraint when I

58:24

know I want a column to be required effectively. Now, where could I best apply that?

Maybe I could apply that to the name column in stations. Like stations must have a name.

So, I'll say the name column cannot be null. It

58:39

cannot be empty in here. Line also should probably be not null.

A station must be part of some line. I can't have an empty value for line.

So I'll say too this should be not null.

58:54

Now I could apply this up at name. I could say riders must have a name too.

Let me try that. I'll say text not null.

Or I could leave it optional. I could say maybe text just on its own and let writers choose to supply a name or not.

59:11

Now the question here is should I apply not null to my primary key columns like ID not null or ID not null here you might think that you should for thoroughess sake. Well it turns out that

59:26

when you apply the primary key table constraint down below here this already ensures that there are several constraints applied to this particular column called ID. among them being that ID cannot be null.

So no need to

59:42

duplicate this and say that this ID cannot be null when I already have it specified down below that ID is a primary key. Let me check others here.

You might also think could I do it for rider ID and

59:57

station ID? Should I include not null here?

Rider ID not null. Station ID not null.

Well, that would be a good thought, but again, we're taken care of by our table constraint using our foreign key here. Again, this constraint

00:13

will say if rider ID doesn't already exist in the ID column of riders, I can't insert that value. And we could probably presume that if riders ID is a primary key, well, null will not be part of this column.

And therefore I already

00:30

can't insert null for rider ID or station ID. This would be in this case redundant.

So not null is good when you have columns that are neither primary keys nor foreign keys and you want to make sure that they have all they always

00:47

have a value that they are never uh null effectively. Okay.

So that is not null and we could keep going here. We also had one called unique that makes sure every um every value every row in this column is

01:04

unique. Where could we apply this?

I could try to apply it let's say to the name of a station like station should have unique names. So I'll say not null and unique.

Now this column has two constraints. The first not null.

It

01:20

should always have a value. The second unique the value shouldn't repeat throughout this column.

line I might leave without this constraint. I could imagine two stations being on the same line like both on blue.

I'll allow that in this case. Now again, we could try to

01:37

apply unique to our primary keys or our foreign keys as I just did here, but it's already taken care of for us using this primary key constraint. A primary key again is always going to be unique and never null.

So we'll take advantage

01:53

of that already using our primary key and foreign key constraints here. Okay.

So we've seen unique and not null. And I might argue we're at the point where this schema seems to be fairly

02:08

optimized at least using our column constraints, our table constraints, our type affinities and so on. So let's ask then what questions do we have on not null and unique if any.

>> So basically to recap if I understood

02:26

correctly it's not precisely about not null and unique but about the concept of the key uh labeling a key immediately give us the attribute of not null unique and to be referenced. Right?

>> That is true. So when you use a primary key or a foreign key constraint, there

02:43

are other constraints that go along with that constraint. A primary key for instance must not be null.

Um it must be unique and so on. So it would be redundant to apply that again to say that this primary key should be unique or not null.

Good clarification there.

02:59

Okay. So I think we're at the point where this schema is pretty well set for us.

And we're going to need to think about though how we use this in the real world. Like if this is our schema here, we have riders and stations.

Well, what

03:17

could we do? I mean, riders tend to I mean, they could register for the subway.

And riders, well, they do visit stations, but I think if we applied this to the real world, we'd see that this isn't quite how it's actually done. Like riders don't really register that often.

03:34

if uh a rider who's new to the city comes in, they want to ride the subway, they should be able to ride, too. So, it turns out, at least here in Boston, the MBTA doesn't really track riders, per se, but they do track what we call Charlie Cards.

If you want to ride the

03:49

subway, you get a Charlie Card. Charlie card allows you access to the subway, keeps track of your fair, your balance, and so on.

Allows you to swipe in to certain stations. So, and when we come back from a break here, we'll see how we can actually implement these Charlie cards instead of riders to make our

04:06

system more efficient and more in line with what happens in the real world. We'll see you all in just a few.

And we're back. So, we saw last time this challenge of designing a system for the Massachusetts Bay Transportation Authority to represent riders and

04:23

stations, people who get on a subway to go around Boston. But as we also saw, we learned that the MBTA doesn't keep track of riders themselves.

They keep track of Charlie cards, this card that a rider

04:38

might carry in their pocket. And they can scan this card at a station to enter or even to exit in some cases to make sure that their fair is counted as they enter that station.

So let's think now how to improve our schema to represent

04:54

not just riders like me but Charlie cars themselves people might carry around when they enter the subway station. Well we saw before we had riders and cards.

But our goal now is to remove riders from the picture. Focus only on cards.

05:11

Well cards as we've seen might make us swipe at a station. If I enter Harvard station, I might swipe my Charlie card to enter that station.

And we could see that a card would have maybe many swipes

05:27

involved. Like if I swipe at Harvard, I might also swipe at MIT or swipe at Park Street and so on.

We could see that a swipe can only happen at a single station at a time, though. Like if I swipe at Park Street, I'm only swiping at Park Street here.

And similarly, a

05:44

swipe might only involve one single card. So if we think about these entities and how they relate, we could also think about what kinds of columns we should have inside of each entity.

In this case, I would argue we should have something a bit like this. We could say

05:59

that a card has a ID for its primary key in this case. Similarly, this card makes a swipe and this swipe has itself an ID as well as a type, some time that that swipe happened and an amount or a

06:16

transaction that is involved. So, for example, let's say I swipe in at the Harvard Square station.

That type of swipe might be to enter the station at some certain time. Now, associated with that swipe is also some amount in

06:32

dollars that happened to be subtracted from my card. Like, let's say the fair nowadays is $2.40.

Well, that amount is subtracted from my card from this swipe. Now, of course, I do all of this at a station which has our same columns from

06:48

before, ID, name, and line. So, a similar idea now, but we're replacing riders with cards and adding more information to these visits.

They're instead swipes that could be maybe entering the station, exiting the

07:03

station, or just adding funds to my balance, for instance. So, let's see how we could improve our schema now using some new SQL keywords to alter our tables and add some new ones, too.

I'll go back to my computer and let's see how

07:19

we could try to alter the tables that we've already created. Like we already have a riders table, a visits table, and a station's table, but we could learn some new statements, some new queries to actually update and alter these tables as well.

The first one, as we saw a

07:35

little bit before, is this one called drop table. Arguably the most dramatic thing you can do to a table is just drop it, delete it like this.

So, let's try deleting the writers table from our database. I'll go back to my uh go back to my database here.

I'll type SQLite3

07:54

MBTA. DB to pull up this database again.

And now, if I type schema, I'll see I have a riders, stations, and visits table. But no longer do I want to have the riders table.

I want to remove that. So I'll say drop table riders semicolon.

08:13

Now I'll hit enter. No more riders table.

If I type dots schema, that's gone for my database. Well, what could I do now?

I've dropped the table, but I'd still need to maybe update visits to instead be swipes. I

08:29

could probably leave stations as is. But I want to update a table or alter its schema.

I can use some new commands as well. I'll use this one called alter table.

Alter table looks a bit like this. I can use the alter table um

08:47

statement here and give it some table name like let's say alter table visits and then inside uh this visits table I get to ask what do I want to do? Do I want to let's say rename the table?

I could do that. I could also decide to

09:04

add a column, rename a column or drop a column algether. Let's try first looking at rename to.

I want to rename this visits table to a swipes table representing not just a visit to the station, but a swipe of a card. So,

09:20

let's try this one here. I'll go back to my computer and I'll go back to SQLite and I'll say I no longer want visits to be called visits.

I ideally want visits to actually be called swipes. So let me

09:35

try this. I'll say alter table.

So we saw before visits rename to swipes like this semicolon. Now I'll hit enter.

And now if I type dots schema again I see

09:52

oops kind of strange. I'll type dots schema again.

I'll see swipes. no longer called visits, but now called swipes.

Well, we saw I'd ideally like to add a type of the swipe. Maybe I'm entering the station.

Maybe I'm exiting. Maybe

10:09

I'm just adding funds or depositing some funds. So, let me try adding let's say a new column to swipes.

I'll say alter table add column alter table swipes. Going to name that table swipes like

10:25

this. And then let's add a column.

Add a column called type. And this will have the type affinity text.

I'll hit semicolon. Enter.

And now if I type schema, I'll see well I have a column

10:42

called TTP. So clearly I made a typo here.

I had rider ID, station ID, this new column down below called TTP. I kind of want to fix this, right?

I don't want TTP. I want type.

So probably

10:57

a good chance to use my rename column. I'll come back here and I'll try that.

I'll instead do alter table alter table swipes and I will rename a column. Now I'll say rename the column TTP

11:16

to type spelled correctly. Now hit semicolon.

Go my terminal type.s schema. And now I see over here that type has been added to my table of swipes.

I see rider ID, station ID, and now a new column called

11:34

type. So through alter table, can we go ahead and add new columns, rename them even, or if I wanted to just drop the column al together.

Let's say I add this column type and I change my mind. I don't want it here anymore.

I go back to

11:49

my computer and I could try dropping a particular column. Let me try in this case alter table swipes.

And now let me drop column type semicolon. Hit enter.

And now if I type schema, I'm back to

12:07

where I began. So these are the commands we can use to alter our tables to improve them if we make a mistake during our first create table command or if we want to add more functionality later down the line.

So ideally I could keep using you know

12:23

alter table, add table, create table, drop table and so on. But what I want to do here is just probably start from scratch.

Like I have, you know, stations and swipes and so on. Why don't I just go back to my schema file and use that instead?

So what I'll do is I'll drop table for stations and oop semicolon. I

12:41

will drop table for uh swipes now. Semicolon.

I'll type schema. And now I'll see nothing in here.

I'll quit and I'll type code schema.sql. Let me just start from scratch using

12:57

this schema.sql. So we no longer want to have riders.

We only want to have cards. So I could just rename this table here.

I'll call it cards. Crate table cards.

Now cards don't have a name. They only have some

13:12

unique ID in this case. Now I'll leave stations just as it is.

This seems perfectly fine to me. Stations have an ID, a name, and a line with these constraints applied to the name and line columns.

But now the visits table. Well,

13:29

the visits table is no longer a visit to the station per se. It's more a swipe of the card at that station.

So let's now say visits becomes swipes. And among these new columns to add are the following.

I want to have not just an ID

13:46

for each swipe, not just an ID for rider ID and station ID and so on, but also I want to have a type of swipe. Am I entering the station, exiting or depositing some funds?

So I'll say the type of this uh is going to be a new

14:02

column and the data type this holds will be text enter, exit or deposit for some funds. Now let me try another column too.

I'll include a date time. A date time is like a time stamp.

What time did

14:18

this swipe actually happen? And I'll make this type numeric.

Numeric can store all kinds of dates and times for me in this table. Now let me add one final column.

This one will be an amount. I'll also use numeric for this

14:36

kind of uh column here. And I'll say that this column uh called amount can store in this case integers or real numbers like floats and so on.

I'll probably decide on that when I actually add some data to this table. Okay.

So

14:52

here we've updated our schema to represent that diagram we saw before. I have cards, I have stations, and I have swipes that have some type associated with them.

enter, exit, deposit, some date, time, and some amount that was charged to me while I made this swipe.

15:09

Either I added some funds, which case amount is positive, or I subtracted some fun entering or exiting, in this case, uh, from the station. All right, so I have these tables now, and now I want to probably apply some of those same column constraints we saw

15:26

before. like here it's fine but I also want to make sure I'm not adding some data that I don't want to add to this table.

So I could go back to my old friends these column constraints and we saw before we had uh default and not null unique and check we've used not

15:44

null and unique but we haven't used check or default. So let's start using more than just not null and unique and also focus on check and default what they can do for us here.

I'll go back to my schema and let me just make sure that

15:59

I'm making all the columns that I want to be required actually required. I'll go into my swipes table and I'll say that type I mean this should be required.

I should I I should know whether I entered, exited or deposited some funds. So I'll say text not null.

16:17

Similarly for the timestamp the time this swipe happened I want that to be not null as well. So, I must know what time I swiped the card.

And also, it makes sense for there to always be some amount associated with this swipe. Either I added some funds to my balance

16:33

or I removed some funds overall. So, I'll make this not null as well.

Well, let's see what we could do here with default. default gives me some default value, some initial value to add to this column if I don't specify what

16:51

kind of value to add. Well, this could be good for date time.

Date time here is again the timestamp, the time I swiped this card to enter, let's say, Harvard station. Well, if I want this to always have the current time, I could use

17:09

default. I could say the default value for this column is this special value here.

Current timestamp in all caps. Current timestamp will show me the year, the month, the day, the hour, the

17:26

minute, and second all in one value and insert that into my table. So as soon as I add new row, if I don't supply a time for this datetime column, I'll instead get back the current time exactly as it

17:41

is represented in SQLite. Now what could I do further than this?

I could also try to add a my very own check. Maybe I want to make sure that the amounts here are never equal to zero.

Like nobody should ever be able to

17:56

make a Z transaction or Z swipe. they're always being charged some money or they're always depositing some money in this case.

So I could say amount here has my very own check on this column and inside check I could actually write my

18:12

very own expression to check for. I could say for example amount is not equal to zero using those same operators we saw back in week zero.

This will ensure that any value inside amount will not be equal to zero.

18:30

Let's try it also for type. I mean type can only have a few values.

We saw enter exit deposit some funds. I could make sure that only those values are included inside my type column.

I could say check

18:45

that type is in some list of values. Going back to week one here we talked about in I could say maybe the type is in enter exit or is in deposit.

So now

19:00

when I have this table called swipes I'm representing what I'm actually doing when I go to Harvard station. I have here a visit for myself.

My very own ID here which I'll update in just a second. I have a station ID where I'm actually going to visit.

I have a type that I'm

19:17

going to use to enter, exit, or deposit some funds at this station. I'm doing at a certain time, and I have an amount associated with this transaction.

Now, there's one thing to fix here, which is that we're still talking about riders inside our swipes table. So,

19:35

let's fix that here, too. I'll go back to my computer, and let's try fixing this.

I have a rider ID inside of my swipes table, but no longer do I have riders, I have cards. So, let me say that this is now a card ID.

And down

19:52

below in my foreign key, I'll say that this card ID column references the ID column in cards. And I think this should represent everything I want to represent about swipes at the station.

20:08

So let me ask now what questions we have about this new schema and the constraints we've applied. >> I wonder how how could you delete or drop table riders when you use uh ID as

20:23

a foreign key. I tried to do that but I got an error.

>> Ah yeah. So you're getting into some more advanced stuff here.

And suffice to say for now, my foreign key constraints aren't actually being checked right now, but yours might be. If you try to drop a

20:40

table that has actually has some data is referenced by a foreign key, SQLite will warn you, perhaps just tell you you can't do that because this ID is referenced from this table over there. So in that case, best to delete the value that has that foreign key and then

20:55

proceed with dropping that table al together. Good question there.

Yeah, let's take one more. >> How much this syntax uh same the other SQL languages same as SQ uh same as SQL like SQL languages.

21:13

How much the syntax same? >> Yeah, good question.

So here we're using the SQLite database management system. It is similar to but distinct in some ways from others like my SQL or Postgress SQL.

I would say that most of what you're doing here could also be

21:29

ported to MySQL and Postgress SQL with a few exceptions you might need to treat on a case- by case basis. In fact, the developers of SQLite built things so that it would be easy to port their database schemas to another schema like my SQL or Postgress SQL as well.

But

21:46

good question there. All right, let's take one more question here.

Imagine if we put in the ID, we don't put any data type, it's going to give us in the schema, it's going to give us a text or

22:06

Yeah, great question. So, you're asking if I didn't tell SQLite what kind of type affinity a column had, what type affinity would it actually have?

A great question. In this case, by default, SQL light gives the numeric type affinity where numeric can store integers or real

22:23

values. But if you give it like a string of text like let's say red line, it will store that for you too.

Um kind of uh non-intuitively, but it will. But the default uh type is numeric in this case if you don't otherwise specify.

Good

22:38

question. Okay, let's come back then and let's focus on wrapping up on a final few pieces here.

So, we've seen some table constraints which we applied primary keys and foreign keys. We saw column constraints where we could make sure

22:54

that certain values were given to us through not null. We could also make sure that the uh uh let's say the value is in some list of values using check or making sure that it's not some value also using check as well.

default allows us to specify a given value for every

23:11

new insertion of rows here. And this is actually pretty important to have not just you know schemas that have column names and um let's say you know type affinities as well but also constraints to make sure the data we insert works

23:27

well for us. And there actually a story behind this person who's on the Charlie card.

Uh, this person who's on the subway, his name is Charlie, and he's perhaps the most famous subway rider in all of Boston. Back in the 19, let's say

23:44

mid1 1900s, the uh band called the Kingston Trio wrote a song about this man named Charlie. Charlie supposedly got on at the Kendall Square station where MIT is, and he made for Jamaica plane.

But once he got Jamaica plane, the guy asked him for one more nickel.

24:00

And well, he didn't have that nickel. So he got stuck on the train for years and years and years and couldn't get off of the subway.

So keep in mind Charlie when you're adding your own database constraints, making sure that if you get the train, you're able to get off of it at some point, right? Don't end up like

24:15

Charlie in this case. So with this in mind, you're able to design your very own database schemas that keep not just certain columns involved, but also type affinities for those columns.

Types that those data types types of columns can store. You're also able to apply

24:31

constraints to those columns to make sure that the data you're inserting is data you actually want to have in that column. Next time we'll focus on actually adding data to our columns to actually write data to a database file to insert, update and delete that data all together.

So with that in mind,

24:48

we'll see you next time. [Music]

25:08

Well, hello one and all and welcome back to CS50's introduction to databases with SQL. My name is Carter Zeni and last we left off we learned how to create our very own database schemas.

It is a way to organize data in our database. Today

25:24

we'll learn how to actually add data to our databases to insert to update and delete data. We'll do all of this in the context of Boston's very own Museum of Fine Arts or the MFA for short.

So, the MFA here in Boston is a century, well

25:40

maybe about a century old museum that has many artifacts and artwork inside of it, both historical and contemporary. And it's worth asking, I mean, how do they keep track of the thousands of items that are in their collections?

What could they possibly use? Well, chances are they're likely using some

25:57

kind of database. And on that database, they want to do four actions we learned about back in week zero.

They could perhaps create data to add data or insert data to the database when they get some new piece of artwork for instance. They might want to query their

26:13

database to read from it. They could also update data, change the artist, change the artwork in some way.

And they could also just delete data to remove it all together. But if we think about these creating, reading, updating, and deleting, we'll notice that reading,

26:30

updating, and deleting, we can't do those if we don't actually have data in our database. And so today, we'll see how to create data, how to insert data into our very own database.

Now, let's think about the MFA's collections. They're collection of art

26:47

and artifacts. And let me propose they have a database looks a bit like this.

It's a single table and it has a title column, an accession number, which is a fancy way of saying a unique ID internal to the museum and also a date it was acquired. We have of course a primary

27:04

key on this table called ID. And let's think, well, the museum might want to acquire this piece here, this one called profusion of flowers.

Well, how could they log that this artwork is inside of their database? They could maybe just

27:21

add a new row. They could say, "Let's put Profusion of Flowers as the first item in our collections here.

We'll give it a title, an accession number, which again is just unique ID internal to the museum, and the date it was acquired. And that row then has its own primary

27:37

key to identify this row uniquely in our database." Now, let's say they get another piece of artwork. They get this one called Farmers Working at Dawn.

And they want to add this one to their table, too. Well, they could do the very same thing.

They could just add a new row. They could say, "Let's make a

27:52

title, an extension number, and a date it was acquired in this brand new row here for that piece of artwork." And maybe they get another one, too. Same thing.

Maybe they'll get back spring outing and they want to add this to their collection. They could simply add another row like this.

28:09

Now, it turns out that the database administrator behind the MFA might be running a SQL statement that looks a bit like this. Insert into.

We can use insert into to add a new row to any

28:25

given table. And notice how insert into needs to know a few pieces of information.

The first is what table to insert into. What is the name of that table?

Second, it needs to know what columns are we adding data to inside of

28:42

this table. We give it a list of those columns here.

Then, of course, it needs to know what new values should go into this new row. For each of these columns like we saw before, is it profusion of flowers?

Is it spring outing? etc.

Here

28:58

we can see that we have this list of values. And notice how value zero, the first value in this list, corresponds to the new value that will be inserted to this first column.

And we can keep having value one or column one, value two and column two, each one aligning

29:14

with that particular column there. So let's see an example of this actually in code to understand it a bit more concretely.

I'll go back to my computer here. Let's actually create our very own database that involves this schema of

29:30

having our very own table which we can keep artists and artwork and um artifacts as well. So I'll type SQLite 3 MFAD db to create a database for the Museum of Fine Arts abbreviated as MFA.

29:47

I'll hit enter here and notice how I can now have access to my very own MFA. DB inside my SQLite environment.

Well, now I can type schema to see the schema of this database. So, if I hit enter here,

30:02

well, nothing's there because I just made this database. There's nothing in it yet.

Well, it turns out I do actually have a schema file prepared for me already in schema.sql. Here, I propose we make a table called collections.

And like our table

30:18

visually, it has, let's say, four columns. one as an ID, the primary key of this table, one for a title, one for the accession number, the unique ID internal to the museum, and finally the date it was acquired.

And notice here

30:36

that title has to be actually added to our database. It can't be null.

It can't have an empty value. Same thing for accession number.

And further, accession number has to be unique. I can't have two items with a different accession

30:51

number. Otherwise, I might get them confused in my database or inside my museum archives as well.

So, let me then add this schema to my database. And I can do so with this command right here.

This is a SQLite command called read

31:07

that we saw a little bit last week as well. I could type read and then the name of this file I want to read.

So I'll say read schema schema.SQL hit enter. And now if I type let's say dots schema I can see that same SQL

31:25

schema same database schema now inside of my terminal. Okay.

So now, as promised, let's try adding some rows to this table because right now, if I do select star from collections semicolon, I don't see

31:42

anything because nothing is inside yet. But I could add something using insert into.

So let's try that. I'll say insert into the collections table.

And now I have to ask what columns inside of collections do I want to add data to?

32:00

Well, I probably want to add to the first the ID column, that primary key column. Then maybe I want to add a title to this row and also an accession number and also of course the date this piece was acquired.

So now I have the table

32:16

I'm inserting into along with the columns I'm adding values for. So for style sake, I'll hit enter here.

And now I could type a list of values to insert into this new row. I could say values and then inside parenthesis the values I

32:32

want to insert. Maybe the first primary key that I give to this item is going to be just one.

Start at one and add up as we add new items. For the title, I'll say let's call this one profusion of flowers.

This is the piece we recently acquired into our collection. The

32:49

accession number that we gave it was 56.257 257 and the date it was acquired was back in 1956 0412. Now I have all these values here.

I'll type semicolon hit enter and nothing seems to happen. But if I type, let's

33:05

say select star from collections semicolon, what do I see but this new row inside. Let's do it again to get a hang of this.

I want to now add farmers working at dawn to my collection. So I'll do the same thing.

I'll say let's insert into

33:23

the collections table and let's add values for the ID column, the uh title column, the accession number, and also the acquired column like this. Again, for style sake, I'll hit enter.

Now I give some list of values to add into

33:39

this new row. I'll say values here.

Then this list of values to add for each column. Well, the first column I have to give a value for is ID.

If you remember, our last ID was one. So, what should this ID be?

Maybe two. So, I'll type two

33:56

here as the next uh increment of my primary key. Then I'll give it a title.

And this title was farmers. Farmers working at dawn.

We gave it an accession number to keep track of it in our own internal museum records, our own archives here. So, I'll say 11.615

34:14

one 6.5 6.152 is the accession number for this particular item. Then I'll say we acquired it back in 1911 on 0803.

Now I'll hit a semicolon here. Hit enter.

And now I should be able to see

34:29

if I type select star from collections. Select star from collections semicolon.

I now have these two items inside of my collection. Now let's do one more here, but let's focus in particular on this primary key.

34:47

Like notice here how we've been actually inserting our very own primary key one and then two. But maybe that's not the best design.

Like if I let me try this again. I'll say insert into uh let's go for collections is our table name.

And

35:04

maybe I'll try again to add to the ID column, the title column, the accession number, and the acquired column here. What could go wrong, do you think, if I try to specify the primary

35:20

key myself? Let me ask this as an audience question here.

Feel free to raise your hand. What might go wrong if I try to make the primary key myself and insert a value myself?

Uh let's go to Sakiva.

35:37

>> Yeah. what we could do when we had to uh you know load a lot of values like maybe uh like big data or something.

Is there a way to add it through a CSV file or something rather than typing insert into tables?

35:52

>> A good question. I think you're on to something here which is we're inserting one row at a time which could get easily repetitive.

So let me hold that thought for just a minute here and focus on the primary keys before we see some more efficient ways to actually add data to our tables. So, I'm with you on this idea of maybe we don't want to duplicate

36:08

this primary key, right? Maybe we could do a little better than that.

So, I'll come back to my computer here. And if I specify the primary key, I might actually add a value that's already in there.

So, I could thankfully leave it up to SQLite to actually increment this

36:24

value for me. Let me try leaving off ID.

In this case, I'll omit the ID altogether, which seems at first like a bit of a bad thought. Like shouldn't every row have its own ID?

Well, let's just try and figure what happens. I'll hit enter here.

And now I'll say let me

36:40

give the values. Well, now the first column is the title column.

So I'll go ahead and say that this one will be called spring outing. We're going to add this one to our collection here.

The accession number in this case is 14.76. And we acquired this one all the way

36:55

back in 1914 0108 semicolon. So I'll hit enter here.

And notice how there's no ID that I have specified. But now if I hit enter, seems to work.

I'll type select star

37:10

from collections. And what do I see?

But the new primary key of three. So it seems like SQLite actually increments the primary key for me.

If I add some new row, it looks what is the current highest primary key and adds one to that

37:29

automatically for me. And I've gotten that by specifying in my schema this primary key constraint down on line six.

So pretty handy for me here. I see a few other questions too.

So let's take those before we move on as well.

37:46

Let's go to Andre. I just want to ask if I delete uh the let's say the third uh record and type again will it uh number it of the fourth number or or the third one?

>> Uh great question. We'll see this a little bit later on too.

But if I delete

38:03

some row, let's say I deleted the row with primary key of one. Well, what SQLite will do at least by default is actually take the highest value.

Let's say the highest value is still three in my database. It'll add one to that.

And when I insert that new row, it'll have

38:18

the ID of four in this case. Okay.

So let's explore a bit more some of these constraints on tables. Like here we're talking about the primary key constraint, but we also have as we saw before the notnull constraint and the unique constraint.

So let's try

38:34

inserting given those constraints here. I'll go back to my computer and again notice in my schema I specified that this title column should always be not null.

It should never have a null value inside of it. And similarly accession

38:50

number should also be not null. It should also be unique.

I should have no two rows that have the same accession number. And maybe kind of uh playfully subversively here, let's try to run against this constraint.

Let's actually

39:06

try to add the same accession number and see what happens. So I'll come back to my terminal.

Let me just reinsert let's say spring outing. Notice how if I select star from collections, it's already in here.

But I'll try to add it yet again with the same accession

39:22

number. So I'll say insert into collections.

And by now this is what becoming a little more familiar. I now want to say the row the columns I want to add data to.

So I'll say the title column, the uh accession number accession accession number column and

39:38

also the acquired date. I'll hit enter and now I'll say the values again.

Let's reinsert spring outing. I'll say spring outing as a title.

The accession number is 14.76 and the date it was acquired was again 191408

39:55

semicolon. Now if I hit enter, what do we see?

runtime error unique constraint failed. So it seems we ran against this constraint here that we specified on line four here.

Excession number should

40:11

always be unique. But by trying to add a new row that had that same accession number, we run into this runtime error and our operation was not completed.

If I say in this case select star from collections. Notice how I guarded myself

40:27

from adding spring outing more than once with the same accession number. So some usefulness here to these kinds of constraints.

Let's try violating not null too. I'll try adding a title that is actually null, non-existent.

Try to add a painting without a title itself. Let's

40:43

try this. I might try insert into the collections table and I'll add to the title columns, the accession number and also the acquired column.

Now I'll say the values here. But remember this null

40:59

value. Null meaning nothing.

This value doesn't exist. I could insert that into my table or at least try to.

I'll say null is a title. And similarly null is the accession number.

And let's say just playfully we got this painting uh back

41:14

in 1900 on January 10th like this. Okay.

Now semicolon. I'll hit enter.

And we see the same runtime error. Constraint failed.

In this case, the notnull constraint. So notice again how in my

41:30

schema I specified title should be not null. But here when I try to insert a null value, I run into that constraint and I can't insert that value.

If I try to select star from collections, I should see in this case that insert

41:47

didn't work. So these constraints are really guard rails against me, against other users to adding values that we really shouldn't add to our table.

Okay. So presumably here, you know, we're on to something by adding some

42:03

rows. But if the museum acquires more than one item, maybe 100 at a time, I don't want to be the programmer who's sitting there writing 100 insert into.

That's like not what I want to do. There's probably a better way to do this.

And let me show you one way to do this. One way is to instead of inserting

42:21

one row like this to instead insert multiple separated by commas. So here I might say this is my first row for each of these columns.

This is my second row again for each of these columns and so on and so forth. And it's worth noting

42:39

this is not just a matter of convenience. Like if I tried to insert a 100 rows, this is certainly convenient for me, but it's also most often in most cases going to be faster for me to insert 100 rows in one go than to insert

42:54

one row 100 times. So this is both a convenience thing for programmers and also an efficiency thing to actually optimize your database as well.

So let's try this syntax now to avoid me sitting there for hours and hours writing many insert into. I'll go back to my computer

43:12

and this time let's say we got two paintings at once. We got two.

So I'll try to add both of them. I'll say insert into the collections table.

And again, I want to keep adding to my title column, my accession number column, and even my

43:29

acquired column like this. Now, I want to add in some values.

So, I'll type values here. And as a matter of style, let me just say I'll make a new line.

I'll hit enter here. And now, I can type each of my new rows on their very own

43:45

line. So, again, I'll type all of the values that I want in my first row.

I might call this one imaginative landscape. We got this one back in.

Actually, we don't quite know when we got this one. If I type 56.496 is the exception number like here.

56.496.

44:04

The MFA actually doesn't know when they got this painting. So, if they don't know, let's just insert null.

This is intentionally left blank. Okay, this is my first row.

Now, I want to add a second one in one go. I'll follow this

44:19

up with a comma. And now I'll type out my next set of values.

This next one we required is called pianies and butterfly. Now I'll say the succession number is 0681899 and the date we got this one was back in

44:35

1906 on January 1st. Now I'll hit semicolon here.

And now if I hit enter, nothing happens. But if I type select star from collections semicolon, what do we see?

But now two new rows being

44:53

inserted. So a handy way to insert more than one value.

And also if you have a lot of values, a more efficient way as well. So let me pause here and ask what questions we have on insert into

45:08

whether adding one row or adding multiple. Let's go to teas.

Sir, imagine you are writing a code or inserting a row. So by mistake you have entered the uh wrong spelling of the title.

So how will we uh rename it?

45:23

>> Uh a great question too and often is the case with me. I make typos all the time.

I might add the uh artwork title and misspell it for instance. Well in that case I can't use insert into to correct it.

But I can use a new statement we'll see a little later called update. And so

45:41

with update can actually change the spellings of things and we'll see that a little later today. But great question to peek ahead as well.

Okay, so let's keep going here. Let's think about how we can keep adding values to our data set.

And so far we've

45:57

seen insert into with one row and with multiple. But one more way to keep in mind is you might already have your data perhaps in some other format.

And one common format looks a bit like this on

46:13

our screen over here. This file is called a CSV for commaepparated values.

Now why commaepparated values? Well, let's just look at it here.

We see some presumably column or column names

46:28

like ID, title, accession number and acquired. But what separates these column names?

Well, looks like commas. ID, title, title, comma, accession number, and so on.

Every row is still its own row. That makes sense here.

But

46:45

now those row values are also separated by commas. Notice here the first value before the first comma corresponds to the first column.

This id is one. Similarly the next commaepparated value

47:00

profusion of flowers belongs to that next column here title as well and so on and so forth. You could if you wanted to kind of draw a snaky line to see how these columns correspond down our file here.

Now you might often have data

47:16

already in this format and it's actually pretty convenient to try to import this data into SQLite into your very own table so you can use SQL on it. So let's try doing that here.

Taking a CSV like this and adding it to our database. I'll

47:32

come back to my computer and at this point I want to start over. Let's say I didn't use insert at all.

I actually got a CSV from the MFA of all the items in our collection. So I'll typequit to leave this database.

And now I'll type

47:48

rm MFA. DB to remove this file al together.

Now let me show you mfa.csv. This CSV file I have that looks exactly like what we just saw.

I'll type code MFA. CSV.

And now I can indeed see I

48:05

have an ID column, a title column, an accession number, and the date these pieces were acquired, all separated by commas. So let's say I want to quickly import this data into its very own database to run SQL queries on it.

As it

48:22

stands, I can't use SQL queries on this table because it's not inside of a SQLite database. But let me add one.

I'll type SQLite 3 MFA. DB to remake MFA down below.

Let me now introduce this new command that is not a SQL keyword

48:40

but is actually a SQL like command. And this command is called import.imp import lets you take a file like a CSV and automatically insert it row by row into a table of your own making or one that you can let SQLite

48:57

create for you. Let's actually create the table ourselves and then insert this CSV using import.

So I'll go back to SQLite. Let me recreate this schema.

Right now there's no tables inside this database. But let me create one.

I'll

49:12

instead type read schema.sql to read in that old schema. SQL file.

So I can have my own table here. I'll type that schema again.

And now I see I have that table back. But if I type select star from collections

49:29

semicolon, nothing is inside. Well, I could fix this.

I could try to import this CSV into collections to insert all at once from this own from the CSV file we have right here. So to do this, let me type.imp import.

Now before I finish

49:46

this off, there are a few arguments or options I can give to.imp import to make sure it works properly. The ones for this look as follows.

I should type.imp import and then dash d-csv

50:02

to say I'm importing a CSV. If I don't type dash d- CSV, SQLite might assume something about this file that just isn't true.

It should know that file that numbers are here separated by commas. Numbers or values, whatever's inside my table are separated by commas.

50:19

And then d- skip one. Well, let's take a look.

If I go back to this file here, let's see. I have the first row, the second row, the third row, the fourth row.

Are there any rows I probably shouldn't insert into my table here? Let

50:36

me ask the audience. You're free to raise your hand.

Are there any rows I shouldn't insert into this table? Let's go to Sukana.

>> Yes, I think we shouldn't include the first row because it doesn't give us any value that we need.

50:52

>> Yeah, you're right. Right.

So if I look at this header row as we call it, I see ID, title, accession number, and acquired the names for my columns. But notice in my schema, I already have those column names existing.

So I shouldn't insert the value ID into my ID

51:09

column. I want to just skip that row and only do the next one.

So here by typing d- skip one, I'll skip that header row. So let's try this now.

I'll say.imp import- CSV. I'm going to import a CSV now.

D-s skip. How many rows should I

51:26

skip? Well, just one, the header row there.

Now, I say the file to import. So, I'll type mfa.csv, the CSV file to import into my database.

Now, I type the name of the table I'm importing into, collections in this

51:42

case. And notice how I no longer need to quote collections or quote MFA.CSV CSV because this is not a SQL statement.

This is a SQL light statement. So I can get away with not quoting anything here.

Now I'll hit enter. And again, nothing

51:58

seems to happen, but it's probably a good sign. Let me try selecting star from collections.

Semicolon enter. And now all my data is just magically in there.

It's went from my CSV into my very own table.

52:16

Okay, so that's a pretty nice step forward. Like no longer do we have to use just single line inserts or even bulk inserts, those multi-line inserts.

We can now just import an entire table from a CSV. But I think I've been

52:31

showing you a bit of a inaccuracy here, at least something that's not often going to happen to you. Like if I go back to this uh file here, the CSV, what you might notice is that I specified the primary key.

I said ID of one, ID of

52:47

two, ID of three. But if we heard before, this might not be the best design because what if I import this CSV and there's already an item that has the ID of one or the ID of two.

Ideally, I could let SQLite create its very own

53:03

primary keys for each of these rows. And more often than not, you'll likely have a database or a CSV that looks a bit more like this without the primary key, but you'll still want to have a primary key when you import into your database.

So, let's try working with this kind of

53:20

CSV now and having SQLite generate some of those primary keys for us. Come back to my computer and let's update our MFA.csv just to remove those primary key column here.

So I will open up again MFA.CSV

53:36

and let me try to just delete this column al together. I'll remove ID, remove one and two and three and four and five.

Let me save it. And now that ID column is gone but now there's a

53:52

problem. Like if I go to my schema and I type dot schema, notice how there are four columns in this table.

ID, title, accession number, and acquired. Well, now my CSV only has three columns.

So,

54:09

if I try to import this CSV into this table, I'm going to run into trouble because I have different numbers of columns between my CSV and my table. What could I do instead?

It turns out that I could actually import this data

54:25

into a temporary table and then take that data and insert that data in that temporary table into my real one called collections. Here I can use both.imp import and insert into to accomplish that task for me.

So let's try this.

54:40

I'll say import I want to import a CSV now. D-csv.

Which one? Well, MFA.csv in this case.

Now though, I want to create a brand new table that doesn't yet exist. So I'll type the name of the table.

I might just call it temp for now

54:57

to temporarily import this data into a brand new table. And notice how here I'm not using dash skip one because now I want to take advantage of those header rows.

SQLite if I import into a new table. Well, notice I have a header row

55:14

and make my columns the very same names that are in my header row. title, accession number, and acquired.

I don't want to skip them. I want SQLite to see them and create this table using those header rows.

So now I'll hit enter.

55:30

Nothing seems to happen. But but if I type schema, what do I see?

But a brand new table called temp that SQLite has just made automatically. And notice how it used that header row.

I have title, accession number, and acquired as my column names.

55:46

Okay, let's now look inside the temp table. I'll say select star from temp semicolon.

Oh, and now I see all the data in there, but I don't have primary keys yet. So, my goal is really to take all this data and insert it into my

56:03

collections table to give it that primary key I've been wanting. Here, again, if I type select star from collections, uh, well, nothing.

Oh, something is still there. Let me actually just delete this for demonstration.

I'll say delete from collections to remove it all

56:18

together. We'll get that done in just a minute.

And now let me try select star from collections. And I should see nothing inside of collections.

So what could I do? I could insert into collections using the data from my temp

56:34

table. So I'll try that.

I'll say insert into collections and choose those columns yet again. I'll say the title and the accession number.

And now let's go for the acquired column

56:49

as well. I'll hit enter.

But instead of typing many new lines of values, I actually have a way to dynamically select all the values I want to insert. And it looks a bit like this.

I could

57:04

say insert into some table and some columns of that table. But I want to insert the results of this select down below.

So I'll select some columns from some separate table. And so long as

57:19

these columns align, I'll be able to actually take the results of that select and insert all of them into this new table using insert into. So let me finish my statement here and we can see the results.

Come back to my computer and here I'll type a select to get back

57:36

all the items from uh my temp table. I'll say select in this case select um let's go for the title column the accession number column and the acquired column from my temp table.

Now if I hit

57:54

semicolon I should see well nothing at first but if I type select star from collections I see all my data now in there selected from my temporary table and now if I

58:09

type dots schema I still see temp but what can I do now I could just delete that table altogether I could say drop table temp as we saw last week semicolon hit enter dots schema again and Now we're back where we want to be with a single table. And now we've import our

58:27

data from our CSV with primary keys. If I type collections here, we see it all in this table.

Okay. So we've seen several versions of import.

One to import into an existing table and one to import into a brand new

58:43

table. Let me ask then what questions we have on how to use import or how to insert more data into our database.

Let's go. Luis, >> can we ask the insert into command to uh

59:00

place a column in a specific position? >> Ah, good question.

Can we ask insert into to place a column in a certain position? Um, there's a few ways of getting at what I think you're asking here.

So, let me show you a few of those here. I'll come back to my computer and

59:15

let me pull up the syntax for insert into again. I will choose this and I'll show it on the screen over here.

So I'll say insert into some table given some columns. And notice how down below I actually have values to insert into

59:32

those columns. I could to your question rearrange these values.

So I might have um this first value here is some value that goes into this column. This second column here is a value into that column and that would rearrange the values I insert into those columns.

If you're

59:48

asking though if I can reorder the columns that is up to create table and create table only. And in general I encourage you think not so much about an ordering of your columns because it could be in any order whatsoever.

But you could just rearrange your selection of columns here to insert the data you

00:04

want to insert. Okay, let's take one more question here.

What happens if one of the rows you're trying to insert violates a constraint on the table? >> Yeah, so here we're inserting multiple rows and if one actually violates some

00:19

constraint, then we won't actually insert any of those rows. This is because this insert is bundled inside of a transaction which we'll learn more about later on in the course.

Let's take another one. >> I noticed that when you did select star from collections that one of the

00:36

acquired dates was just blank. It didn't say no.

Is that because the CSV itself just had an empty value next to the comma? >> Yeah, great observation.

So, let me try this again so we can see the results of this select. I'll come back to my computer here and let me show you again.

00:52

If I say select star from collections semicolon, notice how this acquired um col this acquired cell here, if I go back to my uh screen is just blank. But we saw before that if I selected star, I would have seen null there if this value

01:09

was truly null. Well, it turns out one downside of importing from a CSV is that all of your data is imported initially as text.

And if I have just a blank cell in my CSV, it won't be converted to null

01:25

automatically. I need to do that myself, perhaps using an update statement that we'll see in just a bit.

So be wary of this. If you do want to keep track of null values and so on, if you don't actually manually make this value a null, it'll appear as just a blank value, not a null, which is different in

01:42

this respect. Okay, so here we've seen how to insert not just one row, but multiple and also how to import data from a CSV.

When we come back, we'll see how to actually update our data altogether and even

01:58

delete it too. So, we'll come back in just a few and talk about how to delete data from our tables.

And we're back. So, we just saw how to insert some rows into our database and also to import some data.

But presumably, we also want to be able to delete some data from our

02:14

table as well. You can imagine the MFA, the Museum of Fine Arts, maybe they're selling a piece of artwork or maybe they've lost one or maybe it was stolen.

But either way, they want to remove the row from their table. Well, let's see here.

We could go back to our schema

02:30

with a table of artifacts and artwork that is inside of the MFA. Now, if I want to delete a particular piece, you could visually think of it a bit like this.

I could first identify that row I want to delete. Let's say it's spring outing here.

We've sold this piece.

02:47

Well, I could visually just remove this row so it's no longer there and shift the remaining ones up metaphorically. And it turns out that to do this in SQL, we have our very own statement we can use.

This one reads delete from some

03:03

table where a condition is true. So we see our old friend where back again and where is vitally important to this delete from.

If I say delete from table with no where, what do you think is going to happen? We might drop

03:20

everything from our table, right? But if I instead say where some condition is true, I can select the rows I want to delete and only delete those rows.

So let's try this here. I'll try to delete some artwork from our collections table.

03:35

For example, maybe we've sold it and we want to get rid of it. I'll come back to my computer over here and I will open up our SQLite database.

I'll say SQLite 3 mfa.db. And now I can type select star from collections.

And I see a few more items

03:52

than last time now all inside of our table. But per the visual, I want to delete spring outing to remove it from this table.

So what could I do? I could try delete from and then the table I want to delete from.

In this case, I'll

04:08

use the let's say collections name delete from collections. But I don't want to do this.

I don't want to say delete from collections semicolon end of statement because then all delete every row. What I should instead do is this

04:25

delete from collections where some condition is true. Maybe the title in this case is a title I'm trying to remove.

So I'll say title equals spring outing just like this. Semicolon enter.

04:40

Now nothing seems to happen. But if I do select star from collections semicolon, we no longer see spring outing.

And notice here how the ID column, the ID of three is now gone. If I were to insert

04:55

some new row here, I would start over with the highest number, which is seven in this case, going from seven to eight. But three is no longer part of our database.

Let me try this again. Notice how this one called imaginative landscape, the acquired date is null.

We

05:12

don't know when we got it. Well, let's say that we eventually sell it.

It's no longer part of our database. We could use a condition based on null to remove this particular artwork.

So, I'll say this. Let me try to delete from collections where title equals not spring outing,

05:30

but uh where the acquired date, sorry, the acquired date is null semicolon. I could also use is not null where acquired is not null, but that would delete all the pieces of artwork that actually have an acquired date.

Here I

05:46

want to delete only those that do not have an acquired date. So I'll say where acquired is null.

Now I'll hit enter. Nothing seems to happen, but I'll say select star from collections semicolon.

And I'll see that that piece

06:01

of artwork is now gone. I only have those that have an acquired date.

Okay. So, similar to insert, we've been able to delete one row at a time, but it would probably be helpful for us to

06:18

delete multiple rows at a time as well. Let's look again at our table.

Here we have our artwork as it currently stands. And maybe let's say we want to delete those pieces that we acquired before 1909.

Uh here in Boston, the MFA

06:35

actually moved locations in 1909 to a new place still in Boston, but a brand new building altogether. So let's say that perhaps they left some items at their old location.

They're no longer part of their collections. via what condition do you think could I select

06:52

these three rows for my delete from? What could I put in my condition to delete these three rows?

Let me ask the audience here. What might you propose for delete from here?

Delete those three

07:07

particular rows. Let's go to shiv >> greater than five.

ID equals than five. >> That's a good observations.

We could look at our table here. We see the ID

07:22

could be greater than or equal to five. That would remove these three rows.

Certainly, there's probably another way to do this, too. Any other ideas besides the ID?

What else could we use?

07:41

Let's go to Yoshvi. Uh >> so we could compare the dates and use greater than or less than sign with the acquired dates.

>> Ah so you're proposing to use the date column too. That makes sense to me as well.

We could see acquired here. Perhaps we could see is the acquired

07:58

value less than 1909 that year that we move locations. We could also probably use the title, the accession number, and so on.

And each has their own tradeoffs. Remember though, for this particular query, which is deleting those items

08:14

that were part of the museum, acquired before 1909, probably best to use the acquired date to actually delete those rows. What if, for instance, our IDs are actually not like this, but they're kind of interspersed around.

I couldn't

08:30

really do a query like if the ID is greater than equal to five because I could also include those that were maybe acquired 1911 1956 and so on. Probably ideal for me to use this date if it's the date that I ultimately care about.

So let's try this. I'll go back to my

08:46

environment and I'll try to delete these three particular rows. I'll try delete from collections as we saw before.

But now here comes my condition. I'll say where the acquired column is less than

09:02

some date that I'll give. And here I'll give 1909 January 1st.

It turns out that SQLite has a few ways to represent dates. One of which is this format that follows the YY or the year four digits

09:19

and then dash and then MM or the month with two digits and then dash again dd in this case the day in two digits. So 1909-01-01 means January 1st, 1909.

09:34

And I can use these same operators with dates. I could say greater than or less than or equal to this particular date.

And SQLite will be able to parse this for me to understand what I mean, which is earlier than 1909.

09:50

So let's try this. Then I'll hit enter and nothing seems to happen.

But if I do select star from collections, I now see I'm down to two paintings, two pieces of artwork that were required only after

10:05

1909. Okay.

So given this uh part on delete, we're able to delete one row or even multiple rows at a time, but there are still instances where we might want to think about should we delete something

10:21

or can we delete something. We'll talk about those in just a minute in terms of constraints.

Be back in a few. And we're back.

So, we just saw how to delete one row, even multiple rows. We haven't yet talked

10:36

about whether we should delete some data or whether we should delete some data. And particularly in the context of these constraints where you might have maybe a piece of data you actually shouldn't delete to maintain the integrity of your table.

Now, one example of this is a

10:51

foreign key constraint, which means that you have some table with a primary key that is referenced by some other table. And we'll get concrete about this just a minute, but it might mean that if you were to delete that primary key, that other table would have nothing to

11:07

reference. So, let's update our schema here for the MFA, the Museum of Fine Arts, and let's say that they have not just a collection now, but also artists involved.

And there exists a many to many relationship among artists and items in the collection. We could say

11:25

that an artist might make more than one piece of art in the collection. And a piece of art might be created by more than one artist.

Now, concretely in our database, this might look as follows. I could have a table for artists and a

11:42

table for collections. And notice how each has their own primary key.

Artist has an ID column called our primary key column. Collections too has an ID called our primary key column.

Here now in the middle is this table created that

11:57

symbolizes that represents the relationship among artists and items in the collection. Here on the first row we see the artist ID of one created the piece of art with the collection ID of two.

So who did what here? Well, we can

12:13

see that the artist with the ID of one is Lee Yin. Lee Yin created this piece of art with the ID of two, which is imaginative landscape.

So, we can kind of relate in this case artists with collections as we saw just a few weeks

12:29

ago. But let's say we decide to delete a particular artist.

Maybe we delete unidentified artists down here. Well, we could just delete from the artist table.

Maybe we find a condition to select this row and we delete from artists. We'll do

12:46

that here. Remove that row.

But what have we done wrong? If we look at our created table in particular, what have we now done wrong?

What kind of problem might we run into?

13:04

Let's go to Kareem. The third row with ID three was only deleted from the artist table without the other table.

>> Yeah, good point. We only deleted the uh artist with the ID of three from the artist's table.

Now, if I look at the

13:21

created table and I look for the artist with ID of three, well, do you see them? I don't exist anymore.

So, I can't understand this relationship. I don't know the artist with the ID of three.

So let's try this now with a new kind of

13:38

schema now in our database. I'll go back to my computer and let me open up our new version of our database.

I'll type SQLite 3 in this case uh MFA. DB same name but now different schema.

If I type

13:54

dot schema, notice a few things here. I have those same tables we saw visually.

I have collections, artists and a created table. Let me focus in particular on this created table.

If I say do schema in this case created

14:12

enter, I'll see I have a foreign key constraint on my artist ID column and my collection ID column. So if I tried to delete an artist that is referenced in my created table, I would probably raise

14:30

a constraint error, a foreign key constraint error. So let's just try this and see what happens.

Let me try delete from the artist table. Delete from artists where let's say the name of that artist

14:48

is literally unidentified artist. This is the name they have in the Museum of Fine Arts collections.

Unidentified artist. So now I want to delete them from that table.

Well, if I hit enter, I do get that foreign key

15:04

constraint error. So, does this mean I just can't delete this artist?

Or is there a workaround? Let's look at it visually again.

I'll go back to what we had before as our table. And let me ask the audience here, what's the solution?

15:21

Like, if I can't delete unidentified artist because they have an ID referenced by this table. What should I maybe do first?

What should I do instead? Yeah, I want to say that first we can delete the ID that is being

15:36

referenced by this ID that we want to delete. Then we can delete it.

In fact, if we want to delete from the artist, first we have to delete from the created table or to other table which it is referencing, then we can delete from the artist table. >> Yeah, I like your I like your thinking

15:51

here. So if we look at the created table, you notice that we can't delete unidentified artist because it's referenced in the created table.

What we should maybe first do is delete this row, delete their affiliation with their work and then delete the artist so we

16:08

don't run into this foreign key constraint. So let's try this in our SQLite environment.

Come back to that computer and again our plan was to first delete the artist's affiliation with their work from the created table. So

16:24

let me show you first the created table. I'll say select select star from created and see how it's exactly like the table we just visualized.

Now let me try to delete this artist affiliation with their work. I'm not deleting the artist.

16:40

I'm deleting their affiliation with their authorship, their artistship, whatever you want to call it for their work. Now I'll say delete delete from let's say delete from artists where the

16:56

artist ID is equal to or equals three. Well I could do this but there's probably a better way.

Like I know the ID and that's fine but we could also use what we saw a little bit ago which were

17:12

sub queries. a way to write a query that returns me the result and that result gets included in another query altogether.

So let's try that instead. Let's say I don't know the artist ID.

What I could do is make a sub query. I could say inside of this let me select

17:28

the ID from oh the artist's table where in this case the name equals unidentified artist. And then let me close out this query.

hit a semicolon. And I already

17:44

see a typo in this, but I'm just going to try it anyway. I will go ahead and hit enter.

And let's see if I type select star from artists. Enter.

I still see unidentified artists. And I think my

17:59

typo here was as follows. If I look up my query using the up arrow on my keyboard, I see delete from artists where the artist ID equals some value here.

But do I want delete from artists? I don't I actually can't delete from artists.

What I should do instead is

18:15

delete from the created table. So, let's try this.

I'll say delete from created making sure I only have double quotes around created. Then I'll say where the artist ID equals let's say either three,

18:30

but it could also be the result of this subquery. I'll say 1 2 3 4 and then select the ID from the artists from the artist table where the name equals unidentified

18:46

artist then I'll close this sub query hitting semicolon hit enter and now if I say select star from created semicolon I should see that this artist's affiliation with their work no longer

19:02

exists. artists.

And because it no longer exists, I can now delete them from the artist table. I don't have this foreign key, artist ID, referencing the primary key of this artist.

So, let's now try that. I could say select star from artists.

I see that unidentified

19:21

artist is still in here, but let's delete them now. I'll say delete from let's say artists where the name equals unidentified artist.

Closing my quotes, semicolon, hit enter. And now if I select star from

19:37

artists, semicolon, I should see they're no longer in this table to our earlier example. This becomes a two-step process.

First, delete their affiliation, then delete their name. Okay, so let's try this yet again, but

19:56

now using some additional uh tools that we have at our disposal. So let's look back at this foreign key constraint that existed in this table.

If you remember, it looked a bit like this. In my created

20:12

table, I had this line foreign key. Artist ID references the ID column in artists.

Well, if that is the case, I can't delete the artist with the ID referenced by this foreign key. But it

20:29

turns out that I could specify some alternative action that happens when I try to delete the ID that is referenced by this foreign key. I can specify that using a keyword called ondee.

So after foreign key reference artist

20:46

ID, I could say afterwards ondee and then specify some action I want to actually happen when I try to delete the primary key referenced by this foreign key. One thing I could use would be restrict, which is kind of like what we saw before.

If I try to delete a primary

21:04

key that is referenced by this foreign key, I will not be allowed to do it. That action is restricted.

I could also decide to take no action. In this case, I could delete the primary key referenced by this foreign key and

21:21

nothing would happen. I would actually be allowed to do that, which may be unwise in some cases, but I could give myself that power.

I could also decide to set null. That is, if I delete the primary key that is referenced by this

21:38

foreign key, what I'll do is set the foreign key to be null, meaning that value no longer exists. I could alternatively set it to a default value for that column.

Or perhaps most compellingly, I could try to cascade the

21:54

deletion. Where cascade means if I delete the artist, for instance, let's also delete their affiliation with their artwork all in one go.

This converts our two-step process into a onestep process. So let's visualize this.

Let's say we

22:11

have now applied this constraint ondee cascade. So if I delete the artist, I'll also delete their affiliation with their work.

We'll have the same two tables, artists, collections, and created. Now again, artist ID references this primary

22:29

key in artists. Now I'm going to try I want to delete unidentified artist here.

Well, I could do that. I could just delete their row.

And now instead of a foreign key constrainted error, what I get is the following. I see and created

22:45

that this row is also gone. The row that had the artist ID of three gets removed.

We've cascaded the deletion onward to the created table. So let's try this now with a new database schema.

I'll go back to my

23:01

computer here. So I'm back at in my terminal here and I can type SQLite 3 MFA.

DB to reopen this database. And notice how if I type dots schema, I've updated this schema to now

23:17

have ondelete cascade. Let me show you over here on the screen here on the created table, I now have the very same kind of table schema, but now my only difference is I've applied this ondee action to my foreign key constraints.

In

23:32

particular, I'm going to cascade the delete from the artist's table to the created table. So let's try that out in SQLite.

I'll come back over here and I will now try just delete from the artists table where the name equals

23:50

unidentified artist. Unidentified artist semicolon.

Now I'll hit enter and I don't get a foreign key constraint anymore. But if I say select star from created semicolon, notice how I've also deleted the

24:07

artist's affiliation with their work. So that is wherever in the artist ID column I saw the ID for the artist I deleted, I would too delete that row.

So I have no references to that primary key which is

24:23

now gone from my table. So let me ask here we've seen how to delete single rows, how to delete multiple rows and now how to delete data among some constraints like our foreign key constraints.

What questions do we have

24:40

on those techniques? Let's go to Han.

>> I have a question regarding deleting. Um since the uh the ID numbers have been um removed while we removing the record I was wondering if do we have to clean it

24:55

up somehow later or will they be populated with the new data as it comes along? >> Yeah great question.

So the question is what happens to our primary keys when we delete our data. So for this one let me show you the visual again that we had before of our tables nicely printed on

25:11

the on the slide. So I'll come back here and I will go back to our idea of these um joint tables where we had an artists table, a collections table and a created table.

And we saw before that we're going to delete this artist called

25:26

unidentified artist. So I'll delete them.

And to your point, well the ID of three no longer exists in this case. Now by default at least in SQL light if I insert a new row what I'll do is take

25:42

the highest ID value and I will then make that the new primary key for the new row that I insert. That is the default situation.

I could if I wanted to get more specific and if I had done

25:57

in my um ID integer col my ID integer column here in my create table if I had also said this keyword called auto increment all one word what would happen instead is I would actually reinsert an

26:14

ID that is not used so in this case three is not used I could insert that one here so up to you what you want to do in general General SQLite by default will take the highest ID, add one from there. If you specify this constraint

26:29

called auto increment, you will instead take whatever ID is not being used and use that in your insert afterwards. Okay, so now we've seen how to insert and how to delete data.

But of course,

26:44

we make mistakes when we add data or even when we delete data. So, we'll see in just a minute how to update our values as well to correct typos and even to update associations between artists and artwork.

And we're back. So, we've so far seen

26:59

how to insert some data into our tables and how to delete it. But sometimes we don't want to fully delete something.

We just want to change its value to correct some typo or correct some association. So let's think to our MFA example where

27:15

the Museum of Fine Arts has some tables that look like this. They have artists in their collection and they also have artwork in their collection.

They also have a created table to associate artists with their artwork. Now in this

27:30

case we know that I have this unidentified artist and we can see that they authored this item in the collections farmers working at Dawn. But let's say that later on we find out it wasn't an unidentified artist.

It was

27:48

instead Lee Yin who created Farmers Working at Dawn. How could we update our tables to make sure it's Lee Yin who we have creating Farmers Working at Dawn?

So uh what we could do is like uh we

28:07

know that in created we have our artist ID and the collection ID. So we could update it there but somehow we need to cascade it over to the artists table because now this unidentified artist row

28:23

I think we probably don't need it. >> Yeah.

Yeah. So, a good point here where we could actually probably change the created table to reassociate an artist with some new artwork here.

So, let's visualize this. I go to my created table and here the artist ID is currently

28:39

three, but I want it really to be one. I want Lee Yin to be associated with this piece called Farmers Working at Dawn.

So, I could update the created table to instead of having three here, have one. Now we see Lee Yin created Farmers

28:56

Working at Dawn. And to your concern about this unidentified artist here, I think it's okay to have an artist in our table who may or may not have an item in collections.

We'll say that's okay, at least for now. So if we can update our

29:11

associations between artists and collections like this, let's actually try to do that in our very own database here. I'll go back to my SQLite environment.

And now let me try to uh open it. First I'll do SQLite 3 MFA.

DB.

29:27

And now let me type dots schema to show you I have the very same schema from before. So I want to update the artist association between Lee Yin and farmers working at dawn.

So let's say I'll select star from created like this. Semicolon.

Here I have my artist ids and

29:46

my collection IDs. I see that we have the unidentified artist ID of three creating uh farmers working at dawn with the ID of one our collection here.

So now let's try to update the artist who created this particular painting here. I

30:03

have three associated with one but I want one associated with one where one is the ID for Lee Yin and one my collection ID is the ID for this artwork here. So let me try this.

I'll say update created and set let's say artist

30:20

ID equal to some particular value. Well, what value should I set it to?

I could try to set it to Lein's ID, which we know is just one. But let me try instead to use a sub query here.

Let me try to

30:36

say parenthesis and then write some query to update this value. I'll say select ID from artists like this.

Enter again where the name equals Lee Yin. And

30:52

then let me close this sub query. Well, if I try to run this query, what might happen?

I'm updating the created table. I'm setting the artist ID equal to the ID for Lee Yin.

But what I've forgotten

31:08

is this where to only choose some rows to update. So let me not close it yet.

I'll instead say where in this case the collection ID the piece of artwork in our collection is equal to well the ID

31:26

for this painting. I'll say select ID from collections and then I'll say where the title equals farmers working at dawn.

Now I'll close this subquery and hit semicolon. And here we've seen our

31:43

first example of an update query. I'm trying to update the artist ID column in created to be the ID for Lee Yin.

I only want to do that though on the row where collection ID is equal to the ID for

31:58

this particular painting we want to change the attribution for. So now I'll hit enter and if I select star from created I should hopefully see in this case that the artist ID associated with this painting is two and also down below

32:16

here is one as well. So I have Lein associated with now two paintings overall.

So let's get a grasp on what this update uh syntax really looks like in general. And for that let's show this slide here.

We have this update keyword update

32:33

statement in SQL to take uh a table name and update the columns inside of it. I say update then the name of the table I want to update.

Then I say set some column equal to some value. I could if I

32:49

wanted to have more than one column here. I could say maybe title and even um maybe if we're talking about authors, authors over here or even acquired date.

I could update more than one column in my set portion here. Then comes this

33:05

where portion where some condition is true. I want to make sure I don't update all of my rows.

I only update those where some condition is actually true. So this is your syntax for updating some columns.

Let's say if you want to change

33:21

an artist attribution or if you want to change a typo you've made. So let's see this now not just in terms of changing artists and their attributions.

Let's see a use case for update. we've made some mistakes in our data and let's say

33:38

the museum decides to host some kind of um event where people vote on their favorite piece of artwork. They kind of handwrite or type it into some online form.

Well, we might get back a CSV of those responses, some commaepparated values, one line for each vote from our

33:54

people who've attended this convention. Let's go back over here and I'll show you that CSV.

Let me go to my environment and I'll type code MFA.csv to open this CSV that I already have. And here actually it's not called

34:10

MFA.csv, it's called code votes.csv. And now here we can see I have a table of one column that has several votes inside of it.

Let's see we have maybe 20 votes to be exact. So the first row is

34:26

the header row. I have in this CSV one column called title and each line here is one vote for a museum goer's favorite piece of artwork.

We see farmers working at dawn. We see imaginative landscape profusion of flowers and our goal is to

34:43

count up these votes to see which is the most popular. Well, let's try using import again to actually turn this CSV into our very own SQLite database.

Let me go back to my terminal and I'll type

34:58

SQLite 3 votes.db to create a votes database. I'll hit enter.

And now I can use import. I could say import a CSV called votes.csv into a table also called votes.

I'll hit

35:14

enter now. And now if I select star from votes semicolon, I'll see all of my votes now in a single table.

So, I want to tally these votes. We could do that using a technique we learned back in the week on querying and relating.

What if I

35:32

wanted to group by my titles and count up those groups? I could do that.

I could say select, let's say, title and also the count of title. Let me count up the votes for each title from, in this case, my votes table like this.

35:50

Now I want to count not just each vote individually but I want to group them. I want to count group votes by group where that group is in this case the title itself.

So let me try running this query and hitting enter. Well I'll see I've started to count some

36:09

votes like I have imaginative landscape here. Spring outing farmers working at dawn.

Spring outing down below here too. But I seem to be missing something.

Like I shouldn't have this many groups. I should only have four groups.

Why do I have so many? Well, it seems like

36:25

there's some typos here. What typos do you see?

Feel free to raise your hand. >> So, uh I think that it is title is uh when you count it is uh case sensitive.

So maybe it is counting one by one.

36:41

>> Yeah, a good idea. So maybe it's case sensitive our group by is.

And you'd be correct if you said that group by is in this case sensitive. So if we see here we see farmers working at dawn all capitalized at least the farmers working and dawn capitalized.

Well that won't be

36:57

part of the same group as uh let's say farmers working at dawn all cap properly capitalized in this case with lowercase working and lowercase dawn as it is in the MFA collections. So we could probably fix some capitalization here.

37:13

What other things could we fix? Let's hear from one more person.

Let's go to Laurens. >> The uh the count on each typo.

>> There are some typos in here. Absolutely.

So, if I look at some of these, I see uh not farmers but famers working at dawn. Um I might see instead

37:31

of imaginative landscape, I just see imaginative landscape. So, definitely some typos in here to fix as well.

So, let's start working on using our update tool, our update keyword to fix some of this data, to clean it up, so to speak, so we can count up our votes for these

37:47

artworks. Come back to my terminal.

And now, let's try updating to at least remove some of this white space here. Like, you might notice that imaginative landscape has a space before it.

And so does spring outing here. That space

38:02

counts as part of the title and I want to remove it. So, what could I do?

I could try using a function in SQLite, one called trim. Trim takes a string and just removes leading and trailing whites space, giving back only the characters

38:19

in the middle. So, let me try that.

I'll say update votes my table here and set the title column equal to what? Well, the trimmed version of title.

Trim looks like this. trim and then some

38:36

parenthesis to say the input to this function. I'll give it the title column.

Now I'll hit semicolon and this query will run as follows. It will update the votes table and inside the votes table set the title column equal to the

38:53

trimmed version of whatever values are inside that column. Trimming here means remove trailing and leading whites space.

I don't need to apply any condition. I don't need to say say where dot dot dot because I want to apply to all rows in my data set.

Now I'll hit

39:11

enter and let me try running this again. I'll say up arrow to get back my old query.

Now I'll try counting again and I think we're making some progress. Like I can see that I've removed removed quote unquote those um titles that had leading

39:26

or trailing whites space. They're now part of a group because their titles now match those groups.

Let's keep going. We noticed before we had some capitalization issues.

So, let's fix those as well. I can go back to my terminal and we could introduce this

39:42

other function, one called upper. Upper converts a string like farmers working at dawn to be all in uppercase kind of shouted out.

And that's useful for us here because we could kind of force everything to uppercase thereby removing any inconsistencies in capitalization.

39:59

So, let me try that. I'll say update again the votes table and this time I'll set title equal to the uppercase version of title.

So upper here is a function in SQLite that takes some values associated

40:15

with a column and uppercases the entire thing to remove any consistencies and capitalization. Again no condition here because I want to apply this to all rows.

I'll hit enter. And now if I select select star from collections

40:32

semicolon oops select star from votes in this case no longer a collections table. I'll say select star from votes semicolon.

I should see all of my titles capitalized. But now I want to group by.

So let's try this. I'll go up arrow to

40:47

get back to my old query that grouped by title. Hit enter.

And now we're still making some more progress. I'm reducing my number of groups until I get to four.

But there's still some typos like we see famers working at dawn or farmers

41:03

working or farmser working at dawn. We want these to be included as part of this group.

Farmers working at dawn. So we should fix some of these typos here.

Let me go back to my terminal and let's try this out. I could in this case just

41:19

manually correct these. Like you could imagine me doing something like this.

update votes and set the title equal to all caps farmers working at dawn. Now including a condition where the title

41:35

presently is is equal to far famers working like this semicolon or not famous work uh I think it's farmers working just all in two words that was one title we saw I could manually update it a bit like this

41:51

let me hit enter and let me do the same query by hitting the up arrow on my keyboard me find this one hit enter and now we can see that that title Farmers working is now just part of the main group farmers working at dawn. I could keep

42:08

going here. I could try to convert famers working at dawn to farmers working at dawn.

Let me try it. I could say select let's say um not select let me update let me update in this case votes and set title equal to farmers

42:24

working at dawn where the title equals in this case famers working at dawn semicolon enter let me hit up arrow to go back to my old query to group by and

42:39

now we're getting somewhere I'm seeing now only two groups for farmers working at dawn on but one more to fix. Now, we'd be here for a while if I spend all this time like one by one fixing these typos.

So, there's probably a better

42:55

way. There is.

This one's going to be using the like keyword. We don't have to say equal so much.

We can just say like now I could try to match any title that looks like Farmers Working at Dawn and update it to be the correct title. So,

43:12

let's try that here. I could now try update votes and set title equal to farmers working at dawn.

Farmers working at dawn where the title

43:27

is like where the title is like some pattern I could give. Now for this data set it turns out that the only artwork that begins with FA is farmers working at dawn.

So to match any title with FA and

43:45

then anything afterwards, I could use FA percent and then close out that pattern. Notice how I don't have to have consistent capitalization like is case insensitive.

And notice how too the percent matches anything that comes

44:02

after FA. Now in this data set, this is okay.

But in a larger data set, you'd probably want to avoid this kind of query because you could match much more than farmers working at dawn. You'd match anything that begins with FA.

So be careful with

44:19

this and think through what pattern should you use to fix up your titles. Let me just hit enter here.

And now let me try hitting up arrow again to show you the result. And now we're in a pretty good place.

I see all of my titles like farmers working at dawn are

44:36

correctly capitalized, consistently spelled, and all in one place. Okay, let's try the others here.

I also have imaginative landscape which could be a little bit better designed here, a little bit better grouped as well by fixing some of these typos. Well, here I

44:53

could try to match on to maybe imaginative. That seems like a long enough word that it could be useful for me here.

So I'll say something like select not select I'll do update votes and set the title equal to imaginative

45:09

landscape. Now I want to match any rows that have a title like imaginative space percent end quote semicolon.

Now I'll match any title that has imaginative as a full

45:26

word at the very beginning of this phrase. This is I would say a better query than FA because FA could match much more than this painting name here.

If we have only one painting that begins with imaginative, this could work for us now. So I'll try this.

I'll say enter.

45:44

And now let me rerun the query group by. And we're so close.

There's still one more imaginative landscape. So for this one, I might just need to manually update that piece.

I could say let's do update votes and set title

46:02

equal to in this case imaginative landscape where of course the title currently is imaginative landscape without the a in there. Now I'll hit semicolon to run this query.

I'll show you the results. And now I think we're

46:18

really almost there. There's only one more to do.

Profusion of flowers. Let's fix that one.

Now I could say select votes not not select update I'm always updating here update votes and then we want to set the title column to be equal

46:35

to profusion profusion of flowers like this then where the title is like in this case profusion at the beginning notice how I can't use profusion space and then

46:51

percent afterwards because we have one title that is literally just profusion plain and simple. No spaces afterwards, no spaces at the beginning.

To match that, I should just say profusion at the beginning. Now I can hit enter.

And if I do my usual query to show you the group,

47:07

the votes group by title, I see I'm finally at my correctly formatted data set. Farmers working at dawn has six votes.

Imaginative landscape has five and so on down the row. So here we've seen update being used not just to

47:24

associate artists with new paintings but also to clean our data to make sure we can actually tally votes up in a way that's consistent and clean in this case. What questions do we have on update?

Now

47:40

let's go to Vixay. >> Yes.

So uh what I wanted to ask is if there is a specific function for like lower case. So there's a there's a function for upper and there are the one for like all lowercase.

>> Yeah, a good corresponding question here. We used upper, but is there lower?

47:56

Turns out there is a lower function. It's spelled um L O W E R and is an all lower case like upper was.

So I could use that as well. There is a whole list of what SQLite calls these scalar functions.

Functions that take in some set of values and return to you some

48:11

modified version of those values. So if you do some googling look for SQLite scalar functions you can find all those functions in one place in the SQLite documentation.

Let's take one more question here. Let's go to Rupender.

>> So yeah my question is so instead of

48:28

updating all the titles is it possible to create a new column with the four categories that we want to have actually. >> Yeah I could see that working too.

So there's a few ways to fix this data. You could for imagine adding a new column

48:44

and maybe you assign values to the rows in that column based on what you see in the um title that they've given you. So if you can match something like imaginative and you'd know that all your paintings associated with imaginative have the same title, you could group

49:00

them into the one category, the two category and so on. Um here you wouldn't be modifying those titles.

you just be trying to strategically apply different categories which could also work as a solution here too. So good thinking.

Okay, so we've seen now how to insert

49:17

data, how to update data and previously how to delete data. What we can still do though are learn how to have other SQL uh command other SQL statements that can run after we make our very own SQL statements as well.

We'll come back and

49:33

talk about these things called triggers. And we're back.

So, we've seen now a whole collection of ways to write data to a database. We've seen how to insert data, how to update data, and even delete data.

What we'll see now, though,

49:49

is this idea of a trigger, a way of writing a SQL statement to run in response to some other SQL statement like an insert, an update, or a delete. Let's consider our museum yet again.

And let's say they have a schema a bit like

50:07

this one. Now they have items in their collections, but they also have a transactions table.

Now, wouldn't it be nice if whenever I deleted something from the collections table, it would show up in transactions as sold, having

50:23

been sold from the museum's collections. Well, I could use a trigger to do just that.

Let's see it visually first though. I'll say let's try to delete spring outing from our collections table.

Well, if I do that, I could pretty easily do it with delete from.

50:39

Right now, it's no longer part of my database. But if I have created a trigger to run and insert whenever I hear or have listened to a a delete on my collections table, I could then see that same title in my transactions table

50:56

as sold all automatically. Let's think too maybe the museum acquires some piece of artwork.

Maybe they actually add something new to their collection. We want that to show up in our transactions as well with the action of bought.

Well, let's try it. I could

51:13

probably add a new row here. I could say let's add pianies and butterfly to my collection here.

Now, if I have the appropriate trigger, I could run an insert into on transactions automatically that would insert this

51:29

same title with the action of bought, thereby keeping a log of all of our sold pieces of artwork and all of our bought pieces of artwork. Now, to create what we're calling a trigger here, we can use the following syntax.

I could say first

51:46

create trigger and then some name. So I tend to give triggers names to identify them among all of my database schemata like my tables and so on.

Here after I say create trigger I have to specify should this trigger this statement run

52:03

after or before some other SQL statement. Let's keep going with before here.

Maybe it runs before an insert on some table. That's fine.

It could also run before an update of a column on some

52:20

table. It could even run before a delete on some table.

And then keep in mind this could be before or after any of these kinds of SQL statements. In this case we have insert, update and delete.

Now after we say this, we should say too

52:37

is for each row. for each row means that if I were to maybe delete multiple rows, I should run my SQL statement for each row that I delete.

If I delete two rows, I should run this statement coming soon

52:54

two times. Okay, so now we've set up our trigger, at least kind of the data, the the the setup for it here.

What we haven't done yet is specified what statement should run whenever we see a delete on particular table. Well, let's

53:11

try this here. I could say begin.

Begin means here comes my statement I want to run whenever I hear a delete on this table. Now I'll write that query down below that statement inside my begin.

And I'll finish it off with an end to

53:27

say this is the end of my statement. And my entire trigger looks like all of this lines of code here to say listen for a delete and run this statement in here for each row that I delete or update or insert.

53:42

So let's try actually implementing in this case the museum's collections where they're trying to sell or buy pieces of artwork and automatically add them to their transaction table whenever they create or delete some piece of data from their collections. go back to my

53:58

terminal here. Let me pull up a database.

I'll do SQLite 3 MFA. DB.

And you'll notice that this is the same schema that we've had before. We have collections, artists, and the created table.

What's missing here though is our

54:15

transactions table. So, let me make that for us here.

I'll say let me go create table transactions. And now let me give it an ID column of type integer.

Now I'll also give it a title column as

54:31

we saw before called text. It's in this column I'll store the title of the piece we either bought or sold.

Now let me also include the action column also of type text to say whether we bought or sold this piece. Okay.

Finally, I'll

54:48

make the ID column my primary key. And if I say semicolon here and hit enter, I now have created this table.

So if I say dots schema, notice how I can now see my transactions table down below and I'm

55:04

safe to run insert on this table to add some data to it. Well, let's try now creating our very first trigger.

We're going to try to create a trigger that whenever an item is deleted from our collections table, we actually add it to

55:20

our transactions table with the action of sold, meaning we sold this particular item. So let me say create trigger as we saw before and give it some name to identify it.

I'll call this one sell. Now I'll say I want to run this trigger

55:36

before I delete on the collections table. So before I run the delete query on my collections table, I want to run this statement instead.

I'll say for each row begin, I want to now give you

55:54

some statement to run for each row that I delete from collections. Let's make it this query here.

I'll do one, two, three, four spaces to indent and make sure this is clearly the query I want to run after I uh run the delete on

56:09

collections. I'll say I want to insert into transactions and I want to insert into the title and action columns.

What value should I insert? Well, I should insert the old title.

And actually in

56:25

triggers, you get access to this keyword called old. This is the title that we've just deleted from our collection.

So, old.title gives me access to the old row, the row we deleted, it's title column in particular. Now, let me add in the

56:43

action which is just plain and simple sold. So, this is the query to run before I delete on collections.

I will take the title of the row I'm about to delete and insert it into transactions along with the action sold. Let me type

56:59

a semicolon here to say this is my entire query. Hit enter again.

And now I can end that particular statement I want to run after I hear a delete on collections. So I'll hit enter now and let me type dot schema.

I can actually

57:14

see this trigger is part of my schema. I can see create trigger cell before delete on transactions underneath the transactions table.

Okay, let's try putting this into action here. Let me try actually deleting something from our collections table.

57:31

I'll say delete from collections where the title equals profusion of flowers. I want to sell this particular piece of artwork from our collection.

I'll hit semicolon here. And now nothing seems to happen as usual.

But if I select star

57:48

from collections from collections, I see that piece is gone. It's no longer in here.

And if I select star from transactions, select star from transactions, I actually see it's been sold. So

58:05

automatically I added profusion of flowers with the action of sold to my transactions table. I did not type insert myself.

I instead created a trigger to run this statement. Insert into whenever I deleted on collections.

58:21

Let's try one more. We want to not to be able to sell artwork but also add it to our collection by buying it.

So I could create a trigger called buy. Let me type create trigger buy.

And now I want to run this

58:38

statement forthcoming after an insert on collections. So I want to first insert a new item to collections.

And when I do after I do I want to run this query coming next. Now I want to for each row

58:57

begin the statement I want to run. So as we said before I want to insert into the transactions table.

I want to insert into the title and action columns just like this. Now I want to insert some

59:12

values. The values I want to insert well I want to insert the new title the new row I've just added.

So in addition to old we also have new when I'm top when I'm using a trigger on an insert. So I'll say new.title title where again new

59:29

is the new row or rows I'm inserting and thetitle is their column is a column called title. Now here I'll say the action was bought.

We bought this artwork. I'll hit semicolon here and now I'll say end to say this is the end of

59:45

my statement I want to run for every row I insert on collections. Let me hit enter here.

I've created this trigger. I can type schema to see it in my actual schema.

Now let's try inserting. I'll say insert

00:01

into collections. I want to insert into the title and accession number as well as the acquired columns.

Here I want to insert some new artwork. Maybe I sold profusion of flowers but now I want to just buy it

00:16

back. So I'll say I'll insert profusion.

Profusion of flowers is the title. The accession number was 56.257.

And the date we acquired it, well, that was in 1956412.

00:33

Let's hit semicolon here. Now I'll insert and I'll say select star from collections.

I should see profusion of flowers back in my table. Now though, if I say select star from transactions, select star from

00:49

transactions, I should see I previously sold provision of flowers and later bought it back. So again, I didn't run this insert myself.

I instead triggered it by making my very own insert on the collections table. So this is the power

01:07

of triggers to be able to run statements that I myself didn't create in response to statements that I actually did. So let me ask here what questions do we have on these triggers and what they can do for us?

01:23

Let's go to Simon. I'd like to know um uh if uh there are only there is only one SQL query that uh that you can write in trigger or multiple queries.

>> Yeah. So we saw begin and end in our

01:41

triggers here where begin indicates the start of our statement and end indicates the end. You actually can have multiple statements inside of the begin and end separated by of course that usual semicolon that we have.

Okay. So, one final idea here that we're

01:58

getting at, we're kind of touching out with this transactions table is this idea of having a soft deletion. So, when I dropped something from my collections table, it actually ended up in my transactions table.

So, it wasn't fully

02:14

deleted in the sense that I'm keeping a record of it somewhere else. There's also one more way among many others to implement this idea of soft deletions.

that is not quite fully deleting something from my database, but instead keeping it around with a log that we've removed it in some way. Let's look at

02:30

this table here. I have collections, but I also have this column called deleted.

And by default, the value here will be zero. That is, by default, I'll add some artwork to my collections table and

02:46

deleted will be zero. If I want to delete a piece of artwork though, what could I do?

Instead of deleting it fully from my table, removing the row, I could mark it as deleted. I could change the deleted column from a zero to a one.

03:02

Let's say I want to delete farmers working at dawn. Instead of writing literally delete from collections where title equals farmers working at dawn, I could say update the deleted column of collections where the title is farmers working at dawn and make it not zero but

03:18

one. Now I've marked this item as deleted.

I could run a query that excludes deleted items, but this row is still around in my table. So let's try implementing this idea of a soft deletion inside of our collections

03:34

table. so that we don't lose records of things we've actually had in our collection.

I'll come back to my computer here and let's work on altering our table for collections here. If I type dots schema collections schema

03:51

collections, you should see I have the table collections and also the triggers associated with it. What I don't have yet in collections.

If I go over here and show you, I don't have a deleted column. I have ID, title, accession

04:07

number, and acquired, but I don't have deleted. So, as we learned last week, we can use alter table to add a column to our collections table and by default make the value zero.

So, let's try that. I'll go back to my environment and now

04:24

I'll run alter table. I might say alter table and a table I want to alter which is collections.

Now I want to add the column deleted deleted to collections and I want to make this type integer

04:41

should store whole numbers whether positive or negative and the default value will be zero in this case. Now I'll hit enter.

And if I type dots schema collections, I should see I have my very own deleted column inside of

04:58

collections. If I type for instance select star from collections from collection semicolon, I'll see that by default all of these values are zero in my new deleted column.

So instead of trying to delete,

05:15

literally delete from collections, I could instead just update it. I could say take the updated column and flip it from a zero to a one.

Let me try update collections and set the deleted the deleted column equal to one where let's

05:33

say the title equals farmers working at dawn. And my query wraps here, but it should still work just fine.

I'll hit semicolon enter. Now I didn't use delete from I used update.

If I select star

05:48

from collections semicolon here I'll see well farmers working at dawn is in this table but technically at least we marked it as deleted. So it seems now I don't want to use just select star from

06:04

collections to see what's inside my collection. I want to apply a filter to remove those that have a deleted value that's not equal to zero.

Let's try this. I'll say select star from collections where deleted does not equal one.

If I

06:22

hit semicolon here, what do I see? Only those values that are not deleted.

I could go ahead and find them. I could say maybe select star from collections where deleted actually is one or deleted

06:39

equals one like this. And now I see all those rows that I marked as deleted.

Now this has some advantages. One of them is we keep data around.

We don't actually formally delete it. We can still recover it later on.

But it also

06:54

has some tricky ethical questions too. Like it's okay if we're talking about artwork here, but if you're talking about user data, is it right to only soft delete it if they ask you to delete it?

And particularly in conversation with um new frameworks like GDPR and the

07:10

right to be forgotten and so on. It's up to you as a programmer to make the right decision here.

When should you find data that's sensitive enough and actually delete it or when is it better to just soft delete it and keep it around in case you need it for later. So we'll end here on this note where if

07:26

I say select star from collections and I want to find those only where deleted is not equal to zero like this or not equal to one. This gives me back all of the items that are not deleted.

But wouldn't

07:41

it be nice if I could actually run a query on some brand new temporary table that actually only will ever have those items that are not soft deleted. Turns out we can do that with an idea called views.

And we'll see those in much more depth next week. We'll see you then.

08:01

[Music]

08:18

Well, hello one and all and welcome back to CS introduction to databases with SQL. My name is Carter Zeni and up until now you've been learning how to design databases and how to add data to them like how to insert, update, and delete

08:34

some values all together. Now along the way we've added some complexity that is we went from one table to multiple and we had relationships too among all those tables as well.

Now what we'll introduce today is a way to simplify some of that complexity and give you a way to see

08:51

your data all the more clearly. This tool called a view.

Now we'll begin with a familiar data set this data set of books. And we learned about this data set of books back in week zero.

If you remember, we had a table of 78 books that had all been nominated for the

09:07

International Booker Prize. It was a single table just of these books.

Now, quickly in week one, we introduced more than just this single table. We actually had multiple tables.

A table for books, a table for authors, for example, and also some relationship between those

09:25

authors and those books. We said that an author could write not just one book but multiple books and similarly a book could be written by not just one author but multiple as well.

This is what we called a many to many relationship. Now

09:42

we also saw this not just visually but in our database in terms of tables as well. We had this kind of schema where we had a table for authors, a table for books, and then a table in the middle to talk about the relationship between

09:58

authors and books. Which author wrote which book?

We could see this in the authored table. Looking at these IDs here.

And now to recap, if I wanted to find the book that Han here has written, let me ask how could I do that? How

10:15

could I find the book that Han in the author's table has written over here in the books table? And feel free to raise your hand.

>> By looking at the author ID, 31. >> Nice.

So start by looking at the author ID. That's a great idea.

So here we see

10:32

that Han's author ID is 31. And we could make this link between the author's table and the authored table.

So I'll highlight this here so we can see it visually. Han has the ID 31.

And here in the author table we see the author ID of 31 next to actually some book ID. This

10:51

book ID is 74. Well, what book has that ID 74?

Turns out it's the white book all the way over here. Now this is adding some complexity to our queries.

If I want to find who wrote what book, I have

11:07

to go through the author's table, then the authored table, and then the books table. That's just a lot of steps to go through.

And we could though try to simplify this to have a better view of this data to see it with just a single column for authors and a single column

11:24

for titles. So let's try to visualize this together.

What I might do first is try to flip around this primary key of authors, this ID column. Let's put it closer to the author table just visually, metaphorically here.

Now I

11:39

might see that these ids line up one to another. And we saw in a previous lecture that I could use a join in SQL to put these tables into a single table.

And what a join does if you remember is take the primary key in one table and

11:57

the foreign key in another and just find which ones match to put the rows of each table together. A bit like touching your fingertips together like this.

So I'll then visually move these together, join these tables into a single table. And notice how if I now just get rid of

12:13

these IDs metaphorically, just visually here, I now have a much more simple table, a simple view of this data. And if I want to find which book Han has written, well, I could do that.

Look at a single row here. Look at the name

12:29

column and then the title column here. Now what we're doing by creating this new view of our data set is actually creating well a view and a view is defined by this here a virtual table

12:45

that is itself defined by a query. Now what does this mean?

What is a virtual table? What is a query?

Let's go back a few steps and see this in action. So we started with in this case three tables authors, books and authored.

And

13:00

presumably there's some query here that gets us from point A, these three tables, to point B, this single table. That query might involve some selects, some joins we talked about earlier.

But there's some query we can write to get us from point A to point B. And notice

13:19

here that the result of this query is itself a table. Like this has rows and columns.

And what we can do is see all of those in terms of a table we could later on query. So a view is a way of

13:35

writing some query to see our data in a new way and then saving this view of our data so we can query it later on. Now why might you want to build a view?

Well, you might want to do it for a few reasons here. You could use it to

13:52

simplify your data as we just saw here. You could use it to aggregate your data to try to sum up some values and store that in a single table.

You could use it to partition your data or divide it into logical pieces. And you could even use it to secure your data to perhaps hide

14:08

some columns you don't want somebody to see. There are more reasons too to use views, but we'll focus on these for today.

And let's begin by focusing on simplifying. So we saw earlier a way to simplify these multiple tables down in

14:24

to one. If I go back here, we'll see we got from this set of three tables to a single table that had both author names and author titles.

So let's see here exactly how much we're reducing complexity by trying out different

14:41

queries and then adding in a view to see all the benefits of this new view we have here. So we'll go back and we'll see in this case our three tables authors books and authored and let's say I want to find the books a particular author has written.

I could write a

14:58

query to do just that. Let's go back to SQLite here to run that query ourselves.

I'll come back to my computer here in SQLite and my goal will be to find which author wrote which particular books. So if I go to my database here, I can go to

15:14

my terminal and type SQLite 3 long list. DB to reopen this database.

And now I should see if I type schema all the tables that are inside of it. I have a table for authors.

If I scroll up a little bit, a table for books and a

15:29

table for authored, that is relationships between authors and books. Now, let's say I want to write a query to find which books Fernanda Melor wrote.

Well, I probably first need to find Fernandanda's ID as we just saw

15:46

visually in our earlier example. So, let's find Fernandanda's ID.

I could say select let's say ID from the author's table where the name equals Fernanda Melor. So, I'll say select ID from authors.

Then where the name equals

16:03

Fernanda Fernanda Melor semicolon. So it seems Fernanda's ID is 24.

And I could use this ID in the rest of my query, but I could probably do better than this. I could use what we saw before something

16:19

called a sub query to take this query I just wrote and put it inside some other one here. So let's take the next step and find the book ids that Fernando wrote.

So let's try looking at the authored table for that which has author

16:35

ids and book ids side by side. Okay, I'll say select in this case the book ID from the author table where in this case the author ID is equal to well I could say 24 but I want to do better.

I could

16:52

actually have a my very own subquery here to find Fran's ID dynamically. I could put uh parenthesis here, then hit 1 2 3 four spaces and say select ID, select ID from the author's table where

17:07

the name equals Fernanda Melor, that same query from before. I'll close this out.

Close out my sub query. And then if I hit enter, I should see now the book IDs that Fernanda has written.

But there's still one more step here. What

17:24

do I now need to do? I have book IDs that Fernando wrote.

But how do you think I could find the titles from those book IDs? >> You could select the title from the books.

>> Nice. We need to select title from that books table.

So if you remember, we have

17:41

titles inside of our books table, but we still need to link those titles through their ID to the book ids Fernanda has written. So let's do one last step here.

I'll come back to my terminal and I'll write the query in full now. So I know I

17:57

want to end up with titles. I'll say select in this case title from the books table.

Now I want to find particular titles where the ID is in that list of books Fernanda has written. So I'll say

18:12

where the ID is in some subquery, the query we actually just wrote. So let me write it again.

I'll say 1 2 3 four spaces here to indent for style. Then I'll say select the book ID from the

18:28

author table. in this case where the author ID is equal to some number in this case Fernand's ID.

But what is Fernand's ID? Let's find out.

I'll make another subquery here to find Fernand's

18:44

ID indenting four times just for style here. Then I'll say select ID from the author's table and then again indent where the name equals Fernanda Melor.

Then I'll close out all my queries to

19:00

create this single long query to find all the books Fernanda has written. And again the order here is first find Fernandanda's ID, then find the book IDs associated with that ID, then find the titles associated with those book IDs

19:17

themselves. Now I'll hit enter and I should see all those books that Fernanda has written.

Now, as we talked about, this is a lot of complexity, a lot of tables to go through, and there's probably a better way to do this. Let me open up a new terminal and try out a

19:34

different way of going about this. I'll make a new connection to my database.

I'll make a new terminal and I'll say SQLite 3 long list. DB.

Hit enter. Now, I should see that I have a brand new connection to my database.

And I could

19:50

try to find a better way to do this. Let me try to do what we did visually and like join these tables together.

Well, I could try select name and title from the author's table. But notice how author's

20:08

table doesn't have titles in it. It does have the name of the author, but it doesn't have book titles.

So to get titles, I have to join in some new table here. I'll hit enter and I'll say join let's say the author table on author's

20:26

dot id equals author do aauthor ID and all I'm doing here is saying I'll take first the author's table and then why don't I try to combine this table with the authored table linking it up by

20:43

looking at the author's do ID column and the authorauthor ID column And visually, let's take a look at what we're doing here in this join. If I look at this in my slides here, I should see I started with my author's table here.

I

20:59

had names of those authors and I had an ID column in that author table. Now, I'm trying to join the author table by telling SQL that these two columns, author ID and ID, should align.

They

21:14

should line up like this. So, their rows are all together, but there's still one more step.

I've joined the author table and the author table, but what's the next step to find book titles? I still

21:30

have to join the books table. So, let's try that here.

I'll come back over and instead of joining just the auth table, let me now try to also join let me hit enter to also join the books table. I'll

21:46

join books on books. ID equals in this case authored dotbook ID.

And to be clear, this line here means that what I'm trying to do is now

22:03

join this books table which is over here with this author table aligning it so that their ids match up into a single row as we see here with 74 and 74 one and one and so on. I'll come back and

22:19

now let me try this. come back over here and I'll just try hitting enter to see what I can see from this query.

Okay, let me zoom out a little bit and you'll see I actually do have author

22:35

names next to book titles all in one table just as we thought we would. So, this is useful to me.

But what if I wanted to have this stored as a way to see my tables? I want to be able to

22:52

query this kind of table I see right here. Well, for that I could try to introduce a view.

Now, a view starts with a query as we just saw. I wrote a query to select names and titles from

23:09

three different tables joining them together. But if I want to save the results of that query so I can query them later on, I could use this syntax here.

create a view that has a name that I can give to it as this query down

23:26

below here. So create view given some name as the result of this query gives me a view that is then saved and part of my schema.

So let's try that here to create a view by which we can see both author and book titles side by side.

23:44

I'll come back to SQL light here and I am already in this brand new terminal. So I could try to remake this view and save it now for later usage.

I'll say create a view and call it long list. The

24:00

same name we gave our single table back in week zero. Now I'll say this view is the result of this following query as let's say select name and title from the

24:16

author's table but not just the author's table join let's say the author table join authorred on author's do ID equals author do aauthor ID and then join again

24:34

our books table as we saw visually here. Join books join books on books dot id equals authored dotbook id.

Now that is the query I used before

24:50

to see author names and book titles side by side. If I create this as a view hitting semicolon and enter nothing quite happens.

But now if I type dots schema and hit enter, I should see a

25:06

view called long list that has two columns, name and title. And I actually see the query I used to create this view.

So let me think now I want to see what's inside this view. And to query a

25:22

view, I can use it exactly as I would a table. I could say select star from long list semicolon hit enter and now I should see author names and book titles side by side.

So if I have this kind of

25:40

table with authors and book titles side by side, what then does my query become to find the books written by Fernanda? Let me ask our audience here.

What query could I use now on this view to find the

25:56

books written by Fernanda? >> Thank you, Mr.

Carter. We cannot query uh the book or the all of the books using her specific name.

>> A good idea. I can use particular name in this case and perhaps just use a

26:11

simple where. So let's try that here.

I go back to my terminal and if I want to find the books written by Fernanda, well, I could just query for them in this single table that I created called long list or not really a table but now a view. I'll say in this case select the

26:27

title from select title from long list where to Muhammad's point I could just say name equals Fernanda meernanda Melor semicolon. I'll hit enter here and

26:43

I see those same books Fernanda has written. Paradus and Hurricane season.

So let's see where we started here. We started with this kind of query long multiple lines many sub queries involved

26:58

just to find the answer to which books Fernanda had written. Now though that we have a new view, I can simplify this query to just this here.

select title from long list where the name equals Fernanda. And I can do all of this

27:15

without using much if any more disk space. A view, remember, is a virtual table, not a real one.

And I can then have the results organized like this, while still being in those underlying tables stored essentially elsewhere.

27:32

So, let me ask what questions we have on how views can simplify our data. Um, can we then somehow manipulate the views to uh maybe order the results or display them just differently?

27:48

>> A great idea. So, if I wanted to maybe order these books, I could absolutely do that in the same way I would do in a table.

So, let me go back and show you how to do that. Let's say I want to organize these books alphabetically by title.

So, if you remember using our

28:04

regular old table, we could use order by. I could do the same thing here.

I could say select both the name and title columns from my long list table. And now let me try to order by order by the

28:19

title column in this case. And I'll hit semicolon.

Now if I hit enter, I should see the book titles organized alphabetically. Now, the view itself does not have these titles automatically alphabetically sorted for me, but I

28:36

could make it do that. If I had when I was making this view included order by let's say title at the end of that query, I would then have those titles saved in this view automatically as a toz.

So very powerful if you want to not

28:52

just find particular ways of seeing your data, but also organizing it or ordering it too. So with that note, we've seen how to simplify one example of having multiple tables down into one.

We'll come back in just a few and see what we can aggregate

29:08

data too, putting that data into a single table for us to see some statistics and such as well. Come back in a few.

And we're back. So we just saw how to simplify some data, how to take multiple tables and see them as one.

What we'll

29:25

do now is think about how we can aggregate data. That is to take many individual data points and summarize them to find an average or a min or a max but see those results inside of a view.

So if you remember in our long list database we had a table of ratings

29:44

and in this case books had individual ratings from individual people. Like maybe I gave book ID one a four out of five stars, but David gave it only a three.

Now we store these individual ratings in our database. But ultimately,

30:00

I don't care so much what I rated it or maybe what David rated it. I care about the average rating, let's say, for each book.

But if we care about those averages, why then would we store all the individual ratings? Let's think of

30:15

this as a design question here. Can you think of a reason we might want to actually have these individual ratings stored in our database?

What does that give us at the end of the day? >> Perhaps reliability to know uh how many persons rated a book.

30:31

>> Nice. So, it's nice to have some more data.

I could know how, let's say, I rated a book or how you rated a book. That's good here too.

And I would argue that from having this additional data, we could compute more statistics. I could find not just the average rating

30:46

but also the minimum rating or the maximum rating or the median rating and so on. So these individual results allow us to find and compute more statistics like averages, mins and maxes as opposed to just storing the average.

Now if I

31:03

wanted to find the average of these results, what could I do? Well, we saw before I could try to group ratings by individual book IDs.

Let's say I want to find the ratings for book ID 1 and book ID 2. I could group by the book ID

31:19

column, turning these into individual groups. And let's say I want to find the average here using a SQL query.

I could say find me the average of the rating column when I have groups by book ID. that effectively takes my table like

31:36

this and says, "Okay, I'll combine all the ratings for each individual book ID and find for you the average. I'll first kind of compress this data together, literally putting it maybe one on top of the other here.

And then find the average of all those results. Let's say

31:52

3.67 for book one and 2.5 for book two." Kind of compressing all of our data into a single result. In this case, the average.

So let's walk through then how we found the average using a SQL query and then we'll turn that into a view. So

32:09

I'll come back over here to my computer and I'll open up long list db. I'm already here.

So I can type dots schema ratings to see the schema for this ratings table. And you'll see it does have a book ID column and a rating

32:26

column as well. So if I want to find the average rating for each individual book ID, what could I do?

I could try to select the book ID column and the rating column like this. But I don't want to

32:42

select just the rating column itself. That gives me individual values.

I want to find the average rating instead. I could instead say find me the average rating from this table.

32:57

Now let me give this a new name. I'll say as rating calling the average rating now newly in this case rating.

And I'll select this from the ratings table. And finally I need to group by something.

I need to group by book ID. That means

33:15

I'll calculate the average rating for each book ID. I'll hit semicolon here and enter.

And I should see all of these book IDs top to bottom with their own rating.

33:31

But here it doesn't look so pretty. What could I do to try to make this not so long?

Like 3.774019431. There's a better way to do this.

And we saw it before. What could I do to enhance this view of my ratings table?

I

33:48

see some suggestions about rounding the ratings, but I was also going to suggest adding the book titles of course. >> Ah, good uh point here too.

So, we first want to add the first want to probably add the book titles to see what titles we're talking about, not just in this

34:04

case individual book IDs, but also a good point to say, let's try rounding these so we don't see 3.7740194. Let's try to round that maybe to two decimal places just to be safe.

So let's first try rounding this and then we'll try adding in some book titles to your

34:21

suggestion here. I'll come back over and let me just try rounding it first.

So I will come back to my terminal and I'll say I want to find the average rating for each book ID and I want to select not just the average rating as we did

34:37

before but the rounded average rating. So I can use round and say take the average rating and round it to two decimal places.

And I'll call this column just rating in the final result. I'll select this from the ratings table.

34:55

And now I can group by in this case book ID. Enter.

I should see I have rounded ratings now. A little more pretty than before.

Now to your point, let's try to add in not just book IDs but also book

35:10

titles. Like what does it mean for a book ID to have some rating?

I want to see titles here in the end. So let's do that.

I'll say select book ID and also select title and maybe even select the year that book was nominated for the

35:27

Booker Prize. I'll then select the rounded average rating to two decimal places as rating.

And I want to select this from the ratings table from ratings. But what do you notice?

If I

35:43

were to select just from ratings, what data am I missing? And where could I find it?

>> Hey, you're missing the titles from the books uh table. Yeah, I have to have titles and those are stored in the books

35:59

table. So, what should we do?

Probably just join in the books table matching up that primary key with some foreign keys. So, I'll come back over here and we'll do that.

I want to select from ratings of course, but I also want to join in in this case the books table. So, I'll hit

36:14

enter. And now I'll say join as well the books table.

Now, how can I match up the books table with the rating table? Well, I know that the rating table has a foreign key called book ID and the book table has a primary key called ID.

So,

36:32

I'll tell SQL I want to match these two up a bit like this. I'll say join books on ratings.book ID, that foreign key that's inside of ratings equals in this case books.

36:47

ID. Now, there's one more step which is to group by book ID.

still. So, I'll hit enter and group by book ID, but now I should be able to hit semicolon and enter.

And I should see not just book

37:04

IDs, but now titles and the years those books were nominated as well. So, this view is getting better and better.

And I might say we actually go ahead and just add this to our schema. We could create a view that returns to us the results of

37:19

this query. So, let's try that.

I'll say create a view called maybe average book ratings. Average book ratings for people to come in and do analysis on if they'd like.

Then I could say um I want to create this view as that query we wrote

37:37

before. So I'll say again select the book ID the um title and the uh let's say the year column the rounded average rating to two decimal places and then I

37:54

want to select it as rating. I'll select this from the ratings table from ratings and then I'll join as we did before the book table from ratings and then join books on that primary key and that foreign key.

So I'll say on

38:11

ratings dotbook id that foreign key in ratings and set that equal to books ID that primary key we're going to use to join these tables together. Then I'll say group by book ID.

And I should have

38:26

if I hit enter my very own view called average book ratings that I can use to then see the averages for each of these books. I'll say select let's say star from average book ratings semicolon.

And

38:43

now I should see all of those results just as we had before. Now, notably, because this is in a view, I'm not using much if any additional space in my database.

I still have underlying this view individual ratings. But because I

39:01

saved this query and the results it created in a view, I can actually have access to the a rounded average ratings for each of these and I can query that view as well. So let me ask then what questions we have on how we've used

39:17

views to aggregate some data so far. >> Actually I have a question can we use both uh uh same name like we before use long list and here we using for average something can we use long list for this

39:35

both of them. >> Yeah.

So if you remember before we had this view called long list and long list had book titles and authors inside of it. And I think you're asking could we try to join let's say the long list table with our ratings to get book

39:50

titles and so on. We couldn't do that because we don't have the ID column in that long list view.

Um we could though perhaps write some queries that involve that long list view as well. Okay, let's keep going here and seeing

40:07

what advantages this view might give us. So, I'll come back over here.

And one more advantage is that if I were to add in some new rating to my ratings column, I could just requery this view to find the updated average. So, let's say we

40:23

use some insert into statement here and I update some books ratings. Well, if I'm a data analyst and I come back in a little later and I want to find the updated rating, I can do it just again by selecting from selecting star from

40:38

average book ratings like this. Average book ratings.

Hit enter. And now I see perhaps some updated averages as well.

Every time I query this view, I'll rerun essentially that query from before. And

40:54

now I'll see the updated averages for each book. Now sometimes as a data analyst I might want to create a view but not store it longterm as part of my schema.

Like here

41:09

if I type schema I see both our prior view called long list right here and I also see our average book ratings view. But if I am somebody who comes in and I maybe want to find the average book ratings not by each book but by each

41:27

year I could do that but then not store that view long term. Maybe I don't want others to see this view or maybe it's just for my own u analysis on this given day.

Well, to do that, I could move beyond just create view and I could also

41:44

enhance this to be create temporary view where a temporary view exists only for the duration of my connection with database. So to be clear, that means if I type SQLite 3 long list db, I'm opening a connection to the database.

If

42:01

I then typequit, I would then lose that connection, quit that connection, and that view would no longer be there. So, let's try this.

I'll come back to my terminal, and we'll create a temporary view that shows us the average ratings for the books by the

42:16

year they were nominated, not by every individual book here. So I could try doing the same query from before like select um let's say the rating average rating select it from the ratings table join books and titles and so on but what

42:33

do I have already to build on top of if I wanted to find a view that has a title a rating and even let's say the year that book was published what do I already have in my schema for that? Uh,

42:50

so we could use the view that we just created. >> Yeah, nice idea.

So we could actually use the same view we just created to create ourselves a brand new view. So just because something is a view doesn't mean we can't use it to then create another view as well.

So we'll do

43:07

exactly that. We'll come back over here and I can type uh dots schema to see I still have this view called average book ratings and in it I have book ids their title the year they were nominated and their average rating but now I want to

43:24

find let's say the average rating for each individual year not for each individual book so what could I do I could say select the year column and the rounded average rating in this case from the rating column in

43:43

average average book ratings and I'll wrap this query here. Let me zoom out so you can see it all online.

Now I'll say I want to group by the year column. So here I'm now saying find me the average rating of

44:00

each book of each book's average rating for each individual year. I'll hit enter and we should see if I zoom back in for 2018 we have 3.75 for 2019 we have 3.64.

So this means that apparently in 2023

44:16

the average rating for books that were long listed for the international booker prize was 3.78 and same for 2022 3.87 2021 was 3.69. So I have this new view here.

I could store this temporarily inside of a view

44:31

that I could use while I'm doing some analysis on this given day. So I'll say create a temporary view and I'll call this one average ratings by year.

How were the books rated each individual year on average? And I'll say this is

44:49

the query that I just did before. Select in this case the year and the rounded average rating to two decimal places as the rating column from average book ratings.

That same view from before. Now

45:06

I'm creating a view from my view. I'll group by here.

Group by year. And then I'll hit enter.

And I should see nothing popping up. But now if I wanted to query this view, I could say select star from

45:22

average ratings by year and I'll see the results of that view. Now though, here's the catch and why it's temporary.

If I were to typequit now.quit

45:38

and try it again, SQLite 3 long list db. Oops, let me retype that.

SQLite 3 long list.db. If I now try to select star from average ratings by year semicolon,

45:55

I'll get no such table and really no such view. I don't have a view called average ratings by year anymore because this was temporary.

So let me ask what questions do we have on temporary tables if any

46:11

temporary views rather? Right.

So thank you. So the the first thing that I notic is that it just you know helps us to do some kind of testing when we use you know temporary views.

Basically you know

46:26

uh the views will not be saved in the database. Uh but again it's just you know a good way to you know just to test the query whether it is usable or not.

>> That's a great use case for them. And in general, if you are working on a

46:42

database and you want to organize the data in some way, but you don't want to store that organization long term, a temporary view is great for that. If though you are a developer and you want to ingrain this view as part of your database, you might just use create

46:58

view, plain and simple, to have it always be in your database for you wherever you connect to it and whenever you connect to it. Great question.

Okay, let's keep going and let's introduce now something called a common table expression or a CT.

47:14

And a common table expression is simply a view that exists for the duration of a single query. So a regular view, as we saw before, exists forever in my schema.

A temporary view exists for a single connection, but a CTE exists for a

47:32

single query. And the syntax looks just a bit different.

With a CTE, I can write a single query, but at the beginning of that query, define the CTE, the common table expression. To do so, I would say with and give it some name as and then

47:50

inside these parenthesis say the query I want to be this CTE here. I could then optionally follow it up with more CTE for this same query or I could omit the comma and then use just select from the

48:05

name of my CTE up above. This is useful if you want to have some very temporary view that you don't want to store in your schema but you do want to use for this particular query.

So let's see an example now recreating our average

48:22

ratings by year but now using a CTE. So I'll come back to my computer and I'm already in my SQLite terminal.

What I should do though is try to drop the prior view. I want to remove that view

48:37

called average book ratings to create room to have now a CTE with that very same name. So I'll say drop view and the name of the view I want to drop average book ratings average book ratings

48:53

semicolon and now I should see that that view is no longer in my schema. So I want to recreate though what we did before which was finding the average ratings of books year overyear.

So let

49:09

me try to write this query. But it would be handy if in that query I had access to a view that gave me the average rating for each book.

So I'll write the query like this. I'll use a CTE and I'll say with average book ratings average

49:26

book ratings as now this query I'll write now inside. And this query should be familiar.

It's the same query we use to find the average rating for each book. I'll say here I want to select I

49:42

want to select the book ID column, the title column, the year column, and the rounded average rating to two decimal places. Calling it rating calling it rating in this case from my ratings

50:02

table like this. Now what do I need to do?

I also need to you saw before join in my books table join books on the primary and foreign keys aligning. So I'll say the book ID

50:18

column that foreign key in the ratings table should equal books. ID that primary key column in books.

Then I will group by the book ID to find the average rating for each book. And now let me

50:34

close out this CTE. I'll close my parenthesis.

And because I'm only using one CTE, I don't need a comma. I can then just write the rest of my query.

I'll hit enter for style sake. And now

50:50

with this CTE called average book ratings, I want to find the average rating yearover-year grouping not by book but by year. So I'll say select in this case year and then select the rounded average rating still to two

51:08

decimal places as a new rating column. Then I'll select this from in this case my CTE.

So I'll hit enter here as well from average book ratings that same CTE

51:25

I defined earlier in my query. Now finally to find the average rating across individual years I'll say group by year semicolon and now hopefully if I hit enter I should see the average

51:40

rating for each year in my database. Okay, so the CTE is useful when you have sub queries you don't want to repeat or when you want to make a view that only exists for the duration of a query.

And

51:55

it's with all of these tools like views, temporary views, and CTE that we can get away with storing statistics about some data in our data set and not storing them uh in the actual um database, but only inside this view we might have

52:11

here. we'll do next is see how we can use views to create not just these aggregations but also partitions of our data.

A way to split our data up into logical pieces for use for our own sake or even for application sake as well.

52:26

We'll come back in just a few. And we're back.

So we've seen so far how to use views to simplify and also aggregate our data. What we'll see now though is how to use views to partition our data.

that is to break it into smaller pieces that

52:42

be useful to us or to some application. Now on the international booker prize website they have a page for each year of long listed books.

But in our table we have all the books together regardless of their year. Well maybe it

52:59

will be useful in the application even for me personally to have a table one for each year of books that have been nominated for this prize. But before I create that view, I should ask how do I try to find the books nominated in 2023?

53:16

If my table looks like this, I have a table of books with ID, title, and year columns. What query could I use to find only those books nominated in 2023?

>> Use the wear clause with the year.

53:33

>> Yeah, I could use the wear clause and try to search for a given year. Like I could try to say where in this case the year is 2023 or equal to 2023 and that would give me back these two rows.

If only two books were nominated in 2023. In reality it's 13.