1. List all the employees in the emp table. If they work for department Accounti

Question

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

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.

Explanation / Answer

Dear, 1.SQL> 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.SQL> SELECT supplier_name, description,
            CASE supplier_id
            WHEN ‘ASP’ THEN price + 1
            WHEN ‘CBC’ THEN price + 2
            ELSE price
            END
           “new_price” FROM l_foods f, l_suppliers s, WHERE f.supplier_id = s.supplier_id; 3)SQL> Select employee_id, employee_name from l_employees
             where (select top 4 creditlimit from l_employees);


4)SQL> Select employee_id, employee_name from l_employees
             where (select bottom 4 creditlimit from l_employees);

5.SQL> Select project_name,project_hours,hours_worked,date_worked             From project; 6.SQL> 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.SQL> Select supplier_id, supplier_name,sum(QUANTITY) as number_of_foods
             From l_lunch_items l
             order by supplier_id; 8.SQL> Select department_code, department_name,count(department_code) as number_of_employees
             From l_employees e,l_departments d
             where e.department_code=d.department_code
             order by department_code; 2.SQL> SELECT supplier_name, description,
            CASE supplier_id
            WHEN ‘ASP’ THEN price + 1
            WHEN ‘CBC’ THEN price + 2
            ELSE price
            END
           “new_price” FROM l_foods f, l_suppliers s, WHERE f.supplier_id = s.supplier_id; 3)SQL> Select employee_id, employee_name from l_employees
             where (select top 4 creditlimit from l_employees);


4)SQL> Select employee_id, employee_name from l_employees
             where (select bottom 4 creditlimit from l_employees);

5.SQL> Select project_name,project_hours,hours_worked,date_worked             From project; 6.SQL> 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.SQL> Select supplier_id, supplier_name,sum(QUANTITY) as number_of_foods
             From l_lunch_items l
             order by supplier_id; 8.SQL> Select department_code, department_name,count(department_code) as number_of_employees
             From l_employees e,l_departments d
             where e.department_code=d.department_code
             order by department_code;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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