What are "common" (needed/useful to know) SQL Database-operations ?

Question

I am looking for a good list (cheat-sheet?) of SQL tasks/commands for Database operations (although probably all of them are for that).

Stuff like:

• JOIN - merge two tables
• WHERE - subset tables
• And so on

But also a bit more elaborate tasks.

My motivation is that I want to know SQL a bit better, and I thought it would be fun to take a bunch of these tasks and see how to do them with SQL vs how to do them in a language I know (R).

And then maybe put these in a place like this:

http://rosettacode.org/wiki/Category:Database_operations

(I wasn't able to find this when searching - but if this is a duplicate I'd be happy to close this thread)

Answer 1

Cheat sheet for joins (Left, Inner, Right, Outer etc) Look at a venn diagram and they basically map i.e. inner join is where the two/three circles all overlap, left is the area in the left circle that doesn't overlap etc.

Answer 2

You have exactly 4 basic SQL statements:

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

JOIN, WHERE etc are only constituents clauses in one of these 4 statements

A View or stored proc or trigger or functions are simply wrappers for one or more of these 4 statements.

Answer 3

• CREATE TABLE
  • PRIMARY KEY, UNIQUE, NOT NULL, and CHECK constraints.
  • DEFAULT
  • REFERENCES
• DELETE
• DROP TABLE
• INSERT
• REPLACE (if supported)
• UPDATE
• SELECT
  • DISTINCT
  • GROUP BY and HAVING
  • ORDER BY
  • LIMIT and OFFSET (if supported)
  • JOIN (INNER and OUTER)
  • UNION
• Transactions: BEGIN, COMMIT, and ROLLBACK.