HW1. SQL Basics

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
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.

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

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
Please 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.

%load_ext sql
1.1 (1 point) Create an empty database named cssys
%sql sqlite:///cssys
'Connected: @cssys'
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
%%sql

CREATE TABLE students (
    id integer, 
    firstname char(15), 
    lastname char(15), 
    age integer, 
    gender char(1), 
    gpa double, 
    primary key(id)
)
 * sqlite:///cssys
Done.
[]
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
%%sql

CREATE TABLE courses (
    id integer, 
    name varchar(30), 
    credit integer, 
    prereq integer, 
    primary key(id, prereq)
)
 * sqlite:///cssys
Done.
[]
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
%%sql

CREATE TABLE transcript (
    studentid integer, 
    courseid integer, 
    mark double, 
    semester integer, 
    credit integer, 
    primary key(studentid, courseid)
)
 * sqlite:///cssys
Done.
[]
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.

%%sql

DROP TABLE students;
CREATE TABLE students (
    id integer, 
    firstname char(15), 
    lastname char(15), 
    dob date default '0000-00-00', 
    gender char(1), 
    gpa double, 
    primary key(id)
)
 * sqlite:///cssys
Done.
Done.
[]
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
%%sql

INSERT INTO students(id, firstname, lastname, dob, gender, gpa)
VALUES(1001, 'adam', 'smith', '2000-01-03', 'm', 3.1);
INSERT INTO students(id, firstname, lastname, dob, gender, gpa)
VALUES(1002, 'alice', 'frank', '1999-03-11', 'f', 3.4);
INSERT INTO students(id, firstname, lastname, dob, gender, gpa)
VALUES(1003, 'bob', 'hal', '1999-09-01', 'm', 2);
 * sqlite:///cssys
1 rows affected.
1 rows affected.
1 rows affected.
[]
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
%%sql

INSERT INTO courses (id, name, credit, prereq) VALUES (100, 'programming', 3, NULL);
INSERT INTO courses (id, name, credit, prereq) VALUES (110, 'math', 3, NULL);
INSERT INTO courses (id, name, credit, prereq) VALUES (120, 'web', 4, NULL);
INSERT INTO courses (id, name, credit, prereq) VALUES (301, 'networking', 4, 200);
INSERT INTO courses (id, name, credit, prereq) VALUES (301, 'networking', 4, 150);
INSERT INTO courses (id, name, credit, prereq) VAlues (301, 'networking', 4, 210);
INSERT INTO courses (id, name, credit, prereq) VAlues (354, 'database', 3, 120);
INSERT INTO courses (id, name, credit, prereq) VAlues (354, 'database', 3, 110);
INSERT INTO courses (id, name, credit, prereq) VAlues (360, 'os', 3, 150);
INSERT INTO courses (id, name, credit, prereq) VAlues (360, 'os', 3, 210);
 * sqlite:///cssys
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
[]
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

%%sql

INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1001, 100, 3, 201801, 3);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1001, 110, 3.5, 201801, 3);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1001, 120, 2.7, 201801, 4);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1001, 301, 3.4, 201802, 4);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1002, 100, 3, 201801, 3);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1002, 110, 3.2, 201901, 3);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1002, 301, 3.1, 201902, 4);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1003, 100, 2.5, 201801, 3);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1003, 120, 3.5, 201901, 4);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1003, 301, 2.8, 201902, 4);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1003, 354, 4, 201903, 3);
INSERT INTO transcript (studentid, courseid, mark, semester, credit) VALUES(1003, 360, 3.5, 201802, 3);
 * sqlite:///cssys
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
[]
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.

%%sql

SELECT * FROM students
 * sqlite:///cssys
Done.
id    firstname    lastname    dob    gender    gpa
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.0
4.2 (1 point) Please write an SQL query to show the rows whose credit is 3 in the courses table.

%%sql

SELECT * FROM courses
WHERE credit = 3
 * sqlite:///cssys
Done.
id    name    credit    prereq
100    programming    3    None
110    math    3    None
354    database    3    120
354    database    3    110
360    os    3    150
360    os    3    210
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.

%%sql

SELECT * FROM transcript
WHERE mark > 3
AND credit >=3
 * sqlite:///cssys
Done.
studentid    courseid    mark    semester    credit
1001    110    3.5    201801    3
1001    301    3.4    201802    4
1002    110    3.2    201901    3
1002    301    3.1    201902    4
1003    120    3.5    201901    4
1003    354    4.0    201903    3
1003    360    3.5    201802    3
4.4 (1 point) Please write an SQL query to show studentid, courseid and mark of all rows in the transcript table.

%%sql

SELECT studentid, courseid, mark FROM transcript
 * sqlite:///cssys
Done.
studentid    courseid    mark
1001    100    3.0
1001    110    3.5
1001    120    2.7
1001    301    3.4
1002    100    3.0
1002    110    3.2
1002    301    3.1
1003    100    2.5
1003    120    3.5
1003    301    2.8
1003    354    4.0
1003    360    3.5
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.

%%sql

SELECT studentid, courseid, mark FROM transcript
WHERE semester = 201902
 * sqlite:///cssys
Done.
studentid    courseid    mark
1002    301    3.1
1003    301    2.8
4.6 (1 point) Please write an SQL query to show distinct courseid of all rows in the transcript table.

%%sql

SELECT DISTINCT courseid FROM transcript
 * sqlite:///cssys
Done.
courseid
100
110
120
301
354
360
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.

%%sql

SELECT firstname, lastname, gpa FROM students
ORDER BY gpa DESC
 * sqlite:///cssys
Done.
firstname    lastname    gpa
alice    frank    3.4
adam    smith    3.1
bob    hal    2.0
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"
%%sql

SELECT studentid, courseid, CASE WHEN mark >= 3.5 then 'A'
WHEN mark >=3 AND mark < 3.5 then 'B'
WHEN mark >=2.5 AND mark < 3 then 'C'
WHEN mark >=2 AND mark < 2.5 then 'D'
WHEN mark <2 then 'F'
end as lettergrade FROM transcript
 * sqlite:///cssys
Done.
studentid    courseid    lettergrade
1001    100    B
1001    110    A
1001    120    C
1001    301    B
1002    100    B
1002    110    B
1002    301    B
1003    100    C
1003    120    A
1003    301    C
1003    354    A
1003    360    A
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.