reading-notes

SQL

Source: SQLBolt

What is SQL

‘Structured Query Language’
Query, manipulate, transform data in a relational database
SQlite, MySQL, Postgres, Oracle, MS SQL Server

Relational Databases

Collection of related tables
Table is similiar to a spreadhseet
Columns and rows of data

Getting Data

SELECT specify column names from a table, or * for wildcard
```
SELECT column, another_column
FROM mytable
```

SELECT DISTINCT to filter out duplicates

SELECT DISTINCT column another_column, …
FROM mytable
WHERE condition(s);

WHERE to filter

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

ORDER BY to sort

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

LIMIT to reduce results

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

Manipulating Data

INSERT to add data

Must specify all data, unless the database has defaults defined for some columns

INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

INSERT INTO boxoffice
(movie_id, rating, sales_in_millions)
VALUES (1, 9.9, 283742034 / 1000000);

UPDATE to update data

UPDATE mytable
SET column = value_or_expr,
    other_column = another_value_or_expr,
    …
WHERE condition;

DELETE to delete data
```
DELETE FROM mytable
WHERE condition;
```

Creating & Manitpulating Tables

CREATE to create a table

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

CREATE TABLE movies (
  id INTEGER PRIMARY KEY,
  title TEXT,
  director TEXT,
  year INTEGER,
  length_minutes INTEGER
);

ALTER to modify table columns

Adding new columns

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint
    DEFAULT default_value;

Removing columns

ALTER TABLE mytable
DROP column_to_be_deleted;

Changing table name

ALTER TABLE mytable
RENAME TO new_table_name;

DROP to remove table
```
DROP TABLE IF EXISTS mytable;
```

Resources

W3Schools – Practice

SQL (Structured Query Language) in one page – Cheatsheet

Back