DB - SQL 면접 연습 문제

구조적 질의 언어

• 관계형 데이터베이스를 조작하는 데 있어 만국 공통어
• 거의 모든 유형의 데이터베이스 조작법 제공

면접에 나오는 데이터베이스 문제는 대부분 스키마가 주어졌을 때 질의를 작성하는 문제! -> SELECT, INSERT 등의 기초적인 SQL 명령어에 대한 이해가 필요하다.

그 이외는 트랜잭션과 외부키 제약조건 등…

문제

1. 다음과 같은 테이블이 있는 데이터베이스가 주어졌다고 하자.

Olympics (
	city CHAR(16),
	year INTEGER(4)
);

Montreal 1976을 데이터베이스에 입력하기 위한 SQL문을 작성하라.

• INSERT INTO Olympics VALUES('Montreal', 1976)
  

1. 예시 스키마&데이터

Company (
	companyName CHAR(30),
    id INTEGER(4) PRIMARY KEY
);

EmployeesHired (
	id INTEGER(4) PRIMARY KEY,
    numHired INTEGER(4),
    fiscalQuarter INTEGER(4),
    FOREIGN KEY (id) REFERENCES Company
);

Company 샘플 데이터

EmployeesHired 샘플 데이터

2-1. 4분기에 직원을 고용한 모든 회사의 이름을 반환하는 SQL문을 작성하라.

• SELECT companyName FROM Company, EmployeesHired
  WHERE Company.id=EmployeesHired.id AND fiscalQuarter=4;
  

• 위의 SQL문에는 문제가 있다.

  • 4분기에 아무도 고용하지 않았다면?(==numHired값이 0이라면?)
  • EmployeesHired(6,0,4)와 같은 열이 존재할 수 있다 -> 위의 SQL문은 4분기에 한명도 고용하지 않은 회사에 대한 정보도 모두 반환한다

• SELECT companyName FROM Company, EmployeesHired
  WHERE Company.id=EmployeesHired.id AND fiscalQuarter=4
  		AND numHired > 0;
  

  numHired>0을 통해 4분기에 한 명도 고용하지 않은 회사에 대한 정보는 제외한다.

2-2. 1분기에서 4분기까지 한 명도 고용하지 않은 회사 이름을 모두 반환하는 SQL문을 작성하라.

• NOT IN 활용

• SELECT companyName FROM Company WHERE NOT IN
  (SELECT id FROM EmployeesHired WHERE numHired > 0);
  

2-3. 모든 회사의 이름과 1분기에서 4분기까지 그 회사에서 고용한 총 직원 수를 각각 구하라.

• SUM, GROUP BY

• SELECT companyName, SUM(numHired)
  FROM Company, EmployeesHired
  WHERE Company.id = EmployeesHired.id
  GROUP BY companyName;
  

• 위 SQL문의 문제?

  • 내부 결합이 처리됨-> EmployeesHired에 행이 있는 회사의 이름만 리턴된다
    • ex) John Lawn Company는 결과에 포함되지 않는다.

• Left Outer Join

• SELECT companyName, SUM(numHired)
  FROM Company LEFT OUTER JOIN EmployeesHired
  			ON Company.id = EmployeesHired.id
  GROUP BY companyName;
  

• 아직도 문제가 있다

  • 외부 결합 정의상 EmployeesHired에 행이 없는 회사의 numHired는 NULL이다.
  • SUM(NULL)=NULL이므로 질의에서 그 회사에서 고용한 사람 수를 NULL로 리턴한다.

• NULL을 전부 0으로 바꾸는 SQL함수 적용

• SELECT companyName, COALESCE(SUM(numHired),0)
  FROM Company, EmployeesHired
  WHERE Company.id = EmployeesHired.id
  GROUP BY companyName;
  

1. 다음과 같은 SQL 데이터베이스 스키마가 있다고 할 때

   Test(
   	num INTEGER(4)
   );
   

   집단함수 (MAX, MIN 등)를 쓰지 않고 num의 최댓값을 반환하는 SQL문을 작성하라.

• 샘플 데이터

  num
  5
  23
  -6
  7

• 샘플데이터 기준으로는 23을 출력해야 한다.

  • 가장 큰 수 == 자기보다 큰 값이 하나도 없는 수

• 자기보다 큰 수를 가지는 수를 찾는 질의

  • SELF JOIN

• SELECT Lesser.num, Greater.num
  FROM Test AS Greater, Test AS Lesser
  WHERE Lesser.num<Greater.num
  

• 위의 질의를 실행시킨 결과

• Lesser열에는 23을 제외한 모든 값이 들어있다 -> 위 질의를 NOT IN의 하위질의로 사용

• SELECT num FROM Test WHERE num NOT IN
  (SELECT Lesser.num FROM Test AS Greater, Test AS Lesser
  WHERE Lesser.num<Greater.num);
  

• 위의 질의에는 문제가 있다

  • 최댓값이 여러 번 등장한 경우 두개 이상의 같은 값이 반환된다

• DISTINCT

• SELECT DISTINCT num FROM Test WHERE num NOT IN
  (SELECT Lesser.num FROM Test AS Greater, Test AS Lesser
  WHERE Lesser.num<Greater.num);
  

1. 다음과 같은 테이블이 주어졌을 때,

   Address(
   	street CHAR(30) NOT NULL,
       apartment CHAR(10),
       city CHAR(40) NOT NULL,
   );
   

   아파트가 아닌 주소만 반환하는 SQL문을 작성하라.

• SELECT * FROM Address WHERE apartment = null;
  

• 위의 질의에는 문제가 있다

  • 아무 주소도 반환되지 않는다.
  • SQL의 삼진 논리 때문이다.

• SQL에서의 논리 연산에서는 두 개가 아닌 세 개의 값이 나올 수 있다

  • TRUE/ FALSE/ UNKNOWN(값이 알려져 있지 않거나 표현이 불가능하다)

• 대부분의 데이터베이스에서는 NULL과 값 비교(동치 연산자 = 사용)를 하면 UNKNOWN을 반호나한다.

  • NULL은 값이 빠졌거나 알려지지 않은 데이터를 나타낸다->NULL과 NULL을 비교해도 UNKNOWN

• 질의에서는 WHERE절이 TRUE인 행만 리턴한다.

  • 따라서 위의 질의에서 WHERE절이 UNKNOWN 값이 들어있으므로 아무 행도 리턴되지 않는다.

• IS NULL / IS NOT NULL

• SELECT * FROM Address WHERE apartment IS NULL;
  

• WHERE절에서 UNKNOWN 값을 고려해야 하는 문제를 많이 낸다(특히 NULL값이 끼어들면 문제가 까다롭다)

  • SELECT * FROM Address WHERE apartment <> 1;
    

  • 위의 질의는 apartment = 1이 아미면서 NULL이 아닌 행을 리턴함에 주의

출처

프로그래밍 면접 이렇게 준비한다(한빛미디어)