Notes

CREATE TABLE Department
(
departmentId int PRIMARY KEY Identity(1,1),
departmentName varchar(255),
isActive bit,
createdUser int,
createdDate DateTime
)

INSERT INTO Department(departmentName,isActive,createdUser,createdDate)
VALUES('HR',1,2,'01-22-2020'),
('Marketing',0,3,'01-22-2020'),
('Sales',1,4,'01-22-2020')

INSERT INTO Department(departmentName,isActive,createdUser,createdDate)
VALUES('Finance',1,2,GETDATE())

SELECT * FROM Department

---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Employee
(
employeeId int PRIMARY KEY Identity(1,1),
firstName varchar(255),
lastName varchar(255),
DOJ DateTime,
departmentId int ,
[address] nvarchar(255),
city varchar(100),
createdUser int,
createdDate DateTime
)

INSERT INTO Employee(firstName,lastName,DOJ,departmentId,[address],city,createdUser,createdDate)
VALUES('Milin','Khalas',GETDATE(),3,'78 Chaya Bunglow,Ahmedabad','Surat',1,GETDATE()),
('Abhisek','Ippkayal',GETDATE(),4,'76 Chaya Bunglow,Ahmedabad','Chennai',1,GETDATE()),
('Hitesh','Chaudhary',GETDATE(),5,'77 Chaya Bunglow,Ahmedabad','Ahmedabad',1,GETDATE())



SELECT * FROM Employee


CREATE VIEW [vw_employee]
as
select firstName+' '+lastName as FullName,DOJ,departmentId,[address],city,createdUser,createdDate from Employee

select * from [vw_employee]


24-1-20


CREATE TABLE Login(
loginId int PRIMARY KEY IDENTITY(1,1),
employeeId int FOREIGN KEY REFERENCES Employee(employeeId),
userName varchar(255),
[password] nvarchar(255),
createdUser int,
createdDate DateTime);

INSERT INTO Login(employeeId,userName,[password],createdUser,createdDate)
VALUES(1,'Bhavesh Koshti','QmhhdmVzaCQxMg==',1,GETDATE()),
(1,'Hitesh Rajput','SGl0ZXNoJjIx',2,GETDATE());

ALTER TABLE Employee ADD Email nvarchar(255);
ALTER TABLE Employee ADD UNIQUE(Email)

ALTER TABLE Employee
ADD CONSTRAINT df_email
DEFAULT '[email protected]' FOR Email;


ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;



1) select * from Login where employeeId in (select employeeId from Employee where departmentId=(select departmentId from Department where departmentName='HR'))

2) delete from Login where employeeId in (select employeeId from Employee where departmentId=(select departmentId from Department where departmentName='HR'))

3) delete from Employee where departmentId=(select departmentId from Department where departmentName='HR')


ALTER TABLE Department
ADD CONSTRAINT df_isactive
DEFAULT 1 FOR isActive;


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DAY 3

CREATE TABLE EmployeeSalary
(
Id int PRIMARY KEY Identity(1,1),
employeeId int REFERENCES Employee(employeeId),
CTC numeric(18,2),
createdUser int,
createdDate DateTime
)


select d.departmentName,count(employeeId) as Numberofemployees from Employee e
join Department d on e.departmentId=d.departmentId
GROUP BY d.departmentName
10500,15000,22700,26500,22700,55000,41000,65000,88000,150000,98000



select d.departmentId,departmentName ,sum(CTC) as TotalSalary
from Department d
left join Employee e
on e.departmentId= d.departmentId
inner join EmployeeSalary s
on e.employeeId= s.employeeId
group by d.departmentName, d.departmentId



select firstName, CTC from Employee e
inner join EmployeeSalary s
on s.employeeId = e.employeeId
where s.CTC=(select max(CTC)AS ctc from EmployeeSalary)

select firstName, CTC from Employee e
inner join EmployeeSalary s
on s.employeeId = e.employeeId
where s.CTC=(select min(CTC)AS ctc from EmployeeSalary)