SQL Grammar

programming_language

conventions

tuple: a row in a table
relation: a table consisting of several tuples
database: several relations

relation algebra fundation

select, project, union, difference, Cartesian-product, rename
Union: the two relations must follow the same features and their domain won't conflict.

supplyment:

• intersection
• natural join: do Cartesian-product and selct the tuples with the same values on features from both the two relations.
• division: A$\div$B include all features in A but not in B
• assignment: temp <- operation expression

expand relation algebra

• generalized-projection: use arithmatic operations in projection
• aggregating function: sum, count, min, max
• outer-join: make up the quitted values to avoid info lose (left/right/full outer-join)

basic data type

• char(n) fixed length
• varchar(n)
• int
• smallint
• numeric(p,q) 有效数字，小数点位置从右到左
• real
• double
• float(n)

basic operations

create a table/relation

create table <table_name> (attr1 data_type,... ,
primary key(attrs), foreign key(attr) another_table)

prime key is unique and not null
foreign key is a reference to other table.

select, from, where

select <attrbutes>----projection
from <relations> ----- Cartesian-product
where <predicates> -----selection predicate

SQL returns a relation

select * means to select all attributes.
select distinct means to eliminate duplicates
select all means not to do that
name as T used to set an alias

insertion

add a tuple to a table
insert into <table_name> values(attr1 val1, attre2 val2...)
all attributes follow the order in the definition.
If you forget the order, use this:
insert into <table_name>(attr1,atr2,...) values(val1,val2...)

add an attribute to a table
alter table <table_name> add <attribute> <domain>

how to insert in batches?
One way to insert several tuples is:
insert into <table_name> select ... from ... where ...

delete

Note: delete clause only applies to ONE relation.
clear all tuples from the relation
delete from <table_name>
delete the table from the database
drop table <table_name>
delete an attribute from a table
alter table <table_name> drop <attribute>

join

<table_name1> natural join <table_name2>
Natural join returns tuples with the same value on the same attributes of both realtions.

<table1> join <table2> using(<attr1>,<attr2>...)
join tuples from two relations with the same values on the given attributes.

set order

order by <attr> desc/asc ascend default

string matching

where <attr> like '%abc%d_'
% matches any string
_ matches a single char
escape '/' means /% is just a %

set operations

(select A from B) union/intersect/except (all) (select C from D)

union, intersect, except abandon dupllicates automatically

### aggregating functions
avg min max sum count
group by <attr> construct groups according to features to which aggregating functions apply
having <predicates> predicates apply to groups generated by group by

If there are both where and having subsentence in a query, SQL culculates predicates in where first and forms groups according to group by, before applying having. Groups not fitting having predicates will be abandaned and the left return to select as result.

useful components

detect null

is null / is not null /

check whether a tuple is in the relation

<tuple> in <relation>
not in

check an empty relation

exsists

compare

some at least one
all for all

between ... and ...

describe numeric range

with clause

decomposite the complicated query by defining temporary tiny view

with tempo_view(attr1,attr2...) as 
    select ... from ... 
select ...
from tempo_view
where ...

update

change the value of some features without changing the whole tuple.

update <table_name>
set <old_attr> = <new_attr>
where ...

case clause

case 
    when <pred1> then <result1>
    when <pred2> then <result2>
    ...
    eles <result0>
end

Advanced operations

grant

grant <operations> on <attributes> to <users> (with grant option)
authorize other users with centain operations (including the operation to autherize)
revoke <operations> on <attributes> to <users>
take back that authorization

integrity constrain

in case of data destruction

create table <table_name> (attr1 data_type,... ,
primary key(attrs), 
foreign key(attr) another_table
check(attr1 in (val1,val2,val3...))
)

the value of attribute1 must be among the given values.

transaction

A transaction consists of query expressions and update expressions.
When a SQL clause was excuted, a transaction started.
The following clause end a transaction:
commit work (submit the update made by SQL above and automatically start a new transaction)
or
rollback work

view

view is not actual logic model but a virtual relation shown to users.

• create a view
  create view <name> as <query expression>
  query expression means "select from where" clause.
  We can explicitly indicate the features of the view
  create view <name>(attr1,attr2...) as <query expression>
  The database system stores the definition of the view rather than the result of the query expression of the view.

More: mateiralized view & view maintenance.

linkage relations

inner join
left outer join
natural right outer join
full outer join

HackerRank Experience

• count minus
  select count(city)-(select count(distinct city) from table) from table