— SELECT power((post-50000)/15.0, 2) FROM TSTUDENT;
— SELECT sqrt((post-50000)/15.0) FROM TSTUDENT;
SELECT sign((post-50000)/15.0) FROM TSTUDENT;
문자함수
SELECT hakbun || “-“ || name FROM TSTUDENT;
SELECT hakbun, name, lower(gender) FROM TSTUDENT;
SELECT replace(hakbun, “2015”, “2023”), name, lower(gender) FROM TSTUDENT;
SELECT replace(hakbun, “2015”, “2023”) as hakbun, name, lower(gender) FROM TSTUDENT;
SELECT title, length(title) as len FROM TSUBJ;
SELECT substr(name, 1, 1) as lastname FROM TSTUDENT;
SELECT substr(name, 1, 1) as lastname, count(*) as cnt FROM TSTUDENT group by substr(name, 1, 1);
날짜/시간 함수
부속질의
중첩질의 – WHERE 부속질의
SELECT * FROM tsungjuk
WHERE final >= (SELECT avg(final) FROM tsungjuk)
스칼라 부속질의 – SELECT 부속질의
SELECT
sj.hakbun, avg(sj.final)
FROM tsungjuk sj
GROUP BY sj.hakbun
SELECT
(SELECT name FROM tstudent st WHERE st.hakbun=sj.hakbun), sj.hakbun, avg(sj.final)
FROM tsungjuk sj
GROUP BY sj.hakbun
SELECT
(SELECT name FROM tstudent st WHERE st.hakbun=sj.hakbun) as name, sj.hakbun as hakbun, avg(sj.final) as final
FROM tsungjuk sj
GROUP BY sj.hakbun
인라인 뷰 – FROM 부속질의
SELECT
sj.hakbun, avg(sj.final)
FROM tsjungk sj
WHERE sj.hakbun < ‘201600000’
GROUP BY sj.hakbun
SELECT
st.name, sj.hakbun, avg(sj.final)
FROM (SELECT name, hakbun FROM tstudent WHERE hakbun < ‘201600000’) st, tsungjuk sj
WHERE st.hakbun = sj.hakbun
GROUP BY sj.hakbun