【PTA】【数据库】【SQL命令】编程题2

数据库SQL命令测试题2

---

10-1 查询“李琳”老师所授课程的课程名称

作者 邵煜

单位 宁波财经学院

本题目要求编写SQL语句，检索出teachers、teaching、course表中“李琳”老师所授课程的课程名称。

提示：请使用join连接查询语句作答。

表结构:

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

CREATE TABLE teachers (tno char(3) ,tname char(8),ps char(10),tbirthday date ,tdept char(16) ,tsex char(2),PRIMARY KEY (tno)
) ;CREATE TABLE teaching (sid int ,cterm int,class char(10) DEFAULT NULL,cno char(7) NOT NULL,tno char(3) DEFAULT NULL,period int DEFAULT NULL,PRIMARY KEY (sid)
) ;CREATE TABLE course (cno char(7) ,cname varchar(20) NOT NULL,cpno char(7),ccredit int NOT NULL,PRIMARY KEY (cno)
) ;

表样例

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

teachers表：

tno	tname	ps	tbirthday	tdept	tsex
001	谭浩强	教授	1958-01-01	计科	男
002	王珊	教授	1962-02-13	计科	女
003	萨师煊	教授	1953-05-01	计科	男
004	严蔚敏	副教授	1968-07-02	软工	女
005	李琳	讲师	1988-11-15	软工	女
006	韩万江	助教	1992-10-17	信管	男

teaching表：

sid	cterm	class	cno	tno	period
1	1	17 物流 1	0000011	001	36
2	1	17 物流 1	0000034	002	72
3	3	17 物流 1	0000052	003	60
4	1	17 物流 1	0000027	004	108
5	2	17 物流 1	0000039	005	36
6	6	17 物流 1	0000005	006	72

course表：

cno	cname	cpno	ccredit
0000001	数据库 OCP 考证	NULL	4
0000002	C 语言基础	0000027	9
0000003	Linux 操作系统	0000013	5
0000010	数据结构	0000002	4
0000039	基础会计	NULL	2

输出样例:

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

cname
基础会计

提交代码：

select c.cname
from course c
join teaching t1 on c.cno = t1.cno
join teachers t2 on t1.tno = t2.tno
where t2.tname = "李琳";

---

10-2 查询成绩比所有课程的平均成绩高的学生的学号及成绩

作者 邵煜

单位 宁波财经学院

本题目要求编写SQL语句，检索出sc表中，课程成绩比所有课程的平均成绩高的学生的学号及成绩。

提示：请使用嵌套查询语句作答。

表结构:

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

CREATE TABLE sc (sno char(7) ,cno char(7) ,score decimal(4,1),point decimal(2,1),PRIMARY KEY (sno,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

输出样例:

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

sno	score
1311104	80.0
1311105	84.0

提交代码：

select sno,score
from sc
where score > (select avg(score)from sc
);

---

10-3 创建带表达式的视图StuView

作者 李翔坤

单位 大连东软信息学院

已知学生表Student，创建学生信息的视图StuView，包括学生学号、姓名和年龄，在视图中的列名分别为No，Name和Age。

Student表结构:

create table  Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '软件工程');

Student 表 数据样例：

Student表：

sno	sname	gender	birthdate	major
21012101	李勇	男	2005-10-20	计算机科学
21012102	刘晨	男	2006-5-5	计算机科学
21012103	王晓敏	女	2005-10-6	计算机科学
21021101	李佳睿	男	2006-3-30	软件工程
21021102	吴宾	男	2005-9-21	软件工程
21021103	张海	男	2005-10-20	软件工程
21031101	钱晓萍	女	2006-6-1	网络工程
21031102	王大力	男	2005-11-15	网络工程
21041101	于洋	男	2006-3-15	数据科学
21041102	郭霖	男	2006-3-2	数据科学

输出样例:

StuView视图：

提交代码：

create view StuView(No,Name,Age)
as select sno,sname,YEAR(CURRENT_DATE) - YEAR(birthdate)
from Student;

---

10-4 从视图PerView中查询数据

作者 李翔坤

单位 大连东软信息学院

从上题中创建的视图PerView中查询平均成绩超过75分的专业有哪些。

PerView视图结构:

 Create view PerView(专业名, 修课人数, 平均成绩)
AS Select major, count(distinct sc.sno), avg(grade) from student join sc on student.sno=sc.sno group by major;

PerView视图数据样例

PerView视图：

专业名	修课人数	平均成绩
数据科学	1	72.0
网络工程	1	87.5
计算机科学	2	80.0
软件工程	2	73.4

输出样例:

专业名	平均成绩
网络工程	87.5
计算机科学	80.0

提价代码：

select 专业名,平均成绩
from PerView
where 平均成绩 > 75;

---

10-5 查询工资高于在“HR”部门工作的所有员工的工资的员工信息

作者 李翔坤

单位 大连东软信息学院

查询工资高于在“HR”部门工作的所有员工的工资的员工信息

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

表结构:

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

CREATE TABLE employees (  employee_id INT PRIMARY KEY,  first_name VARCHAR(50),  last_name VARCHAR(50),  department VARCHAR(50),  salary INT  
);

表样例

employees表：

输出样例:

提交代码：

select *
from employees 
where salary > (select max(salary)from employeeswhere department = "HR"
);

---

10-6 查询选修的各课程平均成绩高于85分的学生学号、姓名和专业

作者 李翔坤

单位 大连东软信息学院

查询选修的各课程平均成绩高于85分的学生学号、姓名和专业

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

表结构:

create table if not exists Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '软件工程');
create table if not exists SC(    -- 选课成绩单表scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1),     -- 学生得到的课程绩点memo  text(100)    --  备注);

表样例

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

Student表：

SC表：

输出样例:

提交代码：

SELECT s.sno, s.sname, s.major
FROM Student s
JOIN (SELECT sno, AVG(grade) AS avg_gradeFROM SCGROUP BY snoHAVING AVG(grade) > 85
) AS high_avg ON s.sno = high_avg.sno;

---

10-7 查询修课平均成绩最高的学生学号

作者 李翔坤

单位 大连东软信息学院

查询修课平均成绩最高的学生学号

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

表结构:

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

create table if not exists Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '软件工程');select * from student;
create table if not exists Course(cno char(10) primary key,cname varchar(20) not null,ccredit int check(ccredit>0), -- 课程学分semester int check(semester>0),  -- 学期period int  check(period>0)     -- 总学时);select * from course;
create table if not exists Teacher(Tno char(15) primary key,Tname varchar(10) not null,gender char(2),deptname varchar(50) ,  -- 所属系部title varchar(20)       -- 职称
);
create table if not exists SC(    -- 选课成绩单表scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1),     -- 学生得到的课程绩点memo  text(100)    --  备注);

表样例

Student表：

Course表：

Teacher表：

SC表：

输出样例:

提交代码：

select sno
from SC
GROUP BY sno
HAVING AVG(grade) = (SELECT MAX(avg_grade)FROM (SELECT AVG(grade) AS avg_gradeFROM SCGROUP BY sno) AS avg_grades
);

---

10-8 SQL除法查询1

作者 沈炜

单位 浙江理工大学

本题目要求编写SQL语句，
检索出movies表中拍摄了所有Fox公司拍摄的所有电影类型的电影公司。

提示：本题意思就是找这样的电影公司，只要是Fox拍了某个电影类型的电影，那么这个公司也拍过这样类型的电影。

表结构:

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

CREATE TABLE movies (title char(100) NOT NULL DEFAULT '',year int(11) NOT NULL DEFAULT '0',length int(11) DEFAULT NULL,movieType char(10) DEFAULT NULL,studioName char(30) DEFAULT NULL,producerC int(11) DEFAULT NULL,PRIMARY KEY (title,year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表样例

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

movies表：

title	year	length	movieType	studioName	producerC
Empire Strikes Back	1980	111	drama	Fox	555
Gone With the Wind	1938	238	drama	MGM	123
Logan’s run	1977	120	drama	MGM	888
Pretty Woman	1990	119	drama	Disney	999
Star Trek	1979	132	sciFic	Paramount	444
Star Trek: Nemesis	2002	116	sciFic	Paramount	321
Star Wars	1977	124	sciFic	Fox	555
Star Wars	2015		sciFic	FOX
Star Wars	2017		sciFic
Terms of Endearment	1983	132	drama	MGM	123
The Man Who Wasn’t There	2001	116	comedy	USA Entertainm.	777
The Usual Suspects	1995	106	drama	MGM	999

输出样例:

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

studioName
Fox
MGM

注意：返回的公司名称必须唯一。

提交代码：

SELECT DISTINCT studioName
FROM movies m1
WHERE NOT EXISTS (SELECT movieTypeFROM moviesWHERE studioName = 'Fox'GROUP BY movieTypeHAVING NOT EXISTS (SELECT *FROM movies m2WHERE m2.studioName = m1.studioNameAND m2.movieType = movies.movieType)
);

---

10-9 SQL除法查询2

作者 沈炜

单位 浙江理工大学

查询出演了演员Carrie Fisher出演的所有电影的演员

提示：满足题目条件的演员，应该是只要Carrie Fisher出演了一本电影，这个演员也演了；不同年份，名称相同的电影认为是同一本电影。

表结构:

CREATE TABLE starsin (movieTitle char(100) NOT NULL DEFAULT '',movieYear int(11) NOT NULL DEFAULT '0',starName char(30) NOT NULL DEFAULT '',PRIMARY KEY (movieTitle,movieYear,starName)
);

表样例

starsin表：

movieTitle	movieYear	starName
Empire Strikes Back	1980	Harrison Ford
Star Wars	1977	Carrie Fisher
Star Wars	1977	Harrison Ford
Star Wars	1977	Mark Hamill
Star Wars	2017	Carrie Fisher
Terms of Endearment	1983	Debra Winger
Terms of Endearment	1983	Jack Nicholson
The Usual Suspects	1995	Kevin Spacey

输出样例:

starName
Harrison Ford
Carrie Fisher
Mark Hamill

提交代码：

select distinct(starName)
from starsin s1
where not exists(select *from starsin s2where s2.starName = "Carrie Fisher" and not exists(select *from starsin s3where s3.starName = s1.starName and s3.movieTitle = s2.movieTitle)
);

---

10-10 创建分组统计视图

作者 李翔坤

单位 大连东软信息学院

创建每个专业学生修课信息的视图PerView，包括每个专业的专业名称、修课的学生人数、平均成绩。

表结构:

create table Student(sno char(8)  primary key,sname varchar(10) not null,gender char(2) check(gender='男' or gender='女'),birthdate  date,major varchar(20)  default '软件工程');create table SC(  scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1),   memo  text(100)  );

表样例

Student表：

sno	sname	gender	birthdate	major
21012101	李勇	男	2005-10-20	计算机科学
21012102	刘晨	男	2006-5-5	计算机科学
21012103	王晓敏	女	2005-10-6	计算机科学
21021101	李佳睿	男	2006-3-30	软件工程
21021102	吴宾	男	2005-9-21	软件工程
21021103	张海	男	2005-10-20	软件工程
21031101	钱晓萍	女	2006-6-1	网络工程
21031102	王大力	男	2005-11-15	网络工程
21041101	于洋	男	2006-3-15	数据科学
21041102	郭霖	男	2006-3-2	数据科学

SC表：

scid	sno	cno	tno	grade	gpoint	memo
null	21012101	c01	t200306m12132	90	null	null
null	21012101	c02	t200703m12218	86	null	null
null	21012101	c03	t200703m12218	null	null	缺考
null	21012102	c02	t200703m12218	78	null	null
null	21012102	c03	t200703m12218	66	null	null
null	21021102	c01	t200306m12132	82	null	null
null	21021102	c02	t200608f12205	75	null	null
null	21021102	c03	t200306m12132	null	null	缓考
null	21021102	c05	t201803f12405	50	null	null
null	21021103	c02	t200703m12218	68	null	null
null	21021103	c04	t201208m12308	92	null	null
null	21031101	c01	t200306m12132	80	null	null
null	21031101	c02	t200608f12205	95	null	null
null	21041102	c02	t200608f12205	56	null	null
null	21041102	c05	t201803f12405	88	null	null

输出样例:

PerView视图：

提交代码：

CREATE VIEW PerView AS
SELECT s.major AS 专业名,COUNT(DISTINCT s.sno) AS 修课人数,AVG(sc.grade) AS 平均成绩
FROM Student s
JOIN SC sc ON s.sno = sc.sno
GROUP BY s.major;

---

10-11 查询教授多门课程的教师编号及教授的课程门数

作者 马丰媛

单位 大连东软信息学院

题目描述：查询教授多门课程的教师编号及教授的课程门数。

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

表结构:

SC表结构：

create table  SC( scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1), memo  text(100) );

表样例

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

SC表：

输出样例:

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

提交代码：

SELECT tno AS tno,COUNT(DISTINCT cno) AS 门数
FROM SC
GROUP BY tno
HAVING COUNT(DISTINCT cno) > 1;

---

10-12 查询选修了3门及3门以上课程的学生学号、平均成绩和修课门数，并用中文显示列名

作者 马丰媛

单位 大连东软信息学院

题目描述：查询选修了3门及3门以上课程的学生学号、平均成绩和修课门数，并用中文显示列名。

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

表结构:

SC表结构的SQL语句：

create table  SC( scid  int auto_increment  primary key,sno char(8)   references Student(sno),cno char(10)  references Course(cno),tno char(15)  references Teacher(tno),grade int check(grade>=0 and grade<=100),gpoint  decimal(2,1), memo  text(100) );

表样例

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

SC表：

输出样例:

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

select sno as "学号",avg(grade) as "平均成绩",count(cno) as "修课门数"
from SC
group by sno
having count(cno) >= 3;

---

本文作者： 鸿·蒙

撰写工具： Typora

内容反馈： 若发现本文内容有误或有任何意见，欢迎向作者鸿·蒙反馈或评论区留言。

版权声明： 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 鸿·蒙 ！