DBMS LAB 1

1) Write a query to retrieve birth date and address of the employee(s) whose name is ‘John B Smith’.

select bdate,address from employee where fname='John' and minit='B' and lname='Smith';

BDATE ADDRESS
--------- -------------------------
09-JAN-65 731 Fondren, Houston, TX



2) Write a query to retrieve all distinct Salary values.

select distinct(sal) from employee;

SAL
--------
25000
30000
38000
40000
43000
55000


3) Write a query to retrieve all employees whose address is in Houston, Texas.

select * from employee where address like '%Houston, TX';

FNAME ADDRESS
-------- ------------------------
John 731 Fondren, Houston, TX
Franklin 638 Voss, Houston, TX
Joyce 5631 Rice, Houston, TX
Ahmad 980 Dallas, Houston, TX
James 450 Stone, Houston, TX


4) Write a query to retrieve all employees who were born during the 1950s.

select * from employee where bdate like '%5_';

FNAME BDATE
-------- ---------
Franklin 08-DEC-55


5) Write a query to retrieve the SSNs of all employees who work on project number 1, 2 or 3.

select fname,dno from employee where dno in(1,2,3);

FNAME DNO
-------- ----------
James 1

6) Write a query to retrieve all employees whose salary is between $30,000 and $40,000.

select fname,sal from employee where sal between 30000 and 40000;

FNAME SAL
-------- ----------
John 30000
Franklin 40000
Ramesh 38000

7) Write a query to retrieve the names of all employees who do not have supervisors.

select fname,superssn from employee where superssn is null;

FNAME SUPERSSN
-------- ----------
James


8) Write a query to retrieve name and address of all employees who work for 'Research' department.

select e.fname, e.address from employee e,department d where d.dname='Research' and e.dno=d.dnumber;

FNAME ADDRESS
-------- -------------------------
John 731 Fondren, Houston, TX
Franklin 638 Voss, Houston, TX
Ramesh 975 Fire Oak, Humble, TX
Joyce 5631 Rice, Houston, TX


9) Write a query to retrieve employees's first and last nam and first and last name of his or her or her immediate superisor.

select e1.fname, e1.lname, e2.fname, e2.lname from employee e1,employee e2 where e1.superssn=e2.ssn;

FNAME LNAME FNAME LNAME
-------- ---------- -------- ----------
John Smith Franklin Wong
Franklin Wong James Borg
Alicia Zelaya Jennifer Wallace
Jennifer Wallace James Borg
Ramesh Narayan Franklin Wong
Joyce English Franklin Wong
Ahmad Jabbar Jennifer Wallace

10) Write a query to show resulting salaries if every employee working on the 'Product X' project is given a 10 percent raise.

select e.sal+e.sal*0.1"Increamented Salary" from employee e,project p,works_on w where p.pname='ProductX' and p.pnumber=w.pno and w.essn=e.ssn;

Increamented Salary
-------------------
33000
27500


11) Write a query to retrieve list of employees and the projects they are working on,ordered by department and with in each department, ordered alphabetically by last name, first name.

select e.fname,e.lname,p.pname,d.dname
from department d, employee e, works_on w, project p
where e.ssn=w.essn and p.pnumber=w.pno and e.dno=d.dnumber
order by dname,lname,fname;

FNAME LNAME PNAME DNAME
----------- ---------- ------------------ ---------------
Ahmad Jabbar Computerization Administration
Ahmad Jabbar Newbenefits Administration
Jennifer Wallace Newbenefits Administration
Jennifer Wallace Reorganization Administration
Alicia Zelaya Newbenefits Administration
Alicia Zelaya Computerization Administration
James Borg Reorganization Headquaters
Joyce English ProductX Research
Joyce English ProductY Research
Ramesh Narayan ProductZ Research
John Smith ProductX Research
John Smith ProductY Research
Franklin Wong ProductY Research
Franklin Wong ProductZ Research
Franklin Wong Computerization Research
Franklin Wong Reorganization Research


12) Find the sum of the salaries of all employees, the maximum salary, the minimum salary and the average salary.

select sum(sal) "Sum",max(sal) "Maximum", min(sal) "Minimum",avg(sal) " Average" from employee;

Sum Maximum Minimum Average
--------- ---------- ---------- ----------
281000 55000 25000 35125


13) Find the sum of the salaries of all employees, the max.salary, the min. salary and the avg. salary of all employees of the ' Research ' Department.

select sum(sal) "Sum",max(sal) "Maximum", min(sal) "Minimum",avg(sal) " Average" from employee where dno=5;

Sum Maximum Minimum Average
-------- ---------- ---------- ----------
133000 40000 25000 33250

14) Count the number of employees working in the ' Research' Department.

select count(*) from employee e, department d where e.dno=d.dnumber and dname='Research';

COUNT(*)
---------
4


15) For each department, retrieve the department number, the number of employees in the department and their average salary.

select dno,count(*) "no. of emp",avg(sal) "Average" from employee group by dno;

DNO no. of emp Average
---- --------- ----------
1 1 55000
4 3 31000
5 4 33250


16) For each project, retrieve the project number, Project name and the number of employees who work on that project.

select p.pname,p.pnumber,count(w.pno) from project p, works_on w where p.pnumber=w.pno group by w.pno,p.pname,p.pnumber;

PNAME PNUMBER COUNT(W.PNO)
------------------ ---------- ------------
ProductX 1 2
ProductY 2 3
ProductZ 3 2
Computerization 10 3
Reorganization 20 3
Newbenefits 30 3


17) For each project on which more than two employees work, retrieve the project number, project name and the number of employees who work on the project.

select p.pname,p.pnumber,count(w.pno) from project p, works_on w where p.pnumber=w.pno group by w.pno,p.pname,p.pnumber having count(w.pno)>2;

PNAME PNUMBER COUNT(W.PNO)
------------------ ---------- ------------
ProductY 2 3
Computerization 10 3
Reorganization 20 3
Newbenefits 30 3


18) For each project, retrieve the project number, Project name and the number of employees from department 5 who work on the project.

select p.pname,p.pnumber,count(w.pno) from project p, works_on w where p.pnumber=w.pno and p.dnum=5 group by w.pno,p.pname,p.pnumber;

PNAME PNUMBER COUNT(W.PNO)
------------------ ---------- ------------
ProductX 1 2
ProductY 2 3
ProductZ 3 2


19) Write a query to retrieve the names of all employees who have two or more dependents.

select e.fname from employee e,dependant d where e.ssn=d.essn group by d.essn,e.fname having count(essn)>2 ;

FNAME
--------
John
Franklin


20) Write a query to retrieve the names of all employees whose salary is greater than the salary of all the employees in department 5.

select fname,sal from employee where sal>(select max(sal) from employee where dno=5);
select fname,sal from employee where sal> ALL (select sal from employee where dno=5);
FNAME SAL
-------- ----------
Jennifer 43000
James 55000


21). Write a query to retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

select e.fname from employee e,dependant d where e.fname=d.dependent_name and e.sex=d.sex and e.ssn=d.essn;
select e.fname from employee e where e.ssn IN (select essn from dependant d where e.fname=d.dependent_name and e.sex=d.sex);

no rows selected


22) Write a query to retrieve the names of employees who have no dependents.

select fname from employee where ssn not in(select essn from dependant);

FNAME
-----------
Alicia
Ramesh
Joyce
Ahmad
James


23) Write a query to list the names of managers who have at least one dependent.

select distinct(e.fname) from employee e, department dt, dependant d
where e.ssn=dt.mgrssn and dt.mgrssn=d.essn;

FNAME
--------
Franklin
Jennifer


24) For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000.



25) For every project located in ‘Stafford’, list the project number, the controlling department number and the department manager’s last name, birth date.



26) Write a query to make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.






FNAME MINIT LNAME SSN BDATE ADDRESS SEX SAL SUPERSSN DNO
------------------------------------------------------------------------------------------------------------------------------------------------------------------

John B Smith 123456789 9-JAN-1965 731 Fondren, Houston, TX M 30000 333445555 5

Franklin T Wong 333445555 08-DEC-1955 638 Voss, Houston, TX M 40000 888665555 5

Alicia J Zelaya 999887777 19-JUL-1968 3321, Castle, Spring, TX F 25000 987654321 4

Jennifer S Wallace 987654321 20-JUN-1941 291, Berry, Bellaire, TX F 43000 888665555 4

Ramesh K Narayan 666884444 15-SEP-1962 975 Fire Oak, Humble, TX M 38000 333445555 5

Joyce A English 453453453 31-JUL-1972 5631 Rice, Houston, TX F 25000 333445555 5

Ahmad V Jabbar 987987987 29-MAR-1969 980 Dallas, Houston, TX M 25000 987654321 4

James E Borg 888665555 10-NOV-1937 450 Stone, Houston, TX M 55000 1


create table employee(fname varchar2(8), minit char(3), lname varchar2(10), ssn number(10), bdate date, address varchar2(25), sex char(3), sal number(5), superssn number(10), dno number(3));

insert into employee values('John','B', 'Smith',123456789,'9-JAN-1965','731 Fondren, Houston, TX', 'M' ,30000, 333445555,5);
insert into employee values('Franklin', 'T', 'Wong' ,333445555, '08-DEC-1955', '638 Voss, Houston, TX', 'M', 40000, 888665555,5);
insert into employee values('Alicia', 'J', 'Zelaya', 999887777, '19-JUL-1968', '3321, Castle, Spring, TX', 'F', 25000, 987654321,4);
insert into employee values('Jennifer', 'S', 'Wallace', 987654321, '20-JUN-1941', '291, Berry, Bellaire, TX', 'F', 43000, 888665555,4);
insert into employee values('Ramesh', 'K', 'Narayan', 666884444, '15-SEP-1962', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5);
insert into employee values('Joyce', 'A', 'English', 453453453, '31-JUL-1972', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5);
insert into employee values('Ahmad', 'V', 'Jabbar', 987987987, '29-MAR-1969', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4);
insert into employee values('James', 'E', 'Borg', 888665555, '10-NOV-1937', '450 Stone, Houston, TX', 'M', 55000,NULL, 1);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DNAME DNUMBER MGRSSN MGRSTARTDATE
-----------------------------------------------------------------------------------------------------
Research 5 333445555 22-MAY-1988
Administration 4 987654321 01-JAN-1995
Headquarters 1 888665555 19-JUN-1981


create table department(dname varchar2(15), dnumber number(3), mgrssn number(10), mgrstartdate date);

insert into department values('Research', 5, 333445555, '22-MAY-1988');
insert into department values('Administration', 4, 987654321, '01-JAN-1995');
insert into department values('Headquaters', 1, 888665555, '19-JUN-1981');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Table 3: PROJECT

PNAME PNUMBER PLOCATION DNUM
----------------------------------------------------------------------
ProductX 1 Bellaire 5
ProductY 2 Sugarland 5
ProductZ 3 Houston 5
Computerization 10 Stafford 4
Reorganization 20 Houston 1
Newbenefits 30 Stafford 4

create table project (pname varchar2(18), pnumber number(7), plocation varchar2(10), dnum number(3));

insert into project values('ProductX', 1, 'Bellaire', 5);
insert into project values('ProductY', 2, 'Sugarland', 5);
insert into project values('ProductZ', 3, 'Houston', 5);
insert into project values('Computerization', 10, 'Stafford', 4);
insert into project values('Reorganization', 20, 'Houston', 1);
insert into project values('Newbenefits', 30, 'Stafford', 4);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Table 4: WORKS_ON

ESSN PNO HOURS
--------------------------------------
123456789 1 32.5
123456789 2 7.5
666884444 3 40
453453453 1 20
453453453 2 20
333445555 2 10
333445555 3 10
333445555 10 10
333445555 20 10
999887777 30 30
999887777 10 10
987987987 10 35
987987987 30 5
987654321 30 20
987654321 20 15
888665555 20 0

create table works_on(essn number(10), pno number(7), hours number(5,2));

insert into works_on values(123456789, 1, 32.5);
insert into works_on values(123456789, 2, 7.5);
insert into works_on values(666884444, 3, 40);
insert into works_on values(453453453, 1, 20);
insert into works_on values(453453453, 2, 20);
insert into works_on values(333445555, 2, 10);
insert into works_on values(333445555, 3, 10);
insert into works_on values(333445555, 10, 10);
insert into works_on values(333445555, 20, 10);
insert into works_on values(999887777, 30, 30);
insert into works_on values(999887777, 10, 10);
insert into works_on values(987987987, 10, 35);
insert into works_on values(987987987, 30, 5);
insert into works_on values(987654321, 30, 20);
insert into works_on values(987654321, 20, 15);
insert into works_on values(888665555, 20, 0);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Table 5: DEPENDANT

ESSN DEPENDENT_NAME SEX BDATE RELATION
----------------------------------------------------------------------------------------------
333445555 Alice F 05-APR-86 DUAGHTER
333445555 Theodore M 25-OCT-83 SON
333445555 Joy F 03-MAY-58 SPOUSE
987654321 Abner M 28-FEB-42 SPOUSE
123456789 Michael M 04-JAN-88 SON
123456789 Alice F 30-DEC-88 DAUGHTER
123456789 Elizabeth F 05-MAY-67 SPOUSE

create table dependant(essn number(10), dependent_name varchar2(10), sex char(3), bdate date, relation varchar2(10));

insert into dependant values(333445555, 'Alice', 'F', '05-APR-86', 'DUAGHTER');
insert into dependant values(333445555, 'Theodore', 'M', '25-OCT-83', 'SON');
insert into dependant values(333445555, 'Joy', 'F', '03-MAY-58', 'SPOUSE');
insert into dependant values(987654321, 'Abner', 'M', '28-FEB-42', 'SPOUSE');
insert into dependant values(123456789, 'Michael', 'M', '04-JAN-88', 'SON');
insert into dependant values(123456789, 'Alice', 'F', '30-DEC-88', 'DAUGHTER');
insert into dependant values(123456789, 'Elizabeth', 'F', '05-MAY-67', 'SPOUSE');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Table 6: DEPARTMENT_LOCATIONS

DNUMBER DLOCATION
----------------------------------------------
1 Houston
4 Stafford
5 Bellaire
5 Sugar land
5 Houston

create table department_locations(dnumber number(3), dlocation varchar2(10));

insert into department_locations values(1, 'Houston');
insert into department_locations values(4, 'Stafford');
insert into department_locations values(5, 'Bellaire');
insert into department_locations values(5, 'Sugar land');
insert into department_locations values(5, 'Houston');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



PRIMARY KEYS for tables
----------------------

alter table employee add constraint employee_ssn_pk primary key(ssn);
alter table department add constraint department_dnumber_pk primary key(dnumber);
alter table department_locations add constraint dept_locations_dnumdloc_pk primary key(dnumber,dlocation);
alter table project add constraint project_pnumber_pk primary key(pnumber);
alter table works_on add constraint workson_essnpno_pk primary key(essn,pno);
alter table dependant add constraint dependant_essndepen_pk primary key(essn,dependent_name);




REFERENTIAL KEYS for tables
----------------------------

alter table employee add constraint employee_superssn_fk foreign key(superssn) references employee(ssn);
alter table employee add constraint employee_dno_fk foreign key(dno) references department(dnumber);
alter table department add constraint department_mgrssn_fk foreign key(mgrssn) references employee(ssn);
alter table department_locations add constraint deptloc_dnumber_fk foreign key(dnumber) references department(dnumber);
alter table project add constraint project_dnum_fk foreign key(dnum) references department(dnumber);
alter table works_on add constraint workson_essn_fk foreign key(essn) references employee(ssn);
alter table works_on add constraint workson_pno_fk foreign key(pno) references project(pnumber);
alter table dependant add constraint dependent_essn_fk foreign key(essn) references employee(ssn);



NOT NULL for tables
--------------

alter table employee modify fname not null;
alter table employee modify lname not null;
alter table employee modify ssn not null;
alter table employee modify dno not null;

alter table department modify dname not null;
alter table department modify dnumber not null;
alter table department modify mgrssn not null;

alter table department_locations modify dnumber not null;
alter table department_locations modify dlocation not null;

alter table project modify pname not null;
alter table project modify pnumber not null;
alter table project modify dnum not null;

alter table works_on modify essn not null;
alter table works_on modify pno not null;
alter table works_on modify hours not null;

alter table dependant modify essn not null;
alter table dependant modify dependent_name not null;

alter table department add constraint department_dname_uni unique(dname);