SQL练习

发表于 阅读次数： 本文字数： 3.4k 阅读时长 ≈ 12 分钟

已知某数据库中存在如下四张表：

1
2
3
4
department(dNo,dName,officeRoom,homepage)
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)   
sc(sNo,cNo,score,recordDate)

Query

单表

1.查询所有年龄大于等于20岁的学生学号、姓名； 

1
2
3
select sNo, sName 
from student 
where age >= 20;
2.查询所有姓钱的男生学号、姓名、出生年份； 
1
2
3
select sNo, sName, 2018 - age 
from student 
where sName like '钱%' and sex = '男';
3.查询所有学分大于3的课程名称； 
1
2
3
select cName 
from course
where credit > 3;
4.查询所有没有被分配到任何学院的学生姓名； 
1
2
3
select sName 
from student 
where dNo is null;
5.查询所有尚未设置主页的学院名称。 
1
2
3
select dName 
from department 
where homepage is null;

聚集

1.查询各个学院的平均年龄；

1
2
3
select dNo, avg(age) 
from student 
group by dNo;
1
2
3
select department.dNo, department.dName, avg(age) 
from student left join department on (student.dNo = department.dNo)
group by department.dNo, department.dName;
2.查询每个学生选修课程的平均分； 
1
2
3
4
select sNo, avg(score) 				
from sc 		
where score is not null				
group by sNo;						
1
2
3
4
select student.sNo, student.sName, avg(score)
from student, sc
where student.sNo = sc.sNo and sc.score is not null
group by student.sNo, student.sName;
3.查询各课程的平均分；
1
2
3
4
select cNo，avg(score) 			
from sc 
where score is not null						
group by cNo;					
1
2
3
4
select course.cNo, course.cName, avg(score)
from course, sc
where course.cNo = sc.cNo and sc.score is not null
group by course.cNo, course.cName;
4.查询各学院开设的课程门数； 
1
2
3
select dNo, count(cNo) 			
from course 					
group by dNo;					
1
2
3
select department.dNo, department.dName, count(course.cNo)
from department join course using(dNo)
group by department.dNo, department.dName;
5.查询各门课程选修人数。 
1
2
3
select cNo, count(sNo) 			
from sc 						
group by cNo;					
1
2
3
select course.cNo, course.cName, count(sc.sNo)
from sc join course using (cNo)
group by course.cNo, course.cName;

多表

1.查询“信息学院”所有学生学号与姓名； 

1
2
3
4
5
6
select sNo, sName
from student
where dNo in
    (select dNo
    from department
    where dName = '信息学院');
1
2
3
select sNo, sName
from student, department
where student.dNo = department.dNo and department.dName = '信息学院';
1
2
3
select student.sNo, student.sName
from student join department using (dNo)
where department.dName = '信息学院';
1
2
3
select student.sNo, student.sName
from student join department on (student.dNo = department.dNo)
where department.dName = '信息学院';
2.查询“软件学院”开设的所有课程号与课程名称； 
1
2
3
4
5
6
select cNo, cName
from course
where dNo in 
        (select dNo
        from department
        where dName = '软件学院');
3.查询与“陈丽”在同一个系的所有学生学号与姓名； 
1
2
3
4
5
6
select sNo, sName
from student
where dNo in
    (select dNo
    from student
    where sName = '陈丽');
4.查询与“张三”同岁的所有学生学号与姓名； 
1
2
3
4
5
6
select sNo, sName
from student
where age =
        (select age
        from student
        where sName = '张三');
5.查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名； 
1
2
3
4
5
6
7
8
9
10
select sNo, sName
from student
where age =
        (select age
        from student
        where sName = '张三')
    and dNo <>
        (select dNo
        from student
        where sName = '张三');
6.查询学分大于“离散数学”的所有课程名称； 
1
2
3
4
5
6
select cName
from course
where credit > 
        (select credit
        from course
        where cName = '离散数学');
7.查询选修了课程名为“组合数学”的学生人数； 
1
2
3
4
5
6
7
8
9
select count(sNo)
from student
where sNo in 
        (select sNo
        from sc
        where cNo in 
                    (select cNo
                    from course
                    where cName = '组合数学'));
8.查询没有选修“离散数学”的学生姓名； 
1
2
3
4
5
6
7
8
9
select sName
from student
where not exists
        (select *
        from sc
        where sNo = student.sNo and cNo = 
                                        (select cNo
                                        from course
                                        where cName = '离散数学'));
9.查询与“算法设计与分析”、“移动计算”学分不同的所有课程名称； 
1
2
3
4
5
6
7
8
9
10
select cName
from course as c1
where (c1.credit <> 
                (select credit 
                from course
                where cName = '算法设计与分析')
    and c1.credit <> 
            (select credit
            from course
            where cName = '移动计算'));
10.查询平均分大于等于90分的所有课程名称； 
1
2
3
4
5
6
7
8
select cName
from course
where cNo in
        (select cNo
        from sc
        where score is not null
        group by cNo
        having avg(score) >= 90);
11.查询选修了“离散数学”课程的所有学生姓名与成绩； 
1
2
3
4
5
6
select student.sName, sc.score											
from student, sc														
where student.sNo = sc.sNo and sc.cNo = 
                                        (select cNo
                                        from course
                                        where cName = '离散数学');
1
2
3
select student.sName, sc.score
from (student join sc using (sNo)) join course using (cNo)
where course.cName = '离散数学';
12.查询“王兵”所选修的所有课程名称及成绩； 
1
2
3
4
5
6
select course.cName, sc.score				
from course natural join sc					
where sc.sNo in 
                (select sNo
                from student
                where sName = '王兵');							
1
2
3
4
5
6
select course.cName, sc.score
from course join sc on course.cNo = sc.cNo
where sc.sNo in 
            (select sNo
            from student
            where sName = '王兵');
13.查询所有具有不及格课程的学生姓名、课程名与成绩； 
1
2
3
select student.sName, course.cName, sc.score
from student, course, sc 
where student.sNo = sc.sNo and course.cNo = sc.cNo and sc.score < 60;
14.查询选修了“文学院”开设课程的所有学生姓名； 
1
2
3
4
5
6
select s1.sName 
from student s1 
where s1.sNo in 
            (select distinct s2.sNo
            from student s2, sc, department, course
            where department.dName = '文学院' and course.dNo =department.dNo and course.cNo = sc.cNo and s2.sNo = sc.sNo);
15.查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称。 
1
2
3
select student.sName, course.cName
from student, course, department, sc
where department.dName = '信息学院' and student.dNo = department.dNo and course.dNo = department.dNo and sc.sNo = student.sNo and sc.cNo = course.cNo;
1
2
3
4
//包含信息学院中未选信息学院课程的学生
select student.sName, course.cName
from (student join department on (student.dNo = department.dNo)) left join (sc join course on (sc.cNo = course.cNo)) on (student.sNo = sc.sNo)
where department.dName = '信息学院' and (course.dNo = department.dNo or course.dNo is null );
## 综合

1.查询所有学生及其选课信息（包括没有选课的学生）； 

1
2
select student.*, sc.*
from student left outer join sc on (student.sNo = sc.sNo);
2.查询“形式语言与自动机”先修课的课程名称； 
1
2
3
4
5
6
select cName 
from course
where cNo in 
            (select cPNo
            from course
            where cName = '形式语言与自动机');
3.查询“形式语言与自动机”间接先修课课程名称； 
1
2
3
4
5
6
7
8
select cName
from course
where cNo in 
            (select cPNo
            from course
            where cNo in (select cPNo
                        from course
                        where cName = '形式语言与自动机'));
4.查询先修课为编译原理数学的课程名称； 
1
2
3
4
5
6
select cName
from course
where cPNo in 
            (select cNo
            from course
            where cName = '编译原理');
5.查询间接先修课为离散数学的课程名称； 
1
2
3
4
5
6
7
8
9
select cName
from course
where cPNo in 
            (select cNo 
            from course
            where cPNo in 
                        (select cNo
                        from course
                        where cName = '离散数学'));
6.查询所有没有先修课的课程名称； 
1
2
3
select cName 
from course
where cPNo is null;
7.查询所有没选修“形式语言与自动机”课程的学生姓名； 
1
2
3
4
5
6
7
8
9
select sName
from student
where not exists
        (select *
        from sc
        where sNo = student.sNo and cNo = 
                                        (select cNo
                                        from course
                                        where cName = '形式语言与自动机'));
8.查询所有选修了“形式语言与自动机”但没选修其先修课的学生姓名； 
1
2
3
4
5
6
7
8
9
10
select student1.sName
from student student1, sc sc1, course course1
where course1.cName = '形式语言与自动机' and student1.sNo = sc1.sNo and course1.cNo = sc1.cNo
except 
select student2.sName
from student student2, sc sc2, course course2
where course2.cNo = 
                    (select cPNo
                    from course
                    where cName = '形式语言与自动机') and student2.sNo = sc2.sNo and sc2.cNo = course2.cNo;
9.查询选修课程总学分大于等于28的学生姓名及其选修课程总学分； 
1
2
3
4
select student.sName, sum(credit)
from (student join sc on (student.sNo = sc.sNo)) join course on (course.cNo = sc.cNo)
group by student.sNo
having sum(credit) >= 28;
10.查询选修了3门以上课程且成绩都大于85分的学生学号与姓名； 
1
2
3
4
5
6
select student.sNo, student.sName
from student join sc on (student.sNo = sc.sNo)
--将会把有一门低于85的但有三门大于85的人选进来
where sc.score is not null and sc.score > 85
group by student.sNo
having count(sc.cNo) > 3;
1
2
3
4
5
select student.sNo, student.sName
from student join sc on (student.sNo = sc.sNo)
where sc.score is not null 
group by student.sNo
having count(sc.cNo) > 3 and min(sc.score) > 85;
11.查询恰好选修了3门课并且都及格的学生姓名； 
1
2
3
4
5
6
select student.sNo, student.sName
from student join sc on (student.sNo = sc.sNo)
--出现问题如上一题所示
where sc.score is not null and sc.score >= 60
group by student.sNo
having count(sc.cNo) = 3;
1
2
3
4
5
select student.sNo, student.sName
from student join sc on (student.sNo = sc.sNo)
where sc.score is not null 
group by student.sNo
having count(sc.cNo) = 3 and min(sc.score) >= 60;
12.查询人数多于6的学院名称及其学生人数； 
1
2
3
4
select department.dName, count(student.*)
from department join student on (department.dNo = student.dNo)
group by department.dNo
having count(student.*) > 6;
13.查询平均成绩高于王兵的学生姓名； 
1
2
3
4
5
6
7
8
select student1.sNo, student1.sName
from student student1 join sc sc1 on (student1.sNo = sc1.sNo)
where sc1.score is not null
group by student1.sNo
having (avg(sc1.score) >
                        (select avg(sc2.score)
                        from student student2 join sc sc2 on (student2.sNo = sc2.sNo)
                        where sc2.score is not null and student2.sName = '王兵'));
1
2
3
4
5
6
7
8
9
10
select student1.sNo, student1.sName
from student student1 join sc sc1 on (student1.sNo = sc1.sNo)
where sc1.score is not null
group by student1.sNo, student1.sName
having (avg(sc1.score) > (select max(avgScore)
                        from (select student2.sNo, student2.sName, avg(sc2.score)
						from student student2 join sc  sc2 on student2.sNo = sc2.sNo
						where sc2.score is not null
						group by student2.sNo, student2.sName) as s_avg(sNo, sName, avgScore)
						where s_avg.sName = '王兵'));
14.查询所有选修了离散数学并且选修了编译原理课程的学生姓名； 
1
2
3
4
5
6
7
8
9
10
select student1.sName
from student student1, sc sc1, course course1
where course1.cName = '离散数学' and student1.sNo = sc1.sNo and course1.cNo = sc1.cNo
intersect
select student2.sName
from student student2, sc sc2, course course2
where course2.cNo = 
                    (select cPNo
                    from course
                    where cName = '编译原理') and student2.sNo = sc2.sNo and sc2.cNo = course2.cNo;
15.查询软件学院离散数学课程平均分； 
1
2
3
select avg(score)
from sc, course, student, department
where department.dName = '软件学院' and student.dNo = department.dNo and course.cName = '离散数学' and sc.cNo = course.cNo and student.sNo = sc.sNo and sc.score is not null;
16.查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院； 
1
2
3
4
5
select student1.sName, student1.age, department1.dName
from student student1 left join department department1 on (student1.dNo = department1.dNo)
where student1.age not in (select student2.age
                            from student student2 left join department department2 on (student2.dNo = department2.dNo)
                            where department2.dName = '软件学院' and student2.age is not null);
17.查询各学院选修同一门课人数大于4的学院、课程及选课人数； 
1
2
3
4
5
6
7
select department1.dName, course1.cName, other.counts
from department department1, course course1, (select student2.dNo, sc2.cNo, count(student2.sNo)
											  from student student2, sc sc2
											  where student2.sNo = sc2.sNo
											  group by student2.dNo, sc2.cNo
											  having count(student2.sNo) > 4) as other(dNo, cNo, counts)
where department1.dNo = other.dNo and course1.cNo = other.cNo;
18.查询仅仅选修了“高等数学”一门课程的学生姓名；（学号、姓名及所在学院名称） 
1
2
3
4
5
6
7
select student1.sNo, student1.sName, department1.dName
from (student student1 join sc sc1 on (student1.sNo = sc1.sNo)) left join department department1 on (student1.dNo = department1.dNo)
where sc1.sNo not in (select sc2.sNo
                    from sc sc2
                    where sc2.cNo <> (select course1.cNo
                                                    from course course1
                                                    where course1.cName = '高等数学'));
19.查询平均学分积小于70分的学生姓名 
1
2
3
4
5
select student.sName
from (student join sc on (student.sNo = sc.sNo)) join course on (sc.cNo = course.cNo)
where sc.score is not null
group by student.sNo
having (sum(sc.score * course.credit) / sum(course.credit)) < 70;
1
2
3
4
5
select student.sName
from (student join sc on (student.sNo = sc.sNo)) join course on (sc.cNo = course.cNo)
where sc.score is not null
group by student.sNo
having CAST(sum(CAST(sc.score * course.credit AS NUMERIC)) / sum(course.credit) AS DECIMAL(5,2)) < 70.00;
20.查询选修了“信息学院”开设全部课程的学生姓名 
1
2
3
4
SELECT student.sName  FROM student WHERE  
    NOT EXISTS( SELECT course.cNo FROM course   
    WHERE NOT EXISTS(SELECT sc.cNo FROM sc WHERE sc.sNo=student.sNo AND sc.cNo=course.cNo )  
     AND course.dNo=(SELECT department.dNo FROM department WHERE department.dName='信息学院') );
1
2
3
4
5
6
7
8
9
10
11
12
select sName
from student s
where not exists 
                (select *
                from course c
                where c.dNo in 
                            (select dNo
                            from department
                            where dName = '信息学院') and not exists 
                                                            (select *
                                                            from sc
                                                            where sNo = s.sNo and cNo = c.cNo));
21.查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。 
1
2
3
4
5
6
7
8
9
10
11
12
select sname  
from student s  
where not exists
                (select *  
                from sc sc2 
                where sc2.sno=
                                (select sno from student where sname='杨佳伟')  
and not exists
                (select *  
                from sc sc3  
                where s.sno=sc3.sno and sc2.cno=sc3.cno))  
and s.sname<>'杨佳伟'; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select sName
from student
where sNo in 
            (select distinct sNo
            from sc scx
            where not exists 
                            (select * 
                            from sc scy
                            where scy.sNo = 
                                            (select sNo
                                            from student
                                            where sName = '杨佳伟') and not exists 
                                                                            (select *
                                                                            from sc scz
                                                                            where scz.sNo = scx.sNo and scz.cNo = scy.cNo)));

DDL练习

1.创建2张表，信息如下：
图书（编号，书名，作者，ISBN，出版社编号，版本，出版日期）。主码为编号，ISBN唯一。 出版社编号为外码，参照出版社编号。
出版社（编号，名称，地址，电话）。主码为编号。
要求：
(1)创建表的同时创建约束；
(2)删除所创建的表;
(3)重新创建表，在表创建之后增加约束。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
--drop table book cascade
--drop table publisher cascade  

create table publisher(
    pNo             varchar(20)     not null,
	pName           varchar(200),		
	address         varchar(200),
	TEL             varchar(200),
	primary key(pNo)
);

create table book (
    bNo         char(12)		not null unique,
	bName		varchar(200)	not null,
	author  	varchar(200)	not null,
	ISBN		varchar(200)	not null unique,
	pNo             varchar(20),
	bVersion	char(6),
	bDate		date,
	primary key(bNo),
	foreign key(pNo) references publisher(pNo)
);

create table publisher(
	pNo             varchar(20)		not null,
	pName           varchar(200),		
	address         varchar(200),
	TEL             varchar(200)
);

create table book (
	bNo         char(12)        not null unique,
	bName       varchar(200)    not null,
	author      varchar(200)    not null,
	ISBN        varchar(200)    not null unique,
	pNo         varchar(20),
	bVersion    char(6),
	bDate       date,
	primary key(bNo)
);
alter table publisher add primary key(pNo);
alter table book add foreign key(pNo) references publisher(pNo);

2.分别向两张表中各插入2行数据。 

1
2
3
4
5
insert into publisher values('01', '机械工业出版社', '北京', '110');
insert into publisher values('02', '高等教育出版社', '上海', '120');

insert into book values('10086', '算法导论', 'Thomas', '9787040370683', '01', '1.0', to_date('2017-01-08','yyyy-mm-dd'));
insert into book values('10000', '离散数学', 'Pony', '9787040370688', '02', '16.3', to_date('2018-01-08','yyyy-mm-dd'));
3.将其中一个出版社地址变更一下。 
1
2
3
update publisher
set address = '陕西'
where pNo = '01';
4.删除所插入数据。 
1
2
3
4
5
delete from
book cascade;

delete
from publisher cascade;
5.创建一个软件学院所有选修了“离散数学”课程的学生视图，并通过视图插入一行数据。 
1
2
3
4
5
6
--drop view other cascade;
create view other
as
select student.sNo, student.sName
from student join sc on student.sNo = sc.sNo join course on sc.cNo = course.cNo join department on student.dNo = department.dNo
where department.dName = '软件学院' and course.cName = '离散数学';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop view other cascade;
create view other
as
SELECT student.sNo, student.sName
FROM student
WHERE student.dNo in
                    (SELECT department.dNo
                    FROM department
                    WHERE department.dName = '软件学院') and student.sNo in 
                                                                        (SELECT sc.sNo
                                                                        FROM sc
                                                                        where sc.sNo = student.sNo and sc.cNo in 
                                                                                                                (SELECT course.cNo
                                                                                                                FROM course
                                                                                                                WHERE course.cName = '离散数学'));
1
2
3
insert
into other
values('170288', '李天一');
6.创建一个各门课程平均分视图。 
1
2
3
4
5
6
drop view AVERAGE;
create view AVERAGE as
select sc.cNo, course.cName, avg(sc.score)
from course join sc on course.cNo = sc.cNo
where sc.score is not null
group by sc.cNo, course.cName;
7.创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插入所有学生数据。 
1
2
3
4
5
6
--drop view s_score;
create view s_score as
select student.sNo, student.sName, avg(sc.score)
from student left join sc on student.sNo = sc.sNo
where sc.score is not null
group by student.sNo, student.sName
1
2
select *
from s_score;
## DCL
尝试将多条SQL语句组成一个事务执行，体验提交和回滚操作。 
1
2
3
4
5
6
begin transaction;
select *
from sc;
select *
from student;
commit;