Search code examples
sqlrelational-algebra

List the trucks that have only been used to carry fruit

Assume a database with schema

TRUCK  (license-plate, maker, model, year, mileage, max-load)
DRIVER (driver-id, name, age, address, license)
TRIP   (license-plate, driver-id, date, origin, destination, miles, cargo, cost)

Write Relational Algebra and SQL to list the license plate of trucks that have only been used to carry fruit.

I come up with a solution where

Relational Algebra:

enter image description here

SQL:

enter image description here

is not the correct answer. Why is that?

Solution

  • One solution is to check if a truck shipped fruit and no other type of cargo using the EXISTS operator:

    SELECT license-plate
FROM truck
WHERE EXISTS (
    SELECT 1
    FROM trip
    WHERE trip.license-plate = truck.license-plate
    AND trip.cargo = 'fruit'
)
AND NOT EXISTS (
    SELECT 1
    FROM trip
    WHERE trip.license-plate = truck.license-plate
    AND trip.cargo <> 'fruit'
)