-
Notifications
You must be signed in to change notification settings - Fork 0
/
4. Postgres_Script.sql
693 lines (589 loc) · 26.3 KB
/
4. Postgres_Script.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
-- ------------ Write CREATE-DATABASE-stage scripts -----------
CREATE SCHEMA IF NOT EXISTS sourcefuse_db_dbo;
-- ------------ Write CREATE-TABLE-stage scripts -----------
CREATE TABLE sourcefuse_db_dbo."Order Details"(
orderid INTEGER NOT NULL,
productid INTEGER NOT NULL,
unitprice NUMERIC(19,4) NOT NULL DEFAULT (0),
quantity SMALLINT NOT NULL DEFAULT (1),
discount DOUBLE PRECISION NOT NULL DEFAULT (0)
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.categories(
categoryid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
categoryname VARCHAR(15) NOT NULL,
description TEXT,
picture BYTEA
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.customercustomerdemo(
customerid CHAR(5) NOT NULL,
customertypeid CHAR(10) NOT NULL
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.customerdemographics(
customertypeid CHAR(10) NOT NULL,
customerdesc TEXT
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.customers(
customerid CHAR(5) NOT NULL,
companyname VARCHAR(40) NOT NULL,
contactname VARCHAR(30),
contacttitle VARCHAR(30),
address VARCHAR(60),
city VARCHAR(15),
region VARCHAR(15),
postalcode VARCHAR(10),
country VARCHAR(15),
phone VARCHAR(24),
fax VARCHAR(24)
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.employees(
employeeid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
lastname VARCHAR(20) NOT NULL,
firstname VARCHAR(10) NOT NULL,
title VARCHAR(30),
titleofcourtesy VARCHAR(25),
birthdate TIMESTAMP WITHOUT TIME ZONE,
hiredate TIMESTAMP WITHOUT TIME ZONE,
address VARCHAR(60),
city VARCHAR(15),
region VARCHAR(15),
postalcode VARCHAR(10),
country VARCHAR(15),
homephone VARCHAR(24),
extension VARCHAR(4),
photo BYTEA,
notes TEXT,
reportsto INTEGER,
photopath VARCHAR(255)
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.employeeterritories(
employeeid INTEGER NOT NULL,
territoryid VARCHAR(20) NOT NULL
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.orders(
orderid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
customerid CHAR(5),
employeeid INTEGER,
orderdate TIMESTAMP WITHOUT TIME ZONE,
requireddate TIMESTAMP WITHOUT TIME ZONE,
shippeddate TIMESTAMP WITHOUT TIME ZONE,
shipvia INTEGER,
freight NUMERIC(19,4) DEFAULT (0),
shipname VARCHAR(40),
shipaddress VARCHAR(60),
shipcity VARCHAR(15),
shipregion VARCHAR(15),
shippostalcode VARCHAR(10),
shipcountry VARCHAR(15)
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.products(
productid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
productname VARCHAR(40) NOT NULL,
supplierid INTEGER,
categoryid INTEGER,
quantityperunit VARCHAR(20),
unitprice NUMERIC(19,4) DEFAULT (0),
unitsinstock SMALLINT DEFAULT (0),
unitsonorder SMALLINT DEFAULT (0),
reorderlevel SMALLINT DEFAULT (0),
discontinued NUMERIC(1,0) NOT NULL DEFAULT (0)
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.region(
regionid INTEGER NOT NULL,
regiondescription CHAR(50) NOT NULL
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.shippers(
shipperid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
companyname VARCHAR(40) NOT NULL,
phone VARCHAR(24)
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.suppliers(
supplierid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
companyname VARCHAR(40) NOT NULL,
contactname VARCHAR(30),
contacttitle VARCHAR(30),
address VARCHAR(60),
city VARCHAR(15),
region VARCHAR(15),
postalcode VARCHAR(10),
country VARCHAR(15),
phone VARCHAR(24),
fax VARCHAR(24),
homepage TEXT
)
WITH (
OIDS=FALSE
);
CREATE TABLE sourcefuse_db_dbo.territories(
territoryid VARCHAR(20) NOT NULL,
territorydescription CHAR(50) NOT NULL,
regionid INTEGER NOT NULL
)
WITH (
OIDS=FALSE
);
-- ------------ Write CREATE-VIEW-stage scripts -----------
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Alphabetical list of products" (productid, productname, supplierid, categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued, categoryname) AS
SELECT
sourcefuse_db_dbo.products.*, categories.categoryname
FROM sourcefuse_db_dbo.categories
INNER JOIN sourcefuse_db_dbo.products
ON categories.categoryid = products.ix_products_categoryid
WHERE (((products.discontinued) = 0));
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Category Sales for 1997" (categoryname, categorysales) AS
SELECT
"Product Sales for 1997".categoryname, SUM("Product Sales for 1997".productsales) AS categorysales
FROM sourcefuse_db_dbo."Product Sales for 1997"
GROUP BY "Product Sales for 1997".categoryname;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Current Product List" (productid, productname) AS
SELECT
product_list.productid, product_list.productname
FROM sourcefuse_db_dbo.products AS product_list
WHERE (((product_list.discontinued) = 0));
/* ORDER BY Product_List.ProductName */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Customer and Suppliers by City" (city, companyname, contactname, relationship) AS
SELECT
city, companyname, contactname, 'Customers'::TEXT AS relationship
FROM sourcefuse_db_dbo.customers
UNION
SELECT
city, companyname, contactname, 'Suppliers'::TEXT
FROM sourcefuse_db_dbo.suppliers;
/* ORDER BY City, CompanyName */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Order Details Extended" (orderid, productid, productname, unitprice, quantity, discount, extendedprice) AS
SELECT
"Order Details".orderid, "Order Details".productid, products.productname, "Order Details".unitprice, "Order Details".quantity, "Order Details".discount, (CAST (("Order Details".unitprice * quantity * (1 - discount) / 100) AS NUMERIC(19, 4)) * 100) AS extendedprice
FROM sourcefuse_db_dbo.products
INNER JOIN sourcefuse_db_dbo."Order Details"
ON products.productid = "Order Details"."IX_Order Details_ProductID";
/* ORDER BY "Order Details".OrderID */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Order Subtotals" (orderid, subtotal) AS
SELECT
"Order Details".orderid, SUM(CAST (("Order Details".unitprice * quantity * (1 - discount) / 100) AS NUMERIC(19, 4)) * 100) AS subtotal
FROM sourcefuse_db_dbo."Order Details"
GROUP BY "Order Details"."IX_Order Details_OrderID";
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Orders Qry" (orderid, customerid, employeeid, orderdate, requireddate, shippeddate, shipvia, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry, companyname, address, city, region, postalcode, country) AS
SELECT
orders.orderid, orders.customerid, orders.employeeid, orders.orderdate, orders.requireddate, orders.shippeddate, orders.shipvia, orders.freight, orders.shipname, orders.shipaddress, orders.shipcity, orders.shipregion, orders.shippostalcode, orders.shipcountry, customers.companyname, customers.address, customers.city, customers.region, customers.postalcode, customers.country
FROM sourcefuse_db_dbo.customers
INNER JOIN sourcefuse_db_dbo.orders
ON LOWER(customers.customerid) = LOWER(orders.ix_orders_customerid);
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Product Sales for 1997" (categoryname, productname, productsales) AS
SELECT
categories.categoryname, products.productname, SUM(CAST (("Order Details".unitprice * quantity * (1 - discount) / 100) AS NUMERIC(19, 4)) * 100) AS productsales
FROM (sourcefuse_db_dbo.categories
INNER JOIN sourcefuse_db_dbo.products
ON categories.categoryid = products.ix_products_categoryid)
INNER JOIN (sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Details"
ON orders.orderid = "Order Details"."IX_Order Details_OrderID")
ON products.productid = "Order Details"."IX_Order Details_ProductID"
WHERE (((orders.ix_orders_shippeddate) BETWEEN '19970101' AND '19971231'))
GROUP BY categories.ix_categories_categoryname, products.ix_products_productname;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Products Above Average Price" (productname, unitprice) AS
SELECT
products.productname, products.unitprice
FROM sourcefuse_db_dbo.products
WHERE products.unitprice > (SELECT
AVG(unitprice)
FROM sourcefuse_db_dbo.products);
/* ORDER BY Products.UnitPrice DESC */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Products by Category" (categoryname, productname, quantityperunit, unitsinstock, discontinued) AS
SELECT
categories.categoryname, products.productname, products.quantityperunit, products.unitsinstock, products.discontinued
FROM sourcefuse_db_dbo.categories
INNER JOIN sourcefuse_db_dbo.products
ON categories.categoryid = products.ix_products_categoryid
WHERE products.discontinued <> 1;
/* ORDER BY Categories.CategoryName, Products.ProductName */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Quarterly Orders" (customerid, companyname, city, country) AS
SELECT DISTINCT
customers.customerid, customers.companyname, customers.city, customers.country
FROM sourcefuse_db_dbo.customers
RIGHT OUTER JOIN sourcefuse_db_dbo.orders
ON LOWER(customers.customerid) = LOWER(orders.ix_orders_customerid)
WHERE orders.ix_orders_orderdate BETWEEN '19970101' AND '19971231';
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Sales Totals by Amount" (saleamount, orderid, companyname, shippeddate) AS
SELECT
"Order Subtotals".subtotal AS saleamount, orders.orderid, customers.companyname, orders.shippeddate
FROM sourcefuse_db_dbo.customers
INNER JOIN (sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Subtotals"
ON orders.orderid = "Order Subtotals".orderid)
ON LOWER(customers.customerid) = LOWER(orders.ix_orders_customerid)
WHERE ("Order Subtotals".subtotal > 2500) AND (orders.ix_orders_shippeddate BETWEEN '19970101' AND '19971231');
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Sales by Category" (categoryid, categoryname, productname, productsales) AS
SELECT
categories.categoryid, categories.categoryname, products.productname, SUM("Order Details Extended".extendedprice) AS productsales
FROM sourcefuse_db_dbo.categories
INNER JOIN (sourcefuse_db_dbo.products
INNER JOIN (sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Details Extended"
ON orders.orderid = "Order Details Extended".orderid)
ON products.productid = "Order Details Extended".productid)
ON categories.categoryid = products.ix_products_categoryid
WHERE orders.ix_orders_orderdate BETWEEN '19970101' AND '19971231'
GROUP BY categories.categoryid, categories.ix_categories_categoryname, products.ix_products_productname;
/* ORDER BY Products.ProductName */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Summary of Sales by Quarter" (shippeddate, orderid, subtotal) AS
SELECT
orders.shippeddate, orders.orderid, "Order Subtotals".subtotal
FROM sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Subtotals"
ON orders.orderid = "Order Subtotals".orderid
WHERE orders.ix_orders_shippeddate IS NOT NULL;
/* ORDER BY Orders.ShippedDate */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo."Summary of Sales by Year" (shippeddate, orderid, subtotal) AS
SELECT
orders.shippeddate, orders.orderid, "Order Subtotals".subtotal
FROM sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Subtotals"
ON orders.orderid = "Order Subtotals".orderid
WHERE orders.ix_orders_shippeddate IS NOT NULL;
/* ORDER BY Orders.ShippedDate */;
CREATE OR REPLACE VIEW sourcefuse_db_dbo.invoices (shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry, customerid, customername, address, city, region, postalcode, country, salesperson, orderid, orderdate, requireddate, shippeddate, shippername, productid, productname, unitprice, quantity, discount, extendedprice, freight) AS
SELECT
orders.shipname, orders.shipaddress, orders.shipcity, orders.shipregion, orders.shippostalcode, orders.shipcountry, orders.customerid, customers.companyname AS customername, customers.address, customers.city, customers.region, customers.postalcode, customers.country, (firstname || ' ' || lastname) AS salesperson, orders.orderid, orders.orderdate, orders.requireddate, orders.shippeddate, shippers.companyname AS shippername, "Order Details".productid, products.productname, "Order Details".unitprice, "Order Details".quantity, "Order Details".discount, (CAST (("Order Details".unitprice * quantity * (1 - discount) / 100) AS NUMERIC(19, 4)) * 100) AS extendedprice, orders.freight
FROM sourcefuse_db_dbo.shippers
INNER JOIN (sourcefuse_db_dbo.products
INNER JOIN ((sourcefuse_db_dbo.employees
INNER JOIN (sourcefuse_db_dbo.customers
INNER JOIN sourcefuse_db_dbo.orders
ON LOWER(customers.customerid) = LOWER(orders.ix_orders_customerid))
ON employees.employeeid = orders.ix_orders_employeeid)
INNER JOIN sourcefuse_db_dbo."Order Details"
ON orders.orderid = "Order Details"."IX_Order Details_OrderID")
ON products.productid = "Order Details"."IX_Order Details_ProductID")
ON shippers.shipperid = orders.shipvia;
-- ------------ Write CREATE-INDEX-stage scripts -----------
CREATE INDEX "IX_Order Details_OrderID"
ON sourcefuse_db_dbo."Order Details"
USING BTREE (orderid ASC);
CREATE INDEX "IX_Order Details_OrdersOrder_Details"
ON sourcefuse_db_dbo."Order Details"
USING BTREE (orderid ASC);
CREATE INDEX "IX_Order Details_ProductID"
ON sourcefuse_db_dbo."Order Details"
USING BTREE (productid ASC);
CREATE INDEX "IX_Order Details_ProductsOrder_Details"
ON sourcefuse_db_dbo."Order Details"
USING BTREE (productid ASC);
CREATE INDEX ix_categories_categoryname
ON sourcefuse_db_dbo.categories
USING BTREE (categoryname ASC);
CREATE INDEX ix_customers_city
ON sourcefuse_db_dbo.customers
USING BTREE (city ASC);
CREATE INDEX ix_customers_companyname
ON sourcefuse_db_dbo.customers
USING BTREE (companyname ASC);
CREATE INDEX ix_customers_postalcode
ON sourcefuse_db_dbo.customers
USING BTREE (postalcode ASC);
CREATE INDEX ix_customers_region
ON sourcefuse_db_dbo.customers
USING BTREE (region ASC);
CREATE INDEX ix_employees_lastname
ON sourcefuse_db_dbo.employees
USING BTREE (lastname ASC);
CREATE INDEX ix_employees_postalcode
ON sourcefuse_db_dbo.employees
USING BTREE (postalcode ASC);
CREATE INDEX ix_orders_customerid
ON sourcefuse_db_dbo.orders
USING BTREE (customerid ASC);
CREATE INDEX ix_orders_customersorders
ON sourcefuse_db_dbo.orders
USING BTREE (customerid ASC);
CREATE INDEX ix_orders_employeeid
ON sourcefuse_db_dbo.orders
USING BTREE (employeeid ASC);
CREATE INDEX ix_orders_employeesorders
ON sourcefuse_db_dbo.orders
USING BTREE (employeeid ASC);
CREATE INDEX ix_orders_orderdate
ON sourcefuse_db_dbo.orders
USING BTREE (orderdate ASC);
CREATE INDEX ix_orders_shippeddate
ON sourcefuse_db_dbo.orders
USING BTREE (shippeddate ASC);
CREATE INDEX ix_orders_shippersorders
ON sourcefuse_db_dbo.orders
USING BTREE (shipvia ASC);
CREATE INDEX ix_orders_shippostalcode
ON sourcefuse_db_dbo.orders
USING BTREE (shippostalcode ASC);
CREATE INDEX ix_products_categoriesproducts
ON sourcefuse_db_dbo.products
USING BTREE (categoryid ASC);
CREATE INDEX ix_products_categoryid
ON sourcefuse_db_dbo.products
USING BTREE (categoryid ASC);
CREATE INDEX ix_products_productname
ON sourcefuse_db_dbo.products
USING BTREE (productname ASC);
CREATE INDEX ix_products_supplierid
ON sourcefuse_db_dbo.products
USING BTREE (supplierid ASC);
CREATE INDEX ix_products_suppliersproducts
ON sourcefuse_db_dbo.products
USING BTREE (supplierid ASC);
CREATE INDEX ix_suppliers_companyname
ON sourcefuse_db_dbo.suppliers
USING BTREE (companyname ASC);
CREATE INDEX ix_suppliers_postalcode
ON sourcefuse_db_dbo.suppliers
USING BTREE (postalcode ASC);
-- ------------ Write CREATE-CONSTRAINT-stage scripts -----------
ALTER TABLE sourcefuse_db_dbo."Order Details"
ADD CONSTRAINT ck_discount_853578079 CHECK (
(discount >= (0) AND discount <= (1)));
ALTER TABLE sourcefuse_db_dbo."Order Details"
ADD CONSTRAINT ck_quantity_869578136 CHECK (
(quantity > (0)));
ALTER TABLE sourcefuse_db_dbo."Order Details"
ADD CONSTRAINT ck_unitprice_885578193 CHECK (
(unitprice >= (0)));
ALTER TABLE sourcefuse_db_dbo."Order Details"
ADD CONSTRAINT pk_order_details_757577737 PRIMARY KEY (orderid, productid);
ALTER TABLE sourcefuse_db_dbo.categories
ADD CONSTRAINT pk_categories_325576198 PRIMARY KEY (categoryid);
ALTER TABLE sourcefuse_db_dbo.customercustomerdemo
ADD CONSTRAINT pk_customercustomerdemo_1349579846 PRIMARY KEY (customerid, customertypeid);
ALTER TABLE sourcefuse_db_dbo.customerdemographics
ADD CONSTRAINT pk_customerdemographics_1365579903 PRIMARY KEY (customertypeid);
ALTER TABLE sourcefuse_db_dbo.customers
ADD CONSTRAINT pk_customers_357576312 PRIMARY KEY (customerid);
ALTER TABLE sourcefuse_db_dbo.employees
ADD CONSTRAINT ck_birthdate_293576084 CHECK (
(birthdate < clock_timestamp()));
ALTER TABLE sourcefuse_db_dbo.employees
ADD CONSTRAINT pk_employees_261575970 PRIMARY KEY (employeeid);
ALTER TABLE sourcefuse_db_dbo.employeeterritories
ADD CONSTRAINT pk_employeeterritories_1461580245 PRIMARY KEY (employeeid, territoryid);
ALTER TABLE sourcefuse_db_dbo.orders
ADD CONSTRAINT pk_orders_453576654 PRIMARY KEY (orderid);
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT ck_products_unitprice_677577452 CHECK (
(unitprice >= (0)));
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT ck_reorderlevel_693577509 CHECK (
(reorderlevel >= (0)));
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT ck_unitsinstock_709577566 CHECK (
(unitsinstock >= (0)));
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT ck_unitsonorder_725577623 CHECK (
(unitsonorder >= (0)));
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT pk_products_549576996 PRIMARY KEY (productid);
ALTER TABLE sourcefuse_db_dbo.region
ADD CONSTRAINT pk_region_1413580074 PRIMARY KEY (regionid);
ALTER TABLE sourcefuse_db_dbo.shippers
ADD CONSTRAINT pk_shippers_389576426 PRIMARY KEY (shipperid);
ALTER TABLE sourcefuse_db_dbo.suppliers
ADD CONSTRAINT pk_suppliers_421576540 PRIMARY KEY (supplierid);
ALTER TABLE sourcefuse_db_dbo.territories
ADD CONSTRAINT pk_territories_1429580131 PRIMARY KEY (territoryid);
-- ------------ Write CREATE-FOREIGN-KEY-CONSTRAINT-stage scripts -----------
ALTER TABLE sourcefuse_db_dbo."Order Details"
ADD CONSTRAINT fk_order_details_orders_821577965 FOREIGN KEY (orderid)
REFERENCES sourcefuse_db_dbo.orders (orderid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo."Order Details"
ADD CONSTRAINT fk_order_details_products_837578022 FOREIGN KEY (productid)
REFERENCES sourcefuse_db_dbo.products (productid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.customercustomerdemo
ADD CONSTRAINT fk_customercustomerdemo_1381579960 FOREIGN KEY (customertypeid)
REFERENCES sourcefuse_db_dbo.customerdemographics (customertypeid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.customercustomerdemo
ADD CONSTRAINT fk_customercustomerdemo_customers_1397580017 FOREIGN KEY (customerid)
REFERENCES sourcefuse_db_dbo.customers (customerid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.employees
ADD CONSTRAINT fk_employees_employees_277576027 FOREIGN KEY (reportsto)
REFERENCES sourcefuse_db_dbo.employees (employeeid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.employeeterritories
ADD CONSTRAINT fk_employeeterritories_employees_1477580302 FOREIGN KEY (employeeid)
REFERENCES sourcefuse_db_dbo.employees (employeeid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.employeeterritories
ADD CONSTRAINT fk_employeeterritories_territories_1493580359 FOREIGN KEY (territoryid)
REFERENCES sourcefuse_db_dbo.territories (territoryid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.orders
ADD CONSTRAINT fk_orders_customers_485576768 FOREIGN KEY (customerid)
REFERENCES sourcefuse_db_dbo.customers (customerid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.orders
ADD CONSTRAINT fk_orders_employees_501576825 FOREIGN KEY (employeeid)
REFERENCES sourcefuse_db_dbo.employees (employeeid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.orders
ADD CONSTRAINT fk_orders_shippers_517576882 FOREIGN KEY (shipvia)
REFERENCES sourcefuse_db_dbo.shippers (shipperid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT fk_products_categories_645577338 FOREIGN KEY (categoryid)
REFERENCES sourcefuse_db_dbo.categories (categoryid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.products
ADD CONSTRAINT fk_products_suppliers_661577395 FOREIGN KEY (supplierid)
REFERENCES sourcefuse_db_dbo.suppliers (supplierid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE sourcefuse_db_dbo.territories
ADD CONSTRAINT fk_territories_region_1445580188 FOREIGN KEY (regionid)
REFERENCES sourcefuse_db_dbo.region (regionid)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
-- ------------ Write CREATE-PROCEDURE-stage scripts -----------
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo."Employee Sales by Country"(IN par_beginning_date TIMESTAMP WITHOUT TIME ZONE, IN par_ending_date TIMESTAMP WITHOUT TIME ZONE, INOUT p_refcur refcursor)
AS
$BODY$
BEGIN
OPEN p_refcur FOR
SELECT
employees.country, employees.lastname, employees.firstname, orders.shippeddate, orders.orderid, "Order Subtotals".subtotal AS saleamount
FROM sourcefuse_db_dbo.employees
INNER JOIN (sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Subtotals"
ON orders.orderid = "Order Subtotals".orderid)
ON employees.employeeid = orders.ix_orders_employeeid
WHERE orders.ix_orders_shippeddate BETWEEN par_Beginning_Date AND par_Ending_Date;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo."Sales by Year"(IN par_beginning_date TIMESTAMP WITHOUT TIME ZONE, IN par_ending_date TIMESTAMP WITHOUT TIME ZONE, INOUT p_refcur refcursor)
AS
$BODY$
BEGIN
OPEN p_refcur FOR
SELECT
orders.shippeddate, orders.orderid, "Order Subtotals".subtotal, to_char(shippeddate::DATE, 'YYYY') AS year
FROM sourcefuse_db_dbo.orders
INNER JOIN sourcefuse_db_dbo."Order Subtotals"
ON orders.orderid = "Order Subtotals".orderid
WHERE orders.ix_orders_shippeddate BETWEEN par_Beginning_Date AND par_Ending_Date;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo."Ten Most Expensive Products"(INOUT p_refcur refcursor)
AS
$BODY$
BEGIN
/*
[7674 - Severity CRITICAL - DMS SC can't convert the ROWCOUNT clause of the SET statement. Convert your source code manually.]
SET ROWCOUNT 10
*/
OPEN p_refcur FOR
SELECT
products.productname AS tenmostexpensiveproducts, products.unitprice
FROM sourcefuse_db_dbo.products
ORDER BY products.unitprice DESC NULLS LAST;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo.custorderhist(IN par_customerid CHAR, INOUT p_refcur refcursor)
AS
$BODY$
BEGIN
OPEN p_refcur FOR
SELECT
productname, SUM(quantity) AS total
FROM sourcefuse_db_dbo.products AS p, sourcefuse_db_dbo."Order Details" AS od, sourcefuse_db_dbo.orders AS o, sourcefuse_db_dbo.customers AS c
WHERE LOWER(c.customerid) = LOWER(par_CustomerID) AND LOWER(c.customerid) = LOWER(o.customerid) AND o.orderid = od.orderid AND od.productid = p.productid
GROUP BY productname;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo.custordersdetail(IN par_orderid INTEGER, INOUT p_refcur refcursor)
AS
$BODY$
BEGIN
OPEN p_refcur FOR
SELECT
productname, ROUND(od.unitprice, 2) AS unitprice, quantity, CAST (discount * 100 AS INTEGER) AS discount, ROUND(CAST (quantity * (1 - discount) * od.unitprice AS NUMERIC(19, 4)), 2) AS extendedprice
FROM sourcefuse_db_dbo.products AS p, sourcefuse_db_dbo."Order Details" AS od
WHERE od.productid = p.productid AND od.orderid = par_OrderID;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo.custordersorders(IN par_customerid CHAR, INOUT p_refcur refcursor)
AS
$BODY$
BEGIN
OPEN p_refcur FOR
SELECT
orderid, orderdate, requireddate, shippeddate
FROM sourcefuse_db_dbo.orders
WHERE LOWER(customerid) = LOWER(par_CustomerID)
ORDER BY orderid NULLS FIRST;
END;
$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE sourcefuse_db_dbo.salesbycategory(IN par_categoryname VARCHAR, IN par_ordyear VARCHAR DEFAULT '1998', INOUT p_refcur refcursor DEFAULT NULL)
AS
$BODY$
BEGIN
IF LOWER(par_OrdYear) != LOWER('1996') AND LOWER(par_OrdYear) != LOWER('1997') AND LOWER(par_OrdYear) != LOWER('1998') THEN
SELECT
'1998'
INTO par_OrdYear;
END IF;
OPEN p_refcur FOR
SELECT
productname, ROUND(SUM(CAST (od.quantity * (1 - od.discount) * od.unitprice AS NUMERIC(14, 2))), 0) AS totalpurchase
FROM sourcefuse_db_dbo."Order Details" AS od, sourcefuse_db_dbo.orders AS o, sourcefuse_db_dbo.products AS p, sourcefuse_db_dbo.categories AS c
WHERE od.orderid = o.orderid AND od.productid = p.productid AND p.categoryid = c.categoryid AND LOWER(c.categoryname) = LOWER(par_CategoryName) AND LOWER(SUBSTR(aws_sqlserver_ext.conv_datetime_to_string('NVARCHAR(22)', 'DATETIME', o.orderdate, 111), 1, 4)) = LOWER(par_OrdYear)
GROUP BY productname
ORDER BY productname NULLS FIRST;
END;
$BODY$
LANGUAGE plpgsql;