CREATE TABLE EMP
(
EMPNO NUMERIC(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATE,
SAL int,
COMM int,
DEPTNO int
);
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);
Assignment:
----------
Display all the information of the EMP table?
--- select * from emp
Display unique Jobs from EMP table?
--- select distinct job from emp
List the emps in the asc order of their Salaries?
--- select * from emp order by sal
List the details of the emps in asc order of the Dptnos and desc of Jobs?
Display all the unique job groups in the descending order?
Display all the details of all ‘MANAGER’
Display all the details of the emps whose Comm. Is more than their Sal.
List the emps who are either ‘CLERK’ or ‘ANALYST’ in the Desc order.
List the emp who are working for the Deptno 10 or20.
List the emps who are joined in the year 81.
List the emps Who Annual sal ranging from 22000 and 45000.
List the Enames those are having five characters in their Names.
List the Enames those are starting with ‘S’ and with five characters.
List the emps those are having four chars and third character must be ‘r’.
List the Five character names starting with ‘S’ and ending with ‘H’.
List the emps whose Sal is four digit number ending with Zero.
List the emps who does not belong to Deptno 20.
List all the emps except ‘PRESIDENT’ & ‘MANAGER” in asc order of Salaries.
List the emps whose Empno not starting with digit78.
List the emps who are working under ‘MANAGER’.
CREATE TABLE DEPT
(DEPTNO INT,
DNAME VARCHAR(14),
LOC VARCHAR(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');