Student(Sid,Sname,Sage,Ssex)學(xué)生表

• Sid：學(xué)號
• Sname：學(xué)生姓名
• Sage：學(xué)生年齡
• Ssex：學(xué)生性別

Course(Cid,Cname,T#)課程表

• Cid：課程編號
• Cname：課程名稱
• Tid：教師編號

SC(Sid,Cid,score)成績表

• Sid：學(xué)號
• Cid：課程編號
• score：成績

Teacher(Tid,Tname)教師表

• Tid：教師編號：
• Tname：教師名字

1、查詢“001”課程比“002”課程成績高的所有學(xué)生的學(xué)號

select a.sid from 
(select sid,score from sc where cid='001')a,
(select sid,score from sc where cid='002')b 
where a.sid = b.sid and a.score>b.score;

2、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績

select sid,avg(score) from sc
group by sid 
having avg(score)>60;

3、查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績

select s.sid,s.sname,count_cid as 選課數(shù), 
sum_score  as 總成績
from student s
left join 
(select sid,count(cid) as count_cid,sum(score) as sum_score 
from sc group by sid )sc
on s.sid = sc.sid;

4、查詢姓‘李’的老師的個數(shù)：

select count(tname)
from teacher 
where tname like '李%';

5、查詢沒有學(xué)過“葉平”老師可的同學(xué)的學(xué)號、姓名：

select s.sid,s.sname 
from student as s 
where s.sid not in (
    select DISTINCT sid 
    from sc as sc 
    where sc.cid in (
        select cid 
        from course as c 
        left join teacher as t on c.tid = t.tid 
        where t.tname = '葉平')
);

6、查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號、姓名：

select s.sid,s.sname 
from student as s 
where s.sid in (
    select distinct sc.sid 
  from sc as sc 
  where sc.cid in (
    select cid 
    from course as c 
    left join teacher as t on c.tid = t.tid 
    where t.tname = '葉平')
        group by sc.sid 
    HAVING count(cid)=
    (select count(cid) 
     from course as c left join teacher as t on c.tid = t.tid 
     where t.tname = '葉平')
);

7、查詢學(xué)過“011”并且也學(xué)過編號“002”課程的同學(xué)的學(xué)號、姓名：

SELECT s.sid,s.sname 
from student as s 
left join sc as sc on s.sid = sc.sid
where sc.cid = '001'
and EXISTS(
  select * from sc as sc_2 
  where sc.sid = sc_2.sid 
  and sc_2.cid='002');
 
select s.sid,s.sname
from student as s 
left join sc as sc 
on sc.sid = s.sid
where sc.cid = '001'
and s.sid in (
  select sid from sc as sc_2 
  where sc_2.cid='002' 
  and sc_2.sid = sc.sid);

8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學(xué)的學(xué)號、姓名：

select sid,sname
from (select student.sid,student.sname,score,
     (select score from sc as sc_2 
      where sc_2.sid = student.sid 
      and sc_2.cid = '002') as score2 
      from student,sc 
      where student.sid=sc.sid and cid = '001') s_2
where score2<score;

9、查詢所有課程成績小于60的同學(xué)的學(xué)號、姓名：

select sid,sname
from student
where sid not in 
(select s.sid 
from student s,sc 
where s.sid=sc.sid and score>60 );
 
select sid,sname
from student s
where not EXISTS (
select s.sid from sc 
where sc.sid = s.sid and sc.score>60);

10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名：

select s.sid,s.sname
from student s ,sc sc 
where s.sid = sc.sid
group by s.sid,s.sname
having count(sc.cid)<(
select count(cid) 
from course);
 
select s.sid,s.sname
from student s 
right join sc sc on s.sid = sc.sid
group by s.sid,s.sname
having count(sc.cid)<
(select count(cid) from course);

以上就是“送你直通大廠企業(yè)之sql面試題及答案”，你能回答上來嗎?如果想要了解更多的Java面試題相關(guān)內(nèi)容，可以關(guān)注動力節(jié)點Java官網(wǎng)。