实验目标：1．掌握指定列或全部列查询2．掌握按条件查询3．掌握对查询结果排序4．掌握使用聚集函数的查询5．掌握分组统计查询 一、请完成书中实验7.1，并完成以下问题。 1．查询所有
实验目标：
1．掌握指定列或全部列查询
2．掌握按条件查询
3．掌握对查询结果排序
4．掌握使用聚集函数的查询
5．掌握分组统计查询
一、请完成书中实验7.1，并完成以下问题。
1．查询所有学生的姓名及其出生年份回答以下问题：
SQL语句请截图
① 观察查询的数据，若年龄不为空是否能求出出生年份，若年龄为空则出生年份显示什么？
能，若年龄为空，出生年份也显示空
② 使用了哪些函数求出出生年份，AS的作用是什么？
使用GETDATE()得到当前年份，再减去年龄得到出生年份。AS作用给列取别名
2．完成按条件查询SC表考试成绩不及格的学生的学号回答以下问题：
① 插入三行记录，（s2,c7,45）,(s3,c2,59),(s4,c3,50)(重复不能插进去),描述相应的SQL。
INSERT INTO SC(SNO,CNO,SCORE)
VALUES
(&＃8216;S2&＃8242;,&＃8217;C7&＃8217;,45),
(&＃8216;S3&＃8242;,&＃8217;C2&＃8217;,59),
(&＃8216;S4&＃8242;,&＃8217;C3&＃8217;,50)
② 查询考试成绩不及格的学生学号,会出现以下查询结果吗？如果没出现，是什么原因？如果出现了，如何解决出现重复的学号。
解决方法：在SELECT SNO FROM SC 语句的 SNO 前加上 DISTINCT。
3．查询年龄在20-23岁之间的学生的姓名、系名、年龄，试一试，再把between&＃8230; And&＃8230;换成in完成查询，描述相应的SQL语句。
4．完成查询姓李的学生的姓名、学号、性别，试一试再插入两行数据(‘10’,‘李思’,‘女’)、(‘11’,‘李王睿安’,‘男’)，能否使用like分别查询李姓两字、李姓四字学生的姓名、学号、性别，描述相应的SQL语句。
查询李姓两字学生的姓名、学号、性别
查询李姓李姓四字学生的姓名、学号、性别
5．完成对查询结果进行排序，回答排序是用什么子句完成，其中升序、降序的关键字分别是什么？可以按多个字段进行排序吗？
SELECT 字段名 AS 排序名,…n FROM 表名
WHERE (条件)
ORDER BY 字段名（升序），字段名 DESC （降序）
升序：OREDER BY接升序的字段；
降序：OREDER BY接降序的字段DESC
可以按多个字段进行排序
6．对T表查询教师的职称及相应的人数，描述相应的SQL语句。
7．在T表中查询老师的姓名、职称，工资，并按教师的SAL字段进行降序排序。
8.在S表、SC表中查询选课学生、及其选课的总成绩，并根据总成绩进行降序排列，查询结果可参考如下。
9.查询有多少个学生参加选课？
二、执行下面的SQL脚本完成表的创建及数据的插入，并完成后面的问题
1. 数据表（4个表）
l 学生表（学号、姓名、年龄、性别）
create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
)
l 教师表（教师工号、姓名）
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
)
l 课程表（课程号、课程名、教师工号）
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(10),
constraint pk_course primary key (cno,tno)
)
l 成绩表（学号、课程号、分数）
create table sc(
sno varchar(10),
cno varchar(10),
score real,
constraint pk_sc primary key (sno,cno)
)
2. 初始化数据
l 学生表
insert into student values (&＃8216;s001&＃8242;,&＃8217;张三&＃8217;,23,&＃8217;男&＃8217;);
insert into student values (&＃8216;s002&＃8242;,&＃8217;李四&＃8217;,23,&＃8217;男&＃8217;);
insert into student values (&＃8216;s003&＃8242;,&＃8217;吴鹏&＃8217;,25,&＃8217;男&＃8217;);
insert into student values (&＃8216;s004&＃8242;,&＃8217;琴沁&＃8217;,20,&＃8217;女&＃8217;);
insert into student values (&＃8216;s005&＃8242;,&＃8217;王丽&＃8217;,20,&＃8217;女&＃8217;);
insert into student values (&＃8216;s006&＃8242;,&＃8217;李波&＃8217;,21,&＃8217;男&＃8217;);
insert into student values (&＃8216;s007&＃8242;,&＃8217;刘玉&＃8217;,21,&＃8217;男&＃8217;);
insert into student values (&＃8216;s008&＃8242;,&＃8217;萧蓉&＃8217;,21,&＃8217;女&＃8217;);
insert into student values (&＃8216;s009&＃8242;,&＃8217;陈萧晓&＃8217;,23,&＃8217;女&＃8217;);
insert into student values (&＃8216;s010&＃8242;,&＃8217;陈美&＃8217;,22,&＃8217;女&＃8217;);
l 教师表
insert into teacher values (&＃8216;t001&＃8217;, &＃8216;刘阳&＃8217;);
insert into teacher values (&＃8216;t002&＃8217;, &＃8216;谌燕&＃8217;);
insert into teacher values (&＃8216;t003&＃8217;, &＃8216;胡明星&＃8217;);
l 课程表
insert into course values (&＃8216;c001&＃8242;,&＃8217;J2SE&＃8217;,&＃8217;t002&＃8242;);
insert into course values (&＃8216;c002&＃8242;,&＃8217;Java Web&＃8217;,&＃8217;t002&＃8242;);
insert into course values (&＃8216;c003&＃8242;,&＃8217;SSH&＃8217;,&＃8217;t001&＃8242;);
insert into course values (&＃8216;c004&＃8242;,&＃8217;Oracle&＃8217;,&＃8217;t001&＃8242;);
insert into course values (&＃8216;c005&＃8242;,&＃8217;SQL SERVER 2005&＃8242;,&＃8217;t003&＃8217;);
insert into course values (&＃8216;c006&＃8242;,&＃8217;C#&＃8217;,&＃8217;t003&＃8242;);
insert into course values (&＃8216;c007&＃8242;,&＃8217;Javascript&＃8217;,&＃8217;t002&＃8242;);
insert into course values (&＃8216;c008&＃8242;,&＃8217;DIV+CSS&＃8217;,&＃8217;t001&＃8242;);
insert into course values (&＃8216;c009&＃8242;,&＃8217;PHP&＃8217;,&＃8217;t003&＃8242;);
insert into course values (&＃8216;c010&＃8242;,&＃8217;EJB3.0&＃8242;,&＃8217;t002&＃8217;);
l 成绩表
insert into sc values (&＃8216;s001&＃8242;,&＃8217;c001&＃8217;,78.9);
insert into sc values (&＃8216;s002&＃8242;,&＃8217;c001&＃8217;,80.9);
insert into sc values (&＃8216;s003&＃8242;,&＃8217;c001&＃8217;,81.9);
insert into sc values (&＃8216;s004&＃8242;,&＃8217;c001&＃8217;,60.9);
insert into sc values (&＃8216;s001&＃8242;,&＃8217;c002&＃8217;,82.9);
insert into sc values (&＃8216;s002&＃8242;,&＃8217;c002&＃8217;,72.9);
insert into sc values (&＃8216;s003&＃8242;,&＃8217;c002&＃8217;,81.9);
insert into sc values (&＃8216;s001&＃8242;,&＃8217;c003&＃8242;,&＃8217;59&＃8217;);
3. 完成下面的题目
1. 求选了课程的学生人数。
2.查询姓“刘”的老师的个数
3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。
4.查询平均成绩大于60 分的同学的学号和平均成绩（思路按学号分组计算学生的平均成绩，用having子句筛选出平均成绩大于60的学生）。
5. 按各科平均成绩从低到高和及格率的百分数从高到低顺序，效果如下。（思路：可以通过case when..then..else..end统计人数））
6.查询每门课程被选修的学生数。
7.查询男生、女生人数
8. 查询姓“张”的学生名单。
9. 查询1981 年出生的学生名单。
10. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。
11. 查询各个课程及相应的选修人数。
12. 统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
13. 检索至少选修两门课程的学生学号。
14.检索“c004”课程分数小于60，按分数降序排列的同学学号。
15. 删除“s002”同学的“c001”课程的成绩。
京公网安备 11010802041100号