实验三数据库查询及视图.doc

实验三 数据库查询及视图 一、数据库查询 1．实验目的 熟悉SQL Server 2000查询分析器环境。 掌握基本的SELECT查询及其相关子句的使用。 掌握复杂的SELECT查询，如多表查询、子查询、连接和联合查询。 2．实验内容 启动SQL Server 2000 查询分析器环境。 涉及多表的简单查询。 涉及多表的复杂查询。 3. 实验步骤 启动SQL Server查询分析器，打开“SQL查询分析器”窗口。 在“SQL查询分析器”窗口中选择要操作的数据库，如“XSCJ”数据库。 在KC表中查询学分低于3的课程信息，并按课程号升序排列。 在查询命令窗口中输入以下SQL查询命令并执行： SELECT * FROM KC WHERE KC.学分3 ORDER BY 课程号 在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列。 SELECT 学号,平均分=AVG(成绩) FROM XS_KC GROUP BY 学号 ORDER BY 平均分 DESC 在XS_KC表中查询选修了3门以上课程的学生学号。 SELECT 学号 FROM XS_KC GROUP BY 学号 HAVING COUNT(*)3 按学号对不及格的成绩记录进行明细汇总。 SELECT 学号,课程号,成绩 FROM XS_KC WHERE 成绩60 ORDER BY 学号 COMPUTE COUNT(成绩) BY 学号 分别用子查询和连接查询，求107号课程不及格的学生信息。 用子查询： SELECT 学号,姓名,联系电话 FROM XSQK WHERE 学号 IN ( SELECT 学号 FROM XS_KC WHERE 课程号=’107’AND 成绩60) 用连接查询： SELECT 学号,姓名,联系电话 FROM XSQK JOIN XS_KC ON XSQK.学号=XS_KC.学号 WHERE课程号=’107’AND 成绩60 用连接查询在XSQK表中查询住在同一寝室的学生，即其联系电话相同 SELECT A.学号,A.姓名,A.联系电话 FROM XSQK A JOIN XSQK B ON A.联系电话=B.联系电话 WHERE A.学号!=B.学号 请自已完成以下的查询: 查询XSQK表中所有的系名。 查询有多少同学选修了课程。 查询有多少同学没有选课。 查询与杨颖同一个系的同学姓名。 查询选修了课程的学生的姓名、课程名与成绩。 统计每门课程的选课人数和最高分。 统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。 二、 创建和使用视图 1．实验目的 掌握视图的创建、修改和删除。 掌握使用视图来访问数据。 2．实验内容 创建一个简单的视图，查询101号课程不及格的学生信息。 修改简单视图，查询107号课程成绩介于70-90的学生信息。 使用视图访问数据。 删除所创建的视图。 3．实验步骤 启动SQL Server企业管理器，打开“SQL Server Enterprise Manager”窗口。 选择要创建视图的数据库文件夹，如“XSCJ”文件夹，并在右边的对象窗口中选择其中的“视图”对象。 选择“操作”菜单中的“新建视图”命令，打开SQL Server的视图设计窗口。 在“数据源关系图窗口”中单击鼠标右键，打开“添加表”窗口，添加XSQK表和XS_KC表。 选择XSQK表的学号和姓名列，选择XS_KC表的课程号和成绩列，作为视图的显示列。 设置学号列的排序类型为升序。 设置查询条件：先在课程号行的“准则”列设置条件为“=‘101’”，然后在成绩行的“准则”列设置条件为“60”。 点击快捷工具栏上的快捷按钮，在弹出的“另存为”对话框中输入视图名，如“v_101不及格”，然后单击“确定”按钮，关闭视图设计窗口，完成视图的创建。 在“v_101不及格”视图上单击鼠标右键，在弹出的快捷菜单中选择“设计视图”命令，修改视图定义。 添加数据源KC表，以显示107号课程的课程名称。打开“添加表”窗口，选择“KC” 表，系统自动为KC表和XS_KC表建立基于课程号的内连接。 选择KC表中的课程名列。 添加查询条件：修改课程号行中“准则”列的条件为“=‘107’”；修改成绩行中“准则”列的条件为“=70”，并复制该行，去掉“输出”列中的复选，并修改“准则”列的条件为“=90”。 点击快捷工具栏上的快捷按钮，关闭视图设计窗口，保存对视图的修改。 请自已创建以下视图: 创建一个简单视图，查询“计算机系”学生的信息。 创建一个简单视图，统计每门课程的选课人数和最高分。 创建一个复杂视图，查询与“俞奇军”住在同一寝室的学生信息，即其联系电话相同。 创建一个复杂