数据库查询练习题ppt课件.ppt

数据查询习题 1 检索数据 2 你是一位健康护理计划的数据库管理员 使用下面语句创建physicians表 CREATETABLEdbo physicians physician nointIDENTITY 100 2 NOTNULL f namevarchar 25 NOTNULL l namevarchar 25 NOTNULL streetvarchar 50 NULL cityvarchar 255 NULL statevarchar 255 NULL postal codevarchar 7 NULL co paymoneyNOTNULLCONSTRAINTphys co payDEFAULT 10 如何检索在纽约州 NY 华盛顿州 WA 弗吉尼亚州 VA 或加利福尼亚州 CA 实习的医生信息 在结果集中 如何产生一个没有重复州的列表 在结果集中 如何产生一个列 包含co pay值加每位医生 5 00的服务费 并且给该列取一个别名Amt Due 3 a 如何检索在纽约州 NY 华盛顿州 WA 弗吉尼亚州 VA 或加利福尼亚州 CA 实习的医生信息 答 用下面类型的WHERE子句编写一个SELECT语句 WHEREstate NY ORstate WA ORstate Or useaWHEREclausethatincludestheINkeyword WHEREstatein NY WA VA CA b 在结果集中 如何产生一个没有重复州的列表 答 在SELECT语句中使用DISTINCT关键字 c 在结果集中 如何产生一个列 包含co pay值加每位医生 5 00的服务费 并且给该列取一个别名Amt Due 答 在选择列表中使用计算列 使用列别名 Amt Due co pay 5 4 学生成绩表包含列 学号 列 课程代号 列 成绩 列 考试时间 等 列 成绩 的值为NULL表示该学生缺考这门课 请用SELECT语句查询学号为 98120001 的学生所有缺考的课程数 5 SELECTCOUNT FROM学生成绩表WHERE学号 98120001 AND成绩ISNULL 6 雇员信息表包含列 雇员代号 列 雇员姓名 列 出生日期 列 基本工资 列 津贴 总工资 基本工资 津贴 请用SELECT语句查询1972年以后出生的雇员的姓名 出生日期和总工资 按总工资递减的顺序显示 7 SELECT雇员姓名 基本工资 津贴AS总工资 出生日期FROM雇员信息表WHERE出生日期 1972 01 01 ORDERBY总工资DESC 8 现在要做一个电话查询网页 供人们查询某大学的教职工的家庭电话号码 使用者在网页输入教职工的部分姓名 网页就返回匹配该查询条件的所有教职工的姓名和电话号码 输入的部分姓名可以多种多样 比如 查找 张小明 可以输入 张 小 明 张明 小明 等等 假设教职工的姓名和电话号码存放在表tblTelphone 局部变量 PartialName存放输入的部分姓名 请用Transact SQL语句写一段代码完成该查询 请参考字符串函数SUBSTRING 和SQLServer联机丛书 9 DECLARE SearchConditionvarchar 200 DECLARE ismallint CountsmallintSET SearchCondition SET Count LEN PartialName SET i 1WHILE i CountBEGINSET SearchCondition SearchCondition SUBSTRING PartialName i 1 SET i i 1ENDSELECT FROMtblTelphoneWHERE姓名like SearchCondition 10 数据分组与汇总 11 某班级的学生成绩表包含列 学号 列 课程代号 列 成绩 列 学期号 请统计学期号为 第三学期 的每个学生所有科目的平均成绩 12 SELECT学号 AVG 成绩 FROM学生成绩表WHERE学期 第三学期 GROUPBY学号 13 现在想评定该班级第二学年 包括第三学期和第四学期 的优秀学生奖学金 要求从该班级选出平均成绩排前5名的同学 请用SELECT语句显示这些同学的学号和平均成绩 要考虑可能有多个同学并列第5名 14 SELECTTOP5WITHTIES学号 AVG 成绩 AS平均成绩FROM学生成绩表WHERE学期 第三学期 OR学期 第四学期 GROUPBY学号ORDERBY平均成绩DESC 15 接上题 学校规定前三学年 第一学期到第六学期 总平均分在90分以上而且没有一次考试不及格 成绩 60分 的同学可以参加学校最高级别的评奖 请用SELECT语句显示该班级中满足评奖条件的候选人的学号和前三学年的总平均分 16 SELECT学号 AVG 成绩 AS三年平均成绩FROM学生成绩表WHERE学期 第一学期 OR学期 第二学期 OR学期 第三学期 OR学期 第四学期 OR学期 第五学期 OR学期 第六学期 GROUPBY学号HAVINGAVG 成绩 90ANDMIN 成绩 60ORDERBY三年平均成绩DESC 17 需要提供前 个产品和销售倒数百分之五的产品的列表 18 可以使用SELECTTOPn PERCENT 语句解答每个问题 第一个问题可以用SELECTTOP100 ORDERBY DESC语句 这样会使销售量最高的列在上面 第二个问题可以用SELECTTOP5PERCENT ORDERBY ASC语句 这样会使销售量最低的列在上面 19 多表连接 20 学生信息系统中有两个表 学生信息表 和 系信息表 在学生信息表中有列 学号 列 姓名 列 出生日期 列 系号 在系信息表中有列 系号 列 系名 列 系主任 列 系号 是学生信息表引用系信息表的外键 请用SELECT语句列出所有学生的学号 姓名和所在的系名 21 SELECTa 学号 a 姓名 b 系名FROM学生信息表ASaINNERJOIN系信息表ASbONa 系号 b 系号 22 接上题 请用SELECT语句统计并显示每个系的系号 系名以及每个系学生的人数 要求 如果新创建的系还没有学生 就不要显示该系的信息 23 SELECTb 系号 b 系名 count AS系学生人数FROM学生信息表ASaINNERJOIN系信息表ASbONa 系号 b 系号GROUPBYb 系号 b 系名 24 接上题 请用SELECT语句统计并显示每个系的系号 系名以及每个系学生的人数 要求 如果新创建的系还没有学生 该系也要显示 并且该系的学生人数为0 25 SELECTb 系号 b 系名 count 学号 AS系学生人数FROM学生信息表ASaRIGHTJOIN系信息表ASbONa 系号 b 系号GROUPBYb 系号 b 系名 26 接上题 教师信息表有列 教师代号 列 教师姓名 列 出生日期 列 系号 其中列 系号 是教师信息表引用系信息表的外键 请用SELECT语句列出系号为 12 的系的所有人员的姓名和出生日期 包括学生和教师 27 SELECT姓名 出生日期FROM学生信息表WHERE系号 12 UNIONSELECT教师姓名 出生日期FROM教师信息表WHERE系号 12 28 使用子查询 29 学生信息系统中有两个表 学生信息表 和 系信息表 在学生信息表中有列 学号 列 姓名 列 出生日期 列 系号 在系信息表中有列 系号 列 系名 列 系主任 列 系号 是学生信息表引用系信息表的外键 请用 相关子查询作为表达式 方法写出SELECT语句 来统计并显示每个系的系号 系名以及每个系学生的人数 要求 如果新创建的系还没有学生 则显示该系的学生人数为0 30 SELECTb 系号 b 系名 系学生人数 SELECTcount FROM学生信息表ASaWHEREa 系号 b 系号 FROM系信息表ASb 31 接上题 使用派生表方法写出SELECT语句 来统计并显示每个系的系号 系名以及每个系学生的人数 1 要求 如果新创建的系还没有学生 则不显示该系 2 要求 如果新创建的系还没有学生 则显示该系的学生人数为0 32 SELECTa 系号 a 系名 b 系学生人数FROM系信息表ASaINNERJOIN SELECT系号 count AS系学生人数FROM学生信息表GROUPBY系号 ASbONa 系号 b 系号SELECTa 系号 a 系名 ISNULL b 系学生人数 0 FROM系信息表ASaLEFTOUTERJOIN SELECT系号 count AS系学生人数FROM学生信息表GROUPBY系号 ASbONa 系号 b 系号 33 接上题 请显示学生人数最多的系的系号 名称和学生人数 34 SELECTb 系号 b 系名 count 学号 AS系学生人数FROM学生信息表ASaINNERJOIN系信息表ASbONa 系号 b 系号GROUPBYb 系号 b 系名HAVINGcount 学号 SELECTmax 系学生人数 FROM SELECT系号 count 学号 AS系学生人数FROM学生信息表GROUPBY系号 AST 35 在SQLServer2000数据库中创建了如下的一些表 CREATETABLE部门表 部门号char 2 notnullPRIMARYKEY 部门名称varchar 30 notnull CREATETABLE雇员表 雇员代号char 4 notnullPRIMARYKEY 雇员姓名varchar 20 notnull CREATETABLE任职表 雇员代号char 4 notnull 部门号char 2 notnull 开始时间datetimenotnull 备注varchar 1000 null CONSTRAINTPK WorksPRIMARYKEY 雇员代号 部门号 开始时间 CONSTRAINTFK EmployeesFOREIGNKEY 雇员代号 REFERENCES雇员表 雇员代号 ONDELETECASCADE