SQL Basics Notes

I. Introduction to SQL

1.1 What is SQL

SQL (Structured Query Language): the standard programming language for managing relational databases.

RDBMS: Relational Database Management System
Common databases (by type): MySQL, PostgreSQL, SQLite, Oracle, SQL Server
1. SQLite: lightweight, embedded — great for mobile apps
2. MySQL: open-source, widely used — great for web apps
3. PostgreSQL: open-source, feature-rich — great for complex apps
4. Oracle: enterprise-grade, fully featured — great for large-scale apps
5. SQL Server: developed by Microsoft — great for Windows environments

1.2 Basic SQL Categories

Four schools of thought — these are the disciplines you use to communicate with a database. Master them and you’re a data wrangler; give up and you’re just a data janitor. 🐶

Category	Purpose	Keywords
DDL	Define database structure	CREATE, ALTER, DROP
DML	Manipulate data	INSERT, UPDATE, DELETE
DQL	Query data	SELECT
DCL	Control permissions	GRANT, REVOKE

II. Basic Syntax

2.1 Basic Rules

SQL statements end with a semicolon ; (some databases allow omitting it)
Keywords are case-insensitive, but the convention is to write keywords in uppercase and table/column names in lowercase
Strings and dates are wrapped in single quotes ' '
Comments: -- single-line comment, /* multi-line comment */

2.2 Writing Style

-- Recommended writing style
SELECT
    id,
    name,
    email
FROM
    users				/* Whether to use double quotes depends on the database type */
WHERE
    status = 'active'
ORDER BY
    create_time DESC;

2.3 Common Operators

Arithmetic Operators

Operator	Description
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`%` or `MOD()`	Modulo

Comparison Operators

Operator	Description
`=`	Equal to
`<>` or `!=`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

Logical Operators

Operator	Description
`AND`	Logical AND (higher precedence than OR — use parentheses like in C++)
`OR`	Logical OR
`NOT`	Logical NOT

2.4 Common Commands (MySQL)

-- Show all databases
SHOW DATABASES;

-- Show all tables in the current database
SHOW TABLES;

-- View table structure
DESC table_name;
-- or
DESCRIBE table_name;

-- View the CREATE TABLE statement
SHOW CREATE TABLE table_name;

-- Show full column info for a table
SHOW FULL COLUMNS FROM table_name;

2.5 ⚠️ Things to Watch Out For

Query syntax keywords have a specific ordering relationship.

III. DDL — Data Definition

2.1 Creating a Database

1 2	CREATE DATABASE database_name; USE database_name;

2.2 Creating a Table

CREATE TABLE table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
    ...
);

Common data types:

Integer: INT, BIGINT
Decimal: DECIMAL(m,n), FLOAT, DOUBLE
String: VARCHAR(n), CHAR(n), TEXT
Date/Time: DATE, DATETIME, TIMESTAMP

2.3 Constraints

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 18,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Common constraints:

PRIMARY KEY: primary key, uniquely identifies a row
NOT NULL: value cannot be null
UNIQUE: value must be unique
DEFAULT: default value
FOREIGN KEY: foreign key constraint
AUTO_INCREMENT: auto-increment (MySQL)

2.4 Altering Table Structure

-- Add a column
ALTER TABLE table_name ADD column_name data_type;

-- Modify a column
ALTER TABLE table_name MODIFY column_name new_data_type;

-- Drop a column
ALTER TABLE table_name DROP COLUMN column_name;

-- Rename a table
ALTER TABLE table_name RENAME TO new_table_name;

2.5 Dropping a Table

1 2	DROP TABLE table_name; -- Drop the table entirely TRUNCATE TABLE table_name; -- Clear all data (keep the structure)

III. DML — Data Manipulation

3.1 Inserting Data

-- Insert a single row
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- Insert multiple rows
INSERT INTO table_name (col1, col2) VALUES
(val1, val2),
(val3, val4),
(val5, val6);

-- Import from another table
INSERT INTO table_name SELECT * FROM other_table WHERE condition;

3.2 Updating Data

1
2
3

UPDATE table_name
SET col1 = new_val1, col2 = new_val2
WHERE condition;

3.3 Deleting Data

1	DELETE FROM table_name WHERE condition;

IV. DQL — Data Query (Core)

4.1 Basic Queries

-- Query all columns
SELECT * FROM table_name;

-- Query specific columns
SELECT col1, col2 FROM table_name;

-- Deduplicate
SELECT DISTINCT col FROM table_name;

-- Alias
SELECT col AS alias FROM table_name;

4.2 Conditional Queries — WHERE

SELECT * FROM table_name WHERE condition;

-- Comparison operators
WHERE age > 18
WHERE name = 'Alice'
WHERE age >= 18 AND age <= 30

-- Range
WHERE age BETWEEN 18 AND 30

-- Enumeration
WHERE status IN ('active', 'pending')

-- Pattern matching
WHERE name LIKE 'A%'       -- starts with A
WHERE name LIKE '%son%'    -- contains "son"
WHERE name LIKE 'A_'       -- starts with A, exactly 2 characters

-- Null checks
WHERE email IS NULL
WHERE email IS NOT NULL

4.3 Sorting — ORDER BY

SELECT * FROM table_name ORDER BY col1 ASC, col2 DESC;

-- ASC: ascending (default)
-- DESC: descending

4.4 Limiting Results — LIMIT

-- MySQL
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 5, 10;  -- Start from row 5, fetch 10 rows

-- SQL Server
SELECT TOP 10 * FROM table_name;

-- Oracle
SELECT * FROM table_name WHERE ROWNUM <= 10;

4.5 Aggregate Functions

SELECT
    COUNT(*)          AS total_rows,
    COUNT(col)        AS non_null_count,
    SUM(col)          AS total,
    AVG(col)          AS average,
    MAX(col)          AS maximum,
    MIN(col)          AS minimum
FROM table_name;

4.6 Grouping — GROUP BY

SELECT col, aggregate_function
FROM table_name
GROUP BY col
HAVING aggregate_condition;

Note: WHERE filters before grouping; HAVING filters after grouping.

-- Example: average salary per department
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 5000;

4.7 Multi-Table Queries

Joins (JOIN)

-- INNER JOIN: only keep matching rows
SELECT *
FROM table1
INNER JOIN table2 ON table1.col = table2.col;

-- LEFT JOIN: keep all rows from the left table; NULLs where there's no match on the right
SELECT *
FROM table1
LEFT JOIN table2 ON table1.col = table2.col;

-- RIGHT JOIN: keep all rows from the right table
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.col = table2.col;

-- FULL JOIN (MySQL doesn't support this natively — simulate with UNION)
SELECT * FROM table1 LEFT JOIN table2 ON ...
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON ...;

Subqueries

-- Subquery in WHERE
SELECT * FROM table_name WHERE col = (SELECT col FROM ...);

-- IN subquery
SELECT * FROM table_name WHERE col IN (SELECT col FROM ...);

-- EXISTS subquery
SELECT * FROM table_name WHERE EXISTS (SELECT 1 FROM ... WHERE condition);

4.8 UNION — Combined Queries

SELECT col FROM table1
UNION                 -- merge and deduplicate
SELECT col FROM table2;

SELECT col FROM table1
UNION ALL            -- merge and keep duplicates
SELECT col FROM table2;

V. Common Functions

5.1 String Functions

Function	Description
`CONCAT(s1, s2)`	Concatenate strings
`LENGTH(s)`	Get string length
`UPPER(s)` / `LOWER(s)`	Convert case
`TRIM(s)`	Strip leading/trailing spaces
`SUBSTRING(s, start, len)`	Extract a substring
`REPLACE(s, old, new)`	Replace substring
`IFNULL(s, default)`	Replace NULL with a default value

5.2 Numeric Functions

Function	Description
`ROUND(n, d)`	Round to d decimal places
`CEIL(n)` / `FLOOR(n)`	Ceiling / floor
`ABS(n)`	Absolute value
`MOD(n, m)`	Modulo
`RAND()`	Random number

5.3 Date Functions

Function	Description
`NOW()` / `SYSDATE()`	Current date and time
`CURDATE()`	Current date
`YEAR(d)` / `MONTH(d)` / `DAY(d)`	Extract year / month / day
`DATE_FORMAT(d, format)`	Format a date
`DATE_ADD(d, INTERVAL n unit)`	Add/subtract from a date
`DATEDIFF(d1, d2)`	Difference between two dates

1	SELECT DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') FROM table_name;

5.4 Conditional Logic

-- IF
SELECT IF(age >= 18, 'adult', 'minor') FROM table_name;

-- CASE WHEN
SELECT
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END AS grade
FROM table_name;

VI. Indexes

6.1 Index Types

Type	Description
Regular index	Allows duplicate values
Unique index	Values must be unique
Primary key index	Auto-created with the primary key; unique and not null
Full-text index	Full-text search (MyISAM)
Composite index	Spans multiple columns

6.2 Creating Indexes

-- Create an index
CREATE INDEX index_name ON table_name(col);

-- Create a unique index
CREATE UNIQUE INDEX index_name ON table_name(col);

-- Create a composite index
CREATE INDEX index_name ON table_name(col1, col2);

-- View indexes
SHOW INDEX FROM table_name;

-- Drop an index
DROP INDEX index_name ON table_name;

6.3 Indexing Principles

Good candidates: large datasets, frequently queried columns, columns often used in WHERE
Avoid: small datasets, frequently updated columns, low-cardinality columns
Leftmost prefix rule: composite indexes are used starting from the leftmost column

VII. Transactions

7.1 Transaction Properties (ACID)

Atomicity: either everything succeeds or everything fails
Consistency: data is in a valid state before and after the transaction
Isolation: concurrent transactions don’t interfere with each other
Durability: once committed, data is permanently saved

7.2 Transaction Control

-- Start a transaction
START TRANSACTION;
-- or
BEGIN;

-- Commit
COMMIT;

-- Rollback
ROLLBACK;

-- Set a savepoint
SAVEPOINT savepoint_name;

-- Rollback to a savepoint
ROLLBACK TO savepoint_name;

7.3 Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Not possible	Possible	Possible
REPEATABLE READ (default)	Not possible	Not possible	Possible
SERIALIZABLE	Not possible	Not possible	Not possible

1	SET SESSION TRANSACTION ISOLATION LEVEL level;

VIII. Views

-- Create a view
CREATE VIEW view_name AS
SELECT col1, col2
FROM table_name
WHERE condition;

-- Use a view
SELECT * FROM view_name;

-- Drop a view
DROP VIEW view_name;

IX. References

SQL Database