SQL

SQL - Structured Query Language

Allows users to query, manipulate, and transform data from a relational database.

Most commonly, a data model is designed first, then data is deconstructed by the process of "normalization". This aims to turn your data in a basic form like into separate tables. E.g. one table for customers and one for orders

RDBMS

SQL is a language which has many Relational Database Management Systems (RDBMS) that all support the common SQL language standard but differ in the additional features and storage types it supports:

• SQLite, MySQL, Postgres, Oracle, Microsoft (MS) SQL Server

Table - collection of related data entries; it consists of columns and rows

• fixed # of labelled columns (a.k.a. attributes/properties/fields)

• any # of rows of data (a.k.a. record) (individual entries that exist on a table)

• Each table is identified by a name (ie. 'Cars')

SQL Statements

• SQL keywords are NOT case sensitive: select === SELECT

• Each statement ends in a ; (like Java)

SQL COMMAND	Description
SELECT	selects data from db
UPDATE	updates data in db
DELETE	deletes data from db
INSERT INTO	inserts new data into db
CREATE DATABASE	creates new db
ALTER DATABASE	modifies db
CREATE TABLE	creates new table
ALTER TABLE	modifies table
DROP TABLE	deletes table
CREATE INDEX	creates an index (search key)
DROP INDEX	deletes an index

> SELECT

• colloquially refered to as queries, which in itself is just a statement which declares what data we're looking for, where to find it in the db.

/* SYNTAX */
select <col_names> from <table_name>;

/* ex. Cars table: */
select Company, Price, Color from Cars

/* SELECT ALL */
select * from Cars;

> SELECT DISTINCT

• gets distinct values in specified column(s) of a table

/*
ex. Car Garage table --> gets Company, but no-repeat values
*/
select distinct Company from Garage;

> WHERE

• used to filter rows that are retrieved from table

• can be paired with select / update / delete

/* Syntax */
select * from Garage
where <condition>;

• The is built using operators (ex. follows above):

Operator	Description	Ex.
=	equal ('abc' | 123)	where Company='BMW'
>	greater than	where Price>50000
<	less than	where Price<50000
>=	greater than or = to	where Price>=50000
<=	less than or = to	where Price<=50000
<>	not equal ('abc' | 123)	where Color<>'Blue'
BETWEEN	between a certain range	where Price BETWEEN 10000 AND 20000
LIKE	search for a pattern	where Company LIKE 's%' (starts with s)
%	match sequence of chars (only w/ LIKE/NOT LIKE)	where col_name LIKE '%AT%' matches = ("AT", "ATTIC", "CAT", "BATS", ... )
-	used anywhere in a str to match a single char	where col_name LIKE 'AN_' matches=("AND", but not "AN")
IN	specify multiple possible values for col	where Company IN ('BMW', 'Tesla')

• note: text columns must use 'single-quotes' and numeric columns should not.

• Wildcard Characters for LIKE

You can chain multiple conditions in a WHERE clause using logical operators:

• AND

  /* displays a row from col1 if ALL 3 conditions are true */
  select col1
  from table_name
  where cond1 AND cond2 AND cond3;

• OR

  /* displays a row from col1 if ANY of the 3 conditions are true */
  select col1
  from table_name
  where cond1 OR cond2 OR cond3;

• NOT

  /* displays a row from col1 if cond1 is NOT TRUE  but the rest are */
  select col1
  from table_name
  where NOT cond1 AND cond2 AND cond3;

Now, we can construct more complex clauses:

between 1 and 10
not between 1.5 and 10.5
in (2, 4, 6, 8, 10)
not in (1, 3, 5, 7, 9)

> ORDER BY

• sorts the result-rows in ASC (default) or DESC order

/* all cars sorted by Price in ASC order */
select * from Garage
order by Price ASC;

• You can sort multiple columns too:

  /*
  orders by Price (asc) but if some rows have the same Price, it orders them by Name (desc)
  */
  select * from Garage
  order by Price ASC, Name DESC

• clauses LIMIT and OFFSET are commonly used with the ORDER BY clause.

  /*
  limit - reduce the number of rows to return
  offset - where to begin counting # rows from
  */
  SELECT a_column, another_column, …
  FROM mytable
  WHERE condition
  ORDER BY column ASC/DESC
  LIMIT num_limit OFFSET num_offset;

---

Multi-Table Queries with JOINs

Up to now, we've been working with a single table, but entity data in the real world is often broken down into pieces and stored across multiple orthogonal tables using a process known as normalization

Database Normalization

• every entity is organized into its own table

• process of organizing data in a database

• This includes creating tables and establishing relationships between those tables

Tables that share info about a single entity need to have a primary key that identifies that entity uniquely across the database; this is usually an auto-incrementing integer (but could be string/hash/...)

$ JOINs

• used to combine rows from 2 or more tables, based on a related column b/n them.

How to Learn SQL JOINs | LearnSQL.com

> (INNER) JOIN

• Returns records that have matching values in both tables

Gif of how inner join iterates through the tables

• matches rows from the first table and the second table which have the same key (as defined by the ON constraint) to create a result row with the combined columns from both tables.

• You can write either INNER JOIN or JOIN

  SELECT col, another_table_col, … FROM mytable
  INNER JOIN another_table 
      ON mytable.id = another_table.id

> LEFT (OUTER) JOIN

• Returns all records from the left table, and the matched records from the right table

Shows the Left join adding matches between the left and right table to the result table

• The result is NULL in the consecutive tables when there is no match

  SELECT col, another_table_col, … FROM mytable
  LEFT JOIN another_table 
      ON mytable.id = another_table.id

> RIGHT (OUTER) JOIN

• Returns all records from the right table, and the matched records from the left table

Shows how a right join iterates through the tables

• The result is NULL in the consecutive tables when there is no match

  SELECT col, another_table_col, … FROM mytable
  RIGHT JOIN another_table 
      ON mytable.id = another_table.id

> FULL (OUTER) JOIN

• simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table

gif showing part one of a full outer join: the left join

SELECT col, another_table_col, … FROM mytable
FULL JOIN another_table 
    ON mytable.id = another_table.id

---

NULLs

• It's always good to reduce the possibility of NULL values in databases bc they require special attention when constructing queries, constraints and when processing the results.

• Alternatives to NULL values include:

  • 0 (for numerical data)

  • '' (for text data)

• But if your database needs to store incomplete data, then NULL values can be appropriate if the default values will skew later analysis

/* Gets employees whose building is null */
SELECT * FROM employees
WHERE Building IS NULL;

/* optionally: not null */
WHERE Building IS NOT NULL;

---

Queries with expressions

you can also use expressions to write more complex logic on column values in a query.

The use of expressions save time but can also make the query harder to read; so it's recommended that you use a descriptive alias AS keyword adfter the SELECT part of the query.

/* syntax */
SELECT col_expression AS expr_description, …
FROM mytable;

/* ex. */
SELECT speed / 2.0 AS half_speed
FROM physics_data

/*
In addition to expressions, regular columns and even tables can also have aliases.
*/
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

---

Queries with aggregates

Aggregate Function - SQL performs a calculation on multiple values and returns a single value

SQL provides many aggregate functions:


GROUPED AGGR. FUNCTIONS

• In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group

• This would then create as many results as there are unique groups defined as by the GROUP BY clause, which groups rows that have the same values into summary rows.

  SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
  FROM mytable
  WHERE constraint_expression
  GROUP BY column;

Note:

• if you want to add a WHERE clause after a GROUP BY clause which is already preceded by a WHERE clause, use the HAVING clause (treat exactly like WHERE):

  where condition1
  group by col1
  having group_condition

---

Order of execution of a Query

(SQL BEDMAS)

1. FROM , JOIN

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. DISTINCT

7. ORDER BY

8. LIMIT / OFFSET

---

Inserting Rows

SCHEMAS - describes the structure of each table, and the datatypes that each column of the table can contain. (think: mongoose schema)

> INSERT INTO

• Used to insert new rows in a table. Can be written 2 ways:

1. Specify both the column names and the values to be inserted

   /* syntax */
   INSERT INTO table_name (col1, col2, ...)
   VALUES (value1, value2, ...); 
   
   /* ex. */
   INSERT INTO Cars (brand, color, price)
   VALUES ('Porsche',  'Red', 90000);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

   /* syntax */
   INSERT INTO table_name
   VALUES (value1, value2, ...);
   
   /* ex. */
   INSERT INTO Cars
   VALUES
   	('Porsche',  'Red', 90000),
   	('Nissan',  'Navy', 75000);

Note: you don't need to insert the ID field as it's an auto-incrementing field and will be generated automatically when a new row is inserted into the table.

---

Updating Rows

> UPDATE (+ SET)

• used to modify the existing records in a table

• Similar to the INSERT statement, you have to specify exactly which table, columns, and rows to update.

• the data you are updating has to follow the schema of those columns.

/* syntax */
UPDATE mytable
SET column_1 = new_value, 
    column_2 = new_value
WHERE condition_1

/*
ex. Update the values of the ContactName and City fields for the row whose CustomerId=1
*/
UPDATE Customers
SET ContactName = 'Christian Bale', City= 'Gotham'
WHERE CustomerID = 1;

---

Deleting Rows

> DELETE

• used to delete existing records in a table.

• The WHERE clause specifies which record(s) should be deleted.

• If you omit the WHERE clause, all records in the table will be deleted.

/* syntax */
DELETE FROM mytable
WHERE condition_1

/* clear table */
DELETE FROM mytable

/* ex. Remove all rows whose Brand=Porsche */ 
 DELETE FROM Cars
 WHERE Brand='Porsche';

---

Creating Tables

> CREATE TABLE

• used to create a new table in a db

• add a IF NOT EXISTS clause to avoid SQL errors.

/* Basic Syntax */
CREATE TABLE IF NOT EXISTS table_name (
  column1 datatype,
  column2 datatype,
	....
); 

/* Extra Optional Syntax: */
CREATE TABLE IF NOT EXISTS table_name (
  column1 datatype TableConstraint DEFAULT default_val,
  column2 datatype TableConstraint DEFAULT default_val,
 	....
);

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

• Data Types Reference

• Constraint Reference

Create Table Using Another Table

• You can also create a copy of an existing table using CREATE TABLE

• The new table gets the same column definitions. All columns or specific columns can be selected.

• If you create a new table using an existing table, the new table will be filled with the existing values from the old table

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

---

Altering Tables

> ALTER TABLE

• used to add, delete, or modify columns in an existing table.

• also used to add and drop various constraints on an existing table.

1. Adding Columns - ADD

• required: column name and datatype

• optional: table constraints and default values.

/* Basic Syntax */
ALTER TABLE mytable
ADD new_column datatype; 

/* Optional Syntax */
ALTER TABLE mytable
ADD new_column dataType optionalTableConstraint
    DEFAULT default_value;

2. Removing Columns - DROP COLUMN

• Dropping columns is as easy as specifying the column to drop, however, some databases (ie. SQLite) don't support this feature.

• Instead, you may have to create a new table and migrate the data over

/* Syntax */
ALTER TABLE mytable
DROP COLUMN column_to_be_deleted;

3. Renaming Columns - RENAME TO

/* Syntax */
ALTER TABLE mytable
RENAME TO new_table_name;

4. Alter Column Datatype - (varies from rdbms to rdbms)

---

Dropping Tables

> DROP TABLE

• used to remove an entire table including all of its data

• differs from the DELETE statement in that DROP TABLE also removes the table schema from the db entirely

• use with IF EXISTS to avoid errors

  DROP TABLE IF EXISTS mytable;

---

> UNION

• used to combine the result-set of two or more SELECT statements

• Every SELECT within UNION must have the same # of columns of the same data types and in the same order.

  /* syntax */
  SELECT column_name FROM table1
  UNION
  SELECT column_name FROM table2; 

• The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

  /* syntax */
  SELECT column_name FROM table1
  UNION ALL
  SELECT column_name FROM table2;

> EXISTS

• used to test for the existence of any record in a subquery

  SELECT column_name
  FROM table_name
  WHERE EXISTS

> SELECT INTO

• copies all (or some) columns from one table into a new table:

  /* swap out '*' for 'col1, col2, ...' to copy only some columns into a new table */
  SELECT * INTO new_table
  FROM old_table
  WHERE condition_1;
  

> CASE ... END

• The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement).

• Once a condition is true, it will stop reading and return the result.

• If no conditions are true, it returns the value in the ELSE clause

/* Basic Syntax */
 CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

/* Ex. */
SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'Quantity is greater than 30'
    WHEN Quantity = 30 THEN 'Quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

> CREATE DATABASE

• used to create a new SQL db

  CREATE DATABASE db_name

> DROP DATABASE

• used to drop an existing SQL database (use w/ IF EXISTS for safety)

  DROP DATABASE IF EXISTS existing_db_name

Constraints

• SQL constraints are used to specify rules for the data in a table.

• They're used to limit the type of data that can go into a table. (for accuracy and reliability reasons).

• If there is any violation between the constraint and the data action, the action is aborted.

• Constraints can be applied to a column or an entire table.

• The following constraints are commonly used:

Constraint	Description
NOT NULL	ensures that column can't have NULL value
UNIQUE	ensures that all values in a column are different
PRIMARY KEY	combo of NOT NULL + UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY	prevents actions that would destroy links between tables
CHECK	ensures that values in a column satisfy a specific condition
DEFAULT	sets a default value for a column
CREATE INDEX	create and retrieve data from the database very quickly

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName text NOT NULL,
    FirstName text NOT NULL DEFAULT 'Joe',
    Age int
  	PRIMARY KEY (ID)
	  CHECK (Age>=18)
); 

---

Single line commentz: -- here

-- this and that and this

Multi line commentz: /* here */

/*
this
and that
and this
*/