DB 4일차 4()

학생 이승아 추가, 과목 web 추가

/*학생 기준*/	
SELECT
	sm.id AS '순번',
	sbm.name AS '과목명',
	sm.name AS '수강생명',
	em.name AS '담당자명',
	stm.name AS '상태'
FROM
	student_mst sm
	LEFT OUTER join course_mst cm ON(cm.student_id = sm.id)
	LEFT OUTER JOIN subject_mst sbm ON(sbm.id = cm.subject_id)
	LEFT OUTER JOIN student_dtl sd ON(sd.id = sm.id)
	LEFT OUTER JOIN emp_mst em ON(em.id = sd.emp_id)
	LEFT OUTER JOIN status_mst stm ON(stm.id = cm.status_id);

 

학생운 있지믄 수강 안한 학생이 있다.

 

/**/

셀렉 하고 order by

SELECT
	sm.id AS '순번',
	sbm.name AS '과목명',
	sm.name AS '수강생명',
	em.name AS '담당자명',
	stm.name AS '상태'
FROM
	student_mst sm
	LEFT OUTER join course_mst cm ON(cm.student_id = sm.id)
	LEFT OUTER JOIN subject_mst sbm ON(sbm.id = cm.subject_id)
	LEFT OUTER JOIN student_dtl sd ON(sd.id = sm.id)
	LEFT OUTER JOIN emp_mst em ON(em.id = sd.emp_id)
	LEFT OUTER JOIN status_mst stm ON(stm.id = cm.status_id)

ORDER BY
	`순번`,
	`과목명`	 /*알파벳 순서*/
;

 

SELECT
	sm.id AS '순번',
	sbm.name AS '과목명',
	sm.name AS '수강생명',
	em.name AS '담당자명',
	stm.name AS '상태'
FROM
	student_mst sm
	LEFT OUTER join course_mst cm ON(cm.student_id = sm.id)
	LEFT OUTER JOIN subject_mst sbm ON(sbm.id = cm.subject_id)
	LEFT OUTER JOIN student_dtl sd ON(sd.id = sm.id)
	LEFT OUTER JOIN emp_mst em ON(em.id = sd.emp_id)
	LEFT OUTER JOIN status_mst stm ON(stm.id = cm.status_id)

ORDER BY /*알파벳 순서*/ /*순번*/
	sm.id,  /*table 에 넣은 순서대로 정렬*/
	sbm.id;

 

 

백엔드 각 과목의 시험 점수 넣고 평균도 내보자

 

들어가기전

SELECT
	course_id,
	SUM(score) AS `총합`,
	AVG(score) AS `평균`

FROM
	score_mst
GROUP BY
	course_id; /*같은 것을 묶어준다.*/

평균내고 묶어보자

 

SELECT
	course_id,
	SUM(score) AS `총합`,
	AVG(score) AS `평균`

FROM
	score_mst
GROUP BY /*같은 것을 묶어준다.*/
	course_id

HAVING 
 `총합` >100;