Oracle SQL Functions
https://www.tutorialspoint.com/oracle_sql_online_training/index.asp
https://www.youtube.com/user/completeitpro/playlistsOracle WITH clause
https://www.youtube.com/watch?v=t5P-yS1R3qQ
WITH sample_data AS
(
SELECT 'DEVELOPMENT' A, 1 k, 18 w, 397 c, 0 r FROM dual UNION ALL
SELECT 'HT' A, 43 k, 21 w, 673 c, 0 r FROM dual UNION ALL
SELECT 'LT' A, 83 k, 14 w, 7955 c, 60 r FROM dual
)
-- end of sample_data mimicking real table
SELECT t.*, k+w+c+r total FROM sample_data t;
https://stackoverflow.com/questions/35860050/oracle-grand-total-row-and-column-wise
PIVOT
https://community.oracle.com/thread/3950818?start=0&tstart=0
Derived Table
https://www.youtube.com/watch?v=FwcAkH8UyEA&list=PL08903FB7ACA1C2FB&index=48
CTE (common table expressions)
With EmployeeCount (Column names are Optional)
PIVOT & UNPIVOT
oracle
https://www.eandbsoftware.org/pivot-unpivot-in-ms-sql-server/
https://www.youtube.com/watch?v=OtSUxy206Gw&list=PL08903FB7ACA1C2FB&index=119
Pivot
https://www.youtube.com/watch?v=wodH9bKD3qg
https://easy-learning-tech.blogspot.com/2018/05/pivot-on-sql.html
http://www.oracle-developer.net/display.php?id=506
https://easy-learning-tech.blogspot.com/2018/05/pivot-on-sql.html
http://www.oracle-developer.net/display.php?id=506
Unpivot
https://www.youtube.com/watch?v=htZRVbSwEjw
https://easy-learning-tech.blogspot.com/2018/05/unpivot-in-sql.html
http://www.dba-oracle.com/t_advanced_sql_pivot_unpivot.htm
https://easy-learning-tech.blogspot.com/2018/05/unpivot-in-sql.html
http://www.dba-oracle.com/t_advanced_sql_pivot_unpivot.htm
Calculating a Grand Total for a Column
https://docs.oracle.com/cd/E57185_01/RAFWU/ch07s07s06s01.htmlOracle DECODE Function
https://www.youtube.com/watch?v=JzNv8RnEJ3A
Derived tables and common table expressions :
https://www.youtube.com/watch?v=FwcAkH8UyEA&list=PL08903FB7ACA1C2FB&index=48
Table:
1.
lu_day 2.
v_aggr_process_testassy 3. lu_model_mfgid
Relation
select * FROM v_aggr_process_testassy
v1 INNER JOIN lu_model_mfgid l2
ON v1.mfgid =
l2.mfgid AND v1.deid=l2.deid
INNER JOIN lu_day v2
ON v1.asmdate = v2.day_id
Keyword
Round :
decode and case:
The
Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement.
DECODE (expression,
search, result [, search, result]... [,default] )
SELECT
first_name, country, DECODE(country, 'USA', 'North America', 'UK', 'Europe', 'Other')
AS Decode_Result FROM customers;
SELECT
DECODE(NULL, NULL, 1, 0) FROM DUAL;
CASE statement allows you to perform
an IF-THEN-ELSE check within an SQL statement.
SELECT first_name,
last_name, country,
CASE
WHEN country = 'USA' THEN 'North America'
WHEN country = 'Canada' THEN 'North America'
WHEN country = 'UK' THEN 'Europe'
WHEN country = 'France' THEN 'Europe'
ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name,
last_name;
