CS50 SQL - Lecture 1 - Relating

Key Takeaways

[Music] well hello one and all and welcome back to cs's introduction to databases with SQL my name is Carter zanki 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 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 two can apply these same skills for whatever you want to 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 we had this data set that was the books that were longlisted for the international Booker prize to be 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 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 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 I'll type sqlite SQ light 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 this sqlite prompt and here's what we saw last time I could write my SQL queries well before we start let's see the changes to this database so if I want to get a f 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 SQL light to show me what t 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 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 Rel relationship among 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 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 and I book table I have titles and we seem to 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 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 might be something like the honor System I mean I'm honest andly 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 book's 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 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 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 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 AR we run into some issues here if we only have one table of authors and books there is some redundancy some 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 let's say Olga wrote three or four or five Olga would be in here that many times and so we could probably do a bit better than this and let's actually just say this is going to be the most efficient idea for us let's go back to these multiple tables here and think 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 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 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 onetoone relationship if this is our idea of authors and books one author writes one book and one book is written by one author this is a on toone 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 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 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 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 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 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 and 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 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 trans ated and maybe that makes sense to you but at 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 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 use and let me show you what those rules are so here we have what's called a crowsfoot 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 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 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 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 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 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 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 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 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 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 yeah so 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 are existent in the first 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 database as we'll see in a future week 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 how they are related to one another so if you're designing you have the choice but if you're taking database 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 relationships how do we determine that relationship I mean we know that it exist 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 to assign then these relationships perhaps in our database like we know that an author could write multiple books but how do you represent them 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 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 role play 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 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 dranfield and the birthday party by Lauren Moen so let me ask you if 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 lawence I'm hearing Maybe going for Lauren so let's try Lauren over here we'll search for the one by laurren so the birthday party by Lauren M okay okay let me find you that book but well now there are still two books I could choose from do you want in hard cover or paperback and so I could ask you again well which one do you want do you want it in hard cover 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 Paperback let's try that one so we'll search for the birthday party by Lauren m 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 and you could keep voting here but for the sake of just keep 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 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 if I have an ISBN this is what we call in database terms a primary key something that is going to 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- 180 40 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 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 I have an ISBN to uniquely identify each of these books and this 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 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 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 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 what is the ISBN of Boulder and then which ratings correspond to that ISBN in some other table so this is an example of how we could represent these one to many relationships I have here a table that 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 I can 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 our primary key here so let me ask this is an ISBN but if I want to put 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 want I get by using this ISBN as my primary key you would like run out of memory run out of memory it's a good guess like if I had a lot of ISBN 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 bits per character that's let's say 13 plus 4 dases 17 bytes po 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 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's doesn't really mean anything in the context of being a number and now I've lost my unique identifier so we could improve this even further and we can 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 matter 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 just use ispn 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 of space here and notice too that maybe Boulder has the primary key the ID of one the white book has the ID of 74 well does it matter if it's 1 2 3 or four it doesn't as long as 74 uniquely identifies the white book we're okay 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 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 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 if I looked at the author table and saw 23 next to one where 23 is in the author ID column and one is in the book ID column you're to raise your hand what would it mean for me to see those two numbers next to each other yeah it would mean that uh the author that is that has the IDE 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 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 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 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 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 the book ID be the same for example can there be a author ID of one and a book ID if 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 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 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 book side I could say the numbers in here reference the primary keys in the books table and those ID 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 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 it h 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 so 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 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 question a good thought for tradeoffs 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 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 to 27 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 nonunique I'm not making it an ID that somebody else already has and so for that 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 idea of particular book we just use them in our queries as we'll see in just a moment so wonderful questions here we'll do 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 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 using different techniques to that in this case might actually use primary keys and forward Keys within those same queries now one Technique we could use is one called subqueries also called nested queries too that basically put one SQL query inside of another and they're useful in a few different cases but let's say I have one case of a on to many relationship maybe I have Publishers that are publishing many books for instance well I could use subqueries to help me answer questions about these kinds of relationships in my table to 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 galdo editions my favorite publisher let's say what's the problem here I want to find the books published by Fitz Caro 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 people to know the name of publisher yeah good observation so I want to find the books that are published by Fitz Galo 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 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 noticed that Fitz Calo editions has the ID of five so I could probably solve this problem but let me ask again what queries might I need to answer this question there are two queries that I need to do to answer the question of which books were published by Fitz C auditions what two queries are they I think it's select and where did you mean keywords or queries yeah so key would be great so I need to have some kind of Select involved I need to probably select something from Publishers and something from book so could I ask you what 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 where 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 name of the book published by this publisher since we found the ID 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 coraldo editions well I could write a query to find that and I'll say get back five for Fitz coraldo editions well that's one step 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 paradas so let's make this more concrete as you were doing yourself Johan Paul let's write these queries out let's say I have this first one to determine what is the ID of Fitz Caldo editions I could say select ID from Publishers where the publisher is Fitz Caldo editions and I'll get back 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 them back many titles here but here's 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 yeah so John 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 find this number for me more dynamically and so SQL supports this idea of writing a subquery 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 where the publisher ID is equal to well the result of this query here and notice how I have parentheses this means the query furthest inside the parentheses will be run first so first this query is run I get back five then 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 subqueries 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 yeah I'm hearing yes so if 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 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 to well let's say I might write select ID from Publishers where publisher is or equal to Fitz Caldo editions maybe that publisher doesn't exist there's no publisher with that name well in that case this query would return nothing and therefore the next quer 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 structure for our queries I'll go back 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 mleo editions or Mao's press rather so let me try to attempt this one by one 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 IDE of ma lao's press let me try to find that I'll say select let's say ID from publisher where the publisher is equal to none other than Mao's press like this end my quotes semicolon hit enter and hopefully I will see oh see an error 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 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 the Publishers table plural and we'll say um where the publisher is equal to mackley Ho press like this and I'll hit a semicolon Here and Now fingers crossed this should work I do see an ID of 12 okay so Mose pressed 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 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 books that MOS 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 the end essentially I'll say 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 so to do that I could open up some parentheses 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 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 where the publisher is equal to uh not fit scr auditions is equal to maclehose press end quote and now let me close close my parentheses I have an open one here that I should close to show this subquery is done I'll hit enter close my parentheses 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 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 first do is try to find the ID for inmemory 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 and 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 table where the book ID is equal to 33 I believe it was so now I'll hit enter and now I'll should see all of the ratings for this book and there are quite a few that people have left on this book on the Goodreads website so let me again try to improve this and make it just a little bit better I 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 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 space 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 that is my subquery now so I'll close this out I'll hit enter close my parenthesis now have a semicolon and uhit enter and I see those very same ratings well I could probably not get back the individual ratings I want to 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 go back over to my n query and I will hit enter again and I will hit the up Arrow to get back access to my early 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 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 subqueries and so far we've seen this working for a relationship that is one to many we could also use subqueries with many to 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 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 yes need to know idea of author and of that book and Bas 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 the three tables as you're saying so let's go back to 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 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 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 flight so I'll select the ID from the 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 it bit like this I'll select author ID 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 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 all start from the middle find the ID of 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 computer and here maybe I want to find all of those books or 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 say select ID from books where title is equal to the birthday party semicolon enter now the ID is8 okay let me 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 find finding the author ID I'll 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 from this prior query let me indent and say select ID from books where title is equal to the birthday party quote not a time I call in yet end my parenthesis semicolon hit enter and now I see 44 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 the begin or we're going to yeah start at the beginning the place we want to actually get to then I'll say where the ID where 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 author from author 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 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 and then I'll say um where where let me just make sure I'm doing the 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 space space again semi or uh parenthesis enter again parenthesis semicolon and hopefully after all this typing we'll get back what we want we see our friend Lauren Moen 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 so let me Po 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 of 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'm guessing you would set the for you wouldn't set the foreign key to the title instead it be for 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 cover and the paperback and all these other editions right well in that case what I might do is try to actually this is a good segue I might have multiple IDs that I'm searching for 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 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 multiple books or different versions of the same book let me introduce this one this one is called in very simple just i n and I can use in to say well maybe some key or some column is in some 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 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 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 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 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 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 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 book ID select book ID if I can type select 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 say select ID from authors where the name is equal to Fernanda melor parentheses 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 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 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 author where let me actually 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 authorized ID we care about I'll hit enter then I'll do space space space space four spaces for one indentation and now four more spaces 1 2 3 4 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 me close out my queries I'll hit enter space space space again and now I'll use parentheses 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 Fernandez 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 not regarding in 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 yeah good question about style here 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 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 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 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 or two when do we need to use in in work CLA when we are doing you know sub quering I suppose that we can also use sub quering without in as well you're right so we saw an example using equals and we all saw an example using in 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 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 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 we have seen many to one 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 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 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 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 IDE of one let's say three people wrote Boulder well in this case we could adjust this I 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 one two three times but what we're saying each time 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 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 good questions Okay so we've seen subqueries 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 joins so let's dive into joins and the whole idea behind 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 if you're not familiar sea lions are sometimes native to the coast of California and they like to travel they like 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 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 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 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 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 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 primer key the ID to kind of join them together using the primer key in the for 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 that some of these IDs are actually inside of this column too so we could use the primary key in literally both tables here and try to match it together to figure out how these rows could be extended to add more columns colums we could join these tables together to make this table ultimately showing us where a has um traveled and how long it took them to get there so the keyw for doing these kinds of manipulations with data in SQL is called join kind of 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 sea lines that I prepared for us today I will clear my terminal I'll type crl L and I'll get out of my long list database to do that I can type quit a SQL light command to leave this environment I'll hit enter and then what I can do is I can type sqlite 3 spacecore lions. 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 tables to see what tables are inside of this environment tables enter I see both tables migrations and sea lines which is promising for me here so we could see what data is inside of these tables I could say select star from ccore Lions uh semicolon hit enter now I see all of these adorable sea lions inside of this lovely table what if I wanted to find migrations 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 table and we have some IDs in our migration table that actually ar