SQL语句面试题

Question

-- 学生表
create table Student (
Sid int primary key not null,
Sname varchar(32),
Sage int,
Ssex int
);

insert into Student(Sid,Sname,Sage,Ssex) values(001,'楚中天',19,1);
insert into Student(Sid,Sname,Sage,Ssex) values(002,'林蛋大',23,1);
insert into Student(Sid,Sname,Sage,Ssex) values(003,'张日天',20,0);

select * from Student;

-- 课程表
create table Course(
Cid int primary key not null,
Cname varchar(32) ,
Tid int
);

insert into Course(Cid,Cname,Tid) values(1,'数学',001);
insert into Course(Cid,Cname,Tid) values(2,'物理',002);
insert into Course(Cid,Cname,Tid) values(3,'化学',003);

select * from Course;

-- 成绩表
create table Sc(
Sid int not null,
Cid int ,
Score int
);

insert into Sc(Sid,Cid,Score) values(001,1,70);
insert into Sc(Sid,Cid,Score) values(001,2,60);
insert into Sc(Sid,Cid,Score) values(002,1,90);
insert into Sc(Sid,Cid,Score) values(002,2,100);
insert into Sc(Sid,Cid,Score) values(003,3,85);
insert into Sc(Sid,Cid,Score) values(003,3,30);

select * from Sc;

-- 教师表
create table teacher(
Tid int primary key not null,
Tname varchar(32)
);

insert into teacher(Tid,Tname) values(001,'叶萍');
insert into teacher(Tid,Tname) values(002,'鞠萍');
insert into teacher(Tid,Tname) values(003,'花萍');

select * from Student as 学生表;
select * from teacher as 教师表;
select * from Course as 课程表;
select * from Sc as 成绩表;

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

-- 查询学过“叶萍”老师所教的所有课程的同学学号、姓名；

-- 查询课程编号"002"的成绩比课程编号“001”课程低的所有同学的学号、姓名

-- 按平均成绩高到低显示所有学生的 "数学"、"物理"两门课程的成绩，以下形式显示：学生ID，数学，物理，平均分

上面是建表语句 直接粘入执行即可。 下面是问题 我也写了一遍但是感觉写的好复杂，求高手写一个简洁高性能的，万分感谢。

Answer 1

-- 查询所有同学的学号、姓名、选课数、总成绩；
select S.Sid,Sname,选课数=COUNT(Cid),总成绩=SUM(Score)
   from Student S join Sc on S.Sid=sc.Sid
  group by S.Sid,Sname

-- 查询学过“叶萍”老师所教的所有课程的同学学号、姓名；
select S.Sid,Sname
from Student S join Sc on S.Sid=Sc.Sid
    join Course C on C.Cid=Sc.Cid
join teacher T on T.Tid=C.Tid
where Tname='叶萍'
  group by S.Sid,Sname
      having COUNT(distinct C.Cid)=(select COUNT(Cid) from Course join teacher on Course.Tid=teacher.Tid where Tname='叶萍')

-- 查询课程编号"002"的成绩比课程编号“001”课程低的所有同学的学号、姓名
select Sid,Sname
from Student
where Sid in(select A.Sid
from Sc A join Sc B on A.Sid=B.Sid
where A.Cid=1 AND B.Cid=2 AND A.Score>B.Score)

-- 按平均成绩高到低显示所有学生的 "数学"、"物理"两门课程的成绩，以下形式显示：学生ID，数学，物理，平均分
select Sid,数学=max(case when Cname='数学' then Score end),物理=max(case when Cname='物理' then Score end),
平均分=(max(case when Cname='数学' then Score end)+max(case when Cname='物理' then Score end))/2
from Sc join Course C on Sc.Cid=C.Cid
  where Cname in('数学','物理')
group by Sid

Answer 2

SELECT *,
( SELECT COUNT(*) FROM (SELECT COUNT(*),b.sid FROM sc b LEFT JOIN course c ON b.Cid=c.Cid GROUP BY b.sid,c.tid ) ow WHERE ow.sid = a.sid ) as '
选课数量',
(SELECT sum(Score) FROM Sc d WHERE a.sid = d.sid )as '总成绩'
 FROM student a
;

SELECT d.sid,d.sname from teacher a
LEFT JOIN course b on a.tid = b.tid
LEFT JOIN sc c on c.cid  =  b.cid
left JOIN student d ON d.sid = c.sid
WHERE a.tname = '叶萍';

SELECT sid,sname
FROM student
WHERE sid in(select a.sid
FROM sc A left join sc b on a.sid=b.sid
WHERE a.cid=1 and b.cid=2 and a.score>b.score)

SELECT sc.Sid,sum(CASE WHEN c.Cname='
数学' then sc.Score else 0 end) 数学, SUM(case when c.Cname='物理
' THEN sc.Score ELSE 0 END) 物理,AVG(sc.Score) 平均分
FROM sc
INNER JOIN Course c on sc.Cid=c.Cid
WHERE c.Cname in ('数学','物理')
GROUP BYsc.Sid
ORDER BY AVG(sc.Score) DESC

insert into Sc(Sid,Cid,Score) values(003,3,85);

insert into Sc(Sid,Cid,Score) values(003,3,30);

首先这个不知道是你i写错了还是怎么  一个人化学成绩有2个  所以我在这里按照你这个上做的查询  所以有一个人是选了单个课程

Answer 3

select s.Sid,s.Sname,count(sc.Cid) countCourse,sum(sc.Score) sumscore
from Student s
left join Sc on s.Sid=sc.Sid
group by s.Sid,s.Sname

select s.Sid,S.Sname
from Student s
inner join sc on s.Sid=sc.Sid
inner join Course c on sc.Cid=c.Cid
inner join teacher t on c.Tid=t.Tid
where t.Tname = '叶萍'

select s.Sid, s.Sname
from Student s
inner join sc sc1 on s.Sid=sc1.Sid
inner join sc sc2 on sc1.Sid=sc2.Sid
where sc1.Cid='1'
and sc2.Cid='2'
and sc2.Score < sc1.Score

select sc.Sid,sum(case when c.Cname='数学' then sc.Score else 0 end) 数学, sum(case when c.Cname='物理' then sc.Score else 0 end) 物理,avg(sc.Score) 平均分
from sc
inner join Course c on sc.Cid=c.Cid
where c.Cname in ('数学','物理')
group by sc.Sid
order by avg(sc.Score) desc