Assignment 5: SQL III
This assignment has two main parts, joins and subqueries.
Objectives
- Design and implement INNER, LEFT, and RIGHT JOINS
- Create subqueries in the three locations, select, from and where clause.
Step 1. Start by watching this fun video.
Step 2. Then watch their second video.
Step 3. This is a pretty good video on how to do subqueries in the select, from and where clause.
Part 1. Building your Database
So lets first start with our database from Assignment 3. Go ahead and normalize the database.
Let’s create a fresh database in a new Assignment 5 folder.
Your database will have the same data found in Assignment 3, but with a more normalized database. Do this by creating tables for the payee’s and another one for the transaction category. We will add a bank account type [checking, savings].
A couple things to note:
- Notice the table names are plural, while the primary keys are singular. I am okay with this either way, singular or plural, my preference is plural. I have seen it both ways.
- Notice I use the underscore _ to separate out the words.
- Notice everything is lower case for column names and table names.
Create the relationships before you add the data. You can add the relationship by scrolling over to the right when you modify a table.
Now go back and add the data to your new database using the data from Assignment 3. I find it helpful to add the payees, transaction categories, bank account, before adding the transactions. You can also copy the data with their primary keys into an excel spreadsheet to see their primary keys as you add the data.
Lastly, if you are running into issues, you can download a correct database here. Remember it is always better to work through the process of creating and importing the data as it is VERY good practice.
Part II. The SQL Queries
Query 1. The Basic Join
For the first query, I want to know which bank account it came from and what the amount was for, for each transaction where the transaction was greater than $100. This is a simple join on the bank account ID, which is in both tables.
Query 2. Another Join
Next I want to know how many transactions where there for each transaction type AND what the average transaction amount was for each transaction type. Be careful with the column names and column order. Make your answer look just like the below.
Query 3. A More Complex Join
On your own create this query without the use of sub queries, just using joins. Notice there are no null showing up.
Query 4. Adding the Nulls
For this last join query, lets add the date and nulls. Replicate the query, make sure it looks identical. Notice the nulls and the sort order.
Query 5. Using a Subquery in the Where Clause
For this query, return the sum of all the transactions from Kroger using a sub query in the where clause. The cool part about this is you do not have to do a join to use two tables.
Query 6. Same Answer but Use a Join
Now do it again, but this time use a join. Which one did you like better.
Turn It In
Now lets turn it in. Copy all your query code into one (1) .sql document. Label each query. Format the query using the standard found here. Upload your .sql file and your .db file for Assignment 5 in GeorgiaView.