For the Pizza Delivery Shop DB, (see Appendix), write SQL queries to answer each of the following questions.

a) List all the last names, phone numbers and zip codes of the customers. b) List information about only customers from zip code 11483. c) List all the customers that have no first address entered in the database d) List all products that are priced higher than $10 e) List all products that are priced between $5 and $9. f) List all customers whose last name starts with K g) What is the average price of a product? h) What is the highest price of a product? i) What is the total due for order-2003? j) Join the Product and OrderDetail tables so that the result contains the product name, product unit size, and product unit price, as well as the charged price. Do it for order-2000. k) List all the order and order details for each order made by customer with phone number 2127895450. l) Change the price of breadsticks to $3.00 m) Process the order for a new customer. (determine the process stages to actually satisfy a new customer, from the view point of DB processing, activities and manipulations.)

2. For the Company Projects DB, (see Appendix), write SQL queries to answer perform each of the following actions. a) For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. b) Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees. Can we specify this query in SQL? Why or why not? c) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. d) Retrieve the names of all employees whose supervisors supervisor has 888665555 for Ssn. e) Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. f) A view that has the department name, manager name, and manager salary for every department. g) A view that has the employee name, supervisor name, and employee salary for each employee who works in the Research department. h) A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project. i) A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it. j) Retrieve the names of all employees who do not have supervisors. k) Retrieve the names of employees who have no dependents. l) Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project. m) List the names of all employees who have a dependent with the same first name as themselves. n) Find the names of all employees.

3. Do the following problems. a. Choose 3 questions from Problem (1) and reformulate these using Relational Algebra queries. b. Choose 3 questions from Problem (2) and reformulate these using Relational Algebra based queries.

Appendix

1. Pizza Delivery Shop DB design. Relational schemas. Customer(CustomerPhoneKey [PK], LastName, FirstName, Address1, Address2, City, State, Zip). CustomerOrder(OrderKey [PK], OrderDate, OrderTime, CustomerPhoneKey [FK], EmployeeKey[FK]). Employee(EmployeeKey [PK], LastName, FirstName, HireDate). Product(ProductKey [PK], ProductName, UnitSize, UnitPrice). OrderDetail(OrderDetailKey [PK], OrderKey [FK], ProductKey [FK], OrderDetailQuantity, OrderDetailPriceCharged).

2. Company Projects DB. Relational schemas: Employee(Fname, Minit, Lname, Ssn [PK], Bdate, Address, Sex, Salary, Super_ssn, Dno). Department(Dname, Dnumber [PK], Mgr_ssn, Mgr_start_date). DepartmentLocation(Dnumber [PK], Dlocation [PK]). Project(Pname, Pnumber [PK], Plocation, Dnum). WorksOn(Essn [PK], Pno [FK], Hours). Dependent(Essn [FK], Dependent_name [PK], Sex, Bdate, Relationship).