Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Subquery Oracle study notes     - Spring + Log4j + ActiveMQ remote logging - Analysis of combat (Server)

- Reset CentOS / RHEL root account password 7 (Linux)

- Four levels of intrusion on Linux server and counter-measures (Linux)

- Spark On YARN cluster installation deployment (Server)

- Achieve single backup of MySQL by innobackupex (Database)

- DataGuard a hardware issue warnings found (Database)

- Linux daemon (Linux)

- Android imageView in the Src and Background (Programming)

- Detailed Linux network security policies and protection measures (Linux)

- Use of the storage-level replication technology will quickly clone a ASM database to the target environment (Database)

- Oracle Database Restore (Database)

- Depth study and understanding for individual users suicide DDoS attacks (Linux)

- On event processing browser compatibility notes (Programming)

- Oracle 11g tracking and monitoring system-level triggers to drop misuse (Database)

- To record command input under Linux (Linux)

- Hive handle count distinct inclination to produce data processing (Database)

- Installation under Linux Mint system guidelines for Gtk (Linux)

- ASM learning overview (Database)

- Ubuntu download install and configure Plank Dock (Linux)

- ARP Linux-related operations (Linux)

 
         
  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:      
 
- How to write a new Git protocol (Linux)
- About Python default character set (Linux)
- Difference Docker mirror and containers (Server)
- Python substring format (Programming)
- SSL VPN SSL VPN access to security websites patron (Linux)
- configuration ssh without password under Linux (Linux)
- Python programming style (Programming)
- Oracle study notes view (Database)
- Linux kernel update error, update-initramfs: failed Solution (Linux)
- Linux environment SSH login password instead of using the RSA Certificate (Linux)
- Linux System Getting Started tutorial: Ubuntu desktop using the command line to change the system proxy settings (Linux)
- Asynchronous communication mechanism between the Android source code analysis thread (Programming)
- Ubuntu 14.04 users how to install VLC 2.2.0 (Linux)
- CentOS install Memcached (Server)
- Linux rpm command Detailed (Linux)
- CentOS7 installation GAMIT and GMT (Linux)
- Use Markdown editor for document work under Linux (Linux)
- SQL MySQL query table duplicate data (Database)
- CentOS 6.x and CentOS7 install MPlayer (Linux)
- LinSSID: a graphical Wi-Fi scanner under Linux (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.