There are two models for this problem ：

The first is to give you numbers , Groups of tables of letters or attributes , Let you use relational algebra

The second is to give “ Relational database schema ”, Let's use “ Relational algebraic expression ” Represents various queries , More difficult than the first

Random this year

Let's take a look at the concept ：

one , Relational database schema ： Abbreviated as R（A1,A2,~~~An）,R Is the relationship name ,A Is the property name

eg: company （ Company No , Company name , address ）

eg:S (S#,SNAME,AGE,SEX)( Some relationship models will be in pure English ）

two . Relational algebraic expression ：

hand over n, and U, difference -（ High school , slightly ）, Cartesian product x, choice σ, Projection Π, connect

Cartesian product ：

S:

T:

A

B

A

B

3

4

1

5

7

2

3

6

be SxT=

S.AS.BT.AT.B
3415
3436
7215
7236
Cartesian product is an operation in horizontal direction , Each row of the first table is connected to each row of the second table

choice σ：

grammar ：σ+ condition （ Table queried ）

eg： commodity （ commodity ID, Trade name , Price , brand , model , colour , manufacturer , Place of Origin ）

Query commodity information of Shanghai origin ：σ Place of Origin =‘ Shanghai ’ ( commodity )

Select a query that is horizontal , The result is all the attributes of the eligible product （ Remember to use single quotation marks for Strings ）

Projection Π：

eg： commodity （ commodity ID, Trade name , Price , brand , model , colour , manufacturer , Place of Origin ）

Inquire about goods whose origin is Shanghai ID, Trade name ：Π commodity ID, Trade name （σ Place of Origin =‘ Shanghai ’（ commodity ））

Query where projection is vertical , The result is a single attribute

connect ：

Connections are typically used for tables , Connected with natural connection , External connection , Left link , Right connection . The teacher won't embarrass us , There are only natural connection questions in the question bank , So let's just talk about the simplest natural connection

As shown above , Connection is to perform Cartesian operations on rows with the same attributes in two relationships

except ：

In the previous question R As dividend ,S As divisor

method ：（1） Find out first R,S Common properties in B,C

（2） And find the dividend R Common attribute values and S There are rows with the same attribute value ,

Namely R（2）：

ABC
a1b1c2
a1b2c3
a2b2c3
a1b2c1
（3） Remove the second step table R（2） Common attribute columns in B,C, Leave only A column

Namely

A
a1
a1
a2
a1
Simplified

Master these basic concepts , We can do the problem ,SQL Various query methods , So the answers to many questions are not unique .

example 1： True topic

solution ：1.

example 2,

solution ：

example 3,

solution ：

Technology