André Restivo

Parking SQL Solutions

  1. What is the make of all vehicles owned by the client with NIF 1232? (make)
SELECT DISTINCT make
FROM vehicle
WHERE nif = 1232
  1. What cars have ever parked in the second floor? (plate)
SELECT DISTINCT plate
FROM parking JOIN
     spot USING (number)
WHERE floor = 2
  1. In which days what there Ferraris in the park? Order your answer starting with the oldest day first. (day)
SELECT DISTINCT day
FROM vehicle JOIN
     parking USING(plate) JOIN
     spot USING (number)
WHERE make = 'Ferrari'
ORDER BY day
  1. Which cars parked on the 1st of January 2010? Order them by entry time. (entry, plate)
SELECT entry, plate
FROM parking JOIN
     spot USING (number)
WHERE day = '2010-01-01'
ORDER BY entry
  1. How many cars does the client with NIF 1232 own? (quantity)
SELECT COUNT(*)
FROM vehicle
WHERE nif = 1232
  1. How many parking spots are there above the first floor? (quantity)
SELECT COUNT(*)
FROM spot
WHERE floor > 1
  1. How many parking spots in each floor? Sort your answer by floor number. (floor, spots)
SELECT floor, COUNT(*)
FROM spot
GROUP BY floor
ORDER BY floor
  1. How many cars does each client own? Sort your answer from the client with more cars to the one with less cars. If two clients have the same number of cars, order them by their name (nif, name, quantity)
SELECT nif, name, COUNT(*)
FROM vehicle JOIN
     client USING (nif)
GROUP BY nif, name
ORDER BY COUNT(*) DESC, name
  1. Which clients have more than one car? (nif, name)
SELECT nif, name
FROM vehicle JOIN
     client USING (nif)
GROUP BY nif, name
HAVING COUNT(*) > 1
  1. Which clients have more than one car with the same make? (nif, name)
SELECT DISTINCT nif, name
FROM vehicle JOIN
     client USING (nif)
GROUP BY nif, name, make
HAVING COUNT(*) > 1
  1. What floors did not have any cars parked on the 3rd of January 2010? (floor)
SELECT DISTINCT floor
FROM spot WHERE floor NOT IN (
  SELECT floor
  FROM parking JOIN
       spot USING (number)
  WHERE day = '2010-01-03'
)
  1. What was the first client to park in each day? Order your answer by day. (day, nif, name)
SELECT day, nif, name
FROM parking JOIN
     vehicle USING (plate) JOIN
     client USING(nif)
WHERE (day, entry) IN (
  SELECT day, MIN(entry)
  FROM parking
 GROUP BY day
)
ORDER BY day