MigrationDatabaseOnAWS/files/Visualization/ViewTable/RedshiftView.sql at master · fernandito77777/MigrationDatabaseOnAWS · 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
-- 1. offices view
CREATE VIEW classicmodels.offices_vw AS
SELECT
	officeCode,
  	city,
	phone,
    addressLine1,
    addressLine2,
    state,
    country,
    postalCode,
    territory,
    TIMESTAMP
FROM classicmodels.offices
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.offices)
-- execute the query


-- erase the previous query
-- 2. employees view
CREATE VIEW classicmodels.employees_vw AS
SELECT
	employeeNumber,
  	lastName,
    firstName,
    extension,
    email,
    officeCode,
    reportsTo,
    jobTitle,
    TIMESTAMP
FROM classicmodels.employees
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.employees)
-- execute the query


-- erase the previous query
-- 3. customers view
CREATE VIEW classicmodels.customers_vw AS
SELECT
	customerNumber,
    customerName,
    contactLastName,
    contactFirstName,
    phone,
    addressLine1,
    addressLine2,
    city,
    state,
    postalCode,
    country,
    salesRepEmployeeNumber,
    creditLimit,
    TIMESTAMP
FROM classicmodels.customers
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.customers)
-- execute the query


-- erase the previous query
-- 4. orders view
CREATE VIEW classicmodels.orders_vw AS
SELECT
	orderNumber,
    orderDate,
    requiredDate,
    shippedDate,
    status,
    comments,
    customerNumber,
    TIMESTAMP
FROM classicmodels.orders
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.orders)
-- execute the query


-- erase the previous query
-- 5. productlines view
CREATE VIEW classicmodels.productlines_vw AS
SELECT
	productLine,
    textDescription,
    htmlDescription,
    image,
    TIMESTAMP
FROM classicmodels.productlines
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.productlines)
-- execute the query


-- erase the previous query
-- 6. products view
CREATE VIEW classicmodels.products_vw AS
SELECT
	productCode,
    productName,
    productLine,
    productScale,
    productVendor,
    productDescription,
    quantityInStock,
    buyPrice,
    MSRP,
    TIMESTAMP
FROM classicmodels.products
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.products)
-- execute the query


-- erase the previous query
-- 7. orderdetails view
CREATE VIEW classicmodels.orderdetails_vw AS
SELECT
	orderNumber,
    productCode,
    quantityOrdered,
    priceEach,
    orderLineNumber,
    TIMESTAMP
FROM classicmodels.orderdetails
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.orderdetails)
-- execute the query


-- erase the previous query
-- 8. payments view
CREATE VIEW classicmodels.payments_vw AS
SELECT
	customerNumber,
    checkNumber,
    paymentDate,
    amount,
    TIMESTAMP
FROM classicmodels.payments
WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM classicmodels.payments)