SQL Server面试题

1、数据表

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

2、模拟数据

/*模拟数据*/
INSERT INTO Teacher VALUES('T001','叶平')
INSERT INTO Teacher VALUES('T002','李强')
INSERT INTO Teacher VALUES('T003','李红')

INSERT INTO Course VALUES('001','C#编程','T001')
INSERT INTO Course VALUES('002','Java编程','T003')
INSERT INTO Course VALUES('003','SQL数据库','T001')

INSERT INTO Student VALUES('1001','张三',23,1)
INSERT INTO Student VALUES('1002','李四',22,1)
INSERT INTO Student VALUES('1003','王五',27,2)
INSERT INTO Student VALUES('1004','孙六',22,1)

INSERT INTO SC VALUES('1001','001',58)
INSERT INTO SC VALUES('1002','001',70)
INSERT INTO SC VALUES('1003','001',88)
INSERT INTO SC VALUES('1001','002',55)
INSERT INTO SC VALUES('1002','002',71)
INSERT INTO SC VALUES('1003','002',54)
INSERT INTO SC VALUES('1004','002',90)
INSERT INTO SC VALUES('1001','003',43)

3、题目与答案

--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT t1.S# FROM 
SC AS t1
LEFT JOIN SC AS t2 ON t1.S# = t2.S#
WHERE t1.C# = '001'
AND t2.C# = '002'
AND t1.score > t2.score

SELECT t1.S#
FROM (SELECT S#,score FROM SC WHERE C# = '001') t1,
(SELECT S#,score FROM SC WHERE C# = '002') t2
WHERE t1.score > t2.score AND t1.S# = t2.S#

--2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT S#,AVG(score) AS AVG 
FROM SC
GROUP BY S#
HAVING AVG(score) > 60

--3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT t1.S#,t1.Sname,COUNT(t2.S#) AS '选课数',SUM(t2.score) AS '总成绩'
FROM Student AS t1
LEFT JOIN SC AS t2 ON t1.S# = t2.S#
GROUP BY t1.S#,t1.Sname

--4、查询姓“李”的老师的个数；
SELECT COUNT(*)
FROM Teacher
WHERE Tname LIKE('李%')

--5、查询没学过“叶平”老师课的同学的学号、姓名；
SELECT S#,Sname 
FROM Student
WHERE S# NOT IN(
	SELECT t1.S# 
	FROM SC AS t1
	JOIN Course AS t2 ON t1.C# = t2.C#
	JOIN Teacher AS t3 ON t2.T# = t3.T#
	WHERE t3.Tname = '叶平'
)

SELECT S#,Sname 
FROM Student
WHERE S# NOT IN(
	SELECT SC.S# 
	FROM SC,Course,Teacher 
	WHERE SC.C# = Course.C#
	AND  Course.T# = Teacher.T#
	AND Teacher.Tname = '叶平'
)

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT S#,Sname  
FROM Student
WHERE S# IN(
	SELECT t1.S# FROM SC AS t1
	JOIN SC AS t2 ON t1.S# = t2.S#
	WHERE t1.C# = '001'
	and t2.C# = '002'
)

--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT S#,Sname 
FROM Student 
WHERE S# IN (
	SELECT S# 
	FROM SC ,Course ,Teacher 
	WHERE SC.C#=Course.C# 
	AND Teacher.T#=Course.T# 
	AND Teacher.Tname='叶平' 
	GROUP BY S# 
	HAVING COUNT(SC.C#)=(SELECT COUNT(C#) FROM Course,Teacher WHERE Teacher.T#=Course.T# AND Tname='叶平')
)

--8、查询所有课程成绩小于60分的同学的学号、姓名； 
SELECT S#,Sname 
FROM Student 
WHERE S# NOT IN (
	SELECT SC.S# 
	FROM SC
	WHERE SC.score>60
	AND SC.score>60
)

--9、查询没有学全所有课的同学的学号、姓名；
SELECT S#,Sname 
FROM Student 
WHERE S# NOT IN (
	SELECT SC.S#
	FROM SC
	GROUP BY SC.S#
	HAVING COUNT(SC.S#) = (SELECT COUNT(*) FROM Course)
)

--10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
SELECT Student.S#,Student.Sname 
FROM Student,SC
WHERE Student.S# = SC.S#
AND SC.C# IN (SELECT C# FROM SC WHERE S#='1001')
AND Student.S# != '1001'
GROUP BY Student.S#,Student.Sname 

--11、删除学习“叶平”老师课的SC表记录； 
--Delect SC 
--FROM course ,Teacher  
--WHERE Course.C#=SC.C# AND Course.T#= Teacher.T# AND Tname='叶平';

--12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
SELECT SC.C# 课程ID, MAX(SC.score) 最高分,MIN(SC.score) 最低分 
FROM SC
GROUP BY SC.C#

--13、查询学生平均成绩及其名次 
SELECT SC.S#,AVG(SC.score) 平均成绩 
FROM SC 
GROUP BY SC.S#
ORDER BY AVG(SC.score) DESC 

--14、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 
FROM SC t1 
WHERE score IN (SELECT TOP 3 score 
           FROM SC 
           WHERE t1.C#= C# 
           ORDER BY score DESC) 
ORDER BY t1.C# 

--15、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 
FROM SC t1 
WHERE score IN (SELECT TOP 2 score 
          FROM SC 
          WHERE t1.C#= C# 
          ORDER BY score DESC ) 
ORDER BY t1.C#