面试-MySQL(一)
JAVA 面试题(一)
JAVA 面试题(二)
JAVA 面试题(三)
JAVA 面试题(四)
JAVA 面试题(五)
JAVA 面试题(六)
JAVA 面试题(七)
JAVA 面试题(八)
JAVA 面试题(九)
Java-面试-逻辑题
Java-面试-逻辑题(二)
文章目录
题目一
课程表(course)-- 课程号(id)、课程名称(cname)、授课教师编号(teacher_id)
教师表(teacher)-- 教师编号(id)、教师名称(tname)
学生表(student)-- 学生编号(id)、姓名(sname)、年龄(age)、性别(sex)
成绩表(score)-- 学生编号(sid)、课程编号(cid)、考试成绩(score)
查询平均成绩大于60分的同学的学号和平均成绩 - 分组
select sid,avg(score) from score group by sid having avg(score) > 60;
查询所有同学的学号、姓名、选课数、总成绩
select sc.sid as '学号',
(select sname from student where id = sc.sid) as '学生姓名',
count(*) as '选课数',
sum(score) as '总成绩'
from score sc group by sid
查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
select t1.sid, (select sname from student where id = t1.sid) as name from
(select sid from score where cid = 1) t1
join
(select sid from score where cid = 2) t2
on t1.sid = t2.sid;
查询没学过“AA”老师课的同学的学号、姓名
#查询AA老师的id
select id from teacher where tname = 'AA'
#再查询AA老师教过的课程id
select id from course where teacher_id = (
#查询AA老师的id
select id from teacher where tname = 'AA'
)
select id,sname from student where id not in (
#查询学过AA老师课程的学生id
select sid from score where cid in (
#再查询AA老师教过的课程id
select id from course where teacher_id = (
#查询AA老师的id
select id from teacher where tname = 'AA'
)
)
);
题目二
现有一表数据如上所示,现要求按如下格式输出
select s.year as'年份',
(select sales from Sa where year = s.year and seasion = 1) as '第一季度',
(select sales from Sa where year = s.year and seasion = 2) as '第二季度',
(select sales from Sa where year = s.year and seasion = 3) as '第三季度',
(select sales from Sa where year = s.year and seasion = 4) as '第四季度'
from Sa s group by year;