Untitled

Name:-
Div:-FY-IT.B
DATE:-16/12/2022
Subject:-DBMS
Roll-No:-
PC_NO:-
Enrollment:-
Aim:-Consider the following employees AND departments TABLE

CREATE TABLE Emp_Detail
(
Emp_ID NUMBER(5) PRIMARY KEY,
Fitrst_Name VARCHAR2(20),
Lase_Name VARCHAR2(20),
Email VARCHAR2(30),
Phone_number NUMBER(10),
Hire_Date DATE,
Job_ID NUMBER(5),
Salary NUMBER(10),
Manager_ID NUMBER(5),
Department_ID NUMBER(5)
);

INSERT INTO Emp_Detail(Emp_ID,Fitrst_Name,Lase_Name,Email,Phone_number,Hire_Date,Job_ID,Salary,Manager_ID,Department_ID)
VALUES(01,'roshan','Patel','[email protected]',9876543210,'2/16/2022',6,50001,6,106);

INSERT INTO Emp_Detail(Emp_ID,Fitrst_Name,Lase_Name,Email,Phone_number,Hire_Date,Job_ID,Salary,Manager_ID,Department_ID)
VALUES(01,'Sarthak','Patel','[email protected]',9300445522,'1/15/2022',1,50000,1,101);

INSERT INTO Emp_Detail(Emp_ID,Fitrst_Name,Lase_Name,Email,Phone_number,Hire_Date,Job_ID,Salary,Manager_ID,Department_ID)
VALUES(02,'Het','goti','[email protected]',0123456789,'1/25/2022',2,40000,2,102);

INSERT INTO Emp_Detail(Emp_ID,Fitrst_Name,Lase_Name,Email,Phone_number,Hire_Date,Job_ID,Salary,Manager_ID,Department_ID)
VALUES(03,'Ronik','Savaliya','[email protected]',9638520147,'1/16/2022',3,45000,3,103);

INSERT INTO Emp_Detail(Emp_ID,Fitrst_Name,Lase_Name,Email,Phone_number,Hire_Date,Job_ID,Salary,Manager_ID,Department_ID)
VALUES(04,'Preatham','Katrodiya','[email protected]',7374141111,'1/17/2022',4,47000,4,104);

INSERT INTO Emp_Detail(Emp_ID,Fitrst_Name,Lase_Name,Email,Phone_number,Hire_Date,Job_ID,Salary,Manager_ID,Department_ID)
VALUES(05,'Jenish','Hirpara','[email protected]',9999999999,'1/23/2022',5,48000,5,105);


SELECT * FROM Emp_Detail;

EMP_ID  FITRST_NAME LASE_NAME   EMAIL   PHONE_NUMBER    HIRE_DATE   JOB_ID  SALARY  MANAGER_ID  DEPARTMENT_ID
1   Sarthak Patel   [email protected]  9876543210  01/15/2022  1   50000   1   101
2   Het goti    [email protected]    0123456789  01/25/2022  2   40000   2   102
3   Ronik   Savaliya    [email protected]   963520147  01/16/2022   3   45000   3   103
4   Preatham    Katrodiya   [email protected]    7374141111  01/17/2022  4   47000   4   104
5   Jenish  Hirpara [email protected]   9999999999  01/23/2022  5   48000   5   105


CREATE TABLE Emp_Department
(
Department_Id NUMBER(5) PRIMARY KEY,
Department_Name VARCHAR2(20),
location_ID NUMBER(5)
);


INSERT INTO Emp_Department(Department_Id,Department_Name,location_ID)
VALUES(101,'Web Devlopment',1700);

INSERT INTO Emp_Department(Department_Id,Department_Name,location_ID)
VALUES(102,'App Devlopment',1800);

INSERT INTO Emp_Department(Department_Id,Department_Name,location_ID)
VALUES(103,'Database Mannager',1900);

INSERT INTO Emp_Department(Department_Id,Department_Name,location_ID)
VALUES(104,'Game Devlopment',2000);

INSERT INTO Emp_Department(Department_Id,Department_Name,location_ID)
VALUES(105,'fltter',2100);


SELECT * FROM  Emp_Department;
DEPARTMENT_ID   DEPARTMENT_NAME LOCATION_ID
101 Web Devlopment  1700
102 App Devlopment  1800
103 DATABASE Mannager   1900
104 Game Devlopment 2000
105 fltter  2100

⦁ TO find ALL employees who locate IN the location WITH the id 1700.

SELECT * FROM  Emp_Department
WHERE location_ID = 1700;

Output:

DEPARTMENT_ID   DEPARTMENT_NAME LOCATION_ID
101 Web Devlopment  1700


⦁  TO find ALL employees who DO NOT locate AT the location 1700.

SELECT * FROM  Emp_Department
WHERE location_ID != 1700;

Output:

DEPARTMENT_ID   DEPARTMENT_NAME LOCATION_ID
102 App Devlopment  1800
103 DATABASE Mannager   1900
104 Game Devlopment 2000
105 fltter  2100


⦁ TO finds the employees who have the highest salary.


SELECT MAX (Salary)
FROM  Emp_Detail;

Output:

MAX(SALARY)
50000


⦁ TO finds ALL employees who salaries are greater than the average salary OF ALL employees.
SELECT AVG (salary)
FROM Emp_Detail;

Output:

AVG(SALARY)
46000


⦁ TO finds ALL departments which have AT LEAST one employee WITH the salary IS greater than 10,000.

SELECT * FROM Emp_Detail
WHERE (Salary) < '10000';


⦁ TO finds ALL departments that DO NOT have ANY employee WITH the salary greater than 10,000.
Ans:
SELECT * FROM Emp_Detail
WHERE (Salary) > '10000';


Output:

EMP_ID  FITRST_NAME LASE_NAME   EMAIL   PHONE_NUMBER    HIRE_DATE   JOB_ID  SALARY  MANAGER_ID  DEPARTMENT_ID
1   Sarthak Patel   [email protected]  9374626600  01/15/2022  1   50000   1   101
2   Het goti    [email protected]    9314626612  01/25/2022  2   40000   2   102
3   Ronik   Savaliya    [email protected]   9371626640  01/16/2022  3   45000   3   103
4   Preatham    Katrodiya   [email protected]    7374626604  01/17/2022  4   47000   4   104
5   Jenish  Hirpara [email protected]   9774646200  01/23/2022  5   48000   5   105


⦁ TO find the lowest salary BY department.


   SELECT MIN (Salary)
FROM  Emp_Detail;

Output:

MIN(SALARY)
40000


⦁ TO finds ALL employees whose salaries are greater than the lowest salary OF every department.

SELECT * FROM Emp_Detail
WHERE (Salary) >'4000';

Output:

EMP_ID  FITRST_NAME LASE_NAME   EMAIL   PHONE_NUMBER    HIRE_DATE   JOB_ID  SALARY  MANAGER_ID  DEPARTMENT_ID
1   Sarthak Patel   [email protected]  9374626600  01/15/2022  1   50000   1   101
2   Het goti    [email protected]    9314626612  01/25/2022  2   40000   2   102
3   Ronik   Savaliya    [email protected]   9371626640  01/16/2022  3   45000   3   103
4   Preatham    Katrodiya   [email protected]    7374626604  01/17/2022  4   47000   4   104
5   Jenish  Hirpara [email protected]   9774646200  01/23/2022  5   48000   5   105

⦁ TO finds ALL employees whose salaries are greater than OR equal TO the highest salary OF every department.
SELECT *
 FROM employees
WHERE salary>=15000;


10. TO finds the salaries OF ALL employees, their average salary, AND the difference BETWEEN the salary OF each employee AND the average salary.
SELECT AVG(salary),MAX(salary)
FROM employees;

ss


AVG(SALARY)
10000
MAX(SALARY)
15000