SQL Complex Query

One, sub-query1The correlated subquery is a subquery that needs to refer to the main query list, and the related subquery is implemented by the EXISTS predicate. Below to show the work in"New York"Examples of how the related subqueries are used, as shown in the following example: SQL>SelectEname,job,sal,deptno fromEmpwhereexists (Select 1  fromDeptwhereDept.deptno=emp.deptno and dept.loc='New York'); As shown above, when using the EXISTS predicate, the condition is true if the subquery has a return result, or false if the subquery does not return a result. 2when a subquery is used in the FROM clause, the subquery is treated as a view and is therefore also referred to as an inline view. Note that when you use a subquery in the FROM clause, you must specify an alias for the subquery. The following is an example of a method that uses a subquery in the FROM clause to show an employee who is above the average wage in the department. Examples are as follows: SQL>SelectEname,job,sal fromEMP, (SelectDeptno,avg (SAL) avgsal fromEMP Group by DEPTNO) DeptwhereEmp.deptno=dept.deptno and Emp.sal>dept.avgsal; second, complex query1. Hierarchical query with parent-child relationship start with: Used to specify the root row of a hierarchical query.      Connect by: Used to specify the relationship between the parent row and the child rows (connect by condition), the parent row must be referenced in condition using prior. SQL>SelectLpad (' ',3* (level-1))||ename ename, Lpad (' ',3* (level-1)|| Job Job fromEMPwhereJob<>'Clerk'Start with Mgr is NULLConnect by Mgr=prior empno;2. Using CASE expression SQL>SelectEname,sal,case when sal> theThen3When Sal> -Then2ELSE1END Grade fromEmpwheredeptno=Ten; 3. Flashback Query view historical data (flashback query can only query data that changed before 5 minutes, but not the data changed within 5 minutes) SQL>SelectEname,sal fromemp As of timestamp To_timestamp ('2003-05-18 19:59:00','YYYY-MM-DD HH24:MI:SS')  whereEname='Clerk'; 4. With Create temp table SQL>With Tempname as (SELECT ...) three, character functions1. Initcap (Char): This function is used to capitalize the first character of each word in a string, the other characters are lowercase, and the words are separated by spaces and non-alphabetic characters. Examples are as follows: SQL> Initcap ('my World'output: My World Four, the number function five, the DATE function six, the conversion function seven, uses the record (single row multi-column data processing uses the record way to be good) defines the record two ways declare type Testrecode ID record ( Rname Number (6,2), Rpassword Emp.emppassword%type) Examplerecord Testrecode; Begin .... examplerecord.rname:='HHH'; End DECLARE Testrecord EMP%RowType; Begin ... Emp.name:='JJJ'; End eight, single row multi-row (index, nesting, Varray) nine, multi-row multi-column (record table) The SQL query that turns row data into column data specifies the GROUP BY clause that is used to place the output rows, and calculates the rollup values for each group if the SELECT clause contains aggregate functions. When you specify group BY, all columns in any non-aggregate expression in the selection list should be included in the group by list, or the group by expression must exactly match the select list expression. That is, the columns selected by select are either in an aggregate function or grouped in group by. If you want to display the data in the following way, the actual storage method in your database is as follows. That is, the data stored in the form of rows, and now to become the above column, storing SQL can be as follows:SelectS.classprocode,s.yearmonth,max ( CaseS.surveycode when'SC001'Then S.scoreElse 0End as 'Application Report', Max ( CaseS.surveycode when'SC002'Then S.scoreElse 0End as 'Logistic Reception', Max ( CaseS.surveycode when'SC003'Then S.scoreElse 0End as 'Deputy head teacher' fromSersurvey Sgroup by S.classprocode,s.yearmonth--Note: The data is converted according to the Surveycode condition, and if the condition is true, the score row is converted into columns, otherwise 0 orSelectA.*,(SelectTop1Score fromSersurveywhereClassprocode = A.classprocode and Yearmonth = a.yearmonth and Surveycode ='SC001') as 'Application Report',(SelectTop1Score fromSersurveywhereClassprocode = A.classprocode and Yearmonth = a.yearmonth and Surveycode ='SC002') as 'Logistic Reception',(SelectTop1Score fromSersurveywhereClassprocode = A.classprocode and Yearmonth = a.yearmonth and Surveycode ='SC003') as 'Deputy head teacher' from (SelectS.classprocode,s.yearmonth fromSersurvey Sgroup by S.classprocode,s.yearmonth) A can be paged: Begin with List as (SELECT row_number () over (ORDER by S.yearmonth DESC) as Row,s.classprocode,s.yearmonth,max ( c12> CaseS.surveycode when'SC001'Then S.scoreElse 0End as 'Applyreport', Max ( CaseS.surveycode when'SC002'Then S.scoreElse 0End as 'logisticsreception', Max ( CaseS.surveycode when'SC003'Then S.scoreElse 0End as 'Viceheadteacher', Max ( CaseS.surveycode when'SC001'Then S.flagElse 0End as 'Applyreportflag', Max ( CaseS.surveycode when'SC002'Then S.flagElse 0End as 'Logisticsreceptionflag', Max ( CaseS.surveycode when'SC003'Then S.flagElse 0End as 'Viceheadteacherflag'  fromSersurvey Swhere 1=1--and S.classprocode like'%ss%'and s.yearmonth='2010-05'GROUP BY S.classprocode,s.yearmonth) SELECT* FROM List WHERE Row between1andTenEnd

SQL Complex Query