• SQL Server
• MySQL
• PostgreSQL
• SQLite
• Oracle

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000

SELECT...
LIMIT y OFFSET x 

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

id (1) tag ('night')
id (2) tag ('awesome')
id (3) tag ('night')

SELECT COUNT(*), `Tag` from `images-tags`
GROUP BY `Tag`

SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY COUNT(id) DESC
LIMIT 20

Answer :

SELECT COUNT(id) AS theCount, `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY theCount DESC
LIMIT 20

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

A    B
-    -
1    3
2    4
3    5
4    6

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

select * from TABLE where field = 1 or field = 2 or field = 3

select * from TABLE where field = 1 || 2 || 3

Answer:

select foo from bar where baz in (1,2,3)

• http://www.sqlite.org/cvstrac/wiki?p=ConverterTools (or snapshot at archive.org)

1. Load data from source database using the ORM class.
2. Store data in memory or serialize to disk.
3. Store data into destination database using the ORM class.

Answer :

answer : 

select a.name, b.address from tbla a 
join tblb b on b.fk_a_id = a.id where a.id=1

create table tbl_ab (a_id, a_name, b_address); 
-- (types elided)

insert tbl_ab select a.id, a.name, b.address from tbla a
join tblb b on b.fk_a_id = a.id 
-- no where clause because you want everything

select a_name as name, b_address as address 
from tbl_ab where a_id = 1;