Explain the oracle table complex query instances and explain the oracle instances

Explain the oracle table complex query instances and explain the oracle instances

Group by and having clauses

Group by is used to group statistics on query results,

The having clause is used to restrict group display results.

Summary of data groups

1 grouping functions can only appear in the selection list, having, order by clause

2 If select statements contain group by, having, and order by statements, their order is group by, having, and order.

3. If columns, expressions, and grouping functions are selected, one of these columns and expressions must appear in the group by clause. Otherwise, an error occurs.

For example, select deptno, avg (sal), max (sal) from emp group by deptno having avg (sal) <2000;

Here, deptno must appear in group.

Self-connection

Self-join refers to the connection query in the same table.

What is subquery?

A subquery is a select statement embedded in other SQL statements. It is also called a nested query.

■ Single row subquery

A single row subquery is a subquery statement that returns only one row of data.

Think about how to display all employees in the same department as SMITH?

■ Multi-row subquery

Multi-row subquery refers to a subquery that returns multi-row data.

Use the all operator in multi-row subqueries

Think about how to display the name, salary, and department number of an employee whose salary is higher than that of all employees in department 30.

Select ename, sal, deptno from emp where sal> all (select sal from emp where deptno = 30 );

Expansion requirements: There are no other query methods.

Select ename, sal, deptno from emp where sal> (select max (sal) from emp where deptno = 30 );

Use the all operator in multi-row subqueries

Think about how to display the name, salary, and department number of an employee whose salary is higher than that of all employees in department 30.

Select ename, sal, deptno from emp where sal> all (select sal from emp where deptno = 30 );

Expansion requirements: There are no other query methods.

Select ename, sal, deptno from emp where sal> (select max (sal) from emp where deptno = 30 );

Use the any operator in multi-row subqueries

Think about how to display the name, salary, and department number of an employee whose salary is higher than that of any employee of Department 30.

Expansion requirements:

There are no other query methods.

Multi-column subquery

A single row subquery refers to a subquery that only returns data in a single column and a single row. A multi-row subquery refers to a single column and multiple rows of data, multi-column subquery refers to the subquery statement that returns data of multiple columns.

Think about how to query all employees who have the same department and position as smith

Use subquery in the from clause

Think about how to display the information of employees who are higher than the average salary of their own departments

Here we need to use the data query tips to use a subquery as a temporary table.

Solution ①

Select e1. *, e2.myavg from emp e1, (select avg (sal) myavg, deptno from emp group by deptno) e2 where e1.deptno = e2.deptno and e1.sal> e2.myavg

Solution ②

Select e1. * from emp e1 where e1.sal> (select avg (sal) from emp where deptno = e1.deptno)

Use subquery in the from clause

Think about it: Find the details of the person with the highest salary in each department

Solution ①

Solution ②

Train of Thought: Get all employees and screen them. Each time you get an employee, you can determine whether the employee's salary is the highest salary in their department.

Select * from emp e where sal = (select max (sal) from emp where deptno = e. deptno );

Use subquery in the from clause

Think about it: display the information and number of people for each department

Solution ①

Complete by yourself...

Solution ②

Query to obtain information about all departments. Every time a department is obtained, we query the number of people in the department and save it as a new column.

Select d. *, (select count (*) from emp where deptno = d. deptno) allnum from dept d;

Use subquery in the from clause

Note that when subqueries are used in the from clause

The subquery is treated as a temporary table,

When subqueries are used in the from clause, subqueries must be provided.

Alias.

Create a new table with query results

This command is a quick table creation method.

Create table mytable (id, name, sal, job, deptno)

As select empno, ename, sal, job, deptno from emp;

■ Self-replication data (worm replication)

Sometimes, to test the efficiency of an SQL statement, you can use this method to create massive data for a table when you need massive data.

Insert into mytable (id, name, sal, job, deptno)

Select empno, ename, sal, job, deptno from emp;

Merge Query

In practice, to merge the results of multiple select statements, you can use the union, union all, intersect, and minus operators.

1) union

This operator is used to obtain the union of two result sets. When this operator is used, repeated rows in the result set are automatically removed.

Select ename, sal, job from emp where sal> 2500 union

Select ename, sal, job from emp where job = 'manager ';

2) union all

This operation is similar to union, but it does not cancel duplicate rows and does not sort.

Select ename, sal, job from E where sal> 2500

Union all select ename, sal, job from emp where

Job = 'manager ';

3) intersect

Use this operator to obtain the intersection of two result sets.

Select ename, sal, job from E where sal> 2500

Intersect select ename, sal, job from emp where

Job = 'manager ';

4) minus

Use this operator to obtain the difference set of two result sets. It only shows that the first set exists, but not the data in the second set.

Select ename, sal, job from emp where sal> 2500 minus

Select ename, sal, job from emp where job = 'manager ';