[MySQL] SQL 고급 실습 연습하기2

데이터모델링 실습

WorkBench를 이용한 데이터 모델링 후 데이터 조회 문제 실습까지

---

 

✔  Guide Line

1️⃣ 총 5개의 테이블을 WorkBench에서 생성합니다. 다만, E-R 다이어그램을 이용합니다. 각각의 테이블 이름은 [Students], [Professors], [Departments], [Register], [Lectures] 이 되며, 아래와 같이 다이어그램을 제작하면 됩니다.

 

 

2️⃣ 그 후 각각의 테이블에 데이터를 입력하는 과정을 진행합니다.

INSERT INTO `테이블명` VALUES(데이터1, 데이터2, 데이터3 ... );

 

 

 

 

✔ 관련 쿼리문

INSERT INTO `Student` VALUES ('20101001',10,'P10101','정우성','760121-1234567','010-1101-7601',NULL,'서울');
INSERT INTO `Student` VALUES ('20101002',10,'P10101','이정재','750611-1234567','010-1102-7506',NULL,'서울');
INSERT INTO `Student` VALUES ('20111011',11,'P11103','전지현','890530-1234567','010-1103-8905','jjh@naver.com','대전');
INSERT INTO `Student` VALUES ('20111013',11,'P11103','이나영','790413-1234567','010-2101-7904','lee@naver.com','대전');
INSERT INTO `Student` VALUES ('20111014',11,'P11104','원빈','660912-1234567','010-2104-6609','one@naver.com','대전');

INSERT INTO `Student` VALUES ('21221010',22,'P22110','장동건','790728-1234567','010-3101-7907','jang@naver.com','대구');
INSERT INTO `Student` VALUES ('21231002',23,'P23102','고소영','840615-1234567','010-4101-8406','goso@daum.net','대구');
INSERT INTO `Student` VALUES ('22311011',31,'P31104','김연아','651021-1234567','010-5101-6510','yuna@daum.net','대구');
INSERT INTO `Student` VALUES ('22311014',31,'P31104','유재석','721128-1234567','010-6101-7211',NULL,'부산');
INSERT INTO `Student` VALUES ('22401001',40,'P40101','강호동','920907-1234567','010-7103-9209',NULL,'부산');

INSERT INTO `Student` VALUES ('22401002',40,'P40101','조인성','891209-1234567','010-7104-8912','join@gmail.com','광주');
INSERT INTO `Student` VALUES ('22421003',42,'P42103','강동원','770314-1234567','010-8101-7703','dong@naver.com','광주');

INSERT INTO Professors VALUES ('P10101',10,'김유신','750120-1234567','010-1101-1976','kimys@hg.ac.kr','서울');
INSERT INTO Professors VALUES ('P10102',10,'계백','740610-1234567','010-1102-1975','haevack@hg.ac.kr','서울');
INSERT INTO Professors VALUES ('P11101',11,'김관창','880529-1234567','010-1103-1989','kwanch@hg.ac.kr','대전');
INSERT INTO Professors VALUES ('P11103',11,'김춘추','780412-1234567','010-2101-1979','kimcc@hg.ac.kr','대전');
INSERT INTO Professors VALUES ('P11104',11,'이사부','650911-1234567','010-2104-1966','leesabu@hg.ac.kr','대전');

INSERT INTO Professors VALUES ('P22110',22,'장보고','780727-1234567','010-3101-1979','jangbg@hg.ac.kr','대구');
INSERT INTO Professors VALUES ('P23102',23,'선덕여왕','830614-1234567','010-4101-1984','gueen@hg.ac.kr','대구');
INSERT INTO Professors VALUES ('P31101',31,'강감찬','641020-1234567','010-5101-1965','kang@hg.ac.kr','대구');
INSERT INTO Professors VALUES ('P31104',31,'신사임당','711127-1234567','010-6101-1972','sinsa@hg.ac.kr','부산');
INSERT INTO Professors VALUES ('P40101',40,'이이','910906-1234567','010-7103-1992','leelee@hg.ac.kr','부산');

INSERT INTO Professors VALUES ('P40102',40,'이황','881208-1234567','010-7104-1989','hwang@hg.ac.kr','광주');
INSERT INTO Professors VALUES ('P42103',42,'송상현','760313-1234567','010-8101-1977','ssh@hg.ac.kr','광주');

INSERT `Departments` VALUES (10,'국어국문학과','051-510-1010');
INSERT `Departments` VALUES (11,'영어영문학과','051-510-1011');
INSERT `Departments` VALUES (20,'경영학과','051-510-1020');
INSERT `Departments` VALUES (21,'경제학과','051-510-1021');
INSERT `Departments` VALUES (22,'정치외교학과','051-510-1022');
INSERT `Departments` VALUES (23,'사회복지학과','051-510-1023');
INSERT `Departments` VALUES (30,'수학과','051-510-1030');
INSERT `Departments` VALUES (31,'통계학과','051-510-1031');
INSERT `Departments` VALUES (32,'생명과학과','051-510-1032');
INSERT `Departments` VALUES (40,'기계공학과','051-510-1040');
INSERT `Departments` VALUES (41,'전자공학과','051-510-1041');
INSERT `Departments` VALUES (42,'컴퓨터공학과','051-510-1042');

INSERT `Lectures` VALUES ('101001','P10101','대학 글쓰기',2,10,'본102');
INSERT `Lectures` VALUES ('101002','P10102','한국어음운론',3,30,'본102');
INSERT `Lectures` VALUES ('101003','P10102','한국현대문학사',3,30,'본103');
INSERT `Lectures` VALUES ('111011','P11103','중세영문학',3,25,'본201');
INSERT `Lectures` VALUES ('111012','P11104','영미시',3,25,'본201');
INSERT `Lectures` VALUES ('231110','P23102','사회복지학개론',1,8,'별관103');
INSERT `Lectures` VALUES ('311002','P31101','통계학의 이해',2,16,'별관303');
INSERT `Lectures` VALUES ('311003','P31104','기초 통계학',3,26,'별관303');
INSERT `Lectures` VALUES ('401019','P40101','기계역학',3,36,'공학관 102');
INSERT `Lectures` VALUES ('421012','P42103','데이터베이스',3,32,'공학관 103');

INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('20101001','101001','P10101');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('20101001','101002','P10102');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('20111013','111011','P11103');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('21231002','231110','P23102');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('22401001','401019','P40101');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('22401001','421012','P42103');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('20101001','101003','P10102');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('22421003','311003','P31104');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('22421003','421012','P42103');
INSERT `Register`(`stdNo`,`lecNo`,`proNo`) VALUES ('20111013','111012','P11104');

 

 

 

 

 

 

---

 

✔ 문제 1

모든 수강신청 내역에서 학생번호, 학생명, 강좌번호, 강좌명, 교수번호, 교수명을 조회하시오

 

 

작성 Query

SELECT 
	a.`stdNo`,
	b.`stdName`,
	a.`lecNo`,
	c.`lecName`,
	a.`proNo`,
	d.`proName`
FROM `Register` AS a 
JOIN `Student` AS b ON a.stdNo = b.stdNo
JOIN `Lectures` AS c ON a.lecNo = c.lecNo
JOIN `Professors` AS d ON a.proNo = d.proNo;

 

 

칼럼 지정해주기_ Error (1052)

Error 1052: Column in field list is ambiguous 해결

칼럼이 많기에, 각 칼럼마다 어떤 테이블의 칼럼을 쓸 것인지 지정을 해준다 ex) a = Register 테이블 / a. `stdNo` -> Register 테이블에서 stdNo 칼럼

 

 

 

 

 

 

 

✔ 문제 2

수강신청 테이블에 출석점수, 중간고사점수, 기말고사점수를 임의로 입력하시오(1-100사이)

 

 

작성 Query

UPDATE `Register` SET 
	`regAttenScore` = CEIL(RAND() * 100),
	`regMidScore`   = CEIL(RAND() * 100),
	`regFinalScore` = CEIL(RAND() * 100);

 

 

랜덤 함수+ 올림값 함수_ CEIL(RAND() * 100)

랜덤함수와 올림값 함수의 결합 = 임의의 정수값 출력

RAND() - 랜덤의 실수값 출력
CEIL() - 값을 올림
CELI(RAND() * 100) ⇒ 10~100사이의 정수 출력

 

 

 

 

 

 

 

 

✔ 문제 3

수강신청 테이블에 입력한 출석점수, 중간고사점수, 기말고사 점수들 모두 합산 후 평균을 구해 총점에 입력하시오

 

 

작성 Query

UPDATE `Register` SET 
	`regTotal` = (`regAttenScore` + `regMidScore` +`regFinalScore` ) /3;

 

 

평균 구하고 입력 동시에 하기_ UPDATE

평균 구하기
(a+b+c)/3 = 평균값

입력하기(데이터 업데이트)
UPDATE `테이블명` SET `칼럼명` = ;

 

 

 

 

 

 

 

 

✔ 문제 4

수강신청 테이블에 총점을 기준으로 A~F 등급을 입력하시오

 

 

작성 Query

UPDATE `Register` SET
	`regGrade` =  if(`regTotal` >= 90, 'A',
		if(`regTotal` >= 80, 'B',
		if(`regTotal` >= 70, 'C',
		if(`regTotal` >= 60, 'D', 'E'))));

 

 

등급 부여하기_ UPDATE & IF

등급 구하기
if ( `칼럼명` >= 기준점, 'A',
if ( `칼럼명` >= 기준점, 'B',
if ( `칼럼명` >= 기준점, 'C',
if ( `칼럼명` >= 기준점, 'D', 'E')))): #조건식에 해당하면 'D' 아니면 'E'

입력하기(데이터 업데이트)
UPDATE `테이블명` SET `칼럼명` = ;