Exercise 7: Demonstration of Select Command
Create the following tables ( primary keys are
underlined).
Emp(eno,ename,sal,address,ph_no) Dept(dno, name,
loc)
Emp and Dept are related with many to one with each
other.
Create the Relations accordingly, so that the
relationship is handled properly and relations are in normalized form
(3NF). Execute following select queries
& write the business task performed by each query.
1.Select * from emp;
2.Select empno, name from emp;
3.Select distinct deptno from emp;
4.Select * from emp where deptno =103;
5.Select * from emp where address = ‘pune’ and sal
> 30000;
6.Select * from emp where address = ‘pune’ and
salary between 10000 and 30000
7.Select * from emp where name like ‘s%’
8.Select * from emp where name like ‘%s and a%’
9.Select * from emp where salary is null;
10.Select * from emp order by eno;
11.Select * from emp order by deptno, eno desc;
12.Select deptno as department, sum(salary) as total
from emp group by deptno order by deptno;
13.Select deptno as department , count(eno) as
total_emp from emp group by deptno having count(eno ) > 4 order by deptno;
14.select avg(salary) from emp;
15.select max(salary),deptno from emp group by
deptno having max(sal) > 30000;
16.select deptno, min(salary) from emp order by
deptno;
17.update emp set salary = salary + 0.5*salary where
deptno = (select deptno from department where dname = ‘finance’);
18.update emp set deptno = (select deptno from
department where dname = ‘finance’) Where deptno = (select deptno from
department where dname = ‘inventory’);
Create the following tables ( primary
keys are underlined).
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
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 project values(1001,'codec',105,50000,'comp sci');
Insert
into emp_project values(1001,1,2);
1.list
the names of departments that controls projects whose budget is greater than 60000.
SQL>Select
d_name from project where budget>60000;
2.list
the names of projects, controlled by department No 201, whose budget is greater
than atleast one project controlled by department No 202.
SQL>select
p_name from project where control_dno='201'
and budget>some(select budget from project where control_dno='202') ;
3.list
the details of the projects with second maximum budget
SQL>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.
SQL>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.
SQL>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
SQL>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
SQL>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.
SQL>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.
SQL>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>10);
10.list the names of employees , who are males , and
earning the maximum salary in their department.
SQL>select name,salary from emp_project,emp where
emp.e_no=emp_project.e_no and
gender='male' and salary =(select max(salary)from emp);
11.list
the names of employees who work in the same department as ‘comp sci’.
SQL>
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 mumbai.
SQL>select
* from emp where city!='pune' and city!='mumbai';
Movies(M_name, release_year, budget)
Consider
the relations
Person
(pnumber, pname, birthdate, income)
Area(
aname,area_type)
An
area can have one or more person living in it , but a person belongs to exactly
one area. The
attribute
‘area_type’ can have values as either urban or rural.
Create
the Relations accordingly, so that the relationship is handled properly and the
relations are
in
normalized form (3NF).Assume appropriate data types for all the attributes. Add
any new attributes as required,
depending
on the queries. Insert sufficient number of records in the relations / tables
with
appropriate
values as suggested by some of the queries.Write select queries for following
business tasks and execute them
create
table Area(aname varchar(20) primary key,area_type varchar check( area_type
in('rural','urban')));
create
table Person(pnumber int Primary key,pname varchar(20),birthdate date,income
numeric(7,2),aname varchar(20),
constraint
fk foreign key(aname) references Area(aname) on delete cascade on update
cascade);
insert
into Area values('Pune','urban');
insert
into Person values(101,'Raj Hawaldar','08/24/1995',50000.00,'Pune');
1.
List the names of all people living in ‘pune’ area.
SQL>Select
* from Person where aname='Pune';
2.
List details of all people whose names start with the alphabet ‘R_’ &
contains maximum 1 alphabets in it.
SQL>Select
pname,max(char_length(pname)) from Person where pname like'R%' group by pname
limit 1;
3.List
the names of all people whose birthday falls in the month of 8.
SQL>select
pname,birthdate from Person where extract (Month from birthdate)=08;
4.Give
the count of people who are born on ‘08/24/1995’
SQL>Select
pname,count(*) from person where birthdate='08/24/1995';
5.Give
the count of people whose income is below 40000.
SQL>Select
pname,income from person where income<40000;
6.List
names of all people whose income is between 10000 and 30000;
SQL>Select
pname,income from person where income between 10000 and 30000;
7.List
the names of people with average income
SQL>Select
pname,avg(income) from person group by pname;
8.List
the sum of incomes of people living in ‘pune’
SQL>select
aname,sum(income) from person group by aname having aname='Pune';
9.List
the names of the areas having people with maximum income (duplicate areas must
be
omitted
in the result)
SQL>select
max(income),aname from person group by aname;
10.Give
the count of people in each area
SQL>Select
aname,count(*) from person group by aname;
11.List
the details of people living in ‘pune’ and having income greater than 50000;
SQL>Select
pname,income from person where aname='Pune' and income>50000;
12.List
the details pf people, sorted by person number
SQL>Select
* from person order by pnumber ;
13.List
the details of people, sorted by area, person name
SQL>Select
* from person order by aname,pname;
14.List
the minimum income of people
SQL>Select
min(income) from person;
15.Transfer
all people living in ‘pune’ to ‘mumbai’.
SQL>Update
person set aname='Mumbai' where aname='Pune';
Actor(A_name, role, charges, A_address)
Producer(producer_id, name, P_address)
Each actor has acted in one or more movies. Each producer has produced many movies and each movie can be produced by more than one producers. Each movie has one or more actors acting in it, in different roles. Create the relations accordingly, so that the relationship is handled properly and the relations are in normalized form(3NF). Insert sufficient number of appropriate records.
create table movie(mname varchar(20) primary key,ryr int,budget int);
create table actor(aname varchar(20) primary key,role varchar(20),charges int,aadd varchar(20));
create table producer(pid int primary key,pname varchar(20),padd varchar(20));
create table ma(mname varchar(20) references movie ,aname varchar(20) references actor);
create table mp(mname varchar(20) references movie ,pid int references prod);
insert into movie values('DDLJ',2008,2500000);
insert into actor values('SRK','Hero',510000,'Mumbai');
insert into producer values(101,'Sanjay','Pune');
insert into ma values('DDLJ','SRK');
insert into mp values('DDLJ',101);
1.List the names of actors who have acted in at least one movie, in which ‘SRK’ has acted.
SQL>select aname from ma where mname in(select mname from ma where aname='SRK');
2.List the names of the movies with the highest budget.
SQL>select mname,budget from movie where budget in(select max(budget) from movie);
3.List the names of actors who have acted in the maximum number of movies.
SQL>select aname,count(mname) from ma group by aname having count(mname)>=all(select count(mname) from ma group by aname);
4.List the names of movies, produced by more than one producer.
SQL>select mname from mp group by mname having count(pid)>1;
5.List the names of actors who are given with the maximum charges for their movie.
SQL>select aname from actor where charges in(select max(charges) from actor);
6.List the names of producers who produce the same movie as ‘________’.
SQL>select pname from prod where pid in(select pid from mp where mname in(select mname from mp where pid=101));
7. List the names of actors who do not live in _________or _________ city.
SQL>select aname from actor where aadd!='Pune';
