[SQL] [CHRISTOPHER BARTHOLOMEW] [HARVARD UNIVERSITY] [THIS IS CS50] [CS50 TV] So, programmers use databases as a tool to store and organize  our persistent data within tables. This means that your data is stored on non-volatile medians such as a hard drive, and even when the computer is off, the data is still intact. And, as programmers, our programs utilize databases for scenarios such as storing user information from web forms, looking up inventory,  or updating information about what sites display. But how do we actually interact with our database, or what do we use to read, store, delete,  and update our data within these database tables? Well, the answer is that we use a special type of database language which interacts directly with our database tables. It's name is Structured Query Language, [Structured Query Language] or what I refer to as SQL. 

Now, [Se-QueL], or SQL, isn't a programming language,  but instead, it's a language that provides a standard set of commands to retrieve and manipulate data from a variety of database management systems. For the purpose of CS50, we'll go over four basic commands: select, insert, update, and delete. Furthermore, we'll utilize a database web interface called phpMyAdmin, which installed on the appliance to write our SQL statements. So, to help you remember these commands,  I've brought some cupcakes in a cupboard to assist with our scenarios. Say you have a cupcake's database,  where you store all information about your cupcakes. Now, databases can contain many tables  and the tables themselves can contain many columns. Inside our cupcake's database, we have a table called cupcake_cupboard. This table will be used to store all information about cupcakes  that are, well, in your cupboard. The columns that are inside your table represent the attributes of a cupcake. For example, the cupcake_cupboard's columns are  Cake ID, CakeType, CakeColor, and CakeIsNice. This boolean-typed column is used to determine if the cake is nice or not nice. We're going to start with writing a select statement.  Select statements are used to retrieve data of a particular database table. In this scenario, we want to know everything  about all cupcakes that exist in our cupboard. The syntax to do this is "Select" space star, or *, space from space our table,  which is cupcake_cupboard. Let's go ahead and execute that. 

As we can see, these are all the cupcakes in our cupboard. It's important to note that *, or asterisk, is a wild card character  that denotes the gathering of all the columns of some table. Normally, we can access a specific column or columns  by replacing * with the actual column name. If we want multiple columns, but not all, we can achieve this  by writing the column names delimiting each column by a comma. For example, let's only retrieve the CakeId and CakeType in the cupcake_cupboard table. The syntax to do this is: SELECT space CakeID comma  CakeType space from our table, cupcake_cupboard. Let's go ahead and execute this. And here, we now only have the two columns we specified  for each cupcake in our cupboard. We can also refine our query results by specifying  the "Where" clause just after the table name. For example, there appears to be a cupcake in our cupboard  that is, well, not so nice looking. Let's figure out all the cupcakes in our cupboard that are, well, not so nice looking using the "Where" clause. The syntax to do this is: SELECT space * space FROM space cupcake_cupboard  space WHERE space our conditional column, in this case CakeIsNice =, and the boolean value of False. It is important to note that if you are using strings, you must enclose it in single quotes. This is true for all strings in SQL, or, in SQL database terms, variant characters known as Varchar datatype. In this case, we are using True or False, which is a boolean value and not a string. Let's go ahead and execute this command. 

Lo and behold, it is the case that we have 1 chocolate  not so nice cupcake in our cupboard. Next, we're going to write an insert statement.  Insert statements are used to insert or add  additional rows of data into your database table.  Revisiting our scenario, let's assume that we've just made a brand new cupcake. Since we are very organized cupcake eaters, we're going to have to insert this new cupcake in our cupcake_cupboard table. The syntax to do this is this: Insert space into space our table,  cupcake_cupboard, space ( , and here we specify the columns names, delimited by a comma, CakeType comma CakeColor comma CakeIsNice) space. Following this, we write the word VALUES space (, and here we enter the value for each column respectively, also delimited by a comma. Single quote, because they are all Varchar values we'll surround them in single quotes, PEANUT BUTTER' comma  'LIGHT BROWN' comma True. Now, it's important to give each row a unique number to identify itself. Autoincrementing a column provides this as   "No two the same IDs can ever exist in this table." Let's go ahead and execute. There we go, all organized. 

The next SQL statement that we will write is an update statement. The update statement can be used to modify data within a column  for any existing row in your database table. Earlier in our scenario, using a select statement, we identified a cupcake  within our cupcake_cupboard's table whose CakeIsNice value was False. Let's assume that while our peanut butter cupcake was in the oven,  we made our not-so-nice cupcake very nice. Being so exceptionally organized, we want our cupcake to reflect this value in our cupcake_cupboard table. Therefore, let's update our chocolate cupcake in the database to reflect this. The syntax is: UPDATE space our table, cupcake_cupboard, space SET space  the column that we wish to change, CakeIsNice =. Then here we place our new value True space. Now, because we don't want to update all rows with this value,  we want to provide a "Where" clause that will pinpoint us  to the exact row that we want to modify. In this case, we know that there is only one cupcake  that has a CakeIsNice value of False. Furthermore, we will also ensure that we are updating the correct row  by using the "And" clause. We use the "And" clause to refine our query further. In this case, because we know that the cupcake is chocolate,  we will use this CakeType column. WHERE space our conditional column space CakeIsNice space = False,  and space CakeType = 'CHOCOLATE.' So, putting it all together, this update statement says  find all the cupcakes in our cupcake cupboard, and if there is a cupcake whose column CakeIsNice contains the value False and CakeType contains the value CHOCOLATE, we want to update the specific rows CakeIsNice value to True. So, let's go ahead and execute the statement. And now, we're organized. 

All this talk of cupcakes has made me a bit hungry. I think I should help myself to one. But if I actually eat this cupcake, I should at least also remove its existence  from our cupcake_cupboard table. To do this, we will use the "Delete" statement. The "Delete" statement can be used to remove all or some rows from the table. If you wish to remove some specific row from the table, then you must provide a "Where" clause, thus specifying a column  that should be unique to the row that you wish to remove. This is the syntax: DELETE space FROM space our table, cupcake_cupboard, space. Now, at this point, whenever you are this far in your delete statement you want to be very careful. For example, if I want to run this query as is without providing some "Where" clause I would lose all the data in this cupcake_cupboard table, but because I already know that my cake IDs are unique, I'll use  the Red Velvet cake ID for my "Where" clause. WHERE space our column, CakeID = 1. Because this is an integer value there is no need to surround it in single quotes. So, let's execute the statement. Well, now that we've wiped the existence of this cupcake  from our cupcake_cupboard table, we only have one thing left: Make it disappear. I'm Christopher BartholOmew. This is CS50.