Note: Tables included, and answers (1, 2, 3, 4, 5, 6, 8,9,10, and 11) have been provided. I just…
Note: Tables included, and answers (1, 2, 3, 4, 5, 6, 8,9,10, and 11) have been provided. I just need the remaining answered 7.
Thanks for the help
Unit 4 Assignment 2
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 > EnterCommand). 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).
Answer = SELECT lunch_id FROM l_lunch_items ORDER BY 1 ASC;
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 includeemployees who don't have a manager.
Sort the rows by product_code.
Answer = SQL> Select first_name, last_name, lunch_date,product_code
From l_employees e,l_lunches l ,l_lunch_items li
where e.employee_id=l.employee_id and l.lunch_id=li.lunch_id and e.manager_id<>null
order 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 therows by employee_id. (Hint…tables can be joined to themselves…just give each table a different alias).
Answer = .SQL> SELECT department_code, department_name,COUNT(*) as number_of_employees.
FROM l_employees e,l_departments d
where e.department_code=d.department_code
GROUP BY department_code;
4. Similar to 3, but include rows for the people who do not have managers.
Answer = SELECT employee_id, last_name,phonenumber FROM employee
WHERE manager_id is null ORDER BY employee_id;
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 therows on the supplier_id.
Answer = SQL> Select supplier_id, supplier_name, count(supplier_id) as number_of_foods
FROM l_foods f,l_suppliers s
where f.supplier_id=s.supplier_id
order by 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 onthe dept_code.
Answer = .SQL> SELECT department_code, department_name,COUNT(*) as number_of_employees.
FROM l_employees e,l_departments d
where e.department_code=d.department_code
GROUP BY department_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, andthe number of servings in all of the lunches.
8. Show the columns that make up the foreign key of the l_lunch_items table.
Answer =
SELECT DISTINCT b.column_name, b.position
FROM user_constraints a JOIN user_cons_columns b ON
a.table_name = b.table_name AND a.constraint_name = b.constraint_name
WHERE
a.table_name = ‘L_LUNCH_ITEMS‘
AND a.constraint_type =’R’
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.
Answer = SELECT a.first_name, a.last_name, credit_limit, SUM(c.quantity * d.price) AS total_price_spent
FROM l_employees a JOIN l_lunches b ON a.employee_id = b.employee_id JOIN l_lunch_items c ON b.lunch_id = c.lunch_id JOIN l_foods d ON c.supplier_id = d.supplier_idAND c.product_code = d.product_code
GROUP BY a.first_name, a.last_name, a.credit_limit
ORDER BY total_price_spent DESC
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, totalquantity, and total price.
Answer = SELECT a.date_entered, c.description AS food_description, d.supplier_name, SUM (b.quantity) AS total_quantity, SUM(c.price*b.quantity) AS total_price
FROM l_lunches a JOIN l_lunch_items b ON a.lunch_id = b.lunch_id JOIN l_foods c ON b.supplier_id = c.supplier_id AND b.product_code = c.product_code JOIN l_suppliers dON c.supplier_id = d.supplier_id
GROUP BY a.date_entered, c.description, d.supplier_name
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.
Answer = SELECT a.first_name, a.last_name, credit_limit, SUM(c.quantity * d.price) AS total_price_spent
FROM l_employees a JOIN l_lunches b ON a.employee_id = b.employee_id JOIN l_lunch_items c ON b.lunch_id = c.lunch_id JOIN l_foods d ON c.supplier_id = d.supplier_idAND c.product_code = d.product_code
HAVING SUM(quantity * price) > a.credit_limit
GROUP BY a.first_name, a.last_name, a.credit_limit
ORDER BY total_price_spent DESC
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPT_CODE HIRE_DATE CREDIT_LIMIT PHONE_NUMBER MANAGER_ID
201 SUSAN BROWN EXE 01-JUN-98 30 3484 –
202 JIM KERN SAL 16-AUG-99 25 8722 201
203 MARTHA WOODS SHP 02-FEB-09 25 7591 201
204 ELLEN OWENS SAL 01-JUL-08 15 6830 202
205 HENRY PERKINS SAL 01-MAR-06 25 5286 202
206 CAROL ROSE ACT – – – –
207 DAN SMITH SHP 01-DEC-08 25 2259 203
208 FRED CAMPBELL SHP 01-APR-08 25 1752 203
209 PAULA JACOBS MKT 17-MAR-99 15 3357 201
210 NANCY HOFFMAN SAL 16-FEB-07 25 2974 203
SUPPLIER_ID PRODUCT_CODE MENU_ITEM DESCRIPTION PRICE PRICE_INCREASE
ASP FS 1 FRESH SALAD 2 .25
ASP SP 2 SOUP OF THE DAY 1.5 –
ASP SW 3 SANDWICH 3.5 .4
CBC GS 4 GRILLED STEAK 6 .7
CBC SW 5 HAMBURGER 2.5 .3
FRV BR 6 BROCCOLI 1 .05
FRV FF 7 FRENCH FRIES 1.5 –
JBR AS 8 SODA 1.25 .25
JBR VR 9 COFFEE .85 .15
VSB AS 10 DESSERT 3 .5
LUNCH_ID LUNCH_DATE EMPLOYEE_ID DATE_ENTERED
1 16-NOV-11 201 13-OCT-11
2 16-NOV-11 207 13-OCT-11
3 16-NOV-11 203 13-OCT-11
4 16-NOV-11 204 13-OCT-11
6 16-NOV-11 202 13-OCT-11
7 16-NOV-11 210 13-OCT-11
8 25-NOV-11 201 14-OCT-11
9 25-NOV-11 208 14-OCT-11
12 25-NOV-11 204 14-OCT-11
13 25-NOV-11 207 18-OCT-11
SUPPLIER_ID SUPPLIER_NAME
ARR ALICE &RAY'S RESTAURANT
ASP A SOUP PLACE
CBC CERTIFIED BEEF COMPANY
FRV FRANK REED'S VEGETABLES
FSN FRANK &SONS
JBR JUST BEVERAGES
JPS JIM PARKER'S SHOP
VSB VIRGINIA STREET BAKERY
DEPT_CODE DEPARTMENT_NAME
ACT ACCOUNTING
EXE EXECUTIVE
MKT MARKETING
PER PERSONNEL
SAL SALES
SHP SHIPPING
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!
NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.
