SQL

Introduction

It's basically a fancy version of Excel.

Idea : You take a database and use SQL commands in order to interact with it

Temporary Tables : this is basically a buffer in Emacs.

Window functions : These allow you to take data from a selection of rows and return some values in a new column, for each of those rows

CETs : These are just what you call functions in SQL

Queries

Transactions

Collections of queries

BEGIN TRANSACTION name

query1 … queryN

Commit / Rollback / Savepoint

COMMIT: Does the actual queries on the database

ROLLBACK: Undoes the queries you just did (can only be used if you haven't committed)

SAVEPOINT: Creates checkpoints that you can rollback to (again, this only works if you haven't committed)

RELEASE SAVEPOINT: Removes a savepoint

CTEs

Common Table Expressions

These are basically function versions of transactions

Order

  1. From and Join
  2. Where
  3. Group by
  4. Having
  5. Select
  6. Distinct
  7. Order by
  8. Limit / Offset

Select / From

Where

Order by

Limit / Offset

Join

The join is a certain kind of pullback on the sets that describe the tables

Inner Join

returns rows when there is a match in both tables

Left Join

Keeps rows from A even if they have no matching row in B

The right join is the dual

The full join keeps all rows, regardless of matching

Self-Join

You can join a table onto itself. This is typically done in the case of hierarchical data

Group by

Aggregate functions like MAX(), SUM(), etc.

HAVING lets you filter the groups

Tables

Constraints

When you create a table, you usually impose constraints on the kind of data that does into it

NOT NULL: prevents column from taking null values

UNIQUE: all entries in the column must be unique

PRIMARY KEY: entries in the column must be both unique and not-null

FOREIGN KEY / REFERENCES: entries in the column must reference an existing record in another table (usually by a primary key on the other table)

CHECK: all entries in the column must satisfy a given condition

DEFAULT: sets a default value in all the entries in the column, if no other value is specified

Modifying

Insert Into Values

Update Set Where

Delete From Where

Alter Table Rename to

Adding Columns

Alter Table Add/Drop

Temporary Table

Stores data temporarily Automatically deleted when session or transaction ends

Temp table names start with # (it automatically makes them temp)

Pivots

A pivot is something like a transposition; it swaps rows and columns

Triggers

You can set a trigger so that something happens when you do something to a table

So if you don't want someone to be able to modify a table

Or you could have a trigger that goes off when something is modified

You can choose whether the action of the trigger is applied before or after the thing the user is doing

When not to use

Stored Procedure

These are basically CTEs that are more flexible

They're very similar conceptually, but they have different interpretations

Optimization

Indexes

Basically an index is made up of a copy of the column in the table, and corresponding pointers to the table

(copyentry , pointer)

The entries of the index are then sorted.

The point is that now, if you want to search that column you can do a binary search rather than a linear one.

Implementation

They're usually either

Cost

You have a memory cost of storing the index And a time cost of the sorting that the index does

Types

Clustered indices

Non-clustered indices are the usual version of indices

Cursors (Avoid)

Cursors are very slow

They basically just give you a way of doing operations on each row individually

Implicit Cursors

Insert, Update, Delete all create implicit cursors

Explicit Cursors

User-defined cursors

Declare Cursor For Select From

Open

Fetch { First/Last/Next/Prior/Absolute n/Relative n } From cursorname Into

Close Deallocate cursorname

Online Transaction Processing (OLTP)

Processes user requests in real-time

Things like ATMs, or Amazon's order system

Normalized data schemes

Online Analytical Processing (OLAP)

Designed for data analysts to do data transformations and processing

Things like fraud detection

OLAP (hyper)Cube

A multi-dimensional array of data

Slices are hyperplanes of the cube (you fix one dimension's value, to get an (n-1)-dim subset )

Dices are made from slices

Drill up/down

Roll-up

Pivot

Difficulty Levels

Intermediate

Advanced