Sub-query with an inequality operator, and a weakly correlated sub-query.
1. a)
Write a SQL query that shows the empno, ename, job and total salary for all employees from the emp table, 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 emp table 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.
Copy and paste your SQL code into this assignment.