面试:数据库基础面试题

基础表结构

student(sno,sname,sage,ssex) 学生表 
course(cno,cname,tno) 课程表 
sc(sno,cno,score) 成绩表 
teacher(tno,tname) 教师表

1、查询课程1的成绩比课程2的成绩高的所有学生的学号

select a.sno 
from
(select sno,score from sc where cno=1) a, 
(select sno,score from sc where cno=2) b 
where a.score>b.score and a.sno=b.sno

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

select a.sno as "学号", avg(a.score) as "平均成绩" 
from (select sno,score from sc) a
group by sno 
having avg(a.score)>60

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

select a.sno as 学号, b.sname as 姓名, count(a.cno) as 选课数, sum(a.score) as 总成绩 
from sc a, student b
where a.sno = b.sno 
group by a.sno, b.sname 
 
或者
 
select student.sno as 学号, student.sname as 姓名, count(sc.cno) as 选课数, sum(score) as 总成绩
from student 
left outer join sc on student.sno = sc.sno 
group by student.sno, sname

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

select count(tno)
from teacher
where tname like '张%'

5、查询没学过“张三”老师课的同学的学号、姓名

select student.sno,student.sname 
from student
where sno not in (select distinct(sc.sno) from sc,course,teacher where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

6、查询同时学过课程1和课程2的同学的学号、姓名

select sno, sname 
from student
where sno in (select sno from sc where sc.cno = 1) and sno in (select sno from sc where sc.cno = 2) 
 
或者
 
selectc.sno, c.sname 
from
(select sno from sc where sc.cno = 1) a, 
(select sno from sc where sc.cno = 2) b, 
student c
where a.sno = b.sno and a.sno = c.sno
 
或者
 
select student.sno,student.sname 
from student,sc 
where student.sno=sc.sno and sc.cno=1 and exists(select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

7、查询学过“李四”老师所教所有课程的所有同学的学号、姓名

select sno
-- 子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t)
from (
    -- 学过该老师课程的同学学号(包含只学一门)
    select sno,count(cno) num
    from sc
    where cno in (
        -- 该老师教了哪些课程
        select cno
        from course
        where tno in (
            select tno 
            from teacher
            where tname = '李四'
        )
    ) group by sno
) t
where t.num = (
    -- 统计该老师总课程数
    select count(cno)
    from course
    where tno = (
        select tno
        from teacher
        where tname = '李四'
    )
)

8、查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名

select a.sno, a.sname 
from student a, 
(select sno, score from sc where cno = 1) b, 
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno

 

9、查询和编号为1002的同学学习的课程完全相同的其他同学学号和姓名

select sc_1.sno
from (select cno from sc where sno='1002')a
left join sc sc_1 on a.cno = sc_1.cno
where sc_1.sno<>'1002' 
group by sc_1.sno 
having count(sc_1.cno) = 
(select count(cno) from sc where sno='1002');
 
select a.sno,s.sname from 
(select sno,GROUP_CONCAT(cno order by cno separator ',') as cid_str 
from sc where sno='1002')b,
(select sno,GROUP_CONCAT(cno order by cno separator ',') as cid_str 
from sc group by sno)a
left join student s 
on a.sno = s.sno
where a.cid_str = b.cid_str and a.sno<>'1002';

10、按平平均分从高到低显示所有学生的如下统计报表：学号，企业管理，马克思，UML，数据库，物理，课程数，平均分

select sno as 学号
,max(case when cno = 1 then score end) AS 企业管理
,max(case when cno = 2 then score end) AS 马克思
,max(case when cno = 3 then score end) AS UML
,max(case when cno = 4 then score end) AS 数据库
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 课程数
,avg(score) AS 平均分
FROM sc GROUP by sno
ORDER by avg(score) DESC

11、查询如下课程平均成绩和及格率的百分数(用"1行"显示)：企业管理（001），马克思（002），UML（003），数据库（004）

select
avg(case when cno = 1 then score end) as 平均分1,
avg(case when cno = 2 then score end) as 平均分2,
avg(case when cno = 3 then score end) as 平均分3,
avg(case when cno = 4 then score end) as 平均分4,
100 * sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,
100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,
100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,
100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4
from sc

 

以上内容来源于百科书,可以关注我了解更多.