-
Notifications
You must be signed in to change notification settings - Fork 0
/
CPA Project.sqbpro
144 lines (123 loc) · 7.02 KB
/
CPA Project.sqbpro
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
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="/Users/manvishkollu/Downloads/stores (1) (1).db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="3228"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,8:mainproducts"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="customers" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="98"/><column index="2" value="177"/><column index="3" value="99"/><column index="5" value="108"/><column index="6" value="163"/><column index="7" value="128"/><column index="8" value="102"/><column index="9" value="72"/><column index="10" value="67"/><column index="11" value="74"/><column index="12" value="145"/><column index="13" value="64"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="offices" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="65"/><column index="2" value="79"/><column index="4" value="137"/><column index="5" value="78"/><column index="6" value="68"/><column index="7" value="53"/><column index="8" value="67"/><column index="9" value="52"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="orderdetails" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="77"/><column index="2" value="76"/><column index="3" value="94"/><column index="4" value="59"/><column index="5" value="99"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="products" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="76"/><column index="2" value="227"/><column index="3" value="96"/><column index="4" value="77"/><column index="5" value="138"/><column index="6" value="300"/><column index="7" value="91"/><column index="8" value="54"/><column index="9" value="45"/></column_widths><filter_values><column index="1" value="s12"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1">SELECT 'Customers' AS table_name,
'13' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM customers
UNION ALL
SELECT 'Employees' AS table_name,
'8' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM employees
UNION ALL
SELECT 'Offices' AS table_name,
'9' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM offices
UNION ALL
SELECT 'OrderDetails' AS table_name,
'5' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM orderdetails
UNION ALL
SELECT 'Orders' AS table_name,
'7' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM orders
UNION ALL
SELECT 'Payments' AS table_name,
'4' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM payments
UNION ALL
SELECT 'ProductLines' AS table_name,
'4' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM productlines
UNION ALL
SELECT 'Products' AS table_name,
'13' AS number_of_columns,
COUNT(*) AS number_of_rows
FROM products;</sql><sql name="SQL 2">SELECT p.productCode, ROUND(SUM(quantityOrdered) *1.0 /
(SELECT quantityInStock
FROM products p
WHERE od.productCode=p.productCode),2) AS low_stock,p.productName,p.productLine
FROM orderdetails od
INNER JOIN products p
ON od.productCode=p.productCode
GROUP BY p.productCode
ORDER BY low_stock DESC
LIMIT 10;
</sql><sql name="Rows/Columns of Tables">SELECT p.productCode, ROUND(SUM(quantityOrdered* priceEach),2) AS product_performance,p.productName,p.productLine
FROM orderdetails AS od
INNER JOIN products p
ON p.productCode=od.productCode
GROUP BY p.productCode
ORDER BY product_performance DESC
LIMIT 10;</sql><sql name="SQL">WITH
low_stock_products AS (
SELECT productCode,
ROUND(SUM(quantityOrdered) *1.0 /
(SELECT quantityInStock
FROM products p
WHERE od.productCode=p.productCode),2) AS low_stock
FROM orderdetails od
GROUP BY productCode
ORDER BY low_stock DESC
LIMIT 10
)
SELECT productCode, SUM(quantityOrdered* priceEach) AS product_performance
FROM orderdetails od
WHERE productCode IN (SELECT productCode FROM low_stock_products)
GROUP BY productCode
ORDER BY product_performance DESC
LIMIT 10;</sql><sql name="SQL 15">SELECT o.customerNumber, SUM(quantityOrdered *(priceEach-buyPrice)) AS profit_per_customer
FROM orders o
INNER JOIN orderdetails od
ON o.orderNumber = od.orderNumber
INNER JOIN products p
ON od.productCode = p.productCode
GROUP BY customerNumber;</sql><sql name="SQL 17">WITH
less_engaged_customers AS (
SELECT o.customerNumber, SUM(quantityOrdered * (priceEach - buyPrice)) AS revenue
FROM products p
JOIN orderdetails od
ON p.productCode = od.productCode
JOIN orders o
ON o.orderNumber = od.orderNumber
GROUP BY o.customerNumber
)
SELECT contactLastName, contactFirstName,city,country, le.revenue
FROM customers c
INNER JOIN less_engaged_customers le
ON c.customerNumber=le.customerNumber
ORDER BY le.revenue
LIMIT 5;
</sql><sql name="SQL 19">WITH
VIP_customers AS (
SELECT o.customerNumber, SUM(quantityOrdered * (priceEach - buyPrice)) AS revenue
FROM products p
JOIN orderdetails od
ON p.productCode = od.productCode
JOIN orders o
ON o.orderNumber = od.orderNumber
GROUP BY o.customerNumber
)
SELECT contactLastName, contactFirstName,city,country, vip.revenue
FROM customers c
INNER JOIN VIP_customers vip
ON c.customerNumber=vip.customerNumber
ORDER BY vip.revenue DESC
LIMIT 5;
</sql><sql name="SQL 18">WITH
money_on_marketing AS (
SELECT o.customerNumber, SUM(quantityOrdered * (priceEach - buyPrice)) AS revenue
FROM products p
JOIN orderdetails od
ON p.productCode = od.productCode
JOIN orders o
ON o.orderNumber = od.orderNumber
GROUP BY o.customerNumber
)
SELECT AVG(mm.revenue) AS Average_revenue_per_customer
FROM customers c
INNER JOIN money_on_marketing mm
ON c.customerNumber=mm.customerNumber</sql><current_tab id="7"/></tab_sql></sqlb_project>