Oracle 11g Study Notes 10_27

Oracle Complex Query * Use subqueries in the from clause

. Display information of employees higher than the average salary of their own departments.

// 1. query the average salary and department number of each department, select deptno, AVG (SAL) mysal from EMP group by deptno; // 2. Regard the preceding query as a sub-table A1, and then use the EMP table to perform multi-table queries such as select a2.ename, a2.sal, a2.deptno, a1.mysal from EMP A2, (select deptno, AVG (SAL) mysal from EMP group by deptno) A1 where a2.deptno = a1.deptno and a2.sal> a1.avg (SAL );

Note: You can use as to get the column alias in Oracle, but you cannot get the alias for the table.
* Paging Query

There are three methods for Oracle paging.
One of them is rownum paging.
. The number in the query page is 4 ~ 9. The employee information is displayed in descending order.
Steps:

// 1. query all information from the EMP table, and display the select ename, Sal from EMP order by Sal DESC in descending order; // 2. Select A1. *, rownum rn from (select ename, Sal from EMP order by Sal DESC) A1 where rownum <= 9; // 3. Select * from (select A1. *, rownum rn from (select ename, Sal from EMP order by Sal DESC) a1 where rownum <= 9) Where rn> = 4; // I don't know why rn in the last 'where rn> = 4' cannot be changed to rownum.

* Create a new table using the query results

Use this command to quickly create a table

create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

The table can be created, and the data in the original table already exists in the new table, which can be used to export the table and do not want
To modify or delete a table, you can also test it first.
* Merge Query
Rarely used
2. Create a database

Two methods:
1) Use the wizard tool provided by Oracle

Wizard tool: Oracle Database Configuration Assistant
It can be used to create, delete, and configure and manage databases.
2) manual Creation