数据库面试多表查询二

题目一

解答：

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;