데이터정의어 (DDL) — 연습 문제

티스토리 뷰

DB/MySQL

데이터정의어 (DDL) — 연습 문제

태로미 2023. 4. 18. 16:18

목차

📍 연습문제 1

– 테이블 생성& 데이터 입력

– Update 구문

– Delete 구문

– Alter Table 구문

– View 생성

📍 연습문제 2

– 테이블 생성

– 데이터 입력

– View 생성

– 백업 스크립트

📍 연습 문제 1

▸ 테이블 생성

1. major table

• 테이블 생성

CREATE TABLE major
(major_id int PRIMARY KEY AUTO_INCREMENT,
 major_name varchar(50) NOT NULL);

• 데이터 입력

INSERT INTO major (major_id, major_name) 
VALUES 
(1, "컴퓨터공학"),
(2, "수학과"),
(3, "영어영문학");

SELECT * FROM major;

✓ 실행 결과

2. student table

• 테이블 생성

CREATE TABLE student
(student_id int PRIMARY KEY AUTO_INCREMENT,
 name varchar(50) NOT NULL,
 birthdate date NOT NULL,
 major_id int,
 FOREIGN KEY(major_id) REFERENCES major(major_id));

• 데이터 입력

INSERT INTO student (name, birthdate, major_id)
VALUES
("김철수", "1999-05-12", 1),
("이영희", "2000-08-25", 2),
("박민수", "1999-11-30", 1),
("최지은", "2001-02-19", 3);

– Auto_increment가 적용된 컬럼은 뺀 나머지 컬럼을 명시하고 데이터를 입력하면
Auto_increment에 의해 해당 컬럼은 자동으로 1씩 증가함.

SELECT * FROM student;

✓ 실행 결과

3. professor table

• 테이블 생성

CREATE TABLE professor
( professor_id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(50) NOT NULL);

• 데이터 입력

 INSERT INTO professor (name)
 VALUES
 ("김도현"),
 ("이윤미"),
 ("박상준");

SELECT * FROM professor;

✓ 실행 결과

4. courses table

• 테이블 생성

CREATE TABLE courses
( course_id int PRIMARY KEY AUTO_INCREMENT,
  course_name varchar(50) NOT NULL,
  professor_id int,
  FOREIGN KEY(professor_id) REFERENCES professor(professor_id));

• 데이터 입력

INSERT INTO courses (course_name, professor_id)
 VALUES
 ("자료구조", 1),
 ("미적분학", 2),
 ("영문학개론", 3),
 ("프로그래밍", 1);

SELECT * FROM courses;

✓ 실행 결과

5. grades table

• 테이블 생성

CREATE TABLE grades
( grade_id int PRIMARY KEY AUTO_INCREMENT,
  student_id int,
  course_id int,
  grade float NOT NULL,
  FOREIGN key(student_id) REFERENCES student(student_id),
  FOREIGN key(course_id) REFERENCES courses(course_id));

• 데이터 입력

 INSERT INTO grades (student_id, course_id, grade)
 VALUES
 (1, 1, 85),
 (2, 2, 90),
 (3, 1, 78),
 (4, 3, 92);

SELECT * FROM grades;

✓ 실행 결과

▸ UPDATE 구문 작성

Q1. 학생 테이블에서 학생 이름이 '김철수'인 학생의 전공을 '영어영문학'으로 변경하기.

UPDATE major SET major_name = "영어영문학"
WHERE major_id = (SELECT major_id
                  FROM student
                  WHERE name = "김철수");

-- 확인
SELECT s.name, m.major_name
FROM major m JOIN student s
ON m.major_id = s.major_id;

✓ 실행 결과

Q2. 성적 테이블에서 학생 아이디가 3인 학생의 성적을 10점 올리기.

UPDATE grades SET grade = (grade+10)
WHERE student_id = 3;

-- 확인
SELECT student_id, grade
FROM grades
WHERE student_id = 3;

✓ 실행 결과

▸ DELETE 구문 작성

Q. 과목 테이블에서 교수 아이디가 2인 교수가 가르치는 과목을 모두 삭제하기.

–   과목 테이블에서 교수 아이디가 2인 교수가 가르치는 과목을 삭제하려 했더니 안됨.
–   FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)) 에러 발생.
     → course 테이블의 course_id 컬럼이 부모 컬럼이라 삭제 안됨.
     →   courses 테이블의 course_id 컬럼을 부모로 가지고 있는 자식컬럼을 먼저 삭제해야 함.
     → 찾아보니 grades 테이블의 course_id가 자식컬럼.
     →   grades 테이블의 course_id 컬럼의 교수 아이디가 2인걸 먼저 삭제.

– 관련 작업이 많은 경우 외래키 제약조건에 ON DELETE CASCADE를 사용하는 것도 방법.

• 자식컬럼 삭제

DELETE FROM grades
WHERE course_id = 2;

• 교수 아이디가 2인 교수가 가르치는 과목 삭제

DELETE FROM courses
WHERE professor_id = 2;

-- 확인
SELECT * FROM courses;

✓ 실행 결과

▸ ALTER TABLE 구문

Q1. 학생 테이블에서 학생의 이름 컬럼의 데이터 타입을 VARCHAR(100)으로 변경하기.

ALTER TABLE student
MODIFY name varchar(100);

-- 확인
DESC student;

✓ 실행 결과

Q2. 교수 테이블에 이메일 컬럼 추가하기.

이메일 컬럼은 VARCHAR(100)이며, 고유한 값을 가져야 함.

ALTER TABLE professor
ADD email varchar(100) DEFAULT '미입력';

-- 확인
SELECT * FROM professor;

✓ 실행 결과

Q3. 과목 테이블에서 교수 아이디(professor_id)에 대한 외래 키 제약조건 삭제하기.
(외래 키 제약조건 이름은 'fk_courses_professors' 라고 가정.)

ALTER TABLE courses
DROP FOREIGN KEY fk_courses_professors;

– 위의 DELETE구문에서 외래키를 삭제했으므로 사실상 걸려있는 외래키가 없음.
→ 실행하면 당연히 에러 발생. 쿼리만 알아두기.

▸ VIEW 생성 및 수정

– courses, grades, majors, students 테이블 JOIN 사용.

Q1. 학생의 이름, 전공 이름, 각 학생의 수강한 과목 이름과 성적을 포함하는 뷰 생성하기.
뷰의 이름은 'student_grades_major'로 지정함.

CREATE VIEW student_grades_major AS 
SELECT s.name, m.major_name, c.course_name ,g.grade
FROM student s
JOIN major m ON s.major_id = m.major_id
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id;

-- 확인
SELECT * FROM student_grades_major

✓ 실행 결과

Q2. 각 과목별로 평균 성적과 학과 이름을 포함하는 뷰 생성하기.
뷰의 이름은 'course_avg_grade_major'로 지정함.

CREATE VIEW course_avg_grade_major AS
SELECT m.major_name, c.course_name, AVG(g.grade)
FROM student s
JOIN major m ON s.major_id = m.major_id
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
GROUP BY m.major_name, c.course_name;

-- 확인
SELECT * FROM course_avg_grade_major ;

✓ 실행 결과

📍 연습 문제 2

▸ 도서 대여

• 데이터베이스 생성

CREATE DATABASE book_borrow;
SHOW DATABASES;
USE book_borrow;

• 테이블 생성

1. User 테이블

CREATE TABLE User
( user_id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  email varchar(100) NOT NULL UNIQUE,
  phone_number varchar(15) NOT NULL UNIQUE,
  address varchar(200) );

2. Category 테이블

CREATE TABLE Category
( category_id int PRIMARY KEY AUTO_INCREMENT,
  category_name varchar(50) NOT NULL UNIQUE );

3. BookLocation 테이블

CREATE TABLE BookLocation
( location_id int PRIMARY KEY AUTO_INCREMENT,
  location varchar(100) NOT NULL UNIQUE );

4. Book 테이블

CREATE TABLE Book
( book_id int PRIMARY KEY AUTO_INCREMENT,
  title varchar(100) NOT NULL,
  author varchar(100) NOT NULL,
  category_id int,
  location_id int,
  FOREIGN KEY (category_id) REFERENCES Category(category_id),
  FOREIGN KEY (location_id) REFERENCES BookLocation(location_id) );

5. BorrowRecord 테이블

CREATE TABLE BorrowRecord
( record_id int PRIMARY KEY AUTO_INCREMENT,
  user_id int,
  book_id int,
  borrow_date date NOT NULL,
  due_date date NOT NULL,
  return_date date,
  FOREIGN KEY (user_id) REFERENCES User(user_id),
  FOREIGN KEY (book_id) REFERENCES Book(book_id) );

• 데이터 입력

INSERT INTO User (name, email, phone_number, address)
VALUES
('김영희', 'younghee@example.com', '010-1234-5678', '서울시 마포구 상암동'),
('이철수', 'cheolsu@example.com', '010-2345-6789', '서울시 강남구 역삼동'),
('박지영', 'jiyoung@example.com', '010-3456-7890', '서울시 송파구 잠실동'),
('최민수', 'minsu@example.com', '010-4567-8901', '인천시 남동구 구월동'),
('김수진', 'sujin@example.com', '010-5678-9012', '서울시 관악구 신림동'),
('황예원', 'yewon@example.com', '010-6789-0123', '서울시 중구 명동'),
('이현우', 'hyunwoo@example.com', '010-7890-1234', '부산시 해운대구 중동'),
('박성민', 'sungmin@example.com', '010-8901-2345', '서울시 서초구 반포동'),
('한지민', 'jimin@example.com', '010-9012-3456', '대전시 유성구 봉명동'),
('장현준', 'hyunjoon@example.com', '010-0123-4567', '울산시 남구 신정동');

INSERT INTO Category (category_name)
VALUES
('소설'),
('에세이'),
('역사'),
('과학'),
('인문학'),
('경제'),
('예술'),
('컴퓨터/IT'),
('여행'),
('자기계발');

INSERT INTO BookLocation (location)
VALUES
('1층 A구역'),
('1층 B구역'),
('2층 A구역'),
('2층 B구역'),
('3층 A구역'),
('3층 B구역'),
('4층 A구역'),
('4층 B구역'),
('5층 A구역'),
('5층 B구역');

INSERT INTO Book (title, author, category_id, location_id)
VALUES
('자존감 수업', '윤홍균', 10, 1),
('해리포터와 마법사의 돌', 'J.K. 롤링', 1, 2),
('컴퓨터과학이 여는 세계', '김진아', 8, 3),
('세계사를 만든 역사', '안창호', 3, 4),
('달러구트 꿈 백화점', '이미예', 1, 5),
('우리가 빛의 속도로 갈 수 없다면', '김초엽', 5, 6),
('시간의 역사', '스티븐 호킹', 4, 7),
('수학이 알려주는 거짓말의 법칙', '조던 엘렌버그', 4, 8),
('미술관에 간 작가들', '이경록', 7, 9),
('누구나 쉽게 익히는 파이썬', '서진수', 8, 10),
('미래의 선택', '앨빈 토플러', 5, 2),
('인공지능의 시대', '김영성', 8, 4),
('심리학의 이해', '이현진', 5, 3),
('코딩 철학', '정용준', 8, 6),
('탈무드의 지혜', '류시화', 5, 7),
('세계미술사', '김영미', 7, 5),
('데이터 과학', '이승준', 8, 6),
('역사 속의 거짓말', '박종석', 3, 1),
('프랑스 여행기', '김민아', 9, 9),
('영어 회화의 비밀', '최은영', 10, 10);


-- 5.
INSERT INTO BorrowRecord (user_id, book_id, borrow_date, due_date, return_date)
VALUES
(1, 1, '2023-03-01', '2023-03-15', '2023-03-13'),
(2, 2, '2023-03-05', '2023-03-19', '2023-03-18'),
(3, 3, '2023-03-10', '2023-03-24', '2023-03-22'),
(4, 4, '2023-03-15', '2023-03-29', '2023-03-28'),
(5, 5, '2023-03-20', '2023-04-03', '2023-04-02'),
(6, 6, '2023-03-25', '2023-04-08', NULL),
(7, 7, '2023-03-30', '2023-04-13', NULL),
(8, 8, '2023-04-04', '2023-04-18', NULL),
(9, 9, '2023-04-09', '2023-04-23', NULL),
(10, 10, '2023-04-14', '2023-04-28', NULL),
(1, 6, '2023-04-16', '2023-04-30', NULL),
(2, 7, '2023-04-11', '2023-04-25', NULL),
(3, 8, '2023-04-06', '2023-04-20', NULL),
(4, 9, '2023-04-01', '2023-04-15', '2023-04-14'),
(5, 10, '2023-03-27', '2023-04-10', '2023-04-09'),
(6, 1, '2023-03-22', '2023-04-05', '2023-04-04'),
(7, 2, '2023-03-17', '2023-03-31', '2023-03-30'),
(8, 3, '2023-03-12', '2023-03-26', '2023-03-25'),
(9, 4, '2023-03-07', '2023-03-21', '2023-03-20'),
(10, 5, '2023-03-02', '2023-03-16', '2023-03-15'),
(6, 16, '2023-01-30', '2023-02-13', '2023-02-12'),
(7, 17, '2023-01-25', '2023-02-08', '2023-02-07'),
(8, 18, '2023-01-20', '2023-02-03', '2023-02-02'),
(9, 19, '2023-01-15', '2023-01-29', '2023-01-28'),
(10, 20, '2023-01-10', '2023-01-24', '2023-01-23'),
(1, 11, '2023-04-15', '2023-04-29', NULL),
(2, 12, '2023-04-10', '2023-04-24', NULL),
(3, 13, '2023-04-05', '2023-04-19', NULL),
(4, 14, '2023-04-01', '2023-04-15', '2023-04-14'),
(5, 15, '2023-03-26', '2023-04-09', '2023-04-08'),
(6, 16, '2023-03-21', '2023-04-04', '2023-04-03'),
(7, 17, '2023-03-16', '2023-03-30', '2023-03-29'),
(8, 18, '2023-03-11', '2023-03-25', '2023-03-24'),
(9, 19, '2023-03-06', '2023-03-20', '2023-03-19'),
(10, 20, '2023-03-01', '2023-03-15', '2023-03-14'),
(1, 11, '2023-02-24', '2023-03-10', '2023-03-09'),
(2, 12, '2023-02-19', '2023-03-05', '2023-03-04'),
(3, 13, '2023-02-14', '2023-02-28', '2023-02-27'),
(4, 14, '2023-02-09', '2023-02-23', '2023-02-22'),
(5, 15, '2023-02-04', '2023-02-18', '2023-02-17'),
(1, 11, '2023-01-05', '2023-01-19', '2023-01-18'),
(2, 12, '2022-12-31', '2023-01-14', '2023-01-13'),
(3, 13, '2022-12-26', '2023-01-09', '2023-01-08'),
(4, 14, '2022-12-21', '2023-01-04', '2023-01-03'),
(5, 15, '2022-12-16', '2022-12-30', '2022-12-29'),
(6, 16, '2022-12-11', '2022-12-25', '2022-12-24'),
(7, 17, '2022-12-06', '2022-12-20', '2022-12-19'),
(8, 18, '2022-12-01', '2022-12-15', '2022-12-14'),
(9, 19, '2022-11-26', '2022-12-10', '2022-12-09'),
(10, 20, '2022-11-21', '2022-12-05', '2022-12-04');

• View 생성

1. 현재 대출 중인 도서의 정보를 출력하는 currently_borrowed_books 뷰 생성하기.

– 반납되지 않은 도서는 대출 중으로 볼 수 있음.

– borrower는 책 빌려간 사람 이름.

CREATE VIEW currently_borrowed_books AS
SELECT b.book_id, b.title, b.author, br.user_id, name borrower, borrow_date, due_date
FROM Book b JOIN BorrowRecord br JOIN User u
ON b.book_id = br.book_id && br.user_id = u.user_id;

-- 확인
SELECT * FROM currently_borrowed_books;

✓ 실행 결과

2. 연체 도서 목록 출력하는 overdue_books 뷰 생성.

– 오늘 (2023-04-17) 기준 연체된 도서 출력.

– borrower는 책 빌려간 사람의 이름.

CREATE VIEW overdue_books AS
SELECT b.book_id, title, author, u.user_id, name borrower, borrow_date, due_date
FROM Book b JOIN BorrowRecord br JOIN User u
ON b.book_id = br.book_id && br.user_id = u.user_id
WHERE '2023-04-17' > due_date;

-- 확인
SELECT * FROM overdue_books;

✓ 실행 결과

3. 도서별 대출 횟수를 출력하는 book_borrow_count 뷰 생성

– borrow_count는 대출 횟수 값.

CREATE VIEW book_borrow_count AS
SELECT br.book_id, title, count(b.book_id) borrow_count
FROM BorrowRecord br JOIN book b
ON br.book_id = b.book_id
GROUP BY b.book_id;

-- 확인
SELECT * FROM book_borrow_count

✓ 실행 결과

4. 사용자 별 대출 횟수를 출력하는 user_borrow_count 뷰 생성하기.

– borrow_count는 대출 횟수 값.

CREATE VIEW user_borrow_count AS
SELECT u.user_id, name, email, count(u.user_id) borrow_count
FROM USER u JOIN BorrowRecord br
ON u.user_id = br.user_id
GROUP BY u.user_id;

-- 확인
SELECT * FROM user_borrow_count;

✓ 실행 결과

• 백업 스크립트

mysqldump -u 계정명 -p 백업DB > 백업파일명.sql
mysqldump -u root -p book_barrow > backup.sql

– 다음 명령어를 터미널에서 입력.

– 백업 스크립트 파일은 접속 중인 터미널 경로에 생성됨.

– 백업 파일의 경로를 절대경로로 작성하는 경우, 현재 접속 위치와는 상관없이 생성됨.

→ 절대경로는 파일명 앞에 작성해줌.

💡 원하는 폴더 위치가 있을 경우, 해당 폴더 위치의 주소창에 cmd를 검색하면 해당 위치로 경로가 지정됨.

저작자표시 비영리 변경금지

'DB > MySQL' 카테고리의 다른 글

데이터정의어 (DDL) — View (0)	2023.04.05
데이터정의어 (DDL) — Table (0)	2023.03.21
트랜잭션제어어 (TCL) (0)	2023.03.21
데이터 조작어 (DML) (0)	2023.03.20
서브쿼리 (SubQuery) (0)	2023.03.14

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/05 »
일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

글 보관함

TAErogramming_____태로그래밍 *

티스토리 뷰