Q1 Consider the following ER diagram. Write SQL statements to create the corresponding tables and capture as many of the constraints as possible (create tables only). name pname age ssn lot Dependents Employees Purchaser eneficiary Policies policyid cost Q2. Assume we have the following two tables and instances for them: CREATE TABLE Employee Work (age INTEGER, ssn CHAR(10), name CHAR(50), pid CHAR(10) Not Null, PRIMARY KEY (ssn), FOREIGN KEY (pid) REFERENCES Project) CREATE TABLE Project( pid CHAR(10), budget CHAR(10), PRIMARY KEY (pid)) Project Employee-work pid budget Ssn name pid 100 500K 5380 Guldu 102 102 200K 5381 Ali 100 5382 Jill 100 What will be the new instances of the tables Employee-work and Project after running the following query? DELETE FROM Project P WHERE pid 100 Q3. A) Are the following queries equivalent? Queryl: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 Query2: SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) B) Considering the following instances of Sailors and Reserves tables, what are the results of the query1 and query2 in part A: sid bid day 1 101 10/10/17 sid sname rating age 3 103 11/12/17 1 dustin 7 45.0 3 103 10/10/18 2 zorba 8 55.5 3 102 02/12/19 3 rusty 10 35.0 Q4. A) Are the following queries equivalent? Queryl: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid<>103 Query2: SELECT S.sname FROM Sailors S EXCEPT SELECT S.sname FROM Sailors Reserves R WHERE S.sid=R.sid AND R.bid=103; B) Considering the following instances of Sailors and Reserves tables, what are the results of the query1 and query2 in part A: sid bid day 1 101 10/10/17 sid sname rating age 3 103 11/12/17 1 dustin 7 45.0 3 103 10/10/18 2 zorba 8 55.5 3 102 02/12/19 3 rusty 10 35.0 Q5 Consider the following relational schema. Emp(eid: integer ename: string age: integer salary: real ) Works(eid integer did: integer start: date Dept(did: integer dname: string budget: real managerid: integer ) The meaning of these relations is straightforward Write the following queries in SQL. 1. Find the id of employees who work in both Hardware and Sale department 2. Find manager (ids) and names of departments that have more than ten employees 3. For each department with more than 20 employees, print did and the number of employees who work in that department. 4. Find the names of managers who manage the departments with the largest budget. Q6. Consider the following schema: Suppliers(sid integer, sname: string, address string) Parts(pid: integer, pname string, color string) Catalog(sid integer, pid integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers Write the following queries SQL: Find the names of suppliers who supply every red part 2. Find the sid of suppliers who supply only red parts 3. Find the sid of suppliers who charge for some part more than the average price of that part. Q7. Formulate the following SQL query in relational algebra using an efficient way The schema is given in the following. Emp(eno, ename, title, city) Proj(pno, pname, budget. city) Works(eno, pno,since) SELECT ename FROM Works Emp WHERE Works pno- P150' AND Works.eno Emp.eno