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.

Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES

This page is powered by Blogger. Isn't yours?

Recent Postings

Google