| Arithmetic Operators |
| Addition |
<numeric_value> + <numeric_value> |
| SELECT 100 + 10 FROM dual; |
| Subtraction |
<numeric_value> - <numeric_value> |
| SELECT 100 - 10 FROM dual; |
| Multiplication |
<numeric_value> * <numeric_value> |
| SELECT 100 * 10 FROM dual; |
| Division |
<numeric_value> / <numeric_value> |
| SELECT 100 / 10 FROM dual; |
| Power (PL/SQL Only) |
<numeric_value> ** <numeric_value> |
set serveroutput on
BEGIN
dbms_output.put_line('2 to the 5th is ' || 2**5);
END;
/ |
| |
| Concatenation Operators |
| Concatenate |
SELECT <string>
|| <string> |
| SELECT 'Daniel '
|| 'Morgan' FROM dual; |
| |
| Date Operators |
| Addition |
SELECT <date_value> + <numeric_value> |
| SELECT SYSDATE + 10 FROM dual; |
| Subtraction |
SELECT <date_value> - <date_value> |
| SELECT SYSDATE - 10 FROM dual; |
| |
| Hierarchical Query Operators |
|
CONNECT, CONNECT BY, CONNECT BY PRIOR, and CONNECT BY ROOT will be dealt with on a separate page on Hierarchical Queries |
| |
| Multiset Operators
- Combine the results of two nested tables into a single nested table |
| Multiset |
CAST(MULTISET(<select statement> AS object_type) |
| See CAST page |
| Multiset Except |
<nested_table1> MULTISET
EXCEPT <ALL | DISTINCT <nested_table2> |
SELECT customer_id, cust_address_ntab1
MULTISET EXCEPT DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo; |
| Multiset Intersect |
<nested_table1> MULTISET
INTERSECT <ALL | DISTINCT <nested_table2> |
SELECT customer_id, cust_address_ntab1
MULTISET INTERSECT DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo; |
| Multiset Union |
<nested_table1> MULTISET
UNION <ALL | DISTINCT <nested_table2> |
SELECT customer_id, cust_address_ntab1
MULTISET UNION DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo; |
| |
| Pivot
Operators (new 11g) |
Pivot |
PIVOT [XML] (<aggregate
function> (expression) [AS <alias>]
FOR (<column_list>)
IN <subquery>) |
SELECT * FROM
(
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL', 'DAL', 'ILC', 'NWO',
'SAL', 'SWA', 'USAF'));
CREATE TABLE pivot_table AS
SELECT * FROM (
SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total
FROM orders)
PIVOT (SUM(order_total)
FOR order_mode
IN ('direct' AS Store, 'online' AS Internet)); |
Unpivot |
UNPIVOT [<INCLUDE |
EXCLUDE> NULLS] (<column_list>) FOR (<column_list>)
IN (<column_list>) [AS (<constant_list>)]) |
CREATE TABLE
pivot_table AS
SELECT * FROM (
SELECT program_id, customer_id, 1 CNT
FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id IN ('AAL', 'DAL', 'ILC', 'NWO', 'SAL', 'SWA', 'USAF'));
SELECT * FROM pivot_table
UNPIVOT (program_id for program_id
IN program_id) as (program_id);
SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
SELECT * FROM pivot_table
UNPIVOT INCLUDE NULLS
(yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode; |
| |
| Set Operators |
| INTERSECT |
<expression> INTERSECT <expression> |
SELECT DISTINCT tablespace_name
FROM all_tables
INTERSECT
SELECT DISTINCT tablespace_name
FROM all_indexes; |
| MINUS |
<expression> MINUS <expression> |
SELECT DISTINCT tablespace_name
FROM all_tables
MINUS
SELECT DISTINCT tablespace_name
FROM all_clusters; |
| UNION ALL |
<expression> UNION ALL <expression> |
SELECT DISTINCT tablespace_name
FROM all_tables
UNION ALL
SELECT DISTINCT tablespace_name
FROM all_indexes; |
| UNION |
<expression> UNION <expression> |
SELECT DISTINCT tablespace_name
FROM all_tables
UNION
SELECT DISTINCT tablespace_name
FROM all_indexes;
|