Database Systems Homework #2

CS143: Database Systems
Homework #2
1. Assume the following tables for this problem:
Employee(person-name, age, street, city)
Work(person-name, company-name, salary)
Company(company-name, city)
Manage(person-name, manager-name)
A person’s name is unique, but a person may work for more than one company. A company name is
unique, but a company may be located in more than one city.
(a) Write a query in SQL to find the names of persons who work in one or more companies where they make a
salary that is less than $22,000.
(b) Write an SQL query to find the names of persons who work in one or more companies and make
less than $22,000 in the majority (i.e., 50% or more) of the companies they work for.
2. Assume the database of the previous problem and write the following queries in SQL. You should use
at least one subquery in each of your answers and write each query in two significantly different ways
(e.g., using different operators such as EXISTS, IN, and ALL)
(a) Find the name(s) of the employee(s) whose total salary is higher than those of all employees living
in Barstow.
(b) Find the name(s) of the manager(s) whose total salary is higher than that of at least one employee
that they manage.
3. Assume the following tables for this problem:
MovieStar(name, address, gender)
MovieExec(name, address, company, netWorth)
(a) We want to find the names and addresses of all female movie stars (gender = ’F’ in the MovieStar
relation) who are also movie executives with a net worth over $2,000,000 (netWorth 2000000
in the MovieExec relation).
i. Write the query in SQL using INTERSECT operator.
ii. Write the query in SQL without using INTERSECT operator.
(b) We want to find the movie stars who are not movie executives.
i. Write the query in SQL using EXCEPT operator.
ii. Write the query in SQL without using EXCEPT operator.
4. Assume the following tables for this problem:
ComputerProduct(manufacturer, model, price)
Desktop(model, speed, ram, hdd)
Laptop(model, speed, ram, hdd, weight)
1
A computer product is either a desktop or a laptop.
(a) Find the average speed of all desktop computers.
(b) Find the average price of all laptops with weight below 2kg.
(c) Find the average price of PC’s and laptops made by “Dell.”
(d) For each different CPU speed, find the average price of a laptop.
(e) Find the manufacturers that make at least three different computer models.
5. Assume the computer-product database of the previous problem, and write the following database
modifications.
(a) Using two INSERT statements, insert a desktop computer manufactured by HP, with model number
1200, price $1000, speed 1.2Ghz, 256MB RAM, and an 80GB hard drive.
(b) Using two DELETE statements, delete all desktops manufactured by IBM with price below $1000.
(Comments: Be careful with the order of your two DELETE statements.)
(c) For each laptop made by Gateway, add one kilogram to the weight. (Hint: The WHERE clause in
a UPDATE statement may contain complex conditions, including subqueries.)
2
6.Returning to the Enroll(sid, dept, cnum, sec) example which shows the enrollment f
or this quarter:
(a) Write an SQL query to find the students who are only enrolled in the CS classes
offered this quarter.
(b) Write and SQL query to find the students who are enrolled in all the CS classes
offered this quarter.
(c) Write the previous queries using different SQL constructs. In particular can you
express those queries using the count aggregate? Please explain.
7. Write SQL queries for Hw1 problems 3 (b), (c), (d), (e) using different constructs
from those you used for HW1 (e.g., if you used NOT EXIST or NOT IN, you
might use COUNT or ALL, or viceversa.)