95 lines
2.4 KiB
Transact-SQL
95 lines
2.4 KiB
Transact-SQL
CREATE DATABASE TPCH;
|
|
Go
|
|
USE TPCH;
|
|
Go
|
|
CREATE SCHEMA Sales;
|
|
Go
|
|
CREATE TABLE Sales.Region
|
|
(
|
|
regionkey INTEGER PRIMARY KEY,
|
|
name CHAR(25),
|
|
comment VARCHAR(152)
|
|
);
|
|
CREATE TABLE Sales.Nation
|
|
(
|
|
nationkey INTEGER PRIMARY KEY,
|
|
name CHAR(25),
|
|
regionkey INTEGER REFERENCES Sales.Region (regionkey),
|
|
comment VARCHAR(152)
|
|
);
|
|
CREATE TABLE Sales.Supplier
|
|
(
|
|
suppkey INTEGER PRIMARY KEY,
|
|
name CHAR(25),
|
|
address VARCHAR(40),
|
|
nationkey INTEGER REFERENCES Sales.Nation (nationkey),
|
|
phone CHAR(15),
|
|
acctbal REAL,
|
|
comment VARCHAR(101)
|
|
);
|
|
CREATE TABLE Sales.Part
|
|
(
|
|
partkey INTEGER PRIMARY KEY,
|
|
name VARCHAR(55),
|
|
mfgr CHAR(25),
|
|
brand CHAR(10),
|
|
type VARCHAR(25),
|
|
size INTEGER,
|
|
container CHAR(10),
|
|
retailprice REAL,
|
|
comment VARCHAR(23)
|
|
);
|
|
CREATE TABLE Sales.Partsupp
|
|
(
|
|
partkey INTEGER REFERENCES Sales.Part (partkey),
|
|
suppkey INTEGER REFERENCES Sales.Supplier (suppkey),
|
|
availqty INTEGER,
|
|
supplycost REAL,
|
|
comment VARCHAR(199),
|
|
PRIMARY KEY (partkey, suppkey)
|
|
);
|
|
CREATE TABLE Sales.Customer
|
|
(
|
|
custkey INTEGER PRIMARY KEY,
|
|
name VARCHAR(25),
|
|
address VARCHAR(40),
|
|
nationkey INTEGER REFERENCES Sales.Nation (nationkey),
|
|
phone CHAR(15),
|
|
acctbal REAL,
|
|
mktsegment CHAR(10),
|
|
comment VARCHAR(117)
|
|
);
|
|
CREATE TABLE Sales.Orders
|
|
(
|
|
orderkey INTEGER PRIMARY KEY,
|
|
custkey INTEGER REFERENCES Sales.Customer (custkey),
|
|
orderstatus CHAR(1),
|
|
totalprice REAL,
|
|
orderdate DATE,
|
|
orderpriority CHAR(15),
|
|
clerk CHAR(15),
|
|
shippriority INTEGER,
|
|
comment VARCHAR(79)
|
|
);
|
|
CREATE TABLE Sales.Lineitem
|
|
(
|
|
orderkey INTEGER REFERENCES Sales.Orders (orderkey),
|
|
partkey INTEGER REFERENCES Sales.Part (partkey),
|
|
suppkey INTEGER REFERENCES Sales.Supplier (suppkey),
|
|
linenumber INTEGER,
|
|
quantity REAL,
|
|
extendedprice REAL,
|
|
discount REAL,
|
|
tax REAL,
|
|
returnflag CHAR(1),
|
|
linestatus CHAR(1),
|
|
shipdate DATE,
|
|
commitdate DATE,
|
|
receiptdate DATE,
|
|
shipinstruct CHAR(25),
|
|
shipmode CHAR(10),
|
|
comment VARCHAR(44),
|
|
PRIMARY KEY (orderkey, linenumber),
|
|
FOREIGN KEY (partkey, suppkey) REFERENCES Sales.Partsupp (partkey, suppkey)
|
|
);
|
|
Go |