NAME : SIVA AN REG NO: 19BCE1582
Ex. No. 3 & 4
01-July-2021
SQL –Set Operations and Subqueries
PART A
Create two tables with the following details
Customer_Fixed_Deposit(cust_ID,Cust_name,Fixed_Deposit_Amount)
Customer_loan(cust_ID, Cust_name, Loan_ Amount)
create table Customer_Fixed_Deposit(Cust_ID int primary
key,Cust_name varchar(20),Fixed_Deposit int);
create table Customer_Loan(Cust_ID int primary key,Cust_name
varchar(20),Loan int);
INSERT INTO Customer_Fixed_Deposit(Cust_ID ,Cust_name
,Fixed_Deposit)
SELECT 1,'siva',5000
FROM DUAL UNION ALL
SELECT 2,'damian',3000
FROM DUAL UNION ALL
SELECT 3,'dinesh',6000
FROM DUAL UNION ALL
SELECT 4,'kevin',3000
FROM DUAL UNION ALL
SELECT 5,'seki',4000
FROM DUAL
INSERT INTO Customer_Loan(Cust_ID ,Cust_name ,Loan)
SELECT 1,'siva',3000
FROM DUAL UNION ALL
SELECT 10,'Saro',10000
FROM DUAL UNION ALL
SELECT 7,'samutha',80000
FROM DUAL UNION ALL
SELECT 3,'dinesh',1000
FROM DUAL UNION ALL
SELECT 5,'seki',2000
FROM DUAL
Write SQL queries for the following
1. Find all customers who have either having Fixed Deposit (FD) or
loan or both
create table customer(Cust_ID int primary key,Cust_name varchar(20))
insert into customer select Cust_ID ,Cust_name from Customer_Fixed_Deposit;
insert into customer select Cust_ID ,Cust_name from Customer_Loan where
Cust_ID not in(select Cust_ID from Customer_Fixed_Deposit);
select * from customer;
2. List all customers who have both Fixed Deposit and Loan using
set operation.
select Cust_ID ,Cust_name from Customer_Fixed_Deposit intersect select Cust_ID
,Cust_name from Customer_Loan ;
3. List all customers who have both Fixed Deposit and Loan using
subquery.
select Cust_ID ,Cust_name from Customer_Fixed_Deposit where Cust_ID in(select
Cust_ID from Customer_Loan);
4. Get all Customers who have not taken a Loan using set operation.
select Cust_ID ,Cust_name from Customer_Fixed_Deposit minus select Cust_ID
,Cust_name from Customer_Loan
5. Get all Customers who have not taken a Loan using subquery.
select Cust_ID ,Cust_name from Customer_Fixed_Deposit where Cust_ID not
in(select Cust_ID from Customer_Loan);
6. Find the names of customers whose FD amount is greater than the
FD of the customer named ‘Rajesh’.
select Cust_name from Customer_Fixed_Deposit where Fixed_Deposit>(select
Fixed_Deposit from Customer_Fixed_Deposit where Cust_name='rajesh');
7. Find the number of customers whose FD amount is greater than
the average of all FD amounts.
select count (Cust_name) from Customer_Fixed_Deposit where
Fixed_Deposit>(select AVG(Fixed_Deposit) from Customer_Fixed_Deposit);
8. Find the customers those who have FD of less than Rs. 50000 and
a loan amount of Rs.100000 or above.
select Cust_ID ,Cust_name from Customer_Fixed_Deposit where
Fixed_Deposit<50000 intersect select Cust_ID ,Cust_name from Customer_Loan
where Loan<10000
9. Find the names of the customers who have taken the maximum loan
amount.
select Cust_ID ,Cust_name from Customer_Loan where Loan=(SELECT
MAX(Loan)
FROM Customer_Loan)
10. Find the number of customers who have both Fixed Deposit and
Loan.
select count (Cust_name)from (select Cust_ID ,Cust_name from
Customer_Fixed_Deposit union select Cust_ID ,Cust_name from
Customer_Loan) ;
PART B
CREATE TABLE Departments(Department_ID int primary key,Department_name varchar(20),Location_ID
int)
CREATE TABLE Employee(Employee_ID int primary key,Fname varchar(20),Lname varchar(20),email
varchar(20),phone int,
Hiredate date,Job_id int,salary int,manager_id int,Department_ID references
Departments(Department_ID) )
Insert into Departments
SELECT 1,'Software',1700
FROM DUAL UNION
SELECT 2,'HR',1200
FROM DUAL UNION
SELECT 3,'Testing',1700
FROM DUAL
Insert into Employee
SELECT 1,'siva','an','siva@gmail.com',82703,TO_DATE('17/12/2015', 'DD/MM/YYYY'),10,50000,23,1
FROM DUAL UNION
SELECT 2,'Saro','S','Saro@gmail.com',944415744,TO_DATE('17/12/2015',
'DD/MM/YYYY'),9,60000,20,2
FROM DUAL UNION
SELECT 3,'Pika','an','pika@gmail.com',82703944,TO_DATE('17/12/2015', 'DD/MM/YYYY'),8,56000,21,3
FROM DUAL
1. Find all employees that belong to the location 1700 by
using the department id
SELECT Employee_ID,Fname ,Lname from Employee where department_ID
in(select Department_ID from Departments where location_ID=1700)
2. Find the employees who have the highest salary:
SELECT Employee_ID,Fname ,Lname FROM Employee where Salary=(SELECT
MAX(Salary) from Employee)
3. Find all employees who salaries are greater than the
average salary of all employees:
SELECT Employee_ID,Fname ,Lname FROM Employee where Salary>(SELECT AVG(Salary) from
Employee)
4. Find all employees whose salaries are greater than the
lowest salary of every department:
SELECT Employee_ID,Fname ,Lname FROM Employee where Salary>(SELECT
MIN(Salary) from Employee)
5. Finds the salaries of all employees, their average salary,
and the difference between the salary of each employee and
the average salary.
Alter table Employee add AVG int;
Update Employee set AVG=(select AVG(salary) from Employee);
select Employee_ID,fname,lname,Salary,AVG,AVG-salary from Employee
