PS13

Write a SQL query that shows the empno, ename, job and total salary for all employees from the emptable, and uses a sub-query to show that earn more than the average salary. You will have to times sal by 52 weeks plus the commission *12 (commission is paid on a monthly basis) in the outer query and in the inner query (sub-query) to get the correct amounts, assuming that the value in sal represents the pay for one week.

Copy and paste your SQL code into this assignment.

b)

Sub-query with an inequality operator, and a strongly correlated sub-query.

Write a SQL query that shows the empno, ename, job and total salary for all employees from the emptable and uses a sub-query to show employees that earn more than the average salary per job, because the first query compared everyone to the overall average, now we want to break it down by job.

Format the salary returned in the result set using the to_char() function to display the numbers in the currency format. You will have to account for a six-figure salary because they are all big earners.

Copy and paste your SQL code into this assignment.

2. Run the following SQL command in the SQL command window:

create table insurance as(select empno, decode(deptno,10,'Providien HS',20,'ORB Health')AS "PROVIDER", sysdate as "CREATEDATE" from emp where deptno IN (10,20));

Sub-query with the EXISTS operator.

3. Write a SQL query that will show the empno, ename, job, deptno, loc from the emp and dept tables. Use a sub-query to check for the existence of a record in the insurance table. You will have to correlate the empno from insurance with the empno from the emp table.

Copy and paste your SQL code into this assignment.

Sub-query with the IN operator.

4. Write a SQL query that will show the empno, ename, job, hiredate for all employees that have Providien HS as their health insurance provider. Use a sub-query with the IN operator to handle the order list of empno that is returned from the subquery.

UNION operator

1. Create report for the managers of the company that shows the empno, ename, job, sal, and commission for all the employees using the data in the emp table. This is a report for the managers so we dont want the other managers to know what each of their fellow managers is earning so use the UNION operator to show the sal and comm for managers as zeroes.

Copy and paste your SQL code into this assignment.

MINUS operator

2. Write a report for the president of the company that shows the empno, ename, job, mgr, deptno from emp. Use the MINUS operator to get rid of the record in the result set for the president since he doesnt want to see any information about himself.

Copy and paste your SQL code into this assignment.

INTERSECT

3. Write a SQL query that shows empno, ename, job, mgr, deptno from emp. Use the INTERSECT operator to show only those employees who have an empno = 7902 or7839.

Copy and paste your SQL code into this assignment.