1. Change the department number of all employees with employee 7900’s job title
ID: 3626673 • Letter: 1
Question
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,1.SQL> update emp set jobtitle = 7900;
3.SQL> create table spec_emp(ename char(20),
jobtitle char(30), mgr char(30), hiredate date, sal float(20));
4.SQL> Insert into spec_emp values(‘mason’,’hr’,’adg’,jan-1-2011,10000.00)
Insert into spec_emp values(‘smith,’gf’,’fgr’,feb-09-2000,20000)
5.SQL> Delete from emp where jobtitle=’smith’;
6.SQL> Select name, depno from spec_emp where jobtitle =’smith’or ‘scott’;
7.SQL> Select suppliers from food where AVG(food) > AVG(pricefood);
9.SQL> select name ,sal, emp from emp where sal < AVG(sal);
10.SQL> select name,sal,depno from emp where sal=MAX(sal) orderby depno;
Note:please post remaining questions in another post....
1.SQL> update emp set jobtitle = 7900;
3.SQL> create table spec_emp(ename char(20),
jobtitle char(30), mgr char(30), hiredate date, sal float(20));
4.SQL> Insert into spec_emp values(‘mason’,’hr’,’adg’,jan-1-2011,10000.00)
Insert into spec_emp values(‘smith,’gf’,’fgr’,feb-09-2000,20000)
5.SQL> Delete from emp where jobtitle=’smith’;
6.SQL> Select name, depno from spec_emp where jobtitle =’smith’or ‘scott’;
7.SQL> Select suppliers from food where AVG(food) > AVG(pricefood);
9.SQL> select name ,sal, emp from emp where sal < AVG(sal);
10.SQL> select name,sal,depno from emp where sal=MAX(sal) orderby depno;
Note:please post remaining questions in another post....
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.