Completely Solved C, C++ Programs Assignment.




SQL-Query performs on a Project Management Database system.

Filed Under: ,

Problem Statement- Query performs on a Project Management Database system.

Consider the following database:-
i)Employee(Fname,Lname,Ssn,Bdate,Address,Sex,Salary,Superssn,Dno)
ii)Department(Dname,Dnumber,Mgrssn)
iii)Project(Pname,Pnumber,Plocation,Dnumber)
iv)Workson(Empssn,Pnumber,Hours)

Given Queries:-
1.Find the name and address of the employees who work for the research department.
2.For every project located in ‘Stafford’, list the project number,the controlling department number
and the department manager’s last name and birth date.
3.Make a list of all project numbers for projects that involve an employee whose last name Smith,
either as a worker or as a manager of the department that controls the project.
4.Find all employees whose address is in Houston.
5.Show the resulting salaries if every employee working on the product x project is given a 10%
raise.
6.Retrieve a list of employees and the project they are working on, ordered by department and
within each department, ordered alphabetically by last name, first name.
7.Retrieve the names of the employees who have no departments.
8.List the names of manager who have atleast one department.
9.Retrieve the name of each employee who works on all the projects controlled by department
number 5.
10.For each project on which more than two employees work, retrieve the project number,the
project name, and the number of employees who work on the project.
11.For each department that has more than five employees, retrieve the department number and
the number of its employees who are making than $40,000.
12.Retrieve the names of all the employees who do not have supervisors.


Commands for table creations of the schemas in:-

i) SQL> create table Employee
2 ( fname varchar2(15) NOT NULL,
3 lname varchar2(15) NOT NULL,
4 ssn char(9) NOT NULL,
5 bdate date,
6 address varchar2(30),
7 sex char(1),
8 salary number(10,2),
9 superssn char(9),
10 dno number(1) NOT NULL,
11 PRIMARY KEY (ssn),
12 FOREIGN KEY (superssn) references Employee(ssn),
13 FOREIGN KEY (dno) references Department(dnumber) );
Table Created.

SQL> INSERT INTO Employee VALUES
(‘John’,’Smith’,’123456789’,’09-Jan-65’,’Houston’,’m’,30000,’333445555’,5);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘Franklyn’,’Wong’,’333445555’,’08-Dec-55’,’Houston’,’m’,40000,’888665555’,5);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘Alicia’,’Zelaya’,’999887777’,’19-Jul-68’,’Spring’,’f’,25000,’987654321’,4);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘Jennifer’,’Wallace’,’987654321’,’20-Jun-61’,’Bellaire’,’f’,43000,’888665555’,4);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘Vincent’,’Fernandez’,’666884444’,’15-Sep-62’,’Humble’,’m’,25000,’333445555’,5);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘Joyce’,’English’,’453453453’,’31-Jul-72’,’Houston’,’f’,25000,’987654321’,4);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘Ahmad’,’Jabbar’,’987987987’,’29-Mar-69’,’Houston’,’m’,25000,’987654321’,4);
1 row created.
SQL> INSERT INTO Employee VALUES
(‘James’,’Borg’,’888665555’,’10-Nov-67’,’Houston’,’m’,30000,’333445555’,1);
1 row created.


ii) SQL> create table Department
2 ( dname varchar2(15) NOT NULL,
3 dnumber number(1) NOT NULL,
4 mgrssn char(9) NOT NULL,
5 PRIMARY KEY (dname),
6 UNIQUE (dname),
7 FOREIGN KEY (mgrssn) references Employee(ssn) );
Table Created.

SQL> INSERT INTO Department VALUES (‘Research’ , 5 , ‘333445555’);
1 row created.
SQL> INSERT INTO Department VALUES (‘Administration’ , 4 , ‘987654321’);
1 row created.
SQL> INSERT INTO Department VALUES (‘Headquarters’ , 1 , ‘888665555’);
1 row created.


iii) SQL> create table Project
2 ( pname varchar2(15) NOT NULL,
3 pnumber number(1) NOT NULL,
4 plocation varchar2(15),
5 dnumber number(1) NOT NULL,
6 PRIMARY KEY (pnumber),
7 UNIQUE (pname),
8 FOREIGN KEY (dnum) references Department(dnumber) );
Table Created.

SQL> INSERT INTO Project VALUES (‘Productx’ , 1 , ‘Ballaire’ , 5);
1 row created.
SQL> INSERT INTO Project VALUES (‘Producty’ , 2 , ‘Sugarland’ , 5);
1 row created.
SQL> INSERT INTO Project VALUES (‘Productz’ , 3 , ‘Houston’ , 5);
1 row created.
SQL> INSERT INTO Project VALUES (‘Computerization’ , 10 , ‘Stafford’ , 4);
1 row created.
SQL> INSERT INTO Project VALUES (‘Reorganization’ , 20 , ‘Houston’ , 1);
1 row created.
SQL> INSERT INTO Project VALUES (‘Newbenefits’ , 30 , ‘Stafford’ , 4);
1 row created.


iv) SQL> create table Workson
2 ( empssn char(9) NOT NULL,
3 pnumber number(1) NOT NULL,
4 hours number(3,1) NOT NULL,
5 PRIMARY KEY (empssn,pnumber),
6 FOREIGN KEY (empssn) references Employee(ssn),
7 FOREIGN KEY (pnumber) references Project(pnumber) );
Table Created.

SQL> INSERT INTO Workson VALUES (‘123456789’ , 1 , 32.5);
1 row created.
SQL> INSERT INTO Workson VALUES (‘123456789’ , 2 , 7.5);
1 row created.
SQL> INSERT INTO Workson VALUES (‘666884444’ , 3 , 40);
1 row created.
SQL> INSERT INTO Workson VALUES (‘453453453’ , 1 , 20);
1 row created.
SQL> INSERT INTO Workson VALUES (‘453453453’ , 2 , 20);
1 row created.
SQL> INSERT INTO Workson VALUES (‘333445555’ , 2 , 10);
1 row created.
SQL> INSERT INTO Workson VALUES (‘333445555’ , 3 , 10);
1 row created.
SQL> INSERT INTO Workson VALUES (‘333445555’ , 10 , 10);
1 row created.
SQL> INSERT INTO Workson VALUES (‘333445555’ , 20 , 10);
1 row created.
SQL> INSERT INTO Workson VALUES (‘987987987’ , 10 , 35);
1 row created.
SQL> INSERT INTO Workson VALUES (‘987987987’ , 30 , 5);
1 row created.
SQL> INSERT INTO Workson VALUES (‘987654321’ , 30 , 20);
1 row created.
SQL> INSERT INTO Workson VALUES (‘987654321’ , 20 , 15);
1 row created.
SQL> INSERT INTO Workson VALUES (‘123456789’ , 3 , 10);
1 row created.
SQL> INSERT INTO Workson VALUES (‘333445555’ , 1 , 33.1);
1 row created.
SQL> INSERT INTO Workson VALUES (‘333445555’ , 30 , 15);
1 row created.


Solution of the queries:-

Query Number 1 :
Find the name and address of the employees who work for the research department.

SQL Statement
SQL> select fname,lname,address
2 from Employee,Department
3 where dname=’Research’ and
4 dnumber=dno;

Output to Query number 1

FNAME LNAME ADDRESS
---------------------- ---------------------- ---------------------------------
John Smith Houston
Franklyn Wong Houston
Vincent Fernandez Humble
Joyce English Houston


Query Number 2 :
For every project located in ‘Stafford’, list the project number,the controlling department number,
and the department manager’s last name and birth date.


SQL Statement
SQL> select pnumber,dnumber,lname,bdate
2 from Project,Department,Employee
3 where Department.dnumber=Project.dnumber and

4 mgrssn=ssn and
5 plocation=’Stafford’;

Output to Query number 2

PNUMBER DNUMBER LNAME BDATE
-------------- --------------- ---------------------- --------------------
10 4 Wallace 20-Jun-61
30 4 Wallace 20-Jun-61


Query Number 3 :
Make a list of all project numbers for projects that involve an employee whose last name Smith,
either as a worker or as a manager of the department that controls the project.

SQL Statement
SQL> (select distinct pnumber
2 from Project,Department,Employee
3 where Department.dnumber=Project.dnumber and
4 empssn=ssn and
5 Lname=’Smith’)
6 union
7 (select distinct pnumber
8 from Project,Workson,Employee
9 where Project.pnumber=Workson.pnumber and
10 empssn=ssn and
11 lname=’Smith’);

Output to Query number 3

PNUMBER
--------------
1
2
3
20
30


Query Number 4 :
Find all employees whose address is in Houston.

SQL Statement
SQL> select fname,lname
2 from Employee
3 where address like ‘Houston’;


Output to Query number 4

FNAME LNAME
------------------------- -------------------------
John Smith
Franklyn Wong
Joyce English
Ahmad Jabbar
James Borg

Query Number 5 :
Show the resulting salaries if every employee working on the product x project is given a 10%
raise.

SQL Statement
SQL> select fname,lname,1.1*salary
2 from Employee,Workson,Project
3 where ssn=empssn and
4 Workson.pnumber=Project.pnumber and
5 pname=’Productx’;

Output to Query number 5

FNAME LNAME 1.1*SALARY
-------------------------- ------------------------ ----------------
John Smith 33000
Joyce English 27500



Query Number 6 :
Retrieve a list of employees and the project they are working on, ordered by department and
within each department, ordered alphabetically by last name, first name.

SQL Statement
SQL> select dname,lname,fname,pname
2 from Department,Employee,Workson,Project
3 where dnumber=dno and
4 ssn=empssn and
5 Workson.pnumber=Project.pnumber
6 order by dname,lname,fname;

Output to Query number 6

DNAME LNAME FNAME PNAME
--------------------------- ------------------------ ------------------------ ----------------------------
Administration Jabbar Ahmad Computerization
Administration Jabbar Ahmad Newbenifits
Administration Wallace Jennifer Newbenifits
Administration Wallace Jennifer Reorganization
Administration Zelaya Alicia Computerization
Administration Zelaya Alicia Newbenifits
Headquarters Borg James Reorganization
Research English Joyce Productx
Research English Joyce Producty
Research Fernandez Vincent Productz
Research Smith John Productx
Research Smith John Producty
Research Smith John Computerization
Research Wong Franklyn Producty
Research Wong Franklyn Productz
Research Wong Franklyn Reorganization


Query Number 7 :
Retrieve the names of the employees who have no departments.

SQL Statement
SQL> select fname,lname
2 from Employee
3 where not exists(select *
4 from Department
5 where ssn=empssn);


Output to Query number 7

FNAME LNAME
------------------------- -------------------------
Alicia Zelaya
Vincent Fernandez
Joyce English
Ahmad Jabbar
James Borg


Query Number 8 :
List the names of manager who have atleast one department.

SQL Statement
SQL> select fname,lname
2 from Employee
3 where exists(select *
4 from Department
5 where ssn=empssn)
6 and
7 exists(select *
8 from Department
9 where ssn=empssn);

Output to Query number 8

FNAME LNAME
------------------------- -------------------------
Franklyn Wong
Jennifer Wallace

Query Number 9 :
Retrieve the name of each employee who works on all the projects controlled by department
number 5.

SQL Statement
SQL> select lname,fname
2 from Employee
3 where not exists(select *
4 from Workson b
5 where(b.number in (select pnumber
6 from Project
7 where dnumber=5))
8 and
9 c.pnumber=b.pnumber));

Output to Query number 9

LNAME FNAME
------------------------- -------------------------
Zelaya Alicia
Wallace Jennifer
Jabbar Ahmad
Borg James


Query Number 10 :
For each project on which more than two employees work, retrieve the project number,the
project name, and the number of employees who work on the project.

SQL Statement
SQL> select pnumber,pname,count(*)
2 from Project,Workson
3 where Project.pnumber=Workson.pnumber
4 group by pnumber,pname
5 having count(*) > 2;

Output to Query number 10

PNUMBER PNAME COUNT(*)
-------------- ------------------------- --------------
2 Producty 3
10 Computerization 3
20 Reorganization 3
30 Newbenifits 3


Query Number 11 :
For each department that has more than five employees, retrieve the department number and
the number of its employees who are making than $40,000.

SQL Statement
SQL> select dnumber,count(*)
2 from Department,Employee
3 where dnumber=dno
4 Salary > 40000 and
5 dno in (select dno
6 from Employee
7 group by dno
8 having count(*) > 5)
9 group by dnumber;


Output to Query number 11

DNUMBER COUNT(*)
-------------- --------------
4 3
5 4


Query Number 12 :
Retrieve the names of all the employees who do not have supervisors.

SQL Statement
SQL> select fname,lname
2 from Employee
3 where superssn is null;

Output to Query number 12

FNAME LNAME
---------------------- ----------------------
James Borg



Discussion:-
The above database created is used to find out different information bout a companies employee details, departments, projects and project allocation. We have solved some given queries using this database, some of which have alternatives methods.

Example:-
Query no. 7 as written above to Find the employee who have no department can also be done by selecting the name of the employee who has an entre in employee table but not in project allocation table (Workson) ,on assumption that an employee without being in a particular department can not get any project allocation so will not get an entry in Workson table.
  We can write this as follows:-
“select fname,lname from employee where ssn not in(select empssn from Workson);”
 This would also give us the same output we desire.
 Back to main directory: Software Practical | Structured Query Language | DBMS


Get Free Programming Tutorials and Solved assignments