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 load an available 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
You will also use SQLite as the DBMS. In contrast to many other database management systems (e.g., Oracle, DB2, and SQL Server), 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.

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

Code style
No penalty for bad code style but it is recomended to use the standard SQL style https://www.sqlstyle.guide/

Setup
You need the same initial setup as for the Interactive Session 1.

Questions
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.db
'Connected: @cssys.db'
1.2 (1 point) Create a table named students

Please create a table named students. The students table has six attributes: student id, first name, last name, 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 PRIMARY KEY, 
    firstname VARCHAR(15), 
    lastname VARCHAR(15), 
    age INTEGER, 
    gender VARCHAR(1), 
    gpa REAL
);
 * sqlite:///cssys.db
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, pre-requisites:

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.db
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 REAL, 
    semester INTEGER,
    credit INTEGER,
    PRIMARY KEY (studentid,courseid)
);
 * sqlite:///cssys.db
Done.
[]
Q2: Modify a database (1 point)
Please write SQL queries replace the age attribute with 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 should decide on how to perform this step (delete/recreate/modify).

%%sql
DROP TABLE students;

CREATE TABLE students(
    id INTEGER PRIMARY KEY, 
    firstname VARCHAR(15), 
    lastname VARCHAR(15), 
    dob date, 
    gender VARCHAR(1), 
    gpa REAL
);
 * sqlite:///cssys.db
Done.
Done.
[]
Q3: Add data to a database (3 points)
3.1 (1 point) 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 VALUES(1001, 'adam', 'smith', '2000-01-03', 'm', 3.1);
INSERT INTO students VALUES(1002, 'alice', 'frank', '1999-03-11', 'f', 3.4);
INSERT INTO students VALUES(1003, 'bob', 'hal', '1999-09-01', 'm', 2);
 * sqlite:///cssys.db
1 rows affected.
1 rows affected.
1 rows affected.
[]
3.2 (1 point) 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),
   (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);
 * sqlite:///cssys.db
10 rows affected.
[]
3.3 (1 point) Add rows to transcript.

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

1001, 100, 3, 202201, 3
1001, 110, 3.5, 202201, 3
1001, 120, 2.7, 202201, 4
1001, 301, 3.4, 202202, 4
1002, 100, 3, 201801, 3
1002, 110, 3.2, 202301, 3
1002, 301, 3.1, 202302, 4
1003, 100, 2.5, 202201, 3
1003, 120, 3.5, 202301, 4
1003, 301, 2.8, 202302, 4
1003, 354, 4, 202303, 3
1003, 360, 3.5, 202202, 3
%%sql
INSERT INTO transcript (studentid,courseid,mark,semester,credit)
VALUES  
    (1001, 100, 3, 202201, 3),
    (1001, 110, 3.5, 202201, 3),
    (1001, 120, 2.7, 202201, 4),
    (1001, 301, 3.4, 202202, 4),
    (1002, 100, 3, 201801, 3),
    (1002, 110, 3.2, 202301, 3),
    (1002, 301, 3.1, 202302, 4),
    (1003, 100, 2.5, 202201, 3),
    (1003, 120, 3.5, 202301, 4),
    (1003, 301, 2.8, 202302, 4),
    (1003, 354, 4, 202303, 3),
    (1003, 360, 3.5, 202202, 3);
 * sqlite:///cssys.db
12 rows affected.
[]
Q4: Query a database (10 points)
Please write the SQL query for each of the requests below.

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

%%sql
SELECT * 
FROM students;
   sqlite:///bank.db
 * sqlite:///cssys.db
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 a SQL query to show the rows whose credit is 3 in the courses table.

%%sql
SELECT *
FROM courses
WHERE credit=3
   sqlite:///bank.db
 * sqlite:///cssys.db
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 a 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.db
Done.
studentid    courseid    mark    semester    credit
1001    110    3.5    202201    3
1001    301    3.4    202202    4
1002    110    3.2    202301    3
1002    301    3.1    202302    4
1003    120    3.5    202301    4
1003    354    4.0    202303    3
1003    360    3.5    202202    3
4.4 (1 point) Please write a SQL query to show studentid, courseid and mark of all rows in the transcript table.

%%sql
SELECT studentid, courseid, mark
FROM transcript
 * sqlite:///cssys.db
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 a SQL query to show studentid, courseid and mark of all rows in the transcript table whose semester value is 202302.

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

%%sql
SELECT DISTINCT courseid
FROM transcript;
 * sqlite:///cssys.db
Done.
courseid
100
110
120
301
354
360
4.7 (1 point) Please write a 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;
   sqlite:///bank.db
 * sqlite:///cssys.db
Done.
firstname    lastname    gpa
bob    hal    2.0
adam    smith    3.1
alice    frank    3.4
4.8 (3 points) Please write a 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 THEN 'B'
    WHEN mark >= 2.5 THEN 'C'
    WHEN mark >= 2 THEN 'D'
    ELSE 'F'
  END AS lettergrade
FROM transcript;
 * sqlite:///cssys.db
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
Q5: Load & Query a database (7 points)
Suppose you work at a bank as a data analyst. Your main job is to analyze the data stored in their database to find out information that can help the business. Please download the database at this link.

The database has six tables. The following shows their schemas. Primary key attributes are underlined and foreign keys are noted in superscript.

Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumberFK-Branch}
Owns = {customerIDFK-Customer, accNumberFK-Account}
Transactions = {transNumber, accNumberFK-Account, amount}
Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch}
Branch = {branchNumber, branchName, managerSINFK-Employee, budget}
Please run the next cell after downloading the database, before you start.

%sql sqlite:///bank.db
'Connected: @bank.db'
5.1 (1 point) Suppose you talked with a customer, you remember their name started with 'M', included an 'r' and finished with an 'a', but you are not sure about the complete spelling. Please write a SQL query to show the first name and last name of the customers with such first name.

%%sql
SELECT firstName, lastName
FROM Customer
WHERE firstName LIKE 'M%r%a';
 * sqlite:///bank.db
   sqlite:///cssys.db
Done.
firstName    lastName
Martha    Young
Martha    Butler
Maria    Morgan
Maria    Young
5.2 (1 point) Please write a SQL query to show names of the branches and first name and last name of their managers.

%%sql
SELECT  b.branchName AS branchName, e.firstName, e.lastName
FROM Branch b
JOIN Employee e ON b.managerSIN = e.sin;
 * sqlite:///bank.db
   sqlite:///cssys.db
Done.
branchName    firstName    lastName
London    Phillip    Edwards
Latveria    Victor    Doom
New York    Victor    Doom
Berlin    Deborah    Hernandez
Moscow    Cheryl    Thompson
5.3 (1 point) Please write a SQL query to find out employees who are also customers (assume all names are unique).

%%sql
SELECT e.firstName, e.lastName
FROM Employee e
JOIN Customer c ON e.firstName = c.firstName AND e.lastName = c.lastName
 * sqlite:///bank.db
   sqlite:///cssys.db
Done.
firstName    lastName
Anne    Ramirez
Steven    Johnson
Margaret    White
Amanda    White
Roy    Morris
Victor    Doom
Victor    Perez
Terry    Bailey
Gerald    Watson
Victor    Doom
Mark    Jackson
Kathleen    Morris
Phillip    Edwards
Ernest    Adams
Mary    Powell
Arthur    Jones
Laura    Alexander
Willie    Jones
Anna    Cooper
Douglas    Wright
Charles    Smith
Susan    Hayes
Justin    Mitchell
Carl    Murphy
Victor    Doom
Amy    Ross
Ernest    Perez
Dennis    Collins
Deborah    Hernandez
5.4 (2 points) Please write a SQL query to show account number, account type, account balance, and transaction amount of the accounts with balance higher than 100,000 and transaction amouns higher than 15000, starting with the accounts with the highest transaction amount and highest account balance.

%%sql
SELECT a.accNumber, a.type, a.balance, t.amount
FROM Account a
JOIN Transactions t ON a.accNumber = t.accNumber
WHERE a.balance > 100000 AND t.amount > 15000
ORDER BY t.amount DESC, a.balance DESC;
 * sqlite:///bank.db
   sqlite:///cssys.db
Done.
accNumber    type    balance    amount
9    SAV    132271.23    114869.79
8    BUS    121267.54    114680.63
31    CHQ    111209.89    110249.28
1    SAV    118231.13    109587.15
25    SAV    105997.07    109068.54
13    CHQ    112505.84    108440.2
20    CHQ    107270.59    108278.46
4    BUS    106503.6    104550.76
26    SAV    112046.36    104346.46
6    CHQ    107309.23    104247.4
18    BUS    103579.69    103802.18
17    CHQ    103356.07    103431.57
28    CHQ    112617.97    102680.84
5    CHQ    105696.04    101945.4
2    CHQ    100808.03    100002.19
81    CHQ    107129.47    99712.38
227    CHQ    109916.78    98987.65
218    BUS    103650.37    98757.79
119    CHQ    113473.16    98480.27
147    CHQ    114094.94    98155.28
121    SAV    103512.78    98101.36
187    CHQ    103328.66    97629.4
165    CHQ    108042.83    96796.3
138    BUS    104044.22    96658.35
176    CHQ    113048.79    96473.74
282    SAV    101063.84    96430.68
135    SAV    105420.87    95889.6
198    CHQ    102686.76    95539.37
272    CHQ    101739.44    95431.93
164    CHQ    101336.25    94145.63
115    BUS    102857.55    93549.92
139    BUS    101394.11    92397.65
59    CHQ    112534.31    91590.92
221    BUS    105068.53    90728.42
291    SAV    101504.47    90181.92
200    BUS    100035.01    87660.19
130    CHQ    102776.09    87542.82
107    BUS    102366.95    86853.53
106    BUS    102297.76    85329.15
148    CHQ    100187.85    83528.49
5.5 (2 points) Please write a SQL query to find the customer ID, first name, and last name of customers who own accounts at London and Berlin branches, order by last name and first name.

%%sql
/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/
SELECT c.customerID, c.firstName, c.lastName
FROM Customer c
JOIN Owns o ON c.customerID = o.customerID
JOIN Account a ON o.accNumber = a.accNumber
JOIN Branch b ON a.branchNumber = b.branchNumber
WHERE b.branchName IN ('London', 'Berlin')
ORDER BY c.lastName, c.firstName;
 * sqlite:///bank.db
   sqlite:///cssys.db
Done.
customerID    firstName    lastName
66418    Stephanie    Adams
66418    Stephanie    Adams
98826    William    Adams
98826    William    Adams
86858    Carol    Alexander
77100    Laura    Alexander
77100    Laura    Alexander
25052    Jack    Anderson
25052    Jack    Anderson
89197    Lawrence    Anderson
89197    Lawrence    Anderson
41545    Terry    Bailey
41545    Terry    Bailey
41545    Terry    Bailey
33133    Henry    Barnes
64055    Laura    Barnes
18166    Ruby    Barnes
33913    Ronald    Bell
67384    Lawrence    Brooks
13230    Marie    Brooks
49747    Philip    Brooks
25159    Shirley    Brooks
72583    Clarence    Brown
30525    Helen    Carter
83620    Carlos    Clark
35380    Harold    Clark
41648    Shawn    Clark
46630    Billy    Coleman
97216    Dennis    Collins
97216    Dennis    Collins
33726    Jerry    Cook
33726    Jerry    Cook
33726    Jerry    Cook
28505    Joe    Cook
81263    Anna    Cooper
81263    Anna    Cooper
51850    Victor    Doom
51850    Victor    Doom
86357    Andrew    Evans
86357    Andrew    Evans
86357    Andrew    Evans
44922    Dennis    Flores
44922    Dennis    Flores
44922    Dennis    Flores
27954    Diana    Gonzales
87978    Christopher    Gonzalez
87978    Christopher    Gonzalez
89902    Joe    Gonzalez
78477    Brian    Gray
32422    Christine    Gray
32422    Christine    Gray
11790    Benjamin    Green
11790    Benjamin    Green
91672    Edward    Green
87416    Jeffrey    Griffin
38003    Joshua    Griffin
10839    Amy    Hayes
10839    Amy    Hayes
40351    Sandra    Hayes
40351    Sandra    Hayes
40351    Sandra    Hayes
82464    Susan    Hayes
61969    Antonio    Henderson
66386    Chris    Henderson
99537    Deborah    Hernandez
99537    Deborah    Hernandez
99537    Deborah    Hernandez
99537    Deborah    Hernandez
13697    Charles    Hill
13697    Charles    Hill
65044    Deborah    Hill
55146    Mark    Jackson
38861    Gerald    James
38861    Gerald    James
38861    Gerald    James
93300    Bonnie    Johnson
27004    Steven    Johnson
27004    Steven    Johnson
73386    Arthur    Jones
73386    Arthur    Jones
81108    Willie    Jones
19973    Albert    Kelly
80321    Kimberly    Kelly
34069    Earl    Lee
34069    Earl    Lee
45960    Jacqueline    Long
62312    Phyllis    Lopez
47953    Frank    Martinez
47953    Frank    Martinez
85587    Justin    Mitchell
88375    Randy    Mitchell
88375    Randy    Mitchell
88375    Randy    Mitchell
90649    Helen    Morgan
90649    Helen    Morgan
93995    Kevin    Morris
30807    Roy    Morris
90667    Carl    Murphy
30622    Harry    Murphy
35059    Larry    Murphy
96475    Sean    Nelson
84873    Cheryl    Parker
84873    Cheryl    Parker
88164    Jimmy    Parker
38351    Victor    Perez
73925    Doris    Powell
63772    Mary    Powell
14295    Anne    Ramirez
44065    Benjamin    Ramirez
98923    Dennis    Reed
46058    Adam    Rivera
46058    Adam    Rivera
92389    Amy    Ross
92389    Amy    Ross
22050    Helen    Sanchez
79601    Joe    Sanders
79601    Joe    Sanders
52189    Shawn    Sanders
52189    Shawn    Sanders
52189    Shawn    Sanders
37716    Annie    Scott
13423    Norma    Simmons
13423    Norma    Simmons
82333    Charles    Smith
59366    Susan    Smith
59366    Susan    Smith
73562    Jeremy    Stewart
69256    Jacqueline    Thomas
69256    Jacqueline    Thomas
65441    Arthur    Thompson
65441    Arthur    Thompson
80315    Roger    Turner
80315    Roger    Turner
33850    Henry    Ward
46937    Juan    Ward
44459    Gerald    Watson
29474    Amanda    White
29474    Amanda    White
28453    Margaret    White
90798    Aaron    Wilson
50742    Louise    Wilson
82244    Douglas    Wright
35780    Harold    Young
63859    Maria    Young
63859    Maria    Young
23010    Martha    Young
Submission
Complete the code in this notebook, and submit it to through Canvas system to your Homework 1 activity.