Using the following tables, you will write a relational algebra expression for e

Question

Using the following tables, you will write a relational algebra expression for each question.

CAPTAINS(CaptainID, CaptainName, CaptainPhone, YearsExperience, CaptainSupervisorID)

CREW(CrewID, CrewName, CrewPhone, DateHired, Position)

BOATS(BoatID, BoatName, SeatingCapacity, HomeHarbor)

TRIPS(TripID, TripDate, TripTime, TripOrigination, TripDestination, TripBoatID, CaptainID)

ASSIGNED_CREW(CrewID, TripID, Payment)

The CaptainSupervisorID matches a CaptainID of another captain.

A. Write a query to output the name of all captains with more than 10 years of experience.

B. Write a query to output the name and phone of all captains and crew members. There should be just two columns in this output.

C. Write a query to output the crewname, crewphone, date hired, tripID and payment for all crew members. If the crew member has never been assigned, we should still see that crew member’s other information in the output.

D. Write a query to output the name of all captains and the name of their supervisor.

E. Write a query to output the name of all crew members and their average payment.

F. Write a query to output the name of all captains who have captained every boat.

G. Write a query to output the names of all captains who have captained a boat with a home harbor of Gloucester.

H. Write a query to output the sum of all payments made to crew members on trip 87.

I. Write a query to output the CrewID of all crew members who have never been assigned.

J. Write a query to print, for each tripID, the number of crew members assigned to that trip.

Explanation / Answer

1.
select CaptainName from CAPTAINS where YearsExperience > 10;

2.select CaptainName,CapatinPhone from CAPTAINS;

select CrewName,CrewPhone from CREW;

3.

select A.CrewName,A.CrewPhone,A.DateHired,B.TripID,B.Payment
from CREW A left outer join ASSIGN_CREW B
on A.CrewID = B.CrewID;

4.

select CaptainName,CaptainSupervisorID from CAPTAINS;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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