2021年07月25日 数据库习题1

SQL专项训练题(一)

(一)数据源情况:以下各题操作表结构如下:

Student(学生表)

字段名 字段类型 说明
sno 文本型 学号,不能存在相同的 主键
sname 文本型 姓名,非空
sex 文本型 性别,只能“男”或“女”
dept 文本型 系别,包括这几个:信息系,计算机科学系,数学系,管理系,中文系,外语系,法学系
birth 日期/时间型 出生日期
age 数值型 年龄,在0~100之间

student(sno,sname,sex,dept,birth,age)

cs(成绩表):

字段名 字段类型 说明
sno 文本型 学号
cno 文本型 课程号
score 数值型 成绩, 只能在0~100之间,可以不输入值

cs(sno,cno,score)

course(课程表)

字段名 字段类型 说明
cno 文本型 课程号, 不能有重复的
cname 文本型 课程名,非空
cval 数值型 学分

course(cno,cname,cval)

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

(二)针对学生课程数据库查询

(1)查询全体学生的学号与姓名。

sno—student
sname—-student

1
2
select sno,sname
from student;

(2)查询全体学生的姓名、学号、所在系,并用别名显示出结果。

sname,sno,dept

1
2
select sname as name,sno as id,dept as 科系
from student;

(3)查询全体学生的详细记录。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
select * from student;

(4)查全体学生的姓名及其出生年份

1
2
select sname,birth
from student;

(5)查询学校中有哪些系。

1
select distinct(birth) from student;

(6)查询选修了课程的学生学号。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
select distinct(sno)
from cs;

(7)查询所有年龄在20岁以下的学生姓名及其年龄。

sno,age—–student

1
2
3
select sname,age
from student
where age<20;

(8)查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。

sname,dept,age—-student

1
2
3
select sanme,dept,age
from student
where age>=20 and age<=23;

(9)查询年龄不在20~23岁之间的学生姓名、系别和年龄。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
select sname,dept,age
from student
where age<20 or age>23;

(10)查询信息系、数学系和计算机科学系生的姓名和性别。

1
2
3
select sname,sex
from student
where dept='信息' or dept='数学' or dept='计算机科学';

(11)查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。

1
2
3
select sname,sex
from student
where dept!='信息' and dept!='数学' and dept!='计算机科学';

(12)查询所有姓刘学生的姓名、学号和性别。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
select sname,sno,sex
from student
where sname like '刘%';

SQL 通配符

在 SQL 中,通配符与 SQL LIKE 操作符一起使用。

SQL 通配符用于搜索表中的数据。

在 SQL 中,可使用以下通配符:

通配符 描述
% 替代0个或多个字符
_ 替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist][!charlist] 不在字符列中的任何单一字符

(13)查询学号为2009011的学生的详细情况。(具体的学号值根据表中数据确定)

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
selec * from student
where sno='2009011';

(14)查询姓“欧阳”且全名为三个汉字的学生姓名

1
2
select sname from student
where sname like '欧阳__';

(15)查询名字中第2个字为“晨”字的学生的姓名和学号

1
2
3
select sname,sno
from student
where sname like '__晨%';

一个汉字占两个字符

(16)查询所有不姓刘的学生姓名。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
select sname
from student
where sname not like '刘%';

SQL NOT运算符

//原文出自【易百教程】,商业转载请联系作者获得授权,非商业转载请保留原文链接:https://www.yiibai.com/sql/sql-not.html#

在前面已经学习了如何使用各种逻辑运算符,如:ANDORLIKEBETWEENINEXISTS。 这些运算符可帮助您在WHERE子句中形成灵活的条件。

要反转任何布尔表达式的结果,请使用NOT运算符。 以下演示如何使用NOT运算符。

1
NOT [Boolean_expression]

(17)查询sql课程的课程号和学分。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
select cno,cval
from course
where cname='sql';

(18)查询以”DB_”开头,且倒数第3个字符为 i的课程的详细情况

1
2
select * from course
where cname like 'DB\_%i__' escpe '\';

SQL like如何匹配%和_字符本身

https://www.hegongshan.com/2018/09/19/sql-like/

如果需要查询的匹配字符串本身就含有%和_,可以使用escape '<换码字符>'对通配符进行转义。

示例:

6.查询Algorithms_Design课程的课程号和学分

复制

1
2
3
select cno,credit
from course
where cname like 'Algorithms\_Design' escape '\';

7.查询课程名以“算法_”开头,且倒数第二个汉字为“设”的课程详情。

复制

1
2
3
select *
from course
where cname like '算法\_%设__' escape '\';

注意:换码字符是可以变化的,一般取不常用的符号。若匹配串中本身含有 “ \ ”,则换码字符可取 “ ? ”等。

(19)查询缺少成绩的学生的学号和相应的课程号。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

sno—student表,sc表
cno—cs表

1
2
3
select sno,cno
from cs
where score is null;
1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
4
select sno
from cs
group by sno
having count(*)>3;

(33)查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数。

sno,count(cno)—-cs表

1
2
3
4
select sno,count(cno) as c_num
from cs
group by sno
having count(*)>=3 and min(score)>=90;

(34)查询学生2006011选修课程的总学分。

1
2
3
4
select sum(score) as total_score
from cs
where sno='2006011'
group by sno;

(35)查询每个学生选修课程的总学分。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
select sno,sum(score) as total_score
from cs
group by sno;

(36)查询每个学生及其选修课程的情况。

1
2
3
select sno,sname,cno
from student as s,cs
where s.sno=cs.sno;
1
2
3
4
select sno,sname,cno,cname
from
student as s,cs,course as c
where s.sno=cs.sno and cs.cno=c.cno;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select sno,sname,cno,cname
from
(select sno,sname from student) as R1
inner join
(
select sno,cno,cname
from(
(select cno,cname from course) as C
inner join
(select sno,cno from cs) as cs2
on c.cno=cs2.cno
)
) as R2
on R1.sno=R2.sno;

(37)查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

sno ——-student表
sname —–student表

cno=’2’—-cs表
score>90—cs表

1
2
3
select s.sno,sname
from student as s,cs
where s.sno=cs.sno and cno='2' and score>=90;

子查询实现

1
2
3
4
5
6
7
8
9
select s.sno,sname
from
student as s,
(
select sno
from cs
where cno='2' and score>=90
) as R
where s.sno=R.sno;

子查询找出选项2号课程,并且成绩大于等于90的学生的学号
然后课程表和子查询得到临时表R进行连接
然后从连接表中取出学号和姓名即可

1
2
3
4
5
6
7
8
9
10
11
select s.sno,sname
from
(
select sno,sname from student
) as s,
(
select sno
from cs
where cno='2' and score>=90
) as R
where s.sno=R.sno;

(38)查询每个学生的学号、姓名、选修的课程名及成绩。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

sno,sname,—–student表
cname,—-course表
score—–cs表

1
2
3
select s.sno,sanme,cname,score
from student as s,cs,course as c
where s.sno=cs.sno and cs.cno=c.cno;
1
2
3
4
5
6
7
8
9
10
11
select sno,sname,cname,score
from
(
select sno,sname from student
) as R1,
(
select sno,cname,score
from cs,course as c
where cs.cno=s.cno
) as R2
where R1.sno=R2.sno;
1
2
3
4
5
6
select s1.sno,sname,c1.cname,score
from
(select sno,sname from student) as s1,
(select cno,cname from course) as c1,
cs
where s1.sno=cs.sno and cs.cno=c1.cno;

(39)查询与“刘晨”在同一个系学习的学号和姓名(分别用嵌套查询和连接查询)

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

sname=’刘晨’—student表
dept——-student表
sno—–student表
sname—-student表

1
2
3
4
5
6
select s1.sno,s1.sname
from
student as s1
inner join
student as s2
on s2.sname='刘晨' and s1.dept=s2.dept;

子查询实现

1
2
3
4
5
6
7
8
select sno,sname
from student as s
where dept in
(
select dept
from student as s
where s.sname='刘晨'
);

(40)查询选修了课程名为“信息系统”的学生学号和姓名

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
4
5
6
7
8
9
10
11
12
select sno,sname
from student
where sno in
(
select sno from
cs
where cno in
(
select cno from course
where cname='信息系统'
)
);

(41)查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
4
5
6
7
8
select sname,age
from student
where dept!='信息' and age < any
(
select age
from student
where dept='信息';
);

(42)查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。

1
2
3
4
5
6
7
select sname,age
from student
where dept!='信息' and age < all(
select age
from student
where dept='信息'
);
1
2
3
4
5
6
7
select sname,age
from student
where dept!='信息' and age < (
select min(age)
from student
where dept='信息'
);

(43)查询所有选修了1号课程的学生姓名。(分别用嵌套查询和连查询)

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

sanme,—student表
cno=’1’—-cs表

嵌套查询

1
2
3
4
5
6
select sname from student
where sno in
(
select sno from cs
where cno='1'
);

连接查询

1
2
3
select sname
from student as s,cs
where s.sno=cs.sno and cs.cno='1';

(44)查询没有选修1号课程的学生姓名。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

子查询

1
2
3
4
5
6
7
select sname from student
where sno in
(
select sno
from cs
where cno!='1'
);

先查询出没有选修1号课程的学生的学号,然后再根据学号选择出姓名

子查询和连接实现

1
2
3
4
5
select sname from
(select sno,sname from student) as R1
inner join
(select sno from cs where cno!='1') R2
on R1.sno=R2.sno;

(45)查询选修了全部课程的学生姓名。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

思路:查询一个学生的姓名,该学生选修课的门数等于所有课程的门数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select sname from student
where sno in
(
-- 查询出选修课程数量等于课程表中的课程数量相等的学生的学号
select sno from cs
-- 按照学号进行分组
group by sno
-- 统计分组中记录的数量,得到学生选修的课程数量
-- 当学生选修的课程数量等于课程表中的课程数量时,说明该学生选修了所有的课程
having count(*)=(
-- 统计课程表中的课程数量
select count(*) from course
)
);

查询选修了全部课程的学生姓名

https://www.pianshen.com/article/1491914644/
image-20210726165956168

第一种:

理解为:查询一个人的姓名,不存课程 该学生没选修
思路:
1.拿出Student表的第一行数据
2.拿出Course表的第一行数据
3.拿出SC表的第一行数据 (其实这个表就是用来与前两个表进行对比判断的)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*查询一个学生的学号 (在Student表中循环取学生信息)*/
SELECT Sname
FROM Student
/*不存在课程 (在Course中循环取所有课程信息)*/ 
WHERE NOT EXISTS(          
  SELECT *
  FROM Course
/*该学生没选修 (在SC表中循环比对) */
  WHERE NOT EXISTS(
    SELECT *
    FROM SC
    WHERE SC.Sno = Student.Sno AND SC.Cno = Course.Cno
  )
);

结果:选择出学生的姓名,这个学生所有课程都选修了

第二种:思路:

查询一个学生的姓名,该学生选修课的门数等于所有课程的门数

逆推:查询一个学生的姓名,姓名怎么来?
  • 通过Student的学号得到,学号怎么得到?
    • 通过在SC表中Sno分组判断该学生的选修的课程门数是否等于所有课程的门数得到,所有课程门数如何得到?
      • 通过在Course表中COUNT(*)得到
        SQL语句:
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        SELECT Sname
        FROM Student
        WHERE Sno in (
          SELECT Sno
          FROM SC
          GROUP BY Sno
        /*该学生的选修的课程门数 = 所有课程的门数*/
          HAVING COUNT(*) = (
        /*所有课程的门数*/
            SELECT COUNT(*)
            FROM Course
          )
        );

要求查询出 :选修了全部课程的学生姓名

https://www.cnblogs.com/losesea/archive/2012/10/10/2718093.html

例子: 三张表 学生表student(Sno,Sname), 课程表course(Cno,Cname) 选课表SC(Sno,Cno)

  • 首先,学生的选课信息存在于SC表中, 要想知道某个学生是否选修了全部课程,至少我们需要知道一共有几门课程,这是首要的条件。
  • 其次,学生选修了与否,我们又要扫描SC全表,统计出选修了所有课程的学生号,
  • 最后,在STUDENT表中根据学生号打出姓名 。
1
2
3
4
5
6
7
8
9
10
11
12
13
select Sname from student             
where Sno IN
(
select Sno from SC
-- 根据学号Sno分组,
group by Sno
-- 统计每个学生选修了几门课程。
-- 如果该学生选课数量,等于course的总数,则说明该学生选修了所有的课程,就是我们要找的Sno
having count(*) = (
-- 统计course中共有几门课程
select count(*) from course
)
);

(46)查询至少选修了学生95002选修的全部课程的学生号码。

需要使用相干子查询

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)

选出这样的学生X, 不存在这样一门课Y, 学生95002选了而学生X没有选

不存在这样一门课,学生95002选了,学生x没有选

select sno from cs
where not exists(
select cno
from cs as
where not exists(
select from cs where sno=’95002’ and ;
)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select distinct x.sno
-- 对于学生x
from sc as x
-- 不存在
where not exists(
select *
from sc as y
-- 这样一门课, 95002选了,x没有选
where y.sno = '95002' and not exists(
select *
from sc as z
-- 课程 y, x没选
where z.cno = y.cno and z.sno = x.sno
)
);
;

(47)查询选修了学生95002选修的课程完全一致的学生学号

(48)查询计算机科学系的学生及年龄不大于19岁的学生的信息。

(49)查询选修了课程1或者选修了课程2的学生的信息。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
4
5
6
7
select * from student
where sno in
(
select sno
from cs
where cno='1' or cno='2'
);

(50)查询计算机科学系中年龄不大于19岁的学生的信息。

1
2
select * from student
where dept='计算机' and age<=19;

(51)查询既选修了课程1又选修了课程2的学生的信息。

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
4
5
6
select * from student
where sno in(
select C1.sno
from cs as C1,cs as C2
where C1.sno=C2.sno and C1.cno='1' and C2.cno='2'
);

(52)通过查询求学号为1学生的总分和平均分。

1
2
3
4
select sum(score) as total_score,avg(score) as avg_score
from cs
group by sno
having sno='1';

(53)求出每个系的学生数量

1
2
3
student(sno,sname,sex,dept,birth,age)
course(cno,cname,cval)
cs(sno,cno,score)
1
2
3
select dept,count(sno) as student_nums
from student
group by dept;

(54)查询平均成绩大于85的学生学号及平均成绩。

1
2
3
4
select sno,avg(score)
from sc
group by sno
having avg(score)>85;

(55)要求查寻学生的所有信息,并且查询的信息按照年龄由高到低排序,如果年龄相等,则按照学号从低到高排序

1
2
select * from student
order by age desc,sno asc;