Database Systems: Design, Implementation, and Management, 12th Edition (Database

Question

Database Systems: Design, Implementation, and Management, 12th Edition (Database Book)

ISBN -13: 978130562748-2

EXERCISES ON RELATIONAL ALGEBRA

Use the following schema to write relational algebra statements for the questions that follow. The primary keys are underlined in the schema.

Pilot (pid: integer, pname: char, salary: integer)

Aircraft (aid: integer, aname: char, cruisingrange: integer)

Flight (flightno: integer, aid: integer, pid: integer, day: date)

1. Find the names of pilots who use the aircraft (aname) Boeing 727.

2. Find the id and name of the pilot who operated flightno CO103 on 01/01/99 with a cruising range of 3000 miles.

3. Find the cruising range of the aircraft operated by Jeffery Jones on 12/23/97.

4. Find the ids and names of pilots who can operate aircrafts with a range greater than 5000 miles with salaries more than $100,000.

5. List all the flights on 02/22/98.

6. List all the pilots.

7. Find the names pilots who operate of aircrafts with a cruising range between 4000 miles and 6000 miles.

8. List the id and name of the aircraft used for flightno NW111.

9. List all the Boeing 757 (aname) aircrafts.

10. Find the names of all pilots who receive a salary greater than $150,000.

Explanation / Answer

[1]
Select pname
From   Pilot
Where pid = (SELECT pid
              FROM   Flight
     WHERE aid = (SELECT aid
                   FROM   Aircraft
       Where aname like 'Boeing 727'));
       
[2]

Select pname
From   Pilot
Where pid = (SELECT pid
              FROM   Flight
     WHERE aid = (SELECT aid
                   FROM   Aircraft
       Where cruisingrange = 3000) AND flightno like 'CO103' AND day like '01/01/99' );

[3]

Select cruisingrange
From   Aircraft
Where aid = (SELECT aid
              FROM   Flight
     WHERE pid = (SELECT pid
                   FROM   Pilot
       Where pname like 'Jeffery Jones ') AND day = '12/23/97' );
       
       
       
[4]

Select pid,pname
From   Pilot
Where pid = (SELECT pid
              FROM   Flight
     WHERE aid = (SELECT aid
                   FROM   Aircraft
       Where cruisingrange > 5000)) AND Salary > 100000;
       
[5]

SELECT *
FROM   Flight
Where day = '02/22/98';     
     
       
[6]
SELECT *
FROM   Pilot;

[7]
Select pname
From   Pilot
Where pid = (SELECT pid
              FROM   Flight
     WHERE aid = (SELECT aid
                   FROM   Aircraft
       Where cruisingrange > 4000 AND cruisingrange < 6000));
       
[8]
Select aid , aname
From   Aircraft
Where aid = (SELECT aid
              FROM   Flight      
              WHERE flightno like 'NW111');
    
    
[9]

SELECT *
FROM Aircraft
WHERE aname like 'Boeing 757';

[10]

SELECT pname
FROM Pilot
WHERE Salary > 150000;    
       
       

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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