oracle student表,ORACLE(student)表習題與答案

因為答案都是小編自己寫的，解法可能有多種，如果您覺得我的解法有誤，希望您有時間給我留言。

題目：

1、 查詢student表中的所有記錄的sname、ssex和class列。

SELECT sname,ssex,class FROM student;

2、 查詢教師所有的單位即不重復的depart列。

SELECT distinct depart FROM t_teacher;

3、 查詢student表的所有記錄。

SELECT * FROM student;

4、 查詢t_score表中成績在60到80之間的所有記錄。

SELECT * FROM t_score where degree>60 and degree<80;

5、 查詢t_score表中成績為85，86或88的記錄。

SELECT * FROM t_score where degree in (85,86,88)

6、 查詢student表中“95031”班或性別為“女”的同學記錄。

SELECT * FROM student where class="95031" and ssex='女'

7、 以class降序查詢student表的所有記錄。

SELECT * FROM student order by class

8、 以cno升序、degree降序查詢score表的所有記錄。

SELECT * FROM (SELECT * FROM t_score order by cno ) order by degree desc

9、 查詢“95031”班的學生人數。

SELECT * FROM student where class="95031"

10、查詢score表中的最高分的學生學號和課程號。

SELECT sno,cno FROM t_score where degree=(SELECT max(degree) FROM t_score )

--記錄第一條： rownum= 1

11、查詢‘3-105’號課程的平均分。

SELECT avg(degree) FROM t_score where cno='3-105'

12、查詢score表中至少有5名學生選修的並以3開頭的課程的平均分數。

SELECT * FROM (SELECT cno FROM t_score group by cno having count(*)>5 ) where cno like'%3%'

13、查詢最低分大於70，最高分小於90的sno列。

SELECT sno FROM t_score where degree>70 and degree<90

14、查詢所有學生的sname、cno和degree列。

SELECT t1.sname,t2.cno,t2.degree FROM student t1,t_score t2 where t1.sno=t2.sno

--學習表

SELECT * FROM student;

--課表表

SELECT * FROM t_course;

--分數表

SELECT * FROM t_score ;

15、查詢所有學生的sno、cname和degree列。

SELECT t1.sname,t2.sno,t2.degree FROM student t1,t_score t2 where t1.sno=t2.sno

16、查詢所有學生的sname、cname和degree列。

--SELECT * FROM t_course;

--SELECT * FROM t_score;

--SELECT * FROM student;

--SELECT sname FROM student;

--SELECT cname FROM t_course;

--SELECT degree FROM t_score;

SELECT t3.sname,t1.cname,t2.degree FROM t_course t1

inner join t_score t2 on t1.cno=t2.cno

inner join student t3 on t3.sno=t2.sno

17、查詢“95033”班所選課程的平均分。

SELECT avg(degree) FROM (SELECT tt.class,yy.degree FROM student tt ,t_score yy where tt.sno=yy.sno) group byclass having class="95033" ;

18、查詢所有同學的sno、cno和rank列。

--着題是多字段關鏈

SELECT t1.sno,t1.cno,t2.rank FROM t_score t1 left join t_grade t2 on t1.degree<=t2.upp and t1.degree>=t2.low

SELECT * FROM t_score

--評級

SELECT * FROM t_grade;

19、查詢選修“3-105”課程的成績高於“109”號同學課程最高成績的所有同學的記錄。

SELECT degree FROM t_score where cno= '3-105' and degree>(

SELECT max(degree) FROM t_score where sno=109 )

20、查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。

SELECT * FROM t_score where cno in (

SELECT cno FROM t_score group by cno having count(*)>1) ORDER BY degree desc

21、查詢成績高於學號為“109”、課程號為“3-105”的成績的所有記錄。

SELECT * FROM (

SELECT * FROM t_score where cno='3-105')where degree>(SELECT degree FROM t_score where sno=109 and rownum=1 )

22、查詢和學號為108的同學同年出生的所有學生的sno、sname和sbirthday列。

SELECT * FROM student where sno=108;

23、查詢“張旭“教師任課的學生成績。

SELECT pp.sno,ii.sname,pp.degree,pp.cno

FROM t_course oo,t_score pp ,student ii

where tno=(

SELECT tno FROM t_teacher where tname='張旭') and oo.cno=pp.cno and ii.sno=pp.sno

24、查詢選修某課程的同學人數多於5人的教師姓名。

SELECT hh.tname FROM t_course gg left join t_teacher hh on gg.tno=hh.tno where cno=(

SELECT cno FROM t_score group by cno having count(sno)>5

)

25、查詢95033班和95031班全體學生的記錄。

SELECT vv.*,cc.cno,cc.degree FROM student vv left join t_score cc on vv.sno=cc.sno where class="95033" or class="95031"

26、查詢存在有85分以上成績的課程cno.

SELECT dd.*,ss.cname FROM t_score dd left join t_course ss on dd.cno=ss.cno where degree >85

27、查詢出“計算機系“教師所教課程的成績表。

--課程

SELECT * FROM t_score where cno in (

SELECT cno FROM t_course where tno in (

SELECT tno FROM t_teacher where depart='計算機系'

)

)

28、查詢“計算機系”與“電子工程系“不同職稱的教師的tname和prof。

SELECT * FROM t_teacher where prof in (SELECT prof FROM t_teacher ui group by prof having count(*)<2) and depart in ('電子工程系','計算機系')

29、查詢選修編號為“3-105“課程且成績至少高於選修編號為“3-245”的同學的cno、sno和degree,並按degree從高到低次序排序。

SELECT * FROM t_score where cno='3-245' and degree>(SELECT degree FROM t_score where cno='3-105') order by degree

--等一下再做

30、查詢選修編號為“3-105”且成績高於選修編號為“3-245”課程的同學的cno、sno和degree.

select * from t_score a where cno = '3-105' and a.degree>(

select b.degree from t_score b where cno = '3-245'and a.sno=b.sno)

31、查詢所有教師和同學的name、sex和birthday.

SELECT t1.tname,t1.tsex,t1.tbirthday,t4.sname,t4.ssex,t4.sbirthday FROM t_teacher t1

inner join t_course t2 on t1.tno=t2.tno

inner join t_score t3 on t2.cno=t3.cno

inner join student t4 on t3.sno=t4.sno

32、查詢所有“女”教師和“女”同學的name、sex和birthday.

SELECT t1.tname,t1.tsex,t1.tbirthday,t4.sname,t4.ssex,t4.sbirthday FROM t_teacher t1

inner join t_course t2 on t1.tno=t2.tno

inner join t_score t3 on t2.cno=t3.cno

inner join student t4 on t3.sno=t4.sno where t4.ssex='女' and t1.tsex='女'

33、查詢成績比該課程平均成績低的同學的成績表。

SELECT * FROM t_score q1 inner join (

SELECT cno,avg(degree) degree FROM t_score group by cno) q2 on q1.cno=q2.cno where q1.degree>q2.degree

34、查詢所有任課教師的tname和depart.

SELECT distinct y1.tname,y1.depart FROM t_teacher y1 left join t_course y2 on y1.tno=y2.tno

35、查詢所有未講課的教師的tname和depart.

SELECT distinct y1.tname,y1.depart FROM t_teacher y1 left join t_course y2 on y1.tno!=y2.tno

36、查詢至少有2名男生的班號。

SELECT * FROM student where class in (SELECT class FROM student group by class having count(*)>2) and ssex='男'

37、查詢student表中不姓“王”的同學記錄。

SELECT * FROM student where sname not like '%王%'

38、查詢student表中每個學生的姓名和年齡。

SELECT sname,to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy') FROM student

39、查詢student表中最大和最小的sbirthday日期值。

SELECT max(sbirthday),min(sbirthday) FROM student ;

40、以班號和年齡從大到小的順序查詢student表中的全部記錄。

SELECT sno,sname,ssex, class,to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')nl FROM student ORDER BY class,nl desc

41、查詢“男”教師及其所上的課程。

SELECT ii.cname FROM t_course ii inner join (

SELECT tno FROM t_teacher where tsex='男'

) oo on ii.tno=oo.tno

42、查詢最高分同學的sno、cno和degree列。

SELECT tt.* FROM (

SELECT sno,cno,degree FROM t_score ORDER BY degree desc)tt where rownum=1

43、查詢和“李軍”同性別的所有同學的sname.

SELECT sname FROM student where ssex=(

SELECT ssex FROM student where sname='李軍')

44、查詢和“李軍”同性別並同班的同學sname.

SELECT tq.*,qw.class FROM student tq inner join (

SELECT ssex,class FROM student where sname='李軍') qw on tq.ssex=qw.ssex and tq.class=qw.class

45、查詢所有選修“計算機導論”課程的“男”同學的成績表

SELECT fg.*,gh.degree,gh.sno,hj.ssex FROM t_course fg

inner join t_score gh on fg.cno=gh.cno

inner join student hj on gh.sno=hj.sno

where cname='計算機導論' and hj.ssex='男'

46、查詢出選修課程號為3-245和6-166的課程的學生學號與姓名

SELECT vc.sname,vc.sno FROM student vc inner join t_score xc on vc.sno=xc.sno where cno in ('3-245','6-166')

47、查詢出沒有選修課程號為3-245和6-166的課程的學生學號與姓名

SELECT distinct hd.sno,dd.sname FROM t_score hd left join student dd on hd.sno=dd.sno where cno!='3-245' or cno!='6-166'

48、查詢學號為'101'的學生的所有課程的成績，如果沒有選修該課程，則成績為0

select nvl(sno,'101'),cname,nvl(degree,0) from

t_score t2 right join t_course t1

on t1.cno=t2.cno and t2.sno='101'

附加題：

查詢 班級為3-105所有學生的成績

SELECT q1.sno,q1.cno,q3.degrees FROM t_score q1 inner join (

SELECT sno ,sum(degree)degrees FROM t_score group by sno

) q3 on q1.sno=q3.sno where cno='3-105'