-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathvalidate_enum_data.sql
More file actions
80 lines (68 loc) ยท 2.8 KB
/
validate_enum_data.sql
File metadata and controls
80 lines (68 loc) ยท 2.8 KB
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
-- =========================
-- varchar โ enum ๋ณํ ์ ๋ฐ์ดํฐ ๊ฒ์ฆ SQL
-- =========================
-- meal.restaurant ๊ฒ์ฆ (ํ์ฉ๊ฐ: DODAM, DORMITORY, FOOD_COURT, SNACK_CORNER, HAKSIK, FACULTY)
SELECT DISTINCT restaurant, COUNT(*) as count
FROM meal
WHERE restaurant NOT IN ('DODAM','DORMITORY','FOOD_COURT','SNACK_CORNER','HAKSIK','FACULTY')
GROUP BY restaurant;
-- meal.time_part ๊ฒ์ฆ (ํ์ฉ๊ฐ: MORNING, LUNCH, DINNER)
SELECT DISTINCT time_part, COUNT(*) as count
FROM meal
WHERE time_part NOT IN ('MORNING','LUNCH','DINNER')
GROUP BY time_part;
-- menu_category.restaurant ๊ฒ์ฆ
SELECT DISTINCT restaurant, COUNT(*) as count
FROM menu_category
WHERE restaurant NOT IN ('DODAM','DORMITORY','FOOD_COURT','SNACK_CORNER','HAKSIK','FACULTY')
GROUP BY restaurant;
-- menu.restaurant ๊ฒ์ฆ
SELECT DISTINCT restaurant, COUNT(*) as count
FROM menu
WHERE restaurant NOT IN ('DODAM','DORMITORY','FOOD_COURT','SNACK_CORNER','HAKSIK','FACULTY')
GROUP BY restaurant;
-- user.provider ๊ฒ์ฆ (ํ์ฉ๊ฐ: EATSSU, KAKAO, APPLE)
SELECT DISTINCT provider, COUNT(*) as count
FROM user
WHERE provider NOT IN ('EATSSU','KAKAO','APPLE')
GROUP BY provider;
-- user.role ๊ฒ์ฆ (ํ์ฉ๊ฐ: USER, ADMIN)
SELECT DISTINCT role, COUNT(*) as count
FROM user
WHERE role NOT IN ('USER','ADMIN')
GROUP BY role;
-- user.status ๊ฒ์ฆ (ํ์ฉ๊ฐ: ACTIVE, INACTIVE)
SELECT DISTINCT status, COUNT(*) as count
FROM user
WHERE status NOT IN ('ACTIVE','INACTIVE')
GROUP BY status;
-- inquiry.status ๊ฒ์ฆ (ํ์ฉ๊ฐ: WAITING, ANSWERED, HOLD)
SELECT DISTINCT status, COUNT(*) as count
FROM inquiry
WHERE status NOT IN ('WAITING','ANSWERED','HOLD')
GROUP BY status;
-- report.report_type ๊ฒ์ฆ (ํ์ฉ๊ฐ: NO_ASSOCIATE_CONTENT, IMPROPER_CONTENT, IMPROPER_ADVERTISEMENT, COPY, COPYRIGHT, EXTRA)
SELECT DISTINCT report_type, COUNT(*) as count
FROM report
WHERE report_type NOT IN ('NO_ASSOCIATE_CONTENT','IMPROPER_CONTENT','IMPROPER_ADVERTISEMENT','COPY','COPYRIGHT','EXTRA')
GROUP BY report_type;
-- report.status ๊ฒ์ฆ (ํ์ฉ๊ฐ: PENDING, IN_PROGRESS, RESOLVED, REJECTED, FALSE_REPORT)
SELECT DISTINCT status, COUNT(*) as count
FROM report
WHERE status NOT IN ('PENDING','IN_PROGRESS','RESOLVED','REJECTED','FALSE_REPORT')
GROUP BY status;
-- =========================
-- ์ ์ฒด ๋ฐ์ดํฐ ๋ถํฌ ํ์ธ
-- =========================
SELECT 'meal.restaurant' as table_column, restaurant as value, COUNT(*) as count FROM meal GROUP BY restaurant
UNION ALL
SELECT 'meal.time_part', time_part, COUNT(*) FROM meal GROUP BY time_part
UNION ALL
SELECT 'menu.restaurant', restaurant, COUNT(*) FROM menu GROUP BY restaurant
UNION ALL
SELECT 'user.provider', provider, COUNT(*) FROM user GROUP BY provider
UNION ALL
SELECT 'user.role', role, COUNT(*) FROM user GROUP BY role
UNION ALL
SELECT 'user.status', status, COUNT(*) FROM user GROUP BY status
ORDER BY table_column, value;