Notes

The following questions pertain to a database with the following tables.

Employee( emp_name, street, )
Works(emp_name,comp_name,salary)
Company( comp_name, city)
manages(emp_name, mgr_name)

create table Employee (emp_id varchar2(5) primary key,emp_name varchar2(20), street varchar2(50), city varchar2(20));


create table Works (emp_id varchar2(5) primary key,comp_id varchar2(5) references Company(comp_id) , salary number);

create table manages (emp_id varchar2(5) primary key,mgr_id varchar2(5) references Employee(emp_id));

create table Company (comp_id varchar2(5) primary key, comp_name varchar2(30) , mgr_name varchar2(30),city varchar2(20));



insert into Employee values ('E001','ABC','10 abc street','Kolkata');
insert into Employee values ('E002','ABD','11 abc street','Delhi');
insert into Employee values ('E003','ABE','11 abc street','MUmbai');


insert into Company values ('C001','First Bank Corporation','ABD','Kolkata');


insert into Works values ('E001','C001',12000);

1.select emp_name from Employee where emp_id in (select emp_id from Works where comp_id in (SELECT comp_id FROM Company where comp_name='First Bank Corporation'))

2.select emp_name,city from Employee where emp_id in (select emp_id from Works where comp_id in (SELECT comp_id FROM Company where comp_name='First Bank Corporation'))

3.select emp_name,street from Employee where emp_id in (select emp_id from Works where comp_id in (SELECT comp_id FROM Company where salary>10000 and comp_name='First Bank Corporation'))


4.select Employee.emp_id,Employee.emp_name,Employee.street,Employee.city from Employee,Company,Works where Works.emp_id=Employee.emp_id and Works.comp_id=Company.comp_id and Employee.city=Company.city

5.select emp_name from Employee where emp_id not in (select emp_id from Works where comp_id in (SELECT comp_id FROM Company where comp_name='First Bank Corporation'))
Write a note in this area. It's really easy to share with others. Click here ...