How would you construct an SQL statement for all those who have the surname "Brown" and have booked cars?

Question

Basically, i am wanting to construct an SQL statement where i want to find those who have the surname "brown" and have booked cars where the surname is its own column in the "customers table"

Would it be something like:

SELECT Surname
FROM Customers
WHERE Surname = Brown AND have booked cars

Thanks

Answer 1

It entirely depends on your table structure.



All SQL select statements are made up of 3 basic parts.



The "select" is the part that you would declare what columns you wish to view (* Means all columns)



The "From" declares what table/s you want to get the data from



The Clauses "Where, And, Having" is where you define your criteria.



If you have all your data in one table you would use something like below. (Assuming booking is a Yes or No)



Select *

from customers

where upper(surname) = 'BROWN'

and booking = 'Yes'



I made the surname convert to upper as you may have BROWN, Brown, brown, bRoWn in your database so this matches the string not the casing



If you have a separate customers and bookings table it would look something like below. Assuming both tables have some commonality (I am assuming both have customerid)



Select *

from customers, bookings

where customers.customerid = bookings.customerid

and upper(customers.surname) = 'BROWN'

and bookings.booking = 'Yes'



Or



Select *

from customers

where upper(surname) = 'BROWN'

and customerid in (select customerid from bookings)



I gave 2 ways to do the statement if there are multiple tables and both would return all the customer data if there is a booking. HOWEVER if you want to see some data from the bookings table you would use the first as you can define columns e.g. Customers.*, Bookings.Booking date



If you want more information in the form of a tutorial please see. http://www.mikespraggett.co.uk/Pages/HowTo/HowToViewer.aspx?Article=7&Title=Selecting%20Data

Answer 2

If you have a key field for the customer - which you've used in a second table which has bookings. You can use something like



select *

from customer c

where surname = 'Brown'

and exists ( select 1 from bookings b

where c.customerid = b.customerid );



( This is Oracle - so you may have to tweak it for your database ).

So what your doing is saying that the surname is Brown in the customer table, and there is a record in the bookings table for their customerid.

Answer 3

SELECT FROM CUSTOMERS

WHERE Surname = Brown AND BookedCar = true



This returns a subset of customer records contain all elements in the CUSTOMER schema, such that the Surname element is Brown and the BookedCar element is set to true.



Are both Surname and BookedCar in the same relation or separate relations (tables)? If separate tables, you have to JOIN them first:



SELECT FROM JOIN Customers WITH Bookings

USING Surname

WHERE Surname = Brown AND BookedCar = true

Answer 4

SELECT *

FROM Customers

WHERE Surname = 'Brown' AND booked_cars = 'Yes'

Assuming booked_Cars is a field name with Yes/No data.