SQL Server面试题

快来打我* 2021-09-10 05:52 315阅读 0赞

**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#