Oracle advanced query (instance based on four Scott users table)

Oracle Advanced Query (instance based on four Scott users table)

Group queries

Multi-Table Query

Sub-query

Comprehensive examples

=======================================================================

Four tables for Scott users (emp,dept,bonus,salgrade)

There are no reference http://blog.csdn.net/love_legain/article/details/54311040 for these four tables to create

-------------------------------------------

DESC EMP

Name null value type

-------- -------- ------------

EMPNO not NULL number (4)

Ename VARCHAR2 (10)

JOB VARCHAR2 (9)

MGR Number (4)

HireDate DATE

SAL Number (7,2)

COMM Number (7,2)

DEPTNO Number (2)

---------------------------------------------

DESC Dept

Name null value type

------ -------- ------------

DEPTNO not NULL number (2)

Dname VARCHAR2 (14)

LOC VARCHAR2 (13)

------------------------------------------------

Desc Salgrade

Name null value type

----- -- ------

GRADE number

Losal number

Hisal number

------------------------------------------------

DESC Bonus

Name null value type

----- -- ------------

Ename VARCHAR2 (10)

JOB VARCHAR2 (9)

SAL number

COMM number

============================= Group query ==================================

The concept of ① grouping function

A grouping function acts on a set of data and returns a value to a set of data

The use of ② grouping functions

--select AVG (SAL), sum (SAL) from EMP;

--Select Max (sal), Min (sal) from EMP;

--select Count (*) from EMP;

--select count (distinct DEPTNO) from EMP;

Wm_concat: row to Column

Select Deptno,wm_concat (ename) has abandoned the wm_concat function on the EMP group by DEPTNO--11GR2 and 12C

Use the NVL function in a grouping function: TheNVL function makes the grouping function unable to ignore null values

Select COUNT (*), COUNT (NVL (comm,0)) from EMP;

③ using GROUP BY clause data grouping

Select Deptno,avg (SAL) from the EMP group by DEPTNO;

Note: All columns in the select list that are not included in the group function should be included in the groups by clause

Columns included in the group by clause do not have to be included in the select List

④ filtering grouped result sets using a HAVING clause

Grouping functions can no longer be used in a where clause

You can use the grouping function in the HAVING clause

Select Deptno,avg (SAL) from the EMP group by DEPTNO have deptno=10;

Select Deptno,avg (SAL) from the EMP where deptno=10 group by DEPTNO;

⑤ using the order by clause in a grouped query

⑥group by statement enhanced

Select Deptno,job,sum (SAL) from the EMP group by Deptno,job;

+

Select Deptno,sum (SAL) from the EMP group by DEPTNO;

+

Select sum (SAL) from EMP;

=

Select Deptno,job,sum (SAL) from the EMP Group by Rollup (Deptno,job);

Sql*plus Reporting features

================================ Multi-table query ================================

① What is a multi-table query

Fetching data from multiple tables

② Cartesian product

③ equivalent Connection

Select E.empno,e.ename,e.sal,d.dname from emp e,dept D where E.deptno=d.deptno;

④ not equivalent connection

Select E.empno,e.ename,e.sal,s.grade from EMP e,salgrade s where e.sal between S.losal and S.hisal;

⑤ External Connection

Core: Through the external link, the link is not established record, is included in the final result

Left outer connection: When the connection condition is not true, the table to the left of the equals sign is still included

Right outer connection: When the connection condition is not true, the table to the right of the equal sign is still included

Select D.deptno Department number , d.dname department name , COUNT (e.empno) number of people from EMP e,dept D where e.deptno (+) = D.deptno GROUP by d.deptno,d.dname;-- right outer connection

⑥ Self-connect

Core: Use aliases to treat the same table as multiple tables

Select E.ename employee name , B.ename boss name from EMP e,emp b where e.mgr=b.empno;

Problem with self-connection: not suitable for operation large table

⑦ Workaround: Hierarchical query

Select Level,empno,ename,sal,mgr from EMP Connect by prior Empno=mgr start with MGR are null ORDER by 1;

============================== Sub-query ===================================

① Sub-Query overview

-- query employee information that is higher than Scott 's salary

SELECT * from emp where sal> (select Sal from emp where ename= ' Scott ');

Use of ② subqueries

Where you can use subqueries : Where,select,having,from

Main query and subquery may not be the same table

SELECT * from emp where deptno= (select Deptno from dept where Dname= ' SALES ');

Select e.* from emp e,dept D where E.deptno=d.deptno and D.dname= ' SALES ';

Sorting is not generally used in subqueries, but in top-n parsing problems, subqueries must be sorted

--rownum line number pseudo-column

Select Rownum,empno,ename,sal from (SELECT * from emp ORDER BY sal Desc) where rownum <= 3;

Line numbers are always generated in the default order

Line numbers can only use <,<=; Cannot use >,>=

A subquery is typically executed before the main query, except for related subqueries

Select Empno,ename,sal, (select AVG (SAL) from EMP where Deptno=e.deptno) avgsal the from EMP e where sal> (select AVG (SAL) from EMP where Deptno=e.deptno);

Single-row subqueries and multiline subqueries

Operator (multiple lines)

in equals any one of the list

Any value that is returned by any and a subquery is compared

all and all values returned by the subquery are compared

Operator (single line)

= Equal To

> Greater than

>=greater than or equal to

<less than

<= less than or equal to

<>not equal To

SELECT * from emp where job= (select Job from EMP where empno=7566) and Sal > (select sal from EMP where empno=7782);

SELECT * from emp where sal= (select min (sal) from EMP);

- to inquire the minimum wage for the department number and department minimum wage which is greater than the minimum wage for Department No.

Select Deptno,min (SAL) from the EMP Group by Deptno have min (sal) > (select min (sal) from EMP where deptno=20);

-- Query department name is employee information of SALES and ACCOUNTING

SELECT * from EMP where deptno in (select Deptno from dept where dname= ' SALES ' or dname= ' ACCOUNTING ');

Select e.* from emp e,dept D where E.deptno=d.deptno and (d.dname= ' SALES ' OR d.dname= ' ACCOUNTING ');

SELECT * from emp where Sal>any (select Sal from EMP where deptno=30);

-- equivalent to

SELECT * from emp where sal > (select min (sal) from EMP where deptno=30);

Subquery-to-control issues

-- Query the employee who is not the boss

SELECT * from EMP where empno not in (the Select Mgr from EMP where Mgr are NOT NULL);

=================================== Comprehensive example =============================

Instance One

Paged Query displays employee information: Show employee number, name, monthly salary

- show four records per page

- employees showing the second page

- in descending order of monthly salary

Select R,emp,ename,sal

From (select RowNum r,empno,ename,sal

From (select Rownum,empno,ename,sal to emp order BY Sal Desc) E1 where rownum <=8) E2

where r>=5;

--oracle Paging through subqueries

Example Two

Find employees who pay more than the department's average salary in the employee table

Select E.empno,e.name,e.sal,d.avgsal from emp E, (select Deptno,avg (SAL) avgsal from EMP Group by Deptno) d where e.deptno= D.deptno and e.sal>d.avgsal;

Example Three

The number of employees by department, output according to the following format (employee's entry year is known)

Total 1980 1981 1982 1987

14 1 10) 1 2

Select COUNT (*) Total,

SUM (Decode (To_char (hiredate, ' YYYY '), ' 1980 ', 1,0)) "1980",

SUM (Decode (To_char (hiredate, ' YYYY '), ' 1981 ', 1,0)) "1981",

SUM (Decode (To_char (hiredate, ' YYYY '), ' 1982 ', 1,0)) "1982"

from EMP;

-- using sub-query method

Select

(SELECT COUNT (*) from EMP) Total,

(SELECT COUNT (*) from EMP where To_char (hiredate, ' yyyy ') = ' 1980 ') "1980",

(SELECT COUNT (*) from EMP where To_char (hiredate, ' yyyy ') = ' 1981 ') "1981",

(SELECT COUNT (*) from EMP where To_char (hiredate, ' yyyy ') = ' 1982 ') "1982"

from dual;

Oracle advanced query (instance based on four Scott users table)