sql面试题（学生表_课程表_成绩表_教师表）mysql版

骑猪看日落 2022-03-28 09:29 316阅读 0赞

原帖链接:http://bbs.csdn.net/topics/280002741

建表语句：

CREATE TABLE student 
    ( 
         s_id    INT,
         sname varchar(32),
         sage  INT, 
         ssex  varchar(8) 
     );
    
    CREATE TABLE course 
      ( 
         c_id    INT, 
         cname varchar(32), 
         t_id    INT 
      );
    
    CREATE TABLE sc 
    ( 
         s_id  INT, 
         c_id  INT, 
         score INT 
    ) ;
    
    CREATE TABLE teacher 
    ( 
         t_id    INT, 
         tname varchar(16) 
    );

插入测试数据

insert into Student select 1,N'刘一',18,N'男' union all
     select 2,N'钱二',19,N'女' union all
     select 3,N'张三',17,N'男' union all
     select 4,N'李四',18,N'女' union all
     select 5,N'王五',17,N'男' union all
     select 6,N'赵六',19,N'女' ;
     
     insert into Teacher select 1,N'叶平' union all
     select 2,N'贺高' union all
     select 3,N'杨艳' union all
     select 4,N'周磊';
     
     insert into Course select 1,N'语文',1 union all
     select 2,N'数学',2 union all
     select 3,N'英语',3 union all
     select 4,N'物理',4;
     
     insert into SC 
     select 1,1,56 union all 
     select 1,2,78 union all 
     select 1,3,67 union all 
     select 1,4,58 union all 
     select 2,1,79 union all 
     select 2,2,81 union all 
     select 2,3,92 union all 
     select 2,4,68 union all 
     select 3,1,91 union all 
     select 3,2,47 union all 
     select 3,3,88 union all 
     select 3,4,56 union all 
     select 4,2,88 union all 
     select 4,3,90 union all 
     select 4,4,93 union all 
     select 5,1,46 union all 
     select 5,3,78 union all 
     select 5,4,53 union all 
     select 6,1,35 union all 
     select 6,2,68 union all 
     select 6,4,71;

常见问题

1、查询“001”课程比“002”课程成绩高的所有学生的学号； 
    	 select a.s_id FROM
    	 (select s_id,score from sc where c_id = '001')a,(select s_id,score from sc where c_id = '002')b
    	 where a.score > b.score and a.s_id = b.s_id;
    
    2、查询平均成绩大于60分的同学的学号和平均成绩； 
        select s_id,avg(score)
    	  from sc
        group by s_id
        having avg(score)>60;
    3、查询所有同学的学号、姓名、选课数、总成绩； 
       select Student.s_id,Student.Sname,count(SC.c_id),sum(score) 
       from Student left Outer join SC on Student.S_id=SC.S_id 
       group by Student.S_id,Sname ;
    4、查询姓“李”的老师的个数； 
    		select DISTINCT(count(tname))
    		from teacher
    		where tname like '李%';
    5、查询没学过“叶平”老师课的同学的学号、姓名； 
       select Student.S_id,Student.Sname 
       from Student  where s_id not in
       (select distinct( SC.S_id) from SC,Course,Teacher where  SC.C_id=Course.C_id and Teacher.T_id=Course.T_id and teacher.tname = '叶平')
       
        
    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 
    	
    	select student.s_id,student.sname from SC,student where  SC.s_id=student.s_id and SC.C_id='001'
      and EXISTS 
      (select * from SC as SC2  where  SC2.s_id=SC.s_id and SC2.C_id='002')
    
    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
       select s_id,sname
       from student
       where s_id in
       (select SC.S_id from SC,Course,Teacher where  SC.C_id=Course.C_id and Teacher.T_id=Course.T_id and teacher.tname = '叶平' 
       GROUP BY sc.s_id
       having count(SC.c_id) =
       (select count(c.c_id) from course c,teacher t
       where c.t_id=t.t_id and t.tname = '叶平')) ;
    	
      
    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； 
         select a.s_id,a.sname from
      	(select sc.s_id,sc.score,student.sname from SC,student where  SC.s_id=student.s_id and SC.C_id='001')a,
      	(select sc.s_id,sc.score from SC,student where  SC.s_id=student.s_id and SC.C_id='002')b
        where a.s_id = b.s_id and a.score> b.score;
    
    
    9、查询所有课程成绩小于60分的同学的学号、姓名； 
      select S_id,Sname 
      from Student 
      where S_id not in (select S.S_id from Student AS S,SC where S.S_id=SC.S_id and score>60); 
    
    10、查询没有学全所有课的同学的学号、姓名； 
      select s.s_id,s.sname
      from student s,sc sc
      where s.s_id = sc.s_id
      GROUP BY s.s_id,s.sname
      HAVING COUNT(sc.c_id) < (select count(c_id) from course)
        
    11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； 
    	 select  DISTINCT Student.s_id,Student.sname 
       from Student,sc where student.s_id = sc.s_id
    	 and sc.c_id in (select c_id from sc where s_id='1')