DS2002-Final-Project/AdventureWorks_Streaming_Data_MySQL.sql at main · Taran-Gupta/DS2002-Final-Project · GitHub

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
USE adventureworks;

# 1181 rows divided by 3 is 393 + 394 + 394
SELECT COUNT(*) AS RowCount
FROM adventureworks.productinventory AS pi
LEFT OUTER JOIN adventureworks.product AS p
    ON pi.productID = p.ProductID
LEFT OUTER JOIN adventureworks.location AS loc
    ON pi.LocationID = loc.LocationID
LEFT OUTER JOIN adventureworks.productvendor AS pv
    ON pv.ProductID = pi.ProductID
INNER JOIN adventureworks.vendor AS v
    ON v.VendorID = pv.VendorID;


# SELECT EVERYTHING that should end up in the "Silver" Table
SELECT pi.productID,
	pi.locationID,
    v.VendorID,
    pi.ModifiedDate,
    pv.LastReceiptDate,
    pi.Shelf,
    pi.Bin,
    pi.Quantity,
    p.Size,
    p.Weight,
    loc.CostRate,
    loc.Availability,
    pv.AverageLeadTime,
    v.PreferredVendorStatus,
    pv.MinOrderQty,
    pv.MaxOrderQty,
    pv.OnOrderQty
FROM adventureworks.productinventory AS pi
LEFT OUTER JOIN adventureworks.product AS p
ON pi.productID = p.ProductID
LEFT OUTER JOIN adventureworks.location as loc
ON pi.LocationID = loc.LocationID
LEFT OUTER JOIN adventureworks.productvendor as pv
ON pv.ProductID = pi.ProductID
INNER JOIN adventureworks.vendor as v
ON v.VendorID = pv.VendorID;


WITH RowNumbers AS (
    SELECT
        pi.productID,
        pi.locationID,
        v.VendorID,
        pi.ModifiedDate,
        pv.LastReceiptDate,
        pi.Shelf,
        pi.Bin,
        pi.Quantity,
        p.Size,
        p.Weight,
        loc.CostRate,
        loc.Availability,
        pv.AverageLeadTime,
        v.PreferredVendorStatus,
        pv.MinOrderQty,
        pv.MaxOrderQty,
        pv.OnOrderQty,
        ROW_NUMBER() OVER (ORDER BY pi.productID) AS RowNum
    FROM adventureworks.productinventory AS pi
    LEFT OUTER JOIN adventureworks.product AS p
        ON pi.productID = p.ProductID
    LEFT OUTER JOIN adventureworks.location AS loc
        ON pi.LocationID = loc.LocationID
    LEFT OUTER JOIN adventureworks.productvendor AS pv
        ON pv.ProductID = pi.ProductID
    INNER JOIN adventureworks.vendor AS v
        ON v.VendorID = pv.VendorID
)
SELECT *
FROM RowNumbers
WHERE RowNum BETWEEN 1 AND 393;

WITH RowNumbers AS (
    SELECT
        pi.productID,
        pi.locationID,
        v.VendorID,
        pi.ModifiedDate,
        pv.LastReceiptDate,
        pi.Shelf,
        pi.Bin,
        pi.Quantity,
        p.Size,
        p.Weight,
        loc.CostRate,
        loc.Availability,
        pv.AverageLeadTime,
        v.PreferredVendorStatus,
        pv.MinOrderQty,
        pv.MaxOrderQty,
        pv.OnOrderQty,
        ROW_NUMBER() OVER (ORDER BY pi.productID) AS RowNum
    FROM adventureworks.productinventory AS pi
    LEFT OUTER JOIN adventureworks.product AS p
        ON pi.productID = p.ProductID
    LEFT OUTER JOIN adventureworks.location AS loc
        ON pi.LocationID = loc.LocationID
    LEFT OUTER JOIN adventureworks.productvendor AS pv
        ON pv.ProductID = pi.ProductID
    INNER JOIN adventureworks.vendor AS v
        ON v.VendorID = pv.VendorID
)
SELECT *
FROM RowNumbers
WHERE RowNum BETWEEN 394 AND 787;  -- Second 1/3 of rows

WITH RowNumbers AS (
    SELECT
        pi.productID,
        pi.locationID,
        v.VendorID,
        pi.ModifiedDate,
        pv.LastReceiptDate,
        pi.Shelf,
        pi.Bin,
        pi.Quantity,
        p.Size,
        p.Weight,
        loc.CostRate,
        loc.Availability,
        pv.AverageLeadTime,
        v.PreferredVendorStatus,
        pv.MinOrderQty,
        pv.MaxOrderQty,
        pv.OnOrderQty,
        ROW_NUMBER() OVER (ORDER BY pi.productID) AS RowNum
    FROM adventureworks.productinventory AS pi
    LEFT OUTER JOIN adventureworks.product AS p
        ON pi.productID = p.ProductID
    LEFT OUTER JOIN adventureworks.location AS loc
        ON pi.LocationID = loc.LocationID
    LEFT OUTER JOIN adventureworks.productvendor AS pv
        ON pv.ProductID = pi.ProductID
    INNER JOIN adventureworks.vendor AS v
        ON v.VendorID = pv.VendorID
)
SELECT *
FROM RowNumbers
WHERE RowNum BETWEEN 788 AND 1181;  -- Last 1/3 of rows