SQL

Queries

SQL Functions

Set Operators

UNION
UNION ALL
MINUS
INTERSECTION

JOINS
* INNER JOIN
* LEFT OUTER JOIN
* RIGHT OUTER JOIN

GROUP BY
HAVING

PIVOT
UNPIVOT



Performance Tuning


Select modified data from 2 same tables and then update [1]




first day of the month [1]
======================================
select TRUNC (to_date('25-Mar-2017'), 'MONTH') "First day of current month"
FROM DUAL

last day of the month [1]
=======================================
SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;

first day of the year [1]
=======================================
SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day"
FROM DUAL;

last day of the year [1]
=======================================
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day"
FROM DUAL

number of days in current month [1]
=======================================
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
FROM DUAL;


database version
============
select * from v$version

actual database size in GB
====================
select sum(bytes) / 1024 /1024 /1024 as GB
from dba_data_files

space occupied by data in a database or database usage details
============================================
select sum(bytes) / 1024 / 1024 / 1024 as GB
from dba_segments



show the progress of long running queries
===============================
SELECT a.sid,
  a.serial#,
  b.username,
  opname OPERATION,
  target OBJECT,
  TRUNC (elapsed_seconds, 5) "ET (s)",
  TO_CHAR (start_time, 'HH24:MI:SS') start_time,
  ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a,
  v$session b
WHERE a.sid           = b.sid
  AND b.username NOT IN ('SYS', 'SYSTEM')
  AND totalwork       > 0
ORDER BY elapsed_seconds;


find last record from the table
======================
SELECT *
FROM employees
WHERE rowid IN(SELECT MAX(rowid)
                               FROM employees )


multiply row data of a table                                                             [http://viralpatel.net/blogs/row-data-multiplication-in-oracle/]
deleting duplicate rows                                                                    [http://viralpatel.net/blogs/deleting-duplicate-rows-in-oracle/]
How To Convert Number into Words using Oracle SQL Query     [http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/]



GROUP BY
* used in SELECT statement to collect data across multiple records and group the results by one or more columns [1]

e.g.
SQL join tables with group by and order by [1]



SET OPERATORS
* ORDER BY clause can appear only once at the end of the query containing compound SELECT statement [1]
* individual SELECT statement can not have ORDER BY clause [1]
* sorting can be based on the columns which appear in the first SELECT query only.
    for this reason it is recommended to sort the compound query using column position [1]

PIVOT

* introduced in 11g
* lets u write cross tabulation queries that rotate rows into columns while aggregating data in the rotation process.
* also called transposed, crosstab or matrix queries.



PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1 [1]
Oracle Database 11g: The Top Features for DBAs and Developers [1]





UNPIVOT

* introduced in 11g
* is a sql operation that rotates data from columns into rows [1]


Create table
* to create a new table with same structure as an existing table use CTAS [sanref2]
        create table new_table
        as
        select *    
        from old_table

Insert
* inserts a new row in d/b [sanref2]


INNER JOIN


Rownum
* use it to perform top-N processing [sanref3]
* use rownum to paginate through a query [sanref3]





[sanref1] [http://mll.csie.ntu.edu.tw/course/database_f07/assignment/Solutions/assignment3_solution.pdf]
[sanref2] [http://sql-plsql.blogspot.com.au]
[sanref3] [http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html]
[http://blog.mclaughlinsoftware.com/oracle-sql-programming/basic-sql-join-semantics/] Basic SQL join semantics

[http://www.dba-oracle.com/t_advanced_sql_hierarchical_queries.htm]
[https://asktom.oracle.com/pls/apex/asktom.search?tag=outer-join-concept-20080612101651] Outer Join Concept
[https://asktom.oracle.com/pls/apex/asktom.search?tag=query-regarding-join-order-in-ansi]