[아이티윌 빅데이터 52기] Day 5 고급 데이터 조회 2 / 데이터 베이스 구축

고급 데이터 조회 2 / 데이터 베이스 구축

<학습 내용>

-INNER/OUTER 외의 JOIN ( SELF JOIN ,EQUI JOIN ,CROSS JOIN)

-서브쿼리 (단일행 / 다중행 / 스칼라 / 인라인뷰)

-데이터 베이스 관리

-테이블 관리

 

 

 

 

EQUI JOIN

기본적으로 INNER JOIN 과 동일함

ANSI SQL 은 모든 DBMS 가 따르는 표준적인 문법

2개 이상의 테이블에서 데이터를 조회하면서, WHRER 절을 통해 JOIN 조건을 명시함

FROM 절에는 , 를 기준으로 테이블 명시

 

 

JOIN 조건을 써준 후에 추가적인 필터링은 AND 로 이어줄 수 있음

 

 

 

 

CROSS JOIN

EQUI JOIN 에서 JOIN 조건이 제외된 경우 

즉, 조합 가능한 모든 경우의 수가 생성 (카테시안곱)

 

 

SELF JOIN

하나의 테이블에서 두개의 별칭을 사용해서 마치 두 테이블처럼 조인하는 방식

FROM 절과 JOIN 절의 테이블이 동일

계층 구조 표현 / 같은 집단 내 비교/중복 이름 값 찾기

 

 

 

<연습 문제>

1.EQUI JOIN을 사용하여 학생 이름과 지도 교수 이름을 함께 출력하시오

 

SELECT S.NAME, P.NAME FROM STUDENTS S,PROFESSORS P
WHERE S.PROFESSOR_ID = P.ID

 

 

2.EQUI JOIN을 사용하여 수강신청한 학생의 이름과 수강한 과목명을 출력하시오

 

SELECT S.NAME, SB.NAME FROM STUDENTS S, SUBJECTS SB, ENROLLMENTS E
WHERE S.ID = E.STUDENT_ID AND E.SUBJECT_ID = SB.ID

 

3.EQUI JOIN을 사용하여 학생이 수강한 과목과 그 과목의 학과명을 출력하시오

SELECT  S.NAME,
        SB.NAME,
        D.DNAME
FROM STUDENTS S, ENROLLMENTS E ,SUBJECTS SB, DEPARTMENTS D
WHERE S.ID=E.STUDENT_ID AND E.SUBJECT_ID = SB.ID AND SB.DEPARTMENT_ID = D.ID;

 

4.EQUI JOIN을 사용하여 2024년 이후 입사한 교수의 이름과 소속 학과명을 출력하시오

 

SELECT  P.NAME, D.DNAME FROM PROFESSORS P , DEPARTMENTS D
WHERE (P.DEPARTMENT_ID = D.ID) AND DATE_FORMAT(HIREDATE, '%Y-%m-%d') >= '2024-01-01'

 

 

5.같은 학년에 재학 중인 학생 쌍을 모두 조회하시오.

단, 학생 번호가 작은 사람을 먼저 표시하고, 자기 자신과의 비교는 제외하세요

SELECT a.name AS 학생1,
               b.name AS 학생2,
               a.grade AS 학년
FROM students a JOIN students b ON a.grade = b.grade AND a.id < b.id;




SELECT A.ID, A.NAME,A.GRADE , B.ID, B.NAME,B.GRADE
FROM STUDENTS A
JOIN STUDENTS B 
WHERE A.ID != B.ID AND A.GRADE = B.GRADE
ORDER BY A.ID

 

 

 

 

서브쿼리1 - where 절

다른 SQL 문 내부에 중첩되어 사용되는 SELECT 문으로

조건을 비교하거나 추출, 필터링 등을 할 때 쓰임

괄호 안의 SELECT 문이 먼저 실행되어 외부 쿼리에 값을 전달함

 

 

*단일 값이 나오면 = 를 쓰고, 여러 값이 나오면 in 활용

 

 

<연습 문제>

 

1.학생 전체의 평균 몸무게보다 몸무게가 많이 나가는 1학년 학생의 이름, 성별, 키, 몸무게를 조회하시오

#오답
SELECT NAME,GENDER,HEIGHT,WEIGHT FROM STUDENTS
WHERE WEIGHT >= (SELECT AVG(WEIGHT) FROM STUDENTS WHERE GRADE =1);



#정답

SELECT NAME,GENDER,HEIGHT,WEIGHT FROM STUDENTS
WHERE grade =1 and WEIGHT >= (SELECT AVG(WEIGHT) FROM STUDENTS)

 

*GRADE =1 조건을 서브쿼리가 아닌 메인 쿼리에서 필터링 해야 했음

GRADE =1 조건이 서브쿼리 안에 있으면 1학년의 평균 몸무게를 기준으로 연산

 

2.평균 급여 이상을 받는 교수들 중, 재직 상태인 교수의 이름과 급여를 출력하시오

#오답
SELECT NAME,SAL FROM PROFESSORS
WHERE SAL >= (SELECT AVG(SAL) FROM PROFESSORS WHERE STATUS ='재직');



#정답
SELECT NAME,SAL FROM PROFESSORS
WHERE SAL >= (SELECT AVG(SAL) FROM PROFESSORS ) AND STATUS ='재직';

 

1번과 마찬가지로, 재직 조건을 서브쿼리에 넣게 되면,

평균 급여 이상을 받는 재직 중인 교수들 중 ~ 이라는 조건으로 바뀜

 

 

3.가장 낮은 학점을 부여하는 과목을 담당하는 교수의 이름, 직급, 급여, 소속학과 이름을 조회하시오

SELECT P.NAME, P.POSITION, P.SAL, D.DNAME ,S.CREDIT  FROM PROFESSORS P
INNER JOIN SUBJECTS S ON P.ID = S. PROFESSOR_ID
INNER JOIN DEPARTMENTS D ON  P.DEPARTMENT_ID = D.ID
WHERE S.CREDIT = (SELECT MIN(CREDIT) FROM SUBJECTS);

 

4.수강신청한 학생 수가 3명 이상인 과목의 이름을 조회하시오

 

SELECT * FROM SUBJECTS
WHERE ID IN
(SELECT SUBJECT_ID FROM ENROLLMENTS GROUP BY SUBJECT_ID HAVING COUNT(STUDENT_ID)  >=3);

 

 

5.과목을 한 번도 수강하지 않은 학생은 모두 몇 명인지 조회하시오

 

SELECT COUNT(ID) FROM STUDENTS
WHERE ID IN
( SELECT ID FROM STUDENTS S LEFT JOIN ENROLLMENTS E ON S.ID=E.STUDENT_ID WHERE STUDENT_ID IS NULL)


#같은 결과, 다른 표현
SELECT COUNT(*) NUM FROM students
WHERE id NOT IN ( SELECT DISTINCT student_id FROM enrollments );

 

 

 

서브쿼리2 - FROM 절 

스칼라 서브쿼리의 종류는 두가지가 있음

 

상관 서브쿼리 - 메인 쿼리를 스캔하면서 효율이 안 좋아질 수 있음

비상관 서브쿼리 - 서브쿼리가 메인쿼리와 상관없이 독립적으로 실행

 

 

<연습 문제>

 

1. enrollments 에서 학생별 평균 점수(Null 제외)를 계산한 뒤, 평균이 85점 이상인 학생의 id , name , avg_score 를 출 력하시오. 이때 학생별 평균 점수 계산은 FROM절 인라인 뷰로 작성하고, 결과는 avg_score 내림차순으로 정렬하시오. (점수가 모두 NULL인 학생은 평균 0으로 간주

 

 

 

2. subjects 의 각 과목 행에 대해, professor_id 를 이용하여 담당 교수의 이름을 스칼라 서브쿼리로 조회하여 professor_name 열에 표시하시오. 담당 교수가 없으면 ' 미배정 ' 으로 출력하시오. 결과는 department_id , id 순으 로정렬하시오

 

 

 

3. 각학과(departments)에 대해 개설 과목 수와 **재학생 수( students.status=' 재학 ' )**를 함께 보여주는 보고서를 만드시오. 인라인 뷰를 두 개( subjects 집계, students 집계) 만든 뒤, departments 에 LEFT JOIN하여 학과가 비어 있어도(0 건) 표시되도록 하시오

 

 

 

4. 학생별 **가장 최근 수강신청일(last_enroll)**을 인라인 뷰로 구한 뒤, 그 값이 해당 학과에서의 최대 최근 수강일과 동일한 학생만 조회하시오. 즉 , 학과별로 “가장 최근에 수강신청한 학생(들)”을 찾는 문제입니다. 출력: student_id, name, department_id, last_enroll

 

 

 

 

데이터 베이스 관리

앞서 배운 내용들은 데이터를 조회 /입력/수정/삭제 즉, SELECT, INSERT,UPDATE,DELETE 하면서

기존의 데이터를 조작했음

 

위의 4가지 SELECT, INSERT,UPDATE,DELETE 는 데이터 조작어라고 부르고 DML (DATA MANIPULATION LANGUAGE) 라고 함

 

 

이제 하려는 내용은 DB 자체를 생성/삭제 하고, DB 에 테이블을 생성/삭제/수정할 내용

 

즉 데이터의 구조를 정의하는 것으로 DATA DEFINITION LANGUAGE DDL 이라고 부름

 

DCL DATA CREATE LANGUAGE

 

 

 

 

특정 컴퓨터에 접속하려면 IP 주소가 필요함

각 컴퓨터마다 DNS

 

 

DROP 데이터 베이스 삭제 (되돌릴 수 없음)

 

데이터 베이스 백업하기

 

 

 

 

테이블 관리

컬럼의 제약 조건

데이터 타입을 지정한 후에 필요한 제약 조건을 공백으로 구분하여 나열할 수 있음