Master SQL Basics: A Concise Review Guide for Data Analysts

Database (Database): a repository that organizes, stores, and manages data according to a data structure.

RDBMS (Relational Database Management System): software that stores and manipulates data arranged in relational tables.

SQL (Structured Query Language): a language for querying, updating, and managing relational database systems, also used as the file extension for database scripts.

Table : a set of data arranged in rows and columns; columns represent attributes, rows represent records.

1. Database Query Language

1.1 How to select multiple columns

# Syntax
SELECT <Column List>
FROM <Table Name>
WHERE <Search Condition>
# Example
SELECT FirstName, LastName, OrderDate
FROM Orders
WHERE OrderDate > 10/10/2010

1.2 How to select a column and remove duplicates (key point)

# Syntax
SELECT DISTINCT <Column_name>
FROM <Table Name>
# Example
SELECT DISTINCT FirstName FROM Orders

1.3 How to select the first five rows starting from the first row

# Syntax
SELECT <col_name>
FROM table_name
limt 1 offset 5;
# Example
SELECT FirstName FROM Orders limt 1 offset 5;

1.4 How to sort data in a specified direction (ascending or descending)

# Syntax
SELECT co_name1, col_name2
FROM table_name
ORDER BY col_name1 DESC, col_name2 ASC
# Example
SELECT ,FirstName FROM Orders
ORDER BY OrderDate DESC

1.5 Common filtering selections (key point)

# Range check
SELECT col_name FROM table_name WHERE col_name BETWEEN 5 AND 10;
# Null check
SELECT col_name FROM table_name WHERE col_name IS null;
# IN operator
SELECT col_name FROM table_name WHERE col_name IN (....);
# NOT operator
SELECT col_name FROM table_name WHERE NOT (condition);
# AND and OR (AND has higher priority than OR)
SELECT col_name FROM table_name WHERE (condi_1 OR condi_2) AND condi_3;
# Wildcard filtering
SELECT col_name FROM table_name WHERE col_name like ..%..;
SELECT col_name FROM table_name WHERE col_name like .._..;
SELECT col_name FROM table_name WHERE col_name like []%;

1.6 How to group and filter data (key point)

# Syntax
SELECT <Column List>, <Aggregate Function>(<Column Name>)
FROM <Table Name>
WHERE <Search Condition>
GROUP BY <Column List>
# Example
SELECT LastName, SUM(OrderValue)
FROM Orders
WHERE OrderDate > 10/10/2010
GROUP BY LastName
# Note: GROUP BY can also be followed by conditions.
# WHERE works with HAVING; WHERE is evaluated before HAVING.
# WHERE filters rows, HAVING filters groups.

1.7 Order of grouping and sorting (key point)

/*
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
*/
SELECT col_name FROM table_name
WHERE <condition_1>
GROUP BY <col_name>
HAVING <condition_2>
ORDER BY <col_name>

1.8 How to use subqueries (key point)

# Syntax
SELECT col_name FROM table_name
WHERE col_name = (SELECT col_name FROM table_name WHERE ...);

1.9 How to perform join queries (very important!)

# Syntax
SELECT <Column List>
FROM <Table1> JOIN <Table2>
ON <Table1>.<Column1> = <Table2>.<Column1>
# Example
SELECT Orders.LastName, Countries.CountryName
FROM Orders INNER JOIN Countries ON Orders.CountryID = Countries.ID
# Also left join LEFT JOIN, right join RIGHT JOIN, full join FULL JOIN

1.10 How to use UNION queries

# Syntax
SELECT <Column List> FROM <Table1>
UNION
SELECT <Column List> FROM <Table2>
# Example
SELECT <Column List> FROM <Table1>
UNION
SELECT <Column List> FROM <Table2>

2. Database Manipulation Language

2.1 INSERT (Insert)

# Syntax
INSERT INTO <Table Name>
(Column List) VALUES (Values)
# Example
INSERT INTO Orders (FirstName, LastName, OrderDate) VALUES (John, Smith, 10/10/2010)

2.2 UPDATE (Update)

# Syntax
UPDATE <Table Name>
SET Column1 = Value1, Column2 = Value2, …
WHERE <Search Condition>
# Example
UPDATE Orders SET FirstName = John, LastName = Who WHERE LastName = Wo

2.3 DELETE (Delete)

# Syntax
DELETE FROM <Table Name>
WHERE <Search Condition>
# Example
DELETE FROM Orders WHERE OrderDate < 10/10/2010

3. Database Definition Language

3.1 CREATE (Create Table)

# Syntax
CREATE TABLE <Table Name>
( Column1 DataType,
  Column2 DataType,
  Column3 DataType )
# Example
CREATE TABLE Orders (
  FirstName CHAR(100),
  LastName CHAR(100),
  OrderDate DATE )

3.2 ALTER (Alter Table)

# Syntax
ALTER TABLE <Table Name> ADD col_name DataType;
ALTER TABLE <Table Name> DROP COLUMN col_name;
# Example
ALTER TABLE Vendors ADD vend_phone CHAR(20);
ALTER TABLE Vendors DROP COLUMN vend_phone;

3.3 DROP (Drop Table)

# Syntax
DROP TABLE table_name;
# Example
DROP TABLE CustCopy;

4. Database Control Language

4.1 GRANT

4.2 REVOKE

These sections are omitted for brevity; data analysts should focus on mastering query statements.