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