SELECT UPPER('Hello Oracle') FROM dual;
-- 返回: 'HELLO ORACLE'

SELECT LOWER('Hello Oracle') FROM dual;
-- 返回: 'hello oracle'

SELECT INITCAP('hello oracle world') FROM dual;
-- 返回: 'Hello Oracle World'

SELECT LENGTH('Oracle SQL') FROM dual;
-- 返回: 10

SELECT INSTR('oracle sql is cool sql', 'sql', 1, 2) FROM dual;
-- 返回: 21 (從第1個(gè)字符開始查找，第2次出現(xiàn)的'sql'的位置)

SELECT SUBSTR('Oracle Database', 8, 8) FROM dual;
-- 返回: 'Database' (從第8個(gè)字符開始，截取8個(gè)字符)

SELECT REPLACE('black cat and blue cat', 'cat', 'dog') FROM dual;
-- 返回: 'black dog and blue dog'

SELECT CONCAT('Hello', ' World') FROM dual;
-- 返回: 'Hello World'

SELECT 'Oracle' || ' ' || 'SQL' FROM dual;
-- 返回: 'Oracle SQL'

SELECT LPAD('123', 5, '0') FROM dual;
-- 返回: '00123'

SELECT RPAD('abc', 5, '*') FROM dual;
-- 返回: 'abc**'

SELECT TRIM('  Oracle  ') FROM dual;
-- 返回: 'Oracle'

SELECT LTRIM('$$$100', '$') FROM dual;
-- 返回: '100'

SELECT RTRIM('abc##', '#') FROM dual;
-- 返回: 'abc'

SELECT ROUND(123.456, 2) FROM dual;
-- 返回: 123.46

SELECT TRUNC(123.456, 2) FROM dual;
-- 返回: 123.45

SELECT CEIL(99.1) FROM dual;
-- 返回: 100

SELECT FLOOR(99.9) FROM dual;
-- 返回: 99

SELECT MOD(10, 3) FROM dual;
-- 返回: 1

SELECT ABS(-123) FROM dual;
-- 返回: 123

SELECT SYSDATE FROM dual;
-- 返回: (當(dāng)前日期和時(shí)間，例如 2024-03-22 10:30:00)

SELECT SYSTIMESTAMP FROM dual;
-- 返回: (當(dāng)前日期時(shí)間+小數(shù)秒+時(shí)區(qū)，例如 22-MAR-24 10.30.00.123456 AM +08:00)

SELECT ADD_MONTHS(TO_DATE('2024-01-31', 'YYYY-MM-DD'), 1) FROM dual;
-- 返回: 29-FEB-24 (會(huì)自動(dòng)處理月末日期)

SELECT MONTHS_BETWEEN(TO_DATE('2024-07-15', 'YYYY-MM-DD'), TO_DATE('2024-01-15', 'YYYY-MM-DD')) FROM dual;
-- 返回: 6

SELECT LAST_DAY(TO_DATE('2024-02-10', 'YYYY-MM-DD')) FROM dual;
-- 返回: 29-FEB-24 (2024是閏年)

SELECT NEXT_DAY(TO_DATE('2024-03-22', 'YYYY-MM-DD'), '星期一') FROM dual; -- 假設(shè)NLS_DATE_LANGUAGE是中文
-- 返回: 25-MAR-24

SELECT TRUNC(SYSDATE, 'MM') FROM dual;
-- 返回: (當(dāng)月的第一天，例如 01-MAR-24)

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
-- 返回: (當(dāng)前年份，例如 2024)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 返回: '2024-03-22 10:30:00' (示例)

SELECT TO_CHAR(12345.67, 'FM99G999D00') FROM dual;
-- 返回: '12,345.67'

SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM dual;
-- 返回: 15-JAN-24 (日期類型)

SELECT TO_NUMBER('1,234.56', '9,999.99') FROM dual;
-- 返回: 1234.56 (數(shù)字類型)

-- 假設(shè) employees 表有10條記錄, 其中 commission_pct 有3個(gè)非空值，2種不同的非空值
SELECT COUNT(*), COUNT(commission_pct), COUNT(DISTINCT commission_pct) FROM employees;
-- 返回: 10, 3, 2

SELECT SUM(salary) FROM employees;
-- 返回: (所有員工薪水總和)

SELECT AVG(salary) FROM employees;
-- 返回: (所有員工薪水平均值)

SELECT MAX(salary) FROM employees;
-- 返回: (最高薪水)

SELECT MIN(salary) FROM employees;
-- 返回: (最低薪水)

SELECT NVL(commission_pct, 0) FROM employees;
-- 返回: (如果commission_pct是NULL，則顯示0，否則顯示其本身的值)

SELECT NVL2(commission_pct, 'Has Commission', 'No Commission') FROM employees;
-- 返回: (根據(jù)commission_pct是否為NULL，顯示不同的字符串)

SELECT department, DECODE(department, 'Sales', 'S', 'HR', 'H', 'Other') AS dept_code FROM employees;
-- 返回: (將部門名轉(zhuǎn)換為代碼)

SELECT salary, CASE WHEN salary > 10000 THEN 'High' ELSE 'Normal' END AS salary_level FROM employees;
-- 返回: (根據(jù)薪水是否大于10000，顯示不同的等級(jí))

CREATE TABLE employees (
    employee_id     NUMBER(10) NOT NULL,
    full_name       VARCHAR2(100 CHAR) NOT NULL,
    job_title       VARCHAR2(100 CHAR) NOT NULL,
    department      VARCHAR2(50 CHAR) NOT NULL,
    salary          NUMBER(10, 2) NOT NULL,
    commission_pct  NUMBER(4, 2),
    hire_date       DATE NOT NULL,
    CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

SELECT UPPER(SUBSTR(full_name, INSTR(full_name, ',') + 2) || ' ' || SUBSTR(full_name, 1, INSTR(full_name, ',') - 1)) AS formatted_name
FROM employees;

SELECT full_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) AS months_worked
FROM employees;

SELECT SUBSTR(full_name, 1, INSTR(full_name, ',') - 1) AS last_name
FROM employees;

SELECT full_name, salary,
       CASE
         WHEN salary > 10000 THEN 'A'
         WHEN salary BETWEEN 5000 AND 10000 THEN 'B'
         ELSE 'C'
       END AS salary_grade
FROM employees;

SELECT department, COUNT(*) AS number_of_employees, ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department;

SELECT full_name, salary + (salary * NVL(commission_pct, 0)) AS total_income
FROM employees;

SELECT full_name, TO_CHAR(hire_date, 'YYYY"年"MM"月"DD"日"') AS formatted_hire_date
FROM employees;

SELECT full_name, department, salary,
       LAG(salary, 1, NULL) OVER (PARTITION BY department ORDER BY salary DESC) AS next_highest_salary
FROM employees;

SELECT INITCAP(TRIM(SUBSTR(full_name, 1, INSTR(full_name, ',') - 1))) AS cleaned_last_name
FROM employees;

SELECT full_name, hire_date, TO_CHAR(LAST_DAY(hire_date), 'Day') AS last_day_of_hire_month
FROM employees;