Directions Perform the following tasks below by using the correct SQL statements

Question

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

1. Change the department number of all employees with employee 7900’s job title to employee 7900’s current department number. (use the emp table)

2. Generate a listing showing the ename, salary, and department number for all employees that have the same title as the employee Adams. (Use the emp table).

3. Create a new table spec_emp that includes the ename, job, mgr, hiredate, sal, and comm columns from the emp table. Include only those employees that have the same hiredate as James.

4. Insert into the spec_emp table all the employees that work in the same department as Jones. (Use the emp table )

5. Delete the information from the spec_emp table for all employees who have the same job as Smith. (Use the emp table )

6. Display the employee number and name for each employee who job is not the same as that of any employee named Smith or Scott. (Use the emp table)

7. Find the suppliers whose average price of food is greater than that average price of food. (Use the l_foods table).



8. Find all the employees who have a higher credit limit than the average credit limit of their particular department. Show employee_id, first_name, last_name, credit_limit, and dept_code in your results. (l_employees table)

9. Find all employees who make less than the average salary. Show emp, ename, sal in your results. (emp table)

10. Find all the employees who have the highest salary per department. Show empno,ename,deptno,sal in your results. (emp table )

11. Using the l_employees table, create a query that shows the management hierarchy. Structure the query so that it will always start with the employee who does not have a manager. Show employee_id, first_name, last_name in your results. Pad the results so that each successive level is indented.

12. List all the employees who have not signed up for any lunches.

13. List all the employees who have signed up for at least one lunch.

14. List all the suppliers who did not supply any food to the lunches.

15. List all the names from the l_employees and emp table. (Just the names only). Concatenate the first_name and last_name from the l_employees table to get one name.

Explanation / Answer

Dear User,
1)      UPDATE employee SET deptno = (SELECT deptno FROM employee WHERE empno = 7900) WHERE job = (select job FROM employee WHERE empno = 7900)
2)      SQL> Select ename, salary, and department number Fom emp where ename='Adams'
3)      CREATE TABLE spec_emp AS (SELECT ename, job, mgr, hiredate,sal,comm. FROM employee WHERE hiredate = (SELECT hiredate FROM employee WHERE ename = ‘james’))
4)      SELECT * INTO spec_emp FROM employee WHERE dept_code = (SELECT deptcode FROM employee WHERE ename = ‘jones’)
5)      DELETE FROM spec_emp where job = (SELECT job FROM employee WHERE ename = ‘smith’) Hope this will help you. Please post the remaining questions in other posts

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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