데이터베이스 쿼리 작성

- 쿼리 생성하기
 
[MySQL로 배우는 데이터베이스 개론과 실습]을 통해 MySQL workbench를 사용하여 쿼리 작성법을 배웠다. 과제로 주어진 데이터베이스 테이블 생성부터 쿼리 생성까지 내용을 정리하고자 한다.

풀이 시 주의사항
* join 사용하지 않을 것
* limit 사용하지 않을 것
 
생성한 SQL query
(실명은 가명처리 혹은 데이터 마스킹 처리)
 
 
1. 변의주 교수님이나 왕이샹 교수님이 가르치는 과목이름, 시간, 장소를 교수 이름의 내림차순으로 보이시오.

SELECT p.pname, c.cname, l.time, l.room
FROM professor p , lecture l, course c
WHERE (p.pname = '변의주' OR p.pname = '왕이샹')
AND p.pnum = l.pnum
AND l.cnum = c.cnum
ORDER BY p.pname desc;

 
2. 목요일 6교시에 시작하는 과목이름과 강의교수이름, 강의실 번호와 시간을 보이시오.

SELECT c.cname, p.pname, l.room, l.time
FROM course c, lecture l, professor p
WHERE time = 46
AND c.cnum = l.cnum
AND p.pnum = l.pnum
ORDER BY l.room;

 
3. 이름이 '윤'으로 시작하는 학생의 수강과목, 수강과목의 시수, 점수를 보이시오.

SELECT s.sname AS 학생이름, c.cname AS 수강과목, c.hrs AS 시수, e.grade AS 점수
FROM student s, course c, enrol e
WHERE s.snum = e.snum
AND c.cnum = e.cnum
AND s.sname LIKE '윤_%';

 
4. 각 학과별 2학년 학생 중 성적이 90점 이상인 학생의 수를 내림차순으로 보이시오.(학과이름, 학생수)
-- nd_Grade 뷰 생성:
2학년 중 과목 성적이 90점 이상인 학생의 학과이름, 학번, 학생이름, 점수를 담은 뷰

CREATE VIEW nd_Grade AS
SELECT d.dname AS 학과이름, s.snum AS 학번, s.sname AS 학생이름, e.grade AS 점수
FROM student s, enrol e, department d
WHERE d.dnum = s.major
AND s.snum = e.snum
AND s.year = 2
AND e.grade >= 90;

 
-- nd_Grade 뷰 보이기.

SELECT * FROM nd_Grade;

 
-- 뷰를 사용한 쿼리

SELECT 학과이름, COUNT(DISTINCT 학번) AS '90점 이상 학생 수'
FROM nd_Grade
GROUP BY 학과이름
ORDER BY COUNT(DISTINCT 학번) DESC;

* 최종적으로 보이고자 하는 것은 학과이름과 학생 수이다. 이를 구하기 위해서는 학과와 학번, 학생이름, 학생의 점수가 필요하며 3개의 테이블을 가져와 서로 연결해야 한다. 또한 조건인 학년을 2로 설정하고 점수를 90점 이상으로 준 뷰를 생성했다. 이제 뷰를 사용한 쿼리를 작성한다. 학과별로 학생의 수를 구해야 하기 때문에 한 학생이 2번 이상의 시험에서 90점 이상을 받았을 경우를 제외하고자 학번의 중복을 distinct로 제외하였고 order by를 통해 90점 이상 학생 수 내림차순으로 정렬하였다. order by줄에서 COUNT(DISTINCT 학번) 대신 '90점 이상 학생 수'를 사용해도 된다.
 

5. 2학년 학생 중 과목 성적이 90점 이상인 학생의 지도를 담당하는 교수 이름과 교수번호, 지도를 맡는 학생의 학번, 학생이름, 학점을 보이시오.
 
-- adv_Professor 뷰 생성:
학생의 지도를 담당하는 교수이름, 교수번호, 지도를 맡는 학생의 학년, 학번, 학점을 담은 뷰

CREATE VIEW adv_Professor AS
SELECT p.pnum AS 교수번호, p.pname AS 교수이름, s.year AS 학년, s.snum AS 학번, s.qpa AS 학점
FROM professor p, student s
WHERE p.pnum = s.advise
ORDER BY p.pnum;

 
-- adv_Professor 뷰 보이기

SELECT * FROM adv_Professor;

 
-- 2단계 뷰를 사용한 query

SELECT DISTINCT a.교수번호, a.교수이름 AS 지도교수, a.학번, n.학생이름, a.학점
FROM adv_Professor a, nd_Grade n
WHERE a.학번 = n.학번;

* professor 테이블과 student 테이블을 연결하기 위해 외래키로 참조하고 있는 advise를 사용한다. 교수번호 오름차순 정렬하여 뷰를 생성했다. 이제 뷰를 사용한 쿼리를 작성한다. 속성을 보면 교수이름을 as를 사용해 지도교수라는 별칭을 지정했다. 다음, 해당 뷰와 이전에 생성한 뷰를 불러와 동일한 속성인 학번으로 조인시킨다.
 

6. 평균 평점이 전체 평균 평점보다 높은 학생들의 학번, 학생이름, 학점, 지도교수명 (학점 내림차순)

SELECT s.snum AS 학번, s.sname AS 학생이름, s.qpa AS 학점, p.pname AS 지도교수명
FROM student s, professor p
WHERE s.advise = p.pnum
AND s.qpa > (SELECT AVG(st.qpa)
FROM student st)
ORDER BY s.qpa DESC;

* 학점(qpa)이 전체 학생들의 평균 학점보다 높은 행들을 가져오기 위해 서브쿼리를 사용하여 전체 학생들의 평균 학점을 계산하고, 이를 기준으로 조건을 걸고 있다.
 
 
7. 학과명과 학년을 보이고 수강과목별 최고점을 받은 학생과 최저점을 받은 학생의 점수차이가 5점 이하인 과목의 강의시간 보이시오(점수차이 오름차순)
 
-- sco_Minus 뷰 생성:
각 학과, 학년, 수강과목별 최고점을 받은 학생과 최고점을 받은 학생의 점수차이

CREATE VIEW sco_Minus AS
SELECT d.dname AS 학과명, s.year AS 학년, c.cname AS 수강과목, c.cnum AS 과목번호,
MAX(e.grade) AS 최고점,
MIN(e.grade) AS 최저점,
MAX(e.grade) - MIN(e.grade) AS 점수차이
FROM enrol e, course c, student s, department d
WHERE e.cnum = c.cnum
AND e.snum = s.snum
AND s.major = d.dnum
GROUP BY d.dname, s.year, c.cname, c.cnum;

 
-- sco_Minus 뷰 보이기

SELECT * FROM sco_Minus;

 
-- 뷰를 사용한 query

SELECT 수강과목 AS 과목, 점수차이
FROM sco_Minus
WHERE 점수차이 <= 5
ORDER BY 점수차이;

*  max를 사용하여 최고점을 계산하고, min을 사용하여 최저점을 계산하여 최고점과 최저점의 차이를 구하는 뷰를 생성한다. 뷰를 사용하여 점수차이가 5점 이하인 행을 선택하고 점수차로 오름차순 정렬한다.
 

8. 최고점을 받은 학생과 최저점을 받은 학생의 점수차이가 가장 작은 과목의 강의시간 보이시오
 
-- 뷰를 사용한 query

SELECT sc.수강과목, sc.점수차이, l.time AS 강의시간
FROM sco_Minus sc, lecture l
WHERE sc.과목번호 = l.cnum
AND sc.점수차이 = (
        SELECT MIN(점수차이)
        FROM sco_Minus);

* 뷰를 사용한 두 번째 query이다. 서브쿼리를 사용하여 최소 점수차이를 가진 행을 선택한다. 이 서브쿼리는 sco_Minus 뷰에서 최소 점수차이를 찾아낸다.
 

9. 각 학과별, 학년별로 교수가 지도하는 학생 중 최고 학점을 받은 1학년 학생
 
-- adv_highQPA 뷰 생성:
각 학과별, 학년별로 교수가 지도하는 학생 중 최고 학점을 받은 학생을 담은 뷰(학과번호, 학년 오름차순)

CREATE VIEW adv_highQPA AS
SELECT p.pnum AS 교수번호, p.pname AS 지도교수명, s.snum AS 학번, s.sname AS 학생명, s.year AS 학년, s.qpa AS 학점, s.major AS 학과번호
FROM professor p, student s
WHERE p.pnum = s.advise
AND (s.major, s.year, s.qpa) IN (
         SELECT s2.major, s2.year, MAX(s2.qpa)
         FROM student s2
         WHERE s2.advise = s.advise
         GROUP BY s2.major, s2.year)
ORDER BY s.major, s.year;

 
-- st_Grade 뷰 생성:
1학년 학생의 학번, 학생명, 학년, 학점을 담은 뷰

CREATE VIEW st_Grade AS
SELECT snum AS 학번, sname AS 학생명, year AS 학년, qpa AS 학점
FROM student
WHERE year = 1;

 
-- 2단계 뷰를 사용한 query

SELECT st.학번, st.학생명, ad.지도교수명, ad.학년, ad.학점
FROM st_Grade st, adv_highQPA ad
WHERE st.학번 = ad.학번;

* 2단계 뷰를 사용하기 위해 지도교수가 지도하는 학생 중 최고점을 받은 학생을 구하는 뷰를 생성하고, 1학생 학생의 정보를 담은 뷰를 생성했다. 두 개의 뷰를 조인하여 지도교수별 최고점을 받은 1학년 학생을 구했다.
 

10. 세무00학과 2학점 과목과 해당 과목을 수강하는 학생이름, 학번을 보이시오(학번 순)

SELECT d.dname, c.cname, c.credit, s.sname, s.snum
FROM student s, course c, enrol e, department d
WHERE s.snum = e.snum
AND e.cnum = c.cnum
AND s.major = d.dnum
AND d.dname = '세무'
AND c.credit = 2
ORDER BY s.snum;

* 조인할 테이블이 많은 query이다. 외래키로 사용되는 테이블들을 모두 조인하여 특정 학과에서 2학점 과목을 수강하는 학생들의 정보를 검색한다.학번을 기준으로 오름차순 정렬된다.