EMP TABLE

subhamsingh · April 12, 2023 
CREATE TABLE EMP (
  EMPNO NUMERIC(4) PRIMARY KEY,
  ENAME VARCHAR(20) NOT NULL,
  JOB CHAR(10) NOT NULL,
  MGR NUMERIC(4),
  HIREDATE DATETIME,
  SAL NUMERIC(9,2),
  COMM NUMERIC(7,2),
  DEPTNO NUMERIC(2)
);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK','7902','1980-12-17',800,'','20');
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN','7698','1981-02-20',1600,'300','30');
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN','7698','1981-02-22',1250,'500','30');
INSERT INTO EMP VALUES (7566,'JONES','MANAGER','7839','1981-04-02',2975,'','20');
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN','7698','1981-09-28',2850,'1400','30');
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER','7839','1981-05-01',2850,'','30');
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER','7839','1981-06-09',2450,'','10');
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST','7566','1987-04-19',3000,'','20');
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT','','1981-11-17',5000,'','10');
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN','7698','1981-09-08',1500,'0','30');
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK','7788','1987-05-23',1100,'','20');
INSERT INTO EMP VALUES (7900,'JAMES','CLERK','7698','1981-12-03',950,'','30');
INSERT INTO EMP VALUES (7902,'FORD','ANALYST','7566','1981-12-03',3000,'','20');
INSERT INTO EMP VALUES (7934,'MILLER','CLERK','7782','1982-01-23',1300,'','10');

SELECT * FROM EMP; 

CREATE TABLE DEPT (
DEPTNO NUMERIC(2) PRIMARY KEY,
DNAME VARCHAR(20) NOT NULL,
LOC VARCHAR(10) 
);

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');

SELECT * FROM DEPT;
SELECT ENAME, SAL FROM EMP WHERE DEPTNO='20';
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='MANAGER';
SELECT ENAME,JOB, SAL FROM EMP WHERE JOB <> 'MANAGER';
SELECT * FROM EMP WHERE HIREDATE > '1981-03-01' AND HIREDATE < '1983-06-01'; 
SELECT ENAME FROM EMP WHERE HIREDATE LIKE'1981%';
SELECT ENAME FROM EMP WHERE ENAME LIKE'S%';
SELECT ENAME FROM EMP WHERE ENAME LIKE'____%S';
SELECT ENAME FROM EMP WHERE DEPTNO IN ('20','30');
SELECT ENAME, DEPTNO FROM EMP WHERE JOB IN ('CLERCK','SALESMAN');
SELECT ENAME FROM EMP WHERE JOB ='MANAGER'AND SAL>'2000';
SELECT SUM(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = '30';
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = '20';
SELECT MAX(HIREDATE) FROM EMP;
SELECT SUM(DEPTNO/10) FROM EMP WHERE DEPTNO= '10';
SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>8500 AND JOB <> 'SALESMAN'; 
SELECT ENAME,MIN(HIREDATE) FROM EMP;
SELECT SUM(SAL) FROM EMP WHERE JOB <> 'MANAGER';
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING COUNT(JOB) >2;
SELECT SQRT(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP WHERE JOB = 'CLERK';
SELECT TOTAL(SAL) FROM EMP WHERE HIREDATE LIKE '1981%';
UPDATE EMP SET JOB = 'CLERK', DEPTNO ='30', SAL = '1500' WHERE EMPNO = '7788';
CREATE TABLE NEW_EMP AS SELECT * FROM EMP;
ALTER TABLE NEW_EMP RENAME JOB TO SALES;
ALTER TABLE NEW_EMP ADD ADDRESS VARCHAR(10);
SELECT * FROM NEW_EMP;
DROP TABLE NEW_EMP;


CREATE TABLE EMP (
  EMPNO NUMERIC(4) PRIMARY KEY,
  ENAME VARCHAR(20) NOT NULL,
  JOB CHAR(10) NOT NULL,
  MGR NUMERIC(4),
  HIREDATE DATETIME,
  SAL NUMERIC(9,2),
  COMM NUMERIC(7,2),
  DEPTNO NUMERIC(2)
);

INSERT INTO EMP VALUES (7369,'SMITH','CLERK','7902','1980-12-17',800,'','20');
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN','7698','1981-02-20',1600,'300','30');
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN','7698','1981-02-22',1250,'500','30');
INSERT INTO EMP VALUES (7566,'JONES','MANAGER','7839','1981-04-02',2975,'','20');
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN','7698','1981-09-28',2850,'1400','30');
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER','7839','1981-05-01',2850,'','30');
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER','7839','1981-06-09',2450,'','10');
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST','7566','1987-04-19',3000,'','20');
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT','','1981-11-17',5000,'','10');
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN','7698','1981-09-08',1500,'0','30');
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK','7788','1987-05-23',1100,'','20');
INSERT INTO EMP VALUES (7900,'JAMES','CLERK','7698','1981-12-03',950,'','30');
INSERT INTO EMP VALUES (7902,'FORD','ANALYST','7566','1981-12-03',3000,'','20');
INSERT INTO EMP VALUES (7934,'MILLER','CLERK','7782','1982-01-23',1300,'','10');

SELECT * FROM EMP; 

CREATE TABLE DEPT (
DEPTNO NUMERIC(2) PRIMARY KEY,
DNAME VARCHAR(20) NOT NULL,
LOC VARCHAR(10) 
);

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');

SELECT * FROM DEPT;
SELECT ENAME, SAL FROM EMP WHERE DEPTNO='20';
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='MANAGER';
SELECT ENAME,JOB, SAL FROM EMP WHERE JOB <> 'MANAGER';
SELECT * FROM EMP WHERE HIREDATE > '1981-03-01' AND HIREDATE < '1983-06-01'; 
SELECT ENAME FROM EMP WHERE HIREDATE LIKE'1981%';
SELECT ENAME FROM EMP WHERE ENAME LIKE'S%';
SELECT ENAME FROM EMP WHERE ENAME LIKE'____%S';
SELECT ENAME FROM EMP WHERE DEPTNO IN('20','40');
SELECT ENAME, DEPTNO FROM EMP WHERE JOB IN('CLERK','SALESMAN');
SELECT ENAME FROM EMP WHERE JOB ='MANAGER' AND SAL>'2000';
SELECT ENAME FROM EMP WHERE DEPTNO='30' ORDER BY SAL DESC;
SELECT SUM(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = '30';
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = '20';
SELECT MAX(HIREDATE) FROM EMP;
SELECT SUM(DEPTNO/10) FROM EMP WHERE DEPTNO= '10';
SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>8500 AND JOB <> 'SALESMAN'; 
SELECT ENAME,MIN(HIREDATE) FROM EMP;
SELECT SUM(SAL) FROM EMP WHERE JOB <> 'MANAGER';
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING COUNT(JOB) >2;
SELECT SQRT(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP WHERE JOB = 'CLERK';
SELECT TOTAL(SAL) FROM EMP WHERE HIREDATE LIKE '1981%';
UPDATE EMP SET JOB = 'CLERK', DEPTNO ='30', SAL = '1500' WHERE EMPNO = '7788';
CREATE TABLE NEW_EMP AS SELECT * FROM EMP;
ALTER TABLE NEW_EMP RENAME JOB TO SALES;
ALTER TABLE NEW_EMP ADD ADDRESS VARCHAR(10);
SELECT * FROM NEW_EMP;
DROP TABLE NEW_EMP;
Output 




























Comments







Please sign up or log in to contribute to the discussion.




















Embed on website









To embed this program on your website, copy the following code and paste it into your website's HTML: