use 晚homework

1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。
select *from Student
select? Sname ,Ssex, Class from? Student?

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

select *from Teacher

select? distinct Depart? from Teacher

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

select? *from? Student

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

select *from score where? degree between 60 and 80

5、 查詢Score表中成績?yōu)?5，86或88的記錄。

select *from Score? where Degree in ('85','86','88' )
?
?
?6、 查詢Student表中“95031”班或性別為“女”的同學記錄。
?
?select? *from Student
?
?select? Class, Ssex from Student where Class='95031' or Ssex='女'
7、 以Class降序查詢Student表的所有記錄。
select *from Student? order by Class? desc

8、 以Cno升序、Degree降序查詢Score表的所有記錄。
select *from Score? order by Cno asc,Degree desc

9、 查詢“95031”班的學生人數(shù)。

select COUNT(*) from Student where Class ='95031'
10、?查詢Score表中的最高分的學生學號和課程號。（子查詢或者排序）
select *from Score
select Sno ,Cno from Score
where degree=(select? MAX (degree) from Score )
11、 查詢每門課的平均成績。
select AVG (degree) ,cno from Score group by cno
12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數(shù)。

select AVG(degree) from Score group by Cno having Cno like '3%'and COUNT(sno)>=5
13、查詢分數(shù)大于70，小于90的Sno列。
select sno from Score where Degree between 70 and? 90

select? sno from Score? where degree? between 70 and 90

14、查詢所有學生的Sname、Cno和Degree列。

select sname,cno ,degree from? Student
join Score? on Score.Sno? =Student .Sno

select sname ,cno,degree from Student
?join Score on Score.Sno? =Student .Sno

15、查詢所有學生的Sno、Cname和Degree列。
select sno,cname,DEGREE from Score
join Course on Course.Cno=Score.cno

16、查詢所有學生的Sname、Cname和Degree列。

select sname ,cname ,DEGREE from Student
?join score on score.sno=student.sno
join Course on Course.Cno=Score.cno

17、?查詢“95033”班學生的平均分。

select AVG (degree) from Score

join Student on Score .Sno=Student .Sno
where Class ='95033'

?

?

select?? AVG (degree) from Score?
join student on Score.Sno=Student .Sno
?
where Class ='95033'

18、 假設使用如下命令建立了一個grade表：
create table grade(low? int(3),upp? int(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)查詢所有同學的Sno、Cno和rank列。
19、? 查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。
select? degree from Score where? cno='3-105'and Degree>(select? Degree from Score where Cno ='3-105' and Sno ='109')

?

select *from Score? join Student on Student.Sno=Score .Sno
join? Course on Course.Cno=Score.Cno
join?? teacher on teacher.tno=course.tno
where course.cno='3-105'and degree>( select degree? from score where score.cno='3-105'and score.sno='109'? )
select
--20、查詢score中選學多門課程的同學中分數(shù)為非最高分成績的記錄。

select DEGREE from Score where Degree < (select max (Degree)? from Score? where COUNT (Cno )>2 order by? )
--21、 查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。
select
22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。
select? sno,sname,sbirthday from Student
where YEAR (Sbirthday )=YEAR ((select sbirthday from Student? where sno='108'))

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

24、查詢選修某課程的同學人數(shù)多于5人的教師姓名。
select tname from Teacher where tno in
?(select Tno? from Course where cno in(select cno? from Course? group by cno having COUNT(Cno ) >5))
25、查詢95033班和95031班全體學生的記錄。
select *from Student where Class ='95033' or Class ='95031'
26、? 查詢存在有85分以上成績的課程Cno.
select? distinct cno from Score where Degree >85

?

select? cno from score where degree >85
27、查詢出“計算機系“教師所教課程的成績表。
select DEGREE from Score where Cno in(
select cno from Course where Tno in
(select tno from Teacher where Depart ='計算機系'))
28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof。
select tname ,prof from Teacher where prof not in (select? prof from teacher where depart='計算機系'
and Prof? in(select Prof from Teacher? where Depart ='電子工程系'))

29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和Degree,并按Degree從高到低次序排序。
select cno,sno,DEGREE from Score where Cno ='3-105'and Degree >any(select Degree from Score where cno='3-245')
order by Degree? desc

30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和Degree.

select cno,sno,degree from Score where cno='3-105'and degree>all(select DEGREE from Score where Cno ='3-245')

31、?查詢所有教師和同學的name、sex和birthday.
select tname,tsex,tbirthday from Teacher
select sname,ssex,sbirthday from Student
32、查詢所有“女”教師和“女”同學的name、sex和birthday.
select tname,tsex,tbirthday from Teacher where Tsex ='女'
select sname,ssex,sbirthday from Student where Ssex ='女'

33、?查詢成績比該課程平均成績低的同學的成績表。
select DEGREE from Score where Degree < (select? AVG(Degree ) from score )
34、 查詢所有任課教師的Tname和Depart.
select? tname,depart from Teacher
35?、 查詢所有未講課的教師的Tname和Depart.

select tname ,depart from Teacher where Tno not in (select Tno from Course )
36、查詢至少有2名男生的班號。
select? class from Student where Ssex ='男' group by Class? having COUNT (Sno )>=2

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

select *from Student where Sname? not like '王%'
38、查詢Student表中每個學生的姓名和年齡。
select sname ,YEAR (GETDATE ())-YEAR (sbirthday ) from Student

39、查詢Student表中最大和最小的Sbirthday日期值。
select MAX (sbirthday),MIN (sbirthday) from Student
40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。
select *from Student? order by Class desc,Sbirthday desc
41、查詢“男”教師及其所上的課程。
select? tname ,(select cname from Course where Course .Tno =Teacher .Tno )from Teacher? where tsex='男'
42、查詢最高分同學的Sno、Cno和Degree列。
select sno,cno,DEGREE from Score? where Degree > (select? MAX (Degree )? from Score? ) order by? degree? desc
43、查詢和“李軍”同性別的所有同學的Sname.
select? sname from Student where Ssex=(select Ssex? where Sname ='李軍')
44、查詢和“李軍”同性別并同班的同學Sname.
select? sname from Student where Ssex=(select Ssex? where Sname ='李軍') and class=(select class where sname='李軍')
45、查詢所有選修“計算機導論”課程的“男”同學的成績表。
select degree? from?? Score???? where Sno in (select Sno from Student where Ssex ='男')and Cno in(select Cno from Course? where Cname ='計算機導論')
?

轉載于:https://www.cnblogs.com/wei270647220/p/4134405.html

總結

以上是生活随笔為你收集整理的表格 作业题练习的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。