Wednesday, March 22, 2006
Oracle SQL : I
SELECT *|{[DISTINCT] column|expression|group_function(column) [[AS] alias],....)}
FROM table
[WHERE condition(s)]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY {columns,column alias,expr} [ASC|DESC]];
DISTINCT : eliminates duplicate rows.
ASC : NULL values are displayed last. DESC : NULL values are displayed first.
DESCRIBE table;
Table columns descriptions:
-------------------------------------------------------
NUMBER(p,s) :
VARCHAR2(s) : variable length character value of max size s.
DATE : date between 1-Jan-4712BC & 31-Dec-9999AD.
CHAR(s) : fixed length character value of size s.
1. Arithmetic Expressions [BMDAS : revised BODMAS? :) ]:
*
/
+
-
2. Concatenation operator:
|| : same as concat(string,string)
Eg : select last_name||' :1month salary='|| salary FROM employees;
3. Comparision conditions:
=
>
>=
<
<=
<>, !=, ^=
4. Other Comparison conditions:
IS NULL , IS NOT NULL
LIKE : match a character patten.
% : zero of more characters. eg : LIKE 'S%'
_ : one character. eg : LIKE '_O%'
To search for actual wide card characters use ESCAPE.
eg: WHERE job_id LIKE '%sale\_%' ESCAPE '\';
IN(set)
BETWEEN ... AND ...
Logical conditions:
NOT
AND
OR
Rules of precedence:
1. Arithmetic operators
2. Concatenation Operators
3. Comparison conditions
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. NOT
7. AND
8. OR
null : unavailable, unassigned, unknown or inapplicable.
Arithmetic expressions with a null component will return null. x divide by a null value returns null and not error.)
Literals : String & date literals are to be enclosed in single inverted commas (' '), number need not.
FROM table
[WHERE condition(s)]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY {columns,column alias,expr} [ASC|DESC]];
DISTINCT : eliminates duplicate rows.
ASC : NULL values are displayed last. DESC : NULL values are displayed first.
DESCRIBE table;
Table columns descriptions:
-------------------------------------------------------
NUMBER(p,s) :
VARCHAR2(s) : variable length character value of max size s.
DATE : date between 1-Jan-4712BC & 31-Dec-9999AD.
CHAR(s) : fixed length character value of size s.
1. Arithmetic Expressions [BMDAS : revised BODMAS? :) ]:
*
/
+
-
2. Concatenation operator:
|| : same as concat(string,string)
Eg : select last_name||' :1month salary='|| salary FROM employees;
3. Comparision conditions:
=
>
>=
<
<=
<>, !=, ^=
4. Other Comparison conditions:
IS NULL , IS NOT NULL
LIKE : match a character patten.
% : zero of more characters. eg : LIKE 'S%'
_ : one character. eg : LIKE '_O%'
To search for actual wide card characters use ESCAPE.
eg: WHERE job_id LIKE '%sale\_%' ESCAPE '\';
IN(set)
BETWEEN ... AND ...
Logical conditions:
NOT
AND
OR
Rules of precedence:
1. Arithmetic operators
2. Concatenation Operators
3. Comparison conditions
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. NOT
7. AND
8. OR
null : unavailable, unassigned, unknown or inapplicable.
Arithmetic expressions with a null component will return null. x divide by a null value returns null and not error.)
Literals : String & date literals are to be enclosed in single inverted commas (' '), number need not.
Advertisements
|
ARCHIVES
|
Recent Postings
|
