【MySQL】数据库原理复习——SQL语言

对基本的SQL语句编写的练习题，其中的题目的答案可能会有多种书写方式。

1、题目1

1.1 关系模式

学生student：SNO：学号，SNAME：姓名，AGE：年龄 SEX：性别

SNO	SNAME	AGE	SEX
1	李强	23	男
2	刘丽	22	女
5	张友	22	男

       课程course：CNO：课程代码，CNAME：课程名称，TEACHER：教师

CNO	CNAME	TEACHER
K1	C语言	王华
K5	数据库原理	程军
K8	编译原理	程军

    学生成绩SC：SNO：学号，CNO：课程代码，SCORE：成绩

SNO	CNO	SCORE
1	K1	83
2	K1	85
5	K1	92
2	K5	90
5	K5	84
5	K8	80

 

1.2 要求一

用SQL语言完成表的创建以及数据的插入：

use test;

create table if not exists student
(
	SNO varchar(20) primary key,
	SNAME varchar(20) character set gbk,
	AGE int,
	SEX  char(2) character set gbk CHECK(SEX IN('男','女')) 
);
insert into student values('1','李强',23,'男');
insert into student values('2','刘丽',22,'女');
insert into student values('5','张友',22,'男');

create table if not exists course
(
	CNO varchar(20) primary key,
	CNAME varchar(20) character set gbk,
	TEACHER varchar(20) character set gbk
);
insert into course values('K1','C语言','王华');
insert into course values('K5','数据库原理','程军');
insert into course values('K8','编译原理','程军');

create table if not exists sc
(
	SNO varchar(20) NOT NULL,
	CNO varchar(20) NOT NULL,
	SCORE int NOT NULL,
	primary key (SNO,CNO),
	foreign key (SNO) references student(SNO),
	foreign key (CNO) references course(CNO)
);
insert into sc values('1','K1',83);
insert into sc values('2','K1',85);
insert into sc values('5','K1',92);
insert into sc values('2','K5',90);
insert into sc values('5','K5',84);
insert into sc values('5','K8',80);

1.3 要求二

用SQL语言完成如下要求：

（1）       检索至少选修＂程军＂老师所授全部课程的学生姓名（SNAME）；

     本题的另外一种说法就是，检索选修了“程军”老师所授全部课程的学生姓名

select sname from student
where not exists
(
   select * from course
   where teacher='程军' and not exists
   (
       select * from sc 
       where sc.sno=student.sno and sc.cno=course.cno
    )
);

这里的检索结果是：

+-------+
| sname |
+-------+
| 张友  |
+-------+
1 row in set (0.00 sec)

（2）       检索＂李强＂同学不学课程的课程号(CNO)；

select course.cno from course
where course.cno not in
(
select sc.cno from sc,student
where student.sname='李强' and student.sno=sc.sno
);

+-----+
| cno |
+-----+
| K5  |
| K8  |
+-----+
2 rows in set (0.22 sec)

（3）       检索选修不少于3门课程的学生学号(SNO)；

SELECT sc.sno
FROM sc
GROUP BY sc.sno HAVING count(*)>=3
;

+-----+
| sno |
+-----+
| 5   |
+-----+
1 row in set (0.09 sec)

（4）       检索选修全部课程的学生姓名(SNAME)。

SELECT sname FROM student
WHERE NOT EXISTS
(
SELECT * FROM course
WHERE NOT EXISTS
    (
        SELECT * FROM sc
        WHERE course.cno=sc.cno and student.sno=sc.sno
    )
);

+-------+
| sname |
+-------+
| 张友  |
+-------+
1 row in set (0.02 sec)

（5）       检索不学"C语言"的学生信息

SELECT * FROM student
WHERE sno not in
(
    SELECT sc.sno FROM sc,course
    WHERE course.cno=sc.cno
);

1.4 要求三

请用SQL语言完成如下查询：

（1）查询“程军”老师所教授的所有课程；

SELECT * FROM course
WHERE teacher='程军';

（2）查询“李强”同学所有课程的成绩；

SELECT score FROM student,sc
WHERE student.sname='李强' AND student.sno=sc.sno

（3）查询课程名为“C语言”的平均成绩；

SELECT AVG(score) FROM sc,course
WHERE course.cname='C语言' AND course.cno=sc.cno

（4）查询选修了所有课程的同学信息。

SELECT * FROM student 
WHERE NOT EXISTS
(
    SELECT * FROM course
    WHERE NOT EXISTS
    (
         SELECT * FROM sc
         WHERE course.cno=sc.cno AND student.sno=sc.sno
    )
);

1.5 要求四

（1）检索王老师所授课程的课程号和课程名。

SELECT cno,cname FROM course WHERE teacher LIKE '王%';

（2）检索年龄大于23岁的男学生的学号和姓名。

SELECT sno,sname FROM student
WHERE age>23;

（3）检索至少选修王老师所授课程中一门课程的女学生姓名。

SELECT sname FROM student
WHERE sex='女' AND sno IN
(
    SELECT distinct sno FROM sc,course
    WHERE teacher LIKE '王%' AND sc.cno=course.cno
);

（4）检索李同学不学的课程的课程号。

SELECT course.cno FROM course
WHERE course.cno NOT IN
(
    SELECT sc.cno FROM sc,student
    WHERE student.sname LIKE '李%' AND sc.sno=student.sno
);

（5）检索至少选修两门课程的学生学号。

SELECT sno FROM sc
GROUP BY sno HAVING (count(*)>=2);

（6）检索全部学生都选修的课程的课程号与课程名。

SELECT course.cno,course.cname FROM course 
WHERE course.cno IN
(
    SELECT cno FROM sc
    GROUP BY cno HAVing count(*)=(SELECT count(*) FROM student)
);

（7）检索选修课程包含王老师所授课的学生学号。

SELECT DISTINCT sno FROM sc
WHERE cno IN
(
    SELECT cno FROM course
    WHERE teacher LIKE '王%'
);

（8）统计有学生选修的课程门数。

SELECT count(*)  NumberOfCourse
FROM 
(
      SELECT DISTINCT cno FROM sc
      GROUP BY cno
) AS testTab;

（9）求选修K1课程的学生的平均年龄。

SELECT AVG(age) AverageOfAge
FROM student 
WHERE sno IN
(
    SELECT sno FROM sc
    WHERE cno='K1'
);

（10）求王老师所授课程的每门课程的学生平均成绩。

SELECT sc.cno,AVG(sc.score)
FROM sc
WHERE sc.cno IN
(
SELECT course.cno FROM course
WHERE course.teacher LIKE '王%'
)
GROUP BY sc.cno

（11）统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECT cno,Num
FROM 
(
SELECT cno,count(*) as Num
FROM sc
GROUP BY cno HAVING (count(*) >= 2)
) tb_temp
ORDER BY Num DESC,cno ASC
;

（12）检索学号比李同学大，而年龄比他小的学生姓名。

SELECT stu1.sname 
FROM student stu1,
(
    SELECT max(sno) snoLi,min(age) ageLi FROM student 
    WHERE sname LIKE '李%'
) AS stuLi
WHERE (stu1.age < stuLi.ageLi) AND (stu1.sno>stuLi.snoLi)
; 

（13）检索姓名以李打头的所有学生的姓名和年龄。

SELECT sname,age FROM student
WHERE sname LIKE '李%'
;

（14）在SC中检索成绩为空值的学生学号和课程号。

SELECT sno,cno FROM sc
WHERE score IS NULL;

（15）求年龄大于女同学平均年龄的男学生姓名和年龄。

SELECT sname,age FROM student
WHERE
sex='男' AND age > (
                      SELECT AVG(age)
                      FROM student
                      WHERE sex='女'
                   )
;

（16）求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECT sname,age
FROM student
WHERE sex='男' AND age > (
                            SELECT max(age)
                             FROM student
                            WHERE sex='女'
                         )
;