Please if possible write the queries using SQl developer 1.Queryjob_id and avera

Question

Please if possible write the queries using SQl developer

1.Queryjob_id and average salary by job_id, but only list those average salaries that are greater than 5000. (hr.employees)

2.Query job_id and the number of employees by job_id, but only list the results that the job_id ends with “CLERK”, sort the results based on job id. (hr.employees)

3.Query department_id and average salary by department. Only list those departments with average salary less than 10000 and department id greater than 50. (hr.employees)

4.Query average salaries by manager_id. But only list the lowest average salary. (hr.employees)

Hint: note in slide 8, Ch 3

5.Query the difference between themaximum salary and the minimum salary by department. (hr.employees)

Hint: note in slide 8, Ch 3

6.Insert a new job into Jobs table with job_id of ‘CISO’ and job_titleof ‘Chief Information Security Officer’.

7.Update commission_pct to 0.18 for employees whose manger has id of 100. (employees table)

8.First use the following CREATE TABLE statement to create contacts table, then import four columns (first_name,last_name, email, contact_date) data from hr.employees table into contacts table.

CREATE TABLEcontacts (fnamevarchar(20), lname varchar(20), email varchar(60), contact_date date);

9.Delete rows such that the year of contact_date is 1999. (Contacts table)

Hint: use extract () in Where clause

10.Update contact_date for those who have first name ending with ‘ar’to Pat Fay’s contact-date. (contacts table)

Hint: the first example in Slide 8 of Ch5. You need to create a subquery to get Pat Fay’s contact_date. In main statement, you set contact_date = subquery

Explanation / Answer

SELECT         job_id, avg_salary FROM hr.employees   WHERE avg_salary >5000; SELECT         job_id, number_of_employees FROM hr.employees WHERE job_id LIKE ‘%CLERK’   ORDER BY   job_id; SELECT         department_id, avg_salary_dept FROM hr.employees WHERE avg_salary_dept < 10000  AND department_id >50; SELECT manager_id, MIN(avg_sal) FROM hr.employees   WHERE manager_id IS NOT NULL GROUP BY   manager_id; SELECT         MAX(avg_sal) – MIN(avg_sal) DIFFERENCE   FROM hr.employees  GROUP BY   department_id; INSERT INTO Job (job_id, job_title)   VALUES (‘CISO, ‘Cheif Information Security Officer’); UPDATE        Employee SET commission_pct = 0.18 WHERE manager_id = 100; CREATE TABLE Contacts (fname varchar(20), lname varchar(20), email varchar(60), contact_date date);   INSERT INTO Contacts   SELECT first_name, last_name, email,contact_date FROM                hr.employees; DELETE        FROM             Contacts WHERE          contact_date = ‘1999’; UPDATE        Contacts   SET contact_date = (SELECT contact_date FROM   Contacts   WHERE       first_name= “Pat Fay”) WHERE first_name LIKE ‘%ar’;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---