COMMON GROUP FUNCTIONS:
COUNT( )
COUNT(*) : Returns number of rows from the table including duplication
and null values .
COUNT(expr) : Returns number of non null values in the given column or
expression.
MAX( ) : Returns maximum value in the column.
MIN( ) : Returns minimum value in the column.
SUM( ) : Returns the sum of all values in the column.
AVG( ) : Returns average of all values in the column.
Using Group Functions
SELECT column,group_functions(col) FROM table [WHERE condition]
[ORDER BY column];
Using GROUP BY clause
SELECT column,group_functions(col) FROM table [WHERE condition]
[GROUP BY group_by_exp] [ORDER BY column];
Using HAVING clause
SELECT column,group_functions(col) FROM table [WHERE condition]
[GROUP BY group_by_exp] [HAVING group_condition]
[ORDER BY column];
SUB QUERIES:
SELECT select_list FROM table WHERE expr operator(SELECT select_list
FROM table);
The subquery (inner query) executes once before the main query(outer query).
The main query uses the result of sub query.
Guidelines for sub queries
A sub-query must be enclosed in parenthesis.
A sub-query must appear on the right side of comparison operator.
Sub-queries cannot contain an ORDER BY clause.
Use single row operators with single row sub-queries.
Use multiple rows operators with multiple row sub-queries.
Types of sub - queries
1. Single row sub-queries
A single row sub-query is one that returns only one row from the inner SELECT statement. This type of sub-query uses a sinle row operator.
Single row comparison operators. =,>,<,>=,<=,<>
2. Multiple rows sub-queries
A multiple row sub-query is one that returns more than one row from the inner SELECT statement. This type of sub-query uses multiple row comparison operators.
Multiple row comparison operators.
IN Equal to any member in the sub query's result list.
ANY Compare value to each value returned by tghe sub-query.
=ANY is equivalent to IN.
ALL Compare value to every value returned by the sub-query.
3. Inline views:
A sub-query is in the FROM clause used for defining an intermediate result set to query from.
4. Multiple column sub-queries:
A sub-query that contains more than one column of return data in addition to, how ever many rows are given in the output.
QUERIES:
1. Retrieve the birth date and address of the employee(s) whose name ‘John
B Smith’.
SQL> select bdate,address from employee where fname='john' and minit='b' and lname='smith';
BDATE ADDRESS
---------------------- -----------------------------------
09-JAN-65 731 fondren,houston,tx
02. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.
SQL> select d.ssn,d.fname,d.lname,e.fname "msg fname",e.lname "msg lanme"
from employee e,employee d where d.superssn=e.ssn;
SSN FNAME LNAME msg fname msg lanme
---------- ------------------------- ------------------------------------------------- -
123456789 john smith franklin wong
333445555 franklin wong james borg
999887777 alicia zelaya jennifer Wallace
987654321 jennifer Wallace james borg
666884444 ramesh narayan franklin wong
453453453 joyce English franklin wong
6 rows selected.
03. Display ssn of all employees.
SQL> select ssn,fname from employee;
SSN FNAME
---------- -------------------------
123456789 john
333445555 franklin
999887777 alicia
987654321 jennifer
666884444 ramesh
453453453 joyce
888665555 james
7 rows selected.
04. Select all combinations of employee ssn and department dname .
SQL> select ssn,dname from department,employee;
SSN DNAME
---------- ---------------------
123456789 research
123456789 administration
123456789 headquarters
333445555 research
333445555 administration
333445555 headquarters
999887777 research
999887777 administration
999887777 headquarters
987654321 research
987654321 administration
987654321 headquarters
666884444 research
666884444 administration
666884444 headquarters
453453453 research
453453453 administration
453453453 headquarters
888665555 research
888665555 administration
888665555 headquarters
21 rows selected.
05. Retrieve the salary of every employee.
SQL> select fname,salary from employee;
FNAME SALARY
------------------------- ----------
john 30000
franklin 40000
alicia 25000
jennifer 43000
ramesh 38000
joyce 25000
james 55000
7 rows selected.
06. Retrieve all employees whose address is in Houston, Texas.
SQL> select fname,address from employee where address like '%houston,tx';
FNAME ADDRESS
------------------------- ----------------------
john 731 ,fondren,houston,tx
franklin 638, voss,houston,tx
joyce 5631, rice,houston,tx
james 450, stone,houston,tx
4 rows selected.
07. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.
SQL> select salary, fname, dno, (salary+salary*(10/100)) "salary increase" from employee where dno=(select dnum from project where pname='productx');
SALARY FNAME DNO salary increase
---------- ------------------------- ---------- --------------------
30000 john 5 33000
40000 franklin 5 44000
38000 ramesh 5 41800
25000 joyce 5 27500
08. Retrieve all employees in department 5 whose salary is between $30,000
and $40,000.
SQL> select fname, salary from employee where salary between 30000 and
40000 and dno=5;
FNAME SALARY
------------------------- ------------
john 30000
franklin 40000
ramesh 8000
09. Retrieve a list of employees and the projects they are working on, ordered by department an within each department, ordered alphabetically by last name, first name.
SQL>select fname,lname,department.dname,project.pname from employee ,department, project, works_on where employee.ssn=works_on.essn and works_on.pno=project.pno and department.dnumber=employee.dno order by dname,fname,lname;
FNAME LNAME DNAME PNAME
------------- ------------ ------------------------- ---------------
Alicia zelaya administration newbenefits
alicia zelaya administration computerization
jennifer wallace administration newbenefits
jennifer wallace administration reorganization
james borg headquarters reorganization
franklin wong research producty
franklin wong research productz
franklin wong research computerization
franklin wong research reorganization
john smith research productx
john smith research producty
joyce english research productx
joyce english research producty
ramesh narayan research productz
14 rows selected.
10. Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.
SQL> select e.fname,e.lname from employee e where e.ssn in (select essn from dependent where e.fname=dependent_name and e.sex=sex);
no rows selected
11. Retreive the social security numbers of all employees who works on project number 1,2 or 3.
SQL> select ssn,fname from employee where dno in(1,2,3);
SSN FNAME
---------- -------------------------
888665555 james
12. Retrieve the names of all employees who do not have supervisors.
SQL> select fname, ssn, superssn from employee where superssn is null;
FNAME SSN SUPERSSN
------------------------- ---------- ----------
james 888665555
13. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.
SQL> select max(salary),min(salary),avg(salary) from employee;
MAX(SALARY) MIN(SALARY) AVG(SALARY)
----------- ----------- -----------
55000 25000 36571.4286
14. 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 max(salary),min(salary),avg(salary) from employee where dno=(select dnumber from department where dname='research');
MAX(SALARY) MIN(SALARY) AVG(SALARY)
----------- ----------- -----------
40000 25000 33250
15. Retrieve the total number of employees in the company.
SQL> select count(*) from employee;
COUNT(*)
----------
7
16. Retrieve the number of employees in the ‘Research’ department .
SQL> select count(*) from employee where dno=(select dnumber from department where dname='research');
COUNT(*)
----------
4
17. Count the number of distinct salary values in the database.
SQL> select distinct salary from employee;
SALARY
----------
25000
30000
38000
40000
43000
55000
6 rows selected.
18. For each department, retrieve the department number, the number of employees in the department, and their average salary.
SQL> select dno,count(*),avg(salary) from employee group by dno;
DNO COUNT(*) AVG(SALARY)
---------- ---------- -----------------------------
1 1 55000
4 2 34000
5 4 33250
19. For each project, retrieve the projrct number, the project name, and the number of employees who work on that project
SQL> select pnumber,pname,count(*) from works_on,project where pnumber=pno group by pnumber,pname;
PNUMBER PNAME COUNT(*)
---------- ------------------------- ------------------
1 productx 2
2 producty 3
3 productz 2
10 computerization 3
20 reorganization 3
30 newbenefits 3
6 rows selected.
20. 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 pnumber,pname,count(*) from works_on,project where pnumber=pno group by pnumber,pname
2 having count(*)>2;
PNUMBER PNAME COUNT(*)
---------- ------------------------- ---------
2 producty 3
10 computerization 3
20 reorganization 3
30 newbenefits 3
21. 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 pnumber,pname,count(*) from works_on,employee,project where pnumber=pno and essn=ssn and dno=5 group by pnumber,pname;
PNUMBER PNAME COUNT(*)
---------- ------------------------- ----------
1 productx 2
2 producty 3
3 productz 2
10 computerization 1
20 reorganization 1
22. 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.
SQL> select dnumber,count(*) from department,employee where dnumber=dno and salary>40000 and dno in(select dno from employee group by dno having count(*)>5) group by dnumber;
no rows selected
23. Find the number of employees who get commission.
SQL> select count(*) from emp where comm>0;
COUNT(*)
----------
3
(or)
SQL> select count(*) from emp where comm is not null;
COUNT(*)
----------
3
24. Find the average commission for employees.
SQL> select avg(comm) from emp where comm>0;
AVG(COMM)
----------
733.333333
SQL> select avg(comm) from emp where comm is not null;
AVG(COMM)
----------
733.333333
25. Find the average salary in each department.
SQL> select deptno,avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
26. Find the departments having maximum salary greater than $2000.
SQL> select deptno,max(sal) from emp group by deptno having max(sal)>2000;
DEPTNO MAX(SAL)
---------- ----------
10 5000
20 3000
30 2850
27. Find the departments having average salary greater than $2000.
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
DEPTNO AVG(SAL)
---------- ---------
10 2916.66667
20 2175
28. Display the job title and monthly salary for every job whose payroll exceeds $5000.
SQL> select job,sum(sal) from emp group by job having sum(sal)>5000;
JOB SUM(SAL)
--------- ----------
ANALYST 6000
MANAGER 8275
SALESMAN 5600
29. Find the department that has maximum average salary.
SQL> select max(avg(sal)) from emp group by deptno;
MAX(AVG(SAL))
-------------
2916.66667
30. Find the employees who get salary greater than the salary of the employee whose number is 7566.
SQL> select ename,sal from emp where sal>(select sal from emp where empno=7566);
ENAME SAL
---------------- ----------
SCOTT 3000
KING 5000
FORD 3000
31. Find the employees who have same job as employee numbered 7369 and having salary same as the salary of the employee numbered 7876 .
SQL> select ename,sal from emp where job=(select job from emp where empno=7369) and sal=(select sal from emp where empno=7876);
ENAME SAL
-------------------- ----------
ADAMS 1100
32. Display the department numbers of employees whose salary is less than the salary of any employee in department no 30 .
SQL> select deptno from emp where sal< any( select sal from emp where deptno=30); DEPTNO ---------- 20 30 30 30 10 30 20 30 10 9 rows selected. 33. Display the names and salaries of employees whose salary is less than the salary of any clerk and whose job is not clerk. SQL> select ename,sal from emp where sal< any(select sal from emp where job='clerk') and job!='clerk'; no rows selected SQL> select ename,sal from emp where sal< (select max(sal) from emp where job='clerk') and job!='clerk'; no rows selected 34. Display the employee names whose salary is greater than the average salary of all the departments. SQL> select ename,sal from emp where sal> all(select avg(sal) from emp group by deptno);
ENAME SAL
-------------------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
35. Display the name, salary and the department of employees who get salary greater than the average salary of their respective department.
SQL> select d.ename,d.sal,d.deptno from emp e,emp d where e.deptno=d.deptno group by d.deptno,d.sal,d.ename,e.deptno having d.sal > avg(e.sal);
ENAME SAL DEPTNO
-------------------- ---------- ----------
KING 5000 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
ALLEN 1600 30
BLAKE 2850 30
6 rows selected.
36. Display the names of all employees and their hire date who work in the same department as Blake.
SQL> select ename,deptno from emp where deptno=(select deptno from emp where ename='BLAKE');
ENAME DEPTNO
-------------------- ----------
ALLEN 30
WARD 30
MARTIN 30
BLAKE 30
TURNER 30
JAMES 30
6 rows selected.
37. Display the employee number and names of all employees who earn more than the average salary .Sort the results in descending order of salary.
SQL> select empno,ename from emp where sal >(select avg(sal) from emp);
EMPNO ENAME
---------- --------------------
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7902 FORD
6 rows selected.
38. Display the employee number and name for all the employees who work in a department with any employee whose name contains a T.
SQL> select empno,ename from emp where deptno in(select deptno from emp where ename like '%T%');
EMPNO ENAME
---------- --------------------
7369 SMITH
7876 ADAMS
7902 FORD
7788 SCOTT
7566 JONES
7499 ALLEN
7698 BLAKE
7654 MARTIN
7900 JAMES
7844 TURNER
7521 WARD
11 rows selected.
39. Display the employee name, department number and job title for all employees whose department location is Dallas.
SQL> select deptno,ename,job from emp where deptno=(select deptno from dept where loc='dallas');
DEPTNO ENAME JOB
---------- -------------------- ---------
20 SMITH CLERK
20 JONES MANAGER
20 SCOTT ANALYST
20 ADAMS CLERK
20 FORD ANALYST
40. Display the employee name and salary of all employees who report to King
SQL> select ename,sal from emp where mgr=(select empno from emp where ename='KING');
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
41. Display the employee number, name and salary for all employees who earn more than the average salary and who work in a department with any employee with a ‘T’ in their name.
SQL> select empno,ename,sal from emp where sal >(select avg(sal) from emp) and deptno in(select deptno from emp where ename like '%T%');
EMPNO ENAME SAL
---------- -------------------- ----------
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
42. Find Nth maximum salary of any employee using employee table.
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
43. Display the department number, name and job for all employees in the SALES department
SQL> select deptno,ename,job from emp where deptno=(select deptno from dept where dname='sales');
DEPTNO ENAME JOB
---------- -------------------- ---------
30 ALLEN SALESMAN
30 WARD SALESMAN
30 MARTIN SALESMAN
30 BLAKE MANAGER
30 TURNER SALESMAN
30 JAMES CLERK
6 rows selected.
44. Display the name, department number and salary of any employee whose department number and salary matches both the department number and salary of any employee who earns a commission .
SQL> select ename,deptno,sal from emp where (deptno,sal) in( select deptno,sal from emp where comm is not null);
ENAME DEPTNO SAL
-------------------- ---------- ----------
WARD 30 1250
MARTIN 30 1250
ALLEN 30 1600
45. Display the name, department name and salary of any employee whose salary and commission matches both the salary and commission of any employee located in DALLAS.
SQL> select ename,deptno,sal from emp where (sal,nvl(comm,0)) in(select sal,nvl(comm,0) from emp where deptno=(select deptno from dept where loc='dallas'));
ENAME DEPTNO SAL
-------------------- ---------- ----------
SMITH 20 800
ADAMS 20 1100
JONES 20 2975
SCOTT 20 3000
FORD 20 3000
46. Display the name, hire-date and salary for all employees who have both the same salary and commission as SCOTT.
SQL> select ename,hiredate,sal from emp where (sal,nvl(comm,0)) in(select sal,nvl(comm,0) from emp where ename='SCOTT') and ename!='SCOTT';
ENAME HIREDATE SAL
-------------------- --------- ----------
FORD 03-DEC-81 3000
47. Display the employees who earn a salary that is higher than the salary of any of the CLERKS .Sort the results on salary from highest to lowest.
SQL> select ename,sal from emp where sal> any(select sal from emp where job='CLERK') order by sal desc;
ENAME SAL
-------------------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
13 rows selected.
48. Display the number of employees working in department number 30.
SQL> select count(*) from emp where deptno=30;
COUNT(*)
----------
6
49. Display the average commission for all employees.
SQL> select avg(comm) from emp;
AVG(COMM)
----------
733.333333
50. Display the department number, job and sum of salary for each job grouped by department number.
SQL> select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
9 rows selected.
51. Display the title and monthly salary for every job whose payroll exceeds $5000 .
SQL> select job,sum(sal) from emp having sum(sal)>5000 group by job;
JOB SUM(SAL)
--------- ----------
ANALYST 6000
MANAGER 8275
SALESMAN 5600
52. Display the employee name ,job title and salary of employees whose salary is equal to the minimum salary .
SQL> select ename,job,sal from emp where sal =(select min(sal) from emp);
ENAME JOB SAL
-------------------- --------- ----------
SMITH CLERK 800
53. Display all departments that have a minimum salary greater than that of department whose department number is 20
SQL> select deptno,min(sal) from emp having min(sal)>(select min(sal) from emp where deptno=20) group by deptno;
DEPTNO MIN(SAL)
---------- ----------
10 1300
30 950
54. Display the name, department number, salary and commission of any employee whose salary and commission matches both the commission and salary of any employee in department number is 30 .
ENAME DEPTNO SAL COMM
---------- ---------- --------- ----------
JAMES 30 950
WARD 30 1250 500
MARTIN 30 1250 1400
TURNER 30 1500 0
ALLEN 30 1600 300
BLAKE 30 2850
6 rows selected.
0 comments:
Post a Comment