-
Notifications
You must be signed in to change notification settings - Fork 0
/
join_mul.sql
187 lines (163 loc) · 7.25 KB
/
join_mul.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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
--NEW TABLE SETS
CREATE TABLE customers (
customer_id int(11) NOT NULL,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
birth_date date DEFAULT NULL,
phone varchar(50) DEFAULT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL,
points int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (customer_id)
)
INSERT INTO customers VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO customers VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO customers VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO customers VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO customers VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO customers VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO customers VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO customers VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO customers VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO customers VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);
--order_statuses table
CREATE TABLE order_statuses (
order_status_id int(4) NOT NULL,
name varchar(50) NOT NULL,
PRIMARY KEY (order_status_id)
)
INSERT INTO order_statuses VALUES (1,'Processed');
INSERT INTO order_statuses VALUES (2,'Shipped');
INSERT INTO order_statuses VALUES (3,'Delivered');
--order table
CREATE TABLE orders (
order_id int(11) NOT NULL AUTO_INCREMENT,
customer_id int(11) NOT NULL,
order_date date NOT NULL,
status tinyint(4) NOT NULL DEFAULT '1',
comments varchar(2000) DEFAULT NULL,
shipped_date date DEFAULT NULL,
shipper_id smallint(6) DEFAULT NULL,
PRIMARY KEY (order_id),
)
INSERT INTO orders VALUES (1,6,'2019-01-30',1,NULL,NULL,NULL);
INSERT INTO orders VALUES (2,7,'2018-08-02',2,NULL,'2018-08-03',4);
INSERT INTO orders VALUES (3,8,'2017-12-01',1,NULL,NULL,NULL);
INSERT INTO orders VALUES (4,2,'2017-01-22',1,NULL,NULL,NULL);
INSERT INTO orders VALUES (5,5,'2017-08-25',2,'','2017-08-26',3);
INSERT INTO orders VALUES (6,10,'2018-11-18',1,'Aliquam erat volutpat. In congue.',NULL,NULL);
INSERT INTO orders VALUES (7,2,'2018-09-22',2,NULL,'2018-09-23',4);
INSERT INTO orders VALUES (8,5,'2018-06-08',1,'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.',NULL,NULL);
INSERT INTO orders VALUES (9,10,'2017-07-05',2,'Nulla mollis molestie lorem. Quisque ut erat.','2017-07-06',1);
INSERT INTO orders VALUES (10,6,'2018-04-22',2,NULL,'2018-04-23',2);
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON os.order_status_id = o.status
SELECT order_id,
order_date,
first_name,
last_name,
os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON os.order_status_id = o.status
ORDER BY order_id
/*__________________________________________________________________________________________________________________________*/
--NEW TABLE SETS
CREATE TABLE payments (
payment_id int(11) NOT NULL,
client_id int(11) NOT NULL,
invoice_id int(11) NOT NULL,
date date NOT NULL,
amount decimal(9,2) NOT NULL,
payment_method int(4) NOT NULL,
PRIMARY KEY (payment_id)
)
INSERT INTO payments VALUES (1,5,2,'2019-02-12',8.18,1);
INSERT INTO payments VALUES (2,1,6,'2019-01-03',74.55,1);
INSERT INTO payments VALUES (3,3,11,'2019-01-11',0.03,1);
INSERT INTO payments VALUES (4,5,13,'2019-01-26',87.44,1);
INSERT INTO payments VALUES (5,3,15,'2019-01-15',80.31,1);
INSERT INTO payments VALUES (6,3,17,'2019-01-15',68.10,1);
INSERT INTO payments VALUES (7,5,18,'2019-01-08',32.77,1);
INSERT INTO payments VALUES (8,5,18,'2019-01-08',10.00,2);
CREATE TABLE payment_methods (
payment_method_id int(4) NOT NULL,
name varchar(50) NOT NULL,
PRIMARY KEY (payment_method_id)
)
INSERT INTO payment_methods VALUES (1,'Credit Card');
INSERT INTO payment_methods VALUES (2,'Cash');
INSERT INTO payment_methods VALUES (3,'PayPal');
INSERT INTO payment_methods VALUES (4,'Wire Transfer');
CREATE TABLE clients (
client_id int(11) NOT NULL,
name varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL,
phone varchar(50) DEFAULT NULL,
PRIMARY KEY (client_id)
)
INSERT INTO clients VALUES (1,'Vinte','3 Nevada Parkway','Syracuse','NY','315-252-7305');
INSERT INTO clients VALUES (2,'Myworks','34267 Glendale Parkway','Huntington','WV','304-659-1170');
INSERT INTO clients VALUES (3,'Yadel','096 Pawling Parkway','San Francisco','CA','415-144-6037');
INSERT INTO clients VALUES (4,'Kwideo','81674 Westerfield Circle','Waco','TX','254-750-0784');
INSERT INTO clients VALUES (5,'Topiclounge','0863 Farmco Road','Portland','OR','971-888-9129');
-- joining the payments and the payments_method table and the clients table
USE sql_invoicing;
SELECT c.name, c.client_id , date, payment_id, pm.name
FROM payments p
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
JOIN clients c ON c.client_id = p.client_id
ORDER BY client_id
/*_________________________________________________________________________________________________________________________*/
--NEW TABLE SETS
CREATE TABLE order_items (
order_id int(11) NOT NULL ,
product_id int(11) NOT NULL,
quantity int(11) NOT NULL,
unit_price decimal(4,2) NOT NULL,
PRIMARY KEY (order_id,product_id),
)
INSERT INTO order_items VALUES (1,4,4,3.74);
INSERT INTO order_items VALUES (2,1,2,9.10);
INSERT INTO order_items VALUES (2,4,4,1.66);
INSERT INTO order_items VALUES (2,6,2,2.94);
INSERT INTO order_items VALUES (3,3,10,9.12);
INSERT INTO order_items VALUES (4,3,7,6.99);
INSERT INTO order_items VALUES (4,10,7,6.40);
INSERT INTO order_items VALUES (5,2,3,9.89);
INSERT INTO order_items VALUES (6,1,4,8.65);
INSERT INTO order_items VALUES (6,2,4,3.28);
INSERT INTO order_items VALUES (6,3,4,7.46);
INSERT INTO order_items VALUES (6,5,1,3.45);
INSERT INTO order_items VALUES (7,3,7,9.17);
INSERT INTO order_items VALUES (8,5,2,6.94);
INSERT INTO order_items VALUES (8,8,2,8.59);
INSERT INTO order_items VALUES (9,6,5,7.28);
INSERT INTO order_items VALUES (10,1,10,6.01);
INSERT INTO order_items VALUES (10,9,9,4.28);
CREATE TABLE order_item_notes (
note_id int NOT NULL,
order_Id int NOT NULL,
product_id int NOT NULL,
note VARCHAR(255) NOT NULL,
PRIMARY KEY (note_id)
);
INSERT INTO order_item_notes (note_id, order_Id, product_id, note) VALUES ('1', '1', '2', 'first note');
INSERT INTO order_item_notes (note_id, order_Id, product_id, note) VALUES ('2', '1', '2', 'second note');
--compound joining statement
USE sql_store;
SELECT *
FROM order_items o
JOIN order_item_notes oi
ON o.order_id = oi.order_Id AND o.product_id = oi.product_id
/*
--this is a explicit sntax of joining the tables is as same of JOIN syntax
SELECT *
FROM order_items o, order_item_notes oi
WHERE o.order_id = oi.order_id
*/