Các câu lệnh DML với bảng trong Oracle Database

--1.SELECT

 -- Size Cho table : non-partition

select segment_name, tablespace_name, sum(bytes)/1024/1024 "MB" 

from dba_Segments where segment_type = 'TABLE' 

and owner like 'USER1'

group by segment_name, tablespace_name

order by MB desc;

--Top n

select * from V$ACTIVE_SESSION_HISTORY where rownum <=20

--Beetween

SELECT *

FROM orders

WHERE order_date >= TO_DATE('2003/01/01', 'yyyy/mm/dd')

AND order_date <= TO_DATE('2003/12/31','yyyy/mm/dd');

--Hiển thị những row có ở table_1 nhưng không có ở bảng table_2

select col_1, col_2, col_3 from table_1 minus

select col_1, col_2, col_3 from table_2;

--intersect only returns the rows that are found in all select statements:

select col_1, col_2, col_3 from table_1 intersect

select col_1, col_2, col_3 from table_2;

Only the galaxy record is returned. It's the only record that is stored in both tables:

--union all is very similar to union, however, it dismisses duplicate rows found across different select statements:

select col_1, col_2, col_3 from table_1 union

select col_1, col_2, col_3 from table_2;

The galaxy record is a duplicate. Hence, it is returned only once:

--SELECT DISTINCT column_name(s)

FROM table_name 

--SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

%: mọi ký tự

select * from V$FIXED_TABLE where name like '%DATAFILE%'; // Show 

_: 1 single

SELECT * FROM Persons

WHERE LastName IN ('Hansen','Pettersen')

 and tablespace_name not in ('SYSTEM','SYSAUX')

 

 --2.INSERT

  INSERT INTO departments

   VALUES (280, 'Recreation', 121, 1700);

INSERT INTO employees (employee_id, last_name, email, 

      hire_date, job_id, salary, commission_pct) 

   VALUES (207, 'Gregory', 'pgregory@example.com', 

      sysdate, 'PU_CLERK', 1.2E3, NULL);

- Select lồng bên trong câu lệnh INSERT

INSERT INTO bonuses

   SELECT employee_id, salary*1.1 

   FROM employees

   WHERE commission_pct > 0.25; 

   

--3.UPDATE

UPDATE employees

   SET commission_pct = NULL

   WHERE job_id = 'SH_CLERK';

   --4.DELETE

    DELETE FROM employees

   WHERE job_id = 'SA_REP'

   AND commission_pct < .2;

-- Xoá bản ghi trùng nhau

DELETE FROM t_viet_red_bk

WHERE rowid not in

(SELECT MIN(rowid)

FROM t_viet_red_bk

GROUP BY sub_id);

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm