Oracle syntax

1. Basic syntax: SELECT *[column name [[AS] alias], column name 2...] FROM table name [[AS] alias];

2. use|| Do concatenation: String in Oracle with single quotes.

Example: SELECT 'Name:'|| ename|| 'Monthly salary'|| sal FROM emp;

3. Use DISTINCT to eliminate duplicate content

4. WHERE clause: Filters the data using the conditions specified by WHERE, returning records (rows) that match the conditions.

SELECT *[|Column name [[AS] alias], Column name 2...]

FROM table name [[AS] alias]

WHERE conditions;

1)WHERE clauses can include operators, SQL operators, usage (), constants, columns, functions.

5. Operator:

1)Arithmetic: + - * /

2)Comparison: > >=

=,=,!=, between.. and..

How do I get records 5 through 10? subquery, join query

8. Sort the results:

SELECT *[|Column name [[AS] alias], Column name 2...]

FROM table name [[AS] alias]

WHERE condition

ORDER BY Sort Column Name [ASC| DESC], sort column name [ASC| DESC]...;

9. SQL functions: Some of the functionality provided by a database management system (DBMS) is encapsulated. Different database products provide different functions.

1)Single-row function: operates on only one row at a time and returns a value for each row.

Aggregate function: operates on multiple rows simultaneously, which return only one value.

2)Single-line function:

a) String: lower(char),upper(char),length(char),initcap(x)

b) Number: round(number,n)

c) datetime: sysdate Returns the current datetime of the operating system on which the database resides.

systemstamp Returns the current timestamp of the operating system on which the database resides.

last_day(d) Returns the date and time of the last day of the month on the specified date

trunc(d[,unit]) truncates datetime, unit: 'year',' month','day'

d) A dual table is a table that Oracle provides to any user, often used in SELECT statements that have no target table.

e) Transfer function:

to_char(datatime[,format]), format: yyyy,mm,dd,hh34,mi,ss

to_char(number[,format]), format: l, 9, ','

to_date(char[,format]), string--> datetime

to_timestamp(char[,format]), string--> datetimestamp

to_number(expr[,format])

f) Other functions:

nvl(expr1,expr2) Returns the value of expr2 if expr1 is empty, expr1 otherwise.

nvl2(expr1,expr2, expr3) returns expr2 if expr1 is not empty, expr3 otherwise.

decode(expr, search2,result1,search3,result2,...)

case expr when search2 then result1

when search3 then result2

...

else resultn

end