From the course: Learning SQL Programming

Exploring DB Browser for SQLite - SQL Tutorial

From the course: Learning SQL Programming

Exploring DB Browser for SQLite

- [Instructor] When we work with SQL, we need some kind of tool that lets us write 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 the software someone might use to write SQL, I've chosen an application that works on all major 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 SQLitebrowser.org/dl. Download the file and install it according to the instructions for your platform. If you're using Windows, chances are you'll want this one, the installer for the 64-bit version. If you're using Windows, but you're not able to install software on the computer that you're using, you can try out the zip version or the standalone version of the software instead. On a Mac, this is the one you want. And if you're using Linux, you can find instructions for your system down below. Install the software and then open it up. I already have the exercise files downloaded here on my system. So if you don't have them, be sure to download them and unzip them if need be. After downloading and unpacking the exercise files, find the open database option in the toolbar, and browse to where you downloaded the exercise files. Open up that folder and browse to the SQLite folder and then find the quiz data file. Click open. And that will open the database here in DB Browser. Let's take a quick tour of the software. Here under the database structure tab, we see the database structure. It's a pretty simple database with a couple of table. We'll get more into those details as we explore the data in a little bit. Here, you can see the create statement for the tables. We can browse through the data in each table using 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. In this version of DB Browser, it's under edit and then preferences. In the SQL section, there's options for the SQL editor font size and log size. I'll set these to something larger. You can pick something that looks good to you, or you can leave it set as is. I'll try 16. And I'll change the color of strings that we type from this really bright red to a more muted orange. That's just a preference and you don't need to do that. And you don't need to change the font size if you don't want to either. These changes just help things look a little nicer here on my screen. I'll click save and go back to the editor. Now, I'll write a very quick SQL statement here in the execute SQL tab to check things out. I'll explain the statement later. But for now, this will show us how some other parts of the interface work. Throughout the course, we'll be writing SQL statements and running them. So to run the statement, I'll either click the play button up 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 on a Mac, you can press command + R or command + return to run the statement too. Here's the result of our query. Again, don't worry about exactly what this is right now. We'll explore it later on. Down here at the bottom of the window is a status message back from the database server about what happened. This is useful for troubleshooting. And it's good to keep an eye on this to see what the database did when we asked it to run a statement. Over here on the right side, are some panels we can get rid of if we want to. Click the X button to send them away, or pop the panel out so it floats on its own. You can click the icon with the two overlapping boxes, and you can put it back by dragging. To toggle these side panels, we can also go to the view menu and choose them from the list. I'm going to get rid of all of these by going down to window layout and choosing simplify window layout. I want to draw attention to one panel you might want to use, the SQL log. It keeps a record of every command sent to the database for this project. I'll make sure I'm looking at the user section. So I have a running history of everything that I've typed. This is useful because if I go over here and type another statement in the query window, and run it, all of the statements in this query box run. So if I just wanted to run one statement, I would need to erase what I had before, and then run my single query. You can also select one query by painting it with the mouse and run it just by itself. But the log gives us a running history. So if you delete a statement that you ran in the query window, it's not gone forever. You can copy and paste from the log back into the query window. The DB Browser for SQL Lite software is focused on writing SQL and managing one type of database, SQL Lite, which is popular for storing data in mobile and desktop applications. So there's all kinds of features in the software that we won't be using in this course. But I really like the 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 the software because it's 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.

Contents