sungsWork

나의 답:

SELECT ED.ID, ED.GENOTYPE, EDP.GENOTYPE AS PARENT_GENOTYPE FROM ECOLI_DATA AS ED
LEFT JOIN (SELECT ID, GENOTYPE FROM ECOLI_DATA) AS EDP
ON ED.PARENT_ID = EDP.ID
WHERE ED.GENOTYPE & EDP.GENOTYPE = EDP.GENOTYPE
ORDER BY ID;

주어진 테이블의 SIZE_OF_COLONY, DIFFERENTIATION_DATE 는 필요없음.

비트연산자에 대한 개념을 몰라서 이렇게 간단한 쿼리인줄 모르고 끙끙 앓았다.

이진법이 나오면 비트연산자를 의심하자.

부모의 형질을 다 포함하냐는 말은 자식 형질과 부모 형질의 교집합이 부모 형질이냐는 말과 같다 !

즉, & 비트연산자를 사용했을 때 결과가 부모 형질과 같아야 한다.

비트연산자

a = 25, b = 10

이를 이진법으로 표현하면

a = 0001 1001, b = 0000 1010

두 값의 각 자리를 비교하며 해당 자리의 결과를 출력하는 연산자

AND 연산( & ): 둘 다 1일 때만 1을 출력

a & b = 0000 1000 = 8

OR 연산( | ): 둘 중 하나라도 1이면 1을 출력

a | b = 0001 1011 = 27

XOR 연산( ^ ): 둘 중 하나만 1일 때만 1을 출력

a ^ b = 0001 0011 = 19

하나의 값과 사용되는 연산자

보수 연산( ~ ): 모든 비트를 반전시킨다

~a = 1110 0110 = -26

왼쪽 시프트(<<): 지정된 비트 수만큼 왼쪽으로 이동

a << 2 = 0110 0100 = 100

오른쪽 시프트(>>): 지정된 비트 수만큼 오른쪽으로 이동

a >> 2 = 0000 0110 = 6 

조건절에서 사용되는 기타 연산자

각 단어의 뜻을 이해하면 암기하지 않아도 쉽다.

• IN: 목록 내의 값과 일치하는지 확인할 때 사용
• ANY: 여러 값 중 하나라도 조건을 만족하면 됨
• ALL: 모든 값이 조건을 만족해야 함
• EXISTS: 서브쿼리의 결과가 존재하는지 여부만 확인 (IN과 비슷)

SELECT * FROM 상품 WHERE 카테고리 IN ('의류', '신발', '가방');

SELECT * FROM 상품  WHERE 가격 > ANY (5000, 10000, 15000);  -- 5000보다 비싼 상품들 조회됨

SELECT * FROM 상품  WHERE 가격 < ANY (5000, 10000, 15000);  -- 15000보다 저렴한 상품들 조회됨

SELECT * FROM 상품  WHERE 가격 = ANY (5000, 10000, 15000);  -- IN과 동일 기능

SELECT * FROM 상품  WHERE 가격 > ALL (5000, 10000, 15000); -- 15000보다 비싼 상품들 조회됨

SELECT * FROM 상품  WHERE 가격 < ALL (5000, 10000, 15000); -- 5000보다 저렴한 상품들 조회됨

SELECT * FROM 상품  WHERE 가격 = ALL (5000, 10000, 15000); -- X

SELECT * FROM 주문  WHERE EXISTS (SELECT * FROM 배송완료);

나의 답:

SELECT * FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC

LIKE는 = 연산자처럼 조건문에서 사용되며

어떤 문자열이 포함되었는지 여부를 유연하게 판별할 수 있도록 하는 연산자다.

% 와 _ 문자를 문자열에 포함하여 사용할 수 있다.

%는 0개 이상의 문자열, _는 하나의 문자를 의미한다.

[연습]

SELECT * FROM 테이블명 WHERE 컬럼 LIKE 'ABC'

>> = 'ABC'와 같은 기능. 

SELECT * FROM 테이블명 WHERE 컬럼 LIKE 'ABC%'

>> 'ABC'로 시작하는 문자열이라면 true 반환. ex) 'ABC', 'ABCDE'

SELECT * FROM 테이블명 WHERE 컬럼 LIKE '%ABC%'

>> 'ABC'를 포함하는 문자열이면 true 반환. ex) 'ABC', 'ABCDE', 'ZZABCD'

SELECT * FROM 테이블명 WHERE 컬럼 LIKE 'A%BC'

>> 'A'로 시작하고 'BC'로 끝나는 문자열이면 true 반환. ex) 'ABC', 'AZZBC'

SELECT * FROM 테이블명 WHERE 컬럼 LIKE 'ABC_'

>> 'ABC' 뒤에 문자 하나가 더 붙은 문자열이면 true 반환. ex) 'ABCD'

SELECT * FROM 테이블명 WHERE 컬럼 LIKE '___ABC'

>> 'ABC' 앞에 문자 세개가 더 붙은 문자열이면 true 반환. ex) 'ZZZABC'

나의 답:

SELECT FH.FLAVOR FROM FIRST_HALF AS FH
JOIN ICECREAM_INFO AS II 
ON FH.FLAVOR = II.FLAVOR
WHERE FH.TOTAL_ORDER > 3000 AND II.INGREDIENT_TYPE = 'fruit_based'
ORDER BY FH.TOTAL_ORDER DESC;

데이터를 주제에 따라 겹치지 않게 효율적인 구조로 정규화를 해서 테이블을 분리했다면,

조회할때는 두개 이상의 테이블에서 데이터를 묶어 가져오는 조인을 사용한다.

JOIN ON 구문의 위치는 WHERE 전이어야 한다.

기준테이블에 추가로 컬럼을 합쳐 테이블을 형성한 뒤

조건문으로 정제가 시작되는 것이다.

JOIN 사용시 가독성을 위해 주로 테이블에 별칭을 붙혀주고,

컬럼 사용시 어떤 테이블의 컬럼인지 별칭.~을 붙혀주는 것이 좋다.

ICECREAM_INFO 테이블의 FLAVOR는

FIRST_HALF테이블 FLAVOR의 외래키다. (조인하는 테이블은 주로 일대다 관계(기본키-외래키))

= ICECREAM_INFO 테이블에서 FLAVOR는 기본키이다.

JOIN에는 INNER JOIN, (LEFT / RIGHT) OUTER JOIN, FULL JOIN 이 있다.

가장 많이 사용하는건 INNER JOIN이다. INNER를 생략해도 이너조인으로 인식한다.

위의 예제는 OUTER JOIN을 이해하기 좋은 케이스는 아니다.

왜냐면 합치는 기준컬럼(FLAVOR)에 대한 모든 경우가 ICECREAM_INFO테이블에 들어있기 때문이다.

아래의 새로운 케이스로 살펴보자.

-- Customers 테이블
| CustomerID | CustomerName |
|------------|--------------|
| 1          | John         |
| 2          | Jane         |
| 3          | Bob          |
| 4          | Alice        |

-- Orders 테이블
| OrderID | CustomerID | OrderDate  |
|---------|------------|------------|
| 101     | 1          | 2024-01-15 |
| 102     | 2          | 2024-01-20 |
| 103     | 1          | 2024-02-10 |
| 105     | 5          | 2024-03-05 |

위의 두개 테이블을 보면 바로

Order테이블이 기준테이블이 되고, CustomersID를 기준으로 Customers테이블을 조인하겠구나 라는 생각이 든다.

1. INNER JOIN

SELECT o.OrderID, o.OrderDate, 
       c.CustomerID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 결과
| OrderID | OrderDate  | CustomerID | CustomerName |
|---------|------------|------------|--------------|
| 101     | 2024-01-15 | 1          | John         |
| 102     | 2024-01-20 | 2          | Jane         |
| 103     | 2024-02-10 | 1          | John         |

두 테이블의 교집합에 해당하는 데이터(레코드)만 조회가 된다.

2. LEFT OUTER JOIN

SELECT o.OrderID, o.OrderDate, 
       c.CustomerID, c.CustomerName
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 결과
| OrderID | OrderDate  | CustomerID | CustomerName |
|---------|------------|------------|--------------|
| 101     | 2024-01-15 | 1          | John         |
| 102     | 2024-01-20 | 2          | Jane         |
| 103     | 2024-02-10 | 1          | John         |
| 105     | 2024-03-05 | NULL       | NULL         |

기준테이블의 데이터(레코드)들은 모두 조회되고,

거기에 해당하는 Orders테이블의 데이터는 없으니 NULL처리가 된다.

(교집합에 해당하는 데이터 + 기준테이블 쪽 데이터)

3. RIGHT OUTER JOIN

SELECT o.OrderID, o.OrderDate, 
       c.CustomerID, c.CustomerName
FROM Orders o
FULL OUTER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 결과
| OrderID | OrderDate  | CustomerID | CustomerName |
|---------|------------|------------|--------------|
| 101     | 2024-01-15 | 1          | John         |
| 102     | 2024-01-20 | 2          | Jane         |
| 103     | 2024-02-10 | 1          | John         |
| NULL    | NULL       | 3          | Bob          |
| NULL    | NULL       | 4          | Alice        |

조인 테이블의 데이터(레코드)들은 모두 조회되고,

거기에 해당하는 Orders테이블의 데이터는 없으니 NULL처리가 된다.

(교집합에 해당하는 데이터 + 조인테이블 쪽 데이터)

4. FULL JOIN

SELECT o.OrderID, o.OrderDate, 
       c.CustomerID, c.CustomerName
FROM Orders o
FULL OUTER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 결과
| OrderID | OrderDate  | CustomerID | CustomerName |
|---------|------------|------------|--------------|
| 101     | 2024-01-15 | 1          | John         |
| 102     | 2024-01-20 | 2          | Jane         |
| 103     | 2024-02-10 | 1          | John         |
| 105     | 2024-03-05 | NULL       | NULL         |
| NULL    | NULL       | 3          | Bob          |
| NULL    | NULL       | 4          | Alice        |

두 테이블간의 합집합 원소(레코드)를 모두 보여준다.

만약 Customers 테이블을 기준으로 조인한다면 어떨지도 살펴보자.

1. INNER JOIN  >> 동일

SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 결과
| CustomerID | CustomerName | OrderID | OrderDate  |
|------------|--------------|---------|------------|
| 1          | John         | 101     | 2024-01-15 |
| 1          | John         | 103     | 2024-02-10 |
| 2          | Jane         | 102     | 2024-01-20 |

2. LEFT OUTER JOIN  >> 위의 RIGHT OUTER JOIN과 동일

SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 결과
| CustomerID | CustomerName | OrderID | OrderDate  |
|------------|--------------|---------|------------|
| 1          | John         | 101     | 2024-01-15 |
| 1          | John         | 103     | 2024-02-10 |
| 2          | Jane         | 102     | 2024-01-20 |
| 3          | Bob          | NULL    | NULL       |
| 4          | Alice        | NULL    | NULL       |

3. RIGHT OUTER JOIN  >> 위의 LEFT OUTER JOIN과 동일

SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 결과
| CustomerID | CustomerName | OrderID | OrderDate  |
|------------|--------------|---------|------------|
| 1          | John         | 101     | 2024-01-15 |
| 1          | John         | 103     | 2024-02-10 |
| 2          | Jane         | 102     | 2024-01-20 |
| NULL       | NULL         | 105     | 2024-03-05 |

4. FULL JOIN  >> 동일

SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 기준
| CustomerID | CustomerName | OrderID | OrderDate  |
|------------|--------------|---------|------------|
| 1          | John         | 101     | 2024-01-15 |
| 1          | John         | 103     | 2024-02-10 |
| 2          | Jane         | 102     | 2024-01-20 |
| 3          | Bob          | NULL    | NULL       |
| 4          | Alice        | NULL    | NULL       |
| NULL       | NULL         | 105     | 2024-03-05 |

나의 답:

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,'%Y-%m-%d') AS HIRE_YMD FROM DOCTOR
WHERE MCDP_CD ='CS' OR MCDP_CD ='GS'
ORDER BY HIRE_YMD DESC, DR_NAME;

AS는 alias(별명/가명)의 약어로, 테이블이나 컬럼 뒤에 붙히고(생략도 가능) 이후에 해당 별칭으로 다루게 된다. 

주로 컬럼의 별칭은 계산된 컬럼을 조회하여 보여주는 경우에 필드명을 알아보기 좋도록 사용되고,

테이블 별칭은 조인시 쿼리의 가독성을 위해 잘 사용된다.

DATE_FORMAT함수는 인자를 두개 받는다.

DATE_FORMAT(날짜데이터, '출력할포맷') 이렇게 사용해주면 되는데,

출력할 포맷에서 활용되는 문자는 %알파벳의 형태다. 

예를 들어 날짜데이터 속 연도는 %Y 혹은 %y에 담기게 된다.

이렇게 내맘대로 포맷문자를 포함해서 문자열을 입력하면

해당 포맷으로 데이터가 출력된다.

이 문제에서 의문점은 HIRE_YMD의 타입이 DATETIME이 아닌 DATE인데, 포맷 없이 출력시 시간까지 조회된다는 것이다.

문제에서 주어진 타입이 오라클 기준이어서 그런 것 같다.
(오라클은 날짜데이터 타입이 DATE, TIMESTAMP 두개고, 둘다 날짜와 시간 모두 포함한다.)

추가로 날짜데이터를 YEAR(), MONTH(), DAY() 함수로 감싸서 해당 값만 꺼내 활용할 수도 있다.

+ 코테문제는 주로 문제설명, 문제, 예시, 주의사항으로 나눠지는 것으로 보인다.

문제 설명은 문제에 등장하는 테이블의 틀을 설명해준다. 

문제에서 본격적인 문제가 나오고 (가끔 문제 설명에 문제까지 그냥 합쳐져있는 경우도 있음)

예시에서는 어떤 입력데이터가 주어졌을 때 어떤 출력이 나와야 하는지 보여주며 이해를 도와준다.

추가로 주의사항이 있는 경우도 있다.

몇 문제 풀어보며 느낀점:

1. 문제설명보다는 문제와 예시를 중점적으로 봐야한다.

문제 설명은 처음엔 테이블이 한개인지 그 이상인지 정도만 확인하고

이후 문제를 풀며 가끔씩 돌아와 확인하는 용도가 적당한 것 같다.

2. 주의사항이 있는지 까먹지 말고 잘 봐야한다.

3. 예시에서 출력 테이블을 확인하여 어떤 컬럼을 셀렉해야 하는지 빠르게 파악할 수 있다.

나의 답:

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(SALES_AMOUNT) > 1
ORDER BY USER_ID, PRODUCT_ID DESC;

COUNT() 안에는 SALES_AMOUNT가 아닌 다른 컬럼이 들어가도 상관 없다.

어떤 사용자가 어떤 상품을 또 구매한 이력이 있는지를 알려면 그 두개를 기준으로 그룹화 했을 때

뭉쳐지는 어떤 필드의 데이터 개수를 구하면 되는 것이다.

참고

SELECT USER_ID, PRODUCT_ID, COUNT(ONLINE_SALE_ID), COUNT(SALES_DATE), COUNT(SALES_AMOUNT) 
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(ONLINE_SALE_ID) > 1
ORDER BY USER_ID, PRODUCT_ID DESC;

결과:

중요한 건

• group by로 두개 이상의 컬럼을 기준으로 그룹화 할 수 있음.
• group by를 사용한 쿼리에서 조건문을 having 대신 where에 쓰지 않도록 주의해야 한다.
  (where은 그룹화 전 조건처리, having은 그룹화 후 보여주기 전 조건처리.
  즉, 조건에 집계함수 이용한다는건 그룹화한 이후 시점의 처리이므로 having 이용)

SELECT product, COUNT(*)
FROM products
WHERE price > 100 -- 그룹화 전에 조건
GROUP BY product;

마지막으로 order by는 간단함.

쿼리의 마지막에 위치하고, 컬럼과 함께 asc(오름차순) desc(내림차순) 명시 해주거나 생략하면 asc처리됨.

컬럼 asc(생략가능)/desc를 나열해서 쓸 수 있는데, 먼저쓸수록 우선순위로 처리되고 같은 것끼리 뒤의 조건으로 처리됨.

나의 답:

함수 없이 ORDER BY와 LIMIT를 이용했다.

SELECT DATETIME FROM ANIMAL_INS 
ORDER BY DATETIME desc
LIMIT 1

아마 출제자의 의도:

MAX함수로 컬럼을 감싸주면 더 간결한 쿼리를 작성할 수 있다. 늘 함수를 염두해 두자.

SELECT MAX(DATETIME) '시간'
FROM ANIMAL_INS;

MAX함수는 집계함수이며 GROUP BY와 많이 쓰인다.

집계함수는 여러 행에서 하나의 결과값을 추출하는 함수이고 SUM, COUNT, MAX, MIN, AVG등이 있다.

GROUP BY를 알아보자. 

만약 사용하게 된다면 이런 형태를 갖추게 된다.

select 컬럼들 from 테이블 where 조건문

group by 컬럼 having 조건문

group by로 기준이 되는 컬럼 외에 다른 컬럼도 조회하게 된다면
여러 값이 뭉치게 되므로 해당 컬럼을 집계함수로 감싸줘야 되는 원리이다.

만약 group by 없이 집계함수를 사용하게 되면 위와 같이 하나의 레코드만 출력되는 결과가 나온다.

(단, group by 없이 집계함수를 사용했을 때, 일반 컬럼이 섞여있다면 오류가 발생한다.)

그럼 만약에 아래와 같은 쿼리는 결과가 어떨까?

SELECT MAX(DATETIME), MIN(DATETIME) 
FROM ANIMAL_INS

결과는

하나의 레코드에 두 값이 각각 출력된다.

총 두개의 행이 나오지 않을까 싶었는데, 그러면 각 레코드에 해당 속성 말고는 해당되지 않으니 저렇게 재구성되어 조회되는 것이 맞다.

즉, 집계함수를 사용하게 되면 더이상 각 레코드의 정체성은 사라지고 값을 조회하는 용도로 사용되는 느낌이다.

마지막 예시로 이해를 완전히 해보자.

SELECT SEX_UPON_INTAKE, MAX(DATETIME), MIN(DATETIME) 
FROM ANIMAL_INS 
GROUP BY SEX_UPON_INTAKE

결과:

(각각의 SEX_UPON_INTAKE(기준컬럼)중에 MAX(DATETIME), MIN(DATETIME) 조회)