[210530일] 오라클 데이터베이스 sql A분반 연습문제

5번)

 

<table member 생성>

create table member

(

id varchar2(20) primary key ,

name varchar2(20) not null ,

password varchar2(20) not null );

 

 

<table board 생성>

create table board

(

boardnum number(11) primary key ,

userid varchar2(20) not null ,

title varchar2(100) ,

text varchar2(2000) ,

hits number(11) default 0 ,

inputdate date default sysdate );

 

create sequence board_seq;

 

 

<table reply 생성>

create table reply

(

replynum number(11) primary key ,

boardnum number(11) constraint reply_fk references board(boardnum) on delete cascade ,

id varchar2(20) not null ,

text varchar2(200) ,

inputdate date default sysdate );

 

create sequence reply_seq;

 

 

6번)

 

insert into member
values
('AAA','홍길동','1234');

insert into member
values
('BBB','박찬호','5678');

insert into member
values
('CCC','김철수','9999');

select * from member
order by userid;

 

 

 

 

7번)

insert into board
(boardnum, userid, title, text)
values
(board_seq.nextval,'AAA','글제목','글내용11');

insert into board
(boardnum, userid, title, text)
values
(board_seq.nextval,'BBB','글제목2','글내용22');

insert into board
(boardnum, userid, title, text)
values
(board_seq.nextval,'BBB','글제목3','글내용33');

insert into board
(boardnum, userid, title, text)
values
(board_seq.nextval,'CCC','글제목','글내용44');

select * from board
order by 1;

 

 

 

8번)

insert into reply
(replynum, boardnum, id, text)
values
(reply_seq.nextval, 1, 'AAA', '리플내용11'); 

insert into reply
(replynum, boardnum, id, text)
values
(reply_seq.nextval, 1, 'BBB', '리플내용22'); 

insert into reply
(replynum, boardnum, id, text)
values
(reply_seq.nextval, 1, 'AAA', '리플내용33'); 

insert into reply
(replynum, boardnum, id, text)
values
(reply_seq.nextval, 2, 'AAA', '리플내용44'); 

insert into reply
(replynum, boardnum, id, text)
values
(reply_seq.nextval, 2, 'BBB', '리플내용5'); 

select * from reply
order by replynum;

 

 

 

<조인문제>

1번)

<table board와 table member 조인>

 

select

    b.boardnum as 글번호 , b.id || '(' || m.name || ')' as 작성자 ,b.title as 제목

from board b , member m

where b.id = m.id

order by b.boardnum desc;

 

 

2번)

<table board, table member, table reply 조인>

select

    b.boardnum ,r.id ,m.name , r.text

from board b, member m, reply r

where

    b.boardnum = r.boardnum

and r.id = m.id

and b.boardnum = 1;

 

 

3번)

 

select

    b.boardnum as 본문글번호 ,m.name as 본문글작성자 ,r.replynum as 리플번호 ,m2.name as 리플작성자

from

    board b, reply r , member m , member m2

where m.id = b.id and b.boardnum = r.boardnum and r.id = m2.id

order by b.boardnum;

 

 

4번)

select

    boardnum as 본문글번호 ,count(*) as 리플개수

from reply

group by boardnum;