Malaysia

 

Mohammad Nazmul Huda Rubol Chowdhury

T2-13A-06-01, icon city, Jalan no.1 , sungai way, petaling jaya, Selangor.

Postal: 47300

Telephone : +60178855673

Rubol30@gmail.com

Oracle

Oracle SQL Functions

https://www.tutorialspoint.com/oracle_sql_online_training/index.asp

https://www.youtube.com/user/completeitpro/playlists

Oracle WITH clause

http://www.dba-oracle.com/t_with_clause.htm

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)

http://www.dba-oracle.com/t_with_clause.htm

With EmployeeCount (Column names are Optional)

https://www.dotnettricks.com/learn/sqlserver/difference-between-cte-and-temp-table-and-table-variable

https://www.tutorialgateway.org/difference-between-cte-temp-tables-derived-tables/

PIVOT & UNPIVOT

oracle

https://www.youtube.com/watch?v=htZRVbSwEjw

https://www.youtube.com/watch?v=h3BtudZehuo&list=PL08903FB7ACA1C2FB&index=54

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

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

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

Calculating a Grand Total for a Column

https://docs.oracle.com/cd/E57185_01/RAFWU/ch07s07s06s01.html

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

https://www.youtube.com/watch?v=JzNv8RnEJ3A

https://www.youtube.com/watch?v=IZNm-MdPAX4