Assignment_1.txt

DROP TABLE EMP;

DROP TABLE DESIGNATION;

DROP TABLE DEPARTMENT;


CREATE TABLE EMP 
(
EMP_CODE varchar(5) PRIMARY KEY,
EMP_NAME varchar(20),
DEPT_CODE varchar(5),
DESIG_CODE varchar(5),
SEX varchar(1),
ADDRESS varchar(25),
CITY varchar(20),
STATE varchar(20),
PIN varchar(6),
BASIC Number,
JN_DT Date
);



CREATE TABLE DESIGNATION
(
	DESIG_CODE 	varchar(5) PRIMARY KEY,
	DESIG_DESC	varchar(20)
);




CREATE TABLE DEPARTMENT
(
	DEPT_CODE 	varchar(5) PRIMARY KEY,
	DEPT_NAME	varchar(15)
);


INSERT INTO DESIGNATION(DESIG_CODE,DESIG_DESC)
VALUES ('MAN','Manager');


INSERT INTO DESIGNATION(DESIG_CODE,DESIG_DESC)
VALUES ('OFF','Officer');


INSERT INTO DESIGNATION(DESIG_CODE,DESIG_DESC)
VALUES ('EXEC','Executive');


INSERT INTO DESIGNATION(DESIG_CODE,DESIG_DESC)
VALUES ('CLERK','Clerk');


INSERT INTO DESIGNATION(DESIG_CODE,DESIG_DESC)
VALUES ('HELP','Helper');


INSERT INTO DEPARTMENT(DEPT_CODE,DEPT_NAME)
VALUES ('PER','PERSONNEL');



INSERT INTO DEPARTMENT(DEPT_CODE,DEPT_NAME)
VALUES ('PUR','Purchase');



INSERT INTO DEPARTMENT(DEPT_CODE,DEPT_NAME)
VALUES ('FIN','Finance');



INSERT INTO DEPARTMENT(DEPT_CODE,DEPT_NAME)
VALUES ('RESCH','Research');

ALTER TABLE EMP ADD FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE);


ALTER TABLE EMP
 ADD FOREIGN KEY (DESIG_CODE) REFERENCES DESIGNATION(DESIG_CODE);


INSERT INTO EMP(
EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC,
JN_DT
)
VALUES ('RAJ001','RAJONYA','PUR','CLERK','M','Balur','Kol','WB','670','0',GETDATE());
SELECT * FROM EMP 

WHERE DEPT_CODE is NULL;
SELECT * FROM EMP WHERE BASIC = 0;

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A1','RAJONYA','PUR','CLERK','M','Balur','Kol','WB','670','10000');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A2','INDRA','RESCH','OFF','M','Jorashanko','Kol','WB','670','100000');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A3','JANA','PER','EXEC','M','HOW','Kol','WB','6700','98766');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A4','AKASH','FIN','HELP','M','RANIKUTHI','Kol','WB','6700','0');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN)

VALUES ('A5','PRITHWISH','RESCH','MAN','M','HOW','Kol','WB','6700');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A6','SOULIB','FIN','M','HOW','Kol','WB','6700','98766');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A7','ANURAN','M','HOW','Kol','WB','6700','98766');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A8','Madhurima','PUR','MAN','F','Balur','Kol','WB','670','100098');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A9','RAJkonya','PER','EXEC','F','Balur','Kol','WB','670','676750');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A10','Sneha','RESCH','EXEC','F','Balur','Kol','WB','670','78000');

INSERT INTO EMP(EMP_CODE,
EMP_NAME,
DEPT_CODE,
DESIG_CODE,
SEX,
ADDRESS,
CITY,
STATE,
PIN,
BASIC)

VALUES ('A11','Aayushi','FIN','OFF','F','SOUTH','Gurgaon','Delhi','2234','87600');


					

SELECT DEPT_CODE, MIN(BASIC) AS "Lowest salary"
FROM EMP
GROUP BY DEPT_CODE;

SELECT DEPT_CODE, MAX(BASIC) AS "Highest salary"
FROM EMP
GROUP BY DEPT_CODE;

SELECT DEPT_CODE, AVG(BASIC) AS "MEDIAN salary"
FROM EMP
GROUP BY DEPT_CODE;

SELECT DEPT_CODE,COUNT(DISTINCT CITY) AS "No. of Emps"
FROM EMP
GROUP BY DEPT_CODE


SELECT DISTINCT CITY FROM EMP;

SELECT * 
FROM EMP 
ORDER BY DEPT_CODE,BASIC;

SELECT COUNT(*) AS "No. of Employees"
FROM EMP
ORDER BY DEPT_CODE,CITY
GROUP BY DEPT_CODE,CITY;