CREATE EMPLOYEE TABLE Easily

                                         

                                              

(Employee_id int not null primary key,

First_name varchar(15),

Last_name varchar(15),

salary int,

Joining_date date,

Department varchar(15));

select * from Employee;

                            INSERTING SOME VALUES EMPLOYEE TABLE

insert into Employee values

(1,'Anika','Arora',100000,'2020-02-14 9:00:00','HR')

insert into Employee values

(2,'Veena','Verma',80000,'2011-06-15 9:00:00','Admin'),

(3,'Vishal','Singhal',300000,'2020-02-16 9:00:00','HR'),

(4,'Sushanth','Singh',500000,'2020-02-17 9:00:00','Admin'),

(5,'Bhupal','Bhati',500000,'2011-06-18 9:00:00','Admin'),

(6,'Dheeraj','Diwan',200000,'2011-06-19 9:00:00','Account'),

(7,'Karan','Kumar',75000,'2020-01-14 9:00:00','Account'),

(8,'Chandrika','Chauhan',90000,'2011-04-15 9:00:00','Admin')

insert into Employee values

(9,'Chandrika','Chauhan',90000,'2011-04-15 9:00:00','Admin')

delete from Employee where Employee_id=9;

                                           CREATE EMPLOYEE_BONUS TABLE

create table Employee_Bonus

(Employee_ref_id int foreign key references Employee(Employee_id), Bonus_Amount int, 

Bonus_Date date);

                               INSERTING SOME VALUES EMPLOYEE_BONUS TABLE

insert into Employee_Bonus values

(1,5000,'2020-02-16 0:00:00'),

(2,3000,'2011-06-16 0:00:00'),

(3,4000,'2020-02-16 0:00:00'),

(1,4500,'2020-02-16 0:00:00'),

(2,3500,'2011-06-16 0:00:00');

                                              CREATE EMPLOYEE_TITLE TABLE

create table Employee_Title

(Employee_ref_id int, Employee_tittle varchar(20), 

Affective_Date date);

                                INSERTING SOME VALUES EMPLOYEE_TITLE TABLE

insert into Employee_Title values

(1,'Manager','2016-02-20 0:00:00'),

(2,'Executive','2016-06-11 0:00:00'),

(8,'Executive','2016-06-11 0:00:00'),

(5,'Manager','2016-06-11 0:00:00'),

(4,'Asst. Manager','2016-06-11 0:00:00'),

(7,'Executive','2016-06-11 0:00:00'),

(6,'Lead','2016-06-11 0:00:00'),

(3,'Lead','2016-06-11 0:00:00');

select * from Employee_Title;

                          SOME QUERY FOR ABOVE TABLES

-- 1. Display the “FIRST_NAME” from Employee table using the alias name as Employee_name.

select First_name as Employee_name from Employee;

-- 2 Display “LAST_NAME” from Employee table in upper case

select upper(LAST_NAME) from Employee;

-- 3 Display unique values of DEPARTMENT from EMPLOYEE table.

select distinct department from Employee;

--4 Display the first three characters of LAST_NAME from EMPLOYEE table.

select  left(Last_name,3) from Employee;

--Display the unique values of DEPARTMENT from EMPLOYEE table and prints its length.

select distinct department, len(department) as lenth_of_dept from Employee;

--6 Display the FIRST_NAME and LAST_NAME from EMPLOYEE table into a single column AS FULL_NAME. 

select First_name+' '+Last_name as Full_name from Employee;

--7 DISPLAY all EMPLOYEE details from the employee table order by FIRST_NAME Ascending.

select * from Employee order by First_name asc;

--Display all EMPLOYEE details order by FIRST_NAME Ascending and DEPARTMENT Descending

select * from Employee order by First_name asc,  department desc;

--9 Display details for EMPLOYEE with the first name as “VEENA” and “KARAN” from EMPLOYEE table.

Select First_name from Employee where First_name ='Veena' or First_name ='Karan';

--10 Display details of EMPLOYEE with DEPARTMENT name as “Admin”.

select * from Employee where  Department='Admin';

--11 DISPLAY details of the EMPLOYEES whose FIRST_NAME contains ‘V’.

select * from Employee where First_name like '%V%' ;

--12 DISPLAY details of the EMPLOYEES whose SALARY lies between 100000 and 500000.

select * from Employee where salary between 100000 and 500000 ;

--13 Display details of the employees who have joined in Feb-2020.

select * from Employee  where Joining_date between '2020-02-01 0:00:00' and '2020-02-28 0:00:00';

--14 Display employee names with salaries >= 50000 and <= 100000.

select * from Employee where salary>=50000 or salary<=100000;

--15 DISPLAY details of the EMPLOYEES who are also Managers.

select * from Employee,Employee_Title where Employee_tittle='Manager';

--16 DISPLAY duplicate records having matching data in some fields of a table.

SELECT salary,count(salary)

FROM Employee

GROUP BY salary

HAVING count(salary)>1;

 

--17 Display only odd rows from a table.

SELECT * FROM Employee where Employee_id IN(select Employee_id from Employee where Employee_id%2<>0);

--18 Clone a new table from EMPLOYEE table.

CREATE TABLE Clone_Employee(Employee_id int not null primary key,

First_name varchar(15),

Last_name varchar(15),

salary int,

Joining_date date,

Department varchar(15)) select * from Employee;

INSERT INTO Clone_Employee SELECT * FROM Employee;

SELECT * FROM Clone_Employee;

---19 DISPLAY the TOP 2 highest salary from a table.

SELECT * FROM Employee WHERE (salary IN (SELECT TOP (1) salary FROM Employee GROUP BY salary ORDER BY salary DESC ));

---20. DISPLAY the list of employees with the same salary.

SELECT * FROM Employee WHERE salary IN(SELECT salary FROM Employee e WHERE Employee.Employee_id <> e.Employee_id);

--21 Display the second highest salary from a table.

SELECT  salary FROM employee A WHERE 3-1 = (SELECT count(salary) FROM employee B WHERE B.salary>A.salary);

--22 Display the first 50% records from a table.

select top 50 percent * from Employee;

--23 Display the departments that have less than 4 people in it.

SELECT * FROM Employee WHERE Department IN

  (

    SELECT Department

       FROM Employee

      GROUP BY Department

      HAVING COUNT(*) <4

  );

--24. Display all departments along with the number of people in there

SELECT Department,count(*) "Total_no_Emp"  FROM Employee group by Department;

--25. Display the name of employees having the highest salary in each department.

SELECT First_name as Emp_name FROM Employee E WHERE E.salary IN (SELECT max(salary) FROM Employee GROUP BY Department );

--26 Display the names of employees who earn the highest salary.

SELECT First_name as Emp_name FROM Employee E WHERE  E.salary IN (SELECT top(1)(salary) FROM Employee);

--27 Diplay the average salaries for each department

SELECT salary Avg_salary FROM Employee E WHERE E.salary IN (SELECT avg(salary) FROM Employee GROUP BY Department );