오라클로 배우는 데이터베이스 개론과 실습 4장 연습문제 해설

오라클로 배우는 데이터베이스 개론과 실습 4장 연습문제 해설

 

오라클로 배우는 데이터베이스 개론과 실습 4장 연습문제 해설

 

1. 다음 내장함수의 결과를 적으시오.

select abs(-15) from dual;

: 15

select ceil(15.7) from dual;

: 16

select cos(3.14159) from dual;

: -1

select floor(15.7) from dual;

: 15

select log(10,100) from dual;

: 2

select mod(11,4) from dual;

: 3

select power(3,2) from dual;

: 9

select round(15.7) from dual;

: 16

select sign(-15) from dual;

: -1

select trunc(15.7) from dual;

: 15 (trunc : 소수점 버림

select chr(67) from dual;

: C

select concat('HAPPY','Birthday') from dual;

: HAPPYBirthday

select lower('Birthday') from dual;

: birthday

select lpad('Page 1', 15, '*.') from dual;

: *.*.*.*.*Page 1

select ltrim('Page 1','ae') from dual;

: Page 1

select replace('JACK', 'J', 'BL') from dual;

: BLACK

select rpad('Page 1', 15, '*.') from dual;

: Page 1*.*.*.*.*

select rtrim('Page 1', 'ae') from dual;

: Page 1

select substr('ABCDEFG',3,4) from dual;

: CDEF

select trim(LEADING 0 FROM '00AA00') from dual;

: AA00

select upper('Birthday') from dual;

: BIRTHDAY

select ascii('A') from dual;

: 65

select instr('CORPORATE FLOOR','OR',3,2) from dual;

: 14

select length('Birthday') from dual;

: 8

select add_months(TO_DATE('14/05/21', 'yy/mm/dd'),1) from dual;

: 21-JUN-14

select last_day(sysdate) from dual;

: 30-APR-20

select next_day(sysdate, 'Thu') from dual;

: 30-APR-20

select round(sysdate) from dual;

: 28-APR-20

select sysdate from dual;

: 27-APR-20

select to_char(sysdate) from dual;

: 27-APR-20

select to_char(123) from dual;

: 123

select to_date('12 05 2014', 'DD MM YYYY') from dual;

: 12-MAY-14

select to_number('12.3') from dual;

: 12.3

select decode(1,1,'aa','bb') from dual;

: aa

select nullif(123,345) from dual;

: 123

select nvl(null, 123) from dual;

: 123

 

2. Mybook 테이블을 생성하고 NULL에 관한 다음 SQL 문에 답하시오. 질의의 결과를 보면서 NULL에 대한 개념을 정리해보시오.

(Mybook 테이블)

bookid	price
1	10000
2	20000
3	NULL

2-(1). select * from Mybook;

 

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

 

2-(3). select * from Mybook where price IS NULL;

 

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

 

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

 

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

 

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

 

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

 

3. ROWNUM에 관한 다음 SQL 문에 답하시오. 데이터는 마당서점 데이터베이스를 이용한다.

3-(1). select * from Book;

 

3-(2). select * from Book where rownum<=5;

 

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

 

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

 

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

 

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