DB2020FALL/week4.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
SELECT *
FROM categories;

SELECT *
FROM customers;

SELECT *
FROM orders;

SELECT *
FROM order_items;

SELECT *
FROM products;

SELECT
  product_name AS "p_name",
  list_price   AS "price"
FROM products;

SELECT
  lower(product_name) AS "Product name in lower",
  upper(product_name) AS "Product name in upper"
FROM products ORDER BY "Product name in lower";

SELECT
  2 + 3      AS sum,
  2 + 3 * 10 AS sum_and_product;


SELECT DISTINCT *
FROM products;


SELECT *
FROM public.products;

SELECT DISTINCT ON (brand_id)
  product_name,
  list_price
FROM products;


SELECT *
FROM products
WHERE list_price > 2000;


SELECT *
FROM products
WHERE model_year > 2017 OR
      (
        list_price > 2000 AND list_price < 5000
      );


SELECT *
FROM products
WHERE model_year > 2017 AND
      (
        list_price > 2000 AND list_price < 5000
      );

SELECT brand_id
FROM products
GROUP BY brand_id;


SELECT
  brand_id,
  sum(list_price),
  max(list_price),
  min(list_price),
  avg(list_price),
  count(*)
FROM products
GROUP BY brand_id;

SELECT
  brand_id,
  category_id,
  sum(list_price),
  max(list_price),
  min(list_price),
  avg(list_price),
  count(*)
FROM products
GROUP BY (brand_id, category_id);


SELECT *
FROM products;


SELECT
  brand_id,
  sum(list_price),
  max(list_price),
  min(list_price),
  avg(list_price),
  count(*)
FROM products
WHERE model_year >= 2017
GROUP BY brand_id
HAVING count(category_id) > 3;

SELECT *
FROM products
WHERE model_year >= 2018
UNION DISTINCT
SELECT *
FROM products
WHERE list_price >= 2000;

SELECT *
FROM products
WHERE model_year >= 2018
INTERSECT
SELECT *
FROM products
WHERE list_price >= 2000;

SELECT *
FROM products
INTERSECT
SELECT *
FROM products;


SELECT *
FROM products
WHERE model_year >= 2018
EXCEPT
SELECT *
FROM products
WHERE list_price >= 2000;


-- INSERT INTO products_tmp
--   SELECT *
--   FROM products
--   WHERE model_year >= 2018
--   EXCEPT
--   SELECT *
--   FROM products
--   WHERE list_price >= 2000;