数据库实验2-2

10-1 将学号为“1911203”的学生的联系电话改为“590987”

本题目要求编写SQL语句，
在students表中，将学号为“1911203”的学生的联系电话改为“590987”。

提示：请使用UPDATE语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE students (
  sno char(7) ,
  sname char(8) NOT NULL,
  class char(10),
  ssex char(2),
  bday date ,
  bplace char(10) ,
  IDNum char(18) ,
  sdept char(16),
  phone char(11),
  PRIMARY KEY (sno)
) ;

表样例

请在这里给出上述表结构对应的表样例。例如

students表：

sno	sname	class	ssex	bday	bplace	IDNum	sdept	phone
1311104	李嘉欣	13英语1	女	1994-05-28	山西太原	330204199405281056	人文学院	15900002211
1311105	苏有明	13英语1	男	1995-04-16	内蒙古包头	330204199504162036	人文学院	15900002222
1711101	赵薇	17物流1	女	1999-02-11	安徽合肥	330203199902110925	经管学院	15900001177
1911201	陈坤	19信管2	男	1990-07-19	山东烟台	330204199007199604	信息学院	15911113388
1911203	张三	19物流1	女	1999-02-17	上海	330203199902170017	经管学院	15900001188

输出样例:

请在这里给出输出样例。例如：

students表：

sno	sname	class	ssex	bday	bplace	IDNum	sdept	phone
1311104	李嘉欣	13英语1	女	1994-05-28	山西太原	330204199405281056	人文学院	15900002211
1311105	苏有明	13英语1	男	1995-04-16	内蒙古包头	330204199504162036	人文学院	15900002222
1711101	赵薇	17物流1	女	1999-02-11	安徽合肥	330203199902110925	经管学院	15900001177
1911201	陈坤	19信管2	男	1990-07-19	山东烟台	330204199007199604	信息学院	15911113388
1911203	张三	19物流1	女	1999-02-17	上海	330203199902170017	经管学院	590987

update students
set phone='590987'
where sno=1911203

 10-2 把选修了“平板撑”课程而成绩不及格的学生的成绩全改为空值(NULL)

本题目要求编写SQL语句，
在``sc```表中，把选修了“平板撑”课程而成绩不及格的学生的成绩全改为空值(NULL)。

提示：请使用UPDATE语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE sc (
 sno char(7) ,
 cno char(7) ,
 score decimal(4,1),
 point decimal(2,1),
 PRIMARY KEY (sno,cno)
) ;

CREATE TABLE course (
 cno char(7) ,
 cname varchar(20) NOT NULL,
 cpno char(7),
 ccredit int NOT NULL,
 PRIMARY KEY (cno)
) ;

表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

sno	cno	score	point
1311104	0000011	53.0	0.0
1311104	0000027	80.0	1.0
1311105	0000027	84.0	1.0
1711101	0000052	71.0	2.0
1711101	0000011	51.0	0.0

course表：

cno	cname	cpno	ccredit
0000001	数据库OCP考证	NULL	4
0000002	C语言基础	0000027	9
0000003	Linux操作系统	0000013	5
0000004	C#程序设计	0000002	6
0000011	平板撑	NULL	2

输出样例:

请在这里给出输出样例。例如：

sc表：

sno	cno	score	point
1311104	0000011	53.0	0.0
1311104	0000027	80.0	1.0
1311105	0000027	84.0	1.0
1711101	0000052	71.0	2.0
1711101	0000011	NULL	0.0

update sc
set score=null
where cno in
(
    select cno
    from course
    where cname = '平板撑'
) and score <60

 10-3 80.理学院(CS)新开一门课程“数学建模”，课程编号20, 学分4，学时72，选修课程，最多选课人数为50

本题目要求编写SQL语句，理学院(CS)新开一门课程“数学建模”，无先行课，课程编号20, 学分4，学时72，选修课程，最多选课人数为50。

现有教务管理系统的关系描述如下：

• 每个院系（部门）有多个班级和多名教师，每名教师各自开设有多门课程。
• 每位教师管理多个班级（班主任），每个班级只能被一位老师管理。
• 每名学生属于一个班级，可以选修多门课程。
• 每门课程可被多位老师讲授，并且有些课程具有先导课程，每门课程的成绩由平时成绩、期中成绩、期末成绩组成，最终计算总评成绩。
• 课程信息表供教师和学生查询，包含课程信息、任课教师、上课班级、上课教室、上课日期（周几）、上课时间（第几节课）、上课学期学年等信息。

  1.课程表：course

  表结构

  

  表数据

  

insert into course
values('20',NULL,'CS','数学建模','4','72','选修','50');

 10-4 添加一条学生记录

本题目要求编写 Insert语句，在stu表中添加一条学生记录：

学号：S012，姓名：周强，性别：1，其它属性为NULL.

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

输出样例:

插入一条学生记录后，stu表如下：

insert into stu
values('S012','周强','1',null,null,null)

 10-5 修改女生成绩

本题目要求编写UPDATE语句，
把所有低于７５分的女生成绩提高5%;

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`)
);
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
)

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

sc表：

输出样例:

例如：
修改女生成绩后的sc表如下：

update sc
set grade=grade*1.05
where grade<75 and sno in
(
    select sno 
    from stu
    where sex=0
)

 

10-6 删除学生所有信息 

本题目要求编写DELETE语句，
删除学生姓名为'周强'的所有数据信息。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`)
);
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

sc表：

输出样例:

删除学生周强的所有数据信息后：

stu表：

sc表：

delete 
from sc
where sno in
(
    select sno
    from stu 
    where sname='周强'
);

delete 
from stu
where sname ='周强'

10-7 将student表中的数计学院的学生信息插入到stu表中。 

 

题干:

将student表中的数计学院的学生信息插入到stu表中。

提示：请使用INSERT语句作答，stu数据表已存在，结构同student一样。

表结构:

定义表结构的SQL语句如下：

CREATE TABLE student (

sno varchar(6) NOT NULL ,

sname varchar(10) ,

sex char(2) ,

nation char(2) ,

pnum char(18) ,

birth date ,

phone char(11) ,

dept varchar(20) ,

PRIMARY KEY (sno)

) ;

表样例

上述表结构对应的表样例：

student表：

输出样例:

输出样例：

stu表：

insert
into stu
select *
from student
where dept='数计学院'

 10-8 删除没有销售过的产品

本题目要求编写SQL语句，
删除没有销售过的产品。

表结构:

CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight` decimal(10, 3)    --重量
);
CREATE TABLE `orders` (
  `OrdNo` int(11),   --订单编号
  `Sid` varchar(10), --供应商编号
  `Eid` varchar(10), --职工编号
  `Pid` varchar(20), --商品编号
  `Price` decimal(10,2), --价格
  `QTY` int(11),     --订购数量
  `ordDate` date    --订单日期
);

表样例

product表：

Pid	PName	Weight
P01	M6螺栓	30
P02	M8螺栓	40
P03	M16螺栓	140
P04	螺帽	30
P05	螺母	45
P06	垫片	10
P07	铰链	70

orders表：

OrdNo	Sid	Eid	Pid	Price	QTY	ordDate
1	S01	0011	P01	11	23	2022-02-13
2	S02	0012	P05	12	25	2022-02-14
3	S03	0012	P03	60	55	2022-02-14

输出样例:

Pid	PName	Weight
P01	M6螺栓	30
P03	M16螺栓	140
P05	螺母	45

DELETE p  
FROM product p  
LEFT JOIN orders o ON p.Pid = o.Pid  
WHERE o.Pid IS NULL;