⏰ 2024.10.24 (D+11)
1.그룹함수(집계함수)
🔖 중요]
- 그룹함수는 SELECT절에서 단독으로 쓸 수 있으나 다른 칼럼 정보를 함께 출력할 수 없다.
1) COUNT(*)
테이블의 전체 레코드 개수를 가져온다
조건식 ⭐]
SELECT COUNT(*) FROM 테이블명예제 📖]
❗ 전체 emp 테이블의 개수를 추출하는 경우,
SELECT COUNT(*) FROM EMP;예제 📖]
❗ 특정 emp 테이블의 mgr데이터의 개수를 추출하는 경우,
select count(mgr) from emp;
2) AVG(컬럼명)
해당 컬럼의 전체 평균값을 구한다.
조건식 ⭐]
SELECT AVG(컬럼명) FROM 테이블명예제 📖]
❗ EMP 테이블의 SAL컬럼의 전체 평균을 추출하는 경우,
SELECT AVG(SAL) FROM EMP;
3) MAX(컬럼명)
해당 컬럼의 가장 큰 값을 구한다.
조건식 ⭐]
SELECT MAX(컬럼명) FROM 테이블명예제 📖]
❗ EMP 테이블의 SAL컬럼의 가장 큰 값을 추출하는 경우,
SELECT MAX(SAL) FROM EMP;
4) MIN(컬럼명)
해당 컬럼의 가장 작은 값을 구한다
SELECT MIN(컬럼명) FROM 테이블명
SELECT SUM(컬러명) FROM 테이블명 => 합계 구하기
예제 📖]
❗ EMP 테이블의 SAL컬럼의 가장 큰 값을 추출하는 경우,
SELECT MIN(SAL) FROM EMP;
2. GROUP BY
🔖 중요]
- GROUP BY는 ORDER BY전에 기술해야 한다.
- 그룹에 대한 조건은 HAVING절에서 기술해야 한다.
- WHERE조건과 함께 쓰면 WHERE조건절에서 먼저 필터링 한후 그룹화 한다.
예제 📖]
select
avg(sal),
deptno,
job
from emp
group by deptno;💡오류가 발생한 이유 : job가 그룹핑이 되지 않았기 때문에 오류 발생
위의 오류사항을 출력할 수 있도록 수정한 경우,
select
avg(sal),
deptno,
job
from emp
group by deptno, job;
예제 📖]
select
avg(sal) 평균,
deptno
from emp
group by deptno
having 평균 >=2500;💡오류가 발생한 이유 : emp테이블에서 평균이라는 컬럼이 없기 때문
위의 오류사항을 출력할 수 있도록 수정한 경우,
❗ having avg(sal)으로 변경해야 함select
avg(sal) 평균,
deptno
from emp
group by deptno
having avg(sal) >=1000
order by 평균 DESC;
3. 조인문
1) INNER JOIN
- 두 개 이상의 테이블로부터 자료를 검색하기 위해서 join을 사용
- 일반적으로 Primary Key와 Foreign Key을 사용하여 join 하는 경우가 대부분
- 가장 많이 사용되는 조인문으로 테이블 간에 연결 조건을 모두 만족하는 행 을 검색하는데 사용
- 검색시 검색되는 컬럼이 조인하는 테이블 모두에 존재한다면 반드시 컬럼명 에 테이블 이름을 "테이블명.컬러명" 의 형태로 기술
- INNER JOIN은 자식 테이블(FK가 있는 테이블)을 기준으로 모든 데이타를 가져온다.
참고사항 💡]
자식테이블에 전체 레코드가 10개이면 INNER JOIN한 결과의 레코드 수는 10개
한 개 테이블 조인
표준 SQL식 INNER JOIN
SELECT 컬럼명…..
From 테이블명1 [INNER] JOIN 테이블명2 ON 테이블명1.PK컬럼명 = 테이블명2.FK컬럼명
--JOIN 테이블3 ON 테이블1.PK = 테이블3.FK
--JOIN 테이블4 ON 테이블4.FK =테이블2.PK
세 개 이상의 테이블 조인
※ 각 테이블간의 관계(부모-자식)를 먼저 파악해야 한다.
표준 SQL식 INNER JOIN
가정
테이블명 1는 부모이고 테이블명 2은 자식
테이블명 2는 부모이고 테이블명 3는자식
테이블명 1과 테이블명 3는 부모 자식관계가 아님SELECT 컬럼명…..
From 테이블명1 [INNER] JOIN 테이블명2
ON 테이블명1.PK컬럼명 = 테이블명2.FK컬럼명
INNER JOIN 테이블명3
ON 테이블명2.PK컬럼명 = 테이블명3.FK컬러명※ INNER JOIN ~ON을 사용하는 경우,
WHERE절 사용시 WHERE절은 ON 다음에 기술해야 한다.
💡조인시 찾는 조건 추가하는 방법 1
SELECT E.ENAME ,E.SAL,D.DNAME,D.LOC,D.DEPTNO
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE SAL >=3000
💡조인시 찾는 조건 추가하는 방법 2
SELECT E.ENAME ,E.SAL,D.DNAME,D.LOC,D.DEPTNO
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO AND SAL >=3000
📌 테이블에 별칭 부여
- 테이블에 별칭을 부여하여 긴 테이블 명을 간단하게 사용
- 테이블 이름대신에 별칭을 사용
- SQL코드를 적게 사용하여 코딩 시간이 절약되고 메모리를 보다 적게 사용
- 테이블 별칭은 30자까지 사용 가능하지만 짧을수록 좋다.
참고사항 💡]
테이블명 별칭[o]
테이블명 as 별칭[x]:as는 사용불가
INNER JOIN 예제 📖]
📌 참고사항 : PK는 부모, FK 자식 관계파악 먼저 확인 후 문제 풀이
select
ename,
job,
sal,
d.dname,
e.deptno
from emp e
inner join dept d
on e.deptno = d.deptno;
2) OUTER JOIN
- 두 테이블에 조인 조건이 정확히 일치 하지 않더라도 어느 한쪽 테이블에서 결과값을 모두 가져오는 JOIN문
- OUTER JOIN문을 사용할때는 반드시 OUTER 전에 데이터를 어느 쪽 테이블에서 가져올지 기술
- 즉 왼쪽인지(LEFT) 오른쪽인지(RIGHT) 아니면 양쪽 테이블(FULL)인지 반드시 기술
- 자식을 기준으로 아웃터 조인하면 INNER 조인과 같다. (※보통 부모를 기준으로 아웃터 조인)
- 공통 컬럼도 부모 기준으로 SELECT한다
- 관계형 데이타베이스에서는 Full outer조인은 의미 없다
- LEFT OUTER조인은 보통 왼쪽에 부모,RIGHT OUTER은 오른쪽에 부모를 위치 시킨다
(1) LEFT OUTER JOIN
: LEFT OUTER조인은 보통 왼쪽에 부모, RIGHT OUTER은 오른쪽에 부모를 위치 시킨다
조건식 ⭐]
SELECT 컬럼명…..
From 테이블명 1 LEFT OUTER JOIN 테이블명 2
ON 조인 조건예제 📖]
JOIN문 왼쪽을 중심으로 왼쪽에 있는 테이블에서 데이터를 모두 가져온다.select
ename,
job,
sal,
d.dname,
e.deptno
from emp e LEFT OUTER JOIN dept d
on e.deptno = d.deptno;
(2) RIGHT OUTER JOIN
: JOIN문 오른쪽을 중심으로 오른쪽에 있는 테이블에서 데이터를 모두 가져온다.
조건식 ⭐]
SELECT 컬럼명…..
From 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 조인 조건예제 📖]
select
ename,
job,
sal,
d.dname,
e.deptno
from emp e RIGHT OUTER JOIN dept d
on e.deptno = d.deptno;
(3) FULL OUTER JOIN
: JOIN문 양쪽에 있는 모든 테이블에서 데이터를 모두 가져온다.
조건식 ⭐]
SELECT 컬럼명…..
From 테이블명1 FULL OUTER JOIN 테이블명2
ON 조인 조건
4. SQL 문제 12 ~ 23번 (제외 16 ~ 18번)
📙문제 12]
EMP테이블에서 연봉이 1000이상이고 이름이 S로 끝나는 직원의 총 수를 구해라.select
count(*)
from emp
where sal >=1000 and ename like '%S';
📙문제 13]
직업이 SALESMAN인 직원들의 연봉 평균을 구해라select
AVG(sal)
from emp
where job = 'SALESMAN';
📙문제 14]
hr계정에서 부서별 최고 연봉을 구해서 최고 연봉과 부서 코드 를 출력 하여라 그리고 최고 연봉순으로 내림 차순 하여라.select
department_id as 부서코드,
max (SALARY) as 최고연봉
from employees
group by department_id
order by max(SALARY) desc;
📙문제 15]
hr계정에서 부서별 평균 연봉을 구해서 평균 연봉과 부서 코드 를 출력하여라.
단, 평균 연봉을 구할때 연봉이 3000이하인 직원 과 커미션을 받는 직원은 제외하여라 출력 시에는 평균 연봉 순으로 내림 차순 하여라.select
department_id as 부서코드,
avg (SALARY) as 평균연봉
from employees
where SALARY > 3000 and COMMISSION_PCT is null
group by department_id
order by avg (SALARY) desc;
📙문제 19]
HR계정에서EMPLOYEES,LOCATIONS 및 DEPARTMENTS테이블을 JOIN해서 이름(LAST_NAME || FIRST_NAME),연봉,부서명,부서가 속한 도시명을 출력해라
(표준 INNER JOIN문과 ORACLE식 JOIN 두 가지를 이용해서)select
LAST_NAME || FIRST_NAME 이름,
SALARY 연봉,
DEPARTMENT_NAME 부서명,
CITY 도시명
FROM DEPARTMENTS DE INNER JOIN LOCATIONS LO ON DE.LOCATION_ID=LO.LOCATION_ID
JOIN EMPLOYEES EM ON DE.DEPARTMENT_ID = EM.DEPARTMENT_ID;
📙문제 20]
EMPLOYESS,LOCATIONS 및 DEPARTMENTS,COUNTRIES테이블을 JOIN해서
이름(LAST_NAME || FIRST_NAME),연봉,부서명,부서가 속한 도시명,국가명,국가코드을 출력해라.select
LAST_NAME || FIRST_NAME 이름,
SALARY 연봉,
DEPARTMENT_NAME 부서명,
CITY 도시명,
COUNTRY_NAME 국가명,
CO.COUNTRY_ID 국가코드
FROM DEPARTMENTS DE
INNER JOIN LOCATIONS LO ON DE.LOCATION_ID=LO.LOCATION_ID
JOIN EMPLOYEES EM ON DE.DEPARTMENT_ID = EM.DEPARTMENT_ID
JOIN COUNTRIES CO ON CO.COUNTRY_ID = LO.COUNTRY_ID;
📙문제 21]
SCOTT계정에서 아직 부서에 충원이 안 된 부서는 어느 부서인지 부서명 검색해보자(힌트-아웃터 조인)select
Dname,
d.deptno
from dept d LEFT OUTER JOIN emp e
on e.deptno = d.deptno
where empno is NULL;SELECT
*
FROM DEPT D LEFT OUTER JOIN EMP E
ON E.DEPTNO = D.DEPTNO
WHERE EMPNO IS NOT NULL;
📙문제 22]
HR계정에서 직원 중에 부서 배치가 안 된 모든 직원의 이름,연봉,입사일을 출력해라.select
LAST_NAME || FIRST_NAME 이름,
SALARY 연봉,
HIRE_DATE 입사일,
DEPARTMENT_ID 부서코드
from EMPLOYEES
where DEPARTMENT_ID is NULL;
📙문제 23]
HR계정에서 현재,충원이 안 된 모든 부서의 부서코드(department_id),부서명(department_name)을 출력 하여라.
24.23)번에 출력시 도시명(city),국가명(country_name)을 추가하여라.select
D.DEPARTMENT_NAME 부서명,
D.DEPARTMENT_ID 부서코드,
L.CITY 도시명,
C.COUNTRY_NAME 국가명,
C.COUNTRY_ID 국가코드
from
DEPARTMENTS D LEFT OUTER JOIN EMPLOYEES E
on e.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L ON D.location_id = L.location_id
JOIN countries C ON L.COUNTRY_ID = C.COUNTRY_ID
where E.DEPARTMENT_ID is NULL;
--where E.EMPLOYEE_ID is NULL;
