数据库系统原理-实验二

一、实验目的：

掌握使用 SQL 语言进行各种查询的操作和视图的操纵方法。

二、实验要求：

在现有的数据库上进行各种查询操作，对视图的创建、使用等操作。

三、实验步骤：

1、 开始→程序→Microsoft SQL Server→SQL Server Management Studio。

2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进 入 SQL Server Management Studio 操作界面。

3、单击“新建查询”按钮，在上部的空白区导入文件“实验二 SQL 语句.doc”中 的 SQL 语句，然后单击工具栏上 “执行”按钮（红色叹号）。建立起实验要操 作的数据库 test1，并在库中建立表、数据。下部的空白区显示该语句的运行情 况。（注：以下操作均在上部的空白区输入 SQL 语句，单击执行后，下部的空 白区显示该语句的运行情况。）

4、在数据库 test1 中进行下列查询操作，将查询语句与结果写入实验报告。

以下为实验中的操作以及相应的sql 语句：

(1)
SELECT *
FROM test1.supplier
ORDER BY city ASC, sname DESC;

(2)
SELECT *
FROM test1.part
ORDER BY pname ASC, weight DESC;

(3)
SELECT *
FROM test1.project
WHERE pname LIKE '%厂%';

(4)
SELECT *
FROM test1.supplier
WHERE substring(sname, 2, 1) = '方';

(5)
SELECT MAX(weight), MIN(weight), AVG(weight)
FROM test1.part;

(6)
SELECT COUNT(DISTINCT color) as num, AVG(weight)
FROM test1.part
WHERE pname = '螺丝刀';

(7)
SELECT test1.partsupp.pid, test1.part.pname, test1.partsupp.sid, test1.partsupp.qty
FROM test1.partsupp
JOIN test1.part ON test1.partsupp.pid = test1.part.pid
WHERE test1.partsupp.sid = 'S1';

(8)
SELECT test1.partsuse.pid, test1.part.pname, test1.partsuse.jid, test1.partsuse.qty
FROM test1.partsuse
JOIN test1.part ON test1.partsuse.pid = test1.part.pid
WHERE test1.partsuse.jid = 'J1';

(9)
SELECT DISTINCT pname
FROM test1.partsuse
JOIN test1.part ON test1.partsuse.pid = test1.part.pid
WHERE test1.partsuse.jid NOT IN (
SELECT jid
FROM test1.partsuse
JOIN test1.part ON test1.partsuse.pid = test1.part.pid
WHERE test1.part.color = '红'
);

(10)
SELECT DISTINCT sname
FROM test1.partsupp
JOIN test1.supplier ON test1.partsupp.sid = test1.supplier.sid
WHERE test1.partsupp.pid NOT IN (
SELECT pid
FROM test1.partsupp
JOIN test1.part ON test1.partsupp.pid = test1.part.pid
WHERE test1.part.color = '红'
);

(11)
SELECT jid
FROM test1.partsuse
GROUP BY jid
HAVING SUM(qty) > 500;

(12)
SELECT jid
FROM test1.partsuse
JOIN test1.part ON test1.partsuse.pid = test1.part.pid
GROUP BY jid
HAVING COUNT(DISTINCT test1.partsuse.pid) > 3;

(13)
SELECT jid
FROM test1.partsuse
GROUP BY jid
HAVING COUNT(DISTINCT pid) = (SELECT COUNT(*) FROM test1.part);

(14)
SELECT test1.partsupp.sid
FROM test1.partsuse
JOIN test1.partsupp ON test1.partsuse.pid = test1.partsupp.pid AND test1.partsuse.jid = 'J1'
GROUP BY test1.partsupp.sid
HAVING COUNT(DISTINCT test1.partsuse.pid) = (SELECT COUNT(DISTINCT pid) FROM test1.partsuse WHERE jid = 'J1');

(15)
SELECT test1.supplier.sname, test1.part.pname, test1.project.pname, test1.partsupp.qty
FROM test1.partsupp
JOIN test1.supplier ON test1.partsupp.sid = test1.supplier.sid
JOIN test1.part ON test1.partsupp.pid = test1.part.pid
JOIN test1.project ON test1.partsupp.jid = test1.project.jid
ORDER BY test1.supplier.sname;

(16)
SELECT test1.part.pname, test1.project.pname, test1.partsupp.qty
FROM test1.partsupp
JOIN test1.supplier ON test1.partsupp.sid = test1.supplier.sid
JOIN test1.part ON test1.partsupp.pid = test1.part.pid
JOIN test1.project ON test1.partsupp.jid = test1.project.jid
WHERE test1.supplier.sname = '东方红';

-- 建立视图
CREATE VIEW v_supplier_part AS
SELECT partsupp.sid, partsupp.pid, partsupp.qty
FROM test1.partsupp
JOIN test1.project ON partsupp.jid = project.jid
WHERE project.pname = '机车厂工程项目';

-- (1)
SELECT pid, SUM(qty) as sum_qty
FROM v_supplier_part
GROUP BY pid;

-- (2)
SELECT *
FROM test1.partsupp
JOIN test1.supplier ON partsupp.sid = supplier.sid
WHERE supplier.sname = 'S2';