데이터 베이스 ) SQL 연습하기

🌷 SQL 연습하기 문제 공유 

수업 중 이론적으로 배울 때는 그렇구나 ~~ 하면서 바로 습득이 되었었는데,

직접 써보니까 익숙해지는 데에 시간이 좀 걸리더라구요..

그래서 이론들을 써먹어볼 수 있는 문제들을 만들어봤습니다.

학교에서 데이터 베이스 중 SQL에 대한 공부를 할 때 중간고사를 준비하기 위한 시험지였는데, 

혼자 쓰긴 아까워서.. 공유 합니당..! 

---

 🌷 문제 풀어보는 방법 

아래와 같이 3개의 테이블이 있는 문제이고, DDL, DML, DCL를 구분해서 문제를 풀어볼 수 있게 나눠놨습니다.

크게 1️⃣ 초기화, 2️⃣ 문제,  3️⃣ 답으로 구분이 되어 있습니다.

문제를 풀기 전 1️⃣ 초기화 를 넣고 실행한 후, 2️⃣ 문제를 풀어보시면 되고, 다 푸신 이후 3️⃣ 답과 비교하시면 됩니다 ! 

---

 🌷 DML/ DCL 

▶️ 초기화 

drop table STUDENT;
drop table COURSE;
drop table ENROL;

create table STUDENT (
  sno number(3),
  sname nvarchar2(4),
  year number(1),
  dept nvarchar2(5)
);

▶️ 문제 

-- Q) STUDENT table에 100, '나수영', 4, '컴퓨터'인 학생 추가 

-- Q) STUDENT table에 sno 300, year 4 인 학생 추가 

-- Q) STUDENT table에 학번이 300인 학생의 이름을 '이가은'으로 변경하고, 학번은 한 학년 낮추시오.

-- Q) STUDENT table의 모든 행 삭제

-- Q) STUDENT table의 학번이 300인 학생 삭제 

-- Q) 이전 커밋 시점으로 이동. (결과 같이 어떨지 예측해보라.)

▶️  답

 

-- Q) STUDENT table에 100, '나수영', 4, '컴퓨터'인 학생 추가 
insert into student values (100, '나수영', 4, '컴퓨터');
-- Q) STUDENT table에 sno 300, year 4 인 학생 추가 
insert into student(sno, year) values (300, 4);
-- Q) STUDENT table에 학번이 300인 학생의 이름을 '이가은'으로 변경하고, 학번은 한 학년 낮추시오.
update student set sname = '이가은' , year = year - 1 where sno = 300;
-- Q) STUDENT table의 모든 행 삭제
delete student;
-- Q) STUDENT table의 학번이 300인 학생 삭제 
delete student where sno = 300;

-- Q) 이전 커밋 시점으로 이동. (결과 같이 어떨지 예측해보라.)
rollback; -- DCL

---

 🌷DDL 

▶️ 초기화 

drop table STUDENT;
drop table COURSE;
drop table ENROL;

-- COURSE
create table COURSE (
  cno char(4),
  cname nvarchar2(10) not null,
  credit number(1) not null,
  dept nvarchar2(4) not null,
  professor nvarchar2(4),
  primary key(cno),
  unique (cno,professor)
);

▶️ 문제 

-- Q) Student 테이블을 만드시오.
-- sno 숫자 3자리 (기본키)
-- sname 글자 최대 4, ( null 올 수 없음 -> 제약 조건 명 : sname_always_exists) 
-- year 숫자 1자리 (기본값은 1) (1~4의 값 -> 제약 조건 명 : year_check)
-- dept 글자 최대 5 
-- unique (dept, sname)


-- Q) Enrol 테이블을 만드시오.
-- sno 3 숫자 null 아님 / primary key / STUDENT의 sno에 외래키( 부모 튜플 삭제시 같이 삭제됨 )
-- cno 4고정 문자 null 아님 / primary key / COURSE 기본키에 외래키( 부모 튜플 삭제시 같이 삭제됨 )
-- grade 문자 // 'A', 'B', 'C', 'D', 'F' 중 하나
-- midterm, finterm 정수


-- Q) 모든 테이블, 시퀀스, 뷰 정보 검색

-- Q) 모든 테이블의 정보 검색

-- Q) 모든 칼럼의 정보 검색

-- Q) 모든 제약조건 정보 검색 

-- Q) student table의 sname_always_exists constraint 을 없애시오.

-- Q) student table의  year2 column을 없애시오.

-- Q) student table의 year 기본값을 2로 바꾸시오.

-- Q) student table의 dept2 기본값을 '컴퓨터'로 추가하시오.

-- Q) student 테이블에 잠시 AAA라는 constraint를 제거하고 재개

-- Q) STUDENT table을 삭제하시오.(관련된 외래키 정의 함께 삭제) // 이 의미도 설명하시오

▶️  답

-- Q) Student 테이블을 만드시오.
-- sno 숫자 3자리 (기본키)
-- sname 글자 최대 4, ( null 올 수 없음 -> 제약 조건 명 : sname_always_exists) 
-- year 숫자 1자리 (기본값은 1) (1~4의 값 -> 제약 조건 명 : year_check)
-- dept 글자 최대 5 
-- unique (dept, sname)
create table STUDENT (
    sno number(3) PRIMARY key,
    sname nvarchar2(4) constraint sname_always_exists not null,
    year number(1) constraint year_check check (year between 1 and 4),
    dept nvarchar2(5),
		unique (dept, sname) -- 둘 다 같은 게 있으면 안 됨
);

-- Q) Enrol 테이블을 만드시오.
-- sno 3 숫자 null 아님 / primary key / STUDENT의 sno에 외래키( 부모 튜플 삭제시 같이 삭제됨 )
-- cno 4고정 문자 null 아님 / primary key / COURSE 기본키에 외래키( 부모 튜플 삭제시 같이 삭제됨 )
-- grade 문자 // 'A', 'B', 'C', 'D', 'F' 중 하나
-- midterm, finterm 정수
  create table ENROL (
  sno number(3) not null,
  cno char(4) not null,
  grade char,
  midterm integer,
  finterm integer,
  primary key (sno, cno),
  foreign key (sno) references STUDENT(sno)
	on delete cascade,
  foreign key (cno) references COURSE
	on delete cascade,
  check (grade in ('A', 'B', 'C', 'D', 'F'))
  );

-- Q) 모든 테이블, 시퀀스, 뷰 정보 검색
select * from all_catalog where owner = 'DB202013245';
-- Q) 모든 테이블의 정보 검색
select * from all_tables where owner = 'DB202013245';
-- Q) 모든 칼럼의 정보 검색
select * from all_tab_columns where owner = 'DB202013245';
-- Q) 모든 제약조건 정보 검색 
select * from all_constraints where owner = 'DB202013245';

-- Q) student table의 sname_always_exists constraint 을 없애시오.
alter table student drop constraint sname_always_exists;
-- Q) student table의  year2 column을 없애시오.
alter table student drop column year2;
-- Q) student table의 year 기본값을 2로 바꾸시오.
alter table student modify year default 2;
-- Q) student table의 dept2 기본값을 '컴퓨터'로 추가하시오.
alter table student add dept2 nvarchar2(5) default '컴퓨터';
-- Q) student 테이블에 잠시 AAA라는 constraint를 제거하고 재개
alter table student disable constraint AAA;
alter table student enable constraint AAA;

-- Q) STUDENT table을 삭제하시오.(관련된 외래키 정의 함께 삭제) // 이 의미도 설명하시오
drop table student cascade constraints;
student를 피참조테이블(부모테이블)로 가지는 자식 테이블들의 외래키 설정 해지

---

 🌷 DML _ Select(basic) 

▶️ 초기화 

drop table STUDENT;
drop table COURSE;
drop table ENROL;

create table STUDENT (
  sno number(3),
  sname nvarchar2(4),
  year number(1),
  dept nvarchar2(5)
);

 insert into STUDENT values (100, '나수영', 4, '컴퓨터');
 insert into STUDENT values (200, '이찬수', 3, '전기');
 insert into STUDENT values (300, '정기태', 1, '컴퓨터');
 insert into STUDENT values (400, '송병길', 4, '컴퓨터');
 insert into STUDENT values (500, '박종화', 2, '산공');
  
create table COURSE (
  cno char(4),
  cname nvarchar2(10),
  credit number(1),
  dept nvarchar2(4),
  professor nvarchar2(4)
);

insert into COURSE values ('C123', 'C프로그래밍',  3, '컴퓨터', '김성국');
insert into COURSE values ('C312', '자료구조', 3,	'컴퓨터', '황수관');
insert into COURSE values ('C324', '화일구조', 3,	'컴퓨터', '이규찬');
insert into COURSE values ('C413', '데이터베이스', 3, '컴퓨터', '이일로');
insert into COURSE values ('E412', '반도체', 3, '전자', '홍봉진');
  
  
create table ENROL (
  sno number(3),
  cno char(4),
  grade char,
  midterm integer,
  finterm integer
);
  
insert into ENROL values (100, 'C413',	'A',	90,	95);
insert into ENROL values (100, 'E412',	'A',	95,	95);
insert into ENROL values (200, 'C123',	'B',	85,	80);
insert into ENROL values (300, 'C312',	'A',	90,	95);
insert into ENROL values (300, 'C324',	'C',	75,	75);
insert into ENROL values (300, 'C413',	'A',	95,	90);
insert into ENROL values (400, 'C312',	'A',	90,	95);
insert into ENROL values (400, 'C324',	'A',	95,	90);
insert into ENROL values (400, 'C413',	'B',	80,	85);
insert into ENROL values (400, 'E412',	'C',	65,	75);
insert into ENROL values (500, 'C312',	'B',	85,	80);

commit;

▶️ 문제 

-- Q) 학생 테이블에 기입된 학년 종류 ( year는 학년으로 출력 )

-- Q) C로 시작하는 강의 중 중간 성적이 80 ~ 90이면서, grade가 A 혹은 B 학생을 학번(내림차순), grade(오름차순) 정렬 

-- Q) 3명 이상 수강하는 강의별로 기말고사의 최저 점수를 구하라.

-- Q) 학과 별로 학번이 300 이상인 학생의 학년 평균을 구하라.

-- Q) grade가 'A' 또는 'B'인 강의들 중에 강의의 기말고사 최소가 90 이상인 강의의 중간, 기말 평균을 출력

▶️  답

-- Q) 학생 테이블에 기입된 학년 종류 ( year는 학년으로 출력 )
select distinct year as 학년 from student;

-- Q) C로 시작하는 강의 중 중간 성적이 80 ~ 90이면서, grade가 A 혹은 B 학생을 학번(내림차순), grade(오름차순) 정렬 
select * from enrol 
where cno like 'C%' and midterm between 80 and 90 and grade in ('A', 'B')
order by sno desc, grade asc;

-- Q) 3명 이상 수강하는 강의별로 기말고사의 최저 점수를 구하라.
select min(finterm) from enrol 
group by cno having count(*) >= 3;

-- Q) 학과 별로 학번이 300 이상인 학생의 학년 평균을 구하라.
select dept, avg(year) from student
where sno >= 300
group by dept;

-- Q) grade가 'A' 또는 'B'인 강의들 중에 강의의 기말고사 최소가 90 이상인 강의의 중간, 기말 평균을 출력
select cno, avg(finterm), avg(finterm) from enrol
where grade in ('A', 'B')
group by cno
having min(finterm) >= 90;

---

 🌷 DML_Select(advanced) 

▶️ 초기화 

drop table STUDENT;
drop table COURSE;
drop table ENROL;

create table STUDENT (
  sno number(3),
  sname nvarchar2(4),
  year number(1),
  dept nvarchar2(5)
);

 insert into STUDENT values (100, '나수영', 4, '컴퓨터');
 insert into STUDENT values (200, '이찬수', 3, '전기');
 insert into STUDENT values (300, '정기태', 1, '컴퓨터');
 insert into STUDENT values (400, '송병길', 4, '컴퓨터');
 insert into STUDENT values (500, '박종화', 2, '산공');
  
create table COURSE (
  cno char(4),
  cname nvarchar2(10),
  credit number(1),
  dept nvarchar2(4),
  professor nvarchar2(4)
);

insert into COURSE values ('C123', 'C프로그래밍',  3, '컴퓨터', '김성국');
insert into COURSE values ('C312', '자료구조', 3,	'컴퓨터', '황수관');
insert into COURSE values ('C324', '화일구조', 3,	'컴퓨터', '이규찬');
insert into COURSE values ('C413', '데이터베이스', 3, '컴퓨터', '이일로');
insert into COURSE values ('E412', '반도체', 3, '전자', '홍봉진');
  
  
create table ENROL (
  sno number(3),
  cno char(4),
  grade char,
  midterm integer,
  finterm integer
);
  
insert into ENROL values (100, 'C413',	'A',	90,	95);
insert into ENROL values (100, 'E412',	'A',	95,	95);
insert into ENROL values (200, 'C123',	'B',	85,	80);
insert into ENROL values (300, 'C312',	'A',	90,	95);
insert into ENROL values (300, 'C324',	'C',	75,	75);
insert into ENROL values (300, 'C413',	'A',	95,	90);
insert into ENROL values (400, 'C312',	'A',	90,	95);
insert into ENROL values (400, 'C324',	'A',	95,	90);
insert into ENROL values (400, 'C413',	'B',	80,	85);
insert into ENROL values (400, 'E412',	'C',	65,	75);
insert into ENROL values (500, 'C312',	'B',	85,	80);

commit;

▶️ 문제 

-- Q) 'C413' 과목을 등록한 학생 이름을 검색하라.

-- Q) 학번이 100인 학생과 동일한 학과에 속한 학생을 모두 찾아라. 

-- Q) student와 enrol은 카티션 프로덕트를 하시오. (sno에 따라)


-- Q) 'C413' 과목을 수강하는 학생들의 이름을 모두 찾아라.

-- Q) '데이터베이스' 과목을 수강하는 학생들의 이름을 모두 찾아라.

-- Q) '정기태' 학생이 수강한 모든 과목의 중간고사 성적 평균을 구하라. 

-- Q) '데이터베이스' 과목의 기말고사 최저점수는?

-- Q) '컴퓨터'과 소속 학생들의 과목별 기말고사 평균을 구하라.

-- Q) 모든 학생들에 대해 학생의 이름과 수강 과목이름, 해달 과목의 기말고사와 중간고사의 합계를 출력하라. (이름/Cname/ 합계)


-- STUDENT 테이블과 ENROL 테이블의 공통 속성이 sno라고 할 때 두 테이블을 조인하시오
-- 동일조인 사용

-- 자연조인 사용

-- 카티션 프로젝트 사용


-- '김성국' 교수의 과목을 등록한 학생의 학번과 학과를 구하시오
-- 서브쿼리 이용

-- 조인 이용


-- '컴퓨터구조' 과목을 수강하는 학생의 이름을 구하시오
-- 서브쿼리 이용

-- 조인 이용


-- 세 개 이상의 테이블을 조인해야 할 때 자연조인을 여번 사용하면 안되는 이유?

-- 등록한 과목이 전혀 없는 학생들의 이름을 출력하라

-- 등록한 학생이 전혀 없는 과목의 과목 이름을 출력하라

▶️  답

-- Q) 'C413' 과목을 등록한 학생 이름을 검색하라.
select sname from student where sno in (select sno from enrol where cno = 'C413');
-- Q) 학번이 100인 학생과 동일한 학과에 속한 학생을 모두 찾아라. 
select * from student where dept = (select dept from student where sno = 100);
-- Q) student와 enrol은 카티션 프로덕트를 하시오. (sno에 따라)
select * from student s join enrol e on s.sno = e.sno;
select * from student s, enrol e where s.sno = e.sno;

-- Q) 'C413' 과목을 수강하는 학생들의 이름을 모두 찾아라.
select sname from student where sno in (select distinct sno from enrol where cno = 'C413');

-- Q) '데이터베이스' 과목을 수강하는 학생들의 이름을 모두 찾아라.
select sname from ( COURSE natural join ENROL) join STUDENT on ENROL.sno = STUDENT.sno where COURSE.cname = '데이터베이스';
-- Q) '정기태' 학생이 수강한 모든 과목의 중간고사 성적 평균을 구하라. 
select cno ,avg(midterm) from (COURSE natural join ENROL) group by cno;
-- Q) '데이터베이스' 과목의 기말고사 최저점수는?
select min(finterm) from (course natural join enrol) where cname = ‘데이터베이스’;
-- Q) '컴퓨터'과 소속 학생들의 과목별 기말고사 평균을 구하라.
select avg(finterm) from (student natural join enrol) where dept = '컴퓨터' group by cno;
-- Q) 모든 학생들에 대해 학생의 이름과 수강 과목이름, 해달 과목의 기말고사와 중간고사의 합계를 출력하라. (이름/Cname/ 합계)
select sname, cname, finterm + midterm from (student natural join enrol) join course on enrol.cno = course.cno;


-- STUDENT 테이블과 ENROL 테이블의 공통 속성이 sno라고 할 때 두 테이블을 조인하시오
-- 동일조인 사용
select * from student s, enrol e where student.sno = enrol.sno;
-- 자연조인 사용
select * from student natural join enrol;
-- 카티션 프로젝트 사용
select * from student, enrol where student.sno = enrol.sno;


-- '김성국' 교수의 과목을 등록한 학생의 학번과 학과를 구하시오
-- 서브쿼리 이용
select sno, dept from student
where sno = (select sno from enrol
    where cno in (
        select cno from course where professor = '김성국'
    )
);
-- 조인 이용
select s.sno, s.dept 
from (student s join enrol e on s.sno = e.sno) join course c on e.cno = c.cno
where professor = '김성국';


-- '컴퓨터구조' 과목을 수강하는 학생의 이름을 구하시오
-- 서브쿼리 이용
select s.sname from student s 
where s.sno in (select e.sno from enrol e join course c on e.cno = c.cno where c.cname = '컴퓨터구조');
-- 조인 이용
select sname 
from (course join enrol on course.cno = enrol.cno) join student on enrol.sno = student.sno
where cname = '컴퓨터구조';


-- 세 개 이상의 테이블을 조인해야 할 때 자연조인을 여번 사용하면 안되는 이유
-- 예를 들어 (course natural join enrol) natural join course 와 같이 조인을 사용하면
-- (c 자연조인 n) 의 결과와 s에는 2개의 공통 속성(cno, dept)가 생기기 때문에 두 속성이 모두 동일한 투플들만 조인된다


-- 등록한 과목이 전혀 없는 학생들의 이름을 출력하라
select s.sname from student s left outer join enrol e on s.sno = e.sno
where e.sno is null;  -- full outer join도 가능
select s.sname from enrol e right outer join student s on e.sno = s.sno
where e.sno is null; 

-- 등록한 학생이 전혀 없는 과목의 과목 이름을 출력하라
select cname from enrol e right outer join course c on e.cno = c.cno
where e.cno is null; -- full outer join도 가능
select cname from course c left outer join enrol e on c.cno = e.cno
where e.cno is null;

---

 🌷 서술형 준비

▶️  데이터 베이스의 특성은?

1. 내용에 의한 참조
2. 동시공용
3. 계속적인 변경 가능
4. 온라인 접근

▶️  데이터 베이스의 조건은 ?

1. 통합된 데이터
2. 저장 데이터
3. 운영 데이터
4. 공용 데이터

▶️ 파일을 이용한 데이터 처리의 문제는?

1. 데이터 일관성 상실
2. 데이터 무결성 취약
3. 보안성 취약
4. 경제성 취약

▶️  데이터 무결성이란 ?

→ 데이터 무결성에는 개체 무결성 제약과 참조 무결성 제약이 있다. 개체 무결성 제약은 PK로 지정한 key는 null 값을 가질 수 없다는 점이며, 참조 무결성 제약은 외래키의 값은 참조하고 있는 테이블에 PK 혹은 unique 값으로 존재해야 한다는 점이다.

▶️  데이터 독립성이란 ?

DB의 논리적, 물리적인 구조가 변경되더라도 운영 프로그램은 영향을 받으면 안된다.

▶️  3단계의 스키마 구조를 적으시오.

1. 외부 스키마 (서브 스키마)
2. 개념 스키마
3. 내부 스키마

▶️  DB 내의 데이터 구조, 관계, 제약 조건의 명세

: Schema

▶️  스키마 정보, 사상 정보 등을 저장한 DB

: system catalogue

▶️  시스템 카탈로그를 접근하기 위한 정보

: data directory

▶️  데이터 사용자들 중의 하나이며 데이터베이스 시스템을 관리하고 운영하는 역할을 수행하는 사람

: DBA ( Database administrator)

 

 

 

⚠️ 제가 공부용으로 만든 거라서 오타나, 잘못된 문제가 있을 수도 있습니다 ! 혹시나 발견하신 분이 계시다면, 댓글로 남겨주세요 :) 또, 푸시다가 모르시는 게 있다면 댓글 남겨주세요 !