1. Write a SQL query that uses a sub-query to show the empno, ename, job and total salary for all employees from the emp table that earn more than the average salary. You will have to times sal by 52 weeks 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.

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.

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.

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.