Create Database dbEmployee
Use dbEmployee
-------------Table 1------------
Create Table tblEmpList
(
Employee_ID int primary key,
First_Name nvarchar(max),
Last_Name nvarchar(max),
Employee_Salary money,
)
Insert into tblEmpList values (100,'Steven','King',24000);
Insert into tblEmpList values (101,'Neena','Kochar',17000);
Insert into tblEmpList values (102,'Lex','De Haan',17000);
Insert into tblEmpList values (103,'Alexander','Hunold',9000);
Insert into tblEmpList values (104,'Bruce','Ernst',6000);
Insert into tblEmpList values (105,'David','Austin',4800);
Insert into tblEmpList values (106,'Valli','Pataballa',4800);
Insert into tblEmpList values (107,'Diana','Lorentz',4200);
Insert into tblEmpList values (108,'Nancy','Greenberg',12008);
Insert into tblEmpList values (109,'Daniel','Faviet',9000);
Insert into tblEmpList values (110,'John','Chen',8200);
Select*from tblEmpList
-----------Question 1--------
Select First_Name, Last_Name, Employee_Salary from tblEmplist where Employee_Salary
> ( Select Employee_Salary from tblEmpList where Employee_ID = 110);
--------Question 2-----------
Select First_Name, Last_Name, Employee_Salary from tblEmpList where Employee_Salary
> All (Select avg(salary) from tblEmpList);
--------Question 3-----------
Select*from tblEmpList where Employee_Salary between (Select MIN(salary) from
tblEmpList) AND 2500;
------------Table 2------------
Create table tblDepartment
(
Department_ID int primary key identity,
Department_Name nvarchar(max),
)
Insert into tblDepartment Values ('Web Developer');
Insert into tblDepartment Values ('Graphic Designer');
Insert into tblDepartment Values ('Clerk');
------------Table 3------------
Create Table tblEmployee
(
ID int identity,
Name nvarchar(max),
Gender nvarchar(max),
DepartmentID int foreign key References tblDepartment(Department_ID),
Insert into tblEmployee values ('Smith','Male',1);
Insert into tblEmployee values ('Ram','Male',1);
Insert into tblEmployee values ('Kevin','Female',1);
Insert into tblEmployee values ('Jack','Male',3);
Insert into tblEmployee values ('Smith','Male',3);
Insert into tblEmployee values ('Olivia','Female',2);
Insert into tblEmployee values ('Jacob','Male',1);
Insert into tblEmployee values ('Neymar','Male',2);
Insert into tblEmployee values ('Muller','Male',1);
Insert into tblEmployee values ('Priyanka','Female',2);
Select*from tblEmployee
---------Question 4---------
---------Procedure----------
Create Procedure spEmployeeGenderCount
@Gender nvarchar (50), @CountEmployee int Input AND output
As
Begin
Select @CountEmployee = count(ID) from tblEmployee Where Gender = @Gender
End
Declare @TotalEmployee int
Execute spEmployeeGenderCount 'Male', @TotalEmployee output
