· 8 years ago · Apr 22, 2018, 12:10 PM
1CREATE DATABASE IF NOT EXISTS `Klientai_3PL1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
2USE `Klientai_3PL1`;
3/* ----- Table: StateRegion */
4CREATE TABLE StateRegion
5(
6 ID bigint PRIMARY KEY AUTO_INCREMENT,
7 FullName varchar(50) NOT NULL,
8 Abbreviation varchar(2) NOT NULL,
9 RegionType smallint NOT NULL,
10 Admitted date NULL,
11 Capital varchar(50) NULL
12);
13
14
15INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
16 ('Alabama', 'AL', 1, '1819-12-14', 'Montgomery');
17INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
18 ('Alaska', 'AK', 1, '1959-01-03', 'Juneau');
19INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
20 ('Arizona', 'AZ', 1, '1912-02-14', 'Phoenix');
21INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
22 ('Arkansas', 'AR', 1, '1836-06-15', 'Little Rock');
23INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
24 ('California', 'CA', 1, '1850-09-09', 'Sacramento');
25INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
26 ('Colorado', 'CO', 1, '1876-08-01', 'Denver');
27INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
28 ('Connecticut', 'CT', 1, '1788-01-09', 'Hartford');
29INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
30 ('Delaware', 'DE', 1, '1787-12-07', 'Dover');
31INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
32 ('Florida', 'FL', 1, '1845-03-03', 'Tallahassee');
33INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
34 ('Georgia', 'GA', 1, '1788-01-02', 'Atlanta');
35INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
36 ('Hawaii', 'HI', 1, '1959-08-21', 'Honolulu');
37INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
38 ('Idaho', 'ID', 1, '1890-07-03', 'Boise');
39INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
40 ('Illinois', 'IL', 1, '1818-12-03', 'Springfield');
41INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
42 ('Indiana', 'IN', 1, '1816-12-11', 'Indianapolis');
43INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
44 ('Iowa', 'IA', 1, '1846-12-28', 'Des Moines');
45INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
46 ('Kansas', 'KS', 1, '1861-01-29', 'Topeka');
47INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
48 ('Kentucky', 'KY', 1, '1792-06-01', 'Frankfort');
49INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
50 ('Louisiana', 'LA', 1, '1812-04-30', 'Baton Rouge');
51INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
52 ('Maine', 'ME', 1, '1820-03-15', 'Augusta');
53INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
54 ('Maryland', 'MD', 1, '1788-04-28', 'Annapolis');
55INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
56 ('Massachusetts', 'MA', 1, '1788-02-06', 'Boston');
57INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
58 ('Michigan', 'MI', 1, '1837-01-26', 'Lansing');
59INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
60 ('Minnesota', 'MN', 1, '1858-05-11', 'St. Paul');
61INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
62 ('Mississippi', 'MS', 1, '1817-12-10', 'Jackson');
63INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
64 ('Missouri', 'MO', 1, '1821-08-10', 'Jefferson City');
65INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
66 ('Montana', 'MT', 1, '1889-11-08', 'Helena');
67INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
68 ('Nebraska', 'NE', 1, '1867-03-01', 'Lincoln');
69INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
70 ('Nevada', 'NV', 1, '1864-10-31', 'Carson City');
71INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
72 ('New Hampshire', 'NH', 1, '1788-06-21', 'Concord');
73INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
74 ('New Jersey', 'NJ', 1, '1787-12-18', 'Trenton');
75INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
76 ('New Mexico', 'NM', 1, '1912-01-06', 'Santa Fe');
77INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
78 ('New York', 'NY', 1, '1788-07-26', 'Albany');
79INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
80 ('North Carolina', 'NC', 1, '1789-11-21', 'Raleigh');
81INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
82 ('North Dakota', 'ND', 1, '1889-11-02', 'Bismarck');
83INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
84 ('Ohio', 'OH', 1, '1803-03-01', 'Columbus');
85INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
86 ('Oklahoma', 'OK', 1, '1907-11-16', 'Oklahoma City');
87INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
88 ('Oregon', 'OR', 1, '1859-02-14', 'Salem');
89INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
90 ('Pennsylvania', 'PA', 1, '1787-12-12', 'Harrisburg');
91INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
92 ('Rhode Island', 'RI', 1, '1790-05-29', 'Providence');
93INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
94 ('South Carolina', 'SC', 1, '1788-05-23', 'Columbia');
95INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
96 ('South Dakota', 'SD', 1, '1889-11-02', 'Pierre');
97INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
98 ('Tennessee', 'TN', 1, '1796-06-01', 'Nashville');
99INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
100 ('Texas', 'TX', 1, '1845-12-29', 'Austin');
101INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
102 ('Utah', 'UT', 1, '1896-01-04', 'Salt Lake City');
103INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
104 ('Vermont', 'VT', 1, '1791-03-04', 'Montpelier');
105INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
106 ('Virginia', 'VA', 1, '1788-06-25', 'Richmond');
107INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
108 ('Washington', 'WA', 1, '1889-11-11', 'Olympia');
109INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
110 ('West Virginia', 'WV', 1, '1863-06-20', 'Charleston');
111INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
112 ('Wisconsin', 'WI', 1, '1848-05-29', 'Madison');
113INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
114 ('Wyoming', 'WY', 1, '1890-07-10', 'Cheyenne');
115INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital) VALUES
116 ('District of Columbia', 'DC', 2, NULL, NULL);
117
118
119/* ----- Table: Customer */
120CREATE TABLE Customer
121(
122 ID bigint PRIMARY KEY AUTO_INCREMENT,
123 FullName varchar(50) NOT NULL,
124 Address1 varchar(50) NULL,
125 Address2 varchar(50) NULL,
126 City varchar(25) NULL,
127 StateRegion bigint NULL,
128 PostalCode varchar(10) NULL,
129 PhoneNumber varchar(15) NULL,
130 WebSite varchar(150) NULL,
131 AnnualFee int NOT NULL DEFAULT 0
132);
133alter table Customer add foreign key(StateRegion) References StateRegion(ID);
134
135INSERT INTO Customer (ID, FullName, WebSite, AnnualFee,
136 Address1, City, PostalCode, StateRegion) VALUES
137 (null, 'Coho Vineyard', 'http://www.cohovineyard.com', 200,
138 '123 Main Street', 'Albany', '85000',
139 (SELECT ID FROM StateRegion WHERE Abbreviation = 'NY'));
140INSERT INTO Customer (ID, FullName, WebSite, AnnualFee,
141 Address1, City, PostalCode, StateRegion) VALUES
142 (null, 'Fourth Coffee', 'http://www.fourthcoffee.com', 200,
143 '9876 Oak Boulevard', 'Los Angeles', '00110',
144 (SELECT ID FROM StateRegion WHERE Abbreviation = 'CA'));
145INSERT INTO Customer (ID, FullName, WebSite, AnnualFee,
146 Address1, City, PostalCode, StateRegion) VALUES
147 (null, 'Tailspin Toys', 'http://www.tailspintoys.com', 150,
148 '23 Jefferson Avenue', 'Hamilton', '77002',
149 (SELECT ID FROM StateRegion WHERE Abbreviation = 'MT'));
150
151
152
153
154
155/* ----- Table: OrderEntry */
156CREATE TABLE OrderEntry
157(
158 ID bigint PRIMARY KEY AUTO_INCREMENT,
159 CustomerID bigint NOT NULL ,
160 OrderDate date NOT NULL,
161 ShipDate date NULL,
162 Subtotal int NOT NULL,
163 SalesTax int NOT NULL,
164 Total int NOT NULL,
165 StatusCode varchar(1) NOT NULL /* P=Pending, C=Complete, X=Canceled */
166);
167Alter table OrderEntry add FOREIGN KEY(CustomerID) REFERENCES Customer(ID);
168
169INSERT INTO OrderEntry (CustomerID, OrderDate, ShipDate, Subtotal, SalesTax, Total, StatusCode) VALUES
170 (1, '2010-03-01', '2010-03-09', 392.04, 34.30, 426.34, 'C');
171INSERT INTO OrderEntry (CustomerID, OrderDate, Subtotal, SalesTax, Total, StatusCode) VALUES
172 (1, '2010-03-10', 900.00, 78.75, 978.75, 'X');
173INSERT INTO OrderEntry (CustomerID, OrderDate, Subtotal, SalesTax, Total, StatusCode) VALUES
174 (2, '2010-02-23', 197.88, 17.31, 215.19, 'C');