SQL 实验项目2_2:多表查询

大三上:数据库系统概论所有实验报告和课后作业

一、实验目的及要求

  1. 熟练使用DBMS提供的查询编辑器功能组件来实现对数据库进行连接查询、嵌套查询、和分组统计查询等操作;
  2. 掌握用SQL语句实现对数据库的各类查询;
  3. 理解关系的投影、选择、集合、改名和分组统计等操作。

二、实验内容(或实验原理、实验拓扑)

  • 连接查询:包括实现多表连接、外连接和自身连接等。
  • 嵌套查询:包括使用IN、比较符、ANY或ALL等操作符进行嵌套查询操作。
  • 分组聚集查询:包括使用GROUP BY子句和聚集函数来实现分组聚集查询。
  • 集合查询:包括利用DBMS提供的并、差、交等集合操作功能实现关系表的集合操作。

三、实验设备与环境

I7 7700HQ+16G内存+48TB分布式硬盘的笔记本电脑、Windows 10 1903政府版、Microsoft SQL Server 2008 R2

四、实验设计方案(包括实验步骤、设计思想、算法描述或开发流程等)

1.启动DBMS服务器,视情况决定是否附加实验用数据库。
2.在查询编辑器中,打开查询窗口,打开或选择实验数据库,输入完成实验内容的SELECT语句。

第一阶段:(对数据库eshop中数据进行分组统计查询)。
(1)查询性别为“男”的会员详细信息,查询结果按月薪降序排列.。
(2)查询全体会员的会员帐号,姓名和年龄并按家庭地址升序排列,同一地址中的会员按年龄降序排列。
(3)查询会员帐号为’liuzc’所购买的商品号和订购日期,并按订购日期升序排列。
(4)查询购买商品号为’0910810004’总人数。
(5)查询2005年6月6日前,所有商品的订购总量,要求输出商品号和订购总量。
(6)查询所有会员的平均月薪,最高月薪和最低月薪。
(7)查询所有会员购买商品的种类和,要求输出会员号和商品种类和。
(8)查询各类商品的最高购买数量,要求输出最高数量大于10的商品号和最高数量。

第二阶段:教材P130 第三章习题4、5
4.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询。
(1)求供应工程J1零件的供应商号码SNO;
(2)求供应工程J1零件P1的供应商号码SNO;
(3)求供应工程J1零件为红色的供应商号码SNO;
(4)求没有使用天津供应商生产的红色零件的工程号JNO;
(5)求至少用了供应商S1所供应的全部零件的工程号JNO。

5.针对习题3中的四个表试用SQL语言完成以下各项操作:
(1)找出所有供应商的姓名和所在城市。
(2)找出所有零件的名称、颜色、重量。
(3)找出使用供应商S1所供应零件的工程号码。
(4)找出工程项目J2使用的各种零件的名称及其数量。
(5)找出上海厂商供应的所有零件号码。
(6)找出使用上海产的零件的工程名称。
(7)找出没有使用天津产的零件的工程号码。
(8)把全部红色零件的颜色改成蓝色。
(9)由S5供给J4的零件P6改为由S3供应,请作必要的修改。
(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。
(11)请将(S2, J6, P4, 200) 插入供应情况关系。

*第三阶段: (对数据库学生-课程中数据进行指定条件的多表查询)
(1)实现多表连接、外连接和自身连接查询等,掌握在SELECT语句的WHERE子句中通过设置查询条件实现多表间的连接操作。
①查询每个学生及其所选修的课程的信息。(外连接)
②查询选修“操作系统”课程且成绩在90分以上的学生学号、姓名及成绩。(多表多条件连接查询)
③查询以“大学计算机基础”课程为先修课程的课程名称。(别名实现自身连接)
(2)实现分组聚集查询,掌握使用GROUP BY子句细化聚集函数的作用对象,将聚集函数作用于一个或多个属性咧的值构造的分组上。
①查询学生所选修的各门课程的平均成绩,输出列名显示为“学号”、“平均成绩”,并按平均成绩降序显示输出。(GROUP BY分组统计查询、排序)
②查询女生人数超过10人的系的名称。(where、 group by、 having子句都被使用时,他们的作用对象和执行顺序)
③查询数学系的平均成绩在80分以上的学生的学号和平均成绩,输出列名显示为“学号”和“平均成绩”。(连接查询中实现分组统计查询)
(3)实现嵌套查询,掌握利用DBMS所提供的IN、比较符、ANY、 ALL和EXISTS等操作符进行嵌套查询操作。
①查询没有选修课程编号为C01课程的学生的学号和姓名。(NOT IN \NOT EXISTS实现否定条件的多表查询)
②查询选修课程编号为C02课程的学生中其成绩高于“王玲”的学生的学号和成绩。(可用比较运算符来嵌套独立子查询条件实现多表查询)
③查询学生的所修课程成绩超过其课程平均成绩的选课信息。
④查询其他系中比“计算机”系某一学生年龄大的学生,并按年龄降序输出。
⑤查询选修“数据库”课程的成绩比“操作系统”课程成绩高的学生的学号和成绩。
⑥查询至少选修了学号为S01的学生所选修的全部课程的学生姓名。
(4)实现集合查询,掌握利用DBMS所提供的UNION等操作符进行查询的集合操作。
①查询既没有选修“数据库”课程也没有选修“软件工程”课程的学生姓名。
②查询未被选修的课程信息。(也可用NOT IN 或NOT XEISTS实现带有否定谓词的查询操作。

五、实验结果(包括设计效果、测试数据、运行结果等)

  1. 第一阶段:(对数据库eshop中数据进行分组统计查询)。
    (1)查询性别为“男”的会员详细信息,查询结果按月薪降序排列.。
    在这里插入图片描述
    (2)查询全体会员的会员帐号,姓名和年龄并按家庭地址升序排列,同一地址中的会员按年龄降序排列。
    在这里插入图片描述
    (3)查询会员帐号为’liuzc’所购买的商品号和订购日期,并按订购日期升序排列。
    在这里插入图片描述
    (4)查询购买商品号为’0910810004’总人数。
    在这里插入图片描述
    (5)查询2005年6月6日前,所有商品的订购总量,要求输出商品号和订购总量。
    在这里插入图片描述
    (6)查询所有会员的平均月薪,最高月薪和最低月薪。
    在这里插入图片描述
    (7)查询所有会员购买商品的种类和,要求输出会员号和商品种类和。
    在这里插入图片描述
    (8)查询各类商品的最高购买数量,要求输出最高数量大于10的商品号和最高数量。
    在这里插入图片描述
  2. 第二阶段:教材P130 第三章习题4、5
    4.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询。
    (1)求供应工程J1零件的供应商号码SNO;
    在这里插入图片描述
    (2)求供应工程J1零件P1的供应商号码SNO;
    在这里插入图片描述
    (3)求供应工程J1零件为红色的供应商号码SNO;
    在这里插入图片描述
    (4)求没有使用天津供应商生产的红色零件的工程号JNO;
    在这里插入图片描述
    (5)求至少用了供应商S1所供应的全部零件的工程号JNO。
    在这里插入图片描述
    5.针对习题3中的四个表试用SQL语言完成以下各项操作:
    (1)找出所有供应商的姓名和所在城市。
    在这里插入图片描述
    (2)找出所有零件的名称、颜色、重量。
    在这里插入图片描述
    (3)找出使用供应商S1所供应零件的工程号码。
    在这里插入图片描述
    (4)找出工程项目J2使用的各种零件的名称及其数量。
    在这里插入图片描述
    (5)找出上海厂商供应的所有零件号码。
    在这里插入图片描述
    (6)找出使用上海产的零件的工程名称。
    在这里插入图片描述
    (7)找出没有使用天津产的零件的工程号码。
    在这里插入图片描述
    (8)把全部红色零件的颜色改成蓝色。
    在这里插入图片描述
    (9)由S5供给J4的零件P6改为由S3供应,请作必要的修改。
    在这里插入图片描述
    (10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。
    在这里插入图片描述
    (11)请将(S2, J6, P4, 200) 插入供应情况关系。
    在这里插入图片描述

六、实验小结(包括收获、心得体会、注意事项、存在问题及解决办法、建议等)

操作过程中,注意以下几点:

  1. 注意查询要求的详细描述,先确定要查询的表然后确定要输出的列和行。
  2. 输入SQL语句时应注意,除字符串中的中文字符,语句中均使用英文半角操作符号。
  3. 查询执行后没有查询结果呈现,可能是查询语句错误,也可能是实验数据不够丰富,此时,需要对数据库中的数据更新,再进一步去验证。
  4. 对于得不到正确结果的查询,可考虑检查查询条件及子句等来解决。

where 子句和having子句中的区别:

  1. where 不能放在GROUP BY 后面
  2. HAVING 是跟GROUP BY 连在一起用的,放在GROUP BY 后面,此时的作用相当于WHERE
  3. WHERE 后面的条件中不能有聚集函数,比如SUM(),AVG()等,而HAVING 可以

七、附录(包括作品、流程图、源程序及命令清单等)

  1. 第一阶段:(对数据库eshop中数据进行分组统计查询)。

    use eshop;
    
    -- (1)查询性别为“男”的会员详细信息,查询结果按月薪降序排列。
    select * from members WHERE M_sex = '男' ORDER BY M_salary DESC;
    
    -- (2)查询全体会员的会员帐号,姓名和年龄并按家庭地址升序排列,同一地址中的会员按年龄降序排列。
    SELECT m_account, m_name, m_birth FROM members ORDER BY m_address, m_birth
    
    -- (3)查询会员帐号为’liuzc’所购买的商品号和订购日期,并按订购日期升序排列。
    SELECT p_no, o_date FROM orders WHERE m_account = 'liuzc' ORDER BY o_date
    
    -- (4)查询购买商品号为’0910810004’总人数。
    SELECT COUNT(*) FROM orders WHERE p_no = '0910810004'
    
    -- (5)查询2005年6月6日前,所有商品的订购总量,要求输出商品号和订购总量。
    SELECT p_no, SUM(o_quantity) FROM orders WHERE o_date<'2005-6-6' GROUP BY p_no
    
    -- (6)查询所有会员的平均月薪,最高月薪和最低月薪。
    SELECT AVG(m_salary)+MAX(m_salary)+MIN(m_salary) FROM members
    
    -- (7)查询所有会员购买商品的种类和,要求输出会员号和商品种类和。
    SELECT m_account, COUNT(DISTINCT p_no) FROM orders GROUP BY m_account
    
    -- (8)查询各类商品的最高购买数量,要求输出最高数量大于10的商品号和最高数量。
    SELECT TOP 1 p_no, SUM(o_quantity) FROM orders GROUP BY p_no HAVING SUM(o_quantity)>10 ORDER BY SUM(o_quantity)DESC
    
  2. 第二阶段:教材P130 第三章习题4、5

    use spj
    
    --4.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询。
    --(1)求供应工程J1零件的供应商号码SNO;
    SELECT SNO FROM SPJ WHERE JNO= 'J1';
    
    --(2)求供应工程J1零件P1的供应商号码SNO;
    SELECT SNO FROM SPJ WHERE JNO= 'J1' AND PNO= 'P1';
    
    --(3)求供应工程J1零件为红色的供应商号码SNO;
    SELECT SNO /*这是嵌套查询*/
    FROM SPJ WHERE JNO='J1' AND PNO IN /*找出红色零件的零件号码PNO */
    (SELECT PNO FROM P /*从P表中找*/
    WHERE PColor= '红');
    
    --(4)求没有使用天津供应商生产的红色零件的工程号JNO;
    SELECT JNO /*这种解法是使用多重嵌套查询*/
    FROM J /*注意:从J表入手,以包含那些*/
    WHERE NOT EXISTS /*尚未使用任何零件的工程号*/
    (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO
    AND SNO IN
    ( SELECT SNO /*天津供应商的SNO*/
    FROM S WHERE
    SCity='天津')
    AND PNO IN /*红色零件的PNO*/
    ( SELECT PNO
    FROM P WHERE PColor='红'));
    
    --(5)求至少用了供应商S1所供应的全部零件的工程号JNO。
    SELECT DISTINCT JNO
    FROM
    SPJ SPJZ
    WHERE NOT EXISTS
    /*这是一一个相关子查询*/
    ( SELECT * /*父查询和子查询均引用了SPJ 表*/
    FROM SPJ SPJX
    /*用别名SPJZ、SPJX 将父查询*/
    WHERE SNO='S1' /*与子 查询中的SPJ表区分开*/
    AND NOT EXISTS 
    (SELECT *
    /*用别名SPJY与父查询*/
    FROM SPJ SPJY /*中 的SPJ表区分开*/
    WHERE
    SPJY.PNO=SPJX.PNO
    AND SPJY.JNO= SPJZ.JNO));
    
    
    --5.针对习题3中的四个表试用SQL语言完成以下各项操作:
    --(1)找出所有供应商的姓名和所在城市。
    SELECT SNAME, SCITY FROM S;
    
    --(2)找出所有零件的名称、颜色、重量。
    SELECT PName,PColor,PWeight FROM P
    
    --(3)找出使用供应商S1所供应零件的工程号码。
    SELECT JNO FROM SPJ WHERE SNO= 'S1';
    
    --(4)找出工程项目J2使用的各种零件的名称及其数量。
    SELECT P.PNAME, SPJ.QTY FROM P, SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';
    
    --(5)找出上海厂商供应的所有零件号码。
    SELECT DISTINCT PNO FROM SPJ
    WHERE SNO IN
    ( SELECT SNO
    FROM S
    WHERE SCity='上海') ;
    
    --(6)找出使用上海产的零件的工程名称。
    SELECT JNAME FROM J, SPJ, S
    WHERE J.JNO=SPJ.JNO
    AND
    SPJ.SNO=S.SNO
    AND
    S.SCITY='上海';
    
    --(7)找出没有使用天津产的零件的工程号码。
    SELECT JNO
    FROM J
    WHERE NOT EXISTS
    (SELECT
    *
    FROM
    SPJ
    WHERE SPJ.JNO=J.JNO
    AND
    SNO IN
    (SELECT SNO
    FROM
    S
    WHERE SCITY= '天津'));
    
    --(8)把全部红色零件的颜色改成蓝色。
    UPDATE P
    SET
    PColor='蓝'
    WHERE PColor='红' ;
    
    --(9)由S5供给J4的零件P6改为由S3供应,请作必要的修改。
    UPDATE SPJ
    SET
    SNO='S3'
    WHERE SNO='S5'
    AND
    JNO='J4'
    AND PNO='P6';
    
    --(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。
    DELETE
    FROM
    SPJ
    WHERE SNO='S2';
    DELETE
    FROM
    S
    WHERE
    SNO='S2';
    
    --(11)请将(S2, J6, P4, 200) 插入供应情况关系。
    INSERT
    INTO
    SPJ(SNO, JNO, PNO, QTY) /*INTO 子句中指明列名*/
    VALUES('S2', 'J6', 'P4', 200);
    /*插入的属性值与指明列要对应*/
    
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页