SQL Learning (1)

Create a table:

Create Table tablename (colname datatype [not null]

{, Colname datatype [not null]...}

[, Primary key (colname {, colname...})];

Note: |, [], {},... won't appear in SQL, so mark it

Two simple select statements:

Select distinct ordno, X. CID, X. Aid, X. PID,

. 40 * (X. qty * P. Price)-. 01 * (C. dicnt + A. ercent) * (X. qty * P. Price) as Heji

From orders as X, customers as C, agents as a, products as P

Where C. cid = x. CID and A. Aid = x. Aid and P. PID = x. Aid;

The aliases of X, C, A, and P are orders, MERS MERs, agents, and products. The keyword as can be omitted.

Ordno, CID, aid, PID, and Heji are column names.

The as in front of Heji cannot be omitted, which is defined by Heji. 40 * (X. qty * P. price )-. 01 * (C. dicnt +. ercent) * (X. qty * P. price) is displayed as Heji

Distinct ensures that each row after retrieval is unique

Find the PID value of the product ordered by at least two customers

Select distinct x1.pid

From orders X1, orders X2

Where x1.pid = x2.pid and x1.pid <x2.pid ;,

1. In predicates

General expression of the in predicate: expr in (subquery) | expr in (Val {, Val ...})

For example, retrieve the ordno value of all order records composed of customers living in Duluth and agents living in New York.

Select ordno from orders

Where (CID, aid) in

(Select CID, aid from MERs C, agents a where c. City = 'dulu' and A. City = 'New York ');

The not in predicate is if and only if the calculated expr value is not in the Set returned by the subquery [expr not in (subquery)]

2. Quantified comparison predicates

General Form expr θ {some | any | all} (subquery) where θ is some operator in the set {<, <=, =, <>,>, >=}

Some in this form has the same meaning as any. θ includes: {<, <=, <>, >,>, >=}<> not equal

For example, find the aid value of the agent with the minimum Commission percentage.

Select aid from agents where percent <= All (select percent from agents );

Note: The predicate = some has the same effect as the predicate in.

Predicates <> all and not in are equivalent.

Predicates <> some are not equivalent to not in.

3. predicate exists

General Form [not] exists (subquery) Note: exists (subquery) is true when and only when the subquery returns a non-empty set

For example, find the CID value of the customer who ordered the product p01 and the product p07.

Select distinct CID from orders x

Where pid = 'p01' and exists

(Select * from orders where cid = x. CID and PID = 'p07 ');

Not exists query results can be replaced by not in and equivalent predicates <> All

4. SQL queries have many equivalent forms

For example, the expression used to retrieve the city name of the customer who ordered the product p01 has four main SELECT statement expressions.

Select distinct city from MERs where CID in

(Select CID from orders where pid = 'p01 ');

Select distinct city from MERs where cid = any

(Select CID from orders where pid = 'p01 ');

Select distinct city from MERs C where exists

(Select * From oredrs O where c. cid = O. CID and O. PID = 'p01 ');

Select distinct city from MERs C, orders o

Where C. cid = O. CID and O. PID = 'p01 ';

1. Union operator

Essentially, it is the sum in relational operations.

For example, create a list of cities where the customer is located, the agent is located, or the two are connected.

Select city from MERs

Union select city from agents;

When the designed subquery item is greater than or equal to 3 ()

(Select city from MERs

Union select city from agents)

Union all select city from products;

2. Division: SQL "for all..." Condition

If the query requires that the object set to be retrieved must meet a condition with the keyword "all", follow these steps:

1) Name the object to be retrieved and consider how to express an inverse example of the candidate object to be retrieved in English. In this counterexample, the "all" mentioned above"

At least one of the objects does not meet the specified conditions.

2) create a Search Condition for the SELECT statement to select all the counterexamples created in step 1. (Steps 1 and 2 are bound to reference objects from external select statements,

So we need to show some flexibility on how to use the tables where these external objects are located to reference them)

3) create a search condition that contains the statements created in step 2, indicating that there is no inverse example defined above. The not exists predicate will be involved here

4) use the search criteria in step 3 to create the final select statement and retrieve the expected object.

The preceding step sequence usually produces the following nested subquery pairs:

Select... where not exists (Select ...));

For example, identify the CID value of a customer with the following properties: if the customer c006 has ordered a product, the customer who wants to search for it has also ordered the product.

This question can be rewritten to: Find the CID value of all products ordered by the customer c006.

Step 1: We call C. CID a customer who meets the question and construct an inverse example in English.

"There is a product ordered by customer c006 that is not ordered by C. CID ."

Step 2: Describe the search criteria in English. We will name the product we ordered by c006 as P. PID.

Cond1: P. PID in (select PID from orders X where X. cid = 'c006 ')

And not exists (select * from orders y

Where Y. PID = P. PID and Y. cid = C. CID)

Step 3: Create a condition that indicates that this counter-sample does not exist:

Cond2: Not exists (select P. PID from prducts P

Where p. PID in (select PID from orders x

Where X. cid = 'c006 ') and

Not exists (select * from orders y

Where Y. PID = P. PID and Y. cid = C. CID ))

Step 4: Create the final select

Select CID from customers C

Where not exists (select P. PID from prducts P

Where p. PID in (select PID from orders x

Where X. cid = 'c006 ') and

Not exists (select * from orders y

Where Y. PID = P. PID and Y. cid = C. CID ));

A variant of this select statement is:

Select CID from customers C

Where not exists (select Z. PID from oredrs Z

Where Z. cid = 'c006 'and

Not exists (select * from orders y

Where Y. PID = Z. PID and Y. cid = C. CID ));

3. Table alias usage

For example, retrieve the names of all customers who have purchased the same product for at least two times.

Select cname from (select O. CID as spcid from orders o, oreders X where O. cid = x. CID

And O. PID = x. PID and O. ordno <> X. O. ordon) y, MERS MERs C

Where Y. spcid = C. CID;

In this query, the alias y is specified for the subquery result, and the alias spcid is provided for the column retrieved by the subquery.

1. Union operator

Essentially, it is the sum in relational operations.

For example, create a list of cities where the customer is located, the agent is located, or the two are connected.

Select city from MERs

Union select city from agents;

When the designed subquery item is greater than or equal to 3 ()

(Select city from MERs

Union select city from agents)

Union all select city from products;

2. Division: SQL "for all..." Condition

If the query requires that the object set to be retrieved must meet a condition with the keyword "all", follow these steps:

1) Name the object to be retrieved and consider how to express an inverse example of the candidate object to be retrieved in English. In this counterexample, the "all" mentioned above"

At least one of the objects does not meet the specified conditions.

2) create a Search Condition for the SELECT statement to select all the counterexamples created in step 1. (Steps 1 and 2 are bound to reference objects from external select statements,

So we need to show some flexibility on how to use the tables where these external objects are located to reference them)

3) create a search condition that contains the statements created in step 2, indicating that there is no inverse example defined above. The not exists predicate will be involved here

4) use the search criteria in step 3 to create the final select statement and retrieve the expected object.

The preceding step sequence usually produces the following nested subquery pairs:

Select... where not exists (Select ...));

For example, identify the CID value of a customer with the following properties: if the customer c006 has ordered a product, the customer who wants to search for it has also ordered the product.

This question can be rewritten to: Find the CID value of all products ordered by the customer c006.

Step 1: We call C. CID a customer who meets the question and construct an inverse example in English.

"There is a product ordered by customer c006 that is not ordered by C. CID ."

Step 2: Describe the search criteria in English. We will name the product we ordered by c006 as P. PID.

Cond1: P. PID in (select PID from orders X where X. cid = 'c006 ')

And not exists (select * from orders y

Where Y. PID = P. PID and Y. cid = C. CID)

Step 3: Create a condition that indicates that this counter-sample does not exist:

Cond2: Not exists (select P. PID from prducts P

Where p. PID in (select PID from orders x

Where X. cid = 'c006 ') and

Not exists (select * from orders y

Where Y. PID = P. PID and Y. cid = C. CID ))

Step 4: Create the final select

Select CID from customers C

Where not exists (select P. PID from prducts P

Where p. PID in (select PID from orders x

Where X. cid = 'c006 ') and

Not exists (select * from orders y

Where Y. PID = P. PID and Y. cid = C. CID ));

A variant of this select statement is:

Select CID from customers C

Where not exists (select Z. PID from oredrs Z

Where Z. cid = 'c006 'and

Not exists (select * from orders y

Where Y. PID = Z. PID and Y. cid = C. CID ));

3. Table alias usage

For example, retrieve the names of all customers who have purchased the same product for at least two times.

Select cname from (select O. CID as spcid from orders o, oreders X where O. cid = x. CID

And O. PID = x. PID and O. ordno <> X. O. ordon) y, MERS MERs C

Where Y. spcid = C. CID;

In this query, the alias y is specified for the subquery result, and the alias spcid is provided for the column retrieved by the subquery.