--函數(shù)

--查看員工表數(shù)據(jù)
SELECT * FROM emp_test;

--查看部門表數(shù)據(jù)
SELECT * FROM dept_test;


--round( 數(shù)字 , 小數(shù)點(diǎn)后的位數(shù) )用于數(shù)字的四舍五入
--計(jì)算金額的四舍五入 
--注意：Oracle中別名用雙引號(hào)"原樣輸出"，mysql可以用單引號(hào)
SELECT salary*0.1234567 "原樣輸出",ROUND(salary*0.1234567) "默認(rèn)零位小數(shù)", ROUND(salary*0.1234567,2) "保留兩位小數(shù)" FROM emp_test;

--trunc( 數(shù)字 , 小數(shù)點(diǎn)后的位數(shù) )用于截取如果沒有第二個(gè)參數(shù) , 默認(rèn)是 0
--計(jì)算金額 , 末尾不做四舍五入
--注意：Oracle中截取用關(guān)鍵字TRUNC，MySql用TRUNCATE
SELECT salary*0.1234567 "原樣輸出",TRUNC(salary*0.1234567,2) "直接截取留兩位小數(shù)"  FROM emp_test;


--計(jì)算員工入職多少天?
--計(jì)算時(shí)間差Oracle用(expr1,expr2)DAYS,mysql用函數(shù)DATEDIFF(expr1,expr2),
SELECT name,hire_date,(SYSDATE-hire_date)DAYS FROM emp_test;

--計(jì)算員工入職多少個(gè)月？
--mysql用TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2),Oracle用MONTHS_BETWEEN(date1, date2)
SELECT name, ROUND(MONTHS_BETWEEN(SYSDATE,hire_date)) FROM emp_test;

--計(jì)算 12 個(gè)月之前的時(shí)間點(diǎn)
--Oracle (date, int) *計(jì)算 12 個(gè)月之前的時(shí)間點(diǎn),Myusql用DATE_ADD(date,INTERVAL expr unit)
SELECT ADD_MONTHS(SYSDATE,-12) FROM DUAL;

--計(jì)算本月最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;


--把時(shí)間數(shù)據(jù)按指定格式輸出
SELECT to_char(SYSDATE , 'yyyy-mm-dd  hh24:mi:ss') from DUAL;

--插入一條數(shù)據(jù) , 編號(hào)為 1012 , 姓名為 amy , 入職時(shí)間為當(dāng)前系統(tǒng)時(shí)間
INSERT INTO emp_test(emp_id,name,hire_date) VALUES (1012,'amy',SYSDATE); 
SELECT * FROM emp_test;

--插入一條數(shù)據(jù) , 編號(hào)為 1012 , 姓名為 amy , 入職時(shí)間為 2011 年 10 月 10 日
INSERT INTO emp_test(emp_id,name,hire_date) VALUES (1012,'amy',TO_DATE('2011-10-10','yyyy-mm-dd')); 
SELECT * FROM emp_test;

--按指定格式顯示員工姓名和入職時(shí)間 , 顯示格式為： amy 2011-10-10
SELECT name,TO_CHAR(hire_date,'yyyy-mm-dd') FROM emp_test;

--計(jì)算員工的年終獎(jiǎng)金
--要求：
--1)  如果 bonus 不是 null , 發(fā)年終獎(jiǎng)金額為 bonus
--2)  如果 bonus 是 null , 發(fā)年終獎(jiǎng)金額為 salary * 0.5
--3)  如果 bonus 和 salary 都是 null, 發(fā) 100 元安慰一下
--coalesce( 參數(shù)列表 )函數(shù)的作用：返回參數(shù)列表中第一個(gè)非空參數(shù) , 參數(shù)列表中最后一個(gè)值通常為常量
SELECT COALESCE(bonus,salary*0.5,100) FROM emp_test;

--根據(jù)員工的職位 , 計(jì)算加薪后的薪水?dāng)?shù)據(jù)
--要求：
--1)  如果職位是 Analyst：加薪 10%
--2)  如果職位是 Programmer：加薪 5%
--3)  如果職位是 clerk：加薪 2%
--4)  其他職位：薪水丌變
SELECT name,job,salary ,
            CASE job WHEN 'Analyst' THEN  salary*1.1
                             WHEN 'Programmer' THEN  salary*1.05
                             WHEN 'clerk' THEN  salary*1.02
                             ELSE salary
            END new_salary
            FROM emp_test;
--decode()函數(shù)是Oracle 中等價(jià)于 case when 語句的函數(shù) , 作用同 case 語句相同。
--decode 函數(shù)語法如下：
--decode(判斷條件 , 匹配 1 , 值 1 , 匹配 2 , 值 2 , … , 默認(rèn)值)
--表達(dá)的意思是：如果判斷條件 = 匹配 1 , 則迒回值 1
--判斷條件 = 匹配 2 , 則迒回值 2
--根據(jù)員工的職位 , 計(jì)算加薪后的薪水?dāng)?shù)據(jù)
--要求：和 case 語句相同
--1)  如果職位是 Analyst：加薪 10%
--2)  如果職位是 Programmer：加薪 5%
--3)  如果職位是 clerk：加薪 2%
--4)  其他職位：薪水丌變
SELECT name,job,salary ,
            DECODE(job,'Analyst',salary*1.1,'Programmer',salary*1.05,'clerk',salary*1.02,salary) new_salary
            FROM emp_test;

--薪水由低到高排序( 升序排列 )
SELECT name,salary FROM emp_test ORDER BY salary ASC;

--薪水由高到低排序( 降序排列 )
SELECT name,salary FROM emp_test ORDER BY salary DESC;

--按入職時(shí)間排序 , 入職時(shí)間越早排在前面
SELECT name,hire_date FROM emp_test ORDER BY hire_date ASC;

--按部門排序 , 同一部門按薪水由高到低排序
SELECT name,dept_test_id,salary FROM emp_test ORDER BY dept_test_id,salary;

--員工表中有多少條記錄？
SELECT COUNT(*) FROM emp_test;

--當(dāng)前帳戶( openlab )下有多少個(gè)表？
select count(*) from user_tables

--入職時(shí)間不是 null 的數(shù)據(jù)總數(shù)
SELECT COUNT(hire_date) FROM emp_test WHERE hire_date IS NOT NULL;


--計(jì)算員工的薪水總和是多少？
SELECT SUM(salary) FROM emp_test;

--計(jì)算員工的人數(shù)總和、薪水總和、平均薪水是多少？
SELECT COUNT(*), SUM(salary),AVG(salary) FROM emp_test;

--薪水平均值 = 薪水總和 / 人數(shù)總和 avg(salary) = sum(salary) / count(*)
--而 avg(salary)叧按有薪水的員工人數(shù)計(jì)算平均值。這樣得到的數(shù)據(jù)丌夠準(zhǔn)確。
SELECT COUNT(*), SUM(salary),AVG(NVL(salary,0)) FROM emp_test;

--計(jì)算員工的最高薪水和最低薪水
SELECT max(salary),min(salary) FROM emp_test;

--組函數(shù)：
   --count / avg / sum / max / min 如果函數(shù)中寫列名 , 默認(rèn)忽略空值
? -- avg / sum 針對(duì)數(shù)字的操作
? -- max / min 對(duì)所有數(shù)據(jù)類型都可以操作


--按部門計(jì)算每個(gè)部門的最高和最低薪水分別是多少？
SELECT dept_test_id,max(salary),min(salary) FROM emp_test GROUP BY dept_test_id;

--計(jì)算每個(gè)部門的 薪水總和 和 平均薪水？
SELECT dept_test_id,SUM(salary),AVG(NVL(salary,0)) FROM emp_test GROUP BY dept_test_id;

--每個(gè)部門的統(tǒng)計(jì)信息：
--要求格式如下：
--deptno max_s min_s sum_s avg_s emp_num
--10 10000 5000 23000 6789 3
SELECT dept_test_id deptno,
                max(salary) max_s,
                min(salary) min_s,
                SUM(salary) sum_s,
                AVG(NVL(salary,0)) avg_s, 
                COUNT(*) emp_num
FROM emp_test GROUP BY dept_test_id;

--按職位分組 , 每個(gè)職位的最高、最低薪水和人數(shù)？
SELECT MAX(salary),MIN(salary),COUNT(*) emp_num FROM emp_test GROUP BY job order by emp_num;

SELECT dept_test_id, AVG(NVL(salary,0)) avg_salary  FROM emp_test WHERE dept_test_id IS NOT NULL  GROUP BY dept_test_id HAVING AVG(NVL(salary,0))>5000;

--薪水總和大于 20000 元的部門數(shù)據(jù)？
SELECT dept_test_id, SUM(salary) FROM emp_test WHERE dept_test_id IS NOT NULL GROUP BY dept_test_id HAVING SUM(salary)> 20000;

--哪些職位的人數(shù)超過 2 個(gè)人？
SELECT job,COUNT(*) FROM emp_test GROUP BY job HAVING COUNT(*)>2;


--查詢最高薪水的是誰？
SELECT * FROM emp_test WHERE salary=(SELECT MAX(salary) FROM emp_test);