Name:-
Div:-FY-IT.B
Date:-25/11/2022
Subject:-DBMS
Roll-No:- 
PC_NO:-

PRATICAL ASSIGMENT-6


create table Empl_Details
(
Emp_ID number(5) PRIMARY KEY,
Full_Name varchar2(20),
Manager_ID number(5),
Date_of_Joining date,
city varchar2(15)
);


INSERT INTO Empl_Details(Emp_ID,Full_Name,Manager_ID,Date_of_Joining,city)
VALUES (121,'John Snow',321,'01/31/2019','Toronto');

INSERT INTO Empl_Details(Emp_ID,Full_Name,Manager_ID,Date_of_Joining,city)
VALUES (321,'Walter White',986,'01/30/2020','California');

INSERT INTO Empl_Details(Emp_ID,Full_Name,Manager_ID,Date_of_Joining,city)
VALUES (421,'Kuldeep Rana',876,'11/27/2021','New Delhi');

select *
From Empl_Details;

create table Empl_Salary
(
E_ID number(5),
Project varchar2(5),
Salary number(10),
Variable number(10)
);

ALTER TABLE Empl_Salary
ADD FOREIGN KEY (E_ID) REFERENCES Empl_Details (Emp_ID);

INSERT INTO Empl_Salary(E_ID,Project,Salary,Variable)
VALUES (121,'p1',8000,500);

INSERT INTO Empl_Salary(E_ID,Project,Salary,Variable)
VALUES (321,'p2',10000,1000);

INSERT INTO Empl_Salary(E_ID,Project,Salary,Variable)
VALUES (421,'p1',12000,0);

select *
From Empl_Salary



1.Write an SQL query to fetch the EmpId and FullName of all the employees working under the Manager with id – ‘986’.


SELECT Emp_ID, Full_Name
FROM Empl_Details
WHERE Manager_ID = 986;
Output:
EMP_ID FULL_NAME
321 Walter
White


2. Write an SQL query to fetch the different projects available from the EmployeeSalary table.

SELECT DISTINCT(Project)
FROM Empl_Salary;

Output :
PROJECT
p1
p2

3. Write an SQL query to fetch the count of employees working in project ‘P1’.
SELECT count(*)
FROM Empl_Salary
WHERE Project = 'p1';

Output :
COUNT(*)
2


4. Write an SQL query to find the maximum, minimum, and average salary of the employees.
SELECT MAX(Salary),MIN(Salary),AVG(Salary)
FROM Empl_Salary;
Output:
MAX(SALARY) MIN(SALARY) AVG(SALARY)
12000                   8000                    10000


5. Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000.
SELECT E_ID
FROM Empl_Salary
WHERE Salary BETWEEN 9000 and 15000;

Output :
E_ID
321
421

6. Write an SQL query to fetch those employees who live in Toronto and work under the manager with ManagerId – 321.
SELECT Emp_ID, City, Manager_Id
FROM Empl_Details
WHERE City='Toronto' AND Manager_Id='321';

Output :
EMP_ID CITY MANAGER_ID
121 Toronto321


7. Write an SQL query to fetch all the employees who either live in California or work under a manager with ManagerId – 321.
SELECT Emp_ID, City, Manager_Id
FROM Empl_Details
WHERE City='California' OR Manager_Id='321';
Output:
EMP_ID CITY MANAGER_ID
121 Toronto 321
321 California986

8. Write an SQL query to fetch all those employees who work on Projects other than P1.
SELECT *
FROM Empl_Salary
WHERE NOT Project ='p1';

Output:
E_ID PROJECT SALARY VARIABLE
321 p2 10000 1000

9. Write an SQL query to display the total salary of each employee adding the Salary with Variable value.
SELECT E_ID,
Salary+Variable as totalSalary
FROM Empl_Salary;

Output:
E_ID TOTALSALARY
121 8500
321 11000
421 12000

10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ends with any sequence of characters.
SELECT Full_Name
FROM Empl_Details
WHERE Full_Name LIKE '__hn%';

Output:
FULL_NAME
John Snow

11.Write an SQL query to fetch common records between two tables.
SELECT Emp_Id FROM Empl_Details
UNION
SELECT E_Id FROM Empl_Salary;

Output :
EMP_ID
121
321
421


12. Write an SQL query to fetch records that are present in one table but not in another table.
SELECT *
FROM Empl_Details
WHERE NOT exists(SELECT *FROM Empl_salary
WHERE Empl_Details.Emp_ID=Empl_Salary.E_ID);

Output:
EMP_ID FULL_NAME MANAGER_ID DATE_OF_JOINING CITY
152 Kuldeep
Rana 876 11/27/2021 New
Delhi
13. Write an SQL query to fetch the EmpIds that are present in both the tables –   ‘EmployeeDetails’ and ‘EmployeeSalary.
SELECT Emp_ID
FROM Empl_Details
WHERE exists(SELECT *
FROM Empl_salary
WHERE Empl_Details.Emp_ID=Empl_Salary.E_ID);

Output:
EMP_ID
121
321
42
14. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary.
SELECT Emp_ID
FROM Empl_Details
WHERE not exists(SELECT *
FROM Empl_salary
WHERE Empl_Details.Emp_ID=Empl_Salary.E_ID);
Output:
EMP_ID
152

15. Write an SQL query to display both the EmpId and ManagerId together.
SELECT Emp_ID,Manager_ID
FROM Empl_Details;
Output:
EMP_ID MANAGER_ID
121 321
321 986
421 876
152 876
16. Write an SQL query to uppercase the name of the employee and lowercase the city values.
SELECT Upper(Full_Name)"Full Name",Lower(City)"City"
FROM Empl_Details;
Output:
Full Name City
JOHN SNOW toronto
WALTER WHITE california
KULDEEP RANA new delhi
KULDEEP RANA new delhi

17. Fetch all the employees who are not working on any project.
SELECT Project
FROM Empl_Salary
WHERE not exists(SELECT *
FROM Empl_Details
WHERE Empl_Details.Emp_ID=Empl_Salary.E_ID);
Output :
no data found

18. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000.
SELECT Full_Name
FROM Empl_Details
WHERE exists(SELECT *
FROM Empl_Salary
WHERE Empl_Details.Emp_ID=Empl_Salary.E_ID
AND Salary BETWEEN 5000 AND 10000);
Output :
FULL_NAME
John Snow
Walter
White

19. Write an SQL query to fetch all the Employee details from the EmployeeDetails table who joined in the Year 2020.
SELECT *
FROM Empl_Details
WHERE Date_of_Joining BETWEEN '1/1/2020' AND '12/31/2020';
Output:
EMP_ID FULL_NAME MANAGER_ID DATE_OF_JOINING CITY
321 Walter White 986 01/30/2020 California

20. Write an SQL query to fetch all employee records from the EmployeeDetails table who have a salary record in the EmployeeSalary table.

SELECT *
FROM Empl_Details
WHERE exists(SELECT *
FROM Empl_Salary
WHERE Empl_Details.Emp_ID=Empl_Salary.E_ID);
Output:
EMP_ID FULL_NAME MANAGER_ID DATE_OF_JOINING CITY
121 John Snow 321 01/31/2019 Toronto
321 Walter White 986 01/30/2020 California
421 Kuldeep Rana 876 11/27/2021 New Delhi