【MySQL】经典数据库SQL语句编写练习题——SQL语句扫盲
【MySQL】数据库原理复习——SQL语言
对基本的SQL语句编写的练习题,其中的题目的答案可能会有多种书写方式。
view plaincopy- 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);
view plaincopy- 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
- )
- );
- 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);
[sql]view plaincopy
- 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);
[sql]view plaincopy
- SELECT sc.sno
- FROM sc
- GROUP BY sc.sno HAVING count(*)>=3
- ;
+-----+
| sno |
+-----+
| 5 |
+-----+
1 row in set (0.09 sec)
(4) 检索选修全部课程的学生姓名(SNAME)。
[sql]view plaincopy
- 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语言"的学生信息
[sql]view plaincopy
- SELECT * FROM student
- WHERE sno not in
- (
- SELECT sc.sno FROM sc,course
- WHERE course.cno=sc.cno
- );
1.4 要求三
请用SQL语言完成如下查询:
(1)查询“程军”老师所教授的所有课程;
[sql]view plaincopy
- SELECT * FROM course
- WHERE teacher=\'程军\';
[sql]view plaincopy
- SELECT score FROM student,sc
- WHERE student.sname=\'李强\' AND student.sno=sc.sno
(3)查询课程名为“C语言”的平均成绩;
[sql]view plaincopy
- SELECT AVG(score) FROM sc,course
- WHERE course.cname=\'C语言\' AND course.cno=sc.cno
(4)查询选修了所有课程的同学信息。
[sql]view plaincopy
- 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)检索王老师所授课程的课程号和课程名。
[sql]view plaincopy
- SELECT cno,cname FROM course WHERE teacher LIKE \'王%\';
(2)检索年龄大于23岁的男学生的学号和姓名。
[sql]view plaincopy
- SELECT sno,sname FROM student
- WHERE age>23;
(3)检索至少选修王老师所授课程中一门课程的女学生姓名。
[sql]view plaincopy
- SELECT sname FROM student
- WHERE sex=\'女\' AND sno IN
- (
- SELECT distinct sno FROM sc,course
- WHERE teacher LIKE \'王%\' AND sc.cno=course.cno
- );
[sql]view plaincopy
- 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
- );
[sql]view plaincopy
- SELECT sno FROM sc
- GROUP BY sno HAVING (count(*)>=2);
[sql]view plaincopy
- SELECT course.cno,course.cname FROM course
- WHERE course.cno IN
- (
- SELECT cno FROM sc
- GROUP BY cno HAVing count(*)=(SELECT count(*) FROM student)
- );
[sql]view plaincopy
- SELECT DISTINCT sno FROM sc
- WHERE cno IN
- (
- SELECT cno FROM course
- WHERE teacher LIKE \'王%\'
- );
[sql]view plaincopy
- SELECT count(*) NumberOfCourse
- FROM
- (
- SELECT DISTINCT cno FROM sc
- GROUP BY cno
- ) AS testTab;
[sql]view plaincopy
- SELECT AVG(age) AverageOfAge
- FROM student
- WHERE sno IN
- (
- SELECT sno FROM sc
- WHERE cno=\'K1\'
- );
[sql]view plaincopy
- 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
[sql]view plaincopy
- 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
- ;
[sql]view plaincopy
- 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)
- ;
[sql]view plaincopy
- SELECT sname,age FROM student
- WHERE sname LIKE \'李%\'
- ;
[sql]view plaincopy
- SELECT sno,cno FROM sc
- WHERE score IS NULL;
[sql]view plaincopy
- SELECT sname,age FROM student
- WHERE
- sex=\'男\' AND age > (
- SELECT AVG(age)
- FROM student
- WHERE sex=\'女\'
- )
- ;
[sql]view plaincopy
- SELECT sname,age
- FROM student
- WHERE sex=\'男\' AND age > (
- SELECT max(age)
- FROM student
- WHERE sex=\'女\'
- )
- ;
版权声明:本文为博主原创文章,未经博主允许不得转载。