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 database and use it to answer questions

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.