PostgreSQL: Intermediate PostgreSQL

Aggregates:

Return # of rows: COUNT(*)
Return count of NON-NULL values in a column: COUNT(colname)
SUM(colname)
MAX(colname)
MIN(colname)
AVG(colname)

Grouping aggregates: GROUP BY
select [col], aggregate#1, aggregate#2 from [tbl] group by [col], [aggregate#1];

Aggregate conditionals: HAVING
select [col], aggregate#1 from [tbl] group by [col] having [aggregate condition];

Alias:
select [col] from [tbl] [alias] where [alias].[col?] = [condition];
select [col] from [tbl] [alias1], [tbl] [alias2] where [condition];

Joining:
select [tbl].[col] from [tbl1], [tbl2] where [tbl1].[col] = [tb2].[col];

Distinct: Return distinct col1+col2 pairs only
select distinct [col1], [col2], count(*) from [tbl] where [condition];

Unjoined table: Cartesian product
select * from [tbl1], [tb2];

Nonequijoin: (Could be any of <, <>, !=, >)
select [col] from [tbl] [alias1], [tbl] [alias2] where [alias1].[col1] <> [alias2].[col2];

OIDs: Automatically incremented & always distinct
select oid, [col] from [tbl];
create table [tbl] ( customer_oid OID );

AUTO-Numbering / Sequences:
create sequence [sequence name];
create table [tbl] ( customer_id INTEGER DEFAULT nextval('[sequence name]') );
insert into [tbl] values ( nextval('[sequence name]') );
currval('[sequence name]');
setval('[sequence name]', #);

create table [tbl] ( customer_id SERIAL ); // alternative

UNION: Combined results, A and B
select [col1] from [tbl1] union select [col2] from [tbl2];
select [col1] from [tbl1] union all select [col2] from [tbl2]; // with duplicates

EXCEPT: A but not in B
select [col1] from [tbl1] except select [col2] from [tbl2];

INTERSECT: A and in B
select [col1] from [tbl1] intersect select [col2] from [tbl2];

SUBQUERIES: Select within a select, bypass joins
select [col2] from [tbl2] where [col2] <> ( select [col1] from [tbl1] where [condition] );

SUBQUERIES W/ LISTS: Using IN/NOT IN/ANY/ALL/EXISTS/NOT EXISTS
select [col2] from [tbl2] where [col2] in ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] not in ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] any ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] all ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] exists ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] not exists ( select [col1] from [tbl1] where [condition] );

UPDATE + FROM:
update [tbl1] set [col]=[val] from [tbl2] where [condition w/ tbl1 + tbl2];

INSERT USING SELECTS: transfer data from table 1 to 2
insert into [tbl1] (name) select 'danny' from [tbl2];

SELECT INTO: copy table 1 to 2
select [col1], [col2] into [tbl2] from [tbl1];

Text concatenation: ||
'Danny' || 'Cool' = 'DannyCool'