-- create
CREATE TABLE emp (
EID char(5) primary key,
NAME varchar(20) not null,
ADDR varchar(40),
CITY varchar(20),
PH_NO varchar(15) unique,
EMAIL varchar(30),
DOB date);

alter table emp add constraint utm_ngr check (addr not like '%Uttam Nagar%');

alter table emp add constraint cty check (CITY in ('Delhi','Faridabad','Noida','Gurgaon','Gurugram'));

alter table emp add constraint eml check (EMAIL like '%gmail%' or EMAIL like '%yahoo%'); 

alter table emp add constraint db check (DOB <= '01-Jan-1990');

insert into emp values ('E0001','Rajesh Kumar','E 45,Kohat Enclave','Delhi','9899543210','[email protected]','24-Sep-1982');

insert into emp values ('E0002','Pranshu Sharma','A/2,Jubilee Apartments,Rohini','Delhi','9889543210','[email protected]','19-Mar-1980');

insert into emp values ('E0003','Dheeraj Sharma','A 23,Millenuim Towers,Sector-44','Gurgaon','9823543210','[email protected]','09-Dec-1987');

alter table emp drop constraint cty;

insert into emp values ('E0004','John Mondal','BK-23,Shalimar Bagh','Delhi','9993513210','[email protected]','17-Apr-1987');

insert into emp values ('E0005','Priya Chatterjee','23-C,Prashant Vihar','Delhi','9821143210','[email protected]','16-Jun-1984');

insert into emp values ('E0006','Abdul Sattar','B-23,Galaxy Towers,Sector-11','Noida','9823553280','[email protected]','28-Jan-1980');

insert into emp values ('E0007','Ramesh Pillai','G-23C,Greater Kailash','Delhi','9862343210','[email protected]','6-Aug-1987');

select * from emp;


create table EMP_Sal(
EID char(5) foreign key references EMP (EID),
DEPT varchar(20) default 'TEMP',
DESI varchar(20),
BASIC money,
DOJ date);

alter table EMP_Sal add constraint dept_v check (DEPT in ('HR','MIS','OPS','IT ADMIN','TEMP'));

alter table EMP_Sal add constraint desi_v check (DESI in ('ASSO','MGR','VP','DIR'));

alter table EMP_Sal add constraint ba_sal check (BASIC >= 20000);

insert into EMP_Sal values ('E0001','HR','ASSO',21000,'19-Jan-2020');

insert into EMP_Sal values ('E0002','IT ADMIN','MGR',26000,'20-Feb-2019');

insert into EMP_Sal values ('E0003','OPS','VP',65000,'01-mar-2003');

insert into EMP_Sal values ('E0004','MIS','DIR',80000,'01-mar-2003');

insert into EMP_Sal values ('E0005','TEMP','ASSO',21000,'23-sep-2018');

insert into EMP_Sal values ('E0006','TEMP','ASSO',20000,'01-Feb-2023');

insert into EMP_Sal values ('E0007','OPS','ASSO',20000,'01-Jan-2022');

select * from EMP_Sal;

select * from EMP_Sal
order by BASIC Desc;

select * from EMP_Sal
order by DEPT,BASIC;

select DEPT, sum(BASIC) as salary from EMP_Sal
group by DEPT
order by salary;