Skip to content

HW1: Beginning SQL

Due: Tuesday, Sep 2nd

SQLite logo
Image source: sqlite.org

This is the first of three SQL query assignments. You are given 8 queries to write, along with the expected output. Use DB Browser to write and test the queries. Be sure to start early—this assignment might take longer than you think.

Objectives

  • Write queries that use JOIN, GROUP BY, LIMIT, and expressions.
  • Use incremental development to solve problems and look at data.

Instructions

  1. Download the provided template. Read and follow the instructions at the top of the file.

  2. Download the solution's output. This is the output that your final queries should produce.

  3. Create a SQLite database named hw1.sqlite. Import each of the CSV files provided below.

Note: Your SQL file should run without any errors and finish in under two minutes. If any query is taking longer than 15 seconds, you are probably doing something wrong.

Autograder Script – optional, but recommended

Download the autograder script. Gradescope will use this script to test your submission.

  • Run hw1.py (the autograder script) in the same folder as hw1.sql and hw1-sol.txt.
  • Or you can just submit to Gradescope over and over, and the same script will run.
  • We're giving you the autograder script so you don't have to submit so many times.

Command Line – optional, but recommended

The dot commands (.headers, .mode, .print) in hw1.sql work only in the Command Line Shell for SQLite, not in DB Browser. If the sqlite3 command is not installed on your computer, see the SQLite Download Page. Then run this command to generate your own output file for comparison with the solution:

sqlite3 hw1.sqlite < hw1.sql > hw1-out.txt

Company Database

The first database contains fake data about a company that has a handful of departments and thousands of employees. The data includes about 4 million records in total, including the history of each employee (their departments, job titles, and salaries). The data is provided by datacharmer.

company schema

Download and unzip company.zip for the CSV files.

World Database

The second database contains information about countries of the world, some of the cities in those countries, and languages spoken in each country. The data is provided by Statistics Finland.

world schema

Download and unzip world.zip for the CSV files.

Submission

Submit your hw1.sql to the HW1 assignment on Gradescope.

Your code will first be graded by Gradescope and then by the professor. The grade you receive from Gradescope is the maximum grade that you can receive on the assignment.

After the due date, the professor may manually review your code. At that time, points may be deducted for inelegant code, inappropriate variable names, bad comments, etc.

Gradescope will provide you with hints but might not completely identify the defects in your submission. You are expected to test your own code before submitting.

There is no limit on the number of submissions and no penalty for excessive submissions. Points will be allocated as follows:

Criterion Points Details
Autograder 80 pts Partial Credit Possible
Code Review 20 pts Partial Credit Possible

The code review will include:

  1. The approach you took on each query (10 pts).
  2. Style and formatting of the SQL code (10 pts).