Thứ Ba, 1 tháng 12, 2020

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

 -- 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
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

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

 and tablespace_name not in ('SYSTEM','SYSAUX')
  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', '', 
      sysdate, 'PU_CLERK', 1.2E3, NULL);
- Select lồng bên trong câu lệnh INSERT
   SELECT employee_id, salary*1.1 
   FROM employees
   WHERE commission_pct > 0.25; 
UPDATE employees
   SET commission_pct = NULL
   WHERE job_id = 'SH_CLERK';

    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: ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: 👨 Inbox Messenger: (profile) 👨 Fanpage: 👨 Inbox Fanpage: 👨👩 Group FB: 👨 Website: 👨 Blogger: 🎬 Youtube: 👨 Tiktok: 👨 Linkin: 👨 Twitter: 👨 Đị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


Trần Văn Bình - Oracle Database Master