Any help appreciated Question 1 5 points Answer each of the

Any help appreciated!

Question 1 (5 points). Answer each of the following questions briefly. The questions are based on the following relational schema:

Emp( eid: integer, ename: string, age: integer, sala1l1: real)

Works( eid: integer, did: integer, pet_time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)

1. Give an example of a foreign key constraint that involves the Dept relation.

2. Write the SQL statements required to create the preceding relations, including appropriate

versions of all primary and foreign key integrity constraints.

3. Define the Dept relation in SQL so that every department is guaranteed to have a manager.

4. Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 and salary = 15,000.

5. Write an SQL statement to give every employee a 10 percent raise.

6. Write an SQL statement to delete the Toy department.

Question 2 (5 points). 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 in SQL:

1. Find the pnames of parts for which there is some supplier.

2. Find the snames of suppliers who supply every part.

3. Find the snames of suppliers who supply every red part.

4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

5. Find the sids of suppliers who supply only red parts.

6. Find the sids of suppliers who supply a red part and a green part.

7. Find the sids of suppliers who supply a red part or a green part.

Question 3 (5 points). The following relations keep track of airline flight information:

Write the following queries in SQL:

For each pilot who is certified for more than three aircraft, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.

Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles.

Solution

Question 1