实训数据库开发应用——SQL查询

1．分别查询学生表和学生修课表中的全部数据。

select*fromstudent

select*fromsc

2．查询计算机系的学生的姓名、年龄。

selectsname,sagefromstudentwheresdept='计算机系'

3．查询选修了c01号课程的学生的学号和成绩。

selectsno,gradefromscwherecno='c01'

4.查询成绩在70到80分之间的学生的学号、课程号和成绩。

selectsno,cno,gradefromscwheregradebetween70and80

5．查询计算机系年龄在18到20之间且性别为'男'的学生的姓名、年龄。

selectsname,sagefromstudentwheresdept='计算机系'andssex='男'andsagebetween18and20

selectsname,sagefromstudentwheresdept='计算机系'andsage>=18andsage<=20andssex='男'

6．查询9512101号学生的修课情况。

select*fromscwheresno='9512101'

7．查询c01号课程成绩最高的分数。

selectmax(grade)asmax_gradefromscwherecno='c01'

8．查询学生都修了那些课程,要求列出课程号。

selectdistinctcnofromsc

9.查询Northwind数据库中orders表的OrderID、CustomerID和OrderDate，并将最新的定购日期（OrderDate）列在前边。

selectOrderID,CustomerID,OrderDatefromOrdersorderbyOrderDatedesc

10.查询Northwind数据库中orders表的ShipCountry列以B,C,D,F开始且第三个字符为'a'的OrderID、CustomerID和ShipCountry的信息。

selectOrderID,CustomerID,ShipCountryfromOrders

whereShipCountrylike'[BCDF]_a%'

11.查询Northwind数据库中orders表的ShipCountry列不以A,B,C,D,E,F开始且最后一个字母是'a'的OrderID、CustomerID和ShipCountry的信息。

selectOrderID,CustomerID,ShipCountryfromOrders

whereShipCountrylike'[^A-F]%a'

12.查询学生的最大的年龄和最小的年龄

selectmax(ssex)as最大年龄,min(ssex)as最小年龄fromstudent

13．查询修了c02号课程的所有学生的平均成绩、最高成绩和最低成绩。

selectavg(grade)as平均成绩,max(grade)as最高成绩,min(grade)as最低成绩

fromscwherecno='c02'

selectavg(grade)as平均成绩,最高成绩=max(grade),min(grade)最低成绩fromscwherecno='c02'

14.统计每个系的学生人数。

selectsdept,count(*)as学生人数fromstudentgroupbysdept

15.统计每门课程的修课人数和考试最高分。

selectcno课程号,count(*)修课人数,max(grade)最高分fromscgroupbycno

16.统计每个学生的选课门数，并按选课门数的递增顺序显示结果。

selectsno,count(cno)as选课门数fromscgroupbysnoorderby选课门数

17.统计各系修课的学生总数和考试的平均成绩

selectsdept,count(distinctsc.sno),avg(grade)

fromstudentsjoinscons.sno=sc.sno

groupbysdept

18.查询选课门数超过2门的学生的平均成绩和选课门数。

selectsno,sum(grade)总成绩,avg(grade)平均成绩,count(*)选课门数fromsc

groupbysnohavingcount(*)>2

19.列出总成绩超过200分的学生，要求列出学号、总成绩。

selectsno,sum(grade)总成绩fromsc

groupbysnohavingsum(grade)>200

20.查询pubs数据库的titles表中每类图书的平均价格超过12.0元的书的类型、平均价格和最高价格，要求只计算有确定价格的图书的情况。

selecttype类型,avg(price)平均价格,max(price)最高价格fromtitles

wherepriceisnotnull

groupbytype

havingavg(price)>12.0

21.查询pubs数据库的titles表中版税（royalty）为10的每类图书（type）的平均价格。

selecttype类型,avg(price)平均价格

fromtitleswhereroyalty=10

groupbytype

22.查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。

selecttype类型,sum(price)总价格

fromtitles

groupbytypehavingcount(*)>3

23.查询选修了c02号课程的学生的姓名和所在系。

selectsname,sdeptfromStudentjoinSConStudent.Sno=SC.Sno

wherecno='c02'

24.查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。

selectsname,cno,grade

fromstudentsjoinscons.sno=sc.sno

wheregrade>80

orderbygradedesc

25.查询计算机系修了"数据库基础"的学生的姓名、性别、成绩。

(查询计算机系没有选修"数据库基础"的学生的姓名、性别、和其选修课程的成绩。)

selectsname,ssex,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheresdept='计算机系'

andcname='数据库基础'

selectsname,ssex,grade

fromstudentjoinsconstudent.sno=sc.sno

wheresdept='计算机系'andsc.snonotin(

selectsnofromscjoincourseconc.cno=sc.cno

wherecname='数据库基础')

26.查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、修课号、修课成绩。

selects.sno,sname,cno,gradefromStudentsleftjoinSC

ons.Sno=SC.Sno

27.列出"数据库基础"课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

selecttop3withtiess.sno,sname,sdept,grade

fromStudentsjoinSCons.Sno=SC.Sno

joinCourseconc.Cno=SC.Cno

wherecname='数据库基础'

orderbygradedesc

28.查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。

selectt1.sno,t2.sno,t1.cno

fromscast1joinscast2

ont1.cno=t2.cno

wheret1.sno<t2.sno

29.查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年龄。

selectt1.sname,t2.sname,t1.sage

fromstudentast1joinstudentast2

ont1.sage=t2.sage

wheret1.sname<t2.sname

30.查询哪些课程没有人选，要求列出课程号和课程名。

selectc.cno,cnamefromcoursecleftjoinsc

onc.cno=sc.cno

wheresc.cnoisnull

selectcno,cnamefromcourse

wherenotexists

(select*fromsc

wherecno=course.cno)

31.查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表（假设新表名为new_sc）中，新表的列名分别为：Student_Name,Course_Name,Grade。

selectsnameStudent_Name,cnameCourse_Name,Grade

intonew_sc

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheregradeisnotnull

32.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

selectsdept系名,sname姓名,ssex性别,cname修课名称,grade修课成绩

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheresdept='信息系'

UNION

selectsdept,sname,ssex,cname,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheresdept='计算机系'

33.在Northwind数据库中，查询雇员（Employees表）和顾客（Customers表）

都来自哪些城市（City)，并降结果按城市的字母升序排序

selectdistinctcityfromEmployees

union

selectdistinctcityfromCustomers

orderbycityasc

34.在Northwind数据库的产品表（Products）中，查询库存数量（UnitsInStock）大于10的产品的编号（ProductID）、产品名（ProductName）和单价（UnitPrice），

并将单价小于等于10元的显示为“很便宜”；

单价超过10元但小于等于20元的显示为“较便宜”；

单价超过20元但小于30元的显示为“中等”；

单价超过30元但小于40元的显示为“较贵”；

单价超过40元但小于100元的显示为“很贵”；

单价超过100元的显示为“价格过高”。

selectProductID,ProductName,

case

whenUnitPrice<=10then'很便宜'

whenUnitPrice>10andUnitPrice<=20then'较便宜'

whenUnitPrice>20andUnitPrice<=30then'中等'

whenUnitPrice>30andUnitPrice<=40then'较贵'

whenUnitPrice>40andUnitPrice<=100then'很贵'

whenUnitPrice>100then'价格过高'

end

fromproducts

whereUnitsInStock>10

35.查询选修了vb课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：

当所在系为“计算机系”时，显示“CS”；

当所在系为“信息系”时，显示“IS”；

当所在系为“数学系”时，显示“MA”；

对其他系，均显示“OTHER”。

selects.sno学号,sname姓名,

casesdept

when'计算机系'then'CS'

when'信息系'then'IS'

when'数学系'then'MA'

else'OTHER'

end所在系,grade成绩

fromstudentsjoinscons.sno=sc.sno

joincourseconsc.cno=c.cno

wherecname='vb'

36.用子查询实现如下查询:

（1）查询选修了c01号课程的学生的姓名和所在系。

selectsname,sdeptfromstudent

wheresnoin(

selectsnofromscwherecno='c01')

（2）查询数学系成绩80分以上的学生的学号、姓名。

selectsno,snamefromstudent

wheresnoin(

selectsnofromscwheregrade>80)

andsdept='数学系'

（3）查询计算机系学生所选的课程名。

selectcnamefromcourse

wherecnoin(

selectcnofromsc

wheresnoin(

selectsnofromstudentwheresdept='计算机系'))

（4）查询"VB"课程考试成绩前三名的学生的学号、姓名、所在系。

selectsno,sname,sdeptfromstudent

wheresnoin(selecttop3withtiessnofromscwherecnoin(

selectcnofromcoursewherecname='VB')

orderbygradedesc)

37.查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。

selectsname,cname,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheresdept='计算机系'

andgrade>(

selectavg(grade)fromsc)

38.查询计算机系VB成绩最低的学生的姓名、所在系和VB成绩

方法一：

selectsname,sdept,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheregrade=(

selectmin(grade)fromsc

wherecnoin(

selectcnofromcoursewherecname='vb')

andsnoin(

selectsnofromstudentwheresdept='计算机系'))

andcname='VB'

andsdept=’计算机系’

方法二：

selectsname,sdept,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheregrade=(

selectmin(grade)fromscjoincourseconc.cno=sc.cnojoinstudentsons.sno=sc.sno

wherecname='vb'andsdept='计算机系')

andcname='VB'

andsdept='计算机系'

38题改动--查询计算机系成绩最低的学生的姓名、所在系和此门最低课程名及成绩

selectsname,sdept,cname,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

where　gradein(selectmin(grade)fromsc

wheresnoin(

selectsnofromstudentwheresdept='计算机系')

)　andsdept='计算机系'

38题改动--查询计算机系成绩最低的学生的姓名、所在系和VB成绩

selectsname,sdept,grade

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheres.snoin(

selectsnofromsc

wheregrade=(

selectmin(grade)fromsc

wheresnoin(

selectsnofromstudentwheresdept='计算机系')))

andcname='vb'

andsdept='计算机系'

39.创建一个新表test_t，其结构为：（COL1,COL2,COL3），其中：

COL1：整型，允许空值；

COL2：字符型，长度为10，不允许空值，

COL3：字符型，长度为10，允许空值，

试写出按行插入如下数据的语句（空白处表示不提供值），并注意插入后表中的记录值。

序号	COL1	COL2	COL3
(1)		B1
(2)	1	B2	C2
(3)	2	B3
(4)		B4	C4

createtabletest_t(

COL1int,

COL2char(10)notnull,

COL3char(10))

（1）INSERTINTOtest_t(COL2)values('B1')

（2）INSERTINTOtest_tvalues(1,'B2','C2')

（3）INSERTINTOtest_t(COL1,COL2)values(2,'B3')

（4）INSERTINTOtest_t(COL2,COL3)values('B4','C4')

40.将计算机系成绩大于80分的学生的修课情况插入到另一张表中，分两种情况实现：

（1）在插入数据过程中建表

（2）先建一个新表，然后再插入数据

（1）selects.sno,cno,gradeintonew_table1

fromstudentsjoinscons.sno=sc.sno

wheresdept='计算机系'andgrade>80

（2）createtablenew_table2(

snochar(10),

cnochar(10),

gradetinyint)

insertintonew_table2

selects.sno,cno,grade

fromstudentsjoinscons.sno=sc.sno

wheresdept='计算机系'andgrade>60

41.删除修课成绩小于50分的学生的修课记录

deletefromscwheregrade<60

42.删除信息系修课成绩小于50分的学生的修课纪录，分别用子查询和连接查询实现。

（1）用连接查询实现

deletefromscfromscjoinstudentsons.sno=sc.sno

wheresdept='信息系'andgrade<50

（2）用子查询实现

deletefromscwheresnoin(

selectsnofromstudentwheresdept='信息系')

andgrade<50

43.将所有选修了'c01'课程的学生的成绩加10分。

updatescsetgrade=grade+10

wherecno='c01'

44.将计算机系所有选修了计算机网络课程的学生的成绩加10分，分别用子查询和连接查询实现。

（1）用子查询实现

updatescsetgrade=grade+10

wheresnoin(

selectsnofromstudentwheresdept='计算机系')

andcnoin(

selectcnofromcoursewherecname='计算机网络')

（2）用连接实现

updatescsetgrade=grade-10

fromstudentsjoinscons.sno=sc.sno

joincourseconc.cno=sc.cno

wheresdept='计算机系'andcname='计算机网络'

45.删除VB考试成绩最低的学生的VB修课记录

方法一：

Deletefromsc

Wheresnoin

(selectsnofromsc

Wheregrade=

(selectmin(grade)fromsc

Joincourseconc.cno=sc.cno

Wherecname=’vb’)

Andcnoin

(selectcnofromcourse

Wherecname=’vb’)

)

andcnoin

(selectcnofromcourse

Wherecname=’vb’)

方法二：

deletefromsc

wherecno=(selectcnofromcoursewherecname='VB')

And

grade=(selectmin(grade)fromsc

wherecno=

(selectcnofromcoursewherecname='VB')

)

<!--EndFragment-->