Installation Guide for sqlite
This guide helps you to set up the software you need for the course "Working with SQL databases". As a database system we use SQLite which has the advantage that it is very easy to install and that a database is simply stored in a single file.
SQLite comes with a console based application where you can perform queries as well as enter and edit data. For this course we use the graphical user interface DB Browser for SQLite.
Install SQLite Graphical User Interface (DB Browser for SQLite)
Select the DB Browser for SQL that matches your version of Windows. After the exe file downloads to your computer run the setup file and follow the simple setup instructions.
Select the DB Browser for SQL for Mac. After the dmg file downloads to your computer run the setup file and drag to your Applications folder.
Download the Sample Database (SQLTW_Sample_DB)
We use a sample database called SQLTW_Sample_DB for this course. Please click the link to download it to your computer. After it downloads, do not open it, just save it to your computer. If you wish you can create a folder for this course "SQLTrainingWheels" and place the downloaded sample database in that folder.
After installing the DB Browser graphical user interface and downloading the sample database SQLTW_Sample_DB. Start the DB Browser for SQLite application.
Mac users, go to Finder and double click DB Browser for SQLite in your Applications folder.
Windows users, go to your Start Menu and click on the DB Browser for SQLite application from your list of installed programs.
You will see the following default screen:
3. With DB Browser opened, click on Open Database highlighted in the image above. Navigate to the folder where you downloaded the sample database SQLTW_Sample_DB and open it.
4. When the file is opened the Database Structure tab shows the tables contained in the sample database. This tab also shows the fields contained in each table. In the account table highlighted in the image above there are three fields named account_number_pk, name and city. The account_number_pk field is an integer type. The name and city fields are text fields.
5. The Browse Data tab can be used to see the records contained in each table. Use the drop down (circled in red above) to switch between tables and brows the data contained in each.
6. The Execute SQL tab is where you write your SQL statements. In the example above "SELECT * FROM account" is an SQL statement. To execute the statement you click on either of the play buttons highlighted above. The play button to the left would execute all SQL statements written in the window (if you had more than one SQL statement). The play button to the right only executes the SQL statement that is highlighted (it executes only one statement at a time).
After executing the statement(s) the result of your SQL statement is displayed in the window below. The example above shows the records are returned after executing the SQL statement.
Under the window that displays the result of your executed SQL statement is another window that displays a summary of the data returned. In the example above it displays "102 rows returned in 4ms from: SELECT * FROM account". This window also displays error messages. If you had incorrect syntax in your SQL statement, this area would display a description of what that is. For example the following incomplete SQL statement: " SELECT * FROM " generates the following error message in this window: " near " ": syntax error: SELECT * FROM ".