经典数据库SQL语句编写练习题——SQL语句扫盲

【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语言完成表的创建以及数据的插入：

[sql] view plain copy
print?
  1. use test;  
  2.   
  3. create table if not exists student  
  4. (  
  5.     SNO varchar(20) primary key,  
  6.     SNAME varchar(20) character set gbk,  
  7.     AGE int,  
  8.     SEX  char(2) character set gbk CHECK(SEX IN('男','女'))   
  9. );  
  10. insert into student values('1','李强',23,'男');  
  11. insert into student values('2','刘丽',22,'女');  
  12. insert into student values('5','张友',22,'男');  
  13.   
  14. create table if not exists course  
  15. (  
  16.     CNO varchar(20) primary key,  
  17.     CNAME varchar(20) character set gbk,  
  18.     TEACHER varchar(20) character set gbk  
  19. );  
  20. insert into course values('K1','C语言','王华');  
  21. insert into course values('K5','数据库原理','程军');  
  22. insert into course values('K8','编译原理','程军');  
  23.   
  24. create table if not exists sc  
  25. (  
  26.     SNO varchar(20) NOT NULL,  
  27.     CNO varchar(20) NOT NULL,  
  28.     SCORE int NOT NULL,  
  29.     primary key (SNO,CNO),  
  30.     foreign key (SNO) references student(SNO),  
  31.     foreign key (CNO) references course(CNO)  
  32. );  
  33. insert into sc values('1','K1',83);  
  34. insert into sc values('2','K1',85);  
  35. insert into sc values('5','K1',92);  
  36. insert into sc values('2','K5',90);  
  37. insert into sc values('5','K5',84);  
  38. insert into sc values('5','K8',80);  
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）；

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

[sql] view plain copy
print?
  1. select sname from student  
  2. where not exists  
  3. (  
  4.    select * from course  
  5.    where teacher='程军' and not exists  
  6.    (  
  7.        select * from sc   
  8.        where sc.sno=student.sno and sc.cno=course.cno  
  9.     )  
  10. );  
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 plain copy
print?
  1. select course.cno from course  
  2. where course.cno not in  
  3. (  
  4. select sc.cno from sc,student  
  5. where student.sname='李强' and student.sno=sc.sno  
  6. );  
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 plain copy
print?
  1. SELECT sc.sno  
  2. FROM sc  
  3. GROUP BY sc.sno HAVING count(*)>=3  
  4. ;  
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 plain copy
print?
  1. SELECT sname FROM student  
  2. WHERE NOT EXISTS  
  3. (  
  4. SELECT * FROM course  
  5. WHERE NOT EXISTS  
  6.     (  
  7.         SELECT * FROM sc  
  8.         WHERE course.cno=sc.cno and student.sno=sc.sno  
  9.     )  
  10. );  
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 plain copy
print?
  1. SELECT * FROM student  
  2. WHERE sno not in  
  3. (  
  4.     SELECT sc.sno FROM sc,course  
  5.     WHERE course.cno=sc.cno  
  6. );  
SELECT * FROM student
WHERE sno not in
(
    SELECT sc.sno FROM sc,course
    WHERE course.cno=sc.cno
);

1.4 要求三

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

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

[sql] view plain copy
print?
  1. SELECT * FROM course  
  2. WHERE teacher='程军';  
SELECT * FROM course
WHERE teacher='程军';
（2）查询“李强”同学所有课程的成绩；
[sql] view plain copy
print?
  1. SELECT score FROM student,sc  
  2. WHERE student.sname='李强' AND student.sno=sc.sno  
SELECT score FROM student,sc
WHERE student.sname='李强' AND student.sno=sc.sno

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

[sql] view plain copy
print?
  1. SELECT AVG(score) FROM sc,course  
  2. WHERE course.cname='C语言' AND course.cno=sc.cno  
SELECT AVG(score) FROM sc,course
WHERE course.cname='C语言' AND course.cno=sc.cno

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

[sql] view plain copy
print?
  1. SELECT * FROM student   
  2. WHERE NOT EXISTS  
  3. (  
  4.     SELECT * FROM course  
  5.     WHERE NOT EXISTS  
  6.     (  
  7.          SELECT * FROM sc  
  8.          WHERE course.cno=sc.cno AND student.sno=sc.sno  
  9.     )  
  10. );  
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 plain copy
print?
  1. SELECT cno,cname FROM course WHERE teacher LIKE '王%';  
SELECT cno,cname FROM course WHERE teacher LIKE '王%';
（2）检索年龄大于23岁的男学生的学号和姓名。
[sql] view plain copy
print?
  1. SELECT sno,sname FROM student  
  2. WHERE age>23;  
SELECT sno,sname FROM student
WHERE age>23;
（3）检索至少选修王老师所授课程中一门课程的女学生姓名。
[sql] view plain copy
print?
  1. SELECT sname FROM student  
  2. WHERE sex='女' AND sno IN  
  3. (  
  4.     SELECT distinct sno FROM sc,course  
  5.     WHERE teacher LIKE '王%' AND sc.cno=course.cno  
  6. );  
SELECT sname FROM student
WHERE sex='女' AND sno IN
(
    SELECT distinct sno FROM sc,course
    WHERE teacher LIKE '王%' AND sc.cno=course.cno
);
（4）检索李同学不学的课程的课程号。
[sql] view plain copy
print?
  1. SELECT course.cno FROM course  
  2. WHERE course.cno NOT IN  
  3. (  
  4.     SELECT sc.cno FROM sc,student  
  5.     WHERE student.sname LIKE '李%' AND sc.sno=student.sno  
  6. );  
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）检索至少选修两门课程的学生学号。
[sql] view plain copy
print?
  1. SELECT sno FROM sc  
  2. GROUP BY sno HAVING (count(*)>=2);  
SELECT sno FROM sc
GROUP BY sno HAVING (count(*)>=2);
（6）检索全部学生都选修的课程的课程号与课程名。
[sql] view plain copy
print?
  1. SELECT course.cno,course.cname FROM course   
  2. WHERE course.cno IN  
  3. (  
  4.     SELECT cno FROM sc  
  5.     GROUP BY cno HAVing count(*)=(SELECT count(*) FROM student)  
  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）检索选修课程包含王老师所授课的学生学号。
[sql] view plain copy
print?
  1. SELECT DISTINCT sno FROM sc  
  2. WHERE cno IN  
  3. (  
  4.     SELECT cno FROM course  
  5.     WHERE teacher LIKE '王%'  
  6. );  
SELECT DISTINCT sno FROM sc
WHERE cno IN
(
    SELECT cno FROM course
    WHERE teacher LIKE '王%'
);
（8）统计有学生选修的课程门数。
[sql] view plain copy
print?
  1. SELECT count(*)  NumberOfCourse  
  2. FROM   
  3. (  
  4.       SELECT DISTINCT cno FROM sc  
  5.       GROUP BY cno  
  6. ) AS testTab;  
SELECT count(*)  NumberOfCourse
FROM 
(
      SELECT DISTINCT cno FROM sc
      GROUP BY cno
) AS testTab;
（9）求选修K1课程的学生的平均年龄。
[sql] view plain copy
print?
  1. SELECT AVG(age) AverageOfAge  
  2. FROM student   
  3. WHERE sno IN  
  4. (  
  5.     SELECT sno FROM sc  
  6.     WHERE cno='K1'  
  7. );  
SELECT AVG(age) AverageOfAge
FROM student 
WHERE sno IN
(
    SELECT sno FROM sc
    WHERE cno='K1'
);
（10）求王老师所授课程的每门课程的学生平均成绩。
[sql] view plain copy
print?
  1. SELECT sc.cno,AVG(sc.score)  
  2. FROM sc  
  3. WHERE sc.cno IN  
  4. (  
  5. SELECT course.cno FROM course  
  6. WHERE course.teacher LIKE '王%'  
  7. )  
  8. GROUP BY sc.cno  
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人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
[sql] view plain copy
print?
  1. SELECT cno,Num  
  2. FROM   
  3. (  
  4. SELECT cno,count(*) as Num  
  5. FROM sc  
  6. GROUP BY cno HAVING (count(*) >= 2)  
  7. ) tb_temp  
  8. ORDER BY Num DESC,cno ASC  
  9. ;  
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）检索学号比李同学大，而年龄比他小的学生姓名。
[sql] view plain copy
print?
  1. SELECT stu1.sname   
  2. FROM student stu1,  
  3. (  
  4.     SELECT max(sno) snoLi,min(age) ageLi FROM student   
  5.     WHERE sname LIKE '李%'  
  6. ) AS stuLi  
  7. WHERE (stu1.age < stuLi.ageLi) AND (stu1.sno>stuLi.snoLi)  
  8. ;   
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）检索姓名以李打头的所有学生的姓名和年龄。
[sql] view plain copy
print?
  1. SELECT sname,age FROM student  
  2. WHERE sname LIKE '李%'  
  3. ;  
SELECT sname,age FROM student
WHERE sname LIKE '李%'
;
（14）在SC中检索成绩为空值的学生学号和课程号。
[sql] view plain copy
print?
  1. SELECT sno,cno FROM sc  
  2. WHERE score IS NULL;  
SELECT sno,cno FROM sc
WHERE score IS NULL;
（15）求年龄大于女同学平均年龄的男学生姓名和年龄。
[sql] view plain copy
print?
  1. SELECT sname,age FROM student  
  2. WHERE  
  3. sex='男' AND age > (  
  4.                       SELECT AVG(age)  
  5.                       FROM student  
  6.                       WHERE sex='女'  
  7.                    )  
  8. ;  
SELECT sname,age FROM student
WHERE
sex='男' AND age > (
                      SELECT AVG(age)
                      FROM student
                      WHERE sex='女'
                   )
;
（16）求年龄大于所有女同学年龄的男学生姓名和年龄。
[sql] view plain copy
print?
  1. SELECT sname,age  
  2. FROM student  
  3. WHERE sex='男' AND age > (  
  4.                             SELECT max(age)  
  5.                              FROM student  
  6.                             WHERE sex='女'  
  7.                          )  
  8. ;  
SELECT sname,age
FROM student
WHERE sex='男' AND age > (
                            SELECT max(age)
                             FROM student
                            WHERE sex='女'
                         )
;
posted @ 2018-02-02 11:50  北方客888  阅读(1506)  评论(0)    收藏  举报