[SQL응용 과제제출] 교재 연습문제 3장(1번, 2번) / 4장(1번, 2번, 3번)

[최한결]

3장 연습문제

-- 01번
-- 01)도서번호가 1인 도서의 이름.
SELECT bookname
FROM book
WHERE bookid = 1;

-- 02)가격이 20,000원 이상인 도서의 이름
SELECT bookname
FROM book
WHERE price >= 20000;

-- 03)박지성의 총 구매액
SELECT custid,sum(saleprice) AS 총구매액
FROM orders
WHERE custid = 1
GROUP BY custid;

-- 04)박지성이 구매한 도서의 수
SELECT custid,count(saleprice) AS 구매권수
FROM orders
WHERE custid = 1
GROUP BY custid

-- 02번
-- 01)마당서점 도서의 총수
SELECT count(*) AS 총도서수
FROM book;

-- 02)마당서점에 도서를 출고하는 출판사의 총수
SELECT COUNT(DISTINCT(publisher)) AS 출판사의수
FROM book;

-- 03)모든 고객의 이름, 주소
SELECT name, address
FROM customer;

-- 04)2020년 7월 4일 ~7월 7일 사이에 주문받은 도서의 주문번호
SELECT orderid
FROM ORDERS
WHERE ORDERDATE BETWEEN TO_DATE('2020-07-01','YYYY-MM-DD') 
AND TO_DATE('2020-07-07','YYYY-MM-DD');

-- 05)2020년 7월 4일 ~7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
SELECT orderid
FROM ORDERS
WHERE ORDERDATE > TO_DATE('2020-07-07','YYYY-MM-DD');

-- 06)성이 '김'씨인 고객의 이름과 주소
SELECT name,address
FROM customer
WHERE name Like '%김%';

-- 07)성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
SELECT name,address
FROM customer
WHERE name Like '김_아%';

---

4장 연습문제

-- 연습문제 1번 - 다음 내장 함수의 결과를 적으시오.

- 숫자의 절대값 계산

select ABS(-15) 

from dual;

- 실행 결과

- 숫자보다 크거나 같은 최소의 정수
select ceil(15.7)
from dual;

- 실행 결과

- 코사인값 계산
select cos(3.14159)
from dual;

- 실행 결과

- 숫자보다 작거나 같은 최소의 정수
select floor(15.7)
from dual;

- 실행 결과

- 숫자의 자연로그 값 반환
select log(10,100)
from dual;

- 실행 결과

- n1을 n2로 나눈 나머지 값을 반환
select mod(11,4)
from dual;

- 실행 결과

- 숫자의 n제곱 값 계산
select power(3,2)
from dual;

- 실행 결과

- 숫자가 음수이면 -1, 0이면 0, 양수이면 1
select sign(-15)
from dual;

- 실행 결과

- 지정한 소수점 이하의 값을 잘라낸다.(미지정시 소수점 첫번째 자리부터 절삭)
select trunc(15.7)
from dual;

- 실행 결과

- 정수 아스키코드를 문자로 반환

select chr(67)
from dual;

- 실행 결과

- 두 문자열을 연결
select concat('HAPPY', ' Birthday')
from dual;

- 실행 결과

- 대상 문자열을 모두 소문자로 반환
select lower('Birthday')
from dual;

- 실행 결과

- 대상 문자열의 왼쪽부터 지정한 자릿수까지 지정한 문자로 채움
select lpad('page 1',15,'*')
from dual;

- 실행 결과

- 대상 문자열의 왼쪽부터 지정한 문자들을 제거
select ltrim('page 1','ae')
from dual;

- 실행 결과

- 대상 문자열의 지정한 문자를 원하는 문자로 변경
select replace('jack','j','bl')
from dual;

- 실행 결과

- 대상 문자열의 오른쪽부터 지정한 자릿수까지 지정한 문자로 채움
select rpad('page 1',15,'*')
from dual;

- 실행 결과

- 대상 문자열의 오른쪽부터 지정한 문자들을 제거
select rtrim('page 1','ae')
from dual;

- 실행 결과

- 대상 문자열의 지정된 자리에서 지정된 길이만큼 잘라서 반환
select substr('ABCDEFG',3,4)
from dual;

- 실행 결과

- 대상 문자열의 양쪽에서 지정된 문자를 삭제(leading은 좌측만 삭제)
select trim(LEADING 0 FROM '00AA00')
from dual;

- 실행 결과

- 대상 문자열을 모두 대문자로 반환
select upper('Birthday')
from dual;

- 실행 결과

- 대상 알파벳 문자의 아스키코드 값을 반환
select ascii('A')
from dual;

- 실행 결과

- 문자열 중 n번째 문자부터 시작하여 찾고자 하는 문자열 s2가 k 번째 나타나는 문자. 열 위치 반환
select instr('CORPORATE FLOOR','OR',3,2)
from dual;

- 실행 결과

- 대상 문자열의 글자 수를 반환
select length('Birthday')
from dual;

- 실행 결과

- date 형의 날짜에서 지정한 달만큼 더함(1 : 다음달, -1 : 이전달)
select add_months('14/05/21',1)
from dual;

- 실행 결과

- date 형의 날짜에서 지정한 달의 마지막 날을 반환
select last_day(sysdate)
from dual;

- 실행 결과

- NEXT_DAY 함수는 해당일을 기준으로 명시된 요일의 다음 날짜를 변환
select next_day(sysdate,'화')
from dual;

- 실행 결과

- 지정한 단위(월, 주, 일)로 반올림
select round(sysdate)
from dual;

- 실행 결과

- 날짜형(DATE) 데이터를 문자형(CHAR)으로 반환
select to_char(sysdate)
from dual;

- 실행 결과

- 날짜형(DATE) 데이터를 문자형(CHAR)으로 반환
select to_char(123)
from dual;

- 실행 결과

- 문자형(CHAR) 데이터를 날짜형(DATE)으로 반환
select to_date('12 05 2020', 'DD MM YYYY')
from dual;

- 실행 결과

- 문자형(CHAR) 데이터를 숫자형(NUMBER)으로 반환
select to_number('12.3')
from dual;

- 실행 결과

- decode(컬럼명, 조건값1, 조건1이 true일때, 조건1이 false일때 ....)
select decode(1,1,'aa','bb')
from dual;

- 실행 결과

- nullif(A, B) - A와 B가 동일하면 null, 그렇지 않다면 A를 반환
select nullif(123,345)
from dual;

- 실행 결과

- nvl(null,값) - null이면 "값"으로 대치하여 반환
select nvl(null,123)
from dual;

- 실행 결과

---

-- 연습문제 2번 Mybook 테이블을 생성한 후 다음의 NULL에 대한 SQL문에 답하고 NULL에 대한 개념도 정리해 보시오.

-- (1)
select *
from Mybook;

- 실행 결과 / 정리 -> Mybook 테이블 전체 데이터 출력 

-- (2)
select bookid,nvl(price,0)
from Mybook;

- 실행 결과 / 정리 -> nvl함수로 인해  price컬럼의 null을 0으로 변경.

-- (3)
select *
from Mybook;
where price is null;

- 실행 결과 / 정리 -> 값이 null인 행 출력.

-- (4)
select *
from Mybook;
where price=;

- 실행 결과 / 정리 -> from절과 where절에 세미 콜론(;)이 모두 있어 from 절까지만 실행되어 (1)번과 같은 결과 출력.

                             (스크립트 출력 -> where 절의 조건이 없어 오류 발생)

-- (4)
select *
from Mybook;
where price=";

- 실행 결과 / 정리 -> "null+숫자" 연산의 결과는 항상 null이며, count 함수의 결과는 0이다. 

-- (5)
select bookid, price+100
from Mybook;

- 실행 결과 / 정리 -> "null+숫자" 연산의 결과는 항상 null이다.

-- (6)
select sun(price),avg(price),count(*)
from Mybook
where bookid >= 4;

- 실행 결과 / 정리 -> bookid가 4 이상 인 데이터가 없으므로 값이 null로 입력

-- (7)
select count(*),count(price)
from Mybook;

- 실행 결과 / 정리 -> count(*)의 형식은 null 데이터도 포함한 값을 반환하지만,

                             count(컬럼명)의 형식으로 사용하면 null 데이터를 제외한 값을 반환한다.

-- (8)
select sum(price),avg(price)
from Mybook;

- 실행 결과 / 정리 -> 집계 함수(count, sum, max, min, avg)를 사용할때 null이 포함된 행은 집계에서 빠진다.

---

-- 연습문제 3번 ROWNUM에 관한 다음 SQL 문에 답하시오.(MADANG 데이터 활용)

-- (1)
select *
from book;

- 실행 결과 -> book 테이블의 전체 자료를 조회

-- (2)
select *
from book
where rownum <= 5;

- 실행 결과 -> book 테이블의 모든 자료 중 5개만  조회(순서지정를 하지 않으면, 오라클이 저장해둔 순서로 적용)

-- (3)
select *
from book
where rownum <= 5
order by price;

- 실행 결과 -> book 테이블의 모든 자료에서 오라클이 저장해둔 순으로 5개까지만 조회하여,

                    price 컬럼을 기준으로 오름차순 정렬된 자료만 출력.

-- (4)
select *
from (select * from book order by price)b
where rownum <= 5;

- 실행 결과 -> book 테이블의 모든 자료 중 price 컬럼을 기준으로 오름차순 정렬된 자료를 5개까지만 조회.

-- (5)
select *
from (select * from book where rownum <= 5)b
order by price;

- 실행 결과 -> book 테이블에서 오라클이 저장해둔 순서대로 5개의 자료 선택하고,

                   해당 자료들을 price 컬럼을 기준으로 오름차순 정렬 및 출력

-- (6)
select *
from (select * from book where rownum <= 5
order by price)b;

- 실행 결과 -> book 테이블에서 오라클이 저장해둔 순서대로 5개의 자료 선택하고,

                   해당 자료들을 price 컬럼을 기준으로 오름차순 정렬 및 출력