OVER(partition by )

Partition is explained clearly in the following two links.

Link-I

Link-II

Left Outer Join

select parentrequest.bankname, parentrequest.amount, parentrequest.bofa_ref#, parentrequest.gtemfilenum ' - ' parentrequest.sequencenumber as gtemfilenum, parentrequest.status, parentrequest.approved_bydate, parentrequest.maximum_maturitydate, childrequest.gtemfilenum ' - ' childrequest.sequencenumber as gtemfilenum, childrequeststatus.display as status, childrequest.creationdate, childrequest.maximummaturitydate as maximum_maturitydatefrom own_gtem.requestdetails childrequest join own_gtem.requeststatusdescription childrequeststatus on childrequest.requeststatuskey = childrequeststatus.requeststatuskey, ( select bd.bankname, rd.amount, rd.bacrefnum as bofa_ref#, rd.gtemfilenum, rd.sequencenumber, rsd.display as status, rd.approvedbydate as approved_bydate, rd.maximummaturitydate as maximum_maturitydate from own_gtem.requestdetails rd join own_gtem.requeststatusdescription rsd on rd.requeststatuskey = rsd.requeststatuskey left outer join own_gtem.bankfacilityconnection bfc on rd.requestdetailskey = bfc.requestdetailskey left outer join own_gtem.bankfacility bf on bfc.bankfacilitykey = bf.bankfacilitykey left outer join own_gtem.bankdetails bd on bf.bankdetailskey = bd.bankdetailskey where rd.requeststatuskey=9 ) parentrequestwhere parentrequest.gtemfilenum = childrequest.gtemfilenum and childrequest.requeststatuskey in (6, 7, 8)

Usage of Dual table

If the select query involves fetching data from multiple tables then we use dual table.

AND 0=(SELECT COUNT(*) FROM DUAL WHERE TBUONE.TBUNAME = 'TOC-NY' AND RTT.CODE = 'LACP-OAP' AND CD1.COUNTRYCODE = 'KR' AND RD.AUTOAPPROVEDFLAG = 'N' AND RD.AMOUNT < 500000)

Oracle Tutorials

SQL Tips and Tutorials

Create table concepts.

DROP TABLE OWN_GTEM.USERROLETYPES CASCADE CONSTRAINTS;

cascade constraints
Deletes all foreign keys that reference the table to be dropped, then drops the table.

ALTER TABLE OWN_GTEM.USERROLETYPES DROP PRIMARY KEY CASCADE;

DROP PRIMARY KEY CASCADE
Dropping constraints generally requires knowing their names (only in the special case of primary or unique key constraints can you drop them without specifying their names). Thus, it is always a good idea to name all your constraints.

COMMENT ON TABLE OWN_GTEM.USERSEARCHVALUES IS 'User Search Selections For Reload on Dashboard pages';

COMMENT ON TABLE
The comment statement allows to store some comments about tables, views or columns in the data dictionary.

CREATE INDEX OWN_GTEM.PK_USERSEARCHVALUES ON OWN_GTEM.USERSEARCHVALUES(USERID, DASHBOARD)
LOGGING
TABLESPACE
GTEM_D
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX

TO_DATE Function in SQL

How to find the names of employees who joined between 10th and 20th of August 2008.

SELECT empName FROM emp WHERE joining_date between to_date ('2008/08/10', 'yyyy/mm/dd')AND to_date ('2008/08/20', 'yyyy/mm/dd');

Link-1 and Link-2 helps us understand, to_date and to_char functions better.

ORDER BY TO_DATE(TO_CHAR(APPROVEDBYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')

How to sort null values first using ORDER BY

This link helps to understand the sorting of null values better.


CASE WHEN(rd.riskdistributionflag LIKE 'Y' AND rd.fundingstatustypekey=2)
THEN 'RDU'
WHEN (rd.riskdistributionflag LIKE 'Y' AND rd.fundingstatustypekey=1)
THEN 'RDF'
END AS RDUF,

ORDER BY nvl(RDUF,'AAA')

The above query ensures the NULL rows are ordered first and then the data comes later.

This link helps to understand the sorting order