SELECT sum(height), sum(weight) FROM tblcomedian;
SELECT avg(basicpay) FROM tblinsa;
SELECT max(sudang), min(sudang) FROM tblinsa; -- μ«μν
SELECT max(name), min(name) FROM tblinsa; -- λ¬Έμν
SELECT max(ibsadate), min(ibsadate) FROM tblinsa; -- λ μ§ν
round(), floor(), ceil() -> λ§μ΄ μ΄λ€.
SELECT round(avg(basicpay)) FROM tblinsa;
SELECT
3.5678,
floor(3.5678),
trunc(3.5678),
trunc(3.5678, 1)
FROM
dual;
SELECT
3.14,
CEIL(3.14)
FROM
dual;
SELECT
10 / 3,
mod(10, 3) AS λλ¨Έμ§, -- μ μ % μ μ
floor(10/3) AS λͺ« -- μ μ / μ μ
FROM
dual;
SELECT
abs(10),
abs(-10),
power(2, 2),
power(2, 3),
power(2, 4),
sqrt(4),
sqrt(9),
sqrt(16)
FROM
dual;
λμλ¬Έμ λ³ν
SELECT
first_name
FROM
employees
WHERE
lower(first_name) LIKE '%an%';
-- tblinsa > κΉ, μ΄, λ°, μ΅, μ > κ°κ° λͺλͺ
?
SELECT
count(*)
FROM
tblinsa
WHERE
substr(name, 1, 1) = 'κΉ';
SELECT
count(CASE
WHEN substr(name, 1, 1) = 'κΉ' THEN 1
END) AS κΉ,
count(CASE
WHEN substr(name, 1, 1) = 'μ΄' THEN 1
END) AS μ΄,
count(CASE
WHEN substr(name, 1, 1) = 'λ°' THEN 1
END) AS λ°,
count(CASE
WHEN substr(name, 1, 1) = 'μ΅' THEN 1
END) AS μ΅,
count(CASE
WHEN substr(name, 1, 1) = 'μ ' THEN 1
END) AS μ ,
count(CASE
WHEN substr(name, 1, 1) NOT IN ('κΉ', 'μ΄', 'λ°', 'μ΅', 'μ ') THEN 1
END) AS λλ¨Έμ§
FROM
tblinsa;
νμ λ¬Έμμ΄ κ΅¬μ±μμ
a. 9: μ«μ 1κ°λ₯Ό λ¬Έμ 1κ°λ‘ λ°κΎΈλ μν . λΉ μ리λ₯Ό μ€νμ΄μ€λ‘ μΉν. > %5d
b. 0: μ«μ 1κ°λ₯Ό λ¬Έμ 1κ°λ‘ λ°κΎΈλ μν . λΉ μ리λ₯Ό 0μΌλ‘ μΉν. > %05d
c. $: ν΅ν κΈ°νΈ νν
d. L: ν΅ν κΈ°νΈ νν(Locale)
e. .: μμ«μ
f. ,: μ²λ¨μ νκΈ°
νμλ¬Έμμ΄ κ΅¬μ±μμ
a. yyyy
b. yy
c. month
d. mon
e. mm
f. day
g. dy
h. ddd
i. dd
j. d
k. hh
l. hh24
m. mi
n. ss
o. am(pm)
--μκΈ°!!
SELECT
sysdate,
to_char(sysdate, 'yyyy-mm-dd'),
to_char(sysdate, 'hh24:mi:ss'),
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
to_char(sysdate, 'day am hh:mi:ss') --νμμΌ μ€ν 02:48:13
FROM dual;
SELECT '123' * 2, to_number('123') * 2 FROM dual;
SELECT
'2023-08-29', -- μλ£ν? λ¬Έμ vs λ μ§
to_date('2023-08-29'),
to_date('2023-08-29', 'yyyy-mm-dd'),
to_date('20230829'),
to_date('20230829', 'yyyymmdd'),
to_date('2023/08/29'),
to_date('2023/08/29', 'yyyy/mm/dd'),
--to_date('2023λ
08μ29μΌ', 'yyyyλ
mmμddμΌ'),
to_date('2023-08-29 15:28:39', 'yyyy-mm-dd hh24:mi:ss') -- λ°λμ λ€μ νμλ¬Έμλ₯Ό λΆμ¬μ£Όμ΄μΌ νλ€.
FROM
dual;
sysdate
μκ° - μκ° = μκ°(μΌ) > μ > λΆ > μ΄ νμ° κ°λ₯
> μΌ > μ > λ
νμ° λΆκ°λ₯
μκ° + μκ°(μΌ) = μκ° > μΌ, μ, λΆ, μ΄ κ°λ₯
> μ, λ
λΆκ°λ₯
months_between()
add_months()
μκ° - μκ°
1. μΌ, μ, λΆ, μ΄ > μ°μ°μ(-)
2. μ, λ
> month_between()
μκ° + - μκ°
1. μΌ, μ, λΆ, μ΄ > μ°μ°μ(+, -)
2. μ, λ
> add_months()
SELECT
sysdate,
last_day(sysdate) -- ν΄λΉ λ μ§ ν¬ν¨λ λ§μ§λ§ λ μ§ λ°ν(ν΄λΉ μμ΄ λ©°μΉ κΉμ§?)
FROM
dual;
ex01 ~ ex12: DML κΈ°λ³Έ
ν μ΄λΈ μμ±νκΈ° > μ€ν€λ§ μ μνκΈ° > μ»¬λΌ μ μνκΈ° > 컬λΌμ μ΄λ¦, μλ£ν, μ μ½μ μ μ
create table ν
μ΄λΈλͺ
(
μ»¬λΌ μ μ,
μ»¬λΌ μ μ,
μ»¬λΌ μ μ,
μ»¬λΌ μ μ,
컬λΌλͺ
μλ£ν(κΈΈμ΄),
컬λΌλͺ
μλ£ν(κΈΈμ΄) null μ μ½μ¬ν
);
1. not null
μ μ½ μ¬νμ λ§λλ λ°©λ²