Detailed explanation of complex SQL query statements in Oracle (1/4)

1. Use of query statements
Use a SELECT statement and a subquery (subquery) to return data from one or more tables, views, and entities.

1.1 Related subqueries
You can use a subquery (as subquery) or in or exists as part of a condition of a where, such a query is called a subquery
. A subquery in a where can contain a SELECT statement
The. Where can contain in,exists statements
. You can nest up to 16 layers
. Too many layers can affect performance
[Example] Simple subquery instance
Inquire whether some experts apply for the fund project in the name of the Institute, and apply for the project with the University department as the unit
(Only one unit is required to apply)
Sql> CREATE TABLE Univ_subject
2 (
3 name VARCHAR2 () NOT NULL,
4 per_id number NOT NULL,
5 dept_name varchar2 (20)
6);
sql> INSERT into univ_subject values (' gaoqianjing ', 1001, ' Information Engineering Department ');
sql> INSERT into univ_subject values (' wangbing ', 1002, ' Physics Department ');
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 (' E-Institute ', 1001);
sql> INSERT into colle_subject values (' Physics Institute ', 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

Home 1 2 3 4 last page