DBMS- Assignment 6

 Assignment 6 

 Objectives :- To query table, using set operations (union, intersect)

SQL Set operations : 

You can combine multiple queries using the set operators UNION, UNION ALL,

INTERSECT and Except. ALL set operators have equal precedence.

1 Union: Returns the union of two sets of values, eliminating duplicates.

Syntax: <select query>

Union

<select query>

2 Union all: Returns the union of two sets of values, retaining all duplicates.

Syntax: <select query>

Union all

<select query>

3 Intersect: Returns the intersection of two sets of values, eliminating duplicates. 

Syntax: <select

query>

intersect

<select query>

4 Intersect all: Returns the intersection of two sets of values, retaining duplicates.

Syntax: <select query>

Intersect all

<select query>

5 Except: Returns the difference between two set of values, I.e returns all values fromset1 , not

contained in set2 .eliminates duplicates. 

Syntax: <select query>

except <select

query>

6 Except all: Returns the difference between two set of values, i.e. returns all values from set1, not

contained in set2 .Retains all duplicates.

Syntax: <select query>

Except all

<select query>

The relations participating in the SQL operations union, intersect & except must be compatible i.e. the following  two conditions must hold :

a)The relation r and s must be of the same arity. That is , they must have the same number of attributes.

b) The domains of the ith attribute of r and the ith attribute of s must be the same , for all i

Set A

Create the following relations :

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 employees who are also investors.

4. List the names of employees who are not investors

Queries:- 

Creating the Tables and maintaining the relationships:- 

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,'23/03/2015',4000000.0,1);

insert into investor values('Car',2,'3/2/2015',900000.0,1);

insert into investor values('Business',3,'13/2/2015',9000000.0,2);

insert into investor values('Home',4,'14/04/2015',4000000.0,4);

insert into investor values('Car',5,'08/05/2015',4000000.0,1);

insert into investor values('Business',6,'01/05/2015',4000000.0,2);

insert into investor values('Home',7,'3/3/2015',4000000.0,3);

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

select  distinct 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.

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 employees who are also investors.

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

select emp_name from emp

except 

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