How to write a SQL statement showing that cars have no reservations

Victor Thomas 0
Viewed 24

The is the question: write a SQL statement that displays all vehicles that have no bookings from customers in Gothenburg (NOTE! the question must also show the vehicles for which it does not exist some bookings in the Booking table). Show Registration Number and Model as well as mileage in the result. The list is sorted in ascending order by Miltal.

My SQL code:

Select * 
from fordon Registreringsnummer, Modell, Miltal ASC ;

select from kunder 
where not in Gothenburg

Is there someone that can help me to correct the statement?

database sql
1 Answers

Assuming that you have this table structure on table Cars:

regno nvarchar(50) , model nvarchar(50), mileage int

sample rows

regno   Model   mileage
455 VW Golf 150000
688 Vauxhall Corsa  52000
266 Skoda Fabia 250000
293 Fiat Punto  260000

and this structure on table Bookings:

regnumber nvarchar(50), city nvarchar(50), customerid int

customerid  city    regnumber
1   Gothenburg  688
2   London  266
5   London East 293
6   Gothenburg  455

You want to show all vehicles that do not have a booking from customers in Gothenburg.

Here is my code for this:

SELECT regno ,Model ,mileage
  FROM cars where regno not in 
  (select regnumber from bookings where city='Gothenburg')
  order by mileage asc

Results:

regno   Model   mileage
266 Skoda Fabia 250000
293 Fiat Punto  260000
admiri0
Related