SQL Structured Query Language

(1) SORT DATA

select variables

from table

order by variable desc;

(2) FILTER DATA

between

is null

=  <>  <=

in ()   not in ()

(3) WILDCARD FILTER

Wildcard Description
% A substitute for zero or more characters        where var like ‘fish%’
_ A substitute for a single character                where var like ‘_ inch’
[charlist] Sets and ranges of characters to match         where name like ‘[JM]%’  first letter J or M
[^charlist] Matches only a character NOT specified within the brackets

(4) CONCATENATE

Oracle ||:  select city || ‘,’ || state || ‘,’ || zip as address

SQL Server +: select city + ‘,’ + state + ‘,’ + zip as address

trim padded spaces from right side with RTRIM() function : select RTRIM(city) || ‘,’ || RTRIM(state) || ‘,’ || RTRIM(zip)

(5) DATA MANIPULATION FUNCTION

len(), upper(), lower(), trim(), substr() Oracle / substring() SQL Server, sysdate Oracle/getdate() SQL Server, to_number(), to_char() Oracle / convert() SQL Server

soundex(): match all data that sound similar to the one you assign e.g. where soundex(name)=soundex(‘Mike Green’)

date: 1. SQL Server   where datepart (yy, order_date)=2001 and datepart (mm, order_date)=2

2. where order_date between to_date(’01-Jan-2001′) and to_date(’31-Dec-2001′)

(6) AGGREGATE FUNCTION

avg(), count(), max(), min(), sum(), avg(distinct variable)

(7) GROUP DATA

select ID, count(*) as orders

from Orders

group by ID

having count(*) >=2;

(8) JOIN TABLES

1) left join: returns all rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SQL LEFT JOIN

2) inner join is the same as JOIN: selects all rows from both tables as long as there is a match between the columns in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

SQL INNER JOIN

3) full outer join: returns all rows from the left table and from the right table

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN

4) Union: combines the result of two or more SELECT statements.each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

(9) INSERT DATA

insert into data1 (name, age) values(‘Peter’, 25);

insert into data1 (name, age) select name, age from data 2 where name=’Peter’;

copy one table to another:

select *                                 create table data1 as

into data1                             select *

from data2;                           from data2;  Oracle

(10) UPDATE AND DELETE DATA

update customers set cust_email=’spring14@yahoo.cn’ where cust_id=’001′;

delete from customers where cust_id=’001′;

delete table:  drop table customers;

create index prod_index on table(product);

(11) OTHERS

SELECT DISTINCT column_name,column_name
FROM table_name;

SELECT SUBSTRING(column_name,start,length) AS sth FROM table_name             e.g. substr(proc, -1, 1) not in (‘T’, ‘F’);

SELECT ROUND(column_name,decimals) FROM table_name                                  e.g. ROUND (PER25 * decode(RLV,0,100, RLV)*(1-c.fct),2);

increase speed: e.g. CREATE TABLE name PARALLEL 8 nologging compress for query high as

use partition instead of group by: e.g. SUM(FREQ) OVER (PARTITION BY PROC, MOD,GEOZIP)

create temporary table: e.g. WITH new_name AS (SELECT * from table where…) select * from new_name where…

case when: e.g.  SELECT CASE WHEN (N = 1 OR CHARGE_SD = 0) THEN NULL
ELSE MD3/((N-1)*POWER(CHARGE_SD,3)) END AS skewness from…

macro: SET DEFINE~  DEFINE VAR=’name’  select ~VAR from table;

nvl(): available in Oracle, and not in MySQL or SQL Server. replace NULL value with another value. ifnull() in MySQL and the isnull() in SQL Server.

e.g. nvl(trim(a.mod),’0′) not in (’20’, ’22’, ‘SG’)

references: http://www.w3schools.com/sql/default.asp

(12) PRACTICE

There’s no better way to improve SQL skills than to practice with some real SQL interview questions. The following SQL practice exercises were actually taken from real interview tests with Google and Amazon.

1. The names of all salespeople that have an order with Samsonic.

2. The names of all salespeople that do not have any order with Samsonic.

3. The names of salespeople that have 2 or more orders.

4. Write a SQL statement to insert rows into a table called highAchiever(Name, Age), where a salesperson must have a salary of 100,000 or greater to be included in the table.

1. select a.name from Salesperson a, Customer b, Orders c

where b.name = “Samsonic”

and b.ID=c.cust_id

and c.salesperson_id = a. ID;

2. select a.name from Salesperson a, Customer b, Orders c

where b.name not in “Samsonic”

and b.ID=c.cust_id

and c.salesperson_id = a. ID;

3. select a. name, count(c.salesperson_id) from Salesperson a, Orders c

where count(c.salesperson_id)>1

and c.salesperson_id = a. ID;

4. insert into highAchiever (Name, Age)

(select Name, Age from Salesperson where salary>100,000);

create table highAchiever as

select Name, Age from Salesperson

where salary>100,000;

Salesperson Customer
ID Name Age Salary
1 Abe 61 140000
2 Bob 34 44000
5 Chris 34 40000
7 Dan 41 52000
8 Ken 57 115000
11 Joe 38 38000
     ID Name City Industry   Type
4 Samsonic pleasant J
6 Panasung oaktown J
7 Samony jackson B
9 Orange Jackson B
Orders
Number order_date cust_id salesperson_id Amount
10 8/2/96 4 2 540
20 1/30/99 4 8 1800
30 7/14/95 9 1 460
40 1/29/98 7 2 2400
50 2/3/98 6 7 600
60 3/2/98 6 7 720
70 5/6/98 9 7 150
Advertisements