SQL基礎テストテーマ

1、完了照会は以下の表に表示され、すべての学生の情報が表示され、学生番号の昇順に並べられ、同じ学号に対して課程名のアルファベット順に表示される.
生徒番号
学生の名前
コース名
成績

select s.STUDNO " ",s.STUDNAME " ",c.COURSENAME " ",g.GRADE " "
from student s left join score g on(s.STUDNO=g.STUDNO) 
left join course c on (c.COURSEID=g.COURSEID)
order by s.STUDNO,c.COURSENAME;

score,student,courseの3つのテーブル間の等値接続でよい
2、照会による単科最高成績
生徒番号
学生の名前
コース名
単科最高成績
zhansan
c
98
tempは1つの臨時表で、中には各科の最高点が残っていて、それからstudent、score、courseと4枚の表の接続(select courseid、max(grade)maxgrade from score group by coursed)tempも分析関数で実現することができます

create table temp(" "," "," "," ") as
select s.STUDNO,s.STUDNAME,c.COURSENAME,g.GRADE
from student s,course c,
(select studno,courseid,grade from score 
where (grade,courseid) in (select max(grade),courseid from score group by courseid)) g
where s.STUDNO=g.STUDNO and c.COURSEID=g.COURSEID;

3、学生の授業に合格するか不合格かを調べる
生徒番号
学生の名前
コース名
試験に合格した状態
合格か不合格か

select s.STUDNO " ",s.STUDNAME " ",c.COURSENAME " ",
case when grade>=60 then ' '
else ' ' end " "
from student s,course c,score g 
where s.STUDNO=g.STUDNO and g.COURSEID = c.COURSEID;

case式の使用
4、学生選択科目の数を統計する
生徒番号
学生の名前
コース名

select s.STUDNO " ",s.STUDNAME " ",temp.c " "
from student s,(select count(courseid) c,studno from courseplan group by studno) temp
where s.STUDNO=temp.STUDNO;

5、単科の成績が課程の平均成績を超えた学生の情報を調べ、学生番号、学生名、課程名と課程成績をリストする.

select s.STUDNO " ",s.STUDNAME " ",c.COURSENAME " ",g.grade " " 
from student s,course c,score g,
(select courseid,avg(grade) avggrade from score group by courseid) gg
where s.STUDNO = g.STUDNO
and c.COURSEID   = g.COURSEID and gg.courseid=c.COURSEID and g.GRADE>gg.avggrade;

6、照会は追試が必要な学生の学生番号、学生の名前と課程名を表示する

SELECT s.STUDNO " ",s.STUDNAME " ",c.COURSENAME " ",g.grade " " 
FROM student s,course c,score g
where g.COURSEID = c.COURSEID
AND s.STUDNO   = g.STUDNO
and g.GRADE<60;

7、各科の成績の平均点を統計し、課程番号、課程名、平均点を表示する.

select c.COURSEID " ",c.COURSENAME " ",temp.avggrade " "
from course c ,
(select courseid,avg(grade) avggrade from score group by courseid) temp where c.COURSEID=temp.courseid;

8、Javaコースを選択した学生情報を検索する

select s.STUDNO " ",s.STUDNAME " ", s.BATCHCODE " "
from student s,score g,course c
where g.COURSEID = c.COURSEID
and s.STUDNO   = g.STUDNO
and c.COURSENAME='JAVA';

9、JAVA課程を選択していない学生情報を調べる

select s.STUDNO " ",s.STUDNAME " ", s.BATCHCODE " "
from student s,score g,course c
where g.COURSEID = c.COURSEID
and s.STUDNO   = g.STUDNO
and c.COURSENAME<>'JAVA';

10、教師の李可課程を選択した学生情報を調べる

select s.STUDNO " ",s.STUDNAME " "
from student s,
(select tt.studno from courseplan tt where tt.TEACHID=(select tea.TEACHID from TEACHERINFO tea where tea.TEACHNAME=' ')) bb
where s.STUDNO=bb.STUDNO;

11、A 01とA 02の2つの科目を同時に選択した学生の授業手配を調べ、学生番号、学生名前、クラス番号、授業番号、授業教師、授業日を表示する.

select s.STUDNO " ",s.STUDNAME " ",s.BATCHCODE " ",c.COURSEID " ",t.TEACHNAME " ",co.COURSEDT " "
from student s,course c,courseplan co,teacherinfo t
where s.STUDNO=co.STUDNO and c.COURSEID=co.COURSEID and t.TEACHID=co.TEACHID 
and (c.COURSEID='A01' or c.COURSEID='A02');

12、96571クラスにはどんな授業がありますか.いつどの教師が授業をしますか.

select s.BATCHCODE " ",c.COURSEID " ",c.COURSENAME " ",co.COURSEDT " ",t.TEACHNAME " "
from student s,course c,courseplan co,teacherinfo t,batch b
where c.COURSEID=co.COURSEID and t.TEACHID=co.TEACHID and s.STUDNO=co.STUDNO 
and s.BATCHCODE=b.BATCHCODE and b.BATCHCODE=96571;

13、月曜日に授業をしないクラスを調べる

select BATCHCODE " " from student s,
(select studno from courseplan where coursedt<>' ') tt 
where s.studno=tt.studno;

14、木曜日の授業の先生の名前を調べる

select DISTINCT t.TEACHNAME " " 
from teacherinfo t, courseplan co where co.TEACHID=t.TEACHID and co.COURSEDT=' ';

15、A 02課程の授業教師と授業時間を調べる

select t.TEACHNAME " ",co.COURSEID " ",co.COURSEDT " "
from courseplan co,teacherinfo t 
where co.TEACHID=t.TEACHID and co.COURSEID='A02';

16、各科目の不合格者数がこの科目の受験者数に占める割合を統計する

-- 
select courseid,count(studno) nopass from score where grade<60 group by courseid;
-- 
select courseid,count(studno) allstu from score group by courseid;
select c1.courseid,coursename,c1.nopass/c2.allstu*100 
from (select courseid,count(studno) nopass from score where grade<60 group by courseid) c1,(select courseid,count(studno) allstu from score group by courseid)c2,course 
where c1.courseid=course.courseid and c2.courseid=course.courseid;

2つの埋め込みビューを構築し、1つ目はある科目の合格者数を統計し、2つ目はある科目の総人数を統計し、2つの表は等値で接続すればよい.
17、すべての不合格者が受験生総数に占める割合を統計する

create or REPLACE view passcount as (select count(studno) count  from score   where grade<60  ) ;
create or REPLACE view allcount as (select count(studno) count  from SCORE ) ;
select TRUNC(pc.count/ac.count,2)*100||'%' from passcount pc,ALLCOUNT ac;

18、単科の成績が90点以上の学生はどのクラスですか.授業の教師は誰ですか.

select s.STUDNAME " ",b.BATCHNAME " ",g.COURSEID " ",t.TEACHNAME " "
from student s,courseplan co,teacherinfo t,score g,batch b
where s.BATCHCODE=b.BATCHCODE and s.STUDNO=g.STUDNO and
co.TEACHID=t.TEACHID and g.STUDNO=co.STUDNO and g.GRADE>90;

19、工業工学クラスの授業教師は誰ですか.

select t.TEACHNAME from batch b,student s,courseplan co,teacherinfo t
where b.BATCHCODE=s.BATCHCODE and s.STUDNO=co.STUDNO and co.TEACHID=t.TEACHID
and b.BATCHNAME like ' % ';

20、1068番の学生はいつ授業がありますか.

select coursedt from COURSEPLAN where STUDNO=1068;

21、どの学生の試験の成績が90点以上であるかを調べる

select s.STUDNO,s.STUDNAME,g.GRADE from 
student s,score g 
where s.STUDNO = g.STUDNO and g.GRADE>90

22、同時に2つの課程を超えた教師を検索する

select t.TEACHID,t.TEACHNAME from teacherinfo t,
(select teachid,count(courseid) ccount from courseplan group by teachid) tt
where tt.teachid>2 and t.TEACHID=tt.teachid;

23、各学生の試験成績の総点をまとめ、順位を並べる.学生番号、学生名、クラス番号、合計スコアを表示

select s.studno,s.studname,batchcode,temp.totalscore,rank() over(order by temp.totalscore desc) r 
from student s,(select studno,sum(grade) totalscore from score group by studno)temp 
where s.studno = temp.studno ;

24、クラスごとにグループ化して、学生の番号、学生の名前と総得点を表示して、1つのクラス内で総得点によって順位をつけます

select s.studno,s.studname,temp.totalscore,s.batchcode,rank() over( order by temp.totalscore desc)r
from student s,(select studno,sum(grade) totalscore from score group by studno order by totalscore) temp
where s.studno=temp.studno
order by s.batchcode,temp.totalscore desc;

基本的な考え方は分析関数でクラス内の学生の総成績ランキングを求め、student表と等値に接続し、名前情報を取得することです.