Note: Question 1 and 6 have been answered and question 5 can be skipped until th

Question

Note: Question 1 and 6 have been answered and question 5 can be skipped until the project is complete. Thanks for all you do

Unit 5 Assignment 1
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.
1. 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.
Answer = SELECT e.empno, e.ename, e.deptno, d.dname, e.sal,
DECODE (e.deptno,10,e.sal + 1200,
30,e.sal + 100,
e.sal) as new_salary
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);

2. 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.
3. List the employee_id and employee_name of the employees form the l_employees table with the top 4 credit limits.
4. Change your answer from above to list those employees with the bottom 4 credit limits.
5. 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).

6. 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.)

Answer = SELECT e.first_name, e.last_name, e.credit_limit, SUM(i.quantity * f.price) AS total_price_spent
FROM l_employees e ,
l_lunches l,
l_lunch_items i,
l_foods f
WHERE
e.employee_id = l.employee_id
AND l.lunch_id = i.lunch_id
AND i.supplier_id = f.supplier_id
AND i.product_code = f.product_code
HAVING sum(i.quantity * f.price) > e.credit_limit
GROUP BY e.first_name, e.last_name, e.credit_limit
ORDER BY total_price_spent DESC


7. 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.
8. 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.

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

Explanation / Answer

3) Select employee_id, employee_name from l_employees where (select top 4 creditlimit from l_employees) 4) Select employee_id, employee_name from l_employees where (select bottom 4 creditlimit from l_employees)

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.