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.

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

E. Query for name of all crew members and avg payment:

SELECT x.CrewName, x.Payment FROM (SELECT c.crewid, AVG(ac.payment) as avg_salary FROM crew c JOIN assigned_crew ac ON c.crewid=ac.crewid GROUP BY c.crewName) x

F. Query to output the name of all captains who have captained every boat:

SELECT c.captainname as CAPTAIN_NAME FROM captains c, trips t where c.captainid=t.captainid and t.captainid IN(SELECT t.captainid from trips t GROUP BY t.captainid having COUNT(t.captainid)=COUNT(DISTINCT(t.tripboatid)));

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

SELECT c.capitanName as CAPTAIN_NAME FROM captains c, boats b, trips t where t.captainid=c.captainid and t.captainid IN (SELECT t.captainid FROM trips t, boats b WHERE t.tripboatid=b.boatid and b.homeharber=’Y’ );

H. Query to output the sum of all payments made to crew members on trip 87:

SELECT SUM(payment) FROM assigned_crew where tripid=’87’;

I. Query to output the CrewID of all crew members who have never been assigned

SELECT c.crewid FROM crew c,assigned_crew ac where c.crewid NOT IN(SELECT ac.crewid FROM assigned_crew ac);

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

SELECT tripid COUNT(crewid) AS NUMBER_OF_CREW_MEMBERS FROM assigned_crew GROUP BY tripid;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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