SQL查询复习.docx
《SQL查询复习.docx》由会员分享，可在线阅读，更多相关《SQL查询复习.docx（12页珍藏版）》请在冰豆网上搜索。
SQL查询复习
数据查询
一、目的与要求
1．掌握SELECT语句的基本语法。
2．掌握子查询的表示。
3．掌握连接查询的表示。
4．掌握SELECT语句的统计函数的作用和使用方法。
5．掌握SELECT语句的GROUPBY和ORDERBY子句的作用和使用方法。
二、实验准备
1．了解SELECT语句的基本语法格式。
2．了解SELECT语句的执行方法。
3．了解子查询的表示方法。
4．了解SELECT语句的统计函数的作用。
5．了解SELECT语句的GROUPBY和ORDERBY子句的作用。
三、实验步骤
1．将FTP下实验文件夹下的stu_c.txt文件拷到本地盘,打开后,在SQL查询分析器中执行,生成STUDENT、COURSE、SC三张表。
将所有运行通过的SQL语句保存在文档中。
请认真观察每张表的表名、属性及表之间的联系
2.SELECT语句的基本使用
（1）分别查询STUDENT、COURSE、SC的基本信息。
SELECT*FROMStudent;
（2）查询全体学生的姓名、学号、所在系、家庭地址。
SELECTSname,Sno,Sdept,SaddrFROMStudent;
（3）查询全体学生的姓名、出生年份和所在系、班级，要求用小写字母表示所在系名，并用别名表达列标题。
SELECTsname姓名,2010-sage出生年份,lower（sdept）系别,class班级FROMStudent;
（4）查询那些学生选修了课程。
SELECTdistinctsnoFROMSCWHEREgradeisnull;
（5）查询所有年龄在20岁以上的学生姓名、班级及其年龄。
SELECTsname,class,sageFROMStudentWHEREsage>20;
（6）查询年龄在17~19岁（包括17岁和19岁）之间的学生的姓名、系别、班级和年龄。
SELECTsname,sdept,class,sageFROMStudentWHEREsagebetween17and19;
（7）查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
SELECTsname,ssexFROMStudentWHEREsdeptnotin（‘IS’,’MA’,’cs’）;
（8）查询所有姓张学生的姓名、学号和性别。
SELECTsname,sno,ssexFROMStudentWHEREsnamelike‘张%’;
（9）查询姓"欧阳"且全名为四个汉字的学生的姓名。
SELECTsnameFROMStudentWHEREsnamelike’欧阳__’;
（10）查询所有家庭地址中包含‘成都’的同学的基本信息
SELECT*FROMStudentWHEREsaddrlike‘%成都%’;
2.单表查询
（1）查询信息系（IS）、数学系（MA）和计算机科学系（CS）中女学生的姓名和性别
（分别用inand和andor两种方式查询）
SELECTsname,ssex
FROMStuden
WHEREsdeptin（‘IS’,’CS’,’MA’）andssex=’女’;
SELECTsname,ssex
FROMStudent
WHERE（sdept=’IS’orsdept=’CS’orsdept=”MA’）andssex=’女’;
（2）查询选修表中1号课程成绩为空（即1号课程选修了而未参加考试）的同学学号.
SELECTsnoFROMSCWHEREgradeisnullandcno=’1’;
（3）查询课程表中第三学期开设了那些选修课.
SELECT*FROMCourseWHEREctype=’选修’;
（4）查询SC的基本信息,结果按学号升序排列,同一学号按课程号降序排列.
SELECT*FROMSCorderby
（5）查询学生表的学号、姓名、班级，结果按班级号升序排列。
SELECTFROMStudent,Course,SCWHEREorderbysno,classdesc
（6）查询学生表的学号、姓名、班级，结果按班级号升序排列，同一班级学生按学号升序排列。
SELECTsno,sname,classFROMStudentWHEREorderbyclass,snodesc
（7）查询家在成都的信息系（IS）的男同学基本信息。
SELECT*FROMStudentWHEREsaddrlike‘%成都%’andssex=’男’;
（8）查询200507班的女同学的姓名、学号。
SELECTsname,snoFROMStudentWHEREclass=’200507’;
3．SELECT语句的连接查询（用连接查询）
（1）列出“计算机”（CS）系选修了编号为“2”的课程的学生的学号和姓名
SELECTsname,student.sno
FROMStudent,SC
WHEREstudent.sno=sc.snoandsdept=’CS’andcno=’2’
（2）列出选修了学分大于3，并且成绩不及格的学生的学号、姓名、课程编号
SELECTsc.sno,sname,o
FROMStudent,course,SC
WHEREstudent.sno=sc.snoando=oandgrade<60andccredit>3;
（3）列出“信息”（IS）、“数学”（MA）、“计算机”三个系的学生成绩在85分以上的选修课程编号、成绩；
SELECTdistinctcno,grade
FROMStudent,SC
WHEREstudent.sno=sc.snoando=o
Andvsdeptin（‘IS’,’MA’,’CS’）andgrade>85;
（4）查询成绩在80到90分之间的学生的学号、姓名、选修的课程名称、成绩
SELECTsc.sno,sname,cname,grade
FROMStudent,Course,SC
WHEREstudent.sno=sc.snoando=oandgradebetween80and90;
（6）查询所有学生的学号、姓名、选修的课程名及成绩（并对其按学号升序排序、课程名降序排列。
SELECTsc.sno,sname,cname,grade
FROMStudent,Course,SC
WHEREstudent.sno=sc.snoando=o
orderbysc.sno,cnamedesc;
（7）查询选修“数据库”这门课且成绩在85分以上的所有学生的学号、姓名,并对其按学号升序排序
SELECTsc.sno,sname
FROMStudent,Course,SC
WHEREstudent.sno=sc.snoando=oandcname=’数据库’
Andgrade>=85
Orderbysno
（8）查询学分在2-4范围内的课程的基本信息
SELECT*FROMCourseWHEREccreditbetween2and4
（9）查找选修了2号课程，并且成绩在85-----100之间的学生的学号、姓名、所在系、成绩，结果以成绩降序排列，成绩相同者以学号升序排列
SELECTsc.sno,sname,sdept,grade
FROMStudent,SC
WHEREstudent.sno=sc.snoandcno=’2’andgradebetween85and100
Orderbygradedesc,sc.sno
（10）查询信息系选修“PASCAL语言”课程的同学学号及姓名
SELECTsc.sno,sname
FROMStudent,Course,SC
WHEREstudent.sno=sc.snoando=oand
Cname=’PASCAL语言’;
（11）列出所有学生的的学号、姓名、不及格的课程编号及具体成绩
SELECTsc,sno,sname,cno,grade
FROMStudent,SC
WHEREstudent.sno=sc.snoandgrade<60;
（12）列出所有课程的选修情况,包括没有选的课程信息
SELECT*FROMCoursejoinleftouterSCo=o’
（13）列出所有同学的选课信息,包括未选课的同学
SELECT*FROMStudentjoinleftSCstudent.sno=sc.sno
4.聚合函数及分组查询
（1）统计有成绩的学生的人数。
SELECTcount（distinctsno）
FROMSC
WHEREgradeidnull
（2）统计编号为“2”的课程的平均成绩。
SELECTavg（grade）FROMSCWHEREcno=’2’;
（4）求全校有多少个系。
SELECTcount（distinctsdept）FROMStudent
（5）求“MA”系成绩大于85分的学生的人数。
SELECTcount（distinctsc.sno）
FROMStudent,SC
WHEREstudent.sno=sc.snoandgrade>=85;
（6）求学号为“95001”的学生的总分和平均分。
SELECTsum（grade）,avg（grade）
FROMSC
WHEREsno=’95001’;
（7）查询“1”号课程的最高分、最低分及之间相差的分数。
SELECTmax（grade）,min（grade）,max（grade）-min（grade）
FROMSCWHEREcno=’1’;
（8）求“计算机”系学生的人数。
SELECTcount（sno）FROMStudentWHEREsdept=’CS’;
（9）查询每门课的选课人数及平均成绩。
SELECTcno,count（sno）FROMSC
Groupbycno
（10）查询有两门课以上不及格的学生学号及不及格的课程门数。
SELECTsno,count（cno）
FROMSC
WHEREgrade<60
Groupbysno
Havingcount（cno）>=2;
（11）统计每门选修课程不及格的人数，列出课程编号和不及格的人数。
SELECTcno,count（sno）
FROM,SC
WHEREgrade<60
Groupbycno
（12）统计每个同学所修的学分总数
SELECTsno,sum（ccredit）
FROMCourse,SC
WHEREo=o
Groupbysno
（13）查询每个系的学生人数、并按人数的降序排列。
SELECTsdept,count（sno）
FROMStudent
Groupbysdept
Orderbycount（sno）desc;
（15）查询每门课的平均分、最高分、最低分。
SELECTavg（grade）,max（grade）,min（grade）
FROMSC
Groupbycno
（17）分课程统计成绩在85分以上的学生的人数，列出课程编号、课程名称和学生人数
SELECTo,cname,count（sno）
FROMCourse,SC
WHEREo=oandgrade>=85
Groupbyo,sname
（18）列出平均成绩在85分以上的学生的学号和姓名
SELECTsc.sno,sname
FROMStudent,SC
Groupbysc.sno,sname
Havingavg（grade）;
（20）列出选修人数小于5的课程的编号和实际选修人数
SELECTcno,count（sno）
FROMSC
Groupbycno
Havingcount（sno）<5;
（21）列出“信息”系每个学生不及格的课程门数
SELECTsc.sno,count（cno）
FROMStudent,SC
WHEREstudent.sno=sc.snoandsdept=’IS’andgrade<60
Groupbysc.sno
嵌套查询（子查询）
（1）查询选修了一号课程的同学姓名
ELECTsnameFROMStudent
WHEREsnoin（selectsno
Fromsc
Wherecno=’1’）
（2）查找家庭住址包含“成都”的学生选修的课程的名称。
SELECTcnameFROMCourse
WHEREcnoin（selectcno
Fromsc
Wheresnoin（selectsno
Fromstudent
Wheresaddrlike（%成都%））;
（3）查询选修了数据库这门课的同学的学号、姓名
SELECTsno,snameFROMStudent
WHEREsnoin（selectsno
Fromsc
Wherecnoin（selectcno
Fromcourse
Wherecname=’数据库’））;
（4）查询选修了‘1’号课程，并且成绩在该课程平均分以下的学生的学号、姓名、成绩
SELECTsc.sno,sname,grade
FROMStudent,SC
WHEREstudent.sno=sc.snoandcno=’1’andgrade<
（selectavg（grade）
Fromsc
Wherecno=’1’）;
（5）查询选修了“数据库系统”这门课且成绩在85分以上的所有学生的学号、姓名。
SELECTsno,sname
FROMStudent
WHEREsnoin（selectsno
Fromsc
Grade>=85andcnoin（selectcno
Fromcourse
Wherecname=’数据库’）;
（7）列出选修的课程学分都大于3的学生的学号、姓名
SELECTsno,snameFROMStudent
WHEREsnoin（selectsno
Fromsc
Wherecnoin（selectcno
Fromcoursewhereccredit>3））;
（8）查询其他系中比信息系所有学生年龄都小的学生姓名及年龄
SELECTsname,sage
FROMStudent
WHEREsdept<>’IS’andsageFromstudent
Wheresdept=’IS’）;
（9）查询同时选修了一号课程和选修了二号课程的同学姓名。
SELECTsnameFROMStudent
WHEREsnoin（selectsno
Fromsc
Wherecno=’1’andsnoin（selectsno
Fromsc
Wherecno=’2’））;
（10）列出“2”号课程得分最高的学生的学号,姓名,成绩
SELECTsc.sno,sname,grade
FROMStudent,SC
WHEREcno=’2’andgrade>=all（selectgrade
Fromsc
Wherecno=’2’）;
1.列出没有任何一门课成绩不及格的学生的学号、姓名
SELECTsno,sname
FROMStudent
WHEREsnonotin（selectsno
Fromsc
Grade<60）;
2.查询李丽同学不学的课程的课程号
SELECTcnoFROMCourse
WHEREcnonotin（selectcno
Fromsc
Wheresnoin（selectsno
Fromstudent
Wheresname=’李丽’））;
3.求年龄最大的学生姓名
SELECTsnameFROMStudent
WHEREsage>=（selectmax（sage）
Fromstudent）;
4.求年龄最小的学生姓名
SELECTsnameFROMStudent
WHEREsage<=all（selectdistinctsage
Fromstudent）;
5.求得分最低的学生的姓名、课程名及成绩
SELECTsname,cname,grade
FROMStudent,Course,SC
WHEREstudent.sno=sc.snoando=oand
Grade<=（selectmin（grade）
Fromsc）;
6.求未选修3号课程的同学学号。
SELECTsnoFROMStudent
WHEREsnonotin（selectsno
Fromsc
Wherecno=’3’）;
7.求平均成绩最高的学生的姓名及平均分
SELECTsname,avg（grade）
FROMStudent,SC
Wherestudent.sno=sc.sno
Groupbysname
Havingavg（grade）>=all（selectavg（grade）
Fromsc
Groupbysno）;
8.列出既有成绩不及格又有成绩在85分以上的学生的学号,姓名
Selectsno,sname
Fromstudent
Wheresnoin（selectsno
Fromsc
Wheregrade>85andsnoin（selectsno
Fromsc
Wheregrade<60））;
更新
（1）将本人信息插入STUDENT表中
insertintostudent
values（'95066','李勇智','男',21,'CS','200506','四川省成都市光华村街55号一银杏203'）;
（2）插入一条课程信息：
（课程编号“16”，课程名“面向对象程序设计”，先和行课号7，学分4，）
Insertintocourse（cno,cname,cpno,ccredit）
Values（‘16’,’面向对象程序设计’,’7’,4）
（2）向SC表中插入一条选课记录（’95010’,’16’，85）
Insertinto
Values（‘95010’,’16’,85）
（3）删除‘95009’同学选课的记录
Delete
Fromscwheresno=’95009’
（4）删除“数学”系所有学生的选课信息
Delete
Fromsc
Wheresnoin（selectsno
Fromstudent
Wheresdept=’MA’）
（5）将“2”课程的成绩增加5分
Updatesc
Setgrade=grade+5
Wherecno=’2’
（6）将学生选修的学分大于4的课程的成绩增加5分
Updatesc
Setgrade=grade+5
Wherecnoin（selectcno
Fromcourse
Whereccredit>=4）;
（7）将“1”号课程中成绩大于82分的同学分数提高3分。
Updatesc
Setgrade=grade+3
Wherecno=’1’andgrade>82
（8）把所有选修了1号课程的同学的成绩增加5分。
（9）将选修了‘数据库’这门课的同学的该课程分数提高5分。
Updatesc
Setgrade=grade+5
Wherecnoin（selectcno
Fromcourse
Wherecname=’数据库’）;
升级会员 