6296 Assignment 3 Walkthrough

Assignment 3: SQL II

Step 1. Let’s begin this assignment by opening the DB Browser software and creating a new database called assign03_marshall, where marshall would be your last name. Make sure you save it somewhere you will remember for later. I saved mine in the class folder we created in Assignment 1, but made a new folder for my Assignment 3 files.

Step 2. We are going to create a new single table database. This table is going to be called bank_account. It will have 5 fields (transaction_id PK AI, date, amount, payee, transaction_category). Once the database is created and saved (write changes), let’s add some data. Remember the PK means it will be designated as the primary key (identifier) and the AI means it will auto-increment, so we actually do not need to assign a PK value.

Step 3. Add the data into the table. If you click the second tab “Browse Data” you can add some data. I will let you decide the best way to get this data into your database. You can use an SQL statement or create a csv file with the data in it and then import the file into the database. If you do it by import, you may be able to delete the bank_account table when you do the import call it bank_account, then adjust the data types.

2022-10-14, 450, Beginning Value
2022-10-14, 5466.23, Kroger, Paycheck
2022-10-14, -1800, Jimbo Rentals, Rent Payment
2022-10-14, -313.22, Georgia Power, Electricity
2022-10-14, 500, Birthday Money from Mom, Income
2022-10-14, -259.45, Five-Star Honda, Car Payment
2022-10-14, -235.00, Kroger, Groceries
2022-10-14, -52.00, Kroger, Gas
2022-10-14, -23.15, Freddies, Fast Food
2022-11-14, 5466.23, Kroger, Paycheck
2022-11-14, -1800, Jimbo Rentals, Rent Payment
2022-11-14, -313.22, Georgia Power, Electricity
2022-11-14, -259.45, Five-Star Honda, Car Payment
2022-11-14, -285.00, Kroger, Groceries
2022-11-14, -39.00, Kroger, Gas
2022-11-14, -23.15, Star Theater, Movies
2022-12-14, 5466.23, Kroger, Paycheck
2022-12-14, -1800, Jimbo Rentals, Rent Payment
2022-12-14, -313.22, Georgia Power, Electricity
2022-12-14, -259.45, Five-Star Honda, Car Payment
2022-12-14, -135.00, Kroger, Groceries
2022-12-14, -60.00, Kroger, Gas
2022-12-14, -13.15, Burger King, Fast Food

Here is the sql statement I came up with to import the data that way. It does require quite a bit of putting single quotes everywhere.

INSERT INTO 'bank_account' ('date', 'amount', 'payee', 'transaction_category') VALUES
    ('2022-10-14', '450', 'Beginning Value',''),
    ('2022-10-14', '5466.23', 'Kroger', 'Paycheck'),
    ('2022-10-14', '-1800', 'Jimbo Rentals', 'Rent Payment'),
    ('2022-10-14', '-313.22', 'Georgia Power', 'Electricity');

Of course you could just copy all the data piece by piece into the “browse data” inputter. That may take awhile.

Step 4. Now that we have the data lets create some cool queries. We are going to start pretty easy, but it will get harder.

Query 1. Write a query that shows the total amount of money in the bank account.

(hint: this uses two new things, the sum function and aliases). Be sure to save your sql statements in a single sql document .sql. You will need to turn them all in one document at the end. Hint: As you work in your sql document using /* */ will comment out (instruct the DB engine not to run) your past sql statements so you can keep them organized.

Doing it this way would allow you to keep ALL your sql statements in the same document as you work. Just move the last */ past the last sql statement. The engine will run ALL sql statements not inside the comment brackets.

/* QUERY 1
select ...

QUERY 2
select ...

*/

Let’s do this again, but this time add a where clause.

Query 2. Write a query that shows how much rent was paid in 2022.

This may get a little tricky. You need have 2 criteria in the where clause. When you use more than one you use either AND, NOR, or OR for the comparison between the criteria. You also need to learn a little about using dates in SQLite. At the bottom of that page you will see how they used the strftime function in the where clause. We are not worrying too much about the format of the result, we would typically get this data passed back to a front end where the design folks would work on the look and feel of the result.

For this next couple of queries we are going to look at more conditions in the where clause.

Query 3. Write a query that shows how many transactions had a positive impact on the account (money coming in).

Query 4. Write a query that lists all the payees except “Beginning Value” and the total you have paid to or received from each one ordered by the greatest value.

How did I get the payee column to be capitalized? How do you not include just one payee?

Query 5. Write a NEW query that adds a column to Query 4 that shows the average amount paid or received.

Why is Kroger’s average so low and total amount so high?

Query 6. Write a NEW query that adds the category and splits out the average and total based on the category.

Notice on this last one we limited the number of decimal places on the average and total amount to 2 places. Here is how I did that.

SELECT round(-4.535,2);

Result is -4.54.

You can wrap functions round(sum(number),2).

Step 5. Let’s get this turned in. YOU NEED TO MAKE SURE YOUR OUTPUT LOOKS EXCACTLY LIKE MINE.

Upload your two(2) documents; your database (.db) and your sql statements (.sql) to GeorgiaView.