本文详细介绍了教学数据库的查询与更新操作，包括使用关系代数和SQL表达查询，如学生选课、教师授课、课程统计等。同时展示了如何通过SQL语句进行数据插入、删除、修改，以及对职工表、工作表和公司表的操作，涉及职工年龄筛选、课程统计、工资调整等多个场景。
文章目录
- 引言
- PART 1
- PART 2
- PART 3
- PART 4
- (1）用 CREATE TABLE 语句创建上述三个表，需指出主键和外键。
- (2）检索超过35岁的男职工的工号和姓名。
- (3）假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工的工号姓名。
- (4）假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工的工号和姓名。
- (5）检索在“联华公司”工作，工资超过1000元的男性职工的工号和姓名。
- (6）假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（ ENO,NUМ,SUМ_ SALARY )，分别表示工号、公司数目和工资总数。
- (7）工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。
- (8）检索华联公司中低于本公可平均工资的职工的工号和姓名。
- (9）在每一公司中为60岁以上职工加薪100元（若职工为多个公司工作，可重复加）。
- (10)在 EMP 表和WORKS表中删除年龄大于60岁的职工有关元组。
引言
本文基于markdown格式书写，公式及表达式使用latex语句编码。
PART 1
1.对于教学数据库的三个基本表
s ( SNO , SNAME , AGE , SEX ) sc ( SNO , CNO , CNAME )
c ( CNO, CNAME , TEACHER )
试用关系代数表达式表示下列查询语句：
(1）检索学号为S20220099同学所学课程的课程号和课程名。
Π C N O , C N A M E ( σ S N O = ′ S 2022009 9 ′ ( s c ) ) \Pi_{CNO,CNAME}(\sigma_{SNO='S20220099'}(sc)) ΠCNO,CNAME(σSNO=′S20220099′(sc))
(2）检索年龄大于20岁的女同学的学号和姓名。
Π S N O , S N A M E ( σ A G E > 20 ⋀ S E X = ′ 女 ′ ( s ) ) \Pi_{SNO,SNAME}(\sigma_{AGE>20 \bigwedge SEX='女'}(s)) ΠSNO,SNAME(σAGE>20⋀SEX=′女′(s))
(3）检索姓名为WANG学生所学课程的课程名与课程号。
Π C N A M E , C N O ( σ S N A M E = ′ W A N G ′ ( s ⋈ s c ) ) \Pi_{CNAME,CNO}(\sigma_{SNAME='WANG'}(s \Join sc)) ΠCNAME,CNO(σSNAME=′WANG′(s⋈sc))
(4）检索至少教授WANG学生一门课的老师姓名。
Π T E A C H E R ( σ S N A M E = ′ W A N G ′ ⋀ S E X = ′ 男 ′ ( s ⋈ c ⋈ s c ) ) \Pi_{TEACHER}(\sigma_{SNAME='WANG'\bigwedge SEX='男'}(s\Join c \Join sc)) ΠTEACHER(σSNAME=′WANG′⋀SEX=′男′(s⋈c⋈sc))
(5）检索 WANG 同学不学的课程的课程号。
Π C N O ( C ) − Π C N O ( σ S N A M E = ′ W A N G ′ ( s ⋈ s c ) ) \Pi_{CNO}(C)-\Pi_{CNO}(\sigma_{SNAME='WANG'}(s\Join sc)) ΠCNO(C)−ΠCNO(σSNAME=′WANG′(s⋈sc))
(6）检索至少教授两门课的教师姓名。
Π T E A C H E R ( σ C . T E C A H E R = A . T E A C H E R ⋀ C . C N A M E ! = A . C N A M E ( c ⋈ p A ( c ) ) ) \Pi_{TEACHER}(\sigma_{C.TECAHER=A.TEACHER \bigwedge C.CNAME !=A.CNAME}(c \Join pA(c))) ΠTEACHER(σC.TECAHER=A.TEACHER⋀C.CNAME!=A.CNAME(c⋈pA(c)))
(7）检索全部学生都选修的课程的课程号与课程名。
Π C N O , C N A M E ( C ⋈ ( Π S N O , C N O ( S C ) ÷ Π S N O ( S ) ) ) \Pi_{CNO,{CNAME}}(C \Join (\Pi_{SNO,CNO}(SC)\div\Pi_{SNO}(S)) ) ΠCNO,CNAME(C⋈(ΠSNO,CNO(SC)÷ΠSNO(S)))
(8）检索选修课程包含 WANG 同学所选全部课程的学生学号。
Π S N O , C N O ( S C ) ÷ Π C N O ( σ S N A M E = ′ W A N G ′ ( s ⋈ s c ) ) \Pi_{SNO,CNO}(SC)\div\Pi_{CNO}(\sigma_{SNAME='WANG'}(s \Join sc)) ΠSNO,CNO(SC)÷ΠCNO(σSNAME=′WANG′(s⋈sc))
PART 2
2．对于教学数据库的三个基本表：
S ( SNO, SNAME, AGE, SEX )
SC ( SNO, CNo, GRADE )
C ( CNO, CNAME, TEACHER)
试用 SQL 的查询语句表达下列査询：
(1）在表 SC 中统计已选修课程的学生人数
Use education1
GO
--在表 SC 中统计已选修课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC
验证：
SC中已经选秀课程人数为3
(2）求选修C0001课程的学生的姓名和性别。
Use education1
GO
--求选修C0001课程的学生的姓名和性别
SELECT S.Sname,S.Sex
FROM S,SC
WHERE SC.Cno='C001' AND SC.Sno=S.Sno
验证：
选修C001的仅有S001同学，而S001对应liu同学
(3）求王海同学所上每门课程的该门课学生平均成绩。
Use education1
GO
--求王海同学所上每门课程的该门课学生平均成绩
SELECT Cname,AVG(Grade) 平均成绩
FROM SC ,C,S
WHERE SC.Cno=C.Cno AND SC.Sno=S.Sno AND S.Sname='王海'
GROUP BY C.Cno,Cname
(4）统计每个学生选修课程的门数（超过5门的学生才统计）
。要求输出学生学号和选修门数，查询结果按门数降序排列，若门数相同，按学号升序排列。
Use education1
GO
--统计每个学生选修课程的门数（超过5门的学生才统计）。要求输出学生学号和选修门数，查询结果按门数降序排列，若门数相同，按学号升序排列
SELECT DISTINCT Sno 学生学号,COUNT(Cno) 选修门数
FROM SC
GROUP BY Sno
HAVING COUNT(Cno)>5
ORDER BY 2 DESC,Sno ASC
(5）检索选修C0001课程成绩比 Liu 同学高的男学生姓名。
Use education1
GO
--检索选修C0001课程成绩比 Liu 同学高的男学生姓名。
SELECT Sname
from S,SC
WHERE S.Sno IN(SELECT Sno
FROM SC
WHERE SC.Grade>(SELECT Grade FROM SC,S WHERE Sname='liu' AND S.Sno=SC.Sno AND SC.Cno='C002')AND SC.Cno='C001'
)
AND S.Sex='男' AND S.Sno=SC.Sno AND SC.Cno='C001'
(6）在表 SC 中检索成绩为空值的学生的学号和课程号。
Use education1
GO
--在表 SC 中检索成绩为空值的学生的学号和课程号。
SELECT Sno,Cno
from SC
WHERE Grade IS NULL
(7）检索课程名以 “计算机” 打头的所有课程的课程号和课程名。
Use education1
GO
--检索课程名以 “计算机” 打头的所有课程的课程号和课程名
SELECT DISTINCT C.Cno,Cname
from SC,C
WHERE C.Cno=SC.Cno AND C.Cname LIKE '计算机%'
(8）求选修C0001课程成绩高于该门课程男同学平均成绩的女学生的姓名和年龄。
Use education1
GO
--求选修C0001课程成绩高于该门课程男同学平均成绩的女学生的姓名和年龄
SELECT Sname,Age
FROM S
WHERE Sno IN(SELECT Sno
FROM SC
WHERE Grade>(SELECT AVG(Grade)
FROM SC,S
WHERE S.Sno=SC.Sno AND S.Sex='男' AND SC.Cno='C001')
AND SC.Cno='C001' )
AND S.Sex='女'
(9）检索全部学生都选修的课程的课程号与课程名。
Use education1
GO
--检索全部学生都选修的课程的课程号与课程名
Select Sname from S
where not Exists (select * from C where not Exists (select * from SC where Sno=S.Sno and Cno=C.Cno))
(10）检索选修课程包含 Liu 老师所授全部课程的学生学号。
Use education1
GO
--检索选修课程包含 Liu 老师所授全部课程的学生学号。
Select DISTINCT SC.Sno
from S
where exists (select SNO
from SC,C,S
where SC.CNO=C.CNO and SC.SNO = S.SNO and C.TEACHER = 'Liu');
PART 3
3.用 SQL 语句表达对2题中教学数据库关系 S 、 SC 、 C 的更新操作
(1）向关系S中插入一条学生记录（“S20220099”，“宋刚”，18，“男”）
Use education1
GO
--向关系S中插入一条学生记录（“S20220099”，“宋刚”，18，“男”）
INSERT
INTO S(Sno,Sname,Age,Sex)
VALUES('S20220099','宋刚',18,'男')
(2）检索所授每门课程平均成绩均大于80分的教师姓名。
Select TEACHER
from C a where
CNO in
(select CNO
from SC
group by CNO
having avg(GRADE)>80)
group by TEACHER
having count(*)=(select count(*) from C b where a.TEACHER=b.TEACHER)
(3）在 C 中删除无授课教师的选课元组。
Use education1
GO
--在 C 中删除无授课教师的选课元组
DELETE FROM C WHERE Teacher IS NULL
(4）把选修“区块链技术”课程的女同学选课元组全部删去。
Use education1
GO
DELECT FROM SC
WHERE Sno IN(SELECT Sno FROM S WHERE Sex ='女')
AND Cno IN (SELECT Cno FROM C WHERE Cname='区块链技术')
(5）把 MATHS 课六十分以下五十分以上的成绩全改为60分。
Use education1
GO
--把 MATHS 课六十分以下五十分以上的成绩全改为60分
UPDATE SC
SET Grade =60
WHERE Grade <60 AND Grade >50
AND Cno IN (SELECT Cno FROM C WHERE Cname='MATHS')
(6）把低于所有课程总平均成绩的同学成绩提高5%。
Use education1
GO
--把低于所有课程总平均成绩的同学成绩提高5%
UPDATE SC
SET Grade=Grade*1.05
WHERE Sno IN (SELECT Sno FROM S ) AND Grade<(SELECT AVG(Grade) FROM SC)
(7）当某个成绩低于该门课程的平均成绩时提高5%。
Use education1
GO
--当某个成绩低于该门课程的平均成绩时提高5%
UPDATE SC
SET Grade=Grade*1.05
WHERE Grade<(SELECT AVG(Grade)
FROM SC)
PART 4
4．设数据库中有三个关系：
职工表 EMP ( ENO , ENAME , AGE , SEX , ECITY )，其属性分别表示职工工号、姓名年龄、性别和籍贯。
工作表 WORKS ( ENO , CNO , SALARY )，其属性分别表示职工工号、工作的公司编号和工资。
公司表 COMP ( CNO , CNAME , CITY )，其属性分别表示公司编号、公司名称和公司所在城市。
试用 SQL 语句写出下列操作：
(1）用 CREATE TABLE 语句创建上述三个表，需指出主键和外键。
(2）检索超过35岁的男职工的工号和姓名。
(3）假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工的工号姓名。
(4）假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工的工号和姓名。
(5）检索在“联华公司”工作，工资超过1000元的男性职工的工号和姓名。
(6）假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（ ENO,NUМ,SUМ_ SALARY )，分别表示工号、公司数目和工资总数。
(7）工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。
(8）检索华联公司中低于本公可平均工资的职工的工号和姓名。
(9）在每一公司中为60岁以上职工加薪100元（若职工为多个公司工作，可重复加）。
(10)在 EMP 表和WORKS表中删除年龄大于60岁的职工有关元组。
https://blog.youkuaiyun.com/practical_sharp/article/details/89484451
(1）用 CREATE TABLE 语句创建上述三个表，需指出主键和外键。
Use Master;
GO
CREATE Database emp1
ON
( NAME=emp1,
FILENAME='C:\MSML\sqlserver\database\emp.mdf',
SIZE=2,
MAXSIZE=unlimited,
FILEGROWTH=1
)
LOG ON
( NAME=emp1_log,
FILENAME='C:\MSML\sqlserver\database\emp.ldf',
SIZE=1,
MAXSIZE=unlimited,
FILEGROWTH=1
)
GO
Use emp1;
CREATE table EMP(
ENO VARCHAR(2) constraint pk_EMP primary key,
Ename VARCHAR(10) not null,
age smallint CHECK(age>0 AND age<120),
sex char(2) check ( sex in('男','女')),
Ecity varchar(12)
);
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E1','流浪者','18','男','稻妻');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E2','珐露珊','100','女','须弥');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E3','优菈','24','女','蒙德');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E4','纳西妲','8','女','须弥');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E5','妮露','18','女','须弥');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E6','赛诺','17','男','须弥');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E7','达达利亚','26','男','至冬');
INSERT into EMP(ENO,Ename,age,sex,Ecity) VALUES ('E8','夜兰','26','女','璃月');
CREATE TABLE COMP(
CNO VARCHAR(4) Constraint pk_Comp primary key,
Cname VARCHAR(12) Constraint u_Comp unique,
City VARCHAR(12) not null
);
INSERT into COMP(CNO,Cname,City) VALUES('C1','愚人众','至冬');
INSERT into COMP(CNO,Cname,City) VALUES('C2','教令院','须弥');
INSERT into COMP(CNO,Cname,City) VALUES('C3','西风骑士团','蒙德');
INSERT into COMP(CNO,Cname,City) VALUES('C4','净善宫','须弥');
INSERT into COMP(CNO,Cname,City) VALUES('C5','祖拜尔剧场','须弥');
INSERT into COMP(CNO,Cname,City) VALUES('C6','缄默之殿','须弥');
INSERT into COMP(CNO,Cname,City) VALUES('C7','海祇岛','稻妻');
INSERT into COMP(CNO,Cname,City) VALUES('C8','璃月总务司','璃月');
INSERT into COMP(CNO,Cname,City) VALUES('C9','社奉行','稻妻');
INSERT into COMP(CNO,Cname,City) VALUES('C10','镀金旅团','须弥');
CREATE TABLE Works(
ENO VARCHAR(2),
C VARCHAR(4),
Salary int check(salary>0),
Constraint fk_e foreign key(E) references EMP(ENO),
Constraint fk_c foreign key(C) references COMP(CNO)
);
INSERT into Works(ENO,CNO,Salary) VALUES('E1','C3','200');
INSERT into Works(ENO,CNO,Salary) VALUES('E1','C5','900');
INSERT into Works(ENO,CNO,Salary) VALUES('E2','C1','3000');
INSERT into Works(ENO,CNO,Salary) VALUES('E2','C2','1000');
INSERT into Works(ENO,CNO,Salary) VALUES('E2','C8','500');
INSERT into Works(ENO,CNO,Salary) VALUES('E2','C6','300');
INSERT into Works(ENO,CNO,Salary) VALUES('E3','C3','320');
INSERT into Works(ENO,CNO,Salary) VALUES('E3','C7','1050');
INSERT into Works(ENO,CNO,Salary) VALUES('E3','C9','250');
INSERT into Works(ENO,CNO,Salary) VALUES('E3','C10','120');
INSERT into Works(ENO,CNO,Salary) VALUES('E4','C1','240');
INSERT into Works(ENO,CNO,Salary) VALUES('E4','C4','20');
INSERT into Works(ENO,CNO,Salary) VALUES('E4','C6','400');
INSERT into Works(ENO,CNO,Salary) VALUES('E5','C5','600');
INSERT into Works(ENO,CNO,Salary) VALUES('E5','C6','1200');
INSERT into Works(ENO,CNO,Salary) VALUES('E5','C7','900');
INSERT into Works(ENO,CNO,Salary) VALUES('E5','C4','200');
INSERT into Works(ENO,CNO,Salary) VALUES('E6','C7','400');
INSERT into Works(ENO,CNO,Salary) VALUES('E6','C8','5000');
INSERT into Works(ENO,CNO,Salary) VALUES('E6','C4','200');
INSERT into Works(ENO,CNO,Salary) VALUES('E6','C3','20');
INSERT into Works(ENO,CNO,Salary) VALUES('E7','C1','300');
INSERT into Works(ENO,CNO,Salary) VALUES('E7','C2','25');
INSERT into Works(ENO,CNO,Salary) VALUES('E7','C3','650');
INSERT into Works(ENO,CNO,Salary) VALUES('E8','C2','4200');
INSERT into Works(ENO,CNO,Salary) VALUES('E8','C7','21');
(2）检索超过35岁的男职工的工号和姓名。
use emp1
GO
select ENO,Ename from EMP where sex = '男' AND age>35;
(3）假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工的工号姓名。
use emp1
GO
select EMP.ENO,EMP.ENAME from EMP,WORKS
where EMP.ENO = WORKS.E AND EMP.SEX = '男' AND WORKS.SALARY>1000;
(4）假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工的工号和姓名。
use emp1
GO
select ENO,Ename from EMP where ENO in
(select DISTINCT E from WORKS where C = 'C4' OR C = 'C8');
(5）检索在“联华公司”工作，工资超过1000元的男性职工的工号和姓名。
use emp1
GO
select EMP.ENO,EMP.Ename from EMP,WORKS,COMP
where EMP.ENO = WORKS.E AND COMP.CNO = WORKS.C AND
EMP.SEX = '男' AND WORKS.SALARY>1000 AND COMP.CNAME='联华公司';
(6）假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（ ENO,NUМ,SUМ_ SALARY )，分别表示工号、公司数目和工资总数。
select ENO,COUNT(CNO) NUM,Sum(salary) SUM_SALARY
from WORKS
GROUP BY ENO
ORDER BY ENO;
(7）工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。
select DISTINCT ENO from WORKS where CNO
in (select CNO from WORKS where ENO = 'E6')
ORDER BY ENO;
(8）检索华联公司中低于本公可平均工资的职工的工号和姓名。
select WORKS.ENO,EMP.ENAME from WORKS,EMP,
(select CNO,avg(salary) avg_SALARY
from WORKS
where CNO in(select CNO from COMP where cname = '联华公司')
GROUP BY CNO)tb
where WORKS.CNO=TB.CNO AND EMP.ENO = WORKS.ENO AND WORKS.SALARY<TB.AVG_SALARY;
在这里我们创建表的时候没有给出华联公司的信息，用 “教令院”替代
select WORKS.ENO,EMP.ENAME from WORKS,EMP,
(select CNO,avg(salary) avg_SALARY
from WORKS
where CNO in(select CNO from COMP where cname = '教令院')
GROUP BY CNO)tb
where WORKS.CNO=TB.CNO AND EMP.ENO = WORKS.ENO AND WORKS.SALARY<TB.AVG_SALARY;
(9）在每一公司中为60岁以上职工加薪100元（若职工为多个公司工作，可重复加）。
update WORKS set SALARY = SALARY+100
where ENO in
(select ENO from EMP where EMP.age>60);
(10)在 EMP 表和WORKS表中删除年龄大于60岁的职工有关元组。
delete from WORKS where ENO in
(select ENO from EMP where age>60);
delete from EMP where age>60;
扫一扫
1470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
