-
Notifications
You must be signed in to change notification settings - Fork 0
/
.sql
175 lines (169 loc) · 6.47 KB
/
.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
create table products (
id serial primary key,
name text not null,
image text,
description text,
brand text,
category varchar(100),
price float not null,
countInStock int not null,
rating float,
numReviews int,
created_at date default now(),
timestamp time default current_time
)
create table users (
id serial primary key,
name text not null,
email text not null unique,
password text not null,
isAdmin bool default false not null,
created_at date default now(),
timestamp time default current_time
);
create table reviews (
id serial primary key,
name text not null,
rating float not null,
comment text,
author_id int references users(id),
product_id int references products(id),
created_at date default now(),
timestamp time default current_time
);
create table paymentresults (
id serial primary key,
payment_id text,
status text,
update_time text,
email_address text
);
create table orders (
id serial primary key,
user_id int references users(id),
order_item_id int references orderitems(id),
shipping_address_id int references shippingaddresses(id),
payment_method text not null,
payment_result int references paymentresults(id),
tax_price float not null default 0.0,
shipping_price float not null default 0.0,
total_price float not null default 0.0,
is_paid bool not null default false,
paid_date date,
is_delivered bool not null default false,
delivered_date date,
created_at date default now(),
timestamp time default current_time
);
create table shippingaddresses (
id serial primary key,
address text not null,
city text not null,
postal_code text not null,
country text not null,
user_id int references users(id)
);
create table orderitems (
id serial primary key,
name text not null,
qty int not null,
image text not null,
price float not null,
transaction_number int,
product_id int references products(id)
);
select * from users;
select * from orders;
select * from orderitems o
select * from shippingaddresses s
select * from paymentresults p
insert into products
(name, image, description, brand, category, price, countinstock, rating, numreviews, user_id)
values ('Amazon Echo', '/images/alexa.jpg', 'I can turn on/off your lights and shit.', 'Amazon', 'Smart Home', 39.99, 820, 4.5, 951, 1)
insert into users (name, email, password) values ('Kitty Koste', '[email protected]', '123456')
insert into orders (transaction_number, shipping_address_id, payment_method, tax_price, shipping_price, total_price, is_paid)
values ('88aa028d-4b21-4fa1-ad04-35d368f1da8f', 13, 'TestCoin', 7.89, 10.00, 50.81, true);
alter table shippingaddresses add column id serial primary key;
alter table products alter column image set default '/images/placeholder.png';
alter table products alter column user_id set not null;
alter table products add column user_id int references users(id)
alter table shippingaddresses add column user_id int references users(id)
alter table orderitems drop column user_id
alter table orderitems add column bing text;
alter table orderitems drop column bing;
alter table orderitems add column transaction_number text;
alter table orders add column transaction_number text;
alter table shippingaddresses add column id serial primary key;
alter table paymentresults
add column created_at date
default now();
alter table paymentresults
add column timestamp time
default current_time;
alter table orderitems add column transaction_number text;
alter table orderitems drop column transaction_number;
alter table users
add column created_at date
default now();
alter table users
add column timestamp time
default current_time;
alter table orders
add column transaction_number text;
alter table orders
drop column user_id
alter table orders
add column items_price float
select p.id product_id, u.id user_id, p.user_id product_user_id, p."name", p.price from users u inner join products p on p.user_id = u.id where p.user_id = 4;
-- LIKE VS. ILIKE --
select * from products p where brand like '%Apple%' -- LETTER CASE MATTERS
select * from products p where brand ilike '%apple%' -- LETTER CASE DOESN'T MATTER
-- CREATE SHIPPING ADDRESS QUERY --
insert into shippingaddresses (address, city, postal_code, country, user_id) values ('1010 Tenten st', 'Uganda', '45621', 'Idaho', 5)
select * from shippingaddresses s
-- CREATE ORDER ITEM QUERY --
insert into orderitems (name, qty, image, price, product_id) values ('2006 Keyblade BoatSport', 3, '/images/boat.jpg', 15000.99, 3);
select * from orderitems o;
-- SELECTING SHIPPING INFO FOR CREATE ORDER QUERY --
select u.id users_id, u.name, s.user_id shipping_address_user_id, s.address, s.id shippingaddress_id from users u
inner join shippingaddresses s
on u.id = s.user_id
inner join orders o2
on s.id = o2.shipping_address_id
-- SELECT ORDERITEMS INFO FOR CREATE ORDER QUERY --
select p.id products_id, p."name" product_name, p.price, o.transaction_number, o.id order_item_id from products p
inner join orderitems o
on p.id = o.product_id
inner join orders o2
on o2.id = o.transaction_number
where transaction_number = '6a781402-659d-4632-b531-410a8372ce37'
-- SELECT ORDER ITEMS BY TRANSACTION NUMBER IN ORDER QUERY --
select * from orderitems o
inner join orders o2
on o.transaction_number = o2.transaction_number
where o.transaction_number = '5d47d663-ca26-4e01-95cd-8e612469b381'
and o.name ilike '%b%'
--
select u.id users_id, u.name, s.user_id shipping_address_user_id, s.id shippingaddress_id, o2.transaction_number from users u
inner join shippingaddresses s
on u.id = s.user_id
inner join orders o2
on s.id = o2.shipping_address_id
--
select u.id users_id, u.name, u.isadmin, s.user_id shipping_address_user_id, s.id shippingaddress_id, o2.transaction_number from users u inner join shippingaddresses s on u.id = s.user_id inner join orders o2 on s.id = o2.shipping_address_id where o2.transaction_number = '5d47d663-ca26-4e01-95cd-8e612469b381'
select * from users u
inner join shippingaddresses s
on s.user_id = u.id
where s.id = 9
select * from users u;
select * from products p;
update users set "name" = 'Billie Joe' where email = '[email protected]';
--delete from orderitems where transaction_number = 'fd8f2a1a-48b6-4c05-a012-37331100d357'
--delete from orders where id > 2 and id < 9;
--update products set user_id = 5;
--update users set title = 'Doobies Post' where creatorid = 5;
--update orderitems set transaction_number = 5421 where id = 12;
--delete from products where id = 12;
--delete from orderitems;
--delete from shippingaddresses where user_id = 12;
--truncate table products, users restart identity cascade;