SQL语句习题

设某商业集团关于商店销售商品的数据库中有三个基本表：

商店SHOP（S#，SNAME，AREA，MGR_NAME）

其属性是商店编号，商店名称，区域名，经理姓名。

销售SALE（S#，G#， QUANTITY）

其属性是商店编号，商品编号，销售数量。

商品GOODS（G#，GNAME，PRICE）

其属性是商品编号，商品名称，单价。

36．试写出下列查询的关系代数表达式、元组表达式和关系逻辑规则：检索销售“冰箱”的商店的编号和商店名称。

37．试写出上面第36题的SELECT语句表达形式。并写出该查询的图示形式。38．试写出下列操作的SQL语句：

从SALE表中，把“开开商店”中销售单价高于1000元的商品的销售元组全部删除。

39．写一个断言，要求区域名为“EAST”的商店里销售商品的单价不能低于100元。

40．试写出下列操作的SQL语句：

统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。

要求显示（G#，GNAME，SUM_QUANTITY，SUM_VALUE），其属性为商品编号、商品名称、销售数量、销售价值。

37．解：SELECT语句如下：

SELECT A.S#，SNAME

FROM SHOP A，SALE B，GOODS C

WHERE A.S#=B.S# AND B.G#=C.G# AND GNAME='冰箱'；

该查询语句的图示形式如下：

SHOP S# SNAME AREA MGR_NAME

P．_X P.

SALE S# G# QUANTITY GOODS G# GNAME PRICE _X _Y _Y 冰箱

38．解：DELETE FROM SALE

WHERE S# IN（SELECT S#

FROM SHOP

WHERE SNAME='开开商店'）

AND G# IN（SELECT G#

FROM GOODS

WHERE PRICE>1000）；

39．解：CREATE ASSERTION ASSE8 CHECK

（100<=ALL（SELECT PRICE

FROM SHOP A，SALE B，GOODS C

WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'））；

或 CREATE ASSERTION ASSE8 CHECK

（NOT EXISTS（SELECT *

FROM SHOP A，SALE B，GOODS C

WHERE A.S#=B.S# AND B.G#=C.G#

AND AREA='EAST' AND PRICE<100））；

40．解：SELECT C.G#，GNAME，SUM（QUANTITY）AS SUM_QUANTITY，

PRICE*SUM（QUANTITY）AS SUM_VALUE

FROM SHOP A，SALE B，GOODS C

WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'

GROUP BY C.G#，GNAME；

（注：SELECT子句中的属性C.G#，GNAME应在分组子句中出现）

36．设数据库中有两个基本表：

职工表 EMP（E#，ENAME，AGE，SALARY，D#），

其属性分别表示职工工号、姓名、年龄、工资和工作部门的编号。

部门表 DEPT（D#，DNAME，MGR#）,

其属性分别表示部门编号、部门名称和部门经理的职工工号。

试指出每个表的主键和外键。并写出每个表创建语句中的外键子句。37．在第36题的两个基本表中，写出下列查询的关系代数表达式和SQL语句：

检索每个部门经理的工资，要求显示其部门编号、部门名称、经理工号、经理姓名和经理工资。

36．答：EMP表的主键为E#，外键为D#。

DEPT表的主键为D#，外键为MGR#

在EMP表的创建语句中,可写一个外键子句：

FOREIGN KEY D# REFERENCES DEPT（D#）；

在DEPT表的创建语句中,可写一个外键子句：

FOREIGN KEY MGR# REFERENCES EMP（E#）；

（DEPT ? EMP）

37．答：关系表达式为：π

DEPT.D#，DNAME，MGR#，ENAME，SALARY

SELECT语句为：

MGR#=E#

SELECT DEPT.D#，DNAME，MGR#，ENAME，SALARY

FROM DEPT，EMP

WHERE MGR#=E#；

31．设某商业集团为仓库存储商品设计了三个基本表：

仓库STORE（S#，SNAME，SADDR），其属性是仓库编号、仓库名称和地址。

存储SG（S#，G#，QUANTITY），其属性是仓库编号、商品编号和数量。

商品GOODS（G#，GNAME，PRICE），其属性是商品编号、商品名称和单价

现检索仓库名称为“莘庄”的仓库里存储的商品的编号和名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。32．在第31题的基本表中，检索存储全部种类商品的仓库的编号及名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。33．在第31题的基本表中，检索每个仓库存储商品的总价值。试写出相应的SELECT语句。要求显示（S#，SUM_V ALUE），其属性为仓库编号及该库存储商品的总价值。

34．在第31题的基本表中，写一个断言，规定每个仓库存储商品的单价为1万元以上的商品种类最多为20种。

31．解：关系代数表达式为：π

G#，GNAME （σ

SNAME= '莘庄'

（STORE?SG?GOODS））

元组表达式为：{ t | (?u) (?v) (?w) (GOODS(u)∧SG(v)∧STORE（w）

∧u[1]=v[2]∧v[1]=w[1]∧w[2]='莘庄'∧t[1]=u[1]∧t[2]=u[2])}

关系逻辑规则如下：

W（x，y）←GOODS（x，y，a）∧SG（b，x，c）∧STORE（b，'莘庄'，d）

SELECT语句为：

SELECT A.G#，GNAME

FROM GOODS AS A，SG AS B，STORE AS C

WHERE A.G#=B.G# AND B.S#=C.S# AND SNAME='莘庄'；32．解：关系代数表达式为：

π

S#，SNAME （STORE?（π

S#，G#

（SG）÷π

G#

（GOODS）））

元组表达式为：

{ t | (?u) (?v) (?w) (STORE(u) ∧ GOODS（v）∧ SG(w) ∧ w[1]=u[1]

∧ w[2]=v[1] ∧ t[1]=u[1] ∧ t[2]=u[2])} 关系逻辑规则如下：

W（x，y）← STORE（x，y，a）∧┐GOODS（b，c，d）∧┐SG（x，b，e）SELECT语句为：

SELECT S#，SNAME

FROM STORE

WHERE NOT EXISTS

（SELECT *

FROM GOODS

WHERE NOT EXISTS

（SELECT *

FROM SG

WHERE SG.S#=STORE.S#

AND SG.G#=GOODS.G#））；

33．解：SELECT语句为：

SELECT S#，SUM（QUANTITY *PRICE）AS SUM_VALUE

FROM SG，GOODS

WHERE SG.G#=GOODS.G#

GROUP BY S#；

34．解：CREATE ASSERTION ASSE6 CHECK

（20>=ALL（SELECT COUNT（SG.G#）

FROM SG，GOODS

WHERE SG.G#=GOODS.G# AND PRICE>10000

GROUP BY S#））；

现有关系数据库如下：

部门（部门编号，部门名称，电话）

员工(员工编号，姓名，性别，部门编号，职务)

项目(项目编号，项目名称，预算)

施工(员工编号，项目编号，工时)

用关系代数表达式实现下列1—2小题：

1．检索“王小毛”的员工的部门编号和职务；

2．检索工时超过100小时的项目名称和员工的姓名；

用SQL语言实现下列3—7小题：

3．查询每个员工的姓名，并按职称降序排列；

4．查询总工时在480小时以上（含480小时）的员工编号和总工时；

5．检索职称为“高级工程师”，同时至少参加了一预算在100万以上的项目的员工的姓名和部门名称；

6．定义一个工作量视图，包含的属性有员工编号、姓名、项目名称和工时。7．对项目名称为“高教新区项目”的施工工时增加50小时；

1.π

部门编号，职务 (σ

姓名=‘王小毛’

(员工))

2. π

项目名称，姓名(σ

工时)100

(施工)项目员工))

3.SELECT 姓名 FROM 员工 ORDER BY 职称 DESC

4．SELECT 员工编号，SUM（工时） FROM 施工

GROUP BY 员工编号 HAVING SUM（工时）>=480

5. SELECT 姓名，部门名称

FROM 员工，部门，项目，施工

WHERE 部门.部门编号=员工.部门编号 AND 员工.员工编号=施工.员工编号

AND 施工.项目编号=项目.项目编号 AND 职称=‘高级工程师’ AND 预算>1000000

6. CREATE VIEW 工作量( 员工编号，姓名，项目名称，工时)

AS SELECT员工.员工编号，姓名，项目名称，工时

FROM 员工，项目，施工

WHERE员工.员工编号=施工.员工编号AND 施工.项目编号=项目.项目编号

7.UPDATE 施工SET 工时=工时+50 WHERE 项目编号 IN

(SELECT 项目编号FROM 项目WHERE 项目名称=‘高教新区项目’)

37. 图书出版管理数据库中有两个基本表：

图书(书号，书名，作者编号，出版社，出版日期)

作者(作者编号，作者名，年龄，地址)

试用SQL语句写出下列查询：检索年龄低于作者平均年龄的所有作者的作者名、

书名和出版社。

39. 学校有多名学生，财务处每年要收一次学费。为财务处收学费工作设计一个数据库，包括两个关系：

学生(学号，姓名，专业，入学日期)

收费(学年，学号，学费，书费，总金额)

假设规定属性的类型：学费、书费、总金额为数值型数据；学号、姓名、学年、专业为字符型数据；入学日期为日期型数据。列的宽度自定义。试用SQL语句定义上述表的结构。(定义中应包括主键子句和外键子句)

37. SELECT 作者名，书名，出版社FROM 图书，作者WHERE 图书. 作者编号= 作者. 作者编号AND 年龄< = (SELECT A VG (年龄) FROM 作者)；

39. CREATE TABLE 学生

(学号CHAR (8)，姓名CHAR (8)，专业CHAR (13)，入学日期DATE，PRIMARY KEY (学号))；

CREATE TABLE 收费

(学年CHAR (10)，学号CHAR (8)，学费NUMERIC (4)，书费NUMERIC (5，2)，总金额NUMERIC (7，2)，PRIMARY KEY (学年，学号)，FOREIGN KEY (学号) REFERENCES 学生(学号))；

现有关系数据库如下： 学生(学号，姓名，性别，专业，奖学金) 课程(课程号，名称，学分) 学习(学号，课程号，分数)

5.检索不学课程号为“C135”课程的学生信息，包括学号，姓名和专业；

6.检索至少学过课程号为“C135”和“C219”的学生信息，包括学号、姓名和专业；

7.从学生表中删除成绩出现过0分的所有学生信息；

8.定义“英语”专业学生所学课程的信息视图AAA，包括学号、姓名、课程号和分数。

5.SELECT 学号，姓名，专业

FROM 学生 WHERE 学号 NOT IN (SELECT 学号FROM 学习 WHERE 课程号='C135')

6.SELECT 学号，姓名，专业 FROM 学生 WHERE 学号 IN (SELECT X. 学号

FROM 学习 X，学习 Y WHERE X.学号=Y.学号 AND X.课程号='C135' AND Y.课程号=’C219’)

7.DELETE FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 学习 WHERE 分数=0)

8.CREATE VIEW AAA(学号，姓名，课程号，分数) AS SELECT 学号，姓名，课程号，分数 FROM 学生，学习 WHERE 学生.学号 =学习.学号 AND 专业=’英语’

1．用SQL的有关语句定义

1）学生关系Student，包括学号Sno、姓名SN、年龄SA；

2）课程关系Course，包括课程号Cno、课程名CN、任课教师CT；

3）学生选课关系，包括Sno、Cno和成绩G。

注意：说明主键码和外键码（如果有的话），并在键码属性上建立索引。

（12分）

2．按题1的学生关系模式，用SQL的有关语句

1）授予赵华对学生关系Student的插入和修改权限；

2）插入任意3个学生的完整信息；

3）修改所有学生的年龄（加1）。

（12分）

3．结合题1定义的学生选课数据库，用SQL语句查询计算机系学生数据库课的成绩，输出学生姓名和成绩，按成绩排序（降序），若成绩相同，再按学号排序（升序）。

（6分）

1．1）CREATE TABLE Student(

Sno INT PRIMARY KEY,

SN CHAR(30),

SA INT

);

2) CREATE TABEL Course(

Cno INT PRIMARY KEY,

CN CHAR(30),

CT CHAR(30)

);

3) CREATE TABEL SC(

Sno INT,

Cno INT,

G INT,

PRIMAYR KEY(Sno,Cno),

FOREIGN KEY(Sno) REFERENCES Student(Sno),

FOREIGN KEY(Cno) REFERENCES Course(Cno)

);

2．1）GRANT INSERT, UPDATE, ON Student TO 赵华；

2）INSERT INTO Student

(TABLE (01001,’赵华’,19),

(01002,’李芳’,18),

(01003,’刘红’,20),

);

3）UPDATE Student SET SA=SA+1;

3．SELECT SN,G FROM Student,Course,SC

WHERE Student.Sno=SC.Sno AND https://www.360docs.net/doc/8714326486.html,o=https://www.360docs.net/doc/8714326486.html,o

AND https://www.360docs.net/doc/8714326486.html,=’数据库’ORDER BY G DESC,Sno;