-
Notifications
You must be signed in to change notification settings - Fork 0
/
MYSQL.sql
151 lines (112 loc) · 6.68 KB
/
MYSQL.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
/*Q1. Write a query to display customer_id, customer full name with their title (Mr/Ms),
both first name and last name are in upper case, customer_email, customer_creation_year
and display customer’s category after applying below categorization rules:
i. if CUSTOMER_CREATION_DATE year <2005 then category A
ii. if CUSTOMER_CREATION_DATE year >=2005 and <2011 then category B
iii. if CUSTOMER_CREATION_DATE year>= 2011 then category C
Expected 52 rows in final output.
[Note: TABLE to be used - ONLINE_CUSTOMER TABLE]
Hint:Use CASE statement. create customer_creation_year column with the help of customer_creation_date,
no permanent change in the table is required.
(Here don’t UPDATE or DELETE the columns in the table nor CREATE new tables for your representation.
A new column name can be used as an alias for your manipulation in case if you are going to
use a CASE statement.)
*/
## Answer 1.
/* Q2. Write a query to display the following information for the products which have
not been sold: product_id, product_desc, product_quantity_avail, product_price,
inventory values (product_quantity_avail * product_price),
New_Price after applying discount as per below criteria.
Sort the output with respect to decreasing value of Inventory_Value.
i) If Product Price > 200,000 then apply 20% discount
ii) If Product Price > 100,000 then apply 15% discount
iii) if Product Price =< 100,000 then apply 10% discount
Expected 13 rows in final output.
[NOTE: TABLES to be used - PRODUCT, ORDER_ITEMS TABLE]
Hint: Use CASE statement, no permanent change in table required.
(Here don’t UPDATE or DELETE the columns in the table nor CREATE new tables for your representation.
A new column name can be used as an alias for your manipulation in case if you are going to use
a CASE statement.)
*/
## Answer 2.
/*Q3. Write a query to display Product_class_code, Product_class_desc,
Count of Product type in each product class,
Inventory Value (p.product_quantity_avail*p.product_price).
Information should be displayed for only those product_class_code
which have more than 1,00,000 Inventory Value.
Sort the output with respect to decreasing value of Inventory_Value.
Expected 9 rows in final output.
[NOTE: TABLES to be used - PRODUCT, PRODUCT_CLASS]
Hint: 'count of product type in each product class' is the count of product_id based on product_class_code.
*/
## Answer 3.
/* Q4. Write a query to display customer_id, full name, customer_email,
customer_phone and country of customers who have cancelled all the orders placed by them.
Expected 1 row in the final output
[NOTE: TABLES to be used - ONLINE_CUSTOMER, ADDRESSS, OREDER_HEADER]
Hint: USE SUBQUERY
*/
## Answer 4.
/* Q5. Write a query to display Shipper name, City to which it is catering,
num of customer catered by the shipper in the city ,
number of consignment delivered to that city for Shipper DHL
Expected 9 rows in the final output
[NOTE: TABLES to be used - SHIPPER, ONLINE_CUSTOMER, ADDRESSS, ORDER_HEADER]
Hint: The answer should only be based on Shipper_Name -- DHL.
The main intent is to find the number of customers and the consignments catered by DHL in each city.
*/
## Answer 5.
/*Q6. Write a query to display product_id, product_desc, product_quantity_avail, quantity sold
and show inventory Status of products as per below condition:
a. For Electronics and Computer categories,
if sales till date is Zero then show 'No Sales in past, give discount to reduce inventory',
if inventory quantity is less than 10% of quantity sold, show 'Low inventory, need to add inventory',
if inventory quantity is less than 50% of quantity sold, show 'Medium inventory, need to add some inventory',
if inventory quantity is more or equal to 50% of quantity sold, show 'Sufficient inventory'
b. For Mobiles and Watches categories,
if sales till date is Zero then show 'No Sales in past, give discount to reduce inventory',
if inventory quantity is less than 20% of quantity sold, show 'Low inventory, need to add inventory',
if inventory quantity is less than 60% of quantity sold, show 'Medium inventory, need to add some inventory',
if inventory quantity is more or equal to 60% of quantity sold, show 'Sufficient inventory'
c. Rest of the categories,
if sales till date is Zero then show 'No Sales in past, give discount to reduce inventory',
if inventory quantity is less than 30% of quantity sold, show 'Low inventory, need to add inventory',
if inventory quantity is less than 70% of quantity sold, show 'Medium inventory, need to add some inventory',
if inventory quantity is more or equal to 70% of quantity sold, show 'Sufficient inventory'
Expected 60 rows in final output
[NOTE: (USE CASE statement) ; TABLES to be used - PRODUCT, PRODUCT_CLASS, ORDER_ITEMS]
Hint: quantity sold here is product_quantity in order_items table.
You may use multiple case statements to show inventory status
(Low stock, In stock, and Enough stock) that meets both the conditions i.e. on products as well as on quantity
The meaning of the rest of the categories, means products apart from electronics,computers,mobiles and watches
*/
## Answer 6.
/* Q7. Write a query to display order_id and volume of the biggest order (in terms of volume)
that can fit in carton id 10 .
Expected 1 row in final output
[NOTE: TABLES to be used - CARTON, ORDER_ITEMS, PRODUCT]
Hint: First find the volume of carton id 10 and then find the order id with products having
total volume less than the volume of carton id 10
*/
## Answer 7.
/*Q8. Write a query to display customer id, customer full name, total quantity and
total value (quantity*price) shipped where mode of payment is Cash and customer last name starts with 'G'
Expected 2 rows in final output
[NOTE: TABLES to be used - ONLINE_CUSTOMER, ORDER_ITEMS, PRODUCT, ORDER_HEADER]
## Answer 8.
/*Q9. Write a query to display product_id, product_desc and total quantity of products which are sold together
with product id 201 and are not shipped to city Bangalore and New Delhi.
Expected 6 rows in final output
[NOTE: TABLES to be used - ORDER_ITEMS, PRODUCT, ORDER_HEADER, ONLINE_CUSTOMER, ADDRESS]
Hint: Display the output in descending order with respect to the sum of product_quantity.
(USE SUB-QUERY) In final output show only those products ,
product_id’s which are sold with 201 product_id (201 should not be there in output)
and are shipped except Bangalore and New Delhi
*/
## Answer 9.
/* Q10. Write a query to display the order_id, customer_id and customer fullname, total quantity of products
shipped for order ids which are even and shipped to address where pincode is not starting with "5"
Expected 15 rows in final output
[NOTE: TABLES to be used - ONLINE_CUSTOMER, ORDER_HEADER, ORDER_ITEMS, ADDRESS]
*/
## Answer 10.