Exe-17

Set A-1

Create the following relations, for an investment firm
emp( emp-id ,emp-name, address, bdate)
Investor( inv-name , inv-no, inv-date, inv-amt)

An employee may invest in one or more investments, hence he can be an investor.
But an investor need not be an employee of the firm.
Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).
Assume appropriate data types for the attributes. Add any new attributes , as required by the
queries. Insert sufficient number of records in the relations / tables with appropriate values as
suggested by some of the queries.

Write the following queries & execute them.
 1. List the distinct names of customers who are either employees, or investors or both.
2. List the names of customers who are either employees , or investors or both.
3. List the names of emloyees who are also investors.
4. List the names of employees who are not investors.

Ans.

Create table emp(
emp_id int primary key,
emp_name varchar(20),
address text,
bdate date
);

create table investor
(
inv_name varchar(30),
inv_no int primary key,
inv_date date,
inv_amt numeric(8),
emp_id int,
constraint fk foreign key(emp_id) references emp(emp_id) on delete cascade on update cascade
);

insert into emp values(1,'Raj Hawaldar','Sahakar Nagar','08/24/1995');
insert into emp values(2,'Sanjay Pawar','Shaniwar Peth','03/2/1995');
insert into emp values(3,'Samir Shukla','Laxmi Nagar','08/6/1990');
insert into emp values(4,'Sagar Sharma','Kothrud','05/1/1985');
insert into emp values(5,'Kiran Shinde','Sahakar Nagar','08/2/1992');



insert into investor values('Home',1,'3/23/2015',4000000.0,1);
insert into investor values('Car',2,'3/2/2015',900000.0,1);
insert into investor values('Business',3,'3/23/2015',9000000.0,2);
insert into investor values('Home',4,'3/23/2015',4000000.0,4);
insert into investor values('Car',5,'3/23/2015',4000000.0,1);
insert into investor values('Business',6,'3/23/2015',4000000.0,2);
insert into investor values('Home',7,'3/23/2015',4000000.0,3);


 1. List the distinct names of customers who are either employees, or investors or both.

Ans.

  select  emp_name from emp,investor 
where emp.emp_id=investor.emp_id
 union select emp_name from emp;

2. List the names of customers who are either employees , or investors or both.

Ans

select  emp_name from emp,investor
 where emp.emp_id=investor.emp_id
 union all select emp_name from emp;

3. List the names of emloyees who are also investors.

Ans.
select emp_name from emp Intersect
select emp_name from emp,investor
 where emp.emp_id=investor.emp_id;

4. List the names of employees who are not investors.

Ans
select emp_name from emp
except select emp_name from emp,investor where emp.emp_id=investor.emp_id;