forked from thatsabhishek/Coding_Ninjas_DBMS
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathquery20.sql
112 lines (103 loc) · 8.94 KB
/
query20.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
Problem Statement:
List all the distinct products which are ordered with their customer id and customer name.
Information about the tables:
Given below is a database of a newly established e-commerce website.
The database contains multiple tables i.e. products, orders, and transactions.
The information about required tables is given below
Table product:
Attributes list:
Attributes | Data Type
item_id | INT (PRIMARY KEY)
product_id | INT
pname | VARCHAR(225)
instock | CHAR
price | DOUBLE
arrival_days | INT
seller | VARCHAR(225)
youSave | DOUBLE
Data list:
+---------+------------+-------------------------------------------------------------------------+---------+----------+--------------+----------------------------+---------+
| item_id | product_id | p_name | inStock | price | arrival_days | seller | youSave |
+---------+------------+-------------------------------------------------------------------------+---------+----------+--------------+----------------------------+---------+
| 1 | 7556 | Iphone 11 (128GB) | N | 51300.05 | 5 | Maple Store | 3600 |
| 2 | 2223 | optimum whey protien | Y | 1614 | 7 | Cloudtail India | 245 |
| 3 | 7556 | Iphone 11 (128GB) | Y | 53500 | 4 | Kukreja Telecom Store | 1400 |
| 4 | 6754 | Inalsa Electric Kettle Absa (Black/Silver) | Y | 591 | 9 | k3stores | 10040 |
| 5 | 99 | Mini organising tech kit | Y | 1400 | 2 | dailyen | 200 |
| 6 | 7777 | OnePlus 9 Pro 5G (256GB) | Y | 65999 | 2 | Darshita Etel | 4000 |
| 7 | 5454 | Iphone 11 (64GB) | Y | 46999 | 3 | Maple Store | 3000 |
| 8 | 4324 | Women's Cotton Blend Straight Kurti with Palazzos | Y | 549 | 10 | Maxx Store | 1000 |
| 9 | 5655 | Redmi 9 Activ (128GB) | N | 10999 | 3 | Darshita Electronics | 200 |
| 10 | 9753 | OnePlus Buds Z | N | 2999 | 3 | Appario Retail Private Ltd | 191 |
| 11 | 7777 | OnePlus 9 Pro 5G (256GB) | Y | 65999 | 2 | Darshita Etel | 4000 |
| 12 | 6477 | Ikigai: The Japanese secret to a long and happy life | Y | 427 | 3 | Cloudtail India | 123 |
| 13 | 7556 | Iphone 11 (128GB) | N | 51300.05 | 5 | Maple Store | 3600 |
| 14 | 1111 | Solimo Trance High Back Mesh Contemporary Office Chair (Black) | Y | 7599 | 4 | Cloudtail India | 5901 |
| 15 | 2222 | Nayasa 2 in 1 Dustbin - Dry Waste and Wet Waste Dustbin (33 Ltrs) - Big | Y | 1339 | 7 | Cloudtail India | 189 |
| 16 | 5555 | Ben Martin Men's Relaxed Jeans | N | 698 | 12 | Maxx Store | 100 |
| 17 | 7777 | OnePlus 9 Pro 5G (256GB) | Y | 65999 | 2 | Darshita Etel | 4000 |
| 18 | 6754 | Inalsa Electric Kettle Absa (Black/Silver) | Y | 591 | 9 | k3stores | 10040 |
| 19 | 2223 | optimum whey protien | Y | 1614 | 7 | Cloudtail India | 245 |
| 20 | 529 | MI Mobile cover | Y | 999 | 5 | dailyen | 500 |
| 21 | 5655 | Redmi 9 Activ (128GB) | N | 10999 | 3 | Darshita Electronics | 200 |
| 22 | 7556 | Iphone 11 (128GB) | Y | 54900 | 7 | Sunil mobile Store | 0 |
| 23 | 9753 | OnePlus Buds Z | N | 4000 | 5 | sekhri telecoms | 200 |
| 24 | 5454 | Iphone 11 (64GB) | Y | 43999 | 7 | imagine store | 6000 |
+---------+------------+-------------------------------------------------------------------------+---------+----------+--------------+----------------------------+---------+
Table orders:
Attributes list:
Attributes | Data Type
order_id | INT (PRIMARY KEY)
product_id | INT
customer_id | INT
payment_date | DATE
est_delivery_date | DATE
transaction_days | INT
Data list:
+----------+------------+-------------+----------------+--------------+-------------------+----------------+
| order_id | product_id | customer_id | checkout_price | payment_date | est_delivery_date | transaction_id |
+----------+------------+-------------+----------------+--------------+-------------------+----------------+
| oid1 | 7777 | cid3 | 63999 | 2021-11-12 | 2021-11-14 | tid1 |
| oid2 | 7556 | cid1 | 51300 | 2021-11-09 | 2021-11-13 | tid2 |
| oid3 | 6754 | cid5 | 591 | 2021-11-24 | 2021-12-03 | tid3 |
| oid4 | 7556 | cid3 | 51300 | 2021-11-10 | 2021-11-14 | tid4 |
| oid5 | 6477 | cid2 | 500 | 2021-11-06 | 2021-11-09 | tid5 |
| oid6 | 2223 | cid7 | 1514 | 2021-11-27 | 2021-12-04 | tid6 |
+----------+------------+-------------+----------------+--------------+-------------------+----------------+
Table customer:
Attributes list:
Attributes | Data Type
customer_id | VARCHAR(20) (PRIMARY KEY)
customer_name | VARCHAR(25)
phone_no | INT
city | VARCHAR(200)
pin_code | INT
Data list:
+-------------+-----------------+-----------+-----------+----------+
| customer_id | customer_name | phone_no | city | pin_code |
+-------------+-----------------+-----------+-----------+----------+
| cid1 | Riya Khanna | 987655443 | Delhi | 110005 |
| cid2 | Sahil Kumar | 987657643 | Mumbai | 230532 |
| cid3 | Vishwas Aanand | 987658871 | Kolkata | 700007 |
| cid4 | Harleen Kaur | 987677585 | Bengaluru | 560006 |
| cid5 | Priyanshu Gupta | 956758556 | Hyderabad | 500012 |
| cid6 | Lokesh Ravaliya | 998295561 | Bikaner | 334003 |
| cid7 | Kuldeep Daga | 997757204 | Mandsaur | 458001 |
| cid8 | Vaibhav Jain | 987677757 | Jalandhar | 144002 |
| cid9 | Lokesh Gupta | 956758500 | Hyderabad | 500013 |
+-------------+-----------------+-----------+-----------+----------+
Solution:
select DISTINCT p.p_name, o.customer_id, c.customer_name from product p
join orders o on p.product_id = o.product_id
join customer c on o.customer_id = c.customer_id;
Output:
+------------------------------------------------------+-------------+-----------------+
| p_name | customer_id | customer_name |
+------------------------------------------------------+-------------+-----------------+
| Iphone 11 (128GB) | cid3 | Vishwas Aanand |
| Iphone 11 (128GB) | cid1 | Riya Khanna |
| optimum whey protien | cid7 | Kuldeep Daga |
| Inalsa Electric Kettle Absa (Black/Silver) | cid5 | Priyanshu Gupta |
| OnePlus 9 Pro 5G (256GB) | cid3 | Vishwas Aanand |
| Ikigai: The Japanese secret to a long and happy life | cid2 | Sahil Kumar |
+------------------------------------------------------+-------------+-----------------+