面试常见查询语句(能掌握这几个例子就可以了)

时间:2022-05-17 21:27:48

摘要: 表结构: student(s#,sname,sage,ssex)学生表 course(c#,cname,T#)课程表 sc(s#,c#,score)成绩表 Teacher(T#,tname)教师表 1.查询001课程比002课程成绩高的所有学生的学号: select sc1.s# from sc sc1 join sc sc2 on sc1.s# = sc2.s

表结构: 

student(s#,sname,sage,ssex)学生表 
course(c#,cname,T#)课程表 
sc(s#,c#,score)成绩表 
Teacher(T#,tname)教师表
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

1.查询001课程比002课程成绩高的所有学生的学号: 

select sc1.s# 
from sc sc1 join sc sc2 on sc1.s# = sc2.s# 
where sc1.c# = ‘001’ and sc2.c# = ‘002’ and sc1.score > sc2.score
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

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

select s#, avg(score) from sc group by s# having avg(score)>60;
  • 1
  • 1

3.查询所有同学的学号,姓名,选课数,总成绩: 

select student.s#,student.name ,count(sc.c#) ,sum(sc.score) from student left join sc on student.s# = sc.s# 
  • 1
  • 2
  • 1
  • 2

注:内连接要计算笛卡尔积,这里使用左外连接效率更高

4.查询姓李的老师个数: 

select count(T#) from Teacher where tname like “李%” 
  • 1
  • 1

注:模糊查询, %代表任意字符,_代表任一单个字符,[……]代表是括号中字符中的单个字符, [^……]代表不是括号中字符中的单个字符

5.查询没有学过叶萍老师课的同学学号,姓名: 

select studet.s# student.sname from student where s# not in (select distinct(sc.s#) from sc, course, teacher where sc.c# = course.c# and teacher.t# = course.t# and teacher.tname = “叶萍”); 
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

三表联合 + 子查询

6.查询学过001和002课程的同学的姓名学号: 

select student.s3 student .sname from student join course on student.s# = course.s# where(course.c# = ‘001union course.c# = ‘002’);
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

7.查询学过叶萍老师课的同学的学号,姓名: 

select student.s# student.sname from student where s# in(select distinct(sc.s#) from sc course teacher where sc.c# = course.c# and teacher.t# = course.t# and teacher.tnmae = “叶萍”) 
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

注:distinct为去重函数

8.查询002成绩比001低的同学的学号,姓名: 

select student.s# student .name from student where student.s# in(select sc1.s# from sc sc1 join sc sc2 on sc1.s# = sc2.s# where s1.c# = 001 and s2.c# = 002 and sc1.score> sc2.score);
  • 1
  • 2
  • 1
  • 2

9.查询所有课程成绩小于60分的同学的学号,姓名: 

select student.s# student.sname from student join sc on student.s# = sc.s# where sc.score > 60;
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

10.查询没有学全 所有课的同学的学号和姓名: 

select student.s# student.sname from student,sc where student.s# = sc.s# group by student.s#,student.sname having count(c#) < (select count(c#)from course);
  • 1
  • 2
  • 1
  • 2

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

select student.s# ,student.sname from student join sc where student.s# = sc.s# and c# in select c# from sc where s# = ‘1001
  • 1
  • 2
  • 1
  • 2

12.查询至少学过学号为001同学所有一门课的其他同学的学号和姓名: 

select distinct student.s#,student,sname from SC join student on SC.s# = student.s# where c# in(select c# from sc where s#=’001’);
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3