Assignment2 (Chap 3)
SC022650 LI WENTAI
3.1 Write the following queries in SQL, using the university schema (as shown in the lecture ppt or the
textbook).
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
SELECT title
FROM course
WHERE dept_name = 'Comp. Sci.'
AND credits = 3;
b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no
duplicates in the result.
SELECT DISTINCT t.ID
FROM instructor i
JOIN teaches te ON i.ID = te.ID
JOIN takes t ON te.course_id = t.course_id
AND te.sec_id = t.sec_id
AND te.semester = t.semester
AND te.year = t.year
WHERE i.name = 'Einstein';
c. Find the highest salary of any instructor.
SELECT MAX(salary) AS highest_salary
FROM instructor;
d. Find all instructors earning the highest salary (there may be more than one with the same salary).
SELECT ID, name
FROM instructor
WHERE salary = (SELECT MAX(salary) FROM instructor);
e. Find the enrollment of each section that was offered in Fall 2017.
SELECT s.course_id, s.sec_id, COUNT(t.ID) AS enrollment
FROM section s
LEFT JOIN takes t ON s.course_id = t.course_id
AND s.sec_id = t.sec_id
AND s.semester = t.semester
AND s.year = t.year
WHERE s.semester = 'Fall' AND s.year = 2017
GROUP BY s.course_id, s.sec_id;
f. Find the maximum enrollment, across all sections, in Fall 2017.
SELECT MAX(enrollment) AS max_enrollment
FROM (
SELECT COUNT(t.ID) AS enrollment
FROM section s
LEFT JOIN takes t ON s.course_id = t.course_id
AND s.sec_id = t.sec_id
AND s.semester = t.semester
AND s.year = t.year
WHERE s.semester = 'Fall' AND s.year = 2017
GROUP BY s.course_id, s.sec_id
) AS enrollments;
g. Find the sections that had the maximum enrollment in Fall 2017.
WITH enrollment_cte AS (
SELECT s.course_id, s.sec_id, COUNT(t.ID) AS enrollment
FROM section s
LEFT JOIN takes t ON s.course_id = t.course_id
AND s.sec_id = t.sec_id
AND s.semester = t.semester
AND s.year = t.year
WHERE s.semester = 'Fall' AND s.year = 2017
GROUP BY s.course_id, s.sec_id
)
SELECT course_id, sec_id, enrollment
FROM enrollment_cte
WHERE enrollment = (SELECT MAX(enrollment) FROM enrollment_cte);
3.3 Write the following inserts, deletes, or updates in SQL, using the university schema.
a. Increase the salary of each instructor in the Comp. Sci. department by 10%.
UPDATE instructor
SET salary = salary * 1.10
WHERE dept_name = 'Comp. Sci.';
b. Delete all courses that have never been offered (i.e., do not occur in the section relation).
DELETE FROM course
WHERE course_id NOT IN
(SELECT course_id FROM section);
c. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same
department, with a salary of $10,000.
INSERT INTO instructor (ID, name, dept_name, salary)
SELECT ID, name, dept_name, 10000
FROM student
WHERE tot_cred > 100;
3.8 Consider the bank database of Figure 3.18, where the primary keys are underlined. Construct the
following SQL queries for this relational database.
a. Find the ID of each customer of the bank who has an account but not a loan.
SELECT ID
FROM depositor
EXCEPT
SELECT ID
FROM borrower;
b.Find the ID of each customer who lives on the same street and in the same city as customer '12345'.
SELECT ID
FROM customer
WHERE (customer_street, customer_city) = (
SELECT customer_street, customer_city
FROM customer
WHERE ID = '12345'
)
AND ID != '12345'; -- Exclude the customer '12345' itself
c. Find the name of each branch that has at least one customer who has an account in the bank and who
lives in “Harrison”.
SELECT DISTINCT a.branch_name
FROM account a
JOIN depositor d ON a.account_number = d.account_number
JOIN customer c ON d.ID = c.ID
WHERE c.customer_city = 'Harrison';
branch(branch_name, branch city, assets)
customer (ID, customer_name, customer_street, customer_city)
loan (loan_number, branch_name, amount)
borrower (ID, loan number)
account (account_number, branch_name, balance )
depositor (ID, account_number)
Figure 3.18 Banking database.
3.9 Consider the relational database of Figure 3.19, where the primary keys are underlined. Give an
expression in SQL for each of the following queries.
a. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation”.
SELECT e.ID, e.person_name, e.city
FROM employee e
JOIN works w ON e.ID = w.ID
WHERE w.company_name = 'First Bank Corporation';
b. Find the ID, name, and city of residence of each employee who works for First Bank Corporation” and
earns more than $10000.
SELECT e.ID, e.person_name, e.city
FROM employee e
JOIN works w ON e.ID = w.ID
WHERE w.company_name = 'First Bank Corporation'
AND w.salary > 10000;
c. Find the ID of each employee who does not work for “First Bank Corporation”.
SELECT ID
FROM works
WHERE company_name <> 'First Bank Corporation';
d. Find the ID of each employee who earns more than every employee of “Small Bank Corporation”.
SELECT ID
FROM works
WHERE salary > ALL (
SELECT salary
FROM works
WHERE company_name = 'Small Bank Corporation'
);
e. Assume that companies may be located in several cities. Find the name of each company that is
located in every city in which “Small Bank Corporation”is located.
SELECT c.company_name
FROM company c
WHERE NOT EXISTS (
SELECT city
FROM company
WHERE company_name = 'Small Bank Corporation'
EXCEPT
SELECT city
FROM company c2
WHERE c2.company_name = c.company_name
);
f. Find the name of the company that has the most employees (or companies, in the case where there is
a tie for the most).
WITH employee_count AS (
SELECT company_name, COUNT(*) AS num_employees
FROM works
GROUP BY company_name
)
SELECT company_name
FROM employee_count
WHERE num_employees = (SELECT MAX(num_employees) FROM employee_count);
g. Find the name of each company whose employees earn a higher salary, on average, than the average
salary at “First Bank Corporation”.
SELECT company_name
FROM works
GROUP BY company_name
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM works
WHERE company_name = 'First Bank Corporation'
);
employee (ID, person_name, street, city)
works (ID, company_name, salary)
company (company_name, city)
manages (ID, manager_id)
Figure 3.19 Employee database.
