MySQL经典50题（1）

1、查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT a.SId FROM Sc a, Sc b
WHERE a.SId = b.SId 
AND a.CId = '001' 
AND b.CId = '002' 
AND a.Score > b.Score

2、查询平均成绩大于60分的同学的学号和平均成绩:

SELECT SId,AVG(Score)
FROM sc 
GROUP BY SId HAVING AVG(Score)>60

3、查询同学的学号、姓名、选课数、总成绩：

SELECT
SId, StuName,
(SELECT COUNT(CId) FROM sc t1 WHERE t1.SId = s1.SId) course_count,
(SELECT SUM(Score) FROM sc t2 WHERE t2.SId = s1.SId) sum_score
FROM
student s1

4、查询姓“李”的老师的个数：

SELECT COUNT(Tname) FROM teacher WHERE Tname LIKE '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名：

SELECT
SId,
StuName
FROM
student
WHERE SId NOT in(
	SELECT s.SId 
	FROM
	teacher t,sc s,	course c
	WHERE
		t.Tname = '叶平'
	AND c.TId = t.TId
	AND c.CId = s.CId
)

 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名：

SELECT
st.SId,
st.StuName
FROM(
	SELECT t1.SId AS SId
	FROM 
	(SELECT s1.SId FROM sc s1 WHERE s1.CId = '001')t1,
	(SELECT s2.SId FROM sc s2 WHERE s2.CId = '002')t2
	WHERE
	t1.SId = t2.SId
)stu, student st
WHERE
stu.SId = st.SId

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名：

SELECT st.SId, st.StuName FROM teacher t, sc s, student st, course c
WHERE t.Tname = '叶平' AND c.TId=t.TId AND c.CId=s.CId AND s.SId = st.SId

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：

SELECT a.SId FROM Sc a, Sc b
WHERE a.SId = b.SId 
AND a.CId = '001' 
AND b.CId = '002' 
AND a.Score < b.Score

9、查询所有课程成绩小于60分的同学的学号、姓名：

SELECT 
SId, StuName
FROM
student
WHERE SId NOT IN(
	SELECT SId
	FROM sc
	WHERE
	Score > 60
)

10、查询没有学全所有课的同学的学号、姓名：

SELECT stu.SId, stu.StuName FROM 
student stu,
(SELECT COUNT(CId) as number FROM course)t1,
(SELECT SId,COUNT(CId) as number FROM sc GROUP BY SId)t2
WHERE
t1.number > t2.number AND t2.SId = stu.SId

 11.查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

SELECT  DISTINCT(sc.SId), student.StuName 
FROM
	(SELECT CId FROM sc WHERE SId = '1001')a,
	sc,student
WHERE 
	a.CId = sc.CId 
	AND sc.SId = student.SId 
	AND sc.SId <> '1001' 
ORDER BY sc.SId

 12.查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名

SELECT res.SId, student.StuName 
FROM
	(SELECT b.SId, COUNT(*) as number FROM
	(SELECT SId FROM
	(SELECT CId FROM sc WHERE SId = '1001')a,
sc aa
WHERE 
	a.CId = aa.CId)b GROUP BY b.SId)res,
	(SELECT COUNT(CId) as number FROM sc WHERE SId = '1001')c,
	student
WHERE res.number = c.number 
	AND res.SId <> '1001'
	AND student.SId = res.SId

 13、删除“002”同学的“001”课程的成绩

Delete From sc Where Stuld='1002'And Courseld='001'

 14、检索“004”课程分数小于60，按分数降序排列的同学学号(ok)

Select StulD,Score From tblScore Where Courseld='004' And Score<60 Order byScore Desc

15、查询每门功成绩最好的前两名 

Select课程号=Courseld,
第1名=(Select Top 1 Stuld From tblScore Where Courseld=cs.Courseld
Order by Score DESC),
第2名=(Select Top 1 StulD From(Select Top 2 Stuld,Score From tblScore
Where Courseld=cs.Courseld Order by Score DESC) as tmp Order by Score)
From tblCourse cs

 16、求选了课程的学生人数

Select选了课程的学生人数=Count(*) From tblStudent st Where Stuld lN(Select StulD From tblScore)

 17、查询不及格的课程，并按课程号从大到小排列

Select * From tblScore Where Score<60 order by Courseld Desc

18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

Select姓名=StuName,课程名称=CourseName,分数=Score From tblScore sc
lnner Join tblStudent st On sc.Stuld=st.Stuld
Inner Join tblCourse cs On sc.Courseld=cs.Courseld

 19、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

Select学号=Stuld,姓名=StuName,平均成绩=(Select Avg(Score) From tblScoreWhere Stuld=st.Stuld) From tblStudent st
Where (Select Avg(Score)From tblScore Where Stuld=st.Stuld)>85

20、1981年出生的学生名单(注:Student表中Sage列的类型是datetime) 

select * From tblStudent Where Year(Sage)=1981

 21、查询姓“张”的学生名单

select * From tblStudent Where StuName like '张%'

 22、查询男生、女生人数

Select男生人数=(select Count(*) From tblStudent Where StuSex='男')，
女生人数=(select Count(*) From tblStudent Where StuSex='女')