Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Subquery Oracle study notes     - Linux system firewall defense network attacks (Linux)

- DELL D630 Wireless LAN Driver Installation CentOS6 (Linux)

- Elaborate .NET Multithreading: Concepts (Programming)

- PPA on Ubuntu Linux installation Plank 0.8.0 (Linux)

- The Linux OOM Terminator (Server)

- Oracle create a temporary table space group (Database)

- Empty password Linux operating system (Linux)

- Get basic information about Linux server script (Server)

- Ubuntu Series Installation Docker (Linux)

- CentOS 6.4 Telecom ADSL dial-up network configuration (Linux)

- Hadoop 2.5 Pseudo distribution installation (Server)

- Linux productivity tools and tips (Linux)

- MariaDB database storage path modify configuration issues (Database)

- Linux System Getting Started Learning: Join cron job in Linux (Linux)

- To share Linux script automatically change passwords (Linux)

- Ubuntu install Wireshark (Linux)

- Vim useful plugin: vundle (Linux)

- How Oracle implements random reads from specific combinations (Database)

- Availability Hadoop platform - Oozie Workflow (Server)

- Why I do not like the Go language style interface (ie Structural Typing) (Programming)

 
         
  Subquery Oracle study notes
     
  Add Date : 2018-11-21      
         
         
         
  Subqueries

When we need an operation based on another query demerit, then the query is first executed subquery

Divided into sub-queries:

Single separate sub-query: The result of the investigation only one row and only one field

Multi-row single row subqueries: check out the results of multiple lines, but only over one row multiple-column subquery

Check out the multiple rows multiple columns.

Typically, single-line and multi-line multiple-column single query is used in the where clause, and multi-line inquiry for multiple-column

FROM clause.

- View and other employees of the same office SCOTT

SELECT ename, sal, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT')

AND ename < > 'SCOTT';

- See which wages than average

SELECT ename, sal FROM emp WHERE sal> (SELECT AVG (sal) FROM emp);

- Title: View company employees and other positions SALESMAN same sector information

- A first step:

SELECT ename, deptno FROM emp WHERE job = 'SALESMAN';

- Second step:

SELECT ename, job, deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE

job = 'SALESMAN') AND job < > 'SALESMAN';

- Check all other employees more than 20 department employees wages are high

SELECT ename, sal, deptno FROM emp WHERE deptno = 20;

SELECT ename, sal, deptno FROM emp WHERE sal> ALL (SELECT sal FROM emp WHERE deptno = 20);

EXISTS role, when the sub-query can return at least one record, then the expression returns true, the following example shows: See more employees

Department

SELECT deptno, dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno);

- Check the minimum wage higher than the 30th sector minimum wage sector

SELECT deptno, MIN (sal) "minimum salary" FROM emp GROUP BY deptno HAVING MIN (sal)> (SELECT MIN (sal) FROM emp WHERE deptno = 30);

- Check the minimum wage higher than the 30th sector minimum wage sector

SELECT deptno, MIN (sal) "minimum salary" FROM emp GROUP BY deptno HAVING MIN (sal)> (SELECT MIN (sal) FROM emp WHERE deptno = 30);

- Removing duplicates

SELECT DISTINCT deptno FROM emp;

- Query higher than the average salary of the employees of the department of information

- The idea here is that we should first count the average salary for each department as a result of this query is a multi-line multi-column, so we will

- To look at it as a table, and then use the EMP table associated query. Therefore, multi-line multi-column subquery generally used in the FROM clause after.

--FROM In writing subqueries, commonly referred to as internal view

SELECT e.ename, e.sal, e.deptno FROM emp e, (SELECT AVG (sal) avg_sal, deptno FROM emp

GROUP BY deptno) x WHERE e.deptno = x.deptno AND e.sal> x.avg_sal;

- View and other employees of the same office SCOTT

SELECT ename, sal, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT')

AND ename < > 'SCOTT';

- See which wages than average

SELECT ename "Name", sal "wages" FROM emp WHERE sal> (SELECT AVG (sal) FROM emp);

- Title: View company employees and other positions SALESMAN same sector information

- A first step:

SELECT ename, deptno FROM emp WHERE job = 'SALESMAN';

- Second step:

SELECT ename, job, deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE

job = 'SALESMAN') AND job < > 'SALESMAN';

- Check all other employees more than 20 department employees wages are high

SELECT ename, sal, deptno FROM emp WHERE deptno = 20;

SELECT ename, sal, deptno FROM emp WHERE sal> ALL (SELECT sal FROM emp WHERE deptno = 20);

--EXISTS Role, when the sub-query can return at least one record, then the expression returns true, the following example shows: See more employees

- Sector

SELECT deptno, dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno);

- Check the minimum wage higher than the 30th sector minimum wage sector

SELECT deptno, MIN (sal) "minimum salary" FROM emp GROUP BY deptno HAVING MIN (sal)> (SELECT MIN (sal) FROM emp WHERE deptno = 30);

- Removing duplicates

SELECT DISTINCT deptno FROM emp;

- Query higher than the average salary of the employees of the department of information

- The idea here is that we should first count the average salary for each department as a result of this query is a multi-line multi-column, so we will

- To look at it as a table, and then use the EMP table associated query. Therefore, multi-line multi-column subquery generally used in the FROM clause after.

--FROM In writing subqueries, commonly referred to as internal view

SELECT e.ename, e.sal, e.deptno FROM emp e, (SELECT AVG (sal) avg_sal, deptno FROM emp

GROUP BY deptno) x WHERE e.deptno = x.deptno AND e.sal> x.avg_sal;

- Sub-query can also appear in the SELECT clause, the effect is usually achieved outside connection effect, if the associated value during the emp table deptno field

- When there is no query query dept table data, then the value is displayed as null

SELECT e.ename, e.sal, (SELECT d.deptno FROM dept d WHERE d.deptno = e.deptno) deptno FROM emp e;

- Paging, will get all the records in batches, purpose: to speed up queries, reduce system resource consumption

- Paging least for the record number, and sorting

- No: in ORALCE can be used ROWNUM pseudo column ROWNUM itself not in the table, using him as a

- The value is derived from the table the number of inquiries from data, Oracle automatically generates values for the column

SELECT * FROM emp;

SELECT * FROM (SELECT ROWNUM rn, e.ename "name", e.job "work", e.sal "wages" FROM emp e ORDER BY "wages" DESC) WHERE

rn BETWEEN 5 AND 10;

SELECT ename, job, sal, DECODE (job,

                  'MANAGER', sal * 1.2,

                  'ANALYST', sal * 1.1,

                  'SALESMAN', sal * 1.05, sal

                  ) Bouns

                  FROM emp;

                 

- The MANAGER and ANALYST these two positions seen as a group of other positions seen as another set, the two sets of statistics of the total number,

- Ideas: the need to be seen as a group, but those values and different data, we use the DECODE them to the same value.

SELECT DECODE (job, 'MANAGER', 'VIP', 'ANALYST', 'VIP', 'OPERATIONS') NAME, COUNT (*) FROM emp

GROUP BY DECODE (job, 'MANAGER', 'VIP', 'ANALYST', 'VIP', 'OPERATIONS');

SELECT deptno, dname FROM dept ORDER BY DECODE (dname, 'OPERATIONS', 1, 'ACCPOUNTING', 2, 'SALES', 3);

- Grouped by sector, according to the descending wages, generated within the set of consecutive unique numbers:

SELECT ename, deptno, sal, ROW_NUMBER () OVER

(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp; - the difference between rank and function ROW_NUMBER that sort if the value of the same field

- And they are in the same group, then they get the numbers are the same, but in the following figure will jump discontinuity not only within RANK digital generated group.

--DENSE_RANK () Will generate a continuous sole

SELECT ename, deptno, sal, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

- Collection operations: union, the two sets all the elements grouped into a collection of ordinary union with the whole union.

- Full and Collections: generate repeat elements have two sets of elements, there will be twice after the merger in the new collection.

- Intersection: the new collection has retained only two sets of elements

- Set difference: the new collection I have just saved you the element does not have.

- Difference Sets

SELECT ename, job, sal FROM emp WHERE job = 'MANAGER' MINUS SELECT ename, job, sal FROM emp WHERE sal> 2500;

- General and set

SELECT ename, job, sal FROM emp WHERE job = 'MANAGER' UNION SELECT ename, job, sal FROM emp WHERE sal> 2500;

- Full and set

SELECT ename, job, sal FROM emp WHERE job = 'MANAGER' UNION ALL SELECT ename, job, sal FROM emp WHERE sal> 2500;

- Intersection

SELECT ename, job, sal FROM emp WHERE job = 'MANAGER' INTERSECT SELECT ename, job, sal FROM emp WHERE sal> 2500;

SELECT * FROM sales_tab;

SELECT year_id, month_id, day_id, SUM (sales_value) FROM SALES_TAB GROUP BY

GROUPING SETS ((year_id, month_id, day_id), (year_id, month_id))

ORDER BY year_id, month_id, day_id;
     
         
         
         
  More:      
 
- The execution order of Oracle WHERE condition is not from right to left (Database)
- JavaScript: understanding regular expressions (Programming)
- shellinabox: one uses AJAX Web-based terminal emulator (Linux)
- Java study notes: String (Programming)
- Intruder tools Knark Analysis and Prevention Linux environment (Linux)
- Bash difference in single quotes and double quotes (Programming)
- To add the Oracle JDBC driver in Maven repository (Linux)
- Install Java JDK 8 in CentOS 7 / 6.5 / 6.4 (Linux)
- The correct way to open Xcode - Debugging (Programming)
- Kafka cluster deployment (Server)
- Python variable type (Programming)
- You really do need to know a variety of programming languages (Programming)
- Volatile keyword in C language understanding (Programming)
- MongoDB version 3.2 WiredTiger storage engine performance tests (Database)
- Distributed transaction management Spring declarative transactions (Programming)
- To install MySQL 5.6 binary packages under CentOS 6.4 64bit (Database)
- How to view the Linux QPS (Linux)
- Thinking in Java study notes - Generics (Programming)
- Let 32 Linux / CentOS system to support more than 4G memory (Linux)
- C language - Traverse pci device (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.