-- Find the differences between two tables. 


-- Create a copy of SCOTT.DEPT with a few changes
CREATE TABLE dept_copy (deptno, dname, loc) AS
SELECT * FROM dept WHERE deptno = 10
UNION
SELECT deptno, dname, loc || ' changed' FROM dept WHERE deptno = 20
UNION
SELECT 50, 'MARKETING', 'DUBLIN' FROM dual;


-- Find the differences using MINUS
SELECT 'dept' AS source, d.*
FROM dept d
 MINUS
SELECT 'dept', dc.*
FROM dept_copy dc 
 UNION ALL
SELECT 'dept_copy', dc.*
FROM dept_copy dc
 MINUS
SELECT 'dept_copy',d.*
FROM dept d
ORDER BY 2; 


-- Compare using GROUP BY (Tom Kyte solution)
SELECT deptno, dname, loc, count(src1) cnt1, count(src2) cnt2
FROM (
  SELECT d.*, 1 src1, to_number(null) src2
  FROM dept d
  UNION ALL
  SELECT dc.*, to_number(null) src1, 1 src2
  FROM dept_copy dc
) data
GROUP BY deptno, dname, loc
HAVING count(src1) != count(src2)
ORDER BY deptno;

-- Tidy up
DROP TABLE dept;
DROP TABLE dept_copy;

-- Obtain db, os and network environment details from within the database


-- Useful SYS_CONTEXT parameters (see SQL Language Reference for full set)
SELECT 
  sys_context('USERENV', 'CURRENT_USER'),
  sys_context('USERENV', 'DB_DOMAIN'),
  sys_context('USERENV', 'DB_NAME'),
  sys_context('USERENV', 'DB_UNIQUE_NAME'),
  sys_context('USERENV', 'HOST'),
  sys_context('USERENV', 'INSTANCE_NAME'),
  sys_context('USERENV', 'IP_ADDRESS'),
  sys_context('USERENV', 'ISDBA'),
  sys_context('USERENV', 'LANG'),
  sys_context('USERENV', 'LANGUAGE'),
  sys_context('USERENV', 'MODULE'),
  sys_context('USERENV', 'NLS_CALENDAR'),
  sys_context('USERENV', 'NLS_CURRENCY'),
  sys_context('USERENV', 'NLS_DATE_FORMAT'),
  sys_context('USERENV', 'NLS_DATE_LANGUAGE'),
  sys_context('USERENV', 'NLS_SORT'),
  sys_context('USERENV', 'NLS_TERRITORY'),
  sys_context('USERENV', 'OS_USER'),
  sys_context('USERENV', 'SERVER_HOST'),
  sys_context('USERENV', 'SERVICE_NAME'),
  sys_context('USERENV', 'SESSIONID'),
  sys_context('USERENV', 'SID')
FROM dual;


-- Internet Addressing
SELECT 
  UTL_INADDR.get_host_name,
  UTL_INADDR.get_host_address,
  UTL_INADDR.get_host_name ('141.146.8.66'),
  UTL_INADDR.get_host_address ('oracle.com')
FROM DUAL;

-- Merge two tables using the MERGE statement


-- Create a copy of SCOTT.DEPT with a few changes
CREATE TABLE dept_copy (deptno, dname, loc) AS
SELECT * FROM dept WHERE deptno = 10
UNION
SELECT deptno, dname, loc || ' changed' FROM dept WHERE deptno = 20
UNION
SELECT 50, 'MARKETING', 'DUBLIN' FROM dual;


-- MERGE the DEPT table into DEPT_COPY
MERGE INTO dept_copy dc 
  USING dept d ON (dc.deptno = d.deptno)
WHEN matched THEN
  UPDATE SET dc.dname = d.dname,
             dc.loc   = d.loc
WHEN NOT matched THEN
  INSERT VALUES(d.deptno, d.dname, d.loc);
  
COMMIT;

/* MERGE does not remove extra rows.

Even though MERGE has a DELETE clause, we cannot use this to delete extra
rows from the DEPT_COPY table.

From the documentation..

  The only rows affected by this clause [DELETE] are those rows in the 
  destination table that are updated by the merge operation. 

Use find_table_differences.sql to compare the contents of the 2 tables.
*/


-- Use this command to remove extra rows from DEPT_COPY separately
DELETE FROM dept_copy
WHERE deptno NOT IN (SELECT deptno FROM dept);

COMMIT;

-- Tidy up
DROP TABLE dept;
DROP TABLE dept_copy;

-- Techniques for pivoting a set of column values to a CSV list


-- Pivot employee names in SCOTT.EMP by department..
-- using SYS_CONNECT_BY_PATH
SELECT 
  deptno,
  LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') as employees
  FROM (
    SELECT deptno,
           ename,
           row_number() over(PARTITION BY deptno ORDER BY deptno) rn,
           count(*) over(PARTITION BY deptno) cnt
    FROM emp
  )
WHERE rn=cnt
START WITH rn = 1  
CONNECT BY PRIOR rn = rn -1 AND PRIOR deptno = deptno;


-- using a slightly different solution with SYS_CONNECT_BY_PATH
SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev       
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;


-- using XMLAGG
SELECT deptno, 
       rtrim( xmlagg(xmlelement(c, ename || ',') ORDER BY ename).extract('//text()'), ',') as employees
FROM emp
GROUP BY deptno;

-- Pivot totals using CASE


-- Use the SCOTT.EMP table to produce 3 columns (low, medium, high) of salary 
-- totals by department
SELECT deptno, 
       SUM(CASE WHEN sal BETWEEN 0 AND 2000 THEN sal ELSE 0 END) low,
       SUM(CASE WHEN sal BETWEEN 2001 AND 4000 THEN sal ELSE 0 END) medium,
       SUM(CASE WHEN sal > 4000 THEN sal ELSE 0 END) high
FROM emp
GROUP BY deptno;


-- or the number of salaries that fall into those categories
SELECT deptno, 
       SUM(CASE WHEN sal BETWEEN 0 AND 2000 THEN 1 ELSE 0 END) low,
       SUM(CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE 0 END) medium,
       SUM(CASE WHEN sal > 4000 THEN 1 ELSE 0 END) high
FROM emp
GROUP BY deptno;

-- Update one table from another using a correlated subquery and a multivalue IN clause


-- Create a copy of SCOTT.DEPT with a few changes
DROP TABLE dept_copy;
CREATE TABLE dept_copy (deptno, dname, loc) AS
SELECT deptno, dname || ' changed', loc || ' changed' 
FROM dept WHERE deptno IN (30, 40)
UNION ALL
SELECT deptno + 100, dname, loc 
FROM dept;

-- Use a correlated subquery to update the copy table
UPDATE dept_copy
SET (dname, loc) = (SELECT dname, loc
                    FROM dept
                    WHERE dept_copy.deptno = dept.deptno
                    AND dname LIKE 'S%');

/* STOP!

This UPDATE statement will update DEPT 30 correctly, but will
set all other DNAME and LOC values to null.
*/
ROLLBACK;


-- Add a second subquery to the WHERE clause of the update 
UPDATE dept_copy
SET (dname, loc) = (SELECT dname, loc
                    FROM dept
                    WHERE dept_copy.deptno = dept.deptno
                    AND dname LIKE 'S%')
WHERE deptno IN (SELECT deptno
                 FROM dept
                 WHERE dname LIKE 'S%');
                 
COMMIT;

-- Group by with rollup, cube and grouping sets


-- GROUP BY
-- Totals for JOB within DEPTNO from the SCOTT.EMP table
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno, job;


-- ROLLUP
-- Totals for JOB within DEPTNO, plus totals for DEPTNO and a grand total
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY ROLLUP(deptno, job);


-- CUBE
-- Totals for all combinations of JOB and DEPTNO and a grand total
SELECT job, deptno, sum(sal)
FROM emp
GROUP BY CUBE(job, deptno);


-- GROUPING SETS

-- Totals for JOB within DEPTNO, plus a grand total
SELECT job, deptno, sum(sal)
FROM emp
GROUP BY GROUPING SETS((job, deptno), ());

-- Totals for JOB within DEPTNO, plus totals for each JOB and a grand total
SELECT job, deptno, sum(sal)
FROM emp
GROUP BY GROUPING SETS((job, deptno), (job), ());

-- Regular expression substring (REGEXP_SUBSTR) and replace (REGEXP_REPLACE)


-- Extract and remove the area code prefix from the OE.EMPLOYEES table
-- Phone_numbers look like this: 650.507.9833
SELECT 
  REGEXP_SUBSTR(phone_number,'^[[:digit:]]*') area_code,
  REGEXP_REPLACE(phone_number,'^[[:digit:]]*\.', null) remove_area_code
FROM employees;

-- Using gauges in an SQL Developer worksheet


-- SQLDEV:GUAGE:min:max:low:high:value

-- For example, a guage showing salary ranges from the SCOTT.EMP table
SELECT ename, 'SQLDEV:GAUGE:0:6000:2000:4000:' || sal AS salary
FROM emp;

-- Subquery Factoring


/* Overview..

The WITH clause lets you assign a name to a subquery block. 

You can then reference the subquery block multiple times in the main query 
using its name. Oracle optimizes the query by treating the query name as 
either an inline view or as a temporary table.

This can be useful when the same subquery needs to be used several times 
in a larger query.
*/

-- Using SCOTT.EMP, suppose we need to find departments where the average 
-- salary is greater than the maximum salary of any other department.
SELECT deptno, trunc(avg(sal)) as average, max(sal) AS maximum
FROM emp
GROUP BY deptno
ORDER BY deptno;

-- Here are a few ways we could do it; the first two examples without 
-- subquery factoring and the second two with.


-- An inline view and subquery condition
SELECT deptno
FROM (
  SELECT deptno, trunc(avg(sal)) as average
  FROM emp
  GROUP BY deptno
) 
WHERE average > ANY (SELECT max(sal)
                     FROM emp
                     GROUP BY deptno) ;


-- Two inline views and a join condition
SELECT s1.deptno
FROM (
  SELECT deptno, trunc(avg(sal)) as average
  FROM emp
  GROUP BY deptno
) s1 
JOIN (
  SELECT deptno, max(sal) as maximum
  FROM emp
  GROUP BY deptno
) s2 ON s1.average > s2.maximum ;


-- An inline view and subquery condition, with subquery factoring
WITH summary AS
(SELECT deptno, trunc(avg(sal)) as average, max(sal) AS maximum 
  FROM emp
  GROUP BY deptno)                     
SELECT deptno
FROM summary
WHERE average > ANY (SELECT maximum
                     FROM summary) ;

-- Two inline views and a join condition, with subquery factoring                   
WITH summary AS
(SELECT deptno, trunc(avg(sal)) as average, max(sal) AS maximum 
  FROM emp
  GROUP BY deptno)                     
SELECT s1.deptno
FROM summary s1 
JOIN summary s2 ON s1.average > s2.maximum ;