Friday, December 30, 2022
Tuesday, December 20, 2022
Tuesday, December 13, 2022
Employee All Answers
1.Retrieve the birth date and address of the employee(s) whose name is 'John B.Smith'.
SQL> select bdate,address from employee where fname='john' and minit='B' and lname='smith';
BDATE ADDRESS
--------- ----------------------------------------
09-JAN-65 731 Foundren,houstan,Tx
2.Retrieve the name and address of all employees who work for the 'Research' department.
SQL> select fname,minit,lname,address from employee where dno=(select dnum from department where dname='research');
FNAME M LNAME ADDRESS
-------------------- - -------------------- ----------------------------------------
john B smith 731 Foundren,houstan,Tx
franklin T wong 638,Voss,Houstan,Tx
ramesh k narayan 975 Fire Oak,Humble,TX
Joyce A English 5631 Rice,Houstan,Tx
3.For every project located in 'Stafford',list the project number,the controlling department number,and the department manager's last name,address,and birth date.
SQL> select p.pnum,p.dnum,e.bdate,e.address from employee e,department d,project p Where p.dnum=d.dnum and d.dnum=e.dno and p.plocations='Stafford' and d.mgr_ssn=e.ssn;
PNUM DNUM BDATE ADDRESS
---------- ---------- --------- ----------------------------------------
30 4 20-JUN-41 291Berry,Bellaire,Tx
10 4 20-JUN-41 291Berry,Bellaire,Tx
4.Retrieve the names of all employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
SQL> select e.fname,e.minit,e.lname from employee e,works_on w,project p where e.ssn=w.essn and w.pno=p.pnum and p.pname='ProductX' and w.hours>=10.0 and e.dno=5;
FNAME M LNAME
-------------------- - --------------------
john B smith
Joyce A English
5.List the names of all employees who have a dependent with the same first name as themselves.
SQL> select e.fname,e.minit,e.lname from employee e,dependent d where e.ssn=d.essn and e.fname=d.dependent_name;
no rows selected
6.Find the names of all employees who are directly supervised by 'Franklin Wong'.
SQL> select e.fname,e.minit,e.lname from employee e Where e.super_ssn=(select ssn from employee e2 where e2.fname='franklin' and e2.lname='wong');
FNAME M LNAME
-------------------- - --------------------
john B smith
franklin T wong
ramesh k narayan
Joyce A English
7.Make a list of all project numbers for project that involve an employee whose last name is 'Smith',either as a worker or as a manager of the department that controls the project.
SQL> (select w.pno from works_on w,employee e where w.essn=e.ssn and e.lname='smith') union (select pnum from employee e,department d,project p where e.ssn=d.mgr_ssn and e.lname='smith' and p.dnum=d.dnum);
PNO
----------
1
2
8.for each employee ,retrive the employee's first and last name and the first and last name of his or her immediate supervisor.
SQL> select e.fname,e.lname,s.fname,s.lname from employee e,employee s where e.super_ssn=s.ssn;
FNAME LNAME FNAME LNAME
-------------------- -------------------- -------------------- --------------------
Joyce English franklin wong
ramesh narayan franklin wong
franklin wong franklin wong
john smith franklin wong
alicia Zelaya alicia Zelaya
Ahmad jabbar Ahmad jabbar
jennifer wallace james brog
7 rows selected.
9.select all employees ssn and all combinations of employees ssn and department dname in the database.
10.Retrieve the salary of every employee and all distinct salary values.
SQL> select distinct(salary) from employee;
SALARY
----------
38000
43000
55000
30000
40000
25000
6 rows selected.
11.Retrieve all employees whose address is in Houston, Texas.
SQL> select address from employee where address like '%Houstan,Tx';
ADDRESS
----------------------------------------
638,Voss,Houstan,Tx
5631 Rice,Houstan,Tx
980 Dallas,Houstan,Tx
450 Stone,Houstan,Tx
731 Foundren,houstan,Tx
12.Find all employees who were born during the 1950s.
SQL> select fname,minit,lname,bdate from employee where bdate like '_______5_';
FNAME M LNAME BDATE
-------------------- - -------------------- ---------
franklin T wong 08-DEC-55
SQL> select fname,minit,lname,bdate from employee where to_char(bdate,'YYYY')=1955;
FNAME M LNAME BDATE
-------------------- - -------------------- ---------
franklin T wong 08-DEC-55
13.Show the resulting salaries if every employee working on the 'ProductX' project is given a 10 percent raise.
SQL> select salary+(salary*10)/100 from employee e,works_on w,project p Where e.ssn=w.essn and w.pno=p.pnum and pname='ProductX';
SALARY+(SALARY*10)/100
----------------------
33000
27500
14.Retrieve all employees in department 5 whose salary is between 30000 and 40000.
SQL> select fname,minit,lname,dno,salary from employee Where dno=5 and salary between 30000 and 40000;
FNAME M LNAME DNO SALARY
-------------------- - -------------------- ---------- ----------
john B smith 5 30000
franklin T wong 5 40000
ramesh k narayan 5 38000
15.Retrieve a list of employees and the projects they are working on, ordered by department and,within each department, ordered alphabetically by last name, then first name.
SQL> select e.fname,p.pname from employee e,works_on w,project p where e.ssn=w.essn and w.pno=p.pnum order by fname asc;
FNAME PNAME
-------------------- ---------------
Ahmad Newbenefits
Ahmad Computerization
Joyce ProductY
Joyce ProductX
alicia Computerization
alicia Newbenefits
franklin ProductZ
franklin ProductY
franklin Computerization
franklin Reorganization
james Reorganization
FNAME PNAME
-------------------- ---------------
jennifer Newbenefits
jennifer Reorganization
john ProductX
john ProductY
ramesh ProductZ
16 rows selected.
16.Find the names of employees who work on all the projects controlled by department number 5.
17.List the names of all employees with two or more dependents.
SQL> select fname,minit,lname from employee where ssn in(select essn from dependent group by essn having count(essn)>=2);
FNAME M LNAME
-------------------- - --------------------
franklin T wong
john B smith
18.Retrieve the names of employees who have no dependents.
SQL> select fname,minit,lname from employee where ssn not in(select distinct essn from dependent);
FNAME M LNAME
-------------------- - --------------------
Ahmad V jabbar
james e brog
Joyce A English
alicia J Zelaya
ramesh k narayan
19.List the names of managers who have atleast one dependent.
SQL> select fname,minit,lname from employee e,department d where e.ssn=d.mgr_ssn and ssn in(select essn from dependent group by essn having count(essn)>=1);
FNAME M LNAME
-------------------- - --------------------
franklin T wong
jennifer S wallace
20.Retrieve the names of all employees who donot have supervisers.
SQL> select fname,minit,lname from employee where super_ssn is null;
FNAME M LNAME
-------------------- - --------------------
james e brog
21.Retrieve the name of each employees who has a dependent with the same first name and is the same sex as the employee.
SQL> select e.fname,e.minit,e.lname from employee e,dependent d Where e.ssn=d.essn and e.fname=d.dependent_name and e.sex=d.sex;
no rows selected
22.Retrieve the social security numbers of all employees who work on project number 1,2, or 3.
SQL> select e.ssn from employee e,project p where p.dnum=e.dno and p.pnum in(1,2,3);
SSN
----------
123456789
123456789
123456789
333445555
333445555
333445555
666884444
666884444
666884444
453453453
453453453
SSN
----------
453453453
23.Find the sum of salaries of all employees,the maximum salary,the minimum salary,and the average salary.
SQL> select sum(salary),avg(salary),max(salary),min(salary) from employee;
SUM(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY)
----------- ----------- ----------- -----------
281000 35125 55000 25000
24.Find the sum of the salaries of all employees of the 'Research' department,as well as the maximum salary,the minimum salary,and the average salary in this department.
SQL> select sum(salary),avg(salary),max(salary),min(salary) from employee e,department d where e.dno=d.dnum and d.dname='research';
SUM(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY)
----------- ----------- ----------- -----------
133000 33250 40000 25000
25.Retrieve the total number of employees in the company.
SQL> select count(ssn) from employee;
COUNT(SSN)
----------
8
26.Retrieve the number of employees in the 'Research' department.
SQL> select count(ssn) from employee e,department d where e.dno=d.dnum and d.dname='research';
COUNT(SSN)
----------
4
27.Count the number of distinct salary values in the database.
SQL> select count(distinct(salary)) from employee;
COUNT(DISTINCT(SALARY))
-----------------------
6
28.For each department, retrieve the department number, the number of employees in the department and their average salary.
SQL> select avg(salary),dno,count(ssn) from employee group by dno;
AVG(SALARY) DNO COUNT(SSN)
----------- ---------- ----------
55000 1 1
33250 5 4
31000 4 3
29.For each project, retrieve the project number, the project name, and the number of employees who work on that project.
SQL> select p.pnum,p.pname,count(e.ssn) from employee e,project p,works_on w where w.pno=p.pnum and e.ssn=w.essn group by pnum,pname;
PNUM PNAME COUNT(E.SSN)
---------- --------------- ------------
20 Reorganization 3
1 ProductX 2
10 Computerization 3
30 Newbenefits 3
2 ProductY 3
3 ProductZ 2
6 rows selected.
30.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> select p.pnum,p.pname,count(e.ssn) from employee e,works_on w,project p where p.pnum=w.pno and e.ssn=w.essn group by p.pnum,p.pname having count(e.ssn)>2;
PNUM PNAME COUNT(E.SSN)
---------- --------------- ------------
20 Reorganization 3
10 Computerization 3
30 Newbenefits 3
2 ProductY 3
31.For each project, retrieve the project number,the project name, and the number of employees from department 5 who work on the project.
SQL> select p.pnum,p.pname,count(e.ssn) from employee e,project p,works_on w Where e.ssn=w.essn and p.pnum=w.pno and e.dno=5 group by p.pnum,p.pname;
PNUM PNAME COUNT(E.SSN)
---------- --------------- ------------
20 Reorganization 1
1 ProductX 2
10 Computerization 1
2 ProductY 3
3 ProductZ 2
32.For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than 40000.
SQL> select e.dno,count(e.ssn) from employee e,department d where e.salary>40000 group by e.dno having count(e.ssn)>=3;
DNO COUNT(E.SSN)
---------- ------------
1 3
4 3
33.DISPLAY ALL EMPLOYEE NAMES ALONG WITH THEIR DEPARTMENT NAMES.
SQL> select e.fname,e.mi,e.lname,d.dname from employee e,department d where e.dno=d.dnum;
FNAME M LNAME DNAME
--------------- - --------------- --------------------
John B Smith Research
Franklin T Wong Research
Alicia J Zelaya Administartion
Jennifer S Wallace Administartion
Ramesh K Narayana Research
Joyce A English Research
Ahmad V Jabbar Administartion
James E Brog Headquarters
8 rows selected.
34. DISPLAY ALL THE EMPLOYEE NAMES ALONG WITH DEPENDENT NAMES.
SQL> select e.fname,e.mi,e.lname,dependent_name from employee e,dependent d where e.ssn=d.essn;
FNAME M LNAME DEPENDENT_NAME
--------------- - --------------- --------------------
John B Smith Elizabeth
John B Smith Michael
Franklin T Wong Alice
Franklin T Wong Joy
Franklin T Wong Theodore
Jennifer S Wallace Abner
6 rows selected.
35.LIST THE NAMES OF EMPLOYEES ALONG WITH THE NAMES OF THEIR SUPERVISOR USING ALIAS.
SQL> select e.fname,e.lname,e.mi,s.fname,s.lname,s.mi from employee e,employee s where e.superssn=s.ssn;
FNAME LNAME M FNAME LNAME M
--------------- --------------- - --------------- --------------- -
John Smith B Franklin Wong T
Franklin Wong T James Brog E
Alicia Zelaya J Jennifer Wallace S
Jennifer Wallace S James Brog E
Ramesh Narayana K Franklin Wong T
Joyce English A Franklin Wong T
Ahmad Jabbar V Ahmad Jabbar V
7 rows selected.
36.DISPLAY NAMES OF THE DEPARTMENT AND NAME OF MANAGER FOR ALL DEPARTMENTS
SQL> select d.dname,e.fname,e.lname,e.mi from department d, employee e where e.ssn=d.mgrssn;
DNAME FNAME LNAME M
-------------------- --------------- --------------- -
Research Franklin Wong T
Administartion Jennifer Wallace S
Headquarters James Brog E
37.LIST THE DEPARTMENT OF EACH FEMALE EMPLOYEE ALONG WITH HER NAME.
SQL> select e.fname,e.mi,e.lname,d.dname from employee e,department d where e.sex='F' and e.dno=d.dnum;
FNAME M LNAME DNAME
--------------- - --------------- --------------------
Alicia J Zelaya Administartion
Jennifer S Wallace Administartion
Joyce A English Research
38.LIST ALL EMPLOYEE NAMES AND ALSO THE NAMES OF THE DEPARTMENT THEY MANAGE IF THEY HAPPEN TO MANAGE A DEPT.
SQL> select d.dname,e.fname,e.lname,e.mi from employee e,department d where e.ssn=d.mgrssn;
DNAME FNAME LNAME M
-------------------- --------------- --------------- -
Research Franklin Wong T
Administartion Jennifer Wallace S
Headquarters James Brog E
39.RETRIEVE THE NAMES OF EMPLOYEE WHO WORK ON ALL THE PROJECTS THAT 'JOHN' WORK ON
SQL> select e.fname,e.lname,e.mi from employee e where ssn in(select essn from works_on where pno in(select pno from works_on w,employee e,project p where e.ssn=w.essn and e.fname='James'));
FNAME LNAME M
--------------- --------------- -
Franklin Wong T
Jennifer Wallace S
James Brog E
40.FOR EACH PROJECT,LIST THE PROJECT NAME AND TOTAL HOURS(BY ALL EMPLOYEE) SPENT ON THAT PROJECT.
SQL> select pname,sum(hours) from project,works_on where project.pnum=works_on.pno group by pname;
PNAME SUM(HOURS)
-------------------- ----------
Computerization 55
ProductZ 50
ProductY 37.5
ProductX 52.5
Reorganization 25
Newbenifits 55
6 rows selected.
41.DISPLAY THE NAMES AND TOTAL NUMBER OF HOURS WORKED BY AN EMPLOYEE WHO IS WORKING ON MAXIMUM NUMBER OF PROJECTS AMONG ALL THE EMPLOYEE.
SQL> select fname,mi,lname,sum(hours) from employee e,works_on w where essn in(select essn from works_on group by essn having count(essn)=(select max(count(essn)) from works_on group by essn)) and e.ssn=w.essn group by fname,lname,mi;
FNAME M LNAME SUM(HOURS)
--------------- - --------------- ----------
Franklin T Wong 40
42.DISPLAY THE NAMES OF ALL EMPLOYEES AND ALSO NO OF HOURS,PROJECT NAMES THAT THEY WORK ON ON THEY HAPPEN TO WORK ON ANY PROJECT(USE OUTER JOIN).
SQL> create view dbms as(select * from employee e,works_on w where e.ssn=w.essn);
View created.
SQL> select fname,hours,pname from dbms full outer join project on dbms.pno=project.pnum;
FNAME HOURS PNAME
--------------- ---------- --------------------
John 32.5 ProductX
John 7.5 ProductY
Ramesh 40 ProductZ
Joyce 20 ProductX
Joyce 20 ProductY
Franklin 10 ProductY
Franklin 10 ProductZ
Franklin 10 Computerization
Franklin 10 Reorganization
Alicia 30 Newbenifits
Alicia 10 Computerization
FNAME HOURS PNAME
--------------- ---------- --------------------
Ahmad 35 Computerization
Ahmad 5 Newbenifits
Jennifer 20 Newbenifits
Jennifer 15 Reorganization
James Reorganization
16 rows selected.
43.LIST THE EMPLOYEE NAME,PROJECT NAME ON WHICH THEY WORK AND THE DERPARTMENT THEY BELONG TO FOR ALL THE EMPLOYEES USING ALIAS NAMES FOR THE RESULTING COLUMNS.
SQL> select fname,lname,mi,dname,pname from employee e,project p,department d,works_on w where w.pno=p.pnum and w.essn=e.ssn and d.dnum=e.dno;
FNAME LNAME M DNAME PNAME
--------------- --------------- - -------------------- --------------------
John Smith B Research ProductX
John Smith B Research ProductY
Franklin Wong T Research ProductY
Franklin Wong T Research ProductZ
Franklin Wong T Research Computerization
Franklin Wong T Research Reorganization
Joyce English A Research ProductX
Joyce English A Research ProductY
Ramesh Narayana K Research ProductZ
James Brog E Headquarters Reorganization
Jennifer Wallace S Administartion Reorganization
FNAME LNAME M DNAME PNAME
--------------- --------------- - -------------------- --------------------
Jennifer Wallace S Administartion Newbenifits
Ahmad Jabbar V Administartion Computerization
Ahmad Jabbar V Administartion Newbenifits
Alicia Zelaya J Administartion Computerization
Alicia Zelaya J Administartion Newbenifits
16 rows selected.
44.LIST ALL THE DEPARTMENTS THAT CONTAIN AT LEAST ONE OCCURENCE OF C IN THEIR NAMES.
SQL> select dname from department where dname like '%c%';
DNAME
--------------------
Research
45.LIST THE PROJECTS THAT ARE CONTROLLED BY ONE DEPARTMENT.
SQL> select pname from project p,department d where p.dnum=d.dnum and d.dnum=1;
PNAME
--------------------
Reorganization
46.LIST THE MANAGERS OF THE CONTROLLING DEPARTMENT FOR ALL THE PROJECTS.
SQL> select d.dname,p.pname,e.fname from employee e,project p,department d where d.dnum=p.dnum and e.dno=d.dnum and d.mgrssn=e.ssn;
DNAME PNAME FNAME
-------------------- -------------------- ---------------
Research ProductX Franklin
Research ProductY Franklin
Research ProductZ Franklin
Administartion Computerization Jennifer
Headquarters Reorganization James
Administartion Newbenifits Jennifer
6 rows selected.
47.LIST ALL THE LOCATION OF THE CONTROLLING DEPARTMENT FOR ALL THE PROJECTS.
SQL> select d.dlocation,p.pname,ds.dname from project p,dept_locations d,department ds where d.dnum=p.dnum and ds.dnum=p.dnum;
DLOCATION PNAME DNAME
---------- -------------------- --------------------
Bellaire ProductX Research
Houstan ProductX Research
Sugarland ProductX Research
Bellaire ProductY Research
Houstan ProductY Research
Sugarland ProductY Research
Bellaire ProductZ Research
Houstan ProductZ Research
Sugarland ProductZ Research
Stafford Computerization Administartion
Houstan Reorganization Headquarters
DLOCATION PNAME DNAME
---------- -------------------- --------------------
Stafford Newbenifits Administartion
12 rows selected.
SQL>
Subscribe to:
Posts (Atom)
-
Practice-1 I/O STATEMENTS CONDITIONAL STATEMENTS LOOPING CONSTRUCTS FUNCTIONS I-D ARRAY 2-D ARRAY STRINGS RECURSION Number Based Problems A_...
-
Design LALR Bottom up Parser. <parser.l> %{ #include<stdio.h> #include "y.tab.h" %} %% [0-9]+ {yylval...
-
Lucky Gifts "Planet Kids Entertainment Fair" is back to delight kids and parents. The Fair will have non-stop entertainment w...
