1. String Concatenation Operator

SELECT 'Employee: ' || ename || ', Role: ' || job || ', Salary: ' || sal AS details FROM emp;

2. Convert Strings to Lowercase

SELECT LOWER(ename) AS lowercase_name FROM emp;

3. Value Replacmeent

SELECT DECODE(deptno, 10, 'Development', 20, 'Product', 30, 'Maintenance') AS department FROM emp;

4. Extract Current Year

SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual;

5. Calculate Employee Tenure

SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hiredate) AS years_of_service FROM emp;

6. Numeric Formatting

SELECT TO_CHAR(0.123, '$0.9999') AS formatted_value FROM dual;

7. Format Date as String

SELECT TO_CHAR(SYSDATE, 'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') AS formatted_date FROM dual;

8. Calculate Total Compensation

SELECT ename, sal + COALESCE(comm, 0) AS total_compensation FROM emp;

9. Nullif Function

SELECT NULLIF(200, 200) AS result FROM dual;

10. Analytical Functions

Row Number Ranking

SELECT emp.*, ROW_NUMBER() OVER (ORDER BY sal DESC) AS rank_num FROM emp;

Rank Function

SELECT emp.*, RANK() OVER (ORDER BY sal DESC) AS rank_position FROM emp;

11. User Creation and Privileges

CREATE USER app_user IDENTIFIED BY password123;
GRANT CONNECT, CREATE SYNONYM TO app_user;
GRANT SELECT, DELETE, UPDATE ON scott.emp TO app_user;

12. Private Synonym

CREATE SYNONYM emp_data FOR scott.emp;
SELECT * FROM emp_data;

13. Public Synonym

CREATE PUBLIC SYNONYM public_emp FOR scott.emp;
SELECT * FROM public_emp;

14. Sequence and Table Usage

CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR2(50)
);

INSERT INTO students VALUES (student_seq.NEXTVAL, 'Zhang San');
SELECT student_seq.CURRVAL FROM dual;
SELECT * FROM students;

15. Grant View Creation Privilege

GRANT CREATE VIEW TO scott;

16. Create View with Join

CREATE VIEW department_employees AS
SELECT d.deptno, d.dname, d.loc, e.empno, e.ename, e.job, e.mgr, e.hiredate
FROM emp e
JOIN dept d ON e.deptno = d.deptno;