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