Oracle中SQL語句-練慣用

-- 1
CREATE TABLE my_test (
id NUMBER(3),
name VARCHAR2(20)
);

INSERT INTO my_test VALUES (1, 'This is line 1');
INSERT INTO my_test VALUES (1, 'This is line 1');
INSERT INTO my_test VALUES (1, 'This is line 1');
INSERT INTO my_test VALUES (1, 'This is line 1');
INSERT INTO my_test VALUES (1, 'This is line 1');
COMMIT;

SELECT * FROM my_test;

SELECT ROWID, id, name FROM my_test;

-- 具體 ROWID 值要看查詢的返回
DELETE FROM my_test WHERE ROWID='AAAHoSAAJAAAAH0AAC';

SELECT ROWNUM, id, name FROM my_test;

SELECT * FROM my_test WHERE ROWNUM<3;

-- 2
CREATE TABLE employee_copy AS SELECT * FROM employee
WHERE 1=2;

INSERT INTO employee_copy
SELECT * FROM employee WHERE deptcode='DP02';

SELECT empno "職員編號", ename "職員姓名",
designation "職位", dateofjoining "入職日期"
FROM employee;

-- 3
SELECT e.empno, ename, e.deptcode, s.empsal
FROM employee e INNER JOIN salary_records s
ON e.empno = s.empno
WHERE s.empsal >= 15000 AND s.empsal <=20000;

SELECT e.empno, ename, e.deptcode, s.empsal
FROM employee e INNER JOIN salary_records s
ON e.empno = s.empno
WHERE s.empsal BETWEEN 15000 AND 20000;

-- 4
SELECT (orderno || ' 的訂單日期為 ' || odate
|| '，發貨日期為 ' || del_date) AS "訂單狀態"
FROM order_master WHERE vencode = 'V001';

-- 5
SELECT vencode FROM vendor_master
UNION
SELECT vencode FROM order_master WHERE ostatus='c';

-- 6
SELECT EmpNo, Ename, designation, deptcode,
ROUND(dateofjoining, 'YEAR')
FROM Employee WHERE dateofjoining < '01-7月-2003';

-- 7
SELECT ProdId, ProdPrice, RANK() OVER
(ORDER BY ProdPrice DESC) rank
FROM ProductDetails;