1. NUMBER( 7,2)
= At-most 7 digits
= At-least 2 digits after decimal.
= 123456 ( Not valid )
= 1234.567 ( valid )
= 12345.45 ( valid )
2. Execution of clauses:
a.
FROM
b.
WHERE
c.
GROUP BY
d.
HAVING
e.
SELECT
f.
ORDER BY
3. Queries are not case sensitive but values are case sensitive.
a.
SELECT ename, JOB, deptNo from eMP where ename like ‘smith’;
b.
SELECT ename, JOB, deptNo from eMP where ename like ‘SMITH’;
These two queries are different.
4. Alias Names:
a.
ename as “Employee name” valid
b.
ename as ‘Employee name’ Not
valid
c.
ename as Employee Name Not
valid
d.
ename as EmployeeName valid.
5. NULL values
a.
NULL != 0
b.
NULL != ‘ ’
c.
NULL != NULL
d.
NULL + 123 = NULL
e.
NULL || ‘ABCD’ = ABCD
f.
NULL / 0 = NULL
g.
Select * from emp where comm = NULL; (doesn’t
work)
h.
Select * from emp where comm != NULL; (doesn’t work)
i.
Select * from emp where comm IN (NULL); (doesn’t work)
j.
Select * from emp where comm IS NULL; (work)
k.
Select * from emp where comm IS NOT NULL; (work)
l.
For Integer values; use NULL explicitly
m. For Char,
varchar values ; use ‘’ explicitly.
6. Date Fields
a.
Date 1+ Date 2 Not valid
b.
Date 1 – Date 2 returns no of days
c.
Date 1 + 10 add 10 days to Date1.
d.
Date 1 – 10 Subtracts 10 days from Date1.
e.
SYSDATE + 10 add 10 days to current date.
f.
’01-JAN-2012’ +10 Not valid
g.
TO_DATE( ’01-JAN-2012’ ) +10 add
10 days to respective date.
7. Char fields
a.
VARCHAR2(4) abcde Error: Length is too large
b.
VARCHAR2(4) abdf Valid
c.
Select ‘abcd’ from dual; valid
Select “abcd” from dual; In
valid
d.
To display this string: “She’s
very beautiful.”
We need to use ’’.
i.e, Select ‘She’’s very beautiful.’ From dual;
8. To get Distinct or UNIQUE records, we can use
a.
DISTINCT (or)
b.
UNIQUE
=> Select
DISTINCT deptno from emp;
=> Select
UNIQUE deptno from emp;
9.
Select ‘4*4’ from dual; =
4*4
Select 4*4
from dual; = 16.
10. Primitive Data types are
: NUMBER, VARCHAR2, DATE.
11. CHAR vs VARCHAR
a.
When we are Using CHAR datatype, memory is wasted because:
CHAR(6)
= ‘ABC’.
|
A
|
B
|
C
|
|
|
|
ABC
is filled in the first 3 bits and remaining bits are filled with NULL.
b. When we
are Using VARCHAR datatype, memory is
not wasted because:
VARCHAR(6)
= ‘ABC’.
|
A
|
B
|
C
|
ABC
is filled in the first 3 bits and remaining bits are left empty.
12. CHAR, VARCHAR vs NCHAR,
NVARCHAR.
CHAR, VARCHAR supports ASCII characters.
Range
= 0-255
NCHAR, NVARCHAR
supports UNICODE characters.
Range
= 0-65535
13. Metadata Tables:
a.
USER_TABLES: Current schema tables
b.
ALL_TABLES: All schema tables
c.
USER_TAB_COLUMNS: All
column details of current schema tables.