数据库面试多表查询二

最新推荐文章于 2023-10-16 16:35:53 发布

勇者之无惧

最新推荐文章于 2023-10-16 16:35:53 发布

阅读量85

点赞数

分类专栏： java基础面试题文章标签： java 面试

本文链接：https://blog.csdn.net/qq_42378632/article/details/120497967

版权

java基础面试题专栏收录该内容

11 篇文章 1 订阅

订阅专栏

题目一
在这里插入图片描述

解答：

select avg(eglish) from mark;
select mark.math,cust.Name,cust.Address,cust.Telno from mark join cust on mark.studentno = cust.Studentno where cust.studentno in (11,22,33,44,55);
select cust.Name ,mark.computer form cust join mark on cust.Studentno = mark.studentno
order by mark.computer desc;
select cust.Studentno,cust.Name,english + math + computer total
from cust join mark on cust.Studentno = mark.studentno
having total > 240
order by total desc;

在这里插入图片描述

解答：
A. select count(‘王姓学生’) from Student where Student.name like ‘王%’;
B. select id from (
select
a.id
max(case when c.name = ‘语文’ then b.score end) chinese,
max(case when c.name = ‘数学’ then b.score end) math
from student a
join sc b on a.id = b.cid
join course c on c.id = b.cid
group by a.id
having math > chinese);

方法二：
Select a.*,math.score as 数学,chinese.score as 语文 from
student a,
(Select * from sc where cid=(Select id from course where name =‘数学’)) as math,
(Select * from sc where cid=(Select id from course where name =‘语文’)) as chinese
where a.id = math.sid
and math.sid = chinese.sid
and math.score>chinese.score;

C. select Student.id,avg(sores) from Student join SC on Student.id = SC.sid group by Student.id having avg(sores) > 90;