Menu Close

Assignment 1: Installation and SQL I

Installing SQLite3

Step 1. Install DB Browser for SQLite. This software is available for free for Windows and Mac. Make sure you read a little about the software while you are doing the install. The most interesting thing I noticed, is even though the database is small, the file is all containing. Each of the tables with their tuples are in one (1) file, which is easy to turn in.

Step 2. Create your first database using the DB Browser for SQLite.

  • You should create a folder (I called my MMIS_6296). This is where all the files for the database will live, so put it somewhere that is easy to get to (Downloads or Desktop).
  • Inside the MMIS_6296 folder create another folder for Assignment_1. This is where your database file will be saved.
  • Finally, let’s create our first database and save it in the Assignment_1 folder. Let’s call the name of the database “assignment_1_lastname” where lastname is your last name.

Step 3. Add a table to your database with at least 10 rows of data.

  • This table will have four attributes or columns.
  • student_id (this is the primary key, and should be auto incremented and not null)
  • first_name (this is a text field)
  • last_name (this is also a text field)
  • height (I thought about this as an integer field, but someone could be a half an inch, so numeric may be more appropriate)

Step 4. Add data to the table. Open the browse data tab and click the “insert new record” button. Make sure you “write changes” when you are finished adding the data. Make sure you add at least 6 people to your table.

  • Kirby Smart (6′ 2″)
  • Dan Lanning (5′ 11″)
  • Bryan Marshall (6′ 5.5″)
  • Your Name (height)
  • Homer Simpson (5′ 9″ 7/10″) official height
  • Bart Simpson (4′ 0″) official height

Hint: You may need to convert the feet and inches to just inches.

What is a Select Statement?

Lets get familiar with select statements. Select statements is the most basic SQL statement.

Step 1. After you finished creating the database, table, adding the records make sure you save the file by clicking the “write changes” button.

Step 2. Browse to the “Execute SQL” tab. Execute the following code by typing it in and pressing the play button.

select * from student;

This code should display all six students in the table as well as all the columns and their names.

Step 3. The third icon is a “Save SQL File” button, click this button and save the file as “select_all”. Save the sql file inside the Assignment_1 folder. Call the file select_all.sql.

Assignment 1 – Final Answer

Turning In Your Assignment

Make sure you save any changes to your SQLite file. Upload the following files to Assignment 1 in GeorgiaView.

  • assignment_1_marshall.db (this would have your last name in it)
  • select_all.sql