《数据库系统实训》实验报告——子查询与组合查询

灰太狼 2022-11-16 05:48 228阅读 0赞

# **子查询** #

**题号：** **1.1**

**SQL****语句描述：**select cust\_id from orders where order\_num in (SELECT ORDER\_NUM FROM ORDERITEMS where prod\_id='tnt2');

**查询功能描述：**查询购买ID为tnt2的产品的客户；

**执行结果截图：**![20210407184529113.png][]

# **联结表** #

**题号：** **2.1**

**SQL****语句描述：**select vend\_name,prod\_name,prod\_price

from vendors,products

where vendors.vend\_id=products.vend\_id

order by vend\_name,prod\_name;

**查询功能描述：**查询所有供应商名称及产品名称、价格，并且按供应商和产品名称排序；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70][]

# **高级联结** #

**题号：** **3.1**

**SQL****语句描述：**select customers.cust\_id,

customers.cust\_name,

count(orders.order\_num) as num\_ord

from customers inner join orders

on customers.cust\_id=orders.cust\_id

group by customers.cust\_id;

**查询功能描述：**查询所有顾客的订单数量；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1][]

# **组合查询** #

**题号：** **4.1**

**SQL****语句描述：**select vend\_id,prod\_id,prod\_price

from products

where prod\_price<=5

union

select vend\_id,prod\_id,prod\_price

from products

where vend\_id in(1001,1002);

**查询功能描述：**查询供应商ID为1001，1002和价格小于等于5的供应商ID及产品名称、价格；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 2][]

# **全文搜索** #

**题号：** **5.1**

**SQL****语句描述：**select note\_text from productnotes where match(note\_text) against('rabbit');

**查询功能描述：**查询含有rabbit的文本内容；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 3][]

[20210407184529113.png]: /images/20221022/bd479b0b4ea4486cab760ab081fe332c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70]: /images/20221022/39abcb00dcea44c9b16e484d5bddbaa5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1]: /images/20221022/598b660dc5984c2aa5b047188bb05697.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 2]: /images/20221022/2a81943e2012485ea420d1d1c553c3d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 3]: /images/20221022/212e27a76f0141339e2fae27f4e5062f.png