SQL Query Part 2

create database Employeelabtask

use Employeelabtask

CREATE TABLE Employee(

 

Eid INT PRIMARY KEY,

 

E_name VARCHAR(20),

 

E_Age INT CHECK (E_Age between 18 and 60),

 

ECity VARCHAR(20),

 

E_Street VARCHAR(20),

 

E_House# INT,

 

E_Basic_Salary INT,

 

E_Bonus INT,

 

E_Rank VARCHAR(20) CHECK (E_Rank IN('Manager','Develper','Admin')),

 

E_Department VARCHAR(20) DEFAULT 'CS' CHECK (E_Department IN ('HR','CS','IT','Labour')));

 

CREATE TABLE Project1(

 

p_id INT ,

 

pName VARCHAR(20),

 

p_bonus INT,

 

duration_in_months INT,

 

Eid INT,

 

PRIMARY KEY(p_id,Eid),

 

FOREIGN KEY(Eid) REFERENCES Employee(Eid));

 

 

 

 

INSERT INTO employee (Eid, E_name, E_age, Ecity, E_street, E_house#, E_Basic_salary, E_rank)

VALUES (1, 'ALI', 23, 'PINDI', 'YYY', 12, 12000, 'manager')

INSERT INTO employee (Eid, E_name, E_age, Ecity, E_street, E_house#, E_Basic_salary, E_rank)

VALUES (2, 'MUZAMIL', 34, 'DGK', 'YYY', 2, 10000, 'admin')

INSERT INTO employee (Eid, E_name, E_age, Ecity, E_street, E_house#, E_Basic_salary, E_rank)

VALUES (3, 'bais', 34, 'Rwp', 'YYY', 2, 10000, 'admin')

 

--1

SELECT E_name,E_House#+''+E_Street+' '+ECity AS [Emp Name]

FROM Employee;

--2

SELECT TOP(30)PERCENT * FROM EMPLOYEE;

 

--3

SELECT E_name 'Emp Name '

FROM Employee

WHERE

E_House# = 44 AND E_Street = 25 AND ECity = 'Rwp';

 

--4

 

SELECT * FROM Employee WHERE ECity IN('isb','rwp','khi','lhr');

 

--5

 

SELECT * FROM Employee WHERE E_Age BETWEEN 18 AND 33;

--6

SELECT* FROM Employee WHERE E_name LIKE 'S%' AND E_name NOT LIKE '%I%';

 

--7                         

SELECT DISTINCT ECity FROM Employee;

 

--8

SELECT * FROM Employee WHERE ECity = 'isb' ORDER BY E_Age;

 

--9

SELECT E_name, SUM(E_BASIC_SALARY + E_Bonus) AS 'total Salary' FROM

Employee GROUP BY E_name;

 

--10 Display total number of employees.

SELECT COUNT(Eid) AS 'Total number of Employees' FROM Employee;

 

--11Display total number of employees working in each department.

SELECT COUNT(Eid) AS 'Number of Employees' ,E_Department

FROM Employee GROUP BY E_Department;

 

--12- Display Total expenditures of company. (total expenditures = basic salary + bonus of employee + all project bonus)

SELECT SUM(E_Basic_Salary + E_Bonus + p_bonus) FROM Employee INNER JOIN Project1 ON Employee.Eid = Project1.Eid ;

 

 

--13- Display names of projects in which ‘Ahmar’ is working.

SELECT pName FROM Project1 WHERE Eid IN (SELECT Eid FROM Employee WHERE E_name = 'Ahmar')

--14- Display total project bonus of ‘Danish’.

SELECT SUM(p_bonus) FROM Project1 WHERE Eid IN (SELECT Eid FROM Employee WHERE E_name = 'Danish');

 

--15- Display total expenditures of only those departments whose total expenditures are more than 1 Million. (total expenditures = sum of basic salary of all employees of that department)

SELECT pName, SUM(E_Basic_Salary) FROM Employee INNER JOIN Project1 ON Employee.Eid = Project1.Eid GROUP BY pName HAVING SUM(E_Basic_Salary) > 1000000;

 

--16- Create Employee table.

 

CREATE TABLE Employee1 ( Eid INTEGER PRIMARY KEY, Ename VARCHAR,EAge INTEGER, ECity VARCHAR, E_Street VARCHAR, E_House VARCHAR, E_Basic_Salary DECIMAL,  E_Bonus DECIMAL,E_Rank VARCHAR, E_Department VARCHAR DEFAULT 'CS');

--17- Create Project table.

CREATE TABLE Project( p_id INTEGER, pName VARCHAR, p_bonus DECIMAL,duration_in_months INTEGER, Eid INTEGER, PRIMARY KEY (p_id, Eid));

--18- Insert a record in employee table, ( you must not enter edepartment and ebonus)

INSERT INTO Employee ( Eid, E_name,E_Age, ECity, E_Street, E_House#, E_Basic_Salary, E_Rank) VALUES (1, 'ALI', 34, 'ISB', '324', 10000, 'Manager');

 

--19- change basic salary of all employees, add increment of 5% to all.

UPDATE Employee SET E_Basic_Salary = E_Basic_Salary * 1.05 ;

 

--20- Delete only those employees whose salary is above 1Million.

DELETE FROM Employee WHERE E_Basic_Salary > 1000000 ;

 

--21- Change data type employee bonus to “float”.

ALTER TABLE Employee ALTER COLUMN E_Bonus TYPE FLOAT ;

 

--22- Assume that you forgot to add primary key in Employee table, write a code that add a primary key in employee table.

ALTER TABLE Employee ADD CONSTRAINT PK_Employee PRIMARY KEY (Eid) ;

 

--23- Drop primary key constraint you created in above query.

ALTER TABLE Employee DROP CONSTRAINT PK_Employee ;

 

--24- Add a new column ‘e_father_name’ in employee table.

ALTER TABLE Employee ADD COLUMN e_father_name VARCHAR ;

 

--25- Drop E_age column from employee table.

ALTER TABLE Employee DROP COLUMN EAge ;