2022级数据库原理能力测试补测3

最新推荐文章于 2024-09-14 19:55:29 发布

H0r1zon.

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量476

点赞数 1

文章标签：数据库

本文链接：https://blog.csdn.net/H0r1zon/article/details/134488069

版权

10-1 查询所有选了datasad的学生的编号

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

表结构:

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

CREATE TABLE `students` (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`sid`)
);
CREATE TABLE `teachers` (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  `salary` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
  `no` char(10) CHARACTER SET utf8mb4 NOT NULL,
  `sid` bigint(20) DEFAULT NULL,
  `tid` bigint(20) DEFAULT NULL,
  `cid` bigint(20) DEFAULT NULL,
  `sorce` int(11) DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
  KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
  KEY `FK_CHOICES_COURESE_idx` (`cid`),
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

students表：

courses表：

teachers表：

choices表：(此处只显示部分)

输出样例:

这里有个特别偷懒的写法，就是直接从choices表中找到datasad的cid，然后懂的都懂

select sid
from choices 
where cid = '1005'

换种标准写法：

联立表或者子查询，这里我写的子查询：

select sid
from choices
where cid = (
    select cid
    from courses
    where cname = 'datasad'
)

10-2 求出至少被两名学生选修的课程编号

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

表结构:

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

CREATE TABLE `students` (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`sid`)
);
CREATE TABLE `teachers` (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  `salary` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
  `no` char(10) CHARACTER SET utf8mb4 NOT NULL,
  `sid` bigint(20) DEFAULT NULL,
  `tid` bigint(20) DEFAULT NULL,
  `cid` bigint(20) DEFAULT NULL,
  `sorce` int(11) DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
  KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
  KEY `FK_CHOICES_COURESE_idx` (`cid`),
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

choices表：

输出样例:

这题写法比较简单，就一张表，分组就行了，条件表达式用having不用where

select cid
from choices
group by cid
having count(sid) >= 2

10-3 查询学生的基本信息及其选修课程编号和成绩

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

表结构:

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

CREATE TABLE `teachers`  (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses`  (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students`  (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices`  (
  `no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sid` bigint(20) NULL DEFAULT NULL,
  `tid` bigint(20) NULL DEFAULT NULL,
  `cid` bigint(20) NULL DEFAULT NULL,
  `sorce` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE,
  INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
  INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
  INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

students表：

teachers表：

courses表：

choices表：

输出样例:

O5T9AV04VM{64A@64$QEJ12.png

这题感觉也挺简单的，就是联立两张表按要求输出就行

select s.sid,sname,email,grade,cid,sorce
from students s
join choices cs on cs.sid = s.sid

10-4 查询学号800009026的学生的姓名和选修课程名称和成绩

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

表结构:

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

CREATE TABLE `teachers`  (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses`  (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students`  (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices`  (
  `no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sid` bigint(20) NULL DEFAULT NULL,
  `tid` bigint(20) NULL DEFAULT NULL,
  `cid` bigint(20) NULL DEFAULT NULL,
  `sorce` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE,
  INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
  INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
  INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

students表：

teachers表：

courses表：

choices表：

输出样例:

sname	cname	sorce
吴庆	dfdata	58

这一题的话稍微麻烦一点，要联三张表，然后注意一下联的时候有顺序，要不然的话就没办法使用连接

select sname,cname,sorce
from students s
join choices cs on cs.sid = s.sid
join courses cr on cr.cid = cs.cid
where s.sid = '800009026'

10-5 查询与学号850955252的学生同年级的所有学生资料

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

表结构:

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

CREATE TABLE `teachers`  (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses`  (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students`  (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices`  (
  `no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sid` bigint(20) NULL DEFAULT NULL,
  `tid` bigint(20) NULL DEFAULT NULL,
  `cid` bigint(20) NULL DEFAULT NULL,
  `sorce` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE,
  INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
  INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
  INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

students表：

teachers表：

courses表：

choices表：

输出样例:

这题其实我也偷懒了，我直接找学号对应的年级，但是这样是不好滴，因为以后如果不显示这个数据的话就没办法使用这种情况了，所以我这次用子查询来做。

首先是子查询中查询该学号对应的年级，然后年级相同的就全部列出来就行。

select sid,sname,email,grade
from students
where grade = (
    select grade
    from students
    where sid = '850955252'
)

10-6 查询没有学生选的课程的编号

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

表结构:

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

CREATE TABLE `teachers`  (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses`  (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students`  (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices`  (
  `no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sid` bigint(20) NULL DEFAULT NULL,
  `tid` bigint(20) NULL DEFAULT NULL,
  `cid` bigint(20) NULL DEFAULT NULL,
  `sorce` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE,
  INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
  INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
  INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

students表：

teachers表：

courses表：

choices表：

输出样例:

这题的话要使用左连接，然后看看学号为null的值，就证明该课没人选。

select cr.cid 
from courses cr
left join choices cs on cr.cid = cs.cid
where cs.cid is null

10-7 查询选修了课程名为c++的学生学号和姓名
并按sid升序排序

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

表结构:

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

CREATE TABLE `teachers`  (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses`  (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students`  (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices`  (
  `no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sid` bigint(20) NULL DEFAULT NULL,
  `tid` bigint(20) NULL DEFAULT NULL,
  `cid` bigint(20) NULL DEFAULT NULL,
  `sorce` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE,
  INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
  INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
  INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

students表：

teachers表：

courses表：

choices表：

输出样例:

这题的话把三张表连接起来就好，然后按要求输出，最后要记得按照学号排个序。

select s.sid,sname
from students s
join choices cs on cs.sid = s.sid
join courses cr on cr.cid = cs.cid
where cr.cname = 'c++'
order by s.sid asc

10-8 查询所有有选课的学生的详细信息

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

表结构:

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

CREATE TABLE `teachers`  (
  `tid` bigint(20) NOT NULL,
  `tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses`  (
  `cid` bigint(20) NOT NULL,
  `cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students`  (
  `sid` bigint(20) NOT NULL,
  `sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices`  (
  `no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sid` bigint(20) NULL DEFAULT NULL,
  `tid` bigint(20) NULL DEFAULT NULL,
  `cid` bigint(20) NULL DEFAULT NULL,
  `sorce` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`no`) USING BTREE,
  INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
  INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
  INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
  CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表样例

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

students表：

teachers表：

courses表：

choices表：

输出样例:

这题是我想的太复杂了导致一直没写出来，其实只需要联立两张表，学生表和选课表，因为如果没选课的话就不会把数据选上，但是有一点要注意的是一个学生可以有多个选课。

select distinct s.sid,s.sname,s.email,s.grade
from students s
join choices cs on cs.sid = s.sid

10-9 查找来自国家（Country）为Japan、USA各个供应商名称及其供应的产品，显示为：供应商编号(SupplierID)，公司名称（CompanyName），产品编号（ProductID）和产品名称（ProductName）

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

表结构:

products表

列名	数据类型	长度	主码	说明
ProductID	int	11	√	产品编号
ProductName	varchar	40		产品名称
SupplierID	int	11		供应商编号
CategoryID	int	11		种类编号
QuantityPerUnit	varchar	20		数量
UnitPrice	decimal	10,4		单价
UnitsInStock	smallint	2		库存数量
UnitsOnOrder	smallint	2		订购数量
ReorderLevel	smallint	2		再次订购量
Discontinued	bit	1		中止

suppliers表

列名	数据类型	长度	主码	说明
SupplierID	int	11	√	供应商编号
CompanyName	varchar	40		公司名称
ContactName	varchar	30		联系人
ContactTitle	varchar	30		职务
Address	varchar	60		地址
City	varchar	15		城市
Region	varchar	15		区域
PostalCode	varchar	10		邮政编码
Country	varchar	15		国家
Phone	varchar	24		电话
Fax	varchar	24		传真
HomePage	mediumtext,			主页

表样例

products表：

ProductID	ProductName	SupplierID	CategoryID	QuantityPerUnit	UnitPrice	UnitsInStock	UnitsOnOrder	ReorderLevel
1	Chai	1	1	10 boxes x 20 bags	18	39	0	10
2	Chang	1	1	24 - 12 oz bottles	19	17	40	25
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10	13	70	25
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22	53	0	0
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35	0	0	0
6	Grandma's Boysenberry Spread	3	2	12 - 8 oz jars	25	120	0	25
7	Uncle Bob's Organic Dried Pears	3	7	12 - 1 lb pkgs.	30	15	0	10
8	Northwoods Cranberry Sauce	3	2	12 - 12 oz jars	40	6	0	0
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97	29	0	0
10	Ikura	4	8	12 - 200 ml jars	31	31	0	0

suppliers表：

SupplierID	CompanyName	ContactName	ContactTitle	Address	City	Region	PostalCode	Country	Phone	Fax	HomePage
1	Exotic Liquids	Charlotte Cooper	Purchasing Manager	49 Gilbert St.	London		EC1 4SD	UK	(171) 555-2222
2	New Orleans Cajun Delights	Shelley Burke	Order Administrator	P.O. Box 78934	New Orleans	LA	70117	USA	(100) 555-4822		#CAJUN.HTM#
3	Grandma Kelly's Homestead	Regina Murphy	Sales Representative	707 Oxford Rd.	Ann Arbor	MI	48104	USA	(313) 555-5735	(313) 555-3349
4	Tokyo Traders	Yoshi Nagase	Marketing Manager	9-8 SekimaiMusashino-shi	Tokyo		100	Japan	(03) 3555-5011
5	Cooperativa de Quesos 'Las Cabras'	Antonio del Valle Saavedra	Export Administrator	Calle del Rosal 4	Oviedo	Asturias	33007	Spain	(98) 598 76 54
6	Mayumi's	Mayumi Ohno	Marketing Representative	92 Setsuko Chuo-ku	Osaka		545	Japan	(06) 431-7877		Mayumi's (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/mayumi.htm#
7	Pavlova, Ltd.	Ian Devling	Marketing Manager	74 Rose St. Moonie Ponds	Melbourne	Victoria	3058	Australia	(03) 444-2343	(03) 444-6588
8	Specialty Biscuits, Ltd.	Peter Wilson	Sales Representative	29 King's Way	Manchester		M14 GSD	UK	(161) 555-4448
9	PB Knckebrd AB	Lars Peterson	Sales Agent	Kaloadagatan 13	Gteborg		S-345 67	Sweden	031-987 65 43	031-987 65 91
10	Refrescos Americanas LTDA	Carlos Diaz	Marketing Manager	Av. das Americanas 12.890	So Paulo		5442	Brazil	(11) 555 4640

输出样例:

ProductID	ProductName	SupplierID	CompanyName
4	Chef Anton's Cajun Seasoning	2	New Orleans Cajun Delights
5	Chef Anton's Gumbo Mix	2	New Orleans Cajun Delights
8	Northwoods Cranberry Sauce	3	Grandma Kelly's Homestead
7	Uncle Bob's Organic Dried Pears	3	Grandma Kelly's Homestead
6	Grandma's Boysenberry Spread	3	Grandma Kelly's Homestead
10	Ikura	4	Tokyo Traders
9	Mishi Kobe Niku	4	Tokyo Traders

这一题目除了表看上去累人一点难度不高：

select ps.ProductID,ps.ProductName,ss.SupplierID,ss.CompanyName
from products ps
join suppliers ss on ss.SupplierID = ps.SupplierID
where ss.Country = 'Japan' or ss.Country = 'USA'

10-10 查询每位领导的直接下属数量，显示：领导的员工编号（EmployeeID），下属数量（重命名为（countSub））

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

表结构:

列名	数据类型	长度	主码	说明
EmployeeID	int	11	√	员工编号
LastName	varchar	20		姓
FirstName	varchar	10		名
Title	varchar	30		职务
TitleOfCourtesy	varchar	25		礼貌称号
BirthDate	datetime,			出生日期
HireDate	datetime,			入职日期
Address	varchar	60		地址
City	varchar	15		城市
Region	varchar	15		区域
PostalCode	varchar	10		邮政编码
Country	varchar	15		国家
HomePhone	varchar	24		住宅电话
Extension	varchar	4		范围
Photo	longblob,			照片
ReportsTo	int	11		直接上级
PhotoPath	varchar	255		照片

表样例

employees表：

EmployeeID	LastName	FirstName	Title	TitleOfCourtesy	BirthDate	HireDate	Address	City	Region	PostalCode	Country	HomePhone	Extension	ReportsTo
1	Davolio	Nancy	Sales Representative	Ms.	1948/12/8 00:00:00	1992/5/1 00:00:00	507 - 20th Ave. E.Apt. 2A	Seattle	WA	98122	USA	(206) 555-9857	5467	2
2	Fuller	Andrew	Vice President, Sales	Dr.	1952/2/19 00:00:00	1992/8/14 00:00:00	908 W. Capital Way	Tacoma	WA	98401	USA	(206) 555-9482	3457
3	Leverling	Janet	Sales Representative	Ms.	1963/8/30 00:00:00	1992/4/1 00:00:00	722 Moss Bay Blvd.	Kirkland	WA	98033	USA	(206) 555-3412	3355	2

输出样例:

EmployeeID	countSub
2	2

刚刚写出这一题后发现这题其实挺简单的，测试的时候写不出纯粹是一直是count（Title）as countSub，所以注意审题！！！！！！ReportsTo才是直接上级，最后别忘了要is not null不然会把null值也输出

select ReportsTo as EmployeeID,
count(*) as countSub
from employees
group by ReportsTo
having ReportsTo is not null

10-11 查找所有“大学语文”分数在80以上的学生的姓名，所在院系。

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

表结构:

定义表结构的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)

) ;

CREATE TABLE course (

cno varchar(6) NOT NULL,

cname varchar(20) ,

credit int(11) ,

attribute varchar(10) ,

PRIMARY KEY (cno)

) ;

CREATE TABLE score (

sno varchar(6) NOT NULL,

cno varchar(6) NOT NULL,

term varchar(15),

grade int(11),

PRIMARY KEY (sno,cno)

) ;

表样例

student表：

course表：

score表：

输出样例:

又是水题，连表就行

select s.sname,s.dept
from student s
join score sc on sc.sno = s.sno
join course cr on cr.cno = sc.cno
where cr.cname = '大学语文' and sc.grade >= 80

10-12 检索选修40008课程并且成绩在60到80之间的学生学号和选修的学期。

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

表结构:

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

CREATE TABLE score (

sno varchar(6) NOT NULL,

cno varchar(6) NOT NULL,

term varchar(15),

grade int(11),

PRIMARY KEY (sno,cno)

) ;

表样例

score表：

输出样例:

好的水题一道，这里熟悉一下between and用法就好

select sno,term
from score 
where cno = '40008' and grade between '60' and '80'

10-13 显示出所有选课学生的学号，课号以及提高1%的成绩，要求成绩四舍五入保留整数。

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

表结构:

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

CREATE TABLE score (

sno varchar(6) NOT NULL,

cno varchar(6) NOT NULL,

term varchar(15),

grade int(11),

PRIMARY KEY (sno,cno)

) ;

表样例（部分数据）

score表：

输出样例（部分数据）:

这一题熟悉一下round函数是四舍五入函数即可（前面测试的时候出现过）

select sno,cno,
round(grade + grade* 0.01) as grade
from score

10-14 本题目要求编写SQL语句，
查询每个订单的信息及员工姓名，供应商名称和产品名称。

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

表结构:

CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);
CREATE TABLE `orders` (
  `OrdNo` int(11),   --订单编号
  `Sid` varchar(10), --供应商编号
  `Eid` varchar(10), --职工编号
  `Pid` varchar(20), --商品编号
  `Price` decimal(10,2), --价格
  `QTY` int(11),     --订购数量
  `ordDate` date    --订单日期
);
CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight` decimal(10, 3)    --重量
);
CREATE TABLE `supplier` (
  `Sid` varchar(10),   --供应商编号
  `SName` varchar(50), --供应商名称
  `City` varchar(20)   --供应商地址
);

表样例

employee表：

Eid	EName	Wno	Salary
0010	张三	A01	3600
0011	刘勇	A01	2700
0012	张立	A01	8500
0021	刘靖	A02	2500
0022	王强	A02	5600
0023	李军		5000
0031	王林		3500

orders表：

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

product表：

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

supplier表：

Sid	SName	City
S01	东风机械厂	武汉
S02	天鹰紧固件厂	温州
S05	长城机电	杭州

输出样例:

OrdNo	Sid	Eid	Pid	Price	QTY	ordDate	EName	SName	PName
1	S01	0011	P01	11	23	2022-02-13	刘勇	东风机械厂	M6螺栓
2	S02	0012	P01	12	25	2022-02-14	张立	天鹰紧固件厂	M6螺栓
3	S02	0012	P03	60	55	2022-02-14	张立	天鹰紧固件厂	M16螺栓

这题主要是连表复杂了一点，三张表都要连上去

select os.OrdNo,os.Sid,os.Eid,os.Pid,os.Price,os.QTY,os.ordDate,ee.EName,sr.SName,pt.PName
from orders os
join employee ee on ee.Eid = os.Eid
join supplier sr on sr.Sid = os.Sid
join product pt on pt.Pid = os.Pid

10-15 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by。

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

表结构:

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));

表样例

employees表：

salaries表：

输出样例:

这题真的折磨死人，网上查题目也交不了，Chat也交不了，一开始的思路是找到最大值，父查找再找出不属于最大值的另一个最大值（也就是第二大的值），但是这样一直是答案错误，然后换了个思路，最底层（也就是最先运行的子查找）先查找最大值，第二层查找再查找排除掉最大值的第二个最大值，第三层循环就输出该值就行。

select ee.emp_no,ss.salary,ee.last_name,ee.first_name
from employees ee
join salaries ss on ss.emp_no = ee.emp_no
where to_date = '9999-01-01'
and salary = (
    select max(salary)
    from salaries 
    where to_date = '9999-01-01'
    and salary != (
        select max(salary)
        from salaries
    )
)

终于在睡觉前解决了这题，收工！