DB2020FALL/week7.sql at master · bobur554396/DB2020FALL · GitHub

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
CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  TEXT,
  price NUMERIC CHECK (price > 0)
);

INSERT INTO products (name, price) VALUES ('p1', 100);

SELECT *
FROM products;

SELECT *
FROM products_id_seq;

ALTER TABLE products
  ADD CONSTRAINT name_len CHECK (char_length(name) >= 2);

INSERT INTO products (name, price)
VALUES ('p2', 90)
RETURNING *;

SELECT *
FROM products;

INSERT INTO products (name, price)
VALUES ('p3', 120), ('p4', 105)
RETURNING *;

ALTER TABLE products
  DROP CONSTRAINT name_len;

ALTER TABLE products
  ADD COLUMN discounted_price NUMERIC
  CONSTRAINT positive_discount_price
  CHECK (discounted_price > 0);

SELECT *
FROM products;

ALTER TABLE products
  ADD CONSTRAINT price_greater
CHECK (price > discounted_price);

INSERT INTO products (name, price, discounted_price)
VALUES ('p4', 120, 100)
RETURNING *;

SELECT *
FROM products;

ALTER TABLE products
  ADD COLUMN is_in_stock BOOLEAN
  DEFAULT TRUE;


SELECT *
FROM products;

ALTER TABLE products
  ALTER COLUMN name
  SET NOT NULL;

INSERT INTO products (name, price) VALUES ('p6', 100);

SELECT *
FROM products;

ALTER TABLE products
  ADD CONSTRAINT price_not_null
CHECK (price IS NOT NULL);

ALTER TABLE products
  ALTER COLUMN price
  SET NOT NULL;

ALTER TABLE products
  ADD COLUMN test INTEGER UNIQUE;

SELECT *
FROM products;

INSERT INTO products (name, price, test)
VALUES ('p7', 130, 1)
RETURNING *;


ALTER TABLE products
  DROP COLUMN test;

SELECT *
FROM products;


CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  product_id INTEGER REFERENCES products,
  quantity   INTEGER
);

SELECT *
FROM orders;

SELECT *
FROM products;

INSERT INTO orders (product_id, quantity)
VALUES (2, 5), (5, 3), (9, 4);

INSERT INTO orders (product_id, quantity)
VALUES (15, 3);

SELECT *
FROM orders;

--
-- CREATE TABLE tags (
--   id   SERIAL PRIMARY KEY,
--   name TEXT NOT NULL
-- );
--
-- CREATE TABLE product_tag (
--   product_id INTEGER REFERENCES products,
--   tag_id     INTEGER REFERENCES tags
-- );

DROP TABLE orders;

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  address TEXT NOT NULL
);

CREATE TABLE order_items (
  product_id INTEGER REFERENCES products ON DELETE RESTRICT,
  order_id   INTEGER REFERENCES orders ON DELETE CASCADE,
--   order_id   INTEGER REFERENCES orders ON DELETE SET DEFAULT DEFAULT 1,
--   order_id   INTEGER REFERENCES orders ON DELETE SET NULL NULL,
  quantity   INTEGER,
  PRIMARY KEY (product_id, order_id)
);

SELECT *
FROM products;

SELECT *
FROM orders;

INSERT INTO orders (address)
VALUES ('Tole bi 59'), ('Abaya 109');

INSERT INTO order_items (product_id, order_id, quantity)
VALUES
  (2, 1, 4),
  (5, 1, 3),
  (9, 1, 2),
  (5, 2, 6);

SELECT *
FROM products;

SELECT *
FROM orders;

SELECT *
FROM order_items;

DELETE FROM products
WHERE id = 9;

DELETE FROM orders
WHERE id = 1;

SELECT *
FROM orders;

INSERT INTO order_items (product_id, order_id, quantity)
VALUES
  (2, 2, 4),
  (9, 2, 2);

SELECT *
FROM order_items;