SQL_3일차

23.03.13 월 3일차

오늘은 이전에 배웠던 내용 JOIN과
select 구문을 이용한 insert, update, delete를
확인해보았다.
새로운 INDEX 구문을 배웠으나 아직은 많이 서툴고 어려운것 같다.
다시한번 확인해보자.

연습문제

문제1. idx에 p.k 를 부여하여 school을 만드시오.

create table school(
    idx varchar2(3),
    s_name nvarchar2(10),
    age varchar2(2),
    tel nvarchar2(13),
    constraint school_idx_pk primary key(idx)
);

다른 표현 2가지

create table school2(
    idx varchar2(5) primary key,
    s_name nvarchar2(10),
--numeric, number = 숫자 실수형을 알려주는 명령어
    age numeric(3), 
    tel char(13)
    );

create table school3(
    idx varchar2(5),
    s_name nvarchar2(10),
--numeric, number = 숫자 실수형을 알려주는 명령어
    age numeric(3), -
    tel char(13),
    primary key(idx)
);

문제2. 레코드를 추가하시오.

insert into school(idx,s_name,age,tel)
values('101', '둘리', '12', '010-5037-6133');


insert into school(idx,s_name,age,tel)
values('102', '하니', '15', '011-1234-1212');

insert into school(idx,s_name,age,tel)
ues('104', '영심이', '17', '017-5555-0341');

insert into school(idx,s_name,age,tel)
values('105', 'young won', '20', '010-5555-6363');

insert into school(idx,s_name,age,tel)
values('107', '대한민국', '22', '019-3245-1234');

insert into school(idx,s_name,age,tel)
values('109', '아름이', '25', '070-1212-5555');

문제3. 이름을 기준으로 내림차순 정렬하시오.

select * from school
order by s_name desc;

문제4. 번호가 101, 104, 107 인 학생의 레코드를 출력하시오.

select * from school
where idx = '101' or  idx = '104' or  idx = '107';

다른표현

select * from school
where idx in ('101', '104', '107');

문제5. 105번의 이름을 영원으로 변경하시오.

update school set s_name='영원'
where idx='105';

문제6. 104번의 나이를 15살로 수정하시오.

update school set age='15'
where idx='104';

문제7. 이름이 아름이인 학생의 전화번호를 삭제하시오.

update school set tel=' '  
--공백인지 NULL 값인지 문의 필요
where s_name='아름이';

문제8. 전체 학생의 나이를 19살로 수정하시오.

update school set age='19';

문제9. 102번 학생을 삭제하시오.

delete from school
where idx='102';

문제10. 전체 레코드를 삭제하시오.

delete from school; 
--레코드 삭제는 Delete

문제11. 전체 테이블을 삭제하시오.

drop table school;

연습문제(2)

1. 3학년 이상 학생의 학년과 이름을 출력하시오.

select sname,year from student
where year >= 3;

2. 2학년에서 3학년 사이의 학생의 학년과 이름을 출력하시오.

select sname,year from student
where year between '2' and '3';

다른표현

select sname,year from student
where year='2' or year='3';

다른표현

select sname,year from student
where year in ('2', '3');

3. 200번 학생의 이름, 학년, 학년+1 을 출력하시오.

-- 연산이 포함된 칼럼명은 별칭을 선언하는게 좋다
select sname, year, year +1 as 학년2 
from student
where sno='200';

4. 이름에 "찬" 또는 "둘" 이 들어 있는 학생의 이름과 학년을 출력하시오.

select sname, year from student
where sname like '%찬%' or  sname like '%둘%';
-- 두번째 자리에 글자 찾을땐
-- '%_글자%' 같이 언더바=_ 를 활용

5. 이름을 기준으로 내림차순 정렬 하시오.

select * from student
order by sname desc;

연습문제(3)

-- 문제 풀기전 사전 확인

select * from student;
select * from enrol;
select * from course;

alter table student
rename column sno to ssno;

alter table enrol

rename column sno to esno;

1. 학생 테이블과 등록 테이블을 조인하여 학번, 이름 , 중간성적, 기말성적을 나타내시오.

select ssno, sname, midterm, final 
from student s join enrol e
on ssno=esno;

2. 학생 테이블과 등록 테이블을 조인하여 학번, 이름 , 성적 을 나타내시오. (출력- 이름기준 내림차순 정렬 )

select ssno, sname, grade
from student s join enrol e
on s.ssno = e.esno
order by sname desc;

3. 과목 테이블과 등록 테이블을 조인하세요.!!

select * from course c join enrol e
on c.cno = e.cno;

4. enrol 테이블에서 중간고사 성적이 81점 ~ 90점 사이인 레코드를 출력하시오.

select *  from enrol
where midterm between 81 and  90;

5. enrol 테이블을 이용하여 중간고사 성적과 중간고사+3점한 레코드를 출력하시오.

select midterm as "중간고사", midterm+3 as "중간고사추가" 
from enrol;

6. enrol 테이블에서 400번 학생의 레코드를 출력하시오.

select *  from enrol
where esno='400';

7. 학번, 이름, 학년, 수강과목명, 평점을 출력하시오. (세개의 테이블을 모두 조인)

select ssno, sname , year, cname, grade from 
student s join enrol e 
on s.ssno=e.esno join course c 
on e.cno=c.cno;

8. 중복제거

select distinct ssno, sname  from 
student s join enrol e 
on s.ssno=e.esno join course c 
on e.cno=c.cno;

다른표현

select ssno, sname  from 
student s join enrol e 
on s.ssno=e.esno join course c 
on e.cno=c.cno
group by ssno, sname;

VIEW

-- VIEW 만들기

Create  View  V_BIGEMPLOYEES

as

select  *  from  BIGEMPLOYEES

where EMP_NO <=11000

order  by EMP_NO  desc;

-- 만들어진 VIEW 테이블 확인하기

select  count(*) "학생수 " from V_BIGEMPLOYEES;

-- VIEW 만들기

Create  view  v_SE_JOIN
as
select  s.sno ssno  , sname, year, dept, 
e.sno esno, cno, grade, MIDTERM, final 
from student s full join  enrol e
on s.sno=e.sno;

-- 만들어진 VIEW 테이블 확인하기

select  *  from  v_SE_JOIN;

-- 학생 테이블의 학번이 '700'인 학생의 학년을 '3'으로 수정하기

update  student  set year=3
where sno='700';

--2개 이상 join이 된 뷰테이블 v_SE_JOIN의
학번이 '700'인 학생의 학년은 '4'로 변경하여 출력

update v_SE_JOIN set year='4'
where ssno='700';  -- 수정 불가 

--2개 이상 join이 된 뷰테이블 v_SE_JOIN의
학번이 '700'인 학생의 레코드를 삭제

delete from v_SE_JOIN
where ssno='700'; -- 삭제 불가 

2개 이상의 조인테이블을 이용하여
만든 view 테이블은 수정, 삭제 할 수 없다.

-- JOIN이 된 VIEW 테이블을 사용하여 새로운 VIEW 테이블을 만들어라.

Create view v_v_SE_JOIN
as 
SELECT ssno, sname, midterm , final
from  v_SE_JOIN;

-- v_v_SE_JOIN 뷰테이블의 학생이름이 NULL값인 경우를 제외하여 출력

Create view V_v_v_SE_JOIN
as
select  *  from  v_v_SE_JOIN
where sname is not  null;

-- 만들어진 View 테이블 확인

select  *  from  V_v_v_SE_JOIN;

-- 테이블 복사하기

Create table new_BIGEMPLOYEES
as
select * from  BIGEMPLOYEES
where 1=2;

-- 새로운 테이블 만들고 복사하여 값 넣기

Create table new_BIGEMPLOYEES
as
select * from  BIGEMPLOYEES
where EMP_NO <=11000
order  by EMP_NO  desc;

-- 기존 테이블에 레코드 추가하기

insert into new_BIGEMPLOYEES
select *  from  BIGEMPLOYEES
where EMP_NO <=12000
order  by EMP_NO  desc;

-- GROUP BY(동일한 레코드 중복제거하여 그룹화)

select  ssno, sname, sum(MIDTERM) , sum(FINAL) 
from  V_v_v_SE_JOIN
where MIDTERM is not null and FINAL is not null 
group  by ssno, sname;

-- having을 이용한 그룹화

select  ssno, sname, sum(MIDTERM) , sum(FINAL) 
from  V_v_v_SE_JOIN
group  by ssno, sname
having  sum(MIDTERM) is not null and 
sum(FINAL) is not null
order  by  sum(MIDTERM) desc , sum(FINAL) desc;

-- 테이블 컬럼 추가 , 수정, 삭제
-- 추가

alter  table  student
add ( age number(3,1) DEFAULT '11' );

-- 수정

desc  student;
--내림차순 정렬

alter  table  student
MODIFY (sno varchar2(4));

-- 삭제

alter  table  student
drop  column age;

ALTER

-- 칼럼 이름 변경하기

alter table student 
rename COLUMN sname to name2 ; 

alter table student 
rename COLUMN name2 to sname ; 

스토어드 프로시저

단점 : 하나의 레코드만 생성가능

-- INSERT 적용하기

CREATE PROCEDURE MYPROC_INSERT1
(
	SNO varchar2 ,
	SNAME nvarchar2 ,
	DEPT nvarchar2,
	YEAR char
)
IS
BEGIN
INSERT INTO STUDENT(SNO, SNAME,DEPT,YEAR) 
VALUES(SNO, SNAME ,DEPT ,YEAR );

END;

-- 프로시저 호출 삽입

EXECUTE MYPROC_INSERT1('705','지효','전산','4');
EXECUTE MYPROC_INSERT1('706','지솔','물리','3');
EXECUTE MYPROC_INSERT1('707','지윤','화학','2');
EXECUTE MYPROC_INSERT1('708','지민','전산','1');

-- 추가 확인

select * from STUDENT;

-- DELETE 사용하기

CREATE or replace PROCEDURE ps_DelStudent
( 
	mID char
)
IS
BEGIN
delete from student where sno=mID;
END;

select * from student;

-- 프로시저 호출 삭제

execute ps_DelStudent('705'); 
select * from student;

-- UPDATE 사용하기

CREATE or replace PROCEDURE ps_UpdateStudent
(
	mSno char,
	mSname nchar,
	mYear char
)
IS
BEGIN
update student set sname=mSname , year=mYear
where sno=mSno ;
END;
select * from student;

-- 프로시저 호출 수정

execute ps_UpdateStudent('706','둘리','7' ); 
select * from student where sno='706';

-- DBMS_OUTPUT
-- DBMS로 출력 가능하게 on 설정
set serveroutput on;

-- DBMS로 출력

create or replace PROCEDURE select_student1
as
var1 int;
var2 int;
begin
    select count(*) into var1 from student;
    select count(*) as "컴퓨터학과 학생"  into var2 
    from student where dept='컴퓨터';
    DBMS_OUTPUT.PUT_LINE(var1 ||  ','  ||  var2);
    -- 문자열 더하기는 ||  사용 가능 or와는 다른것 + 표시
end;

execute select_student1;

-- DBMS로 출력

create or replace PROCEDURE select_student2
as
var1 VARCHAR2(4);   
var2 NVARCHAR2(10); 
var3 VARCHAR2(10);
var4 NVARCHAR2(10);
begin
    select ssno, sname, year, dept 
    into var1, var2, var3, var4 
    from student where ssno='100';
    DBMS_OUTPUT.PUT_LINE
(var1 || ',' || var2 || ',' || var3 || ',' || var4);
-- 문자열 더하기는 || 연산기호 사용 가능
end;

execute select_student2;

오늘은 스토어드 프로시저의 형식에 대해서 중점으로 배웠다.
스토어드 프로시저는 Oracle에서 제공되는 프로그래밍 기능으로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용하는 기능이다.
새로배운 내용이니 좀더 확실하게 형식과 동작을 위한 세부 내용을 인지하도록 해야겠다.