oracle-Table Query

These are all relatively basic, or a refresher.

A simple query statement
1. Query table structure
SQL>DESC Dept; ( a command window is required to use this statement when using a PL/SQL developer Query )

2. Query all Columns
Sql>select * FROM Dept;

3. Query the specified column
Sql>select Ename,sal,job,deptno from EMP;

4. How to cancel duplicate rows
Sql>select distinct deptno,job from EMP;

5. Using an arithmetic expression
Example: Show annual salary for each employee
Sql>select ename, Sal * 12 "annual salary" from EMP;

6. Use the alias of the column
Sql>select ename "Name", sal*12 as "annual income" from EMP;

7. How to handle null values
Description: for null values, we can use the NVL function to handle it.
Example: Show annual salary (including bonus) for each employee
Sql>select ename, SAL*12+NVL (comm,0) *12 "annual income" from EMP;

8. How to connect strings
Sql>select ename | | ' is a ' | | Job from EMP;

9. Using the WHERE clause
Example: How to show an employee with a salary above 3000
Sql>select ename,sal from EMP where sal>3000;
Example: How to find employees who have been in employment since 1982.1.1
Sql>select ename,hiredate from emp where hiredate> ' January-January-1982 ';
Example: How to show employees with a salary of 2000 to 2500
Sql>select ename,sal from EMP where Sal between and 2500;

10. Using the LIKE operator
(1)%: represents any 0 to more characters.
(2) _: Represents any single character.
Example: How to display the first employee name and salary for s
Sql>select ename,sal from emp where ename like ' s% ';
Example: How to display the names and wages of all employees whose third character is a capital O
Sql>select ename,sal from emp where ename like ' __o% ';

11. Use in in the Where condition
Example: How to display an employee with a empno of 123,345,800
Sql>select * from EMP where empno in (123,345,800);

12. Operator using is null
Example: How to show an employee without a superior
Sql>select * from EMP where Mgr is null;

13. Use logical operation symbols
Example: Query for employees with a salary higher than 500 or job manager, and also to meet their names first letter in uppercase J
Sql>select * from EMP where (sal>500 or job= ' MANAGER ') and ename like ' j% ';

14. Using the ORDER BY clause
Example: How to display employee information in the order of low to high wages
Sql>select * from emp order by Sal;
Example: Ascending by Part number and employee's salary descending order
Sql>select * from emp ORDER BY deptno,sal Desc;

15. Sorting using the alias of the column
Sql>select ename,sal*12 "annual salary" from the EMP Order by "annual salary" DESC;
Note: Aliases need to be surrounded with double quotes.

Second, complex query statements
Description: In practical applications often need to perform complex data statistics, often need to display more than one table of data, now we give you a more complex SELECT statement.
1. Data Grouping--max,min,avg,sum,count
Example: How to display the highest wage and minimum wage for all employees
Sql>select Max (sal), Min (sal) from EMP;
Example: Show the total wage and payroll for all employees
Sql>select avg (SAL), sum (SAL) from EMP;
Example: Calculate the total number of employees
Sql>select Count (*) from EMP;
Example: Please show the name of the highest-paid employee, the job position
Sql>select ename,job from EMP where sal= (select Max (SAL) from EMP);
Example: Show employees with wages above average
Sql>select * from EMP where sal> (select AVG (SAL) from EMP);

2. GROUP BY and HAVING clause
Group BY is used to group statistics on the results of a query.
Having is used to restrict the display of grouped results.
Example: How to show average and maximum wages for each department
Sql>select avg (SAL), Max (SAL), Deptno from EMP Group by DEPTNO;
Example: Shows the average wage and minimum wage for each position in each department
Sql>select avg (Sal), Min (sal), deptno,job from EMP Group by Deptno,job;
Example: Show department number with average salary below 2000 and his average salary
sql>Select AVG (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) >2000;

A summary of the data grouping
1. The grouping function can only appear in the select list, having, ORDER BY clause.
2. If both group BY, have, order by are included in the SELECT statement, then their order is group By,having,order by.
3. If there are columns, expressions, and grouping functions in the selection column, then the columns and expressions must have one in the GROUP BY clause, or an error will occur.
such as: Select Deptno,avg (SAL), Max (SAL) from the EMP GROUP by DEPTNO have avg (SAL) >2000 ORDER by AVG (SAL);
Here Deptno must appear in GROUP by.

Three, multi-table query
Description: A multi-table query is a query that is based on two or more tables or views, and in practice, querying a single table may not meet the requirements (such as displaying the sales department location and the names of its employees), which in this case needs to be used (dept and EMP Tables).
1. Show employee's name, employee's salary and name of department
Sql>select a1.ename,a1.sal,a2.dname from EMP A1 inner joins dept A2 on A1.deptno=a2.deptno;

2. How to display department name, employee and salary of department number 10th
Sql>select a2.dname,a1.ename,a1.sal from EMP a1,dept A2 where A1.deptno=a2.deptno and a2.deptno=10;

3. Display the name, salary and salary level of each employee
Sql>select A1.ename,a1.sal,a2.grade from EMP a1,salgrade A2 where a1.sal between A2.losal and A2.hisal;

4. Display the name of the employee, employee's salary and the department in which they are located, sorted by department
Sql>select a1.ename,a1.sal,a2.dname from EMP A1 INNER JOIN Dept A2 on A1.deptno=a2.deptno Order by A1.deptno;

5. Self-connected
Description: A self-join refers to a connection query in the same table.
Case: Show the name of Ford's superior leader
Sql>select worker.ename "Employee", Boss.ename "superior leader" from EMP Worker,emp boss where Worker.mgr=boss.empno and Worker.ename= ' FORD ';

Four, sub-query
Overview: A subquery is a SELECT statement embedded in another SQL statement, also called a nested query.
1, single-row sub-query
Description: A single-line subquery is a subquery statement that returns only one row of data.
Case: How to display all employees in the same department as Smith
Sql>select * from EMP where deptno= (select Deptno from emp where ename= ' SMITH ');

2, multi-row sub-query
Description: A multiline subquery refers to a subquery that returns multiple rows of data.
Case: How to inquire about the same employee's name, position, salary, department number and department 10 work
Sql>select Ename,job,sal,deptno from EMP where job in (select distinct job from EMP where deptno=10);

3. Use the all operator in a multiline subquery
Case: How to display the employee's name, salary, and department number for a salary higher than all employees in the Department 30
Sql>select Ename,sal,deptno from emp where sal > All (select Sal from EMP where deptno=30);

4. Using the any operator in a multiline subquery
Case: How to display the employee's name, salary, and department number for a salary higher than any employee of the Department 30
Sql>
Sql>select Ename,sal,deptno from emp where sal > No (select Sal from EMP where deptno=30);

5, Dolez Query
Description: A single-line subquery refers to a subquery that returns only single-column, single-row data. A multiline subquery is a single row of multiple rows of data, all for a single column, while a multicolumn subquery refers to a subquery that returns multiple column data.
Case: How to query all employees who are exactly the same as Smith's department and position
Sql>select * from EMP where (deptno,job) = (select Deptno,job from emp where ename= ' SMITH ');

6. Using subqueries in the FROM clause
Case: How to display information about employees who are above their department's average salary
Sql>select a1.ename,a1.sal,a1.deptno,a2.avgsal from EMP A1, (Select Deptno,avg (SAL) from EMP Group by DEPTNO) A2 where a 1.deptno=a2.deptno and sal>avgsal;
Summary: It is important to note that when a subquery is used in the FROM clause, the subquery is treated as a view and is therefore called an inline view, and when a subquery is used in the FROM clause, the subquery must be given an alias.

7. Create a new table with query results
Description: This command is a quick way to build a table.
Case: Create TABLE Myemp (id,ename,sal) as (select Empno,ename,sal from EMP);

8. Inserting data using subqueries
Description: When using the values clause, only one row of data can be inserted at a time, and an INSERT statement inserts a large amount of data when inserting data using a subquery. You can use subqueries to insert data when you are working with row migrations or when loading data from an external table to a database.
Case: Sql>insert into the Users select * from emp where deptno=10;

9. Updating data using subqueries
Description: When updating data using the UPDATE statement, you can either modify the data directly using an expression or a numeric value, or you can modify the data using a subquery.
Case: Want to update employee Scott's position, salary, and benefits as Smith's employees
Sql>update EMP Set (JOB,SAL,COMM) = (select Job,sal,comm from emp where ename= ' SMITH ') where ename= ' SCOTT ';

Five, paging query
1, according to ROWID, the highest efficiency
Sql>select * from EMP where rowid in (select Rid from (select RowNum rn,rid from (select rowID rid,empno from emp Order by empno Desc) where rownum<10) where rn>6) Order by empno Desc;

2, according to the analysis function to divide, the efficiency is the worst
Sql>select * FROM (select T.*,row_number () up (order by empno Desc) rk from emp t) where RK between 6 and 10;

3, according to RowNum to divide, efficiency second
Sql>select * FROM (select A1.*,rownum rn from (SELECT * from EMP) A1 where rownum<=10) where rn>=6;

Vi. Merging queries
Overview: Sometimes in real-world applications, in order to merge the results of multiple SELECT statements, you can use the set operation symbol Union,union All,intersect,minus.
1. Union
Description: This operator is used to obtain a set of two result sets. When the operator is used, duplicate rows in the result set are automatically removed.
Case: Sql>select ename,sal,job from EMP where sal>2500 Union select ename,sal,job from emp where ename= ' MANAGER ';

2. UNION ALL
Description: This operator is used to obtain a set of two result sets. When this operator is used, duplicate rows in the result set are not automatically removed.
Case: Sql>select ename,sal,job from EMP where sal>2500 union ALL select Ename,sal,job from emp where ename= ' MANAGER ';

3, Intersect
Description: Use this operator to get the intersection of two result sets.
Case: Sql>select ename,sal,job from EMP where sal>2500 intersect ename,sal,job,from emp where job= ' MANAGER ';

4, minus
Description: Use this operator to get the difference set of two result sets, which only shows the existence of the first collection, not the data in the second collection.
Case: Sql>select ename,sal,job from emp where sal>2500 minusename,sal,job,from emp where job= ' MANAGER ';

Reprinted from: http://www.cnblogs.com/BeautyOfCode/archive/2010/09/15/1826576.html

oracle-Table Query