NAME- ANUKUL KUMAR
ROLL- 22419MCA013
CREATE TABLE Employee(
EmployeeName varchar2(20) Primary key,
Street varchar2(10),
City varchar2(12)
);
INSERT ALL
INTO Employee (EmployeeName,Street,City) values ('Anukul','123','Brussels')
INTO Employee (EmployeeName,Street,City) values ('Vikash','567','Varanasi')
INTO Employee (EmployeeName,Street,City) values ('Tiwari','987','Delhi')
INTO Employee (EmployeeName,Street,City) values ('Abhishek','665','Venice')
INTO Employee (EmployeeName,Street,City) values ('Babban','612','Mumbai')
Select * from dual;
EMPLOYEENAME STREET CITY
Anukul 123 Brussels
Vikash 567 Varanasi
Tiwari 987 Delhi
Abhishek 665 Venice
Babban 612 Mumbai
CREATE TABLE Works(
EmployeeName varchar2(20) Primary Key,
CompanyName varchar2(22),
Salary number);
insert all
into Works (EmployeeName,CompanyName,Salary) values ('Anukul','First Bank
Corporation',12000)
into Works (EmployeeName,CompanyName,Salary) values ('Tiwari','First Bank
Corporation',16000)
into Works (EmployeeName,CompanyName,Salary) values ('Babban','First Bank
Corporation',8000)
into Works (EmployeeName,CompanyName,Salary) values ('Abhishek','First Bank
Corporation',21000)
into Works (EmployeeName,CompanyName,Salary) values ('Vikash','First Bank
Corporation',4000)
select * from dual;
EMPLOYEENAME COMPANYNAME SALARY
Anukul First Bank Corporation 12000
Tiwari First Bank Corporation 16000
Babban Six Bank Corporation 8000
Abhishek Sky Bank Corporation 21000
Vikash Third Bank Corporation 4000
create table Company(
CompanyName varchar2(22) Primary Key,
City varchar2(15));
insert all
into Company (CompanyName,City) values ('First Bank Corporation','Brussels')
into Company (CompanyName,City) values ('Six Bank Corporation','Venice')
into Company (CompanyName,City) values ('Sky Bank Corporation','Mumbai')
into Company (CompanyName,City) values ('Third Bank Corporation','Delhi')
select * from dual;
COMPANYNAME CITY
First Bank Corporation Brussels
Six Bank Corporation Venice
Sky Bank Corporation Mumbai
Third Bank Corporation Delhi
create table Manages(
EmployeeName varchar2(17) Primary Key,
ManagerName varchar2(17));
insert all
into Manages (EmployeeName, ManagerName) values ('John Doe', 'Jane Smith')
into Manages (EmployeeName, ManagerName) values ('Bob Johnson', 'Jane Smith')
into Manages (EmployeeName, ManagerName) values ('Mary Brown', 'Bob Johnson')
into Manages (EmployeeName, ManagerName) values ('Ranjit', 'Udit')
select * from dual;
EMPLOYEENAME MANAGERNAME
John Doe Jane Smith
Bob Johnson Jane Smith
Mary Brown Bob Johnson
Ranjit Udit
a. Find the names of all employees who work for First Bank Corporation
-> select EmployeeName from Employee
b. Find the names and cities of residence of all employees who work for First Bank
Corporation.
-> select e.EmployeeName,e.city
from Employee e,Works w
where w.CompanyName='First Bank Corporation' and e.EmployeeName=w.EmployeeName
c. Find the names, street addresses, and cities of residence of all employees who
work for
First Bank Corporation and earn more than $10,000.
-> select e.EmployeeName,e.city
from Employee e,Works w
where w.CompanyName='First Bank Corporation' and e.EmployeeName=w.EmployeeName
and w.Salary>10000
d. Find all employees in the database who live in the same cities as the companies
for
which they work.
-> select e.EmployeeName
from Employee e,Works w,Company c
where e.city=c.city and w.CompanyName=c.CompanyName and
e.EmployeeName=w.EmployeeName
e. Find all employees in the database who live in the same cities and on the same
streets
as do their managers.
-> select employee.employee_name from employee natural join
managers,employee as emp where
manager_name=emp.employee_name and
employee.street=emp.street and employee.city=emp.city;
f. Find all employees in the database who do not work for First Bank Corporation
-> select EmployeeName
from Works
where CompanyName != 'First Bank Corporation'
g. Find all employees in the database who earn more than each employee of Third
Bank
Corporation
-> Select EmployeeName
from Works
where Salary > (Select Salary from Works where CompanyName='Third Bank
Corporation')
h. Find all employees who earn more than the average salary of all employees of
their
company.
-> Select EmployeeName
from Works w1
where salary > (select avg(salary) from Works w2 where
w1.companyname=w2.companyname)
i. Find the company that has the most employees.
-> select companyname,count(distinct employeename) from works
group by companyname having count(distinct employeename)>=all(select
count(distinct employeename) from
works group by companyname);
j. Find the company that has the smallest payroll.
-> select companyname
from Works
where salary = (select min(salary) from works)
k. Find those companies whose employees earn a higher salary, on average, than the
average salary at First Bank Corporation
-> select companyname ,employeename
from Works
where salary > (select avg(salary) from works where companyname='First Bank
Corporation')
l. Modify the database so that Anukul now lives in Newtown
-> update employee
set city='NewTown'
where employeename='Anukul'
m. Give all employees of First Bank Corporation a 10 percent raise
-> update works
set salary=1.1*salary
where companyname='First Bank Corporation'
n. Delete all tuples in the works relation for employees of Six Bank Corporation
-> delete from works
where companyname='Six Bank Corporation'
