CS50x 2023 - Lecture 7 - SQL

Key Takeaways

foreign [Music] [Music] [Music] [Music] foreign this is cs50 and this is already week seven and this is the week where we'll continue where we left off with python introducing you to a bit more syntax and capabilities of the language so you can solve like interesting problems but a lot of those problems increasingly are now going to involve data in some form after all if you think of most any website or mobile app uh or uh process nowadays that involves solving problems it almost always involves some amount of data and often data at scale lots and lots of data and so what we're going to see first today is that yes you can use Python to solve all the problems past that we've seen and also some data specific ones but sometimes it's just going to be annoying it's going to be a little painful it's just going to be more work than you might like to just get to some answer and so today we'll too introduce you to a new language called SQL structured query language and this is a language that rest assured is actually much smaller relatively speaking than C and python it sort of does less but it does it really well and it's a language for querying databases storing data in it updating it inserting it to leading it and so much more and it's the kind of Technology that's used nowadays in indeed web apps and mobile apps data science analytics and and so much more it's really good at storing lots and lots of data now this is yet another language and believe it or not next week we'll introduce you to three more languages HTML and CSS which are not technically programming languages they're all about Aesthetics and markup of information but also JavaScript which is in fact a programming language but the goals here in cs50 really are going to be to empower you to program more generally and indeed when you're out there in the real world some years from now invariably there's going to be some new other popular language out there and hopefully in this week and next week and Beyond among the goals is not just to teach you these languages specifically but again like how to teach yourself the future languages that we've not even heard about just yet so with that said let's begin with a survey of sorts if you go to this URL on your phone or laptop cs50.ly favorites a very simple Google form awaits to that's just going to ask you a couple of multiple choice questions so go to cs50.ly slash favorites and that should lead you to a Google form that looks a little something like this asking you first as of now in week seven what is your favorite language among those options here and then further down one more question if you think back on problem sets zero through six what was if any your favorite problem set problem be it in scratch or C or python so answer those two questions and in a moment I'll flip over to my screen here where you'll see and anyone who's used Google forms knows the spreadsheet that's collecting now this data Microsoft Office 365 can do the same if you use one of those forms and what you see here now is a spreadsheet in Google Sheets enumerating all of the audience's questions language is in column B problem is in column C and each row represents one student who has responded a few of you were super eager for class today at 8 33 a.m Eastern Time 10 32 11 10. okay so now we're getting into the actual class time here and if I scroll down we'll probably see a few dozen couple hundred Answers by now and yeah so we're getting a whole lot of answers here and I'm seeing some patterns emerge but it's not necessarily obvious to humanize what those patterns are now of course you can use Google spreadsheets you can like highlight the data and you can create charts magically out of it but you can only do what Google lets you do with the data and same thing for Microsoft Excel or apple numbers but wouldn't it be nice to just be able to manipulate the raw data relatively simple though it is to just answer questions about the data Maybe long term create your own charts customize it just the way you want rather than be holding to like software that's off the shelf like this well how could we go about doing this well let me propose that we treat this data set now as what we're going to call for now a flat file database we'll see today that there's fancier databases but the simplest database in the world is really just like a DOT CSV file and we saw that a couple of weeks ago in C we wrote a bit of C code that used fprintf to write data to a file using commas as the separator we didn't really do much more with csvs at the time though because it's really annoying painful time consuming not fun to you see for something like that because of malloc and memory and all that stuff but with python it's going to be much easier and so anytime you have access to some data set where you can just like download it to your own Mac or PC or your Cloud environment it's sort of a candidate for now writing code to do something with the data Maybe analyze it right away if it's been human imported manually maybe you have to clean it up by doing a lot of find and replace but not with your keyboard but rather with code and so let me go ahead and do this let me go back to my Google sheet here that has all of the data that's come in now and let me go ahead and download this via the file menu here and let's see download and you can see a whole bunch of options if most formats might be familiar but today we'll focus just on this one comma separated values or CSV that's going to go ahead and download it on my Mac here into my own downloads folder and now I'm going to go ahead and do this let me go ahead and pull up vs code in the cloud here and if you've never done this before there's a couple of ways to do it but the simplest way to upload a file to your code space so to speak is just to sort of drag and drop that's going to magically upload it to the server there and we'll see that one it has a very long file name which I'm actually going to clean this up because that's going to be very tedious to type in my code so I could either right click of of course up here but I'm going to use my Linux command so let's move this file called cs50 2022 something or other and let's just name it more simply favorites.csv so all lowercase no spaces sort of good basics and let me go ahead now and open up this file with code favorites.csv I'll close my file explorer and we'll see exactly the same data as before but not quite as pretty as Google Sheets makes it be rather we see here that I still have three columns timestamp language problem and then all of the values down below including the timestamps and the answers therefore but it doesn't have proper columns it just has commas separating them now we could very easily write python code just like we wrote C code to manipulate files like this either to write or read but instead let's do something that's a little more pleasant which is indeed in the form of python so python actually comes with Native support for csvs it has indeed a package called CSV that just lets you read and write and do a whole bunch of useful stuff when it comes to CSV files so let's go ahead and do something with this file let me go back here to vs code I'm going to close favorites.csv for now but just remember in your mind that timestamp was the first column language was the second column and problem was the third third and notice because we're using commas they don't again line up perfectly but that's not a problem there are two commas in every line presumably and I'm going to go ahead and now create a file called how about favorites dot Pi so that I can start writing some code to manipulate this data and let's do something simple let's just write a simple program in Python that opens this file reads it and prints something out just as like a safety check that I know what I'm doing even though it's not going to be useful so in Python if you want CSV support you import CSV and that gives you access to all the magical capabilities thereof let me now go ahead and use this technique to open a file in Python which is similar in C but with python we're going to do this the keyword with I'm going to open a file called favorites.csv which was the shorter name I gave it this is optional but just for explicitness I'm going to open it in read mode explicitly just like f open took a second argument as well and I'm going to name this file once opened quite simply file though I could call it anything I want and now it's just an OP open file so far as python knows at this moment it's just text or better yet it's just zeros and ones if you want this python package called CSV it's actually do something useful with it you have to load this file now into the library and the simplest way to do this is to give myself like a variable called reader because I want to read this file though this too I could call anything else I'm going to then set that equal to the return value of a function called CSV dot reader and I pass to that per the documentation the open file so step one I open the file and this just gives me access to the bytes therein step two now with csv.reader tells the python package called CSV to do something useful with it and start analyzing the commas and allow me to parse it further so let's go ahead and do this let me go ahead now and within this Loop let's say this with sorry within this open file let's do this for every row if you will or line in the file AKA reader here let's go ahead and print out just how about Row Bracket one now what's going on here Well turns out if you read the documentation for the CSV reader function what it hands you back is essentially this special object so to speak that allows you to treat it as though it's just a really big list of lines from the file AKA reader so by saying four row in reader this is a way more succinct way of saying give me the first line in the file plus plus give me the second line in the file plus plus and so forth that we would have done what much more mechanically and C this is just much more pythonic and English friendly if you will so and every iteration of this Loop row is going to contain all of the data from the current row but better yet what the reader function does for me is it hands me each row not just as a big string or Stir of text in Python it gives me what apparently based on the syntax on line six any Instinct yeah it's giving me back indeed a list and I'm I presume the visual clue for you was the fact that we're using square brackets here and indeed Row Bracket one is going to be not the first but the second element in that list and so just take a guess when I run this code in a moment What's going to get printed the time stamp the language or the problem the yeah the language because it's the second column that is in the file delimited by those those commas so let me go ahead and do this let me clear my terminal down here let me run python of favorites.pi and enter and there's everything it was super fast but there's a really long list here and in fact if I increase the size of my terminal and start scrolling up you'll just see all of the raw data now this isn't that useful yet I could have just glanced at the CSV but clearly now I have the ability to open the file parse it so to speak that is break it up into its constituent parts and do something with specific Parts they're in all right so if I want to do this a little more pleasantly though let me at least make this semantically a little cleaner and you know what just for clarity let me just give myself a variable it's not strictly necessary but I know that this is the favorite uh for instance language so let's just call it favorite set it equal to Row Bracket one and now just to be more explicit in my code even though again we don't need the variable per se this code is of course going to do the same thing it's just using an additional variable called favorite if I go down here scroll up run the program again I get back to the exact same data but this is a stepping stone to something that's even more powerful about python support for CSV files is that you don't have to just treat the return value as a list with 0 and 1 and 2. so just thinking intuitively here why is this maybe not the best design to hand you the programmer back the data in a list that's numerically indexed with 0 1 2. it clearly works but critique this what could go wrong what's a little poorly designed yeah exactly so if yeah so it's up to you to repeat it it's up to you to remember like what column the data is actually in and you know God forbid you're you're collaborating with someone else on the spreadsheet you know you've if you've used Google spreadsheets you can move columns around maybe just because you want to visually reorganize things and if you do this and then someone else downloads that same data all of their code is going to break so that's just really bad design it's fragile just because it's you're sort of on the honor System that one means the data that you want so wouldn't it be nice if we could be a little more explicit well recall that the very first line in this file is actually this and I paused the output this time so that we can see more optionally I just re-ran favorite stop high and notice one of these things is not like the other every output was either scratch or C or python except for this first one why am I seeing the word language here where did language come from you didn't have the ability to manual input oh no where did it come from yeah yeah the header the very first row in the file which by human convention generally just defines what the columns represent so that there's some human useful information there now that's not really intended to be part of my output at the moment so there is a way to skip this if you want to skip the first row you can actually do something like this you can say next row and that will just ignore that row so that I'm starting really with the every row thereafter but there's a better way to handle this than that that will get rid of the row and the output but let me go ahead and use a different feature of the CSV package that's just going to make this a little cleaner altogether so let me clear my terminal window here let me undo this next thing that I just added and instead of using a reader let me go ahead and use a dictionary reader abbreviated addict reader that's going to now return me the equivalent of all of the rows one at a time so I can still call it Reader just as before but as the name implies what this reader is going to return is not a list after list after list but a dictionary a dictionary a dictionary and remember a dictionary is just a collection of key value pairs so what does that mean what are the keys what are the values well one now that I'm using a dictionary reader I can actually do this instead of sort of on the honor System remembering that I want column one I can treat row now not as a list but as a dictionary and that means I can go in here and say quote unquote language and we saw that back in week six python allows you to index into dictionaries using square bracket notation in strings or stirs on the inside just like lists allow for numbers but this now I think is going to be a little more robust if I run this again python of favorites.pi all of that worked out fine and let me pause the output 2 by using this program called more now I don't even see the header so now whoever works it uh with python wrote the code for this package to just analyze that first line of code use the header as you just called it as the keys and then every time you iterate through this Loop it updates the values the values the values but the keys stay the same any questions then on this technique suffice it to say this would be painful in C yes foreign exactly so the keys are always going to be quote unquote time stamp uh Pro language and problem but on each iteration of this Loop here the row is going to contain a different row of values different row values different row values so you're going to get back one dictionary for every student who submitted the Google form if you will while iterating through it there all right so once we have this ability here why don't we go ahead and transition to how about not just using that dictionary reader which makes the code a little more robust because now if you move the columns around no big deal it doesn't matter if the numeric indices change you can still use those keywords instead but let's actually analyze the data now I'm just spitting it out which is not solving any problems for anyone so let's go ahead and count the popularity of scratch C and Python and see what everyone's uh been thinking here all right so how might I do this well let me go ahead and do this up here before I start iterating let me give myself let's say three variables and to keep things simple I'll say one variable called scratch set it equal to zero for zero students so far C is going to equal zero and python is going to equal zero there's a slightly prettier way of doing this just because this is like three lines of code to do something very simple you could alternatively in Python but not C do scratch comma C comma python equals zero comma zero so kind of slightly more elegant just to fit it all into one line but now let's just do something more interesting on line seven I'm still going to figure out what the current favorite language is and now I'm just going to do some conditional checks How about if that favorite equals equals quote unquote scratch let's go ahead and increment Scratch by one we can't do plus plus in Python but we can do plus equals one how about L if favorite equals equals c then let's do c plus equals one L and not we could do else this is actually a good design question should I do else should I do L if any instincts here yeah yeah really good instincts just in case someone goes and adds another language to The Forum next week because we're obviously going to introduce another language today you don't want your code to now artificially inflate the scores for python just because you're conflating multiple languages together so the more defensive sort of better way to write this code I agree would be L if favorite equals equals python then let's go ahead and increment python plus equals one and if there's a new language next week we're obviously going to have to update the code but at least we're not miscounting we're just missing the new language so I think that's slightly more robust all right now at the very bottom of this program and outside of the loop when I'm all done counting let me go ahead and print out using some f strings how about the total number of people who uh whose favorite is scratch so this is just uh week six F string syntax let me go ahead and print out another F string for C and I'm of course putting the variables in curly braces all lower case but the English words I'm doing capitalization for let's student final one with f python colon and then in curly braces python close quote and I think I'm done so let me just hide my terminal for a second here's the total program same stuff as before open favorites.csv open it further with the dictionary reader to do that processing for us initialize three variables to zero just so we have something to count with and then iterate over the file row by row and this is just some sort of week one style conditional logic albeit in Python counting things all right so how can we now execute this let me go back to my terminal python of favorites dot pi and here we go as of today everyone who's reporting in live via the Google form their favorite languages are interesting that's pretty interesting too after just one week of python no less so but scratch is a healthy Contender there a lot of C so a pretty good mix here so is this going to be the best way to write this program long term well as you noted if there's a new language next week this week we're going to have to constantly update this and here's where you should let your mind wander to like the future like if we have a fourth language fifth language sixth seventh eighth which aspect here might kind of have some code smell to it like this probably isn't the best design to set us up for the future what might be better than this yeah yeah we have to keep adding a language to line five and okay not a big deal we could add like SQL today and maybe JavaScript next week but you know anytime a line of code a line of logic is just going to kind of grow out of control when we've had this chat a couple of times with different syntax there's probably a better way than that so let's do that instead of using these individual variables we could maybe use a list but a list would be a little confusing because like what does get zero mean what is bracket one bracket two but a dictionary recall is like this Swiss army knife of data structures whereby you can associate anything with anything else keys with values so I dare say a cleaner way to solve this problem that sets us up for Less work or confusion later would be to create like a new variable called counts if that's what we're doing counting things up and just set it equal to an empty dictionary and you can literally say dict with the open parenthesis close parenthesis nothing or the more pythonic just use open and close curly braces with nothing inside that gives me an empty dictionary just like square brackets gives me a list now my logic down here has to change a little bit but what's nice is I don't need one conditional for every language because again if we have a fourth a fifth a sixth that chunk of code is also going to grow a bit out of control too so I can get rid of this here and what I think I'm going to do is say this whatever the current favorite is from the current Row in the file why don't we go into our counts variable at that key and again favorite is a variable it's not quote unquote favorite it's going to be scratch or C or Python and then why don't we go ahead and just increment whatever the value of that count is at that key now this is technically buggy we're really close but there is a bug does anyone want to conjecture what the bug is yeah a good question that answers my question in uh nonetheless so no like the magic you describe will not happen and to repeat the the hypothesis will this automatically create a key for every uh language that we try plugging into those square brackets short answer no odds are this is going to create a key error one of those traceback error messages that you've probably seen by now either in class or in problem sets whereby if scratch hasn't appeared in the dictionary before or C or python like then the dictionary has no clue what you're talking about so I think we actually still need some conditional logic but not that's going to grow longer and longer with each language what I think we probably want to do is this if the current favorite is in the counts dictionary and this is the pythonic way of just saying is this key in this dictionary then go ahead and safely do counts favorite plus equals one else to your conjecture now else what do I want to do counts favorites equals yeah one so initialize a brand new key to a brand new value of one because I'm obviously just seeing this language otherwise increment again and again and now down here I just need to tweak my syntax a little bit I don't need to print out all of these things one at a time manually I can actually get away I think with one another loop at the very bottom here so how about I do this for each favorite in those counts and this is again the pythonic way to iterate over all of the keys in a dictionary go ahead and print out using an F string whatever the current favorite is scratch or C or Python and then a colon and then figure out what its count is and you can do that by going into the counts dictionary looking at the favorite key and get back its value so I close my curly braces I close my quotes and even though this looks ugly at the moment now this is much more dynamic because if we go and add SQL to the CSV file tomorrow or we add JavaScript next week this will just work it will keep working now automatically all I change is the Google form not my actual code all right let's try python a favorites.pi cross my fingers as always and there now is the data as of now questions on this code here yeah really good question what if you wanted to print it in a particular order uh well I could give you a couple of solutions like if you want to print it out in it's already coincidentally in alphabetical order so you got that for free although that's just by chance here but there is a way to do this and let me propose that we go down here to my Loop and I explicitly use a function you might not have seen in Python yet but it's literally called sorted which is going to take either a list or in this case a dictionary and by default sort it by key alphabetically now if my intuition is correct this is not going to change the output because it's already alphabetical but if you read the documentation for the sorted function it takes multiple parameters potentially some of which are named parameters and so you can actually do this if you want to sort the counts but you want to reverse the order for whatever reason here so that it's reverse alphabetical order now let me go ahead and rerun this and I'll keep the previous output on the screen enter and now it's backwards alphabetically if you will other questions on this here no how about then how about then we transition to changing sorting by value and let me this is going to escalate a little quickly briefly but then we'll we'll tone it down again notice that right now this is indeed sorting by key what if especially if I have lots of data it'd be nice to make like a top 10 list or in this case a top three list and actually see in order of the counts the values uh what these popular ones are so it's not C python scratch it should ideally be python then C then scratch because of the values and the magnitude thereof so how can I do this well it turns out there's another key another parameter that you can pass to the sorted function that is typically implemented as a function itself and so I'm going to go ahead and do this I'm going to temporarily Define a function called get value just to make my life easier and this get value function is going to take I'll say a language parameter and then all I'm going to do is return whatever the count is of that language so out of context this is just a super simple function that that you handed a language like scratch or C or python it's just going to tell you what the count is thereof in that or in that dictionary called counts but what I can do now down here in my newly introduced call to sorted is I can tell it what to use as its key instead of using literally the key scratch C python I can sort of override that behavior and say you know what to figure out what to sort by go ahead and call this function called get value notice that I have not put parentheses after get value because I don't want to call get value right then and there I want to pass the get value function as itself in argument to the sorted function so that the sorted function written years ago by the people at python can call my version of get value again and again and again when they try to sort this actual data so now if I add that and I leave reverse equals true let's see what happens python of favorites.com I enter and now I get my top 10 or in this case top three list and if I had more sophisticated data with like more columns altogether that I actually care about I could even sort this more powerfully as well but let me clean this up a little bit just so you've seen it even though we won't use these that often in cs50 until the end of the class will they come up again technically this is a little bit this isn't necessarily the best design to spend all this time implementing a function and then only use it in one place in general we've argued that you don't necessarily need a variable if you're only going to use it in one place you don't really need a function if you're only going to use it in one place and here we kind of have a good candidate for that and so it turns out in Python if you don't want to bother creating a function just to use it once you can create what's called an anonymous function AKA a Lambda function like the Lambda symbol familiar and a Lambda function the syntax is a little strange looking but you say this you literally say Lambda you literally then say the name of the argument that you want this Anonymous function with no name to take then you have a colon and then quite simply you write what you want the return value of this function to be you don't even say return literally these Lambda functions are meant to be used super tersely so that you can in one line Express something like this and I admit this looks more cryptic I think than the previous version but as you get more comfortable with python or other languages that support this feature it allows you to not bother with lines of code like that and just tighten up your code a little bit so this line here Lambda language colon counts language is the one line version of this and you don't even need to bother picking a name for it Lambda tells python I didn't waste any time thinking of a name for this function so questions then on this technique of using python to analyze data like this any questions we're almost done with python questions no okay so why don't we make things a little more interesting because we had a much juicier data set with the problems that we've assigned over the past several weeks why don't we go ahead and quite simply you know I think we wrote pretty darn good code here so I think we can pretty much just change a bit of it to say let's see if I don't want language I want problem and if I want to sort by not language but problem I think that's it I think if I didn't Overlook something here just by changing what column I'm reading the data from and then just to be consistent renaming my variables just so I know what I'm looking at what will this program Now do after those minor changes what will I see when I run this what would be the first thing I see when I run this tough crowd today yes yeah and the problem the top problem so the most popular problem which I'm a little worried it might be hello or just scratch but let's go ahead and see so let me go ahead and open my terminal window I'll even maximize my terminal window so we can see a lot let me go ahead and run python of favorites.pi I'm going to go ahead now and cross my fingers that I didn't mess up and hit enter and okay great we peaked early so scratch was the most popular program according to the data at the time I downloaded it I'm sure other votes have come in since filter uh in week four was tied then with tiedemann as well Mario is a close third there and so forth so this is helpful for us on staff that's not so much love down here at the bottom of the list so it was a bunch of code to write but now that we've written it in this very versatile Dynamic way it's pretty good for just like crunching data and doing some analytics but it's still a decent number of lines to have had to write manually and this is where sometimes it isn't necessarily the right tool for the job but rather the job that but rather a candidate for using some other language altogether especially when it's not just a one-time program that you run and you want to see the answer what if you want to take input from the user and ask answer questions dynamically like a mobile app would like a website would like Microsoft Excel or Apple Numbers your Google Sheets would for you well let's make one final change for now to this version of the program and actually take in some user input so besides just loading all of the data into memory let's go ahead and down below here not just print out the top 10 list if you will but prompt the user for their favorites I'm going to use Python's input function and I'm just going to prompt them with favorite quote unquote like tell me what your favorite problem what problem uh rather you are interested in and now let me go ahead and say if that favorite is in the counts variable so you didn't type in something random that we didn't actually assign as a problem then and let me go ahead and print with a format string whatever that favorite is of yours and show you the actual popularity thereof by indexing into counts using that favorite as the key and printing this so now it's a dynamic program it doesn't dump all of the data in all of the summations rather it's going to allow me to see what my choice of favorite is and I'm going to go ahead and say uh let's see I'm a fan of Mario here so enter and indeed we see the same value we saw a moment ago but just for Mario but the point now is that one all of this is possible two it's way easier and more pleasant than this would have been in C this is still only 15 lines of code and see again there's the memory management there's the iterating over the strings trying to find the commas there's just a lot more work but honestly even when you just want to answer a question like this in Excel in Apple Numbers Google Sheets you know generally you can just highlight things you can click a button and boom you get your answer for summation or Max or Min or any of those sort of Basics wouldn't it be nice if we we weren't taking a step backwards as programmers and being sort of more powerful and yet we now have to do more of the work so maybe sometimes Python's not or any language is not the best tool for the job and that's going to now allow us to introduce more generally something called a relational database graduating from Mere flat file databases like text files or binary files in which all of your data is stored to something more proper but first questions really good question to reiterate if I were to is this case sensitive so if I were to type in Mario in all lowercase and hit enter I actually get no such response now that might be acceptable because the problem technically what is a capital M but that's a little uh ridiculous to be that pedantic about the input so how could we solve this any tips for how we can make this a little more robust yeah okay yeah yeah so we could use a few different functions one of which is called title which will change it to title case where capitalizes like in most English sentences the first letter of that sentence we could use capitalize we could use upper we could use lower but indeed we could just decide how we want to standardize the capitalization either uppercase lowercase or some combination thereof and just make sure that you change the counts themselves make sure that you do the same to favorite and make sure that maybe you keep a backup of the data if you want to show the original version that came from the CSV without presuming to just capitalize everything for the user but indeed that would be the most common scenario you just make things case insensitive when doing those matches other questions now on python before we leave it behind for the coming week all right well then let's introduce these relational databases so relational database is what like every is a super popular way of storing lots of data like this is what the twitters of the world the Googles of the world the metas of the world used to store some of their data at scale there are alternatives to relational databases um indeed today we'll talk about a language called SQL there's also a movement if you will or an alternative generally called nosql which is just the opposite you don't use SQL there are things called object oriented databases and the like but if you've ever heard of MySQL or postgres SQL or Microsoft SQL server or Oracle or mariadb or a bunch of other products both free and Commercial this is what they're talking about databases that are designed to store lots of data and what's nice about relational databases is that they're really similar to the spreadsheets with which you were presumably familiar long before today's class so a relational database is going to store as you'll see all of the data in rows and columns now the terminology will very after be a little different instead of having sheets you're going to have tables but those tables are still going to have rows and columns and you're going to have even more control over the performance of your data when you start to access it using this structured query language or SQL this is a language you can use for web apps mobile apps a lot of analysts would sit down at their Mac or PC and actually ask questions of data to get back the answer and wonderfully even though there will be some new syntax today SQL really just does four basic things crud is the sort of crude acronym here crud is a way of remembering that a relational database supports ultimately creating data reading data updating data and deleting data so even if you're feeling like wow this is a lot of new syntax which it isn't relative to our past languages the only things you're doing really are creating data reading data updating and deleting the same now a little confusingly in SQL the corresponding functions or command funds that exist that map to crud are actually this so it's still create but there's another one called insert it's not read which is more of the computer scientist way of saying it but select which is a little more explicit like select data you care about update is still update delete is still delete but there's another command called drop which lets you drop that is delete entire tables as well so you can create tables using syntax that's generally going to look like this you'll say create table you'll give the name of the table which you can call most anything you want but generally all lower case no spaces is best then in parentheses you can specify a comma separated list of the columns that you might want in this table so this is the code equivalent in the SQL language of like manually opening Google Sheets or Excel or numbers and like clicking in the top left cell and like typing timestamp and then in the next typing language and then the third typing problem this is the way to sort of Define what your headers are if you will in a spreadsheet but now it's called a table now we won't use this command manually first let's do something a little simpler we're going to start off by just importing this data ourselves and I'm going to go ahead and do this let me go back to vs code here I'm going to leave behind favorites.pi for now because now we're going to transition to this other language called SQL and to do this I'm going to create 8 a new database file and I'm going to do so using a command called SQL lite3 which is just the third version thereof and I'm going to give the database a name of favorites.db there's different conventions but this is one of the most common when I hit enter this is going to create for me a new empty database just like opening an Untitled spreadsheet in Excel Google Sheets or Apple Numbers I'm being prompted do I want to create favorites.db I'll hit y for yes okay we're up and running now you're going to notice a different prompt I'm not in my Linux prompt per se which is always the dollar sign I'm now inside of the program called SQL Lite and we're going to use SQL Lite sqlite3 it's just an interactive way for now of playing with SQL code at the end of today we'll show you how you can use SQL in Python code so that you still write python code to do whatever you want but you can talk to databases using Python and this is exactly how web apps mobile apps work for instance on iOS on an iPhone an iPad or the like if you want to store data it's very often stored in a SQL database as we're about to do but you might use a language called Swift or objective c and same exists in the world of Android using Java or kotlin or something else to query the database so we're going to see SQL in isolation for now like an analyst might just use it their Mac or PC but we're going to tie it together by day's end so at this terminal SQL Lite let me go ahead and execute this command first I'm going to first put SQL Lite into CSV mode because I'm going to cut some Corners initially and I'm just going to automatically import all of the data that was submitted via that Google form which I exported as a CSV and uploaded to my code space and I'm just going to automatically say turn this CSV file into a SQL database for me just so I don't have to figure out what those create table commands are so to do this I'm going to say mode CSV so that SQL Lite knows that this is the command knows that this is a CSV file it's literally dot mode so the DOT comes before the keyword there and now I'm going to say dot import and then the name of the file I want to import which is favorites.csv and now the name of the table that I want to create with that data and just for consistency I'm going to call it favorites I could change these things to be anything I want but I'm going to do that and voila nothing seems to have happened but just like in C and in Python and Linux when nothing seems to happen that's usually a good thing it means I didn't mess up so if I want to see what just happened there's this other command and these commands that start with dots these are SQL light specific which is indeed a lightweight version of SQL they're not SQL per se so if you're using Oracle or something else like that you're not going to use these exact commands you'll see the ones we use in just a moment and here's the first when I type dot schema the schema of a database is the design of the database what are the tables what are the columns and all of that so when I type dot schema this actually in this case shows me the create table command that was automatically run for me by just doing this import line once I get more comfortable with SQL I could literally type this out myself or use some program to generate that as well but what it's creating for me is this create table if it doesn't exist even though it's more terse than that I want to create a table called favorites and then the columns for that table are going to be time stamp which is going to be text comma language which is also going to be text comma problem which is also going to be text that was just inferred very trivially by the dot import command to just figure out that yes just give me a three column database table based on the Google form okay questions on this these are commands you run once to get up and running you don't run these commands frequently but we have them on the slide just for reference all right so now let's do something a little more interesting I'm going to clear my sqlite terminal here but I'm still in SQL light I'm going to now use some of my first SQL commands which recall were uh were among them uh select so crud c-r-u-d the r with select this is maybe the most common the most useful the most powerful thing to use with a SQL database selecting data to answer questions akin to the ones we were trying to enter with python this is the general syntax anytime you want to select data from a SQL database you literally say select you then specify the column or columns that you want to select data from you literally write the word from and then you specify the name of the table you want to get that data from semicolon in this case everything that's in capitals here is a SQL keyword strictly speaking you don't have to capitalize things but we would encourage you to do so stylistically and especially as you're learning and even as you're writing it it just helps to distinguish SQL from like words you chose like the names of the columns and the data they're in so do adopt early on this convention so let me go back now to my code space here I'm running my terminal window with SQL lite3 inside of it suppose that I just want to get all of the data from the favorites table which was automatically imported let's do this select I want everything well I can do timestamp comma language comma problem but you know what here's a convenience already if you want everything there's what's called a wild card character in SQL which is just a star an asterisk which means give me every column without my knowing even what they're called let me go ahead now and say from favorites semicolon and this is the SQL way of opening the database iterating over every row they're in printing out every row they're in done so those three steps which was like nine lines of python code give or take earlier is now one line of SQL I hit enter there is all of the data so I see now all of the data just outputted as a CSV here but it's not the CSV file it's now actually the table and in fact just for good measure let me do this because you'll see the behavior a little differently the next time we open the file I've just exited out of sqlite3 I'm going to rerun it but I'm not going to re-import the data or do anything like that because my file now exists in fact let me take one step back if I type LS at my Linux prompt there's my favorite stop PI from before there's my favorites.csv from before and here's a third file that I did create a moment ago when I first Ran sqlite3 So the data is persistent it's not using Ram or memory anything I do now is save there so let's go ahead and rerun sqlite3 with the same file but I'm not gonna I don't have to re-import everything because the file already exists let me now do that same thing again select star from favorites to get all of the data and what you'll see now is the same data but it's a little prettier now because I re-ran it I effectively disabled CSV mode this time and what I'm now seeing is the entire contents of this database table called favorites now there's nothing new here but you're just seeing now like in ASCII or Unicode version of all of the same data from that database well suppose I want to get a subset of the data well let me clear my screen and just like in Linux I can hit Ctrl L just to clean things up aesthetically suppose I want to get just the languages so I could do select language from favorites and this will now select not all three columns AKA star this will only select the language column and all of the data they're in if I hit enter voila now I just see those there no time Stamps no problems it's just a slice of the table if you will all right not that interesting still because it's just a big column of data but now things get more interesting it turns out in SQL that there are functions that come with this language just like C just like python in SQL some of the more useful ones some of the simpler ones are these here average count distinct lower maximum upper which pretty much uh do what they say and count is a particularly useful one let's start with that you know it's a reasonable question to be asked uh how many people submitted the Google form by the time I actually downloaded the CSV well why don't we go ahead and do this let me go back to vs code here in my terminal window let me select not star but the count of star so give me the count of the rows that are being returned from the database called the database table called favorites now when I hit enter I'm not going to get all the data I'm just going to get simply a number 430 rows came back so that's pretty good I now know how much data is in there well what languages were in there well I could do select language from favorites just as before but that's not that useful especially if I'm inheriting the data like I'm the analyst who's been handed to data set by my boss and they want me to like crunch some numbers okay I could like load this into Excel I could sort it but you can use SQL now to answer pretty basic questions too if you want to select the distinct languages in the data set because you didn't you weren't privy to the Google form let me go ahead and select only the distinct languages from the favorites table and now I hit enter and I get back a much more succinct answer just the three languages in question not really that useful since I created the Google form but certainly if you're inheriting data from someone else you've just downloaded a data set at least now I'm arguably wrapping my mind around what's going on now this is not necessary for such a small data set but I can combine these things select the count of the distinct languages in this data set called favorites and now I should get back what answer so hopefully indeed an answer called three and what you're getting back notice aesthetically too is a mini temporary table when I asked for uh just the distinct languages what SQL hands me back is this temporary table in memory that has one column called language and then two Row three rows now this is not saved anywhere it's just executed ephemerally like this but that's why it's depicted in this way what you're getting is subsets of your data smaller tables containing some of your data and same thing down here this is like a crazy long column name you can rename it if you really want but that's all we're seeing there and in fact if that's a little ugly we can actually Alias These Things N is a common name for a variable a number in any programming language so I can actually Alias this to be a column called n hit enter and now I'm getting a tiny tiny table whose column is called n that just has the one value there all right questions on these application of these functions here questions yeah say a little louder oh A S as literally in English so name this column rename this column as this technically it creates an alias for the column so that's all yeah exactly distinct will operate on whatever you handed in parentheses and get rid of all of the duplica