数据库SQL笔试题目

大学生春季运动会的数据库，保存了比赛信息的三个表如下：
运动员 sporter（运动员编号 sporterid，姓名name，性别 sex，所属系号 department），
项目 item（项目编号 itemid，名称 itemname，比赛地点 location），成绩 grade（运动员编号 id，项目编号 itemid，积分 mark）。
用SQL语句完成在“体育馆”进行比赛的各项目名称及其冠军的姓名

SELECT 
    i.itemname, s.name
FROM
    grade g,
    (SELECT 
        itemid iid, MAX(mark) max
    FROM
        grade
    WHERE
        itemid IN (SELECT 
                itemid
            FROM
                item
            WHERE
                location = '体育馆')
    GROUP BY itemid) temp,
    item i,
    sporter s
WHERE
    g.itemid = temp.iid
        AND g.mark = temp.max
        AND temp.iid = i.itemid
        AND s.sporterid = g.sporterid;

注：这道题需要注意：在使用group by的时候， select后面字段要么出现在group by当中要么包含在聚合函数里面，常见的聚合函数有sum()、max()、min()、avg()、count()....

Mysql中表student_table(id,name,birth,sex)，插入如下记录：
('1003' , NULL , '2002-05-20' , '男');
('1004' , '张三' , '2000-09-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');

('1006' , NULL , '2001-12-02' , '女');

修改name字段为NULL的记录，是男生时设置name='男生姓名'，是女生时设置name='女生姓名'

update student_table set name  = ( case when sex = '男' then '男生姓名' when sex = '女' then '女生姓名' end) where name is null ;

注：知识点在于熟悉语法：case when .. then when ... then ... else ... end .其中case和end必不可少。

已知职员表employee(eno,ename,gender,birthday,salary)，现有一张E表，表结构与职员表一致，要求将E表中没有在职员表中出现的女职员添加到职员表中

INSERT INTO employee(eno,ename,gender,birthday,salary)

SELECT eno,ename,gender,birthday,salsry

FROM E

WHERE E.gender='女' and NOT EXISTS(

SELECT * FROM employee

WHERE employee.eno=E.eno)

在MySQL中，现有评分表evaluate（包含班级编号cid和分数point字段），有班级表grade（包含班级编号cid等字段），查询evaluate 表中有没有班级均分大于等于80分的，如果存在，则查询显示grade表按cid由大到小排名的前五行记录

SELECT * FROM grade

WHERE EXISTS (

SELECT cid,AVG(point) AS avg

FROM evaluate GROUP BY cid

HAVING avg>=80)

ORDER BY grade.cid DESC LIMIT 5 ;

注：知识点优先顺序必须是from-->where-->group by -->having --- >order by --> select，这里需要注意的是聚合函数和where一起使用，会出现错误，一般都会用having替代

Mysql中表student_table(id,name,birth,sex)，score_table(stu_id,subject_name,score)，查询多个学科的总分最高的学生记录明细以及总分

select t1.*, sco from student_table inner join (select stu_id, sum(score) as sco from score_table group by stu_id order by sco desc limit 1) as score_T on student_table.id = score_T.stu_id;

知识点：有关MYSQL连接逻辑均在图内

在MySQL中，现有学生表Student（包含学号,学生姓名,出生年月,性别）。查询该班级同名学生有几个人？

select count(Student.SId) from Student inner join (select Sname from Student group by Sname HAVING count(Sname) > 1) as s1 on s1.Sname = Student.Sname;

Select sum(t.q)  from (select count(Sname) q from student group by Sname having count(Sname)>1) t;

后面刷题都是在网上找到的

图解SQL面试题：经典50题 - 知乎

在MySQL中，现有学生表Student（包含学号,学生姓名,出生年月,性别）,成绩表：score(学号,课程号,成绩)。问有多少学生选了课程？

select count(*) from Student where SId in (select distinct(SId) from SC);

知识点：IN和exist的区别，当表a数据更多是推荐用in， In会先查b再查a。反之使用exist。 not exist和not in的区别，更推荐使用not exist，因为会走索引

查询两门以上不及格课程的同学的学号及其平均成绩

select Sid, avg(score) from SC where score < 60 group by Sid having count(Cid) > 2;

查询学生的总成绩并进行排名

select Sid, sum(score) as Stotal, RANK() OVER(ORDER BY sum(score) desc) ranking from SC group by SId order by Stotal desc ;

知识点：

RANK并列跳跃排名：并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。

DENSE_RANK并列连续排序：并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。

ROW_NUMBER连续排名：即使相同的值，依旧按照连续数字进行排名。

使用分段[10085]m,[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select Course.Cid,Course.Cname,sum(case when SC.score > 85 then 1 else 0 end) as '100-85',
sum(case when SC.score <= 85 and SC.score > 70 then 1 else 0 end) as '85-70',
sum(case when SC.score <= 70 and SC.score > 60 then 1 else 0 end) as '70-60',
sum(case when SC.score <= 60 then 1 else 0 end) as '<60' from Course right join SC on SC.Cid = Course.Cid 
group by SC.Cid,Course.Cname;

更多推荐

数据库SQL笔试题目