Lab: SQL DML

In this lab, you will write database queries using SQL’s Data Manipulation Language (DML).

Instructions

You should work on this assignment individually. It is important that all students learn the basics of SQL code. Task 1 will not be graded, and you are welcome to double-check your Task 1 solution with other students before working on Task 2 individually.

Task 1: Document the Credit Card Database Schema

First, download the credit card database: credit.sqlite

Next, draw a relational schema diagram depicting the tables in the database. You should indicate primary keys and foreign keys. You are welcome to use the SQLite command-line, a GUI, or any other tool.

Note: Many real-world databases are poorly documented, so it is realistic to create documentation from an already-implemented database.

Note: If you are unfamiliar with credit cards, here is some background information. Credit cards are used to make payments to vendors. Each month, the credit card company sends each card holder a bill. Customers can opt to make the minimum payment (often 2% of the balance), or to pay up to the card’s complete balance. If the balance isn’t completely paid, a credit card will accrue interest, increasing the card’s balance by an amount determined by the card’s annual percentage rate (APR). Thus, credit card companies profit when customers carry a balance from month-to-month.

SQLite Meta Commands

SQL DML Documentation

Task 2: Write Database Queries

Write SQL queries to answer these questions. Save each query in the appropriate .sql file (e.g., all_customers.sql, j_customers.sql, etc.).

Note: The data shown in the examples will not exactly match the results from your queries.

Note: You must match the column names shown in the examples. You can enable display of column names by running: .headers ON

List All Customers: all_customers.sql

List Customer names and email addresses. Sort in ascending order by email address.

For example:

name email
John Adams Adams35@gmail.com
Albert Holmes Albert69@yahoo.com
Allison Jenkins AllisonJenkins@gmail.com
… …

List All J Customers: j_customers.sql

List the names all customers that start with “J”. Sort in descending order by name.

For example:

name
Julie Huffman
Joyce Anderson
Joshua Wright
…

List Credit Card Information: card_info.sql

For each card number, list the credit limit, APR, and the name of the card holder.

For example:

number limit apr name
2720811648188303 10000 19.99 Jocelyn Cantrell
2229538659200281 10000 17.24 Jocelyn Cantrell
2250063368374444 1000 28.24 Bradley Gonzalez
… … … …

Hint: One of the column names is a reserved keyword in SQL. Generally, it’s a bad idea to name columns with reserved words, but there are so many reserved words they can be hard to avoid. In SQLite, you can use quotation marks to use keywords as column names.

Calculate the Interest Accrued on Credit Cards: interest.sql

For each card number, list the total amount of interest accrued. Sort in descending order by interest accrued.

For example:

card_number interest
2688823347677759 1110.73
2226823034292101 629.75
2268932746428058 516.66
… …

Find Delinquent Customers: delinquent.sql

Identify the customers who missed at least one payment. List their emails, and the number of payments they missed.

For example:

email missed
Adrian41@gmail.com 4
Aguirre55@yahoo.com 3
Amanda98@gmail.com 5
… …

Note: A Payment’s date_paid will be NULL if the customer missed the payment.

Count Number of Credit Cards: card_count.sql

For each customer, count how many credit cards they have.

For example:

name card_count
Victoria Howard 4
Nancy Watts 4
Kathleen Franco 0
… …

Hint: The autograder uses SQLite version 3.37.2, which supports left outer joins, but not right and full outer joins.

List All Vendor Names: vendors.sql

List the names of all vendors where Transactions were made. Eliminate duplicates.

For example:

vendor
Costco
KFC
Mint Mobile
…

Calculate Transaction Statistics for Each Vendor: vendor_stats.sql

For each vendor, calculate:

Sort in descending order by total amount.

For example:

vendor total number average
McDonald’s 42523.8999999999 1950 21.8071282051282
Domino’s Pizza 37627.72 1098 34.2693260473588
Costco 28549.02 381 74.931811023622
… … … …

Note: Although the schema specifies that amounts are represented as decimals (i.e., fixed-point), these results suggest floating-point arithmetic is being performed. In fact, SQLite violates the SQL standard, and uses floating-point operations on fixed-precision columns. Most other databases handle fixed-point arithmetic properly (e.g., PostgreSQL). For this and many other reasons, it would be inappropriate to use SQLite to handle real financial data.

Find Inactive Cards: inactive.sql

Find the credit card numbers for which a transaction hasn’t been made since 2023-01-01.

For example:

card_number
2222877804804275
2224197461873457
2224276981475744
…

Bonus: Calculate the Balance of All Credit Cards: balance.sql

For each credit card, the balance is calculated from transactions made, interest accrued, and payments made:

\[\text{balance} = \text{transactions} + \text{interest} - \text{payments}\]

Retrieve each card number’s balance, in descending order by balance.

For example:

number balance
2688823347677759 4902.14
2267230960974357 2533.17
2543965807454669 2505.94
… …

Submit

Upload your .sql files to Gradescope.

This lab will be graded for accuracy, as part of your assignments grade.