Review of SQL

Welcome back, we're about ready to start coding, 0:00 but before we do so, I'd like to touch on an acronym. 0:03 This is programming, after all. 0:06 You didn't think you'd get through a course without more acronyms, did you? 0:08 The one I want to mention is RDBMS, and it stands for 0:11 relational database management system. 0:15 What this is is the actual tool or vendor used as a database provider. 0:18 Now, there are a lot of big names out there such as MySQL and 0:23 PostgreSQL in the open source realm. 0:27 There's Oracle and Microsoft SQL Server in the proprietary realm. 0:30 And there are a couple lightweight options with file-based modes, 0:33 such as SQLite and H2, both of which we'll use in this course. 0:37 It's worth mentioning here that there are some non-relational 0:42 database management systems out there, most notably, MongoDB. 0:45 We'll assume that in this course we're working with relational data. 0:49 That is, we'll assume here that we have a SQL based database that contains a set of 0:53 related tables. 0:59 Let's now hop over to the SQL playground for a review of how to create and 1:01 interact with data using SQL. 1:05 I've created a SQL playground for our use in the first part of this course, and 1:09 that's what I'm in right now. 1:12 People often use the term schema to refer to a database, all its tables, and 1:14 all the columns of those tables. 1:19 In general, schema refers to the structure of the database without regard to 1:21 the actual data that's stored. 1:24 Currently, our database has an empty schema. 1:27 We can see that by clicking on Database Schema and 1:29 noticing the message that says no tables. 1:33 Let's add a table to our currently empty database. 1:36 I'll click on Add a contacts table. 1:39 In here we'll write the SQL to add a contacts table with all the columns that I 1:42 mentioned earlier. 1:46 So we'll start that using the create table syntax. 1:47 I'll call the table contacts and just for 1:51 good formatting purposes, I'm going to list each column on a line of its own. 1:54 The first column is going to be named id, and 1:59 that's going to be our integer primary key. 2:02 And then we have a first name, which is a string, last name, 2:07 which is a string, email, which is a string. 2:11 And then we'll have a phone number, 2:15 I'll just call that phone, that will be an integer. 2:17 And I will only need ten digits to store that, 2:20 this is will be a United States phone number. 2:23 After clicking Run, you should see the contacts table show up in the sidebar. 2:27 And upon clicking on it, 2:32 we'll see the structure that we just created with all five columns. 2:33 Now that we have a table, let's go to the Create new contacts query. 2:37 And in here, I'll add a couple insert statements, so 2:42 that we can get a couple contacts into the contacts table. 2:44 So I'll use my INSERT INTO syntax. 2:48 And specify the name of the table I'd like to insert into, 2:52 which is contacts in this case. 2:54 And then in parentheses I'll list the column names for 2:56 which I will be providing values. 2:59 That'll be (firstname, lastname, email, phone). 3:01 Next I'll specify the values that I'd like to insert. 3:07 And again, for formatting purposes, I'll list the values on lines of their own. 3:11 Remember, if you have a string value, 3:17 don't forget to put that value in single quotes. 3:18 So I'll put my first and last name in here, my email address. 3:24 And then just some old phone number, 3:30 773, we'll go 5554535. 3:35 Excellent, now, so that we have a couple rows to work with, 3:40 I'm gonna add a semicolon here and include a second insert statement. 3:44 I'm simply going to copy and 3:49 paste this statement that I've already typed, and then change the values. 3:51 Let's go, James Gosling. 3:56 And I'll say James@java.com. 4:03 I'm sure that's not his actual email address. 4:06 5555557788. 4:09 Excellent, now we have two insert statements. 4:14 Let's go ahead and click Run and 4:17 then you should see this message that says Query returned no results. 4:19 Now, that's okay because we weren't asking for any data from the table but 4:24 rather, storing data into the table. 4:28 Let's create another query to read, 4:31 or fetch, all the rows from the contacts table. 4:33 So I'm gonna click on this Read all contacts query. 4:36 And into it I'm going to select all columns, 4:39 remember that's what this asterisk means, from the contacts table. 4:43 Let's click Run. 4:47 Excellent, so what you should see down below 4:50 are the data rows returned from the query in this bottom panel. 4:52 And in this case, there are the two rows that we just inserted with our 4:56 insert statements in the other query. 4:59 Now, let's review the update operation in SQL. 5:02 I'm gonna click on the Update a contact query, and 5:05 add an update statement that will update one of the row's first names. 5:08 So here we go, I'll start with the UPDATE syntax UPDATE contacts. 5:14 I'm gonna SET the firstname equal to, 5:20 let's expand my first name to Christopher from Chris. 5:23 And I need to specify some unique identifier for 5:27 the row that I'd like to update, otherwise it's going to update all rows so 5:32 that all first names are going to be Christopher. 5:36 So I will specify which row I'd like to update by including 5:39 a WHERE clause where the id=1. 5:44 So let's run this query, and again, 5:48 we should see this message that says Query returned no results. 5:50 To verify that the update happened successfully, 5:54 I'm gonna go back to the Read all contacts query and run that one again. 5:57 And here in this first column, the firstname column, 6:02 I can verify that my first name was indeed changed to Christopher. 6:04 Okay, we have one final operation to review and that's the delete operation. 6:10 If we want to delete a row form contacts, we simply use the following syntax. 6:15 DELETE FROM, specify the name of the table from which we'd like to delete a row or 6:20 multiple rows. 6:26 And again, make sure you use that WHERE clause so 6:28 you specify which row or rows that you precisely want to delete. 6:31 If you exclude this WHERE clause right here, 6:36 you'll delete all rows from the contacts table. 6:38 So let's go ahead and run this query. 6:41 And see, by going back to the Read all contacts query, 6:44 if I indeed deleted myself. 6:49 Perfect, I'm gone! 6:51 Now what we've just reviewed are called the CRUD actions that every application 6:54 will need in order to expose a full interactive interface to users. 6:59 What I mean by CRUD is create, read, update, and delete. 7:03 Now that databases in SQL are fresh in your minds, 7:10 we'll turn to how to interact with one using Java. 7:12 But first, why don't you answer a couple questions about SQL? 7:16