4. Oracle Complex Query

Oracle Complex Query

Data Group,

Grouping FunctionsMax, Min, AVG, sum, count

1. Query the name of the person with the highest salary

Select ename from EMP where sal = (select max (SAL) from EMP );

1. Salary increases by 10% for all employees lower than the average salary

Update EMP set sal = (select AVT (SAL) from EMP) * 10% where Sal <(select AVT (SAL) from EMP );

Grout by and having

Groupt by is used to group statistics on query results.

The having clause is used to limit the display of group results.

1. How to display the average and maximum salaries of each department

Select AVG (SAL), max (SAL ),DeptnoFrom EMPGroup Deptno;

1. Displays the average salary and minimum wage for each position in each department

Select AVG (SAL), min (SAL), deptno, job from EMP group by deptno, job;

1. Shows the average salary of less than 2000 of the department and number and its average salary

Select deptno, AVG (SAL) from EMP group by deptnoHavingAVG (SAL)> 2000;

 

Summary:

1. Grouping functions (max, Min, AVG, count) can only appear in the select list (After Select), having, and order by clauses.
2. If the SELECT statement contains both group by, having, and order by, their order must be group by, having, and order.
   (Group first → suppress Result Display → last group)
   
3. If there are columns, expressions, and grouping functions in the selection column, these columns and expressions must be included in the group by clause; otherwise, an error is reported. For example, deptno
   

 

Multi-Table query

1. Display employee name, employee salary, and name of employee's Department
   

Select a1.ename, a1.sal, a2.dname from EMP A1, DEPT A2 where a1.deptno = a2.deptno;

Cartesian Product, principle: the condition for multi-table queries is at least not less than the number of tables-1

1. How to display the Department name, employee name, and salary with the department Number 10
   

Select B. dname, A. ename, A. Sal from emp a, dept B where B. deptno = A. deptnoAnd a. deptno = 10;

-- Connect two tables first, and filter them out with the red condition.

1. Display the name, salary, and salary of each employee
   

Select a1.ename, a1.sal, a2.grade from EMP A1, salgrade A2 where a1.sal between a2.losal and a2.hisal;

1. (Extended) displays and sorts the employee name, employee salary, and department name by department
   

Select a1.ename, a1.sal, a2.dname from EMP A1, DEPT A2 where a1.deptno = a2.deptno order by a1.deptno;

-- Multi-Table sorting

 

Self-connection

Self-connection refers to the query of the same connection

1. Displays the name of an employee superior. For example, Ford
   

Select a. ename from emp a where a. Mgr = A. empno and A. ename = 'Ford ';

Select worker. ename, boss. ename from EMP worker, EMP boss where worker. Mgr = boss. empno and worker. ename = 'Ford ';

-- Tips: Think of a table as two different tables and create aliases.

 

Subquery

A subquery is a SELECT statement embedded in other SQL statements. Also called nested query.

Single Row subquery

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

For example, how to display all employees in the same department as Smith

Select a. ename from emp a where a. deptno = (select deptno from emp B where B. ename = 'Smith ');

Multi-line word Query

Multi-row subquery is a query statement that returns multi-row data.

For example, how to query the names, positions, salaries, and department numbers of employees with the same work as department 10?

Select * from emp a where a. Job In (select distinct job from EMP where EMP. deptno = 10 );

Use the all operation in multi-row subqueries

For example, how to display the name, salary, and department number of an employee whose salary is higher than that of all employees in the department 30?

Method 1:

Select ename, Sal, deptno from EMP where SAL> All (select Sal from EMP where deptno = 30 );

Method 2:

Select ename, Sal, deptno from EMP where SAL> (select max (SAL) from EMP where deptno = 30 );

Comparison:

Method 2 is much more efficient than method 1, because method 1 compares one by one, and method 2 directly compares the results.

 

Use the any operator in multi-row subqueries

For example, the name, salary, and department number of an employee whose salary is higher than that of any employee of Department 30 are displayed.

Method 1:

Select a. ename, A. Sal, A. deptno from emp a where a. SAL> Any (select Sal from EMP where deptno = 30 );

Method 2:

Select ename, Sal, deptno from EMP where SAL> (select Min (SAL) from EMP where deptno = 30 );

Multi-column subquery

A single-row subquery refers to a single-row or single-column data returned by a subquery;

Multi-row subquery refers to the multi-row and single-column data returned by the subquery. For a single column;

Multi-column subquery refers to the subquery statement that the subquery returns multiple columns.

For example, query all employees who have the same department and position as Smith.

Select * from EMP where (deptno, job) = (select deptno, job from EMP where ename = 'Smith ');

 

Use subquery in from clause

Example: Display staff information higher than the average salary of your own department

1) query the average salary and department number of each department

Select deptno, AVG (SAL) from EMP group by deptno;

2) query

Consider the preceding query as a sub-table (select deptno, AVG (SAL) from EMP group by deptno) A1

Select a2.ename, a2.sal, a2.deptno, a1.avgsal from EMP A2, (select deptno, AVG (SAL) avgsal from EMP group by deptno) A1 where a2.deptno = a1.deptno and a2.sal> alias;

 

Summary: When a subquery is used in the from clause, the subquery is treated as a view. Therefore, it is called an embedded view. When a subquery is used in the from clause, you must specify an alias for the subquery.

-- Alias can be used for columns, but alias cannot be used for tables, views, and subqueries.

 

Paging

The paging of Oracle is the most complex. Two subqueries are required, but the efficiency is also the highest, because the two-point search principle is used internally. The paging of MySQL is the simplest, and it is implemented by a single limit, followed by sqlserver.

There are three paging methods in Oracle.

1. Rownum first performs a subquery.
   

Select * from EMP

1. Show rownum [rownum is allocated by Oracle]
   

Select A1. *, rownum rn from (select * from EMP) A1;

The query results will contain an extra column, RN, which indicates rownum, number of row numbers, which is allocated by Oracle.

1. Display the row data to be selected
   

Select A1. *, rownum rn from (select * from EMP) A1 where rownum <= 10;

And rownum> = 6; (rownum cannot be used twice; otherwise, no data can be found)

At this point, 1-10 rows of data can be displayed, and more than half of data has been cut off. The goal is to display 6-10 rows of data.

1. Perform a subquery again
   

Select * Form (select A1. *, rownum rn from (select * from EMP) A1 where rownum <= 10) A2 where rn> = 6;

The result is the expected 6-10 rows of data.

 

Note: several query changes:

1. If you want to specify a query column, instead of querying all columns, you only need to modify the subquery at the innermost layer;
   
2. You only need to modify the subquery at the bottom layer.
   
3. Similarly, grouping, sorting, and modifying all subqueries at the bottom of the hierarchy.
   

 

 

 

 

 

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;

After the command is executed, mytable not only has the same table structure as EMP, but even all the data is poured into the EMP table.

 

 

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 operations on two sets, removing duplicate rows
   

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 ';

1. Union all operations without removing duplicate rows
   

This operator is similar to union, but does not cancel duplicate rows and does not sort

1. Intersect merge Query
   

This operator obtains the intersection of two sets.

1. Minus difference set operation
   

This operator obtains the difference set of two sets.

 

How to Create a database

1. Wizard tools provided by Oracle
   

Database configration Assistant (dbca) Oracle Database Configuration Assistant

1. Manually create code directly
   

 

 

Java operation oralce, JSP and paging

. Net Oracle operations, paging

 

Insert a date value in a specific format

Use the to_date () function to insert a date in the format of year-month-day

To_date ('1970-05-11 ', 'yyyy-mm-dd ')

Date values can be formatted

 

Insert data using a subquery

When the values clause is used, only one row of data can be inserted at a time. When data is inserted using a subquery, a single insert statement can insert a large amount of data. When processing row migration, data migration, or loading external table data to the database, you can use subqueries to insert data.

Insert into mytable (ID, name, deptno) Select (myid, ename, ENO) from EMP where Eno = 10;

 

Use a clause to update data

When using the update clause to update data, you can use an expression or a value to directly modify the data, or use a subquery to modify the data.

For example, if you want to update employee Scott's position, salary, and subsidy, the same as employee simth

Update EMP set (job, Sal, comm) = (select job, Sal, comm from EMP where Enma = 'Smith ') Where ename = 'Scott ';