2021-02-18

数据库实验代码

数据库定义.sql
CREATE DATABASE TPCH ENCODING = 'GBK';

CREATE SCHEMA Sales;

SET SEARCH_PATH TO Sales,Public;
CREATE TABLE Region (
	regionkey INT PRIMARY KEY,
	name CHAR(25),
	comment VARCHAR(152) );

CREATE TABLE Nation (
	nationkey INT PRIMARY KEY,
	name CHAR(25),
	regionkey INT REFERENCES Region(regionkey),
	comment VARCHAR(152) );

CREATE TABLE Supplier (
	suppkey INT PRIMARY KEY,
	name CHAR(25),
	address VARCHAR(40),
	nationkey INT REFERENCES Nation(nationkey),
	phone CHAR(15),
	acctabl REAL,
	comment VARCHAR(101) );

CREATE TABLE Part (
	partkey INT PARMARY KEY,
	name VARCHAR(55),
	mfgr CHAR(25),
	brand CHAR(10),
	type VARCHAR(25),
	size INT,
	container CHAR(10),
	retailprice REAL,
	comment VARCHAR(23) );

CREATE TABLE PartSupp (
	partkey INT REFERENCES Part(partkey),
	suppkey INT REFERENCES Supplier(suppkey),
	availqty INT,
	supplycost REAL,
	comment VARCHAR(199),
	PRIMARY KEY(partkey,suppkey)

CREATE TABLE Customer (
	cuskey INT PRIMARY KEY,
	name VARCHAR(25),
	address VARCHAR(40),
	nationkey INT REFERENCES Nation(nationkey),
	phone CHAR(15),
	acctbal REAL,
	mktsegment CHAR(10),
	comment VARCHAR(117) );

CREATE TABLE Orders (
	orderkey INT PRIMARY KEY,
	custkey INT REFERENCES Customer(custkey),
	orderstatus CHAR(1),
	totalprice REAL,
	orderdate DATE,
	orderpriority CHAR(15),
	clerk CHAR(15),
	shippriority INT,
	comment VARCHAR(49) );

CREATE TABLE Lineitem (
	orderkey INT REFERENCES Orders(orderkey),
	partkey INT REFERENCES Part(partkey),
	suppkey INT REFERENCES Supplier(suppkey),
	linenumber INT,
	quantity REAL,
	extendedprice REAL,
	discount REAL,
	tax REAL,
	returnflag CHAR(1),
	linestatus CHAR(1),
	shipdate DATE,
	commitdate DATE,
	receiptdate DATE,
	shipinstruce CHAR(25),
	shipmode CHAR(10),
	comment VARCHAR(44),
	PRIMARY KEY(orderkey,linenumber),
	FOREIGN KEY(partkey,suppkey) REFERENCES PartSupp(partkey,suppkey) );

数据查询.sql
SELECT name,address,phone
FROM Supplier;

SELECT *
FROM Sales.Orders
WHERE CURRENT_DATE - orderdate < 7 AND totalprice > 1000;
/*这里DATE可能有问题，可以改成timeseat？*/

SELECT C.custkey,SUM(O.totalprice)
FROM Customer C,Order O
WHERE C.custkey = O.custkey
GROUP BY C.custkey;

SELECT C.custkey,MAX(C.name)
FROM Customer C,Order O
WHERE C.custkey = O.custkey
GROUP BY C.custkey;
HAVING AVG(O.totalprice) > 1000;

SELECT F.suppkey,F.name,F.address
FROM Supplier F,Supplier S
WHERE F.nationkey = S.nationkey AND S.name = '金仓集团';

SELECT P.name,P.mfgr,P.retailprice,PS.supplycost
FROM Supplier F,PartSupp S
WHERE P.retailprice > PS.supplycost;

SELECT P.name,P.mfgr,P.retailoprice,PS.supplycost
FROM Part P,PartSupp PS
WHERE P.partkey = PS.partkey
		AND P.etailprice > PS.supplycost;

SELECT O.orderkey,O.totalprice,L.partkey,L.quantity,L.extendedprice
FROM Customer C,Order O,Lineitem L
WHERE C.custkey = O.custkey AND O.orderkey = L.orderkey AND C.name = '苏举库';

数据高级查询.sql
SELECT custkey,name
FROM Customer
WHERE custkey IN (SELECT O.custkey
				  FROM Order O,Lineitem L,PartSupp PS,Part P
				  WHERE O.orderkey = L.orderkey AND
						L.partkey = PS.partkey AND
						L.suppkey = PS.suppkey AND
						PS.partkey = P.partkey AND
						P.mfgr = '海大' AND P.name = '船舶模拟驾驶舱');

SELECT custkey,name
FROM Customer
WHERE custkey IN (SELECT O.custkey
				  FROM Order O,Lineitem L,Part P
				  WHERE O.orderkey = L.orderkey AND
						L.partkey = P.partkey AND
						P.mfgr = '海大' AND P.name = '船舶模拟驾驶舱');

SELECT custkey,name
FROM Customer C
WHERE NOT EXISTS (SELECT O.custkey 
				  FROM Order O,Lineitem L,PartSupp PS,Part P
				  WHERE C.custkey = O.custkey AND
						O.orderkey = L.orderkey AND
						L.partkey = PS.partkey AND
						L.suppkey = PS.suppkey AND
						PS.partkey = P.partkey AND
						P.mfgr = '海大' AND P.name = '船舶模拟驾驶舱');

SELECT CA.name
FROM Customer CA
WHERE NOT EXISTS (SELECT *
				  FROM Customer CB,Order OB,Lineitem LB
				  WHERE CB.custkey = OB.custkey AND
						OB.orderkey = LB.orderkey AND
						CB.name = '张三' AND
						NOT EXISTS (SELECT *
									FROM Order OC,Lineitem LC
									WHERE CA.custkey = OC.custkey AND
										  OC.orderkey = LC.orderkey AND
										  LB.suppkey = LC.suppkey AND
										  LB.partkey = LC.partkey) );