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

发表于 分类于 ， ， Waline： 本文字数： 10k 阅读时长 ≈ 9 分钟

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#

在前面已经学习了如何使用各种逻辑运算符，如：AND，OR，LIKE，BETWEEN，IN和EXISTS。 这些运算符可帮助您在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;