[스나이퍼팩토리] 한컴AI아카데미 Database 1주차 후기

nara_lee·2025년 3월 20일
Databasesql
1

스나이퍼팩토리 한컴AI AI개발자 교육

목록 보기
27/45

Create User, Create table, Grant, Revoke, Update, Insert into, Alter table

Problem 0

use shop_db;
DROP USER IF EXISTS 'product_admin'@'localhost';
-- 1. 'product_admin' 사용자 생성 (DCL)
CREATE USER 'product_admin'@'localhost' IDENTIFIED BY 'prod1234';

-- 2. 'products' 테이블에 대한 권한 부여 (DCL)
GRANT ALL PRIVILEGES ON shop_db.products TO 'product_admin'@'localhost';
FLUSH PRIVILEGES;

-- 3. 제품 정보 추가 (DML)
INSERT INTO shop_db.products (id, name, price, category) VALUES 
(1, '게이밍 노트북', 12000, '전자제품'),
(2, '무선 마우스', 3000, '주변기기'),
(3, '기계식 키보드', 8500, '주변기기'),
(4, '27인치 모니터', 7000, '전자제품');

-- 4. 가격 인상 (DML)
UPDATE shop_db.products 
SET price = price * 1.1 
WHERE price >= 5000;

-- 5. 조회 전용 사용자 생성 및 권한 부여 (DCL)
DROP USER IF EXISTS 'product_viewer'@'localhost';
CREATE USER 'product_viewer'@'localhost' IDENTIFIED BY 'view1234';
GRANT SELECT ON shop_db.products TO 'product_viewer'@'localhost';
FLUSH PRIVILEGES;

-- 결과 확인
SELECT * FROM shop_db.products;
SHOW GRANTS FOR 'product_admin'@'localhost';
SHOW GRANTS FOR 'product_viewer'@'localhost';

Problem 1

1.직원들의 평균 급여를 계산하는 SQL 쿼리를 작성하세요.
2.2023년에 입사한 직원 수를 구하는 쿼리를 작성하세요.
3.부서별 최고 급여와 최저 급여의 차이를 계산하는 쿼리를 작성하세요.

--1--
SELECT AVG(salary)
FROM Registar.employee;

--2--
Select count(*)
FROM Registar.employee
Where (year(hire_date)) = 2025;

--3--
alter table Registar.employee
add dept_id int Default null; 


UPDATE employee
SET dept_id = CASE
 WHEN employee_id < 6 THEN 1
 WHEN employee_id > 10 THEN 2
 WHEN dept_id IS NULL THEN 3
 ELSE dept_id
END;

use Registar;
select dept_id, min(salary) as min, max(salary) as max, max(salary)-min(salary) as diff from employee 
group by dept_id
order by dept_id;

Problem 2: 은행 계좌 관리 시스템

문제: 은행 시스템에서 고객 계좌 관리를 담당하는 데이터베이스 작업을 수행하세요.

1) 'account_manager'와 'transaction_auditor' 두 사용자를 생성하세요.
2) 'account_manager'에게는 고객 계좌 업데이트 권한을, 'transaction_auditor'에게는 조회 권한만 부여하세요.
3) 'accounts' 테이블에 신규 고객 계좌를 3개 추가하세요.
4) 잔액이 10만원 미만인 계좌들에 대해 '휴면계좌' 상태로 업데이트하세요.
5) 'transaction_auditor'에게 'accounts' 테이블의 고객명 정보 접근 권한을 취소하세요.

use Retail;

CREATE TABLE `Accounts` (
  `Account_id` char(13) NOT NULL,
  `Name` varchar(25),
  `Balance` FLOAT(2) NOT NULL DEFAULT 0,
  `Inactive_state` TINYINT NULL DEFAULT 0 COMMENT 'True if Balance < ₩100,000',
  PRIMARY KEY (`Account_id`));
  
--1--
DROP USER IF EXISTS 'account_manager'@'localhost';
DROP USER IF EXISTS 'transaction_auditor'@'localhost';
CREATE USER 'account_manager'@'localhost' IDENTIFIED BY '0000';
CREATE USER 'transaction_auditor'@'localhost';

--2--
GRANT SELECT ON Accounts TO 'transaction_auditor'@'localhost';
GRANT UPDATE, SELECT ON Accounts TO 'account_manager'@'localhost';
FLUSH PRIVILEGES;

--3--
INSERT INTO Accounts (Account_id, Name, Balance) VALUES 
('0000000000001', '김사과', 1200000),
('0000000000002', '청포도', 30000000),
('0000000000003', '이감귤', 8500),
('0000000000004', '박키위', 7000);

--4--
update accounts
set Inactive_state = true
where Balance <100000;

--5--
Grant select on Retail.Accounts to 'transaction_auditor'@'localhost';
REVOKE select on Retail.Accounts from 'transaction_auditor'@'localhost';
Grant select (Account_id, Balance, Inactive_state) on Retail.Accounts to 'transaction_auditor'@'localhost';
-- `Name` column을 뺀 나머지 column들에 대한 권한을 준다.
-- MySQL REVOKE select ([COLUMN1, COLUMN2, ...]) 처럼 특정 column에 대한 권한만 revoke하는게 불가능하다.

Problem 3: 온라인 쇼핑몰 재고 관리

문제:
온라인 쇼핑몰의 재고 관리 시스템을 구현하세요.

1) 'inventory_manager'와 'sales_viewer' 두 사용자를 생성하세요.
2) 'inventory_manager'에게 재고 테이블에 대한 모든 권한을 부여하고, 다른 사용자에게 권한을 부여할 수 있는 권한도 함께 부여하세요.
3) 'inventory' 테이블에 제품 데이터를 4개 추가하세요.
4) 재고가 10개 미만인 제품들의 상태를 '재입고 필요'로 변경하고 우선순위를 'HIGH'로 설정하세요.
5) 'sales_viewer'에게 'inventory' 테이블의 조회 권한만 부여하고, 'sales_data' 테이블의 모든 권한을 취소하세요.

use Retail;
create table if not exists inventory (
	`Item_id` int NOT NULL Primary Key,
    `Name` varchar(25) NOT NULL,
    `Quantity` int Default Null, 
    `Priority` Enum('Low', 'Medium', 'High') Default Null,
    `Need_Restock` Boolean Default false
);
create table if not exists sales_data (
	`transaction_id` char(13) NOT NULL Primary Key,
    `Item_id` int,
    FOREIGN KEY (Item_id) REFERENCES inventory(Item_id)
);

-- 1 --
DROP USER IF EXISTS 'inventory_manager'@'localhost';
DROP USER IF EXISTS 'sales_viewer'@'localhost';
CREATE USER 'inventory_manager'@'localhost' IDENTIFIED BY '0000';
CREATE USER 'sales_viewer'@'localhost';

-- 2 --
GRANT ALL ON inventory TO 'inventory_manager'@'localhost';
GRANT ALL ON sales_data TO 'inventory_manager'@'localhost';
--  Grant all 하면 Grant GRANT OPTION 까지 포함된 명령어

-- 3 --
INSERT INTO inventory (Item_id, Name, Quantity) Values
(1, '게이밍 노트북', 12),
(2, '무선 마우스', 3),
(3, '기계식 키보드', 85),
(4, '27인치 모니터', 7);

-- 4 --
update inventory
set Priority = "HIGH"
where Quantity <10;

update inventory
set Need_Restock = true
where Quantity <10;

-- 5 --
-- REVOKE ALL ON sales_data from 'product_admin'@'localhost';
GRANT SELECT on inventory to 'sales_viewer'@'localhost';

Problem 4: 학생 성적 관리 시스템

문제:
학교 성적 관리 시스템의 데이터베이스 작업을 수행하세요.

1) 'teacher_admin'과 'student_viewer' 두 사용자 계정을 생성하세요.
2) 'teacher_admin'에게 성적 테이블에 대한 모든 권한을 부여하되, 데이터 삭제는 제한하세요.
3) 'grades' 테이블에 5명의 학생 성적 데이터를 추가하세요.
4) 성적이 60점 미만인 학생들의 상태를 '재시험 대상'으로 업데이트하세요.
5) 'student_viewer'에게는 자신의 성적만 볼 수 있도록 뷰를 생성하고 그 뷰에 대한 접근 권한을 부여하세요.

USE Retail;

CREATE TABLE IF NOT EXISTS grade (
    grade_id   INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject    VARCHAR(50) NOT NULL,
    score      INT NOT NULL,
    status     VARCHAR(100)
);
-- 3) 'grades' 테이블에 5명의 학생 성적 데이터를 추가하세요.
INSERT INTO grade (student_id, subject, score, status)
VALUES
  (1001, 'Math',    85, NULL),
  (1001, 'English', 58, NULL),
  (1002, 'Math',    92, NULL),
  (1003, 'Math',    45, NULL),
  (1004, 'English', 78, NULL);

-- 1) 'teacher_admin'과 'student_viewer' 두 사용자 계정을 생성하세요.
create user IF NOT EXISTS 'teacher_admin'@'localhost' IDENTIFIED BY "0000";
create user IF NOT EXISTS 'student_viewer'@'localhost';
-- 2) 'teacher_admin'에게 성적 테이블에 대한 모든 권한을 부여하되, 데이터 삭제는 제한하세요. 
GRANT SELECT, INSERT, UPDATE, CREATE on grade to 'teacher_admin'@'localhost';
-- drop, alter, delete를 제외함
-- 4) 성적이 60점 미만인 학생들의 상태를 '재시험 대상'으로 업데이트하세요.
update grade
set status = "재시험 대상"
where score < 60;

-- 5) 'student_viewer'에게는 자신의 성적만 볼 수 있도록 뷰를 생성하고 그 뷰에 대한 접근 권한을 부여하세요.
create view grade_view_for_students as
	select student_id, subject, score, status from grade;
grant select on grade_view_for_students to 'student_viewer'@'localhost';

FLUSH PRIVILEGES;

Between 과 In을 이용하여 and/or 연산자 대체

SELECT * FROM Registar.employee;

select name, salary 
from Registar.employee
where salary > 7000 and salary < 10000;
-- where salary between 7000 and 10000;

select name, address from Retail.members where address in ('Seoul', 'Busan');
-- select name, address from Retail.members where address = 'Seoul' or  address = 'Busan';

alter table Retail.product
add column Quantity int;

Group by

  • ⚠️ Group by 쓸 때는 항상 avg, sum, count 등의 통계 함수를 같이 써야함.
  • WHERE 대신 HAVING 쓰기!

예시 1

Select dept_id, group_concat(name) from employee group by dept_id;

예시 2

select month(order_date) as mo, sum(total_amount) as sum from Retail.orders
group by month(order_date)
having sum(total_amount) >= 1000000;

Case

case
	when age < 20 then "10대"
	when age between 20 and 29 then "20대"
	when age between 30 and 39 then "30대"
	when age between 40 and 49 then "40대"
	else "50대 이상"
end as age_group
from Retail.members;
SELECT * FROM Retail.members;

Problem 5

customer 테이블과 payment 테이블을 조인하여 각 고객이 지불한 총 금액을 계산하고, 지불 금액이 가장 많은 10명의 고객을 조회

use sakila;
select * from customer;
select * from payment;
-- 1. customer 테이블과 payment 테이블을 조인하여 
select c.customer_id, sum(p.amount) as total_paid
-- from customer natural join payment
-- 위 라인은 이름이 겹치는 column이 여러개이므로 예시와 맞지 않는다
from customer c 
join payment p
on c.customer_id = p.customer_id
-- 각 고객이 지불한 총 금액을 계산하고, 
group by c.customer_id
order by total_paid 
desc limit 10;
-- 지불 금액이 가장 많은 10명의 고객을 조회

Problem 6

actor 테이블과 film 테이블을 film_actor 테이블을 통해 조인하여 배우 "PENELOPE GUINESS"가 출연한 모든 영화의 제목을 조회

-- 배우 "PENELOPE GUINESS"의 actor_id를 actor에서 찾기
select actor_id 
from sakila.actor
where first_name = "PENELOPE" and last_name = "GUINESS";

-- 배우 "PENELOPE GUINESS" 의 actor_id 를 가진 film_id 를 film_actor에서 찾기
select fa.film_id
from sakila.film_actor fa
join (select a.actor_id 
	from sakila.actor
	where first_name = "PENELOPE" and last_name = "GUINESS") a
on a.actor_id = fa.actor_id;
![](https://velog.velcdn.com/images/devredz/post/90e9d304-229a-475d-a961-a712d7ad4367/image.png)

-- 배우 "PENELOPE GUINESS" 의 actor_id 를 가진 film_id를 가진 영화 title을 film에서 찾기
select f.title
from sakila.film f
join (select fa.film_id
	from sakila.film_actor fa
	join (select actor_id 
		from sakila.actor
		where first_name = "PENELOPE" and last_name = "GUINESS") a
	on a.actor_id = fa.actor_id) afa
on f.film_id = afa.film_id;

This is equivalent to:

use sakila;
with penelope_actor_id as (
	select actor_id 
	from actor
	where first_name = "PENELOPE" and last_name = "GUINESS"
),    
penelope_film_id as (
	select film_id
    from film_actor
    join penelope_actor_id
    on penelope_actor_id.actor_id = film_actor.actor_id)
select film.title
from film
join penelope_film_id
on penelope_film_id.film_id = film.film_id;

본 후기는 [한글과컴퓨터x한국생산성본부x스나이퍼팩토리] 한컴 AI 아카데미 (B-log) 리뷰로 작성 되었습니다.

#한컴AI아카데미 #AI개발자 #AI개발자교육 #한글과컴퓨터 #한국생산성본부 #스나이퍼팩토리 #부트캠프 #AI전문가양성 #개발자교육 #개발자취업

profile
nara_lee
이전 포스트

Context API

다음 포스트

ERD Crow's Foot Diagram 실습 과제

0개의 댓글