Use of complex query statements

1. Use of query statements
The SELECT statement and subquery can be used to return data from one or more tables, views, and entities.

1.1 Related subqueries
Subqueries (as subquery), in, or exists can be considered as part of a where condition. Such queries are called subqueries.
. Where can contain a subquery of a SELECT statement
. Where can contain in and exists statements
. A maximum of 16 layers can be nested.
Too many layers will affect performance
[Example] Simple subquery instance
Check whether some experts apply for a fund project in the name of the Institute, and apply for a project in the Unit of the Department of university.
(The application can only be made in one unit as required)
SQL> Create Table univ_subject
2 (
3 name varchar2 (12) not null,
4 per_id number not null,
5 dept_name varchar2 (20)
6 );
SQL> insert into univ_subject values ('gaoqianjing ', 1001, 'department of Information engine ');
SQL> insert into univ_subject values ('wangbing ', 1002, 'physics ');
SQL> insert into univ_subject values ('liming', 1003, 'chemistry Department ');
====================
SQL> Create Table colle_subject
2 (
3 colle_name varchar2 (20 ),
4 per_id number
5 );
SQL> insert into colle_subject values ('Institute of electronics, 1001 );
SQL> insert into colle_subject values ('Institute of physics, 1005 );
======================
SQL> select name, per_id, dept_name from univ_subject where per_id in
2 (select per_id from colle_subject );

Name per_id dept_name
-----------------------------------------
Gaoqianjing 1001 Department of Information Engineering

1.2 outer connections
[Example] external connection instance
The information of all students in enrollment is in the students table, and some students with special skills also have students in stuent_skill in another table.
. All students need to be listed now. If a student has his/her expertise in the student_skill table, the students must be displayed. If
If a student does not have any special skills, the special skills are displayed empty.
SQL> Create Table students
2 (
3 st_id varchar2 (20 ),
4 name varchar2 (10 ),
5 age Number (2 ),
6 tol_score number (3)
7 );
SQL> insert into students values ('20170901', 'hangbindu', 973231 );
SQL> insert into students values ('20140901', 'zhuzhijing', 973232 );
SQL> insert into students values ('20170901', 'gaojing', 973233 );
==============================

SQL> Create Table student_skill
2 (
3 st_id varchar2 (20 ),
4 skill varchar2 (20)
5 );
SQL> insert into student_skill values ('123456', 'basketball ');
SQL> insert into student_skill (st_id) values ('20140901 ');
SQL> insert into student_skill values ('20140901', 'soccer ');
==============================

SQL> select a. *, B. Skill from students a, student_skill B where a. st_id = B. st_id (+)
Order by A. st_id;

St_id name age tol_score skill
--------------------------------------------------------------------
973231 wangbindu 22 501 basketball
973232 zhuzhijing 21 538
973233 Gaojing 21 576 football

1.3 Self-connection
Self-join is a conditional connection in the same table or view.
[Example] self-connection instance
Query the name of each employee and the name of the employee's manager:
SQL> select e1.ename | 'work for '| e2.ename "employees and their managers"
2 from Scott. EMP E1, Scott. EMP E2 where e1.mgr = e2.empno;

Employees and their managers
-------------------------------------------------
Smith work for Ford
Allen work for Blake
Ward work for Blake
Jones work for King
Martin work for Blake
Blake work for King
Clark work for King
Scott work for Jones
Turner work for Blake
Adams work for Scott
James work for Blake
Ford work for Jones
Miller work for Clark

1.4 union, intersect and minus
Union: Put the similar query results of two or more tables together (Union all indicates that all rows are returned)
Syntax:
Select...
Union [all]
Select...
============

Intersect: returns the same information in two tables.
Syntax:
Select...
Intersect
Select...
============

Minus: returns information in a table.
Syntax:
Select...
Minus
Select...
[Example 1] union operation instance
SQL> select st_id from students
2 Union
3 select st_id from student_skill;

St_id
--------------------
973231
973232
973233

[Example 2] intersect operation instance
List student IDs with special skills
SQL> select st_id from students
2 intersect
3 select st_id from student_skill;
St_id
--------------------
973231
973233

[Example 3] minus operation instance
List student IDs without special skills
Select st_id from students
Minus
Select st_id from student_skill;
St_id
--------------------
973232

2. Create a complex view
Many application systems have statistical functions. We recommend that you write these complex statements into a view. Below are several common views.
2.1 group view
[Example 1] Simple grouping View
SQL> Create or replace view dept_tot
2 select a. dname Dept, sum (B. Sal) total_sal from Scott. Dept A, Scott. EMP B
3 where a. deptno = B. deptno group by A. dname;

View created.
SQL> select * From dept_tot;

Dept total_sal
-----------------------
Accounting 8750
Research 10875
Sales 9400

[Example 2] complex function view
SQL> Create or replace view itemtot
2 select persion, sum (amount) itemtot from ledger
3 where actiondate
4 to_date ('01-Mar-1901 ', 'dd-mon-yyyy') and
5 to_date ('31-Mar-1901 ', 'dd-mon-yyyy ')
6 and action in ('bought ', 'raid') group by persion;

2.2 total view
[Example] aggregate function view instance
SQL> Create or replace view emp_no1
2 select deptno, sum (SAL) sum, sum (Comm) sum
3 from Scott. EMP group by deptno;
SQL> select * From emp_no1;
Deptno salary and sum
---------------------------
10 8750
20 10875
30 9400 2200

2.3 combined view
[Example] view with composite Functions
SQL> Create or replace view byitem
2 select L. persion. Item, amount, 100 * Amount/item bypersion, 100 * Amount/total bytotal
3 from ledgent L, itemtotal I, total where L. persion = I. persion where L. persion = I. persion
4 and actiondate
5 to_date ('01-Mar-1901 ', 'dd-mon-yyyy') and
6 to_date ('31-Mar-1901 ', 'dd-mon-yyyy ')
7 and action in ('bought ', 'raid ');

3. Family Tree
Syntax:
Select column from table_name start with column = Value
Connect by prior parent primary key = child primary key

3.1 eliminate SINGULARITY AND BRANCHING
Take the EMP table in Oracle as an example.
[Example] list information of employees from the top to the bottom
SQL> select lpad ('', 4 * (level-1) | ename name, empno, Mgr from EMP start with Mgr is null
2 connect by prior empno = Mgr;

Name empno Mgr
---------------------------
King 7839
Jones 7566 7839
Scott 7788 7566
Adams 7876 7788

3.2 traverse to the root
[Example 1] list the hierarchy of an employee from the employee's superiors.
SQL> Col ename for A30;
SQL> select lpad ('', 4 * (level-1) | ename, Mgr, empno from Scott. EMP
2 start with Mgr = 7788 connect by prior Mgr = empno;
Ename Mgr empno
------------------------------------------------
Adams 7788 7876
Scott 7566 7788
Jones 7839 7566
King 7839

[Example 2] list the hierarchies of all employees
SQL> select lpad ('', 4 * (level-1) | ename, empno, Mgr from Scott. EMP
2 start with Mgr is not null
3 connect by empno = prior Mgr;

Ename empno Mgr
------------------------------------------------
Smith 7369 7902
Ford 7902 7566
Jones 7566 7839
King 7839
Allen 7499 7698
Blake 7698 7839
King 7839
Ward 7521 7698
Blake 7698 7839
King 7839
Jones 7566 7839
King 7839
Martin 7654 7698
Blake 7698 7839
King 7839
Blake 7698 7839
King 7839
Clark 7782 7839
King 7839
Scott 7788 7566
Jones 7566 7839