Note: SQL

###Schema

• Snowflake
• Star

###Relation algebra

• Selection
• Projection

• Cartesian Product

  Join: they are all just syntactic sugar

  • natural join
  • left outer join
  • right outer join
  • full outer join
  • left semi join
  • right semi join
• Rename
• Union
• Minus

###Some gotcha - The use of GROUP BY

followed by```having``` - duplicate vs. redundant

###Refs

• Xquery
• Codd’s theorom

###Feb 4 Class

• SQL does a hell lot of join optimization: simply because join is the most common operation in SQL query

• sub-query:

  • correlated
  • uncorrelated

  normally uncorrelated has a better performance because correlated means do a sub-query for each different value.

• Don’t think SQL execution in terms of algorithm sense: because the execution time really depends on the optimization the database does. There may be different indexes for optimization.
• SQL tries to get around NULL because normally nobody expects it to throw out some errors. But it brings the consequence that operations involving NULL can have unexpected result.
• However, for programming languages, they care more about correctness, so basically it will throw exceptions or errors.
• Eg: in WHERE, only output rows whose outcome is TRUE, but reject UNKNOWN/FALSE