7월 8일

DATA ON-AIR – DATA ON-AIR – 한국데이터산업진흥원 (dataonair.or.kr)

DATA ON-AIR – DATA ON-AIR – 한국데이터산업진흥원

 

SQL의 Tablespaces 확인 명령어

 

 

Tablespace 생성하기

위의 경로로 잘 만들어진 것을 확인 할 수 있다.

 

'resize' 명령어를 활용하여 파일의 사이즈를 늘릴 수 있다.

 

 

SQL 예제문제 1

 

다음과 같은 students 테이블이 있습니다.

 

create table students (student_id INT PRIMARY KEY, name varchar(100), age INT, grade varchar(2));

 

모든 학생의 이름과 나이를 조회하는 SQL 쿼리를 작성하세요.

 

insert into students (student_id, name, age, grade) values ('1', 'KWON', '25', 'A');

insert into students (student_id, name, age, grade) values ('2', 'KIM', '22', 'B-');

insert into students (student_id, name, age, grade) values ('3', 'LEE', '27', 'C+');

insert into students (student_id, name, age, grade) values ('4', 'SA', '21', 'A+');

insert into students (student_id, name, age, grade) values ('5', 'SIN', '31', 'D-');

 

 

 

 

예제 문제 2

create table enrollments (enrollment_id INT PRIMARY  KEY, student_id INT, course_name varchar(100));

insert into enrollments (enrollment_id, student_id, course_name) values ('11', '1', 'math');

insert into enrollments (enrollment_id, student_id, course_name) values('12', '2', 'english');

insert into enrollments (enrollment_id, student_id, course_name) values('13', '3', 'science');

insert into enrollments (enrollment_id, student_id, course_name) values('14', '4', 'physical');

 

create table students2 (student_id INT PRIMARY KEY, name varchar(100));

insert into students2 (student_id, name) values ('2', 'KIM THAE HUN');

insert into students2 (student_id, name) values ('1', 'JANG WON SEOK');

insert into students2 (student_id, name) values('3', 'HWNG JAE HYEOK');

insert into students2 (student_id, name) values('4', 'GAGAGAGA');

 

각 학생이 수강 중인 과목 이름을 함꼐 조회하는 SQL 쿼리를 작성하세요(JOIN)

select students2.*, enrollments.* from students2 join enrollments on students2.student_id = enrollments.student_id;

 

 

예제 문제3 트랜젝션

다음 트랜잭션의 ACID 특성을 설명하세요.

 

원자성(Atomicity) 트랜잭션과 관련된 작업들이 부분적으로 실행되다가 중단되지 않는 것을 보장하는 능력

일관성(Consisency) 트랙잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것을 의미

고립성(Isolation) 트랜잭션을 수행 시 다른 트랙잰션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미

지속성(Durability) 성공적으로 수행된 트랜잭션은 항상 반영되어 있음을 의미함, 모든 트랜잭션은 로그로 남고 시스템 장애 발생 전 특정한 시점으로 되돌릴 수 있음.

 

 

예제 문제 4: 인덱스

인덱스를 사용하는 이유와 인덱스의 단점을 설명하세요.

데이터 검색 속도를 향상, 단점은 추가적 공간 필요, DML이 수반 되므로 함께 index도 갱신

 

 

예제 문제 5:  SQL 고급

다음  orders 테이블에서 각 고객이 주문한 총 금액을 계산하는 SQL 쿼리를 작성하세요.(집계함수)

 

create table orders (order_id INT PRIMARY KEY, customer_id INT, order_amount DECIMAL(10, 2));

insert into orders (order_id, customer_id, order_amount) values ('1', '1', '2500.20');

insert into orders (order_id, customer_id, order_amount) values ('2', '2', '24566.00');

insert into orders (order_id, customer_id, order_amount) values ('3', '3', '212.19');

insert into orders (order_id, customer_id, order_amount) values ('6', '4', '4514.15');

 

select sum(order_amount) from orders;

 

예제 문제 6: 데이터 삽입

students 테이블에 새로운 학생(student_id: 33, name : 'Charile', age: 20, grade: 'B') 를 삽입하는 SQL 쿼리를 작성하세요

insert into students (student_id, name, age, grade) values ('33', 'Charile', '20', 'B');

 

 

예제 문제 7: 데이터 업데이트

students 테이블에서 학생 ID가 ?? 인 학생의 나이를 22로 업데이트 하는 SQL 쿼리를 작성하세요.

update students set age = '22' where student_id = '1';

 

 

예제 문제 8: 데이터 삭제

students 테이블에서 학생 ID가 ?? 인 학생을 삭제하는 SQL 쿼리를 작성하세요.

delete students where student_id = '1';

 

 

예제문제 9:  HAVING

orders 테이블에서 각 고객의 총 주문 금액이 1000 이상인 고객 ID와 총 금액을 조회하는 SQL 쿼리를 작성하세요.

select customer_id, order_amount from orders where order_amount >= 1000 group by customer_id, order_amount;

 

예제문제 10: 서브쿼리

students 테이블에서 가장 나이가 많은 학생의 이름을 조회하는 SQL  쿼리를 작성하세요.

select name from students where age = (select max(age) from students);

 

 

예제 문제 11: 인덱스 생성

students 테이블의 name 열에 인덱스를 생성하는 SQL 쿼리를 작성하세요.

create index name on students(name);

 

 

예제 문제 12: 뷰 생성(hint: join~on 문장 사용, 2개의 table 사용하여, student_id 로 join~)

학생의 이름과 과목 이름을 포함하는 student_courses 뷰를 생성하는 SQL 쿼리를 작성하세요.

create view student_courses AS select s.name, e.course_name from students s join enrollments e on s.student_id = e.student_id;

 

 

예제 문제 13: 트랜잭션 제어

다음 트랜잭션을 수행하기 위한 SQL 쿼리를 작성하세요

학생 ID가 ??인 학생의 나이를 21로 업데이트 하고, 이를 롤백합니다.

update students set age = '21' where student_id = '3';

rollback;

rollback 된 것을 확인할 수 있다.

 

예제 문제 14: 집계 함수

orders 테이블에서 주문 금액의 평균을 계산하는 SQL 쿼리를 작성하세요.

 

 

예제 문제 15: DISTINCT

enrollments 테이블에서 수강 중인 과목의 목록을 중복 없이 조회하는 SQL 쿼리를 작성하세요.

select distinct course_name from enrollments;

 

 

예제 문제 16: CASE문

students 테이블에서 학생의 나이에 따라 '성일'(18세 이상) 또는 '미성년'(18세 미만)으로 분류하여 조회하는 SQL 쿼리를 작성하세요.

select name, age, case when age >= 18 then '성인' else '미성년' end as age_group from students;

 

 

예제 문제 17: 서브쿼리와 join 

각 학생이 수강 중인 과목과 함께 해당 학생이 가장 많이 수강하는 과목의 이름을 조회하는 SQL 쿼리를 작성하세요.

select s.name , e.course_name, sub.max_course from students s join enrollments e on s.student_id = e.student_id join (select student_id, max (course_name) as max_course from enrollments group by student_id) sub on s.student_id = e.student_id;

 

 

예제 문제 18: UNION

students 테이블과 graduates 테이블에서 모든 학생의 이름을 중복 없이 조회하는 SQL 쿼리를 작성하세요.

create table graduates as select * from students; 

==> graduates 테이블을 students 테이블과 똑같이 생성(CTAS)

select name from students union select name from graduates;

 

 

예제 문제 19: 서브쿼리와 IN

students 테이블에서 과목명이 'science' 인 과목을 수강하는 학생의 이름을 조회하는 SQL 쿼리를 작성하세요. enrollments 테이블을 사용하세요.

select name from students where student_id IN(select student_id from enrollments where course_name = 'science');

 

 

예제 문제 20: 데이터 백업(hint: create 문장)

students 테이블을 students_backup 테이블로 백업하는 SQL 쿼리를 작성하세요.

 

 

예제 문제 21: 데이터 복구(hint: insert into 문장)

student_backup 테이블의 데이터를 students 테이블에 복구하는 SQL 쿼리를 작성하세요.

insert into students (student_id, name, age, grade) select student_id, name, age, grade from students_backup; 

 

 

예제 문제 22: 트리거

students 테이블에 레코드가 삽입 될 때마다 audit_log 테이블에 로그를 기록하는 트리거를 작성하세요. audit_log 테이블의 구조는 다음과 같습니다.

create table audit_log (log_in INT PRIMARY KEY AUTO_INCREMENT, student_id INT, action varchar(50), action_time timestamp default current_timestamp);

 

 

예제 문제 23: 저장 프로시저

학생의 나이를 업데이트하는 저장 프로시저를 작성하세요. 프로시저 이름은 update_student_age 이며, 인자로 학생 ID와 새로운 나이를 받습니다.

create procedure ~~~~ ( ~~~~in~~~~in~~~~)

begin

 

end;

 

create procedure update_student_age (in p_student_id int, in p_new_age int)

is

begin

update students set age = p_new_age where student_id = p_student_id;

end;

/

 

 

select * from students; --> 확인(id,age)

인자 주는 방법 --> call update_student_age(1, 25); or exec update_student_age(1, 25);

select * from students; --> 확인 후 인자 주입 후, 바뀐상황 확인

 

 

 

예제 문제 24: 데이터베이스 권한 부여

 사용자 'user'에게 student 테이블에 대한 select 권한을 부여하는 SQL 쿼리를 작성하세요.

grant select on students to 'user';

 

 

예제 문제 25: 데이터베이스 인덱스 삭제

students 테이블의 name 열에 설정된 인덱스를 삭제하는 SQL 쿼리를 작성하세요

drop index idx_name on students;

 

 

예제 문제 26: 데이터베이스 스키마 변경

students 테이블의 email 열을 추가하는 SQL 쿼리를 작성하세요.

alter table students add column email varchar(100);

desc students; --> email열 추가 되었는지 확인

 

 

예제 문제 27: 집계 함수와 조건문

order 테이블에서 주문 금액의 합계가 500 이상인 고객의 ID와 주문 금액 합계를 조회하는 SQL 쿼리를 작성하세요

*집계 함수를 사용하면 where 조건을 사용 못한다, 그 대안으로 사용하는 것이 HAVING*

select customer_id, sum(order_amount) as total_amount from orders group by customer_id having sum(order_amount) >= 500 order by customer_id desc;

 

 

예제 문제 28: 데이터베이스 뷰 수정

기존의 student_courses 뷰에서 student_id 열을 추가하여 수정하는 SQL 쿼리를 작성하세요.

desc student_courses; <-- 확인

create or replace view student_courses as

select s.student_id, s.name, e.course_name

from students s

join enrollments e on s.student_id = e.student_id;

 

 

예제 문제 29: NULL 값 처리

students 테이블에서 grade 값이 NULL인 학생의 이름과 나이를 조회하는 SQL 쿼리를 작성하세요

select name, age

from students 

where grade is NULL;

 

 

예제 문제 30: join과 서브쿼리(hint: left join)

students 테이블과 enrollments 테이블을 사용하여 각 학생이 수강하는 과목 수를 조회하는 SQL 쿼리를 작성하세요.

 

select s.name, count(e.course_name) as course_count 

from students s

left join enrollments e on s.student_id = e.student_id 

group by s.name;

 

 

(제외)예제 문제 31: 날짜 함수

orders 테이브에서 주문일이 지난 30일 이내인 주물을 조회하는 SQL 쿼리를 작성하세요

주문일은 order_date 열에 저장되어 있다고 가정합니다

 

 

예제 문제 32: 조건부 업데이트

students 테이블에서 나이가 18세 미만인 학생의 grade를 'C'로 업데이트 하는 SQL 쿼리를 작성하세요

update students set grade = 'C' where age < 18;

 

 

예제 문제33: 다중 조건 검색

students 테이블에서 나이가 20세 이상 25세 이하인 학생의 이름과 나이를 조회하는 SQL 쿼리를 작성하세요.

select name, age from students where age between 20 and 25;

 

 

예제 문제 34: 데이터 집합 합집합과 교집합

students 테이블과 graduates 테이블에서 모든 학생의 이름을 조회하되, 중복되지 않는 학생의 이름만 조회하는 SQL 쿼리를 작성하세요.

select name from students

union 

select name from graduates

except

select name from sutudents

intersect 

select name from graduates;