Can someone help me please! This is the feedback I received from my instructor (

Question

Can someone help me please! This is the feedback I received from my instructor (in red letters) after attempting this assignment and need help in correcting these statements so they will run correctly. Thank you very much in advance!!!

Unit 4 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.
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)
SELECT deptno;
FROM empno;
WHERE job = 7900;
You should be using subqueries to retrieve the data. Subqueries are used to retrieve data from two different fields in the same 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).
SELECT job;
WHERE ename is 'Adams';
You should be using subqueries to retrieve the data
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.
SELECT hiredate;
WHERE ename = ‘James’;
You should be using subqueries to retrieve the data
4. Insert into the spec_emp table all the employees that work in the same department as Jones. (Use the emp table )
INSERT spec_emp values;
INTO spec_emp values;
WHERE deptno = ename ‘jones’;
You should be using subqueries to retrieve the data
5. Delete the information from the spec_emp table for all employees who have the same job as Smith. (Use the emp table )
DELETE FROM spec_emp WHERE job = (SELECT job FROM employee WHERE ename = ‘Smith’);
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)
SELECT ename, empno
FROM emp
WHERE job NOT IN ( SELECT job FROM emp WHERE ename = ‘SMITH; or ename = ‘SCOTT’
Syntax errors. Remove ; and add a ) to the end of the inside Select
7. Find the suppliers whose average price of food is greater than that average price of food. (Use the l_foods table).
SELECT supplier_id FROM l_foods
WHERE Price>(SELECT AVG(Price) FROM l_foods );
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)
SELECT empno, ename, sal
FROM emp
WHERE sal < ( SELECT AVG ( sal ) FROM emp);
10. Find all the employees who have the highest salary per department. Show empno,ename,deptno,sal in your results. (emp table )
SELECT empno,ename,deptno, sal
FROM emp outer
WHERE sal = ( SELECT MAX ( sal )
FROM emp
WHERE deptno = outer.deptno;
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.
LPAD()
12. List all the employees who have not signed up for any lunches.
Here you will use the MINUS clause to retrieve the information from two different tables.

13. List all the employees who have signed up for at least one lunch.
INTERSECT between two different tables

14. List all the suppliers who did not supply any food to the lunches.
MINUS between two different tables

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.

Concat() function and a UNION clause between two different tables

Explanation / Answer

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) UPDATE emp e set e.deptno=(select deptno from emp e1 where e1.empno=7900) where e.job =(select job from emp e2 where e2.empno=7900); 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). SELECT ename, sal,deptno FROM emp WHERE job=(SELECT job FROM emp e1 where e1.ename = 'Adams'); 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. CREATE TABLE spec_emp as (SELECT ename, job, mgr, hiredate, sal, comm FROM emp where hiredate=(SELECT hiredate from emp e1 WHERE e1.ename='James')); 4. Insert into the spec_emp table all the employees that work in the same department as Jones. (Use the emp table ) INSERT INTO spec_emp(ename, job, mgr, hiredate, sal, comm) SELECT * FROM emp WHERE deptno=(SELECT deptno from emp e1 WHERE e1.ename='Jones') 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) SELECT ename, empno FROM emp WHERE job NOT IN ( SELECT job FROM emp WHERE ename = ‘Smith' or ename = ‘Scott’) 10. Find all the employees who have the highest salary per department. Show empno,ename,deptno,sal in your results. (emp table ) SELECT empno,ename,deptno, MAX(sal) FROM emp e WHERE e.sal = ( SELECT e1.deptno,MAX ( e1.sal ) FROM emp e1 WHERE e1.deptno = e.deptno GROUP BY e1.deptno) 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. LPAD() Please provide the table structure for l_employees for rest of the questions.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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