***********PLEASE USE THIS TABLES SQL************

DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE SALGRADE; create table dept( deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) );

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 emp( empno number(4,0), ename varchar2(10), job varchar2(9), mgr number(4,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(2,0), constraint pk_emp primary key (empno), constraint fk_deptno foreign key (deptno) references dept (deptno) ); 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 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);

commit;

10. Write a query to display the employees of Deptno 20 or 10 joined in the year 1981. 11. Write a query to display the employees number, name, salary, department name of all the 'MANAGERS' and 'CLERKS' working in New York, Dallas with an experiences more than 40 years without receiving the comm. Sort them as ascending of location. 12. Write a query to display the employees whose Jobs are same as Smith. 13. Write a query to display the employees of Deptno 10 whose jobs are same as Deptno 20. 14. Write a query to display the employees whose salary is same as MILLER or ALLEN, sort it based on salary in descending order. 15. Write a query to display any jobs of deptno 10 , those that are not found in deptno 20. 16. Write a query to find the highest paid employee of sales department. 17. Write a query to find the total salary given to the Manager. 18. Write a query to display all managers no and the number of employees working for those managers. Sort them as ascending of manager number. 19. Write a query to display the department details where at least two employees are working in the department. 20 . Write a query to display the employees name, job, department name, location for those who are working as managers. 21. Write a query to display the employees name, salary, commission. For those employees whose net pay is greater than or equal to any other employee salary of the company. 22. Write a query to display the name of department where highest number of employees are working. 23. Write a query to find out how many Managers are their in the company. 24 . Write a query to display the employees name, Job, Annual Salary, department no, department name and grade who earn more than 30000 a year or who are not CLERKS. 25. Write a query to display the employees details for whose grade are greater than the grade of MILLER