Directions
Perform the following tasks below by using the correct SQL statements. Create the statements in Oracle by using the following steps (SQL > SQL commands > Enter Command). Once your command is error-free, copy and paste your statements into this assignment document. Upload this Word document for grading.
All questions should be answered with one SQL statement. Use the new Join syntax.
1. Find the columns that make up the primary key of the l_lunch_items table.
Sort the columns by position. (Do this in one statement).
2. List all the lunches that employees have signed up for. Show me first_name, last_name, lunch_date, and product_code of the items they ordered. Do not include employees who don't have a manager.
Sort the rows by product_code.
3. From the employees table, list the employee_id, last_name, and phone_number of every employee, along with the last_name and phone number of his manager. Sort the rows by employee_id. (Hint...tables can be joined to themselves...just give each table a different alias).
4. Similar to 3, but include rows for the people who do not have managers.
5. Count the number of foods supplied by each supplier. List all the suppliers. Show the supplier_id, supplier_name, and number of foods from each supplier. Sort the rows on the supplier_id.
6. Count the number of employees who work in each department. List all the departments. Show the department code, department name, and the number of employees. Sort on the dept_code.
7. Count the number of servings of each food in all the lunches in the l_lunch_items table. Show all of the foods. Return supplier id, product code, description, and the number of servings in all of the lunches.
8. Show the columns that make up the foreign key of the l_lunch_items table.
9. Find the total amount spent on lunches by each employee. Show first_name,
last_name, credit_limit, and total_price_spent in your results. Order your
answer by total_price_spent in descending order.
10. For each lunch date, list the total quantity of each food needed for that lunch. List the columns: lunch_date, food_description, full name of the supplier, total quantity, and total price.
11. Find the total amount spent on lunches by each employee. Only show the employees who spent more than their credit limit. Show first_name,
last_name, credit_limit, and total_price_spent in your results. Order your
answer by total_price_spent in descending order.
12 List all the employees in the emp table. If they work for department Accounting, then add 1200 to their salary. If they work for department Sales, then add 100 to their salary, otherwise add nothing to their salary.
13 List all the suppliers and the foods that they supply from the l_foods table. If supplier_id ASP provides the food, add 1 dollar to his price. If supplier_id, CBC supplies the food, add 2 dollars to his price. Otherwise, just show the price as is.
14 List the employee_id and employee_name of the employees form the l_employees table with the top 4 credit limits.
15 Change your answer from above to list those employees with the bottom 4 credit limits.
16 From the project, project_hours, and employee table, list the all the projects, the hours worked, and the date worked. (You will need this for the project).
17 Using the Lunches database, find the total amount spent on lunches by each employee. Show first_name, last_name, credit_limit, and total_price_spent in your results. Order your answer by total_price_spent in descending order. Only show the employees
who spent more than their credit limit (Modify question 9 from the last homework.)
18 Using the Lunches database, count the number of foods supplied by each supplier. List all the suppliers. Show the supplier_id, supplier_name, and number of foods from each supplier. Sort the rows on the supplier_id. Make sure you count all suppliers, even if they don't supply foods.
19 Using the Lunches database, count the number of employees who work in each department. List all the departments. Show the department code, department name, and the number of employees. Sort on the dept_code. Make sure you count all employees, regardless of whether they work for departments or not.