1.Consider the following relational DB schema. Write your SQL statements to crea

Question

1.Consider the following relational DB schema. Write your SQL statements to create the following tables. (30 points)

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

Works (eid: integer, did: integer, work_ time: integer)

Dept (did: integer, budget: real, manager_id: integer)

Note that primary keys are underlined. Please specify ALL the primary key and foreign key constraints when you create the tables above.

Question 2. The following relations keep track of airline flight information:

Flight (flno: integer, from: string, to: string, distance: integer,

departs: time, arrives: time, price: real)
Aircraft (aid: integer, aname: string, cruisingrange: integer)

Certified (eid: integer, aid: integer)

Employee (eid: integer, ename: string, salary: integer)

Note that the Employee relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the following queries in SQL. (50 points)

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

2.2 Find the names of pilots whose salary is less than the price of the cheapest flight from Los Angeles, CA to Valdosta, GA.

2.3 Find the names of pilots certified for some Boeing aircraft. (hint: consider substring pattern matching)

2.4 Find the aids of all aircraft that can be used on ALL the routes from Los Angeles, CA to Valdosta, GA.

2.5 Find the cheapest price, the highest price and the count of the flights that fly from New York City, NY to either San Jose, CA or Los Angeles, CA.

Question 3. Write a SQL statement to add a new attribute

Explanation / Answer

Hope it may helps you..........

create table Emp(eid integer,ename varchar2,age integer,salary numeric);

create table Works(eid integer,did integer,work_time integer);

create table Dept(did integer,budget numeric,manager_id integer);

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.