-
Notifications
You must be signed in to change notification settings - Fork 10
Expand file tree
/
Copy pathjoin_subquery_exam.sql
More file actions
238 lines (141 loc) · 4.13 KB
/
join_subquery_exam.sql
File metadata and controls
238 lines (141 loc) · 4.13 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
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
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
-- join & sub query
-- 20210615
-- 마당서점 sub query
select * from book;
select * from customer;
select * from orders;
--1 마당서점의고객이요구하는다음질문에대해SQL 문을작성하시오.
--(5) 박지성이 구매한
-- 도서의 출판사수
SELECT COUNT(DISTINCT PUBLISHER)
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE C.CUSTID=O.CUSTID AND O.BOOKID=B.BOOKID
AND C.NAME='박지성'
;
-- 박지성이 구매한 도서 ID
SELECT DISTINCT O.BOOKID
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID=C.CUSTID AND C.NAME='박지성'
;
SELECT COUNT(DISTINCT PUBLISHER)
FROM BOOK
WHERE BOOKID IN (
SELECT DISTINCT O.BOOKID
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID=C.CUSTID AND C.NAME='박지성'
)
;
--(6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT b.bookname, b.price, B.PRICE-o.saleprice AS PRICEGAP
FROM ORDERS O, CUSTOMER C, BOOK B
WHERE C.CUSTID=O.CUSTID AND O.BOOKID=B.BOOKID
AND C.NAME='박지성'
;
SELECT b.bookname, B.PRICE, B.PRICE-o.saleprice
FROM ORDERS O, BOOK B
WHERE O.BOOKID=B.BOOKID
AND CUSTID=(SELECT CUSTID FROM CUSTOMER WHERE NAME='박지성')
;
SELECT CUSTID FROM CUSTOMER WHERE NAME='박지성';
--(7) 박지성이 구매하지 않은
-- 도서의 이름
SELECT bookid-- *
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID=C.CUSTID AND C.NAME='박지성'
;
SELECT BOOKNAME, BOOKID
FROM BOOK
WHERE BOOKID NOT IN (
SELECT O.BOOKID
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID=C.CUSTID AND C.NAME='박지성'
)
;
--2 마당서점의 운영자와 경영자가 요구하는 다음질문에대해 SQL 문을작성하시오.
--(8) 주문하지 않은 고객의 이름(부속질의사용)
SELECT C.NAME
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID(+)=C.CUSTID
AND O.ORDERID IS NULL
;
SELECT *
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID(+)=C.CUSTID and o.orderid is null
;
SELECT NAME
FROM CUSTOMER
WHERE CUSTID NOT IN (SELECT DISTINCT CUSTID FROM ORDERS)
;
SELECT DISTINCT CUSTID FROM ORDERS ;
--(9) 주문 금액의 총액과 주문의 평균금액
SELECT SUM(SALEPRICE), AVG(SALEPRICE)
FROM ORDERS
;
--(10) 고객의 이름과 고객별 구매액
SELECT C.NAME, SUM(SALEPRICE)
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID=C.CUSTID
GROUP BY C.NAME
;
select o.custid ,
(select c.name from customer c where o.custid=c.custid),
sum(saleprice)
from orders o
group by o.custid
;
--(11) 고객의 이름과 고객이 구매한 도서 목록
SELECT C.NAME, B.BOOKNAME
FROM BOOK B, ORDERS O, CUSTOMER C
WHERE B.BOOKID=O.BOOKID AND O.CUSTID=C.CUSTID
;
--(12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의
-- 차이가 가장 많은 주문
SELECT MAX(B.PRICE-O.SALEPRICE)
FROM ORDERS O, BOOK B
WHERE O.BOOKID=B.BOOKID
;
SELECT B.BOOKNAME, B.PRICE-O.SALEPRICE
FROM ORDERS O, BOOK B
WHERE O.BOOKID=B.BOOKID
AND PRICE-SALEPRICE=(
SELECT MAX(B.PRICE-O.SALEPRICE)
FROM ORDERS O, BOOK B
WHERE O.BOOKID=B.BOOKID
)
;
--(13) 도서의 판매액 평균 보다
-- 자신의 구매액평균이 더높은 고객의 이름
SELECT AVG(SALEPRICE) FROM ORDERS;
SELECT C.NAME, AVG(SALEPRICE)
FROM ORDERS O, CUSTOMER C
WHERE O.CUSTID=C.CUSTID
GROUP BY C.NAME
HAVING AVG(SALEPRICE)>(
SELECT AVG(SALEPRICE) FROM ORDERS
)-- 평균 구매액
;
--3. 마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오.
--(1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
SELECT B.PUBLISHER
FROM ORDERS O, CUSTOMER C, BOOK B
WHERE O.CUSTID=C.CUSTID AND O.BOOKID=B.BOOKID
AND C.NAME='박지성'
;
SELECT *
FROM ORDERS O, CUSTOMER C, BOOK B
WHERE O.CUSTID=C.CUSTID AND O.BOOKID=B.BOOKID
AND B.PUBLISHER IN (
SELECT DISTINCT B.PUBLISHER
FROM ORDERS O, CUSTOMER C, BOOK B
WHERE O.CUSTID=C.CUSTID AND O.BOOKID=B.BOOKID
AND C.NAME='박지성'
)
AND C.NAME!='박지성'
;
--(2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름
SELECT C.NAME, COUNT(DISTINCT PUBLISHER)
FROM ORDERS O, CUSTOMER C, BOOK B
WHERE O.CUSTID=C.CUSTID AND O.BOOKID=B.BOOKID
GROUP BY C.NAME
HAVING COUNT(DISTINCT PUBLISHER) >= 2
;