[go: up one dir, main page]
Scribd
Upload
118 views9 pages

Dbms Assignment: Create Table Commands

The document contains the SQL commands to create tables, insert data, and define foreign key constraints for an employee database. It then lists 11 queries against this database schema including retrieving manager names and dependent counts, employee names for a specific project/location, counting employees or projects for different departments, and more. The queries use joins across multiple tables to retrieve the requested data.

Uploaded by

Himanshu Mishra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
118 views9 pages

Dbms Assignment: Create Table Commands

The document contains the SQL commands to create tables, insert data, and define foreign key constraints for an employee database. It then lists 11 queries against this database schema including retrieving manager names and dependent counts, employee names for a specific project/location, counting employees or projects for different departments, and more. The queries use joins across multiple tables to retrieve the requested data.

Uploaded by

Himanshu Mishra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

DBMS ASSIGNMENT

Create table commands


drop table employee cascade constraint;
drop table department cascade constraint;
drop table project cascade constraint;
drop table workson cascade constraint;
drop table dependent cascade constraint;
drop table dept_loc cascade constraint;

Inserting data into the tables


create table department
(
Dname varchar(10) unique,
Dnum int primary key,
Mgrssn int,
Mgrstdate date
);

create table employee


(
Fname varchar(10),
Mname varchar(10),
Lname varchar(10),
Ssn int primary key,
Dob date,
sex char,
sal number(8,2),
Addr varchar(50),
Superssn int,
dno int references department(dnum)
);

alter table employee add constraint fkemp foreign key(superssn) references


employee(ssn);
alter table department add constraint fkdept foreign key(mgrssn) references
employee(ssn);

create table project


(
Pname varchar(20) unique,
Pnum int primary key,
Ploc varchar(10),
deptno int references department(dnum)
);

create table dependent


(
Depname varchar(10),
essn int references employee(ssn),
sex char,
Ddob date,
Rel varchar(10),
primary key(Depname,essn)
);

create table dept_loc


(
Dnum int references department(dnum),
loc varchar(10),
primary key(dnum,loc)
);

create table workson


(
essn int references employee(ssn),
pno int references project(pnum),
hour float,
primary key(essn,pno)
);
Queries
1. Retrieve names of all managers of the department along with their number
of female dependents.

Ans : select fname,mname,lname,count(*)


from employee e,dependent d
where ssn=essn and d.sex='f'and ssn in ( select mgrssn from department )
group by ssn,fname,mname,lname;

2. Retrieve names of all managers of the department who have more than one
dependent along with their number of dependents.

Ans : select fname,mname,lname,count(*)


from employee e,dependent d
where ssn=essn and ssn in ( select mgrssn from department )
group by ssn,fname,mname,lname
having count(*)>1;
3. For each project for which more than one employee works retrieve its name
and number of employee working.

Ans : select pname,count(*)


from project,workson
where pnum in ( select pno from workson group by pno having count(*)>1)
and pnum=pno
group by pname,pnum;

4. Make a list of employee name whose address is in Vidyanagar and who


works on project productx along with number of dependents.

Ans : select e.fname,e.mname,e.lname,count(d.essn)


from project p,employee e,workson w,dependent d
where p.pname='Productx' and p.pnum=w.pno
and e.ssn=w.essn and e.addr like '%Vidyanagar%' and e.ssn=d.essn
group by e.fname,e.mname,e.lname,d.essn;
5. Retrieve names of all employee who work for the department that controls
more than one project.

Ans : select fname,mname,lname


from employee
where dno in (select dnum from department,project where dnum=deptno
group by dnum having count(*)>1);

6. Retrieve ssn, name and dept. name of all the employees who work on any
project controlled by his own department.

Ans : select ssn,fname,mname,lname,dname


from employee,department,project,workson
where dno=deptno and pnum=pno and essn=ssn and dnum=deptno and
dnum=dno
group by ssn,fname,mname,lname,dname,dnum;
7. For each department that has more than one location retrieve its name and
number of project controlled by that department.

Ans : select dname,count(*)


from project,department
where dnum in (select dnum from dept_loc group by dnum having
count(*)>1) and dnum=deptno
group by dnum,dname;

8. For each project controlled by Research department for which more than one
employee is working Retrieve name of that project and number of
employees working for it.

Ans : select pname,count(*)


from department,project,workson
where pnum in (select pnum from project,workson where pnum=pno group
by pnum having count(*)>1)
and dnum=deptno and dname='Research' and pnum=pno
group by pname;
9. For each department which controls more than one project retrieve
department name along with number of employees working in that
department.

Ans : select dname,count(*)


from department,employee
where dnum in (select dnum from department,project where dnum=deptno
group by dnum having count(*)>1)
and dnum=dno
group by dname,dnum
order by dname ;
10. For each department which do not control any project retrieve department
name along with number of employees working in that department.

Ans : select dname,count(*)


from department,employee
where dnum not in (select dnum from department,project where
dnum=deptno )
and dnum=dno
group by dname,dnum;

11. For each female employee who works for ‘Admin’ department retrieve first
name in ascending order along with number of projects the employee works
on.

Ans : select fname,mname,lname,count(*)


from workson,employee
where ssn in (select ssn from department,employee where dnum=dno and
dname='Admin' and sex='f')
and ssn=essn
group by fname,mname,lname,ssn
order by fname;

You might also like