Homework 2 Solution

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.




Write a query in SQL to nd the names of persons who work in one or more companies where they make a salary that is less than $20,000.



Write the same query in Relational Algebra.



Compare the results of (a) and (b), are they the same? Why?



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 signi cantly di erent ways (e.g., using di erent operators such as EXISTS, IN, and SOME)



Find the name(s) of the employee(s) whose total salary is higher than those of all employees living in Los Angeles.



Find the name(s) of the manager(s) whose total salary is higher than that of at least one employee that they manage.



Assume the following tables for this problem:



MovieStar(name, address, gender)




MovieExec(name, address, company, netWorth)




We want to nd 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 $1,000,000 (netWorth 1000000 in the MovieExec relation).



Write the query in SQL using INTERSECT operator.



Write the query in SQL without using INTERSECT operator.



We want to nd the movie stars who are not movie executives.



Write the query in SQL using EXCEPT operator.



Write the query in SQL without using EXCEPT operator.



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.




Find the average speed of all desktop computers.



Find the average price of all laptops with weight below 2kg.



Find the average price of PC’s and laptops made by \Dell."



For each di erent CPU speed, nd the average price of a laptop.



Find the manufacturers that make at least three di erent computer models.



Assume the computer-product database of the previous problem, and write the following database modi cations.



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.



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



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



6.Returning to the Enroll(sid, dept, cnum, sec) example which shows the enrollment f or this quarter:




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.




Write the previous queries using different SQL constructs. In particular can you express those queries using the count aggregate? Please explain.




















































































2