-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathexercise9
140 lines (114 loc) · 5.47 KB
/
exercise9
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
1.
List
the product name and quantity of invoice items.
mysql> SELECT PRODUCT.PNAME,INVOICE_ITEM.QUANTITY FROM PRODUCT INNER JOIN INVOICE_ITEM ON PRODUCT.PID=INVOICE_ITEM.PID;
+---------+----------+
| PNAME | QUANTITY |
+---------+----------+
| BOX | 2 |
| BOOK | 4 |
| UMBERLA | 6 |
| BOX | 10 |
| BOOK | 4 |
| UMBERLA | 4 |
| PEN | 4 |
+---------+----------+
7 rows in set (0.00 sec)
--------------------------------------------------------------------------------------------------------------------------------------------
2.
Display
invoice details of the transaction dated ’31-01-2022’.
mysql> SELECT * FROM CUSTOMER; +----+--------+---------+------+-------+
| ID | NAME | ADDRESS | CITY | PH_NO |
+----+--------+---------+------+-------+
| 1 | SUGU | JDK | USA | 1111 |
| 4 | ARNOLD | KDY | EKM | NULL |
| 5 | ANU | KDY | EKM | NULL |
| 7 | EDGE | TVM | TVM | 555 |
| 11 | ANN | KKD | EKM | 5522 |
| 12 | ARUN | KKD | EKM | 2522 |
| 13 | ARJU | ABC | TVM | 2562 |
| 16 | YARD | GOA | GOA | 8562 |
+----+--------+---------+------+-------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM INVOICE_MASTER; +--------+---------+------------+
| INV_ID | CUST_ID | INV_DATE |
+--------+---------+------------+
| 101 | 1 | 2021-12-25 |
| 102 | 4 | 2021-12-26 |
| 103 | 1 | 2022-01-15 |
| 104 | 7 | 2022-02-16 |
| 106 | 4 | 2022-02-28 |
| 107 | 1 | 2022-04-10 |
| 108 | 4 | 2022-04-15 |
+--------+---------+------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM PRODUCT; +-----+---------+-------+
| PID | PNAME | PRICE |
+-----+---------+-------+
| 1 | PEN | 31 |
| 2 | BOOK | 77 |
| 3 | UMBERLA | 204 |
| 4 | BOX | 102 |
| 5 | OPIUM | 4080 |
| 20 | SOAP | 40 |
+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM INVOICE_ITEM; +--------+------+----------+
| INV_ID | PID | QUANTITY |
+--------+------+----------+
| 102 | 4 | 2 |
| 104 | 2 | 4 |
| 106 | 3 | 6 |
| 107 | 4 | 10 |
| 102 | 2 | 4 |
| 102 | 3 | 4 |
| 104 | 1 | 4 |
+--------+------+----------+
7 rows in set (0.00 sec)
mysql> SELECT CUSTOMER.NAME,PRODUCT.PNAME,INVOICE_ITEM.QUANTITY FROM CUSTOMER INNER JOIN INVOICE_MASTER ON CUSTOMER.ID=INVOICE_MASTER.CUST_ID INNER JOIN INVOICE_ITEM ON INVOICE_MASTER.INV_ID=INVOICE_ITEM.INV_ID INNER JOIN PRODUCT ON PRODUCT.PID=INVOICE_ITEM.PID WHERE INV_DATE='2022-02-16';
+------+-------+----------+
| NAME | PNAME | QUANTITY |
+------+-------+----------+
| EDGE | BOOK | 4 |
| EDGE | PEN | 4 |
+------+-------+----------+
2 rows in set (0.01 sec)
--------------------------------------------------------------------------------------------------------------------------------------------
3.
Display
the customer name, product name, invoice date of purchased item ‘pen’.
mysql> SELECT CUSTOMER.NAME,PRODUCT.PNAME,INVOICE_MASTER.INV_DATE FROM CUSTOMER INNER JOIN INVOICE_MASTER ON CUSTOMER.ID=INVOICE_MASTER.CUST_ID INNER JOIN INVOICE_ITEM ON INVOICE_MASTER.INV_ID=INVOICE_ITEM.INV_ID INNER JOIN PRODUCT ON PRODUCT.PID=INVOICE_ITEM.PID WHERE PNAME='PEN';
+------+-------+------------+
| NAME | PNAME | INV_DATE |
+------+-------+------------+
| EDGE | PEN | 2022-02-16 |
+------+-------+------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------------------------------------------------------------------
4.
Display
all customer details and invoice details must include customer has not
purchased items.
mysql> SELECT CUSTOMER.NAME,PRODUCT.PNAME,INVOICE_ITEM.QUANTITY,INVOICE_MASTER.INV_ID,INVOICE_MASTER.INV_DATE FROM CUSTOMER LEFT JOIN INVOICE_MASTER ON CUSTOMER.ID=INVOICE_MASTER.CUST_ID LEFT JOIN INVOICE_ITEM ON INVOICE_MASTER.INV_ID=INVOICE_ITEM.INV_ID LEFT JOIN PRODUCT ON PRODUCT.PID=INVOICE_ITEM.PID;
+--------+---------+----------+--------+------------+
| NAME | PNAME | QUANTITY | INV_ID | INV_DATE |
+--------+---------+----------+--------+------------+
| ARNOLD | BOX | 2 | 102 | 2021-12-26 |
| EDGE | BOOK | 4 | 104 | 2022-02-16 |
| ARNOLD | UMBERLA | 6 | 106 | 2022-02-28 |
| SUGU | BOX | 10 | 107 | 2022-04-10 |
| ARNOLD | BOOK | 4 | 102 | 2021-12-26 |
| ARNOLD | UMBERLA | 4 | 102 | 2021-12-26 |
| EDGE | PEN | 4 | 104 | 2022-02-16 |
| SUGU | NULL | NULL | 101 | 2021-12-25 |
| SUGU | NULL | NULL | 103 | 2022-01-15 |
| ARNOLD | NULL | NULL | 108 | 2022-04-15 |
| ANU | NULL | NULL | NULL | NULL |
| ANN | NULL | NULL | NULL | NULL |
| ARUN | NULL | NULL | NULL | NULL |
| ARJU | NULL | NULL | NULL | NULL |
| YARD | NULL | NULL | NULL | NULL |
+--------+---------+----------+--------+------------+
15 rows in set (0.00 sec)
//VERIFIED