SQL（Structured query language)

Relational Terminology

• Database: Set of named Relations

• Relation(Table):

  • Schema: description("metadata")

  • Instance: set of data satisfying the schema

• Attribute: (Column, Field) 

• Tuple:(Record, Row)

Quick check：Examples given below  are all not relation

A: Beacuse the second instance dose not match the schema  

A: Two colums have the same name

 

A: Tuple can not be a colum of the schema

Two sublanguage of sql:

• DDL(Data Defination Language):
  • Define and modify schema
• DML(Data Manipulation Language):
  • Queries can be written intutively

Primary Key:

• provides a unique 'lookup key' for the relation
• cannot have duplicate values
• can be made up of > 1 column (e.g. You can combine firstname and lastname as primary key) 

Aggregate:

• aggregation function includes min,max,count,avg,sum
• Having is applied after grouping and aggregation.
•  Gouping is applied before aggregation
• Having can only be used in aggregate queries

        

DISTINCT

 

In the first case, it removes duplicate name first, and in the second case, it use count function first, then removes same number. 

Set Semantics

• Set: a collectiion of distinct element
• standard ways of manipulating / combing sets
  • UNION
  • INTERSECT
  • EXCEPT
• treat tuples within a relation as  elements of a set

-- R = {A,A,A,A,B,B,C,D}
-- S = {A,A,B,B,B,C,E}

select * 
from R UNION S

-- return {A,B,C,D,E}
 

select *
from R INTERSECT S

-- return {A,B,C}

select *
from R EXCEPT S

-- return {D}

Multiset Semantics:

• UNION ALL: sum of cardinalities
• INTERSECT ALL: min of cardinalities
• EXCEPT ALL: difference of cardinalities

/* 
   R = {A,A,A,A,B,B,C,D} = {A(4),B(2),C(1),D(1)} 
   S = {A,A,B,B,B,C,E} = {A(2),B(3),C(1),E(1)}
*/


select * 
from R UNION ALL S

/* return {A(4+2),B(2+3),C(1+1),D(1+0),E(0+1)}
       = {A,A,A,A,A,A,B,B,B,B,B,C,C,D,E}
*/

select *
from R INTERSECT ALL S

/* return {A(min(4,2)),B(min(2,3)),C(min(1,1)),D(min(1,0)),E(min(0,1))}

       = {A,A,B,B,C}
*/
select * 
from R EXCEPT ALL S

/* return {A(4-2),B(2-3),C(1-1),D(1-0),E(0-1)} 
       = {A,A,D}
*/

Jon Variants

•  [INNER | NATURAL | {LEFT | RIGHT | FULL}{OUTER}] JOIN
• INNER is default and NATURAL JOIN does not have on clause
• 'NATURAL'  meals equi-join for pais of attributes with same name  (avoid using natural join in general)

View: named queries

CREATE VIEW view_name as select_statement

• make development simpler
• often used for securitty
• not 'materialized'

e.g.

CREATE VIEW RedCount
AS
select B.bid, count(*)
from Boat B, Reserve R
where B.bid = R.bid and B.color = 'red'
group by B.bid

WITH: a.k.a. common table expression (CTE)