e.g. truncate table cascade

Preparation of Test Data
test=# 
create table employees (id integer, name varchar, department_id integer);

test=# 
create table departments (id int, department_name varchar);

test=# 
ALTER TABLE DEPARTMENTS
ADD CONSTRAINT DEPT_PK 
PRIMARY KEY (ID);

test=# 
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK 
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(ID);

test=# 
insert into departments
values (1,'IT');

test=# 
insert into departments
values (2,'Sales');

test=# 
insert into departments
values (3,'Marketing');

test=# 
insert into employees
values (1,'John',1);

test=# 
insert into employees
values (2,'Alex',1);

test=# 
insert into employees
values (3,'Guru',2);

test=# 
insert into employees
values (4,'Maximus',3);

test=# 
commit;

Data Check
-- select all rows of parent table 
test=# 
select * from departments;

-- select all rows of child table
test=# 
select * from employees;

Action
-- by using truncate table cascade, parent table and child table data will be removed. 
test=# 
truncate table departments cascade;

Last Data Check
-- select all rows of parent table 
test=# 
select * from departments;

-- select all rows of child table
test=# 
select * from employees;

Info: In Oracle 12c, you are able to use "cascade" clause with "truncate table" statement as well.

e.g. fetching JSON data

Preparation of Test Data
test=# 
create table T_JSON_DATA (id INTEGER, body JSON);

test=#
insert into T_JSON_DATA
values (1, '{ "name":"John", "age":30, "city":"New York" }');

test=#
insert into T_JSON_DATA
values (2, '{ "name":"Alex", "age":25, "city":"China" }');

test=#
insert into T_JSON_DATA
values (3, '{ "name":"Guru", "age":21, "city":"Russia" }');

test=#
insert into T_JSON_DATA
values (4, '{ "name":"Maximus", "age":25, "city":"Somewhere" }');

test=# commit;

Examples for populating test data
test=#
select * from T_JSON_DATA;

-- to retrieve specific field data
test=#
select body->>'name' from T_JSON_DATA;

-- with aliases
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA;

-- use with where clause
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA where id = 1;

ORA-02292 solution

Preparation
-- Insert test data into DEPARTMENT and EMPLOYEES tables.
SQL>
INSERT INTO DEPARTMENTS
VALUES (280,'New Department',100,1700);

SQL>
INSERT INTO EMPLOYEES
VALUES (207,'John','Mathew','JMATHEW','111.111.111',TO_DATE('25.04.2018','DD.MM.YYYY'),'IT_PROG',10000,0.5,100,280);

SQL>
INSERT INTO EMPLOYEES
VALUES (208,'Jim','Parker','JPARKER','111.111.111',TO_DATE('25.05.2018','DD.MM.YYYY'),'IT_PROG',20000,0.6,100,280);

SQL>
INSERT INTO EMPLOYEES
VALUES (209,'Sophia','Ran','SRAN','111.111.111',TO_DATE('25.06.2018','DD.MM.YYYY'),'IT_PROG',30000,0.7,100,280);

SQL>
INSERT INTO EMPLOYEES
VALUES (210,'Wendi','Blake','WBLAKE','111.111.111',TO_DATE('25.07.2018','DD.MM.YYYY'),'IT_PROG',40000,0.8,100,280);

SQL>
COMMIT;

Action
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;

Error
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

Solution
-- Find referencial integrity constraint between DEPARTMENT and EMPLOYEES tables for this error.
SQL>
SELECT * 
FROM ALL_CONSTRAINTS 
WHERE TABLE_NAME IN ('EMPLOYEES', 'DEPARTMENTS') AND CONSTRAINT_TYPE = 'R';

-- Drop constraint
SQL>
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK;

-- Re-define constraint with "ON DELETE CASCADE" option
SQL>
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK 
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)
ON DELETE CASCADE;

-- Try again
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;

SQL>
COMMIT;

e.g. count and distinct aggregate functions

examples; count (1) - count(*) - count(column_name) - count(distinct (column_name))

e.g. count aggregate function
SQL>
SELECT
    COUNT(*)
FROM
    employees;

  COUNT(*)
----------
       107

SQL>
SELECT
    COUNT(1)
FROM
    employees;

  COUNT(*)
----------
       107

SQL>
SELECT
    COUNT(commission_pct)
FROM
    employees;

COUNT(COMMISSION_PCT)
---------------------
                   35

Info: COUNT(1) and COUNT(*) calculate all rows even all columns data are NULL. However, COUNT(COLUMN_NAME) ignores NULL data.

e.g. distinct aggregate function
SQL>
SELECT
    distinct(commission_pct)
FROM
    employees;

COMMISSION_PCT
--------------
          NULL
           ,15
           ,35
            ,4
            ,3
            ,2
           ,25
            ,1

8 rows selected.

Info: DISTINCT(COLUMN_NAME) function uses not to see duplicated rows and also it shows NULL column if it exists.

e.g. both of count and distinct aggregate functions
SQL>
SELECT
    COUNT(DISTINCT(commıssıon_pct) )
FROM
    employees;

COUNT(DISTINCT(COMMISSION_PCT))
-------------------------------
                              7

Info: count(column_name) ignores NULL values, even if distinct function is used.

ORA-02449 solution

Preparation
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
  'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL>
CREATE TABLESPACE EXAMPLE_TS_2 DATAFILE
  'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_02.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;

SQL>
CREATE TABLE T_EXAMPLE_CHILD (ID NUMBER, FID NUMBER)
TABLESPACE EXAMPLE_TS_2;

SQL>
ALTER TABLE T_EXAMPLE
ADD CONSTRAINT ID_EXAMPLE_PK
PRIMARY KEY (ID);

SQL>
ALTER TABLE T_EXAMPLE_CHILD
ADD CONSTRAINT FID_EXAMPLE_FK
FOREIGN KEY (FID)
REFERENCES T_EXAMPLE (ID);

Action
SQL>
DROP TABLESPACE EXAMPLE_TS;

Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

Next Action
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;

Next Error
SQL>
ORA-02449: unique/primary keys in table referenced by foreign keys

Solid Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

ORA-01549 solution

Preparation
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
  'C:\ORACLE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;

Action
SQL>
DROP TABLESPACE EXAMPLE_TS;

Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;

e.g. pivot clause

Preparation 
* Define column names for pivot table
SQL>
SELECT LISTAGG (
          '''' || JOB_ID || '''' || ' AS ' || '"' || JOB_TITLE || '"' || ', ')
       WITHIN GROUP (ORDER BY JOB_TITLE)
  FROM JOBS;

Pivot example
* Show that how many jobs are set to persons according to department names.
SQL>    
SELECT *
    FROM (SELECT JOB_ID, DEPARTMENT_NAME
            FROM EMPLOYEES A, DEPARTMENTS B
           WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) PIVOT (
COUNT (JOB_ID)
            FOR JOB_ID
            IN  ('AD_VP' AS "ADMIN",
                'FI_ACCOUNT' AS "Accountant",
                'AC_MGR' AS "Accounting Manager",
                'AD_ASST' AS "Administration Assistant",
                'FI_MGR' AS "Finance Manager",
                'HR_REP' AS "Human Resources Representative",
                'MK_MAN' AS "Marketing Manager",
                'MK_REP' AS "Marketing Representative",
                'AD_PRES' AS "Presidentt",
                'IT_PROG' AS "Programmer",
                'AC_ACCOUNT' AS "Public Accountant",
                'PR_REP' AS "Public Relations Represent.",
                'PU_CLERK' AS "Purchasing Clerk",
                'PU_MAN' AS "Purchasing Manager",
                'SA_MAN' AS "Sales Manager",
                'SA_REP' AS "Sales Representative",
                'SH_CLERK' AS "Shipping Clerk",
                'ST_CLERK' AS "Stock Clerk",
                'ST_MAN' AS "Stock Manager"))
ORDER BY DEPARTMENT_NAME DESC;

How to use SPOOL command in Oracle?

WINDOWS
e.g.
SQL> 
sqlplus / as sysdba;

SQL>
SET LINESIZE 200;
SET PAGESIZE 100;

-- define a output file for writing
SPOOL 'C:\output_SQL.txt'; 

  SELECT owner,
         object_name,
         created,
         timestamp
    FROM DBA_OBJECTS
   WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;

-- stop to write
SPOOL OFF; 

LINUX
e.g.
SQL> 
sqlplus / as sysdba;

SQL>
SET LINESIZE 200;
SET PAGESIZE 100;

--define a output file for writing
SPOOL '/tmp/output_SQL.txt'; 

  SELECT owner,
         object_name,
         created,
         timestamp
    FROM DBA_OBJECTS
   WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;

-- stop to write
SPOOL OFF; 

-> to see output file
$ cat /tmp/output_SQL.txt

What are pencil hardness grading scales?

2H -> H -> F -> HB -> B -> 2B

H means Hard (hard-tipped)
B means Bold (soft-tipped)
F means Fine

Info: from left to right, writing is thicker on the paper.

Generally, it would say that H (Hard) is designed for drafting and S (Soft) is designed for writing.