Using BurgerBay relational model, write the SQL statements answering the following queries : a. Display the name and city of all suppliers. SELECT SupplyName, City FROM Suppliers; b. Display the EmployeeID, employee name and number of years employed of all the Managers employed for more than 5 years. Display your results from the most recent to the most experienced managers. c. Count the number of restaurants located in each of the following states : California (CA) , Oregon (OR) and Nevada (NV). Assume that states are stored in the database using two characters code, meaning CA, OR and NV. Make sure your query displays the number of restaurants for each state; NOT the number of restaurants for all states combined. d. Using Subqueries, display the name and contract fee of the advertisement agencies working with restaurants located in the state of California.
Assume the following relational model representing the design for BurgerBay
1.
Using BurgerBay relational model, write the SQL statements to create the tables Supply, Schedule and Employees.
Use the following information to define your attributes.
SupplierID : integer ; Each supplier must provide supplies
RestaurantLicense : integer ; Each restaurant must have supplies ;
Each restaurant must have an employee schedule
ScheduleID: integer
EmployeeID: integer
Day: date format ; required value
StartTime: integer; required value
EndTime: integer; required value
EmployeeName: 50 characters maximum; required value
NbrYearEmployed: integer; optional value
Employee_Type: 1 character; required value; only allowed values are ‘C’ (for Cook) and ‘M’ (for Manager)
2.
Using BurgerBay relational model, write the SQL statements answering the following queries :
a. Display the name and city of all suppliers.
SELECT SupplyName, City
FROM Suppliers;
b. Display the EmployeeID, employee name and number of years employed of all the Managers employed for more than 5 years. Display your results from the most recent to the most experienced managers.
c. Count the number of restaurants located in each of the following states : California (CA) , Oregon (OR) and Nevada (NV).
Assume that states are stored in the database using two characters code, meaning CA, OR and NV.
Make sure your query displays the number of restaurants for each state; NOT the number of restaurants for all states combined.
d. Using Subqueries, display the name and contract fee of the advertisement agencies working with restaurants located in the state of California.
e.
Using Join queries, display the name and contract fee of the advertisement agencies working with restaurants located in the state of California.
f. Using the method of your choice, display the EmployeeID, employee name , schedule day ,start time and end time of the employees who work for a restaurant located in the city of San Francisco.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
