Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Subquery Oracle study notes     - UNIX and class UNIX system security check notes (Linux)

- Revive Adserver ad server installation on Ubuntu 15.04 / CentOS7 (Server)

- C ++ CBitmap, HBitmap, Bitmap difference and contact (Programming)

- Ubuntu 14.04 Boot Repair (Linux)

- To install and deploy Apache under the CentOS (Server)

- Ansible module Know (Linux)

- Raspberry Pi 2 to install the latest version of the FPC and Lazarus 1.5 (Linux)

- Linux firewall iptables beginner tutorial (Linux)

- Ant command-line compiler Android project (Programming)

- Towards Docker (Server)

- Linux formatted partition error Could not stat / dev / sda No such file or directory Solution (Linux)

- Redis is installed and set up Ubuntu 14.04 from the environment under the main ssdb (Server)

- LaTeX Getting Started Tutorial (Linux)

- CentOS7 installation configuration (Server)

- Flow control message transmission between RAC (Database)

- Spring JDBC Comments (Programming)

- MySQL EXPLAIN SQL output description (Database)

- Linux boot the system does not display a progress bar setting method (Linux)

- Shell command line operation (Linux)

- Implement binary search algorithm in C language (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:      
 
- You may not know the Linux command-line network monitoring tool (Linux)
- Fatal NI connect error 12170 error in Alert Log (Database)
- CentOS 7.0 Automatic installation CD-ROM production Comments (Linux)
- Ubuntu How to install screen recording tool Simple Screen Recorder 0.3.1 (Linux)
- Access.log Apache access log analysis and how to import it into MySQL (Server)
- Ubuntu modify locale problem solving (Linux)
- MongoDB query statistics grouping remove duplicate records (Database)
- Oracle users to automatically increase the partition table (Database)
- Win8 mount disk partitions under Ubuntu (Linux)
- Postfix mail service system principle and configuration (Linux)
- Linux loopback adapter Driven Design (Programming)
- Using the Android interface in Parcelable (Programming)
- Linux Tutorial Share: How to sudo command to define the PATH environment variable (Linux)
- CentOS 7 update source - the use of domestic sources 163 yum (Linux)
- Ora-00600 [fast hot Atkins soft _ that _ Oh, of course not _less_ profile] (Database)
- To remove those IP is prohibited Fail2ban on CentOS 6/7 (Server)
- Linux argument references and command substitution (Linux)
- Linux system boot process ARM platforms (Linux)
- MySQL loose index scan (Database)
- RHEL5.8 physical opportunities to Read-only file system (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.