HW1. SQL Basics

Objectives

In this homework assignment, you will use SQL to store and query a database. You will learn the followings:

  • How to create a database
  • How to create a table
  • How to change a table after creation
  • How to insert data into a table
  • How to select certain rows or columns from a table
  • How to join two tables together

  • How to use expressions

    This assignment has a total of four questions and 25 points.

You will also use SQLite as the DBMS. In contrast to many other database management systems (e.g., Oracle, DB2, and SQL Servier), SQLite is not a client–server database engine. Rather, it is embedded into the end program. This unique feature has led it to be adopted by billions of applications.

Setup

  1. Install Jupyter Notebook

Jupyter is an open-source web application that allows you to create and share documents that contain live code, equations, visualizations and narrative text. Please install it using Anaconda for python 2.7.

  1. Install SQLite

If you are using Mac OS X or Linux, SQLite should be pre-installed. Open a terminal and type sqlite3. To exit, type ".exit"

If you are using Windows, please follow the instructions on here to install SQLite

  1. Install ipython-sql

ipython-sql is a jupyter notebook extension. It allows using SQL queries inside jupyter notebooks. Please install it using Anaconda:

Open a terminal and type conda install -c conda-forge ipython-sql

Initial Test

Pelase download and follow the steps on the test.ipynb to test your environment before you start your homework assignment.

HomeWork

Q1: Create a database (4 points)

The goal is to create a database to manage student and course information in computing science department (named cssys), and then create three tables in this database. The first table is named students, the second table is named courses, and the third table is called transcript.

To start, please execute the following cell to load the ipython-sql extension.

In [ ]:
%load_ext sql

1.1 (1 point) Create an empty database named cssys

In [ ]:
#REPLACE WITH YOUR CODE#

1.2 (1 point) Create a table named students

Please create a table named students. The students table has six attributes: studentid, firstname, lastname, age, gender, gpa

  • id - integer
  • firstname - char(15)
  • lastname - char(15)
  • age - integer
  • gender - char(1)
  • gpa - double
  • id is Primary Key
In [ ]:
#REPLACE WITH YOUR CODE#

1.3 (1 point) Create a table named courses

Please create a table named courses. The courses table has four attributes: id, name, credit, prereq

  • id- integer
  • name - varchar(30)
  • credit - integer
  • prereq - integer
  • (id, prereq) is Primary Key
In [ ]:
#REPLACE WITH YOUR CODE#

1.4 (1 point) Create a table named transcript

Please create a table named transcript. The transcript table has five columns: studentid, courseid, mark, semester, credit

  • studentid- integer
  • courseid - integer
  • mark - double
  • semester - integer (represented as year + 01:fall, 02:spring, 03:summer)
  • credit - integer
  • (studentid, courseid) is Primary Key
In [ ]:
#REPLACE WITH YOUR CODE#

Q2: Modify a database (3 points)

Please write SQL queries to delete the age attribute and as a Date of Birth (dob) attribute to table students. Please decide on the type and the default value of this attribute and include it in your response. You can decide how to perform this using delete and recreate or modify.

In [ ]:
#REPLACE WITH YOUR CODE#

Q3: Add data to a database (6 points)

3.1 (2 points) Add rows to students.

Please write SQL queries to insert the following rows to the students table. Change the format of date of birth attribute value based on your definition of its type.

1001, adam, smith, 2000-01-03, m, 3.1
1002, alice, frank, 1999-03-11, f , 3.4
1003, bob, hal, 1999-09-01, m, 2
In [ ]:
#REPLACE WITH YOUR CODE#

3.2 (2 points) Add rows to courses.

Please write SQL queries to insert the following rows to the courses table.

100, programming, 3, NULL
110, math, 3, NULL
120, web, 4, NULL
301, networking, 4, 200
301, networking, 4, 150
301, networking, 4, 210
354, database, 3, 120
354, database, 3, 110
360, os, 3, 150
360, os, 3, 210
In [ ]:
#REPLACE WITH YOUR CODE#

3.3 (2 points) Add rows to transcript.

Please write SQL queries to insert the following rows to the transcript table.

1001, 100, 3, 201801, 3
1001, 110, 3.5, 201801, 3
1001, 120, 2.7, 201801, 4
1001, 301, 3.4, 201802, 4
1002, 100, 3, 201801, 3
1002, 110, 3.2, 201901, 3
1002, 301, 3.1, 201902, 4
1003, 100, 2.5, 201801, 3
1003, 120, 3.5, 201901, 4
1003, 301, 2.8, 201902, 4
1003, 354, 4, 201903, 3
1003, 360, 3.5, 201802, 3
In [ ]:
#REPLACE WITH YOUR CODE#

Q4: Query a database (12 points)

Please write the SQL query for each of the requests below.

4.1 (1 point) Please write an SQL query to show all rows in the students table.

In [ ]:
#REPLACE WITH YOUR CODE#

4.2 (1 point) Please write an SQL query to show the rows whose credit is 3 in the courses table.

In [ ]:
#REPLACE WITH YOUR CODE#

4.3 (1 point) Please write an SQL query to show the rows whose mark is larger than 3 and credit is no smaller than 3 in the transcript table.

In [ ]:
#REPLACE WITH YOUR CODE#

4.4 (1 point) Please write an SQL query to show studentid, courseid and mark of all rows in the transcript table.

In [ ]:
#REPLACE WITH YOUR CODE#

4.5 (1 point) Please write an SQL query to show studentid, courseid and mark of all rows in the transcript table whose semester value is 201902.

In [ ]:
#REPLACE WITH YOUR CODE#

4.6 (1 point) Please write an SQL query to show distinct courseid of all rows in the transcript table.

In [ ]:
#REPLACE WITH YOUR CODE#

4.7 (2 points) Please write an SQL query to show the firstname and lastname and gpa from students table and sort it based on gpa.

In [ ]:
#REPLACE WITH YOUR CODE#

4.8 (4 points) Please write an SQL query to compute lettergrade of each row in the transcript table, and show studentid, courseid and lettergrade of all rows in the transcript table. lettergrade is computed as follows:

  • If mark >= 3.5, then lettergrade = "A"
  • If 3 <= mark < 3.5, then lettergrade = "B"
  • If 2.5 <= mark < 3, then lettergrade = "C"
  • If 2 <= mark < 2.5, then lettergrade = "D"
  • If mark < 2, then lettergrade = "F"
In [ ]:
#REPLACE WITH YOUR CODE#

Submission

Complete the code in this notebook hw1.ipynb, and submit it to through Canvas system to your HW1 activity. You can also include a pdf file where you can add your comments, thoughts, explanations about any of the questions.