Explain the complex SQL query statements in oracle (1/4)

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 3 4