資料庫查詢4

 1 --查詢所有任課教師的Tname和Depart.
 2 select t.tname 教師,c.cname 課程名 from teacher t,course c where t.tno = c.tno;
 3 --查詢Student表中不姓“王”的同學記錄。
 4 select * from student t where t.sname not like '王%';
 5 --查詢Student表中每個學生的姓名和年齡。
 6 select st.sname,extract(year from sysdate)-extract(year from st.sbirthday) as 年齡 from student st;
 7 --查詢Student表中最大和最小的Sbirthday日期值。
 8 select max(extract(day from st.sbirthday)) 最大, min(extract(day from st.sbirthday)) 最小 from student st;
 9 --查詢“男”教師及其所上的課程。
10 select t.tname 姓名,c.cname 課程 from teacher t,course c where t.tno = c.tno;
11 --查詢最高分同學的Sno、Cno和Degree列。
12 select * from score sc where sc.degree = (select max(degree) from score);
13 --查詢和“李軍”同性別的所有同學的Sname.
14 select st.sname from student st where st.ssex = (select s.ssex from student s where s.sname='李軍'); 
15 --查詢和“李軍”同性別並同班的同學Sname.
16 select st.sname from student st where st.ssex = (select st1.ssex from student st1 where st1.sname ='李軍')
17 and st.class = (select st1.class from student st1 where st1.sname = '李軍');
18 --查詢所有選修“電腦導論”課程的“男”同學的成績表。
19 select * from score sc where sc.cno = (select c.cno from course c where c.cname='電腦導論')
20 and sc.sno in (select st.sno from student st where st.ssex = '男'); 
21 --以班號和年齡從大到小的順序查詢Student表中的全部記錄。
22 select * from student st order by st.class desc,extract(year from sysdate)-extract(year from st.sbirthday) desc;
23 -- 查詢所有未講課的教師的Tname和Depart. 
24 select t.tname,t.depart from teacher t where t.tno =(
25 select c.tno from course c where c.cno not in(select sc.cno from score sc group by sc.cno));
26 --查詢至少有2名男生的班號。
27 select st.class from student st group by st.class having count(st.ssex)>=2;
28 --查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。
29 select st.sno,st.sname,st.sbirthday from student st where st.sno<>'108'and extract(year from st.sbirthday)=
30 (select extract(year from st2.sbirthday) from student st2 where st2.sno='108') ;