数据库SQL命令测试题2
测试题目录
- 10-1 查询“李琳”老师所授课程的课程名称
- 10-2 查询成绩比所有课程的平均成绩高的学生的学号及成绩
- 10-3 创建带表达式的视图StuView
- 10-4 从视图PerView中查询数据
- 10-5 查询工资高于在“HR”部门工作的所有员工的工资的员工信息
- 10-6 查询选修的各课程平均成绩高于85分的学生学号、姓名和专业
- 10-7 查询修课平均成绩最高的学生学号
- 10-8 SQL除法查询1
- 10-9 SQL除法查询2
- 10-10 创建分组统计视图
- 10-11 查询教授多门课程的教师编号及教授的课程门数
- 10-12 查询选修了3门及3门以上课程的学生学号、平均成绩和修课门数，并用中文显示列名
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 许可协议。转载请注明来自 鸿·蒙 ！