really need help with this assignment Directions Perform the following tasks bel
ID: 3626617 • Letter: R
Question
really need help with this assignmentDirections
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,
Correct answers
1. Need to create table for update query
Create table emp(name char(20),department num(20),jobtitle num(20),sal float(20),avgsal float(20))
Insert into emp values(mason,1790,software engineer,20000,2000000)
Insert into emp values(san,1789,hr,10000,2000000)
Insert into empvalues(sarnia,1786,hr,25000,200000)
update emp set jobtitle = 7900
2.Create table list(ename char(20),sal float(20),depno num(20),title char(20))
Insert into list values(‘san’,20,000,1700,hr)
Insert into list values(‘mahi’,50,000,1600,software engineer)
Update list set title = ‘software engineer’
Output:
Ename sal depno title
San 10000 1700 softwareengineer
Mahi 50000 1600 software enginer
3.create table spec_emp(ename char(20),jobtitle char(30),mgr char(30),hiredate date, sal float(20))
Need to insert fields in the below format
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.Delete from emp where jobtitle=’smith’
6.Select name, depno from spec_emp where jobtitle =’smith’or ‘scott’
7.create table food(avgfood float(20),supplier char(20),pricefood num(20))
Select suppliers from food where avgfood > pricefood
9.select name ,sal, emp from emp where sal < avgsal
10.select name,sal,depno from emp where sal>2000000
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.