뚝딱뚝딱

💡 비절차적 데이터 조작어(DML) VS 절차적 데이터 조작어(ORACLE / SQL server) [4]

• 비절차적 데이터 조작어는 사용자가 무슨(what) 데이터를 원하는 지만을 명세
• 절차적 데이터 조작어는 어떻게(how) 데이터를 접근해야 하는지 명세
  
  

💡 DDL / DML / DCL / TCL 

• DDL : CREATE, DROP, ALTER, RENAME
• DML : SELECT, INSERT, UPDATE, DELETE
• DCL : GRANT, REVOKE
• TCL : COMMIT, ROLLBACK

💡 SQL server [7]

• SQL server에서는 여러개의 컬럼을 동시에 수정하는 구문은 지원하지 않는다.
• SQL server에서는 괄호를 사용하지 않는다.

💡 PK / UNIQUE

• PK : UNIQUE & NOT NULL
• UNIQUE : 테이블 내 중복 값 x, NULL 입력 가능

💡 테이블명-칼럼명 설정 규칙

• 반드시 문자로 시작
• A-Z, a-z, 0-9, $, _, #

💡 외래키

• 외래키 값은 널 값을 가질 수 있음
• 한 테이블에 여러개 존재 가능

💡 컬럼 삭제 sql [16]

• ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

💡 참조무결성 규정 [19]

• DELETE/MODIFY
  CASCADE : master 삭제시 child 같이 삭제
  Set Null : master 삭제 시 child 해당 필드 Null
  Set Default : master 삭제 시 child 해당 필드 Default 값으로 설정
  Restrict : child 테이블에 PK 값이 없는 경우만 master 삭제 허용
  No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음
• INSERT
  Automatic : master 테이블에 PK가 없는 경우 master PK를 생성 후 child 입력
  Dependent : master 테이블에 PK가 존재할 때만 child 입력 허용

💡 테이블 이름 변경 sql [18]

• RENAME 구이름 TO 새이름;

💡 제약조건에 따른 실행 [22]

• FK에 대한 제약조건으로, DELETE 시 SET NULL = 부모 키가 DELETE되면 해당 FK는 NULL을 갖도록 설정
• 그런데, 문제에서는 해당 FK 컬럼이 NOT NULL로 지정되어 있기 때문에, 위와 같이 설정되어 있더라도 NULL 값을 가질 수 없으므로, 이와 같이 수행되는 SQL문은 오류가 발생함 

💡 DELETE [23]

Q. 개발 프로젝트의 표준은 모든 삭제 데이터에 대한 로그를 남기는 것을 원칙으로 하고, 테이블 삭제의 경우는 허가된 인력만이 정기적으로 수행 가능하도록 정하고 있다. 개발팀에서 사용 용도가 없다고 판단한 STADIUM 테이블의 데이터를 삭제하는 가장 좋은 방법은 무엇인가?

A. DELETE FROM STADIUM;

• DELETE는 로그는 남기고, 데이터만 삭제한다.
• TRUNCATE TABLE과 DROP은 로그를 남기지 않으므로 해당하지 않는다.

💡 TRUNCATE TABLE

• 특정 테이블의 모든 데이터를 삭제
• 디스크 사용량을 초기화

💡 DROP vs TRUNCATE vs DELETE [26]

I-2 데이터 모델링

Q 데이터 베이스를 구축하기 위한 용도를 위해 데이터 모델링을 수행하고 업무에 대한 설명은 별도의 표기법을 이용한다.(x)

=> 데이터 모델링이라는 것은 단지 데이터베이스만을 구축하기 위한 용도로 쓰이는 것이 아니라 데이터 모델링 자체로서 업무를 설명하고 분석하는 부분에서도 매우 중요한 의미를 가지고 있다.

I-13 엔터티의 이름을 부여하는 방법

Q 가능하면 약어를 사용하여 엔터티의 이름을 간결하고 명확하게 한다 (x)

=> 가능하면 약어를 사용하지 않는다.

=> 엔터티 생성의미대로 이름을 부여한다.

I-14 속성

Q 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

=> 속성(ATTRIBUTE)

I-12 식별자

Q 다른 엔터티로부터 주식별자를 상속받지 않고, 자신의 고유한 주식별자를 가지며 사원, 부서, 고객, 상품, 자재 등이 예가 될 수 있는 엔터티

=>기본엔터티(키 엔터티)

I-20 관계

Q 데이터 모델링에는 존재적 관계와 행위에 의한 관계를 구분하는 표기법이 없지만, UML에서는 연관관계와 의존관계에 대해 다른 표기법을 가지고 표현하게 되어있다.

=> 실선과 점선의 표기법

I-23 관계

Q  업무기술서, 장표에 관계연결을 가능하게 하는 명사가 있는가? (X)

=> 동사가 있는가?

I-29 관계

Q 부모 엔터티의 주식별자를 자식엔터티에서 받아 손자엔터티까지 계속 흘려보내기 위해 비식별자관계를 고려한다 (X)

=> 식별자 관계를 고려

💡 문제)
USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

1. 회원 ID, 닉네임, 총거래금액을 조회
   SELECT B.USER_ID, B.NICKNAME, SUM(A.PRICE) AS TOTAL_SALES
   문제에서 주어진 쿼리 결과대로 총거래금액의 출력 시 이름을 TOTAL_SALES로 바꾸어준다.
   
   
2. USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서
   테이블 두개가 연결되어야 함을 알 수 있다. 테이블을 보면 USER_ID에 대해서 중복된 것을 알 수 있다 (컬럼이름은 각 테이블에서 다르지만, 결국은 USER의 아이디를 가리키고 있음)
   따라서 두 테이블을 유저 아이디에 대한 조건으로 INNER JOIN시켜야 한다.
   그래야 각 상품 목록에 대해서 유저의 정보를 함께 출력하는 테이블을 얻을 수 있기 때문이다.
   FROM USED_GOODS_BOARD A JOIN USED_GOODS_USER B ON A.WRITER_ID=B.USER_ID
   
   
3. 완료된 중고 거래의
   위의 2번까지를 통해 각 상품에 대한 유저 정보까지 합친 테이블을 얻었으니, 이제 그 중에서도 완료된 거래에 대해서만 추려야 한다.
   그러러면 WHERE 절을 이용해서 STATUS가 DONE인 것만을 가져올 수 있다.
   WHERE A.STATUS="DONE"
   
   
4. 총금액이 70만 원 이상인 사람의
   위의 과정까지는 각 상품에 대해서 개별적으로 조회되기 때문에, 한 사람의 전체 거래 내역을 고려하기 위해서는 사람의 단위로 GROUP을 지어야 한다.
   따라서 GROUP BY 절을 통해 USER_ID 별로 그룹화 시킨다.
   그러면 거래가 완료된 상품의 거래 주인의 아이디, 이름, 총거래금액이 출력되는데, 이 중에서도 조건에서 총거래금액이 70만원 이상인 사람의 것만 출력하라고 되어 있다.
   그러므로 GROUP BY 절에 HAVING을 추가해서 그 그룹 중 총거래금액이 70만원이상인 것만 출력하도록 해준다.
   GROUP BY B.USER_ID HAVING TOTAL_SALES>=700000
   
   
5. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.
   (나는 이 단계를 빼먹고 채점 결과가 자꾸 틀렸다고 떠서 시간을 낭비했다. 꼭 정렬 조건을 확인하도록 하자)
   이제 조건에 만족하는 결과는 다 출력했다.
   마지막 조건인 정렬을 해준다.
   ORDER BY절을 통해 해줄 수 있다.
   ORDER BY TOTAL_SALES;

위 다섯 단계를 거치면서 하나하나씩 접근하면 매우 쉬운 문제이다!

조인, WHERE절, GROUP BY절, ORDER BY절이 다 쓰인 문제라서, 순서를 한번 정리하고 넘어가자

SELECT > FROM > JOIN-ON > WHERE > GROUP BY - HAVING > ORDER BY

FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

WHERE문 안에서는 PRICE=MAX(PRICE) 이런식으로 접근할 수 없다. 따라서 MAX(PRICE)의 값을 가져오려면 서브쿼리를 이용해서 MAX(PRICE) 값을 가져올 수 있도록 해야한다.

SELECT MAX(PRICE) FROM FOOD_PRODUCT를 서브쿼리로 해서 MAX(PRICE)값을 가져오고 그 값이 PRICE와 같으면 해당 정보를 조회하도록 한다.

최종 코드는 아래와 같다.

SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

= 대신에 IN을 써도 무방하다.

**

다른 사람들의 풀이를 보다가 PRICE에 대해 내림차순 정렬 후 LIMIT 1 을 사용해서 조회한 풀이를 발견하였다.

그런데, 내 생각에는, LIMIT 1을 할 시 , 동일하게 MAX PRICE를 갖는 레코드에 대해서는 하나의 레코드만 출력되는 예외가 발생할 수 있기 때문에, 서브쿼리를 이용한 풀이가 더 맞다고 본다. 

이 문제는 원래 JOIN 카테고리에 있는 문제이지만, JOIN으로 풀었을 때, 두 테이블간의 관계가 중복이 되는 문제와 둘 중 한 테이블이 비어 있을 경우에는 ON에 대한 부분에서 성립이 안되는 것 같아서, 간단하게 UNION ALL로 해결 했다.

다른 사람들의 JOIN 풀이 방법을 보았는데, 실제로는 TOTAL_ORDER의 SUM값이 중복되어 계산되는 경우들이 있어서 아직 JOIN으로의 해결은 완벽히 하지 못했다.

UNION ALL로 해결한 간단한 방법에 대해 소개한다.

UNION ALL

SQL의 집합 연산자 중 하나인데, 중복 레코드도 허용한다는 특징이 있다.

예시 테이블을 보면, 어찌되었던, FIRST_HALF에 있는 값도, JULY에 있는 값도 모두 더해져야 할 대상이다.

따라서 두 테이블의 중복 여부와 상관없이, 두 테이블의 모든 값을 한 테이블로 합쳐주어야 한다.

두 테이블의 컬럼들은 모두 동일하다. 그래서 더 쉽게 사용할 수 있다.

두 테이블을 합칠 때는 SELECT * FROM FIRST_HALF UNION ALL SELECT * FROM JULY 로 합칠 수 있다.

이렇게 합쳐진 하나의 테이블에서 필요한 컬럼들을 가져올 것인데, 조회를 하기 위해 필요한 FLAVOR와 SUM(TOTAL_ORDER)을 가져올 것이고, 이때 SUM을 FLAVOR별로 합쳐야 하기 때문에 GROUP BY FLAVOR를 해준다.

여기 까지 하면

SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER_SUM 

FROM (

(SELECT * FROM FIRST_HALF UNION ALL SELECT * FROM JULY)

)AS A 

GROUP BY FLAVOR

이다.

중간 중간 AS로써 컬럼의 이름과 조회 테이블의 이름을 지정해주었다.

이제 FLAVOR와 각 FLAVOR마다의 총 ORDER로 구성된 테이블은 구해졌다.

이 테이블에서 최종으로 조회해야 할 상위 FLAVOR 3개를 출력하면 끝이다.

상위 FLAVOR는 ORDER합의 큰 것부터 이므로, 합을 기준으로 내림차순 해준다.

SELECT FLAVOR 

FROM (

SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER_SUM 

FROM (

(SELECT * FROM FIRST_HALF) UNION ALL (SELECT * FROM JULY)

) AS A

GROUP BY FLAVOR

) AS B

ORDER BY TOTAL_ORDER_SUM DESC

그리고 상위 3개만 구하면 끝이므로, MYSQL에서 지원하는 LIMIT 을 통해 상위 3개만 조회한다.

SELECT FLAVOR 

FROM (

SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER_SUM 

FROM (

(SELECT * FROM FIRST_HALF) UNION ALL (SELECT * FROM JULY)

) AS A

GROUP BY FLAVOR

) AS B

ORDER BY TOTAL_ORDER_SUM DESC

LIMIT 3;

최종 코드를 한번 더 첨부한다.

SELECT FLAVOR 
FROM (
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER_SUM 
FROM (
(SELECT * FROM FIRST_HALF) UNION ALL (SELECT * FROM JULY)
) AS A
GROUP BY FLAVOR
) AS B
ORDER BY TOTAL_ORDER_SUM DESC
LIMIT 3;

JOIN방법을 찾는 다면 다음 편에 이어서 작성하겠다

첫 sql문 문제 풀이이다.

데이터베이스설계를 들으면서 sql문은 어느 정도 익숙해져 있고, 정처기를 준비하면서도 복잡한 sql문을 공부해 왔어서 어렵지 않게 문제를 풀 수 있었으나...! 역시 디테일한 부분은 좀 더 공부할 필요가 있음을 느낀 첫 문제였다.

DATE 형식의 컬럼의 출력 형태 변환하는 방법!

이 문제에서 내가 걸렸던 부분은 주의사항에 있는 DATE 형식을 주어진 예제의 출력결과처럼 변경하여 출력할 수 있도록 하는 것이었다.

문제에서 PUBLISHED_DATE의 형식을 'YYYY-MM-DD' 형태로 출력하도록 요구하고 있다.

처음에는 이 부분을 확인하지 못하고 그냥 출력해 보았는데, 뒤에 상세한 시간까지 출력되는 것을 확인하였다.

이렇게 시간까지 표시되는 것을, 날짜만 표시되도록 변경해주어야 한다.

이때 사용할 수 있는 것이 DATE_FORMAT() 함수이다!!

mysql : DATE를 원하는 형식으로 변경하기

각 sql마다 다른데, 내가 사용하는 mysql을 기준으로는 다음과 같은 방식을 사용할 수 있다.

1. 날짜→문자열로 변환

DATE FORMAT(날짜, 출력 형식)

문제 예시 ) DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d')

위의 형태의 날짜를 '2020-01-02'의 형태로 리턴해준다.

2. 문자열→날짜로 변환
 
STR_TO_DATE(문자, 출력 형식)
 
예시) 
STR_TO_DATE('20080101', '%Y-%m-%d')

20080101이라는 문자를 2008-01-01의 형태의 날짜로 리턴해준다.

---

여기에서 주의할 점은 '%Y-%m-%d' 이 부분인데, 여기에서 Y이면 '2020'과 같은 형태를, m이면 '03'과 같은 형태를, d이면 '23'과 같은 형태로, 보통 'yyyy-mm-dd'형식의 출력을 얻을 수 있다.
이 부분이 왜 헷갈리냐면, 저 문자를 대문자로 쓰느냐, 소문자로 쓰느냐에 따라 다른 출력이 되기 때문이다.
나도 처음에는 모두 대문자로 써서, 월은 'march', 일은 'secondary_three' 이런식으로 나왔다.
따라서 형식에 따라 잘 구분지어 주어야 한다. 
자세한 내용은 아래의 표를 참고하자.

**FORMAT설명

위의 방법을 사용하고 출력한 결과이다.

원하는 출력 형태를 얻을 수 있었다!

전체 코드는 아래와 같다.

SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A JOIN AUTHOR B ON A.AUTHOR_ID=B.AUTHOR_ID 
WHERE A.CATEGORY='경제' 
ORDER BY A.PUBLISHED_DATE;

join할 때 조인할 테이블을 A, B이런식으로 지정해주는 건 정처기 때 버릇.. 아주 좋은 버릇인거 같다.

가끔 안해도 돌아갈 때가 있는데, 정확하게 쓰는 거는 위가 맞기 때문에, 최대한 쓰려고 한다.

select ~ from~join~on~where~order by~ 순으로 알아보기 쉽게 줄바꿈 해 두었다.

* DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE 

이런 부분의 경우, 예시 출력 형태를 보고 컬럼 이름을 잘 지정해주는 것이 중요한 포인트이다.

아깝게 틀릴 수 있는 사소한 문제이기 때문이다!

이상 백준허브 연동 겸 가볍게 풀어본 sql문제였다.