-- THIS IS THE FAMOUS ORACLE DEMO DATABASEEXAMPLE -- I GAVE YOU THE DDL TO CREATE 4 TABLES (ACTUALLY, YOU ARE USING MOSTLY TWO OF THEM) -- READ THE CREATE TABLE STATMENTS AND THE INSERT STATEMENTS TO UNDERSTAND WHAT THEY ARE DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE BONUS; DROP TABLE SALGRADE; CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2)); INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); CREATE TABLE BONUS (ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999); -- THEN ANSWER THESE QUESTIONS. -- 1. DISPLAY THE NAME, SAL, & ANNUAL SAL AFTER ADDING 1000 TO ALL SAL similar to thies -- ENAME SAL ADDITIONAL_SALARY ANNUAL_SALARY -- SMITH 800 1800 1600 -- ALLEN 1600 2600 31200 --- 2. DISPLAY THE TOTAL INCOME(COMM+SAL) OF ALL EMP. TRICKY... some comm is NULL.. -- 3. DISPLAY THE DETAILS OF EMP WHO JOINED AFTER 01/02/81 -- 4. DISPLAY THE DETAILS OF SALESMEN & CLERKS -- 5. DISPLAY THE DETAILS OF EMP IN DEPT 30 WHO HAVE JOINED BEFORE 1ST JAN 1982 -- 6. DISPLAY THE DETAILS OF EMP WHO EITHER REPORT TO 7698 OR EARN LESS THAN 2000 -- 7. Display the name, salary and commission of employees whose commission is greater than their salary. -- 8. Display the emp who are not entitled to get commission -- 9. Display the emp who have a deptno -- 10. List the employees in dept 30 in order of their salaries. -- 11. Order all employees by job, and within job put them in the descending order of their salary. -- 12. What are the minimum and maximum sal of each department -- 13. Show the min and max of salary of the departments that have its -- minimum salary less than 5,000. -- 14. How many employees are salesmen; -- 15. Write an sql to produce the following output (one table with one row): -- EMPLOYEE_COUNT TOTAL_SALARY TOTAL_COMMISSION MIN_COMPENSATION MAX_COMPENSATION -- 14 29025 2200 800 5000 -- 16. Write an sql to show the employee name, job and dept location in THREE ways. -- 17. Run Explain Plan of the three SQLs in question(16) and explain. -- 18. Run Explain Plan for Select * from Emp, Dept;