CREATE TABLE DEPT $($

DEPTNO NUMBER$(2)$ NOT NULL,

DNAME CHAR$(14),$

LOC CHAR$(13),$

CONSTRAINT DEPT$\_$PRIMARY$\_$KEY 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)$ NOT NULL,

ENAME CHAR$(10),$

JOB CHAR$(9),$

MGR NUMBER$(4)$ CONSTRAINT EMP$\_$SELF$\_$KEY

REFERENCES EMP $($EMPNO$),$

HIREDATE DATE,

SAL NUMBER$(7,2),$

COMM NUMBER$(7,2),$

DEPTNO NUMBER$(2)$ NOT NULL,

CONSTRAINT EMP$\_$FOREIGN$\_$KEY FOREIGN KEY $($DEPTNO$)$ REFERENCES DEPT $($DEPTNO$),$

CONSTRAINT EMP$\_$PRIM$\_$KEY PRIMARY KEY $($EMPNO$))$;

INSERT INTO EMP VALUES $(7839,$'KING','PRESIDENT',NULL,$\text{'}17-$NOV$-1981\text{'},5000,$NULL,$10)$;

INSERT INTO EMP VALUES $(7698,$'BLAKE','MANAGER',$7839,\text{'}1-$MAY$-1981\text{'},2850,$NULL,$30)$;

INSERT INTO EMP VALUES $(7782,$'CLARK','MANAGER',$7839,\text{'}9-$JUN$-1981\text{'},2450,$NULL,$10)$;

INSERT INTO EMP VALUES $(7566,$'JONES','MANAGER',$7839,\text{'}2-$APR$-1981\text{'},2975,$NULL,$20)$;

INSERT INTO EMP VALUES $(7654,$'MARTIN','SALESMAN',$7698,\text{'}28-$SEP$-1981\text{'},1250,1400,30)$;

INSERT INTO EMP VALUES $(7499,$'ALLEN','SALESMAN',$7698,\text{'}20-$FEB$-1981\text{'},1600,300,30)$;

INSERT INTO EMP VALUES $(7844,$'TURNER','SALESMAN',$7698,\text{'}8-$SEP$-1981\text{'},1500,0,30)$;

INSERT INTO EMP VALUES $(7900,$'JAMES','CLERK',$7698,\text{'}3-$DEC$-1981\text{'},950,$NULL,$30)$;

INSERT INTO EMP VALUES $(7521,$'WARD','SALESMAN',$7698,\text{'}22-$FEB$-1981\text{'},1250,500,30)$;

INSERT INTO EMP VALUES $(7902,$'FORD','ANALYST',$7566,\text{'}3-$DEC$-1981\text{'},3000,$NULL,$20)$;

INSERT INTO EMP VALUES $(7369,$'SMITH','CLERK',$7902,\text{'}17-$DEC$-1980\text{'},800,$NULL,$20)$;

INSERT INTO EMP VALUES $(7788,$'SCOTT','ANALYST',$7566,\text{'}09-$DEC$-1982\text{'},3000,$NULL,$20)$;

INSERT INTO EMP VALUES $(7876,$'ADAMS','CLERK',$7788,\text{'}12-$JAN$-1983\text{'},1100,$NULL,$20)$;

INSERT INTO EMP VALUES $(7934,$'MILLER','CLERK',$7782,\text{'}23-$JAN$-1982\text{'},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)$;

Question $1$:

$1.$ Write a query to display deptno, dname and count of CLERKS in each department.

select

$2.$ Write a query to show empno, ename, manager$$s name and manager$$s deptno in a report form.