1. BETWEEN ์กฐ๊ฑด์
ํ์์ ๊ฐ์ด ๋ค๋ฅธ ๋์์ผ๋ก ์ ์๋ ๊ฐ๊ฒฉ์ ์๋์ง ์ฌ๋ถ๋ฅผ ๊ฒฐ์
ํจ์์ด๋ฏ๋ก ๊ด๊ณ ์ฐ์ฐ์๊ฐ ๋ ํจ์จ์
ํ์
์ปฌ๋ผ๋ช
BETWEEN ๊ฐ AND ๊ฐ
์ฌ์ฉ ์
-- emp ํ
์ด๋ธ : sal๊ฐ 2000000~3000000 ์ฌ์ด ์ธ ์๋ฃ ์ค name, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, sal
FROM emp
WHERE sal BETWEEN 2000000 AND 3000000;
SELECT name, sal
FROM emp
WHERE sal>= 2000000 AND sal <= 3000000;
-- emp ํ
์ด๋ธ : sal๊ฐ 2000000~3000000์ ์ ์ธํ ์๋ฃ ์ค name, sal ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, sal
FROM emp
WHERE sal NOT BETWEEN 2000000 AND 3000000;
SELECT name, sal
FROM emp
WHERE sal <2000000 OR sal> 3000000;
-- emp ํ
์ด๋ธ : hireDate๊ฐ 2010๋
๋์ธ ์๋ฃ์ค name, hireDate ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, hireDate
FROM emp
WHERE hireDate BETWEEN '2010-01-01' AND '2010-12-31';
SELECT name, hireDate
FROM emp
WHERE TO_CHAR(hireDate, 'YYYY') = 2010;
2. IN ์กฐ๊ฑด์
list์ ์๋ ๊ฐ ์ค ํ๋๋ผ๋ ์ผ์นํ๋ฉด TRUE
-- emp ํ
์ด๋ธ : city๊ฐ '์์ธ', '์ธ์ฒ', '๊ฒฝ๊ธฐ' ์ธ ์๋ฃ ์ค name, city ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, city
FROM emp
WHERE city = '์์ธ' OR city = '๊ฒฝ๊ธฐ' OR city = '์ธ์ฒ';
SELECT name, city
FROM emp
WHERE city IN ('์์ธ', '๊ฒฝ๊ธฐ', '์ธ์ฒ');
-- emp ํ
์ด๋ธ : city๊ฐ '์์ธ', '์ธ์ฒ', '๊ฒฝ๊ธฐ' ๋ฅผ ์ ์ธํ ์๋ฃ ์ค name, city ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, city
FROM emp
WHERE city NOT IN ('์์ธ', '๊ฒฝ๊ธฐ', '์ธ์ฒ');
-- emp ํ
์ด๋ธ : city์ pos๊ฐ '์์ธ ์ด๋ฉด์ '๋ถ์ฅ'์ด๊ฑฐ๋ '๊ฒฝ๊ธฐ' ์ด๋ฉด์ '๊ณผ์ฅ' ์ธ ์๋ฃ ์ค name, city, pos ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, city, pos
FROM emp
WHERE (city ='์์ธ' AND POS = '๋ถ์ฅ') OR (city ='๊ฒฝ๊ธฐ' AND POS = '๊ณผ์ฅ');
SELECT name, city, pos
FROM emp
WHERE (city, pos) IN (('์์ธ', '๋ถ์ฅ'), ('๊ฒฝ๊ธฐ','๊ณผ์ฅ'));
-- emp ํ
์ด๋ธ : city์ pos๊ฐ '์์ธ' ์ด๋ฉด์ '๋ถ์ฅ' ์ธ ์๋ฃ ์ค name, city, pos ์ปฌ๋ผ ์ถ๋ ฅ(subquery)
SELECT name, city, pos
FROM emp
WHERE (city = '์์ธ' AND pos ='๋ถ์ฅ');
SELECT name, city, pos
FROM emp
WHERE (city,pos) IN(('์์ธ','๋ถ์ฅ'));
3. LIKE ์กฐ๊ฑด์
์ฃผ์ด์ง ๋ฌธ์์ด์ด ํจํด๊ณผ ์ผ์น ํ๋์ง ์ฌ๋ถ ํ์ธ
ํจํด ๋ฌธ์
% : 0๋ฌธ์ ์ด์ ๊ณตํต (null๊ณผ๋ ์ผ์นํ ์ X)
_ : 1๋ฌธ์ ์ด์ ๊ณตํต
-- emp ํ
์ด๋ธ : name์ด '๊น'์จ์ธ ์๋ฃ ์ค empNo, name ์ปฌ๋ผ ์ถ๋ ฅ
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '๊น%'; -- 3์ด ์กด์ฌํ๋ ์ ํ๋ฒํธ
-- emp ํ
์ด๋ธ : LIKE ์
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '%3%'; -- 3์ด ์กด์ฌํ๋ ์ ํ๋ฒํธ
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '%3'; -- 3์ผ๋ก ๋๋๋ ์ ํ๋ฒํธ
SELECT empNo, name, tel, rrn FROM emp
WHERE rrn LIKE '_0%'; -- ์๋
์์ผ์์ ๋
๋๊ฐ 10์ ๋ฐฐ์์ธ ์ฌ๋
-- ์ ํ๋ฒํธ์ 3๋๋ 5๊ฐ ์กด์ฌํ๋ ๊ฒฝ์ฐ(empNo, name, tel)
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '%3%' OR '%5%' ;
-- ์ฃผ๋ฏผ๋ฒํธ(rrn)๋ฅผ ์ด์ฉํ์ฌ 2์์ ์ถ์ํ ์์ธ, ๊ฒฝ๊ธฐ ์ฌ๋(empNo, name, rrn, city)
SELECT empNo, name, rrn, city FROM emp
WHERE rrn LIKE '__02%' AND (city ='์์ธ' OR city ='๊ฒฝ๊ธฐ' );
-- ์ฑ์จ๊ฐ 'ใฑ' ์ธ ๋ชจ๋ ์ด๋ฆ ์ถ๋ ฅ(๋์จ๋ ์ถ๋ ฅ ์๋จ)
SELECT name FROM emp
WHERE name > '๊ฐ' AND name < '๋';
ESCAPE : '%' ๋ '_' (ํจํด ๋ฌธ์)๊ฐ ํฌํจ๋ ๋ฐ์ดํฐ๋ฅผ ์ถ์ถํ๊ธฐ ์ํด ํ์ฉ
WITH : ๋ฐ๋ณต์ ์ผ๋ก ์ฌ์ฉ๋๋ ์ฟผ๋ฆฌ๋ฅผ ๋ธ๋ญํ ํ ๊ฒฝ์ฐ ์ฌ์ฉ
WITH tb AS (
SELECT '๊ฐ๊ฐ๊ฐ' name, '์ฐ๋ฆฌ_๋๋ผ' content FROM dual
UNION ALL
SELECT '๋๋๋' name, '์๋ฐ%์คํ๋ง' content FROM dual
UNION ALL
SELECT '๋ค๋ค๋ค' name, '์ฐ๋ฆฌ๋๋ผ' content FROM dual
UNION ALL
SELECT '๋ผ๋ผ๋ผ' name, '๋ชจ๋ฐ์ผ' content FROM dual
UNION ALL
SELECT '๋ง๋ง๋ง' name, '์๋๋ก์ด๋%๋ชจ๋ฐ์ผ' content FROM dual
)
SELECT * FROM tb;
-- content ์ปฌ๋ผ์์ %๋ฅผ ๊ฒ์
WITH tb AS (
SELECT '๊ฐ๊ฐ๊ฐ' name, '์ฐ๋ฆฌ_๋๋ผ' content FROM dual
UNION ALL
SELECT '๋๋๋' name, '์๋ฐ%์คํ๋ง' content FROM dual
UNION ALL
SELECT '๋ค๋ค๋ค' name, '์ฐ๋ฆฌ๋๋ผ' content FROM dual
UNION ALL
SELECT '๋ผ๋ผ๋ผ' name, '๋ชจ๋ฐ์ผ' content FROM dual
UNION ALL
SELECT '๋ง๋ง๋ง' name, '์๋๋ก์ด๋%๋ชจ๋ฐ์ผ' content FROM dual
)
SELECT * FROM tb
WHERE content LIKE '%#%%' ESCAPE '#';
WITH tb AS (
SELECT '๊ฐ๊ฐ๊ฐ' name, '์ฐ๋ฆฌ_๋๋ผ' content FROM dual
UNION ALL
SELECT '๋๋๋' name, '์๋ฐ%์คํ๋ง' content FROM dual
UNION ALL
SELECT '๋ค๋ค๋ค' name, '์ฐ๋ฆฌ๋๋ผ' content FROM dual
UNION ALL
SELECT '๋ผ๋ผ๋ผ' name, '๋ชจ๋ฐ์ผ' content FROM dual
UNION ALL
SELECT '๋ง๋ง๋ง' name, '์๋๋ก์ด๋%๋ชจ๋ฐ์ผ' content FROM dual
)
SELECT * FROM tb
WHERE INSTR(content, '%')>=1;
4. NULL
์ค๋ผํด์์๋ null๊ณผ '' ๋ ๋ชจ๋ null
-- ์ฃผ์์ฌํญ
SELECT 10 + NULL FROM dual; -- null
-- emp ํ
์ด๋ธ : tel์ด NULL ์ธ ์๋ฃ ์ค name, tel ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, tel FROM emp;
SELECT name, tel FROM emp WHERE tel = NULL: -- ์๋ชป๋ ํํ
SELECT name, tel FROM emp WHERE tel IS NULL:
-- emp ํ
์ด๋ธ : tel์ด NULL ์๋ ์๋ฃ ์ค name, tel ์ปฌ๋ผ ์ถ๋ ฅ
SELECT name, tel FROM emp WHERE tel is NOT NULL:
5. CASE ํํ์(Expressions)
IF~THEN~ELSE ๋ ผ๋ฆฌ์ ์ ์ฌํ ๋ฐฉ์์ผ๋ก ํํ์์ ์์ฑํด์ SQL์ ๋น๊ต ์ฐ์ฐ ๊ธฐ๋ฅ์ ๋ณด์ ํ๋ ์ญํ
ํ์1 ๊ฐ๋จํ CASE ํํ์
SELECT empNo, name, rrn,
CASE SUBSTR(rrn, 8, 1)
WHEN '1' THEN '๋จ์' -- WHEN 1 THEN '๋จ์' ๋ผ๊ณ ์ฝ๋ฉํ๋ฉด ์ค๋ฅ
WHEN '2' THEN '์ฌ์'
WHEN '3' THEN '๋จ์'
WHEN '4' THEN '์ฌ์'
END AS ์ฑ๋ณ
FROM emp;
SELECT empNo, name, rrn,
CASE MOD(SUBSTR(rrn, 8, 1), 2)
WHEN 0 THEN '์ฌ์'
WHEN 1 THEN '๋จ์'
END AS ์ฑ๋ณ
FROM emp;
ํ์2 ์กฐ๊ฑด CASE ํํ์
-- empNo, name, sal, bonus, ์ด๊ธ์ฌ, ์ธ๊ธ
-- ์ธ๊ธ์ ์ด๊ธ์ฌ๊ฐ 300๋ง์์ด์์ด๋ฉด 3%
-- ์ธ๊ธ์ ์ด๊ธ์ฌ๊ฐ 200๋ง์์ด์์ด๋ฉด 2%
-- ๋๋จธ์ง ์ธ๊ธ์ 0
SELECT empNo, name, sal, bonus, (sal+bonus) tot,
CASE
WHEN (sal+bonus) >= 3000000 THEN (sal+bonus) * 0.03
WHEN (sal+bonus) >= 2000000 THEN (sal+bonus) * 0.02
ELSE 0
END ์ธ๊ธ
FROM emp;
6. DECODE ํจ์
DECODE(a, 'b', 1) : aํญ๋ชฉ ๊ฐ์ด 'b' ์ด๋ฉด 1, ๊ทธ๋ ์ง ์์ผ๋ฉด null์ ๋ฐํ
DECODE(a, 'b', 1, 'c', 2) : aํญ๋ชฉ ๊ฐ์ด 'b' ์ด๋ฉด 1, 'c' ์ด๋ฉด 2, ๊ทธ๋ ์ง ์์ผ๋ฉด null์ ๋ฐํ
DECODE(a, 'b', 1, 'c', 2, 3) : aํญ๋ชฉ ๊ฐ์ด 'b' ์ด๋ฉด 1, 'c' ์ด๋ฉด 2, ๊ทธ๋ ์ง ์์ผ๋ฉด 3 ์ ๋ฐํ
SELECT empNo, name, rrn,
DECODE(SUBSTR(rrn, 8, 1), 1, '๋จ์')
FROM emp; -- ์ฑ๋ณ์ด 1์ด๋ฉด ๋จ์ ๊ทธ๋ ์ง ์์ผ๋ฉด null
SELECT empNo, name, rrn,
DECODE(SUBSTR(rrn, 8, 1), 1, '๋จ์', 2, '์ฌ์', 3, '๋จ์', 4, '์ฌ์', '์ธ๊ตญ์ธ')
FROM emp;
SELECT empNo, name, rrn,
DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, '์ฌ์', '๋จ์') ์ฑ๋ณ
FROM emp;
7. SELECT ๊ตฌ๋ฌธ์ ์ต์
ALL ์ต์
- ๋ชจ๋ ํ ๋ฐํ, ์๋ต ๊ฐ๋ฅ
SELECT ALL dept FROM emp;
SELECT dept FROM emp;
DISTINCT | UNIQUE ์ต์
- ์ค๋ณต์ ์ธ ํ์ ํ๋๋ง ์ถ๋ ฅ
SELECT DISTINCT dept FROM emp;
SELECT UNIQUE dept FROM emp;
SELECT DISTINCT dept, pos FROM emp;
-- ์์
๋ถ ๋ถ์ฅ
-- ์์
๋ถ ๊ณผ์ฅ
8. SELECT ๊ตฌ๋ฌธ์ ORDER BY ์
--sal ์ค๋ฆ์ฐจ์
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY sal;
--sal ์ค๋ฆ์ฐจ์ (ASC ์๋ต ๊ฐ๋ฅ)
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY sal ASC;
--sal ๋ด๋ฆผ์ฐจ์
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY sal DESC;
-- dept ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ๊ณ dept ๊ฐ ๊ฐ์ผ๋ฉด sal ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY dept, sal DESC ;
-- dept ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌํ๊ณ dept ๊ฐ ๊ฐ์ผ๋ฉด sal ์ค๋ฆ์ฐจ์ ์ ๋ ฌ
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY dept DESC, sal ;
-- dept ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌํ๊ณ dept ๊ฐ ๊ฐ์ผ๋ฉด sal ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY dept DESC, sal DESC ;
-- dept ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ๊ณ dept ๊ฐ ๊ฐ์ผ๋ฉด sal ์ค๋ฆ์ฐจ์ ์ ๋ ฌ
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY dept, sal;
-- sal + bonus ๋ด๋ฆผ์ฐจ์
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY pay DESC ;
๊ตฌ๋ฌธ๋ถ์
FROM ์ -> WHERE์ -> SELECT์ -> ORDER BY
๋ฐ๋ผ์ SELECT ์ ์ ๋ณ๋ช
์ ORDER BY์์ ์ฌ์ฉ ๊ฐ๋ฅ
-- sal + bonus ๋ด๋ฆผ์ฐจ์ : ์ซ์๋ก ์ปฌ๋ผ์ ์์น๋ฅผ ์ง์ ํ์ฌ ์ ๋ ฌ
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY 5 DESC;
-- ๋จ์์ค city ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ๊ณ city ๊ฐ ๊ฐ์ผ๋ฉด ๋จ์๋ฅผ ๋จผ์ ์ถ๋ ฅ : name, rrn, dept, sal
SELECT name, rrn, city, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1),2) = 1
ORDER BY city, sal DESC;
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn, 8, 1) IN (1,3,5,7,9)
ORDER BY city, sal DESC;
-- dept ๊ฐ ๊ฐ๋ฐ๋ถ์ธ ์ฌ๋์ค ๋จ์๋ฅผ ๋จผ์ ์ถ๋ ฅํ๊ณ ์ฑ๋ณ์ด ๊ฐ์ผ๋ฉด ๊ธ์ฌ ๋ด๋ฆผ์ฐจ์ : name, rrn, dept, sal
SELECT name, rrn, dept, sal
FROM emp
WHERE dept ='๊ฐ๋ฐ๋ถ'
ORDER BY MOD(SUBSTR(rrn, 8, 1),2) DESC, sal DESC;
SELECT name, rrn, dept, sal, MOD(SUBSTR(rrn, 8, 1),2) ์ฑ๋ณ
FROM emp
WHERE dept ='๊ฐ๋ฐ๋ถ'
ORDER BY ์ฑ๋ณ DESC, sal DESC;
-- dept ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ๊ณ dept๊ฐ ๊ฐ์ผ๋ฉด ๋จ์๋ฅผ ๋จผ์ ์ถ๋ ฅ: name, rrn, dept, sal
SELECT name, rrn, dept, sal
FROM emp
ORDER BY dept DESC, MOD(SUBSTR(rrn, 8, 1),2) DESC;
-- dept๋ ์์
๋ถ๋ฅผ ๋จผ์ ์ถ๋ ฅ : name, dept
-- ์ค๋ฆ์ฐจ์ ์ ๋ ฌ์์๋ NULL์ด ๋์ค์ ์ถ๋ ฅ
SELECT name, tel FROM emp;
SELECT name, tel FROM emp ORDER BY tel;
SELECT name, dept, CASE WHEN dept = '์์
๋ถ' THEN 0 END
FROM emp;
SELECT name, dept
FROM emp
ORDER BY CASE WHEN dept = '์์
๋ถ' THEN 0 END; -- ์ค๋ฆ์ฐจ์์์ NULL์ ๊ฐ์ฅ ๋ง์ง๋ง์
9. SQL ๋ฌธ์
pos ๊ฐ ๋ถ์ฅ, ๊ณผ์ฅ, ๋๋ฆฌ, ์ฌ์์์ผ๋ก ์ถ๋ ฅ : name, dept, pos
SELECT name, dept, pos
FROM emp
ORDER BY CASE
WHEN pos = '๋ถ์ฅ' THEN 1
WHEN pos = '๊ณผ์ฅ' THEN 2
WHEN pos = '๋๋ฆฌ' THEN 3
WHEN pos = '์ฌ์' THEN 4
END;
SELECT name, dept, pos
FROM emp
ORDER BY DECODE(pos, '๋ถ์ฅ', 1, '๊ณผ์ฅ', 2, '๋๋ฆฌ', 3, '์ฌ์', 4);
์ฌ์๋ฅผ ๋จผ์ ์ถ๋ ฅํ๊ณ ์ฑ๋ณ์ด ๋์ผํ๋ฉด sal ๋ด๋ฆผ์ฐจ์ : name, rrn, sal
SELECT name, rrn, sal
FROM emp
ORDER BY MOD(SUBSTR(rrn, 8,1),2), sal DESC;
์์ธ ์ฌ๋์ค์์ ๊ธฐ๋ณธ๊ธ+์๋น ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌ : name, city, sal+bonus
SELECT name, city, sal+bonus
FROM emp
WHERE city = '์์ธ'
ORDER BY sal+bonus DESC;
SELECT name, city, sal+bonus pay
FROM emp
WHERE city = '์์ธ'
ORDER BY sal+bonus DESC;
์ฌ์์ค ๋ถ์์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํ๊ณ ๋ถ์๊ฐ ๊ฐ์ผ๋ฉด ๊ธฐ๋ณธ๊ธ ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ : name, rrn, dept, sal
SELECT name, rrn, dept, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8,1), 2) = 0
ORDER BY dept, sal DESC;
์ ํ๋ฒํธ๊ฐ NULL ์ธ ๋ฐ์ดํฐ๋ฅผ ๋จผ์ ์ถ๋ ฅ
SELECT name, tel FROM emp ORDER BY tel DESC;
์ ํ๋ฒํธ๊ฐ NULL ์ธ ๋ฐ์ดํฐ๋ฅผ ๋์ค์ ์ถ๋ ฅ
SELECT name, tel FROM emp ORDER BY tel;
๋๋คํ๊ฒ 5๊ฐ ์ถ์ถ(์ด๋ฒคํธ ๋น์ฒจ๋ฑ)
SELECT empNo, name, DBMS_RANDOM.VALUE
FROM emp;
SELECT empNo, name
FROM emp
ORDER BY DBMS_RANDOME.RANK;
SELECT * FROM emp (
SELECT empNo, name
FROM emp
WHERE DBMS_RANDOME.RANK;
) WHERE ROWNUM <=5;
SELECT empNo, name (
FROM emp
ORDER BY DBMS_RANDOME.RANK
FETCH FIRST 5 ROWS ONLY; --12C์ด์์์๋ง ๊ฐ๋ฅ
10. ์งํฉ ์ฐ์ฐ์(Set Operators)
- UNION ํฉ์งํฉ ๊ต์งํฉ์ ํ๋ฒ๋ง ์ถ๋ ฅ
SELECT name, city, dept FROM emp WHERE dept = '๊ฐ๋ฐ๋ถ'
UNION
SELECT name, city, dept FROM emp WHERE city = '์ธ์ฒ';
- UNION ALL ํฉ์งํฉ ์ค๋ณต์ ์ธ ๋ฐ์ดํฐ๋ ์ถ๋ ฅ
SELECT name, city, dept FROM emp WHERE dept = '๊ฐ๋ฐ๋ถ'
UNION ALL
SELECT name, city, dept FROM emp WHERE city = '์ธ์ฒ';
- MINUS ์ฐจ์งํฉ (์ฒซ ๋ฒ์งธ SQL๋ฌธ ๊ฒฐ๊ณผ์ ์๊ณ , ๋ ๋ฒ์ฌ SQL ๋ฌธ์ ๊ฒฐ๊ณผ์๋ ์๋ ๋ฐ์ดํฐ๋ฅผ ์ถ๋ ฅ
SELECT name, city, dept FROM emp WHERE dept = '๊ฐ๋ฐ๋ถ'
MINUS
SELECT name, city, dept FROM emp WHERE city = '์ธ์ฒ';
- INTERSECT ๊ต์งํฉ ๋ ๋ฒ์งธ SQL๋ฌธ์ ๊ฒฐ๊ณผ์ ์ฒซ ๋ฒ์งธ SQL๋ฌธ์ ๊ฒฐ๊ณผ์ ์ค๋ณต๋ ํ๋ง ์ถ๋ ฅ
SELECT name, city, dept FROM emp WHERE dept = '๊ฐ๋ฐ๋ถ'
INTERSECT
SELECT name, city, dept FROM emp WHERE city = '์ธ์ฒ';
11. pseudo ์ปฌ๋ผ(์์ฌ ์ปฌ๋ผ)
์ค๋ผํด์์ ๋ด๋ถ์ ์ผ๋ก ์ฌ์ฉ๋๋ ์ปฌ๋ผ
ํ ์ด๋ธ ์ด์ฒ๋ผ ๋์ํ์ง๋ง ์ค์ ๋ก ํ ์ด๋ธ์๋ ์ ์ฅ X
ROWID : ํ์ ์ฃผ์
SELECT ROWID, empNo, name FROM emp;
ROWNUM : ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ก ๋์ค๋ ๊ฐ๊ฐ์ ํ๋ค์ ๋ํ ์์ ๊ฐ์ ๋ํ๋ด๋ ์์ฌ ์ปฌ๋ผ
ROWNUM์ ์ฌ์ฉํ์ฌ ํ์๋ฅผ ์ ํํ ์ ์์
SELECT ROWNUM, empNo, name, dept, pos, sal FROM emp
WHERE ROWNUM <=10;
์์ ์ ์๋ณด๋ค ํฐ ROWNUM ๊ฐ์ ๋ํด์ ํญ์ ๊ฑฐ์ง
SELECT empNo, name, dept, pos, sal FROM emp
WHERE ROWNUM >1; -- ํํ๋ ์ถ๋ ฅX
- ORDER BY ์ ์ด ์๋ ๊ฒฝ์ฐ ROWNUM์ ์ฌ์ฉํ๋ ๊ฒฝ์ฐ ํ ์์ธ์ค ๋ฐฉ๋ฒ์ ๋ฐ๋ผ ๊ฒฐ๊ณผ๊ฐ ๋ฌ๋ผ์ง - ์ฌ์ฉ ๊ธ์ง
SELECT empNo, name, dept, pos, sal FROM emp
ORDER BY sal DESC;
SELECT empNo, name, dept, pos, sal FROM emp
WHERE ROWNUM <=10
ORDER BY sal DESC; --์๋์ ๋ค๋ฅธ ๊ฒฐ๊ณผ๊ฐ ๋์จ๋ค
- ORDER BY ์ ์ด ์๋ ๊ฒฝ์ฐ ROWNUM์ ์ฌ์ฉํ ๊ฒฝ์ฐ์๋ ์๋ธ์ฟผ๋ฆฌ๋ฅผ ์ฌ์ฉํ๋ค
SELECT * FROM (
SELECT empNo, name, dept, pos, sal FROM emp
ORDER BY sal DESC
) WHERE ROWNUM <=10;
- sal ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌํด์ 6๋ฒ์งธ 10๋ฒ์งธ ๋ฐ์ดํฐ ์ถ๋ ฅ
SELECT * FROM (
SELECT empNo, name, dept, pos, sal
FROM emp
ORDER BY sal DESC
)WHERE ROWNUM >= 6 AND ROWNUM <=10; -- ์ค๋ฅ. ํฌ๋ค๋ ๋น๊ต ๋ถ๊ฐ๋ฅ
SELECT * FROM (
SELECT ROWNUM rnum, tb.* FROM (
SELECT empNo, name, dept, pos, sal
FROM emp
ORDER BY sal DESC
) tb WHERE ROWNUM <= 10
) WHERE rnum >= 6;
'๐ปProgramming > ๐ฝOracle' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
[Oracle] ๋ฐ์ดํฐ ๋์ ๋๋ฆฌ(Data Dictionary) (0) | 2022.08.10 |
---|---|
[ORACLE] ๋ฐ์ดํฐ ์กฐ์์ธ์ด(DML) (0) | 2022.08.09 |
[ORACLE] ๋ฐ์ดํฐ ์ ์์ธ์ด (DDL) (0) | 2022.08.09 |
[ORACLE] SQL ํจ์ - ๋จ์ผํ ํจ์ (0) | 2022.08.05 |
[Oracle] DQL - Select (0) | 2022.08.04 |