SQL

I am using Microsoft SQL Server these days, so this cheatsheet may accidentally assume that you use that. I’ll try to specify DBMSes where possible, and otherwise go through and verify DBMS compatibility.

I want to rework this cheatsheet to verify DBMS compatibility later.

Resources

References:

• My theory notes on: The Relational Model and Relational Algebra
  • I will assume that you are familiar with the basic relational model language from these notes.

Basic Statements

SELECT (read)

SELECT * FROM MyTable;

SELECT
    MyCol1,
    MyCol2,
    MyCol3,
    SUM(MyCol4) AS MySum
FROM MyTable
GROUP BY
    MyCol2,
    MyCol3
WHERE 
    MyCol1 = '1969-04-01'
    AND MyCol2 IN ('mystr1', 'mystr2')
    AND (
        MyCol3 <= 4
        OR MyCol3 BETWEEN 10 AND 20
    )
HAVING
    SUM(MyCol4) > 2
ORDER BY
    MyCol1 ASC,
    MyCol3 DESC
;

• BETWEEN is all inclusive.
• ORDER BY is ASC by default if you omit the ASC/DESC.
• (NOTE: List out aggregate functions?)
• (NOTE: I haven’t tested the whole query yet.)

SELECT *
FROM
    MyTable1 AS MyAlias1
    LEFT OUTER JOIN MyTable2 AS MyAlias2 ON
        MyAlias1.MyCol1 = MyAlias2.MyCol2 AND
        MyAlias1.MyCol3 = MyAlias2.MyCol4
WHERE
    MyAlias2.MyCol5 IS NULL
;

• Alternative joins in place of LEFT OUTER JOIN:
  • JOIN (it’s the same as INNER JOIN)
  • LEFT JOIN (it’s the same as LEFT OUTER JOIN)
  • RIGHT JOIN (it’s the same as RIGHT OUTER JOIN)
  • FULL JOIN (it’s the same as FULL OUTER JOIN)
  • CROSS JOIN (this produces the cartesian product of two tables)
• The ON keyword is usually invalid syntax for CROSS JOIN.
• (NOTE: I haven’t tested all join keywords yet.)

SELECT * FROM MyTable1
UNION
SELECT * FROM MyTable2
;

• (NOTE: I haven’t tested the union keyword yet.)

INSERT INTO (insert new rows)

TODO

UPDATE (modify specified rows)

TODO

DELETE (delete specified rows)

TODO

CTEs

TODO

Correlated Subqueries

TODO (it’s probably not good to use correlated subqueries, but we should outline it anyway.)

PostgreSQL

TODO

Microsoft SQL Server

Indexes

(TODO: What’s the difference between clustered and nonclustered index? I’m assuming at the moment that both are B-tree family data structures but clustered means the records are basically at the leaves while nonclustered stores references that let you find the record somehow. It could be useful to dig deeper into these details, but for my purposes at the moment, I probably don’t need to know.)

TODO

Query Optimization

(TODO: Maybe outline how to use the SSMS UI for this.)

TODO

References:

• Logical and physical showplan operator reference (learn.microsoft.com)
• I found this playlist useful for learning for the first time.

Transact-SQL

TODO

References:

• Transact-SQL Reference (learn.microsoft.com)