SQL Test Question

[SlowRoasted]

No this isn't for school so don't think I'm cheating.

Any help would be greatly appreciated.

The CEO comes in and asks you for a report that shows the number of orders and total amount for Perez’s Moes restaurant in the month of December. Using the following table fields to create a series of or one SQL statement that would give you the information required.

Restaurant
RestaurantID
Restaurant
Address
City
State

Orders
OrderID
RestaurantID
OrderDate
OrderTime
OrderAmount

OrderDetail
OrderDetailID
OrderID
ItemID
ItemAmount

I had something like this



Display all the orders from December

SELECT * FROM Orders WHERE (RestaurantID = ‘Perez’) AND (OrderDate < ‘2006-01-01’) AND (OrderDate > ‘2005-11-30’)

Display the total number of orders in December

SELECT SUM(OrderAmount) From Orders WHERE (RestaurantID = ‘Perez’) AND (OrderDate < ‘2006-01-01’) AND (OrderDate > ‘2005-11-30’)


But Im assuming the the restaurantid is perez, which it may not be. How do you set a variable in SQL? If I had php it would be no problem.

[bwh2]

well, you've got duplicate field names in your first table. so i would assume that one of them is for the restaurant name. restaurant ID is probably an integer. can you do a select count(OrderAmount) in mysql? i know you can in tsql, but i would look into it for mysql.

[λ]

You could do something like this:



SELECT SUM(Orders.OrderAmount) FROM Orders, Restaurant
WHERE Orders.RestaurantID = Restaurant.RestaurantID
AND Restaurant.Restaurant = 'Perez'
AND Orders.OrderDate > '2005-11-30'
AND Orders.OrderDate < '2006-01-01'


May or may not work, but that's what I'd do :)

[SlowRoasted]

thx, it doesn't specify what type of database is being used.

[bwh2]

http://www.w3schools.com/sql/sql_functions.asp

[SlowRoasted]

Thanks, that's a good reference.