[6주차] 38. 마지막 핵심 종합 Test(지금까지 공부한 것을 점검)

* 아래서 테이블의 출력결과에는 오타 있는게 좀 있어서 살짝 다르게 나올수도 있음

drop table albums;

create table albums(
	num number primary key,
	song varchar2(30) not null,
	singer varchar2(30) not null,
	company varchar2(20) not null,
	price number  default 0
);

drop sequence albumseq;

--sequence  albumseq 생성 : 1~100까지 증가
create sequence albumseq
increment by 1
start with 1
minvalue 1
maxvalue 100;

-- 아래의 형태로 3번의 insert 하기
insert into albums values(1, '바운스', '조용필', '다음', 5000);
insert into albums values(2, '미스코리아', '이효리', '네이버', default);
insert into albums values(3, 'badgirl', '이효리', '네이버', 3000);

col song for a15
col singer for a15
col company for a15

select * from albums;
     NUM SONG       SINGER     COMPANY         PRICE
-------- ---------- ---------- ---------- ----------
       1 바운스     조용필     다음             5000
       2 미스코리아 이효리     네이버              0
       3 badgirl    이효리     네이버           3000


위의 select 결과 확인 후 아래 문제 풀기


[테이블의 컬럼 추가]
0. pub_day : 발매일 default 오늘날짜
SQL> alter table albums add(pub_day date default sysdate);


pub_day 추가한 후에 
select * from albums; 

-- 아래의 형태로 추가로 6번의 insert 하기
insert into albums values(4, '허공', '조용필', '네이버', 3000, '14_03_26');
insert into albums values(5, '허공', '조용필', '네이버', 3000, '11/02/26');
insert into albums values(6, 'hello', '조용필', '구글', 0, '13/07/01');
insert into albums values(7, '좋은 날', '아이유', '다음', 2000, '06/12/03');
insert into albums values(8, '마시멜로', '아이유', '네이버', 4000, '01/04/07');
insert into albums values(9, '단발머리', '조용필', '다음', 6000, '18/03/30');

 NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
---- ---------- ---------- ---------- ---------- --------
   1 바운스     조용필     다음             5000 18/03/30
   2 미스코리아 이효리     네이버              0 18/03/30
   3 badgirl    이효리     네이버           3000 18/03/30
   4 허공       조용필     네이버           3000 14/03/26
   5 허공       조용필     네이버           3000 11/02/26
   6 hello      조용필     구글                0 13/07/01
   7 좋은 날    아이유     다음             2000 06/12/03
   8 마시멜로   아이유     네이버           4000 01/04/07
   9 단발머리   조용필     다음             6000 18/03/30


commit;

1. company가 네이버인 행 출력
SQL> select * from albums where company = '네이버';

      NUM SONG       SINGER     COMPANY    PUB_DAY
-------- ---------- ---------- ---------- --------
       4 허공       조용필     네이버     11/02/26
       2 미스코리아 이효리     네이버     14/07/02
       3 badgirl    이효리     네이버     14/07/02
       7 마시멜로   아이유     네이버     01/04/07

2. 발행일이 2013-5-5 이후인 앨범의 num,song,PUB_DAY 출력
SQL> select num, song, pub_day from albums where pub_day > to_date('2013-5-5');
 
  NUM SONG       PUB_DAY
---- ---------- --------
   1 바운스     18/03/30
   2 미스코리아 18/03/30
   3 badgirl    18/03/30
   4 허공       14/03/26
   6 hello      13/07/01
   9 단발머리   18/03/30

3. singer를 가수로, company를 회사명으로 price를 가격 별칭으로 출력하기
(출력하기 전에 가수의 출력 폭을 10칸으로, 회사명의 출력 폭을 10칸으로 지정하시오.)

SQL> col 가수 for a10
SQL> col 회사명 for a10
SQL> select singer 가수, company 회사명, price 가격 from albums;

가수       회사명           가격
---------- ---------- ----------
조용필     다음             5000
이효리     네이버              0
이효리     네이버           3000
조용필     네이버           3000
조용필     네이버           3000
조용필     구글                0
아이유     다음             2000
아이유     네이버           4000
조용필     다음             6000

4. 조용필 행의 singer를 가수로, company를 회사명으로 price를 가격으로 출력하기
SQL> select  singer 가수, company 회사명, price 가격 from albums where singer = '조용필';

가수       회사명           가격
---------- ---------- ----------
조용필     다음             5000
조용필     네이버           3000
조용필     네이버           3000
조용필     구글                0
조용필     다음             6000

5. 모든 회사를 야후로 변경하고 select로 변경사항 확인한 후  rollback하시오.
SQL> update albums set company = '야후';
SQL> select  * from albums;

       NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
---------- ---------- ---------- ---------- ---------- --------
         5 hello      조용필     야후                0 13/07/01
         6 좋은 날    아이유     야후             2000 06/12/03
         7 마시멜로   아이유     야후             4000 01/04/07
         8 단발머리   조용필     야후             6000 14/07/02
         1 바운스     조용필     야후             5000 14/07/02
         2 미스코리아 이효리     야후                0 14/07/02
         3 badgirl    이효리     야후             3000 14/07/02
         4 허공       조용필     야후             3000 11/02/26

rollback;

6. 이름에 '이' 포함된 사람 모두 출력
SQL> select * from albums where singer like '%이%';

       NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
---------- ---------- ---------- ---------- ---------- --------
         6 좋은 날    아이유     다음             2000 06/12/03
         7 마시멜로   아이유     네이버           4000 01/04/07
         2 미스코리아 이효리     네이버              0 14/07/02
         3 badgirl    이효리     네이버           3000 14/07/02

7. singer가 조용필이면서 company가 다음인 행 삭제하고 select로 결과확인 후 rollback하시오.
SQL> delete from albums where singer = '조용필' and company = '다음';
SQL> select * from albums;

NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
--- ---------- ---------- ---------- ---------- --------
  2 미스코리아 이효리     네이버              0 18/03/30
  3 badgirl    이효리     네이버           3000 18/03/30
  4 허공       조용필     네이버           3000 14/03/26
  5 허공       조용필     네이버           3000 11/02/26
  6 hello      조용필     구글                0 13/07/01
  7 좋은 날    아이유     다음             2000 06/12/03
  8 마시멜로   아이유     네이버           4000 01/04/07

rollback;

8. 현재 레코드 갯수 구해서 cnt별칭으로 출력하기
SQL> select count(*) cnt from albums;

       CNT
----------
         9

9. singer가 조용필 이거나 이효리인 행 모두 출력(in, or 2가지 방법사용)
SQL> select * from albums where singer = '조용필' or singer = '이효리';
SQL> select * from albums where singer in('조용필', '이효리');

        NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
---------- ---------- ---------- ---------- ---------- --------
         5 hello      조용필     구글                0 13/07/01
         8 단발머리   조용필     다음             6000 14/07/02
         1 바운스     조용필     다음             5000 14/07/02
         2 미스코리아 이효리     네이버              0 14/07/02
         3 badgirl    이효리     네이버           3000 14/07/02
         4 허공       조용필     네이버           3000 11/02/26

10. singer를 기준으로 내림차순 정렬하고 같은 이름일 때에는 가격을 오름차순으로 아래와 같은 결과가 나오도록 정렬하시오.
SQL>  select * from albums order by singer desc, price asc;
 
       NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
 --------- ---------- ---------- ---------- ---------- --------
        5 hello      조용필     구글                0 13/07/01
        4 허공       조용필     네이버           3000 11/02/26
        1 바운스     조용필     다음             5000 14/07/02
        8 단발머리   조용필     다음             6000 14/07/02
        2 미스코리아 이효리     네이버              0 14/07/02
        3 badgirl    이효리     네이버           3000 14/07/02
        6 좋은 날    아이유     다음             2000 06/12/03
        7 마시멜로   아이유     네이버           4000 01/04/07

11. 조용필의 앨범 가격중 최고 값은? 
SQL> select max(price) 최고값 from albums where singer = '조용필';

    최고값
----------
      6000

12. 가격을 내림차순으로 정렬하여 등수를 정한 후, 3~5등 사이의 행을 아래와 같이 2가지 방법으로 출력하시오.
SQL> select * from albums order by price desc;
      NUM SONG       SINGER     COMPANY         PRICE PUB_DAY
---------- ---------- ---------- ---------- ---------- --------
         9 단발머리   조용필     다음             6000 16/09/05
         1 바운스     조용필     다음             5000 16/09/05
         8 마시멜로   아이유     네이버           4000 01/04/07
         4 허공       조용필     네이버           3000 14/03/26
         3 badgirl    이효리     네이버           3000 16/09/05
         5 허공       조용필     네이버           3000 11/02/26
         7 좋은 날    아이유     다음             2000 06/12/03
         2 미스코리아 이효리     네이버              0 16/09/05
         6 hello      조용필     구글                0 13/07/01

SQL> 
select rownum, num, song, singer, price, rank
from(select rownum, num, song, singer, price, rank() over(order by price desc) rank
from albums)
where rank >= 3 and rank <= 5 ;

   ROWNUM  NUM SONG    SINGER      PRICE       RANK
--------- ---- ------ --------- -------- --------
        1          8 마시멜로   아이유           4000          3
        2          3 badgirl    이효리           3000          4
        3          5 허공       조용필           3000          4
        4          4 허공       조용필           3000          4

SQL> 
select rownum, num, song, singer, price, rank_val
from(select rownum, num, song, singer, price, dense_rank() over(order by price desc) rank_val
from albums)
where rank_val >= 3 and rank_val <= 5 ;

    ROWNUM        NUM SONG       SINGER          PRICE   RANK_VAL
---------- ---------- ---------- ---------- ---------- ----------
         1          8 마시멜로   아이유           4000          3
         2          3 badgirl    이효리           3000          4
         3          4 허공       조용필           3000          4
         4          5 허공       조용필           3000          4
         5          7 좋은 날    아이유           2000          5

13. '네이버'소속의 앨범 가격 평균?
SQL> select avg(price) 가격평균 from albums where company = '네이버' group by company ;

 가격 평균
----------
      2500

14. 전체 가격 평균이상인 레코드의 수는?
SQL> 
select count(*)
from albums
where price >= (select avg(price) 가격평균 from albums);

  COUNT(*)
----------
         5

15. 회사 이름을 중복되지 않게 출력하시오
SQL> select distinct company  from albums;
혹은 select company from albums group by company;

COMPANY
----------
다음
구글
네이버

16. num 칼럼의 이름을 no로 변경하기
SQL> alter table albums rename column num to no;

17. singer의 조용필을 이문세로 수정하고 확인한후 rollback하시오.
SQL> update albums set singer = '이문세' where singer = '조용필';

SQL> select * from albums;

        NO SONG       SINGER     COMPANY         PRICE PUB_DAY
---------- ---------- ---------- ---------- ---------- --------
         1 바운스     이문세     다음             5000 14/01/26
         2 미스코리아 이효리     네이버              0 14/01/26
         3 badgirl    이효리     네이버           3000 12/01/06
         4 허공       이문세     네이버           3000 14/03/26
         5 허공       이문세     네이버           3000 11/02/26
         6 hello      이문세     구글                0 13/07/01
         7 좋은 날    아이유     다음             2000 06/12/03
         9 단발머리   이문세     다음             6000 14/10/17

18. 회사별 가격 평균을 아래와 같이 회사명을 기준으로 오름차순 정렬하여 출력하시오.
SQL> select company, avg(price) 가격평균 from albums group by company order by avg(price) asc;

COMPANY     가격 평균
---------- ----------
구글                0
네이버           2250
다음       4333.33333


19. 회사별 가격 평균이 3000이상인 회사명과 가격 평균을 아래와 같이 출력하시오.
SQL> select company, avg(price) 가격평균 from albums group by company having avg(price) >= 3000 order by avg(price) asc;

COMPANY     가격 평균
---------- ----------
다음       4333.33333

20-1.person 테이블 만들고, id칼럼은 시퀀스로 번호 넣기(sequnce이름:p_seq)

drop table person;

-- 테이블 생성
create table person(
	id number,
	name varchar(20),
	address varchar(20)
);

-- 시퀀스 생성
create sequence p_seq
increment by 1
start with 1
minvalue 1;

-- 시퀀스로 4줄 insert
insert into person values(p_seq.nextVal, '이효리', '서울');
insert into person values(p_seq.nextVal, '조용필', '부산');
insert into person values(p_seq.nextVal, '아이유', '대구');
insert into person values(p_seq.nextVal, '이승기', '인청');

select * from person;

        ID NAME            ADDRESS
------ ----------- -------------
         1 이효리               서울
         2 조용필               부산
         3 아이유               대구
         4 이승기               인천


20-2. albums테이블과 person테이블 조인해서 아래와 같이 조회하기
SQL> 
select singer 가수, song 노래명, address 주소
from albums a inner join person b
on a.singer = b.name;

혹은 

select singer 가수, song 노래명, address 주소
from albums a, person b
where a.singer = b.name;

혹은 
natural join을 해줄 수도 있지만 지금은 같은 이름을 가진 열이 존재하지 않아 위의 두 방법이 더 적절하다.
(natural join은 보통 where 절을 사용하지 않기 위해 사용하는 것이므로)
where 문에 조건을 주면 natural join도 가능하긴 함.

col 가수 for a15
col 노래명 for a15
col 주소 for a15

가수       노래명     주소
---------- ---------- --------------
조용필     바운스     부산
아이유     좋은 날    대구
아이유     마시멜로   대구
조용필     허공       부산
조용필     hello      부산
이효리     미스코리아 서울
이효리     badgirl    서울
조용필     단발머리   부산


21. pl sql문제
company가 naver인 행의 num,song,company 가져오기 
2가지 방식 사용 : 기본 for문, cursor사용해서 가져오기

-- 첫번째 방법(cursor 이용 없이 기본 for 루프만 이용한 방법)
begin
	for i in (select no, song, company  from albums where company = '네이버') loop
		dbms_output.put_line(i.no || ',' || i.song || ',' || i.company);
	end loop;
end;
/

-- 두번째 방법(cursor이용) 
declare
	onerow albums%rowtype;
	cursor c is 
		select no, song, company  from albums where company = '네이버';
begin
	for onerow in c loop
		dbms_output.put_line(onerow.no || ',' || onerow.song || ',' || onerow.company);
	end loop;
end;
/

2,미스코리아,네이버
3,badgirl,네이버
4,허공,네이버
5,허공,네이버
8,마시멜로,네이버

--
cursor를 이용하는 하나 더 방법이 있으나 커서를 열고 닫고 exit ~NOTFOUNT 해줘야 해서 번잡하니 생략

22. pl sql문제, 번호를 넘겨서 해당 번호의 가격이 5000이상이면 500원할인, 
3000~4999사이이면 300원 할인, 그 이하이면 100원할인된 가격을 받을 수 있는 discount 함수 만들기
3000인 가격에 대해서는 2700이 출력된다. 

create or replace function discount(vno number)
return number as
	vprice number;
	result number;
begin
	select price
	into vprice 
	from albums
	where no = vno;

	if vprice >= 5000 then
		result := vprice - 500;
	elsif vprice >= 3000 then
		result := vprice - 300;
	else 
		result := vprice - 100;
	end if;

	return result;
end;
/

select discount(1) from dual;

DISCOUNT(1)
-----------
       4500