Assignment 7
Objectives :- To query tables using nested queries (use of ‘Except’, exists, not exists, all clauses)
A subquery is a select-from-where expression that is nested within another query.
1 Set membership: The ‘in’ & ‘not in’ connectivity tests for set membership & absence of set
membership respectively.
2 Set comparison: the < some, > some, <= some, >= some, = some, <> some are the constructs allowed for comparison. = some is same as the ‘in’ connectivity. <> some is not the same as the ‘not in’ connectivity.
Similarly sql also provides < all, >all, <=all, >= all, <> all comparisons. <>all is same as the ‘not in’ construct.
3 Set cardinality: The ‘exists’ construct returns the value true if the argument subquery is nonempty. We can test for the non- existence of tuples in a subquery by using the ‘not exists’ construct. The ‘not exists ‘ construct can also be used to simulate the set containment operation (the super set ). We can write “relation A contains relation B” as “not exists (B except A)”.
The complete Syntax of select statement containing connectivity or Comparison operators is as follows
select <attribute-list> from<relation-list>
where <connectivity / comparison > { sub-query };
SQL includes a feature for testing whether a subquery has any tuples in its result, using the following clauses :
1 Exists: The ‘exists’ construct returns the value true if the argument subquery is nonempty.
Syntax: select <attribute-list> from<relation-list> where <exists> { sub-query} ;
2 Not exists: We can test for the non-existence of tuples in a subquery by using the ‘not exists’
construct. The ‘not exists ‘ construct can also be used to simulate the set containment
operation (the super set ). We can write “relation A contains relation B” as “not exists (B
except A)”.
Syntax: select <attribute-list> from<relation-list> where <not exists> { sub-query};
Set A
1.Create the following relations :
Emp(eno,name,dno,salary)
Project(pno,pname,control-dno,budget)
Each employee can work on one or more projects, and a project can have many employees working in it. The number of hours worked on each project , by an employee also needs to be stored. 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 , new relations 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 queries for following business tasks & execute them.
1. list the names of departments that controls projects whose budget is greater than .
2. list the names of projects, controlled by department No , whose budget is greater than at least one project controlled by department No .
3. list the details of the projects with second maximum budget
4. list the details of the projects with third maximum budget.
5. list the names of employees, working on some projects that employee number is working.
6. list the names of employees who do not work on any project that employee number works on
7. list the names of employees who do not work on any project controlled by ‘ ’ department
8. list the names of projects along with the controlling department name, for those projects which has at least employees working on it.
9. list the names of employees who is worked for more than 10 hrs on at least one project controlled by‘ ’ dept.
10. list the names of employees , who are males , and earning the maximum salary in their department.
11. list the names of employees who work in the same department as ‘ ’.
12. list the names of employees who do not live in or .
Queries:-
create table emp(
e_no int primary key,
name char(20),
d_no int,
salary int,
city char(20),
gender char
);
create table project(
p_no int primary key,
p_name char(20),
control_dno int,
budget float,
d_name char(20)
);
create table emp_project(
p_no int references project(p_no)on delete cascade,
e_no int references emp(e_no)on delete cascade,no_hours int
);
insert into emp values(01,'ankur',101,49000,'pune','m');
insert into emp values(02,'aniket',102,9000,'karad','m');
insert into emp values(03,'piyush',103,29000,'satara','m');
insert into emp values(04,'prashant',104,21000,'wakad','m');
insert into emp values(05,'omkar',105,2100,'hadapsar','m');
insert into project values(1001,'codec',105,50000,'comp sci');
insert into project values(1002,'dream code',101,50000,'comp sci');
insert into project values(1003,'sci_craft',102,5000,'sci');
insert into project values(1004,'tally',103,50200,'commerce');
insert into project values(1005,'scilab',104,502001,'math');
insert into project values(1006,'sales',104,502001,'math');
Insert into emp_project values(1001,1,2);
Insert into emp_project values(1002,2,3);
Insert into emp_project values(1003,3,4);
Insert into emp_project values(1005,5,5);
insert into emp_project values(1001,2,4);
insert into emp_project values(1001,4,4);
1.list the names of departments that controls projects whose budget is greater than 50000.
Ans.
select d_name from project where budget>50000;
2.list the names of projects, controlled by department No 101, whose budget is greater than
atleast one project controlled by department No 102.
Ans.
select p_name from project where control_dno='101'
and budget>some(select budget from project where control_dno='102') ;
3. list the details of the projects with second maximum budget
Ans.
select* from project where budget=(select max (budget) from
project where budget<(select max(budget)from project)) limit 1;
4. list the details of the projects with third maximum budget.
Ans.
select* from project where budget=(select max (budget) from
project where budget<(select max(budget)from project where
budget<(select max (budget) from project))) limit 1;
5. list the names of employees, working on some projects that employee number __ is working.
Ans.
select name from emp where e_no in(select distinct e_no from
emp_project where p_no in(select e_no from emp_project where e_no='1'));
6. list the names of employees who do not work on any project that employee number __ works
on
Ans.
select name from emp where e_no in(select distinct e_no from
emp_project where p_no not in(select e_no from emp_project where e_no='1'));
7. list the names of employees who do not work on any project controlled by ‘comp sci’
department
Ans.
select name from emp,emp_project where emp.e_no=emp_project.e_no and p_no not in
(select p_no from project where d_name='comp sci');
8. list the names of projects along with the controlling department name, for those projects
which has atleast __ employees working on it.
Ans.
select * from project where p_no in(select p_no from emp_project group by p_no having count(e_no)>=1);
9. list the names of employees who is worked for more than 10 hrs on atleast one project
controlled by ‘comp sci’ dept.
Ans.
select name from emp_project,emp where emp.e_no=emp_project.e_no and p_no not in
(select p_no from project where d_name='comp sci' and no_hours>7);
10. list the names of employees , who are males , and earning the maximum salary in their
department.
Ans.
select name from emp_project,emp where emp.e_no=emp_project.e_no and p_no in
(select p_no from project where d_name='comp sci' and no_hours>7);
11. list the names of employees who work in the same department as ‘comp sci’.
Ans.
select emp.name from emp,emp_project,project
where emp.e_no=emp_project.e_no and project.p_no=emp_project.p_no
and d_name='comp sci';
12. list the names of employees who do not live in pune or karad.
Ans.
select * from emp where city!='pune' and city!='karad';
No comments:
Post a Comment