18、假設(shè)使用如下命令建立了一個(gè)grade表：

create table grade(low number(3,0),up number(3),rank char(1));

insert into grade values(90,100,’A’);

insert into grade values(80,89,’B’);

insert into grade values(70,79,’C’);

insert into grade values(60,69,’D’);

insert into grade values(0,59,’E’);

現(xiàn)查詢所有同學(xué)的Sno嚷堡、Cno和rank列

select sc.sno,sc.cno,g.rank from score sc
join grade g
on/where sc.degree between g.low and g.up;

題目18

19哗总、查詢選修“3-105”課程的成績高于“109”號(hào)同學(xué)成績的所有同學(xué)的記錄

select a.* from score a where a.cno='3-105' and a.degree>all(select degree
from score b where b.sno='109' and b.cno='3-105');

題目19

20琳轿、查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄

select * from score sc where degree<(select max(degree) from score) 
group by sno having count(sno)>1
order by degree;

題目20

21户侥、查詢成績高于學(xué)號(hào)為“109”剔猿、課程號(hào)為“3-105”的成績的所有記錄
<b>同19題</b>
22癞松、查詢和學(xué)號(hào)為108的同學(xué)同年出生的所有學(xué)生的Sno爽撒、Sname和Sbirthday列

select sno,sname,sbirthday from student where year(sbirthday)=
(select year(sbirthday) from student where sno='108');

題目22

23、查詢“張旭“教師任課的學(xué)生成績

select sc.sno,sc.degree from score sc
join(teacher t,course c)
on sc.cno=c.cno and t.tno=c.tno
where t.tname='張旭';

題目23

24拦惋、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名

 select t.tname from teacher t join (course c,score sc)
 on (t.tno=c.tno and c.cno=sc.cno)
 group by sc.cno having count(sc.cno) >5;

題目24

25匆浙、查詢95033班和95031班全體學(xué)生的記錄

select * from student
where class in ('95033','95031');

題目25

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

解法一

select distinct cno from score where degree > 85;

解法二

 select cno from score group by cno having max(degree)>85;

題目26

27厕妖、查詢出“計(jì)算機(jī)系“教師所教課程的成績表

解法一

select sc.* from score sc
join (teacher t,course c)
on sc.cno = c.cno and t.tno=c.tno
where t.depart = '計(jì)算機(jī)系';

解法二

select * from score where cno in
(select c.cno from course c join teacher t
on c.tno=t.tno and t.depart='計(jì)算機(jī)系');

題目27

28首尼、查詢“計(jì)算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof

 select tname,prof from teacher 
where depart='計(jì)算機(jī)系' and prof not in 
(select prof from teacher where depart='電子工程系');

題目28

29、查詢選修編號(hào)為“3-105“課程且成績至少高于選修編號(hào)為“3-245”的同學(xué)的Cno言秸、Sno和Degree,并按Degree從高到低次序排序软能。

select cno,sno,degree from score
where degree >=(select min(degree) from score where cno='3-245')
<!-- any(select degree)-->
and cno='3-105'
order by degree desc;

題目29

30、查詢選修編號(hào)為“3-105”且成績高于選修編號(hào)為“3-245”課程的同學(xué)的Cno举畸、Sno和Degree.

select cno,sno,degree from score
where degree >=(select max(degree) from score where cno='3-245')
and cno='3-105'
order by degree desc;

題目30

31查排、查詢所有教師和同學(xué)的name、sex和birthday.

<!-- as 可以去掉-->
select sname as name,ssex as sex,sbirthday as birthday from student
union
select tname as name,tsex as sex,tbirthday as birthday from teacher;

題目31

32抄沮、查詢所有“女”教師和“女”同學(xué)的name跋核、sex和birthday.

<!-- as 可以去掉-->
select sname as name,ssex as sex,sbirthday as birthday from student
where ssex = '女'
union
select tname as name,tsex as sex,tbirthday as birthday from teacher
where tsex = '女';

題目32

33、查詢成績比該課程平均成績低的同學(xué)的成績表叛买。

select sc.* from score sc 
where degree <(select avg(degree) from score sc2 
where sc.cno=sc2.cno);

題目33

34砂代、查詢所有任課教師的Tname和Depart.

select t.tname,t.depart from teacher t 
join course c on t.tno=c.tno;

題目34

35、查詢所有未講課的教師的Tname和Depart.

關(guān)聯(lián)字段名相同可用using率挣，等同于 a.字段1=b.字段1

select tname,depart from teacher t 
left join course c using(tno) where isnull (c.tno);

題目35

36刻伊、查詢至少有2名男生的班號(hào)。

select class from student s where ssex='男'
group by class having count(ssex)>1;

題目36

37椒功、查詢Student表中不姓“王”的同學(xué)記錄

select * from student where sname not like '王%';

題目37

38捶箱、查詢Student表中每個(gè)學(xué)生的姓名和年齡

 select sname,(year(now())-year(sbirthday)) age from student;

題目38

39、查詢Student表中最大和最小的Sbirthday日期值

select sname,sbirthday from student where sbirthday = (select max(sbirthday) from student) 
union
select sname,sbirthday from student where sbirthday = (select min(sbirthday) 
from student);

題目39

40动漾、以班號(hào)和年齡從大到小的順序查詢Student表中的全部記錄

select class,(year(now())-year(sbirthday)) age from student 
order by class desc,age desc;

題目40

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

select t.tname,c.cname from teacher t 
join course c using(tno) where t.tsex='男';

題目41

42、查詢最高分同學(xué)的Sno旱眯、Cno和Degree列

select sno,cno,degree from score 
where degree = (select max(degree) from score);

題目42

43悦屏、查詢和“李軍”同性別的所有同學(xué)的Sname

select sname from student
where ssex = (select ssex from student where sname='李軍');

select s.sname from student s
where ssex = (select s2.ssex from student s2 where s2.sname='李軍');

題目43

44节沦、查詢和“李軍”同性別并同班的同學(xué)Sname.

select sname from student 
where ssex=(select ssex from student where sname='李軍')
and class=(select class from student where sname='李軍');

select a.sname from student a
where ssex=(select b.ssex from student b where b.sname='李軍')
and class=(select c.class from student c where c.sname='李軍');

題目44

45键思、查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表

解法1

select * from score 
where sno in(select sno from student where ssex='男') 
and cno=(select cno from course where cname='計(jì)算機(jī)導(dǎo)論');

解法2

select sc.* from score sc
join (student s,course c) using(sno,cno)
where s.ssex='男'and c.cname='計(jì)算機(jī)導(dǎo)論';

題目45