More SQL Examples

This is a further crib sheet, with lots of SQL examples - well a few any way. It's just a quick of finding things that I don't use very often.

SQL Example Select Statements

This is a list of basic SQL statements. It is, more than anything, a reminder of syntax - nothing more.

Example Statements:

Select count(*) from emplyee_tbl
where last_name = ‘smith’;

Select count(*) from emplyee_tbl
where last_name like‘sm%’;

Select sum(salary) from emplyee_tbl;

Select avg(salary) from emplyee_tbl;

Select max(salary) from emplyee_tbl;

Select min(salary) from emplyee_tbl;

Select last_name, first_name from emplee_tbl
group by last_name;

Select last_name, first_name from emplee_tbl
where city <> ‘London’
group by last_name
having avg(salary) > 20000;

Column Concatenation

Select ‘John’ + ‘son’ from emplyee_tbl;

Select city || ‘,’ || road from emplyee_tbl;

Oracle – concatenates the values from city and road into one value placing a comma between them.

Example Output

London,
Cranberry Road
Norwich,
Station Road
Seattle,
Orange Road.


Translate – converts characters in a string.

Select city, translate(city, ‘ABC’, ‘XYZ’) from emplyee_tbl;

Select city from employee table swapping X for A, Y for B and Z for C.

Replace – does a similar job to translate.

UPPER/LOWER – change case of a string

Select UPPER(city) from emplyee_tbl;

Substrings.

Oracle – SUBSTR

SUBSTR(column name, starting position, length)

Select substr(city, 2, 3) from emplyee_tbl;

Example output would be:

LONDON becomes OND – stating location of 1 NOT 0.
HUSTON becomes UST.

Instr – searches a string of characters for a specific set of characters reporting their position.

LTRIM, RTRIM – trims characters from strings – left and right.

Example – left trim.

Select position, ltrim(position,’sales’) from emplyee_tbl;

Position               LTRIM(position,
-------------------  -----------------------
Marketing            Marketing
Sales Manager     Manager
SalesMan             Man

Decode
Searches a string for a value or string, if the string is found then display an alternative sting.

DECODE(Column name,’search1’, ‘return1’, [‘search2’, ‘return2’, ‘default value’])

Select city,
decode(city,’indianapolis’, ‘indy’ , ‘London’, ‘Lon’, ‘Anywhere’) from emplyee_tbl;

City                    Decode
----------------  -------------------
London              Lon
Indianapolis       indy
Seatle                Anywhere

Length of a String

Get the length of a string – Length(string name).
Get data from one expression if the other is NULL – NVL(exp)

Select pager, nvl(pager,999999) from emplyee_tbl;

Pager              nvl(pager,999999)
--------------     -------------------------
999999              999999

Pad a string with characters – left and right – LPAD/RPAD

Select LPAD(last_name,20,’.’) from emplyee_tbl;

Last_name
……………smith // note 15 dots before smith.

SubQueries

Select and Select – the most common

Syntax:

Select column_name [, column_name ]
From table1 [, table2]
Where column_name operator (
Select column_name [, column_name]
From Table1 [,table2]
[where]

example:

select e.emp_id, e.last_name, e.first_name, ep.pay_rate
from emplyee_tbl e, emplyee_Pay_tbl ep
where e.emp_id = e.emp_id
and ep.pay_rate > (select pay_rate from emplyee_pay_tbl
          where emp_id = ‘123456’);

Subqueries with INSERT

Change the INSERT statement to read the result of a select.

Insert into table_name [(column1 [column2])]
Select [ *|column1 [column2]])
From table1 [, table2]
Where value operator

Insert into rich_employees
Select e.emp_id, e.last_name, e.first_name, ep.pay_rate
From emplyee_tbl e, emplyee_pay_tbl ep
Where e.emp_id = ep.emp_id;

You can go on to next other selects within this first select – as above.

Subqueries with UPDATE

Change the UPDATE statement to read the result of a select.

Update table
Set [(] column_name [, column_name ) ] =
(select [(] column_name [, column_name )]
from table
[ where ]
Example

Update emplyee_pay_tbl
Set pay_rate = pay_rate * 1.1
Where emp_id in (select emp_id
From employee_tbl
Where city = ‘indianaplis’);Subqueries With Delete

You can also add a sub-query into the DELETE statement in the where clause.

Delete from emplyee_pay_tbl
Where emp_id = (select emp_id from emplyee_tbl
where last_name = ‘Jones’);

Examples of CREATE USER and GRANT

First...

CREATE USER [User Name] IDENTIFIED BY [Password]

Then, grant the new user some privileges.

GRANT [Privilege] TO [User Name]

grant select on [table name] to [group name];

Synonym Example

Dropping a synonym on a table

drop public synonym [table Name];

Creating a new Synonym

create public synonym [table name] for [user name].[table name];

Grants Example

Add Grants to the table for a group

grant select on [table name] to [group name];


Tidy your data with initcap

The initcap function sets the first character in each word to uppercase and the rest to lowercase.

The syntax for the initcap function is:

initcap( string )

'string' is the string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.

initcap('hello world'); -- would return 'Hello World'
initcap('CAPTAIN DEBUG'); -- would return 'Captain Debug'