sql练习题整理2

student 学生表(s_id,s_name,s_birth,s_sex)
course课程表(c_id,c_name,t_id)
teacher老师表(t_id,t_name)
score 课程表(s_id,c_id,s_score)

1.查询‘李’姓老师的数量
SELECT COUNT(*)
FROM teacher
WHERE t_name LIKE ‘李%’；

2.查有成绩的学生信息
SELECT student.*
FROM student
WHERE s_id IN (SELECT s_id FROM score);

3.查询学过‘张三’老师授课的同学的信息
SELECT student.*
FROM student,score,teacher,course
WHERE student.s_id=score.s_id AND course.c_id=score.c_id AND course.t_id=teacher.t_id AND teacher.t_name=‘张三’;

4.查询没有学全所有课程的同学的信息
第一种
SELECT student.*
FROM student,score
WHERE student.s_id=score.s_id
GROUP BY student.s_id
HAVING COUNT(*) < (SELECT COUNT(c_id) FROM course);

第二种
SELECT student.*
FROM student
WHERE EXISTS
(SELECT *
FROM course
WHERE NOT EXISTS
(SELECT*
FROM SC
WHERE student.s_id=score.s_id AND course.c_id=score.c_id));

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT student.s_id,student.s_name,COUNT(student.s_id),SUM(score.s_score)
FROM student,score
WHERE student.s_id=score.s_id
GROUP BY student.s_id

6.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT student.*
FROM student,score
WHERE student.s_id=score.s_id AND score.c_id IN (SELECT c_id FROM score WHERE s_id=‘01’);

7.查询和01号学生学习的课程完全相同的其他同学的信息
SELECT student.*
FROM student
WHERE s_id!=‘01’ AND NOT EXISTS
(SELECT *
FROM score AS sc1
WHERE student.s_id=sc1.s_id AND NOT EXISTS
(SELECT *
FROM score AS sc2
WHERE sc1.c_id=sc2.c_id AND sc2.s_id=‘01’));

8.查询每门课程被选修的学生数
SELECT c_id,COUNT(s_id)
FROM score
GROUP BY c_id;

9.查询男生、女生人数
SELECT s_sex,COUNT(*);
FROM student
GROUP BY s_sex;

10.查询出只选修两门课程的学生学号和姓名
SELECT student.s_id,student.s_name
FROM student,score
WHERE student.s_id=score.s_id
GROUP BY score.s_id
HAVING COUNT(*)=2;

11.查询名字中含有‘风’字的学生信息
SELECT student.*
FROM student
WHERE s_name LIKE ‘%风%’;

12.查询同名同性学生名单，并统计同名人数
SELECT s_name,s_sex,COUNT(*)
FROM student
GROUP BY s_name,s_sex

13.查询课程名称为‘数学’，且分数低于60的学生姓名和分数
SELECT student.s_name,score.s_score
FROM student,score,course
FROM student.s_id=score.s_id AND course.c_id=score.c_id AND score.s_score<60 AND course.c_name=‘数学’；