ASS2_

create table department(department_id int, department_name varchar(30));

create table manages(mgrName varchar(30) unique not null, workId int identity primary key, empId int , cmpId int, sal int, modifiedDate date default convert(date, getdate()));


create table employee(empId int identity primary key, emName varchar(30) not null, street varchar(30),city varchar(30), mgrId int foreign key references manages(workId), dptId int, isActive varchar(10) default 'active', doj date default convert(date, getdate()) );

create table company( cmpId int identity primary key, cmpName varchar(30), city varchar(30)) ;


create table works(workId int ,empId int foreign key references employee(empId), cmpId int foreign key references company(cmpId),sal int) ;




insert into department values (1,'it'),
							  (2, 'admin'),
							  (3, 'hr');


insert into manages values ('kapil',1, 1, 200000, '2019-01-14')
							, ('rahul',4, 1, 100000, '2019-01-14')
							, ('else',2, 1, 100000, '2019-01-14')
							, ('ttt',3, 2, 100000, '2019-01-14');



insert into employee values ('ehte' , 'A-38, noida, UP' , 'gzb' , 1 ,2, 'active', '2019-01-14') ,
							('mansi' , 'A-38, noida, UP' , 'gzb' , 1 ,2, 'active', '2019-01-14'),
							('aman' , 'A-39, noida, UP' , 'noida' , 3 ,2 , 'active', '2019-01-14'),
							('shishir' , 'A-40, noida, UP' , 'noida' , 3 ,3, 'active', '2019-01-14'),
							('rahul' , 'A-41, noida, UP' , 'delhi' , 1 ,2, 'active', '2019-01-14'),
							('mohit' , 'A-42, noida, UP' , 'delhi' , 2 ,1, 'active', '2019-01-14'),
							('karan' , 'A-43, noida, UP' , 'noida' , 1 ,3, 'active', '2019-01-14'),
							('kapil' , 'A-44, noida, UP' , 'noida' , null ,1, 'active', '2019-01-14');





insert into company values ('CTT', 'noida'),
							('HWT', 'delhi');




insert into works values   (1, 3, 1, 100000),
						  (1, 4, 1, 100000),
						  (1, 5, 1, 100000),
						  (1, 7, 1, 200000),
						  (1, 6, 2, 100000),
						  (1, 8, 2, 100000);






--Create Non-Clustered index
create NonClustered index workIndex
      on works(empId, cmpId);



--Create Clustered index
create NonClustered index idWork
      on works(workId);


select * into work_backup from works;

--Filtered non clustered
create NonClustered index backupWorks
      on work_backup(sal) where sal>40000;