Database subject

Solve this pleasy by using Orcal SQL

This is the emp table:

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;

12. From Emp, Dept, Salgrade tables, write a query to find those employees who earn 60000 in a year or not working as an ANALYST. Return employee name, job name, (12*salary) as Annual Salary, department no, and grade. 13. From Emp, Salgrade tables, write a query to find the name and salary of the employee SCOTT. Salary should be equal to the highest salary within his or her salary group. 14. From the Emp table, write a query to find those employees who are working either as a MANAGER or an ANALYST with a salary in the range 2000, 5000 (Begin and end values are included.) without any commission. Return complete information about the employees. 15. From Emp, Dept, Salgrade tables, write a query to find those employees of SALES department come from CHICAGO or DALLAS within the grade 3,4, and 5 and experience over 40 years. Return department no, employee no, employee name, salary, department name, department location and grade. 16. From Emp, Dept tables, write a query to list employees in ascending order on department no and descending order on jobs. Return complete information about the employees