access和SQL数据库练习及答案

1数据库技术及应用实践考核上机考试练习题B（201010）给定的学生_选课库中数据库表结构及样本记录表1-表6数据如下：学生（学号，姓名，年龄，性别，所在系）；课程（课程号，课程名，先行课）；选课（学号，课程号，成绩）.表1学生表结构字段名数据类型长度是否为空约束学号CHAR5否主键姓名CHAR8否年龄SMALLINT默认20性别CHAR2“男”或“女”所在系CHAR20表2课程表结构字段名数据类型长度是否为空约束课程号CHAR5否主键课程名CHAR20否先行课CHAR5表3选课表结构字段名数据类型长度是否为空约束学号CHAR5否主键，外键课程号CHAR5否主键，外键成绩SMALLINT0～100表4学生表数据序号学号姓名性别年龄所在系1S1李明男21计算机2S2张小红男21计算机3S3李和明女22计算机4S4张三男21计算机5S5刘宏男23计算机6S6王红应女20计算机7S7吴非男19数学8S8丁玉男21数学9S9赵名女21数学10S12张共可男22物理2表5课程表数据序号课程号课程名先行课1C1计算机引论2C2数据结构C33C3C语言编程C14C4软件工程C65C6数据库C26C5计算机文化7C7高等数学8C8概率统计C99C9线性代数C710C10力学表6选课表数据序号学号课程号成绩1S1C1602S2C1933S3C14S4C1895S1C2796S2C27S3C2808S4C3909S1C39210S2C38111S1C78512S4C775一、根据给定的样本数据，按顺序完成下列操作：1、数据定义和输入记录（1）创建学生-课程数据库。（2）创建学生表、课程表和选课表，并满足约束条件。（3）输入样本记录。（4）查询输入的样本记录，并保存结果。（5）创建计算机系学生成绩视图。属性包括：学号、姓名、课程名和成绩。（6）查询已创建的视图，并保存结果。（7）创建学生表、课程表和选课表的数据库关系图表。2、数据更新（1）输入一条新学生记录，例如：(‘S11’,’李阳’,’男’,21,’计算机’)（2）删除一条学生记录，例如：删除学号=‘S5’的记录。（3）修改一条学生记录，例如：将学号=‘S5’的记录的系修改为计算机（RSL3改了吴非）。（4）查询当前的学生记录，并保存结果。（5）求每个系的学生的平均年龄，并将结果存入到系平均年龄数据库中。标准：select所在系,avg(年龄)AS平均年龄into平均年龄数据库from学生groupby所在系;SELECT学生.所在系,Avg(学生.年龄)AS平均年龄FROM学生GROUPBY学生.所在系;3、单表查询（1）求计算机系的学生学号和姓名。（2）求选修了课程的学生学号。标准：selectdistinct学号from选课;1.distinct去除重复的数据（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。SELECT选课.学号,选课.成绩,选课.课程号FROM选课WHERE课程号=C1ORDERBY成绩DESC,学号;4（4）求数学系或计算机系姓张的学生的信息。错：只求计算机SELECT学生.所在系,学生.姓名,学生.年龄,学生.性别,学生.学号FROM学生WHERE(((学生.所在系)=计算机)AND((学生.姓名)Like张*));=============================================结果：SELECT学生.所在系,学生.姓名,学生.年龄,学生.性别,学生.学号FROM学生WHERE((学生.姓名)Like张*)AND所在系IN(计算机,数学);=============================================（5）求缺少了成绩的学生的学号和课程号。SELECT选课.学号,选课.课程号FROM选课WHERE(((选课.成绩)IsNull));4、连接查询（1）求学生的学号、姓名、选修的课程名及成绩。SELECT学生.学号,学生.姓名,课程.课程名,选课.成绩FROM课程INNERJOIN(学生INNERJOIN选课ON学生.学号=选课.学5号)ON课程.课程号=选课.课程号;（2）求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。SELECT学生.学号,学生.姓名,选课.成绩FROM学生INNERJOIN选课ON学生.学号=选课.学号WHERE(((选课.成绩)=90)AND((选课.课程号)=C1));（3）查询每一门课的间接先行课（即先行课的先行课）。selecta.课程号,b.先行课fromcurra,currbwherea.先行课=b.课程号;（4）求学生表与选课表的左外连接。select*from学生leftjoin选课on学生.学号=选课.学号;6（5）求选课表与课程表的右外连接。5、嵌套查询（1）求选修了高等数学的学生学号和姓名。select学号,姓名fromstuwhere学号in(select学号from选课where课程号=(select课程号fromcurrwhere课程名='高等数学'));错：以下属于连接查询SELECT课程.课程名,学生.学号,学生.姓名FROM课程INNERJOIN(学生INNERJOIN选课ON学生.学号=选课.学号)ON课程.课程号=选课.课程号WHERE(((课程.课程名)=高等数学));（2）求C1课程的成绩高于张三的学生学号和成绩。SELECT学号,成绩7FROM选课WHERE课程号=C1AND成绩(SELECT成绩FROM选课WHERE学号=(SELECT学号FROM学生WHERE姓名=张三)AND课程号=C1);（3）求其他系中比计算机系学生年龄都小的学生。select*fromstuwhere年龄(selectmin(年龄)from学生where所在系='计算机');（4）求没有选修C2课程的学生姓名。select姓名fromstuwhere学号notin(select学号fromselect_currwhere课程号='C2');（5）求至少选修了两门课程的学生信息。SELECT*FROM学生WHERE学号IN(SELECT学号FROM选课GROUPBY学号HAVING8COUNT(学号)=2);6、组合查询（1）求学生的总人数。SELECTCount(学生.学号)AS学号之计数FROM学生;（2）求选修了课程的学生人数。selectcount(学号)as人数FROM(Selectdistinct学号FROM选课);（3）求计算机系学生的平均年龄定价，用GROUPBY表示。SELECTAVG(年龄)AS平均年龄FROM学生WHERE所在系=计算机;（4）求各课程号和选修该课程的人数。SELECT课程号,COUNT(学号)AS人数9FROM选课GROUPBY课程号;（5）求选修课超过3门课的学生学号。select学号fromselect_currgroupby学号havingcount(课程号)3;二、关于程序验收1、通过可视化工具，验收已创建的数据库、数据表、视图关系表和数据库关系表。2、通过已保存的SQL程序代码，验收查询结果。3、上机操作工作量：除了操作1的数据定义和样本数据输入必做外，其余操作2至操作6，考试时每种操作出1-2道小题，共约8-10题。