- [Instructor] In order to work with SQL, we need some kind of tool that lets us compose a statement and then send that statement to database software that will interpret it and give us a result. There are many different programs and applications that can understand SQL, and often an SQL statement will be embedded inside of the code for an application or program. Because I can't cover all of the software someone might use to write SQL, I've chosen an application that works on all platforms and is free. Many applications that use SQL take a lot of setup, and I don't want to get bogged down with that here at the beginning.
You can follow along with this course on your desktop or laptop computer with a free application for Windows, Mac, and Linux called DB Browser for SQLite. It's available from this page, sqlitebrowser.org. Download and install it according to the instructions for your platform, and then open it up. Then you'll want to download the exercise files from the course page. After downloading and unpacking the exercise files and installing DB Browser, open up DB Browser, then choose File, Open Database, and navigate to your exercise files.
Here there's a file called results.db, or you might see that the Type is Data Base File. Choose that one, and open it up. When it opens up, we have a couple panes of information. In the first tab, we see the Database Structure. It's a pretty simple database with two tables. We'll get more into those details as we explore the data in a little bit. Over here, you can see the CREATE statement for the tables, which also will make sense to you by the end of the course. We can browse the data on this next tab.
And then if we click over to the Execute SQL tab, we have an area where we can write and run SQL statements. I want to make sure my font is large enough, so I'll go into the Preferences, under View, Preferences. In the SQL section, there's options for the SQL editor font size and log size. I'll set these to something larger. I'll try 16.
And then I'll choose OK. I'll write a very quick SQL statement here to check it out. I'll explain the statement later. I'll write SELECT * FROM people. Throughout the course, we'll be writing SQL statements and running them. So to run this statement, I'll either click on the Play button here or press F5 on my keyboard. On a Mac and on some PC laptops with media keys, you may need to hold the Function key to get F5 to work.
And here's the results of our query. Again, don't worry about exactly what this is right now. We'll explore it later on. And down here at the bottom of the window, is a status message back from the database server about what happened. This will be handy for troubleshooting. Over here on the right on the screen, are some boxes we can get rid of if we want to. Click the X here on the right on Windows or on the left on the Mac, and to bring it back, go to the View menu and choose it from the list. I'll get rid of a few of these, but I'll keep this SQL Log one.
It's a record of every command sent to the database. And I'll toggle over to the User submitted section, so I can see the running history of everything that I've typed. That's handy because if I go over here and type another statement in the query window, and then run the command, I can then copy and paste previous commands if I want to run them again.
If you delete a statement that you ran in the query window, it's not gone forever. This software is focused on writing SQL and managing one type of database, called SQLite, which is popular for mobile and desktop apps, so there's all kinds of features here that we won't be using. But I really like this software because it makes it easy to have an offline database on your computer without having to set up developer tools and database servers and all kinds of complicated extra stuff. You may need to do that down the road, but we're not focusing on that right now. I also like this software because it's remarkably consistent across all three major platforms, and it's free.
If you want to use different software, that's fine, but you're on your own for setting that up.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.