1. What is SQL?
SQL (Structured Query Language) is the standard language for accessing and manipulating relational databases. It
became an ANSI standard in 1986 and ISO standard in 1987.
SQL is used with Relational Database Management Systems (RDBMS) such as:
• MySQL
• Microsoft SQL Server
• Oracle Database
• IBM DB2
• Microsoft Access
In an RDBMS, data is stored in tables — collections of rows and columns.
2. Database Commands
CREATE DATABASE
Creates a new SQL database.
CREATE DATABASE databasename;
Example:
CREATE DATABASE school_db;
DROP DATABASE
Permanently deletes an existing database and all its data. Use with caution!
DROP DATABASE databasename;
BACKUP DATABASE (SQL Server)
Creates a full backup of an existing SQL Server database.
BACKUP DATABASE databasename
TO DISK = 'filepath';
-- Example:
BACKUP DATABASE testDB
TO DISK = 'D:\backups\[Link]';
-- MySQL equivalent:
mysqldump -u username -p database_name > backup_file.sql
3. Table Commands
CREATE TABLE
Defines a new table with its columns and data types.
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
-- Example:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
DROP TABLE
Permanently removes a table from the database.
DROP TABLE table_name;
ALTER TABLE
Modifies an existing table — add, remove, or rename columns.
-- Add a column:
ALTER TABLE table_name ADD column_name datatype;
-- Remove a column:
ALTER TABLE table_name DROP COLUMN column_name;
-- Rename a column (MySQL):
ALTER TABLE table_name CHANGE COLUMN old_name new_name DATATYPE;
4. Common MySQL Data Types
Each column must have a defined data type. Here are the most commonly used:
Data Type Description
INT Whole numbers (e.g., 1, 42, -7)
VARCHAR(n) Variable-length text up to n characters
CHAR(n) Fixed-length text of exactly n characters
TEXT Long text (no length limit specified)
DECIMAL(p, s) Exact decimal number (price, grades)
FLOAT / DOUBLE Approximate decimal numbers
DATE Date only — format: YYYY-MM-DD
DATETIME Date and time — format: YYYY-MM-DD HH:MM:SS
BOOLEAN TRUE or FALSE (stored as 1 or 0 in MySQL)
5. SQL Constraints
Constraints enforce rules on data in a table to maintain accuracy and integrity.
Constraint Description
NOT NULL Column cannot be empty — a value is required
UNIQUE All values in the column must be different
PRIMARY KEY Uniquely identifies each row (NOT NULL + UNIQUE)
FOREIGN KEY Links to a PRIMARY KEY in another table
CHECK Ensures values meet a specific condition
DEFAULT Sets a fallback value if none is provided
AUTO_INCREMENT Automatically generates a unique number for each new row
NOT NULL Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
PRIMARY KEY Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
FOREIGN KEY Example
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
AUTO_INCREMENT Example
CREATE TABLE Persons (
PersonID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (PersonID)
);
When AUTO_INCREMENT is used, MySQL automatically assigns the next available number when a row is inserted
— you don't need to provide the value.
6. Querying Data — SELECT
Basic SELECT
Retrieves data from a table. Results are stored in a temporary result set.
-- Select specific columns:
SELECT column1, column2 FROM table_name;
-- Select all columns:
SELECT * FROM table_name;
-- Example:
SELECT CustomerName, City FROM Customers;
SELECT DISTINCT
Returns only unique (non-duplicate) values in a column.
SELECT DISTINCT column_name FROM table_name;
-- Example (remove duplicate country entries):
SELECT DISTINCT Country FROM Customers;
WHERE Clause
Filters rows based on a condition. Only rows that match are returned.
SELECT column1, column2 FROM table_name WHERE condition;
-- Example:
SELECT * FROM Customers WHERE Country = 'Mexico';
AND, OR, NOT Operators
Combine multiple conditions in a WHERE clause.
-- AND: both conditions must be true
SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';
-- OR: at least one condition must be true
SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
-- NOT: reverses the condition
SELECT * FROM Customers
WHERE NOT Country = 'Germany';
ORDER BY
Sorts the results in ascending (default) or descending order.
SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;
-- Example:
SELECT * FROM Customers ORDER BY CustomerName ASC;
SELECT * FROM Customers ORDER BY CustomerName DESC;
LIKE Operator
Searches for a pattern in a column using wildcards.
• % — matches zero, one, or more characters
• _ — matches exactly one character
SELECT * FROM table_name WHERE columnN LIKE pattern;
-- Starts with 'a':
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
-- Contains 'or' anywhere:
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
-- Second character is 'r':
SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
BETWEEN Operator
Selects values within a range (inclusive — both endpoints are included).
SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;
-- Example:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
7. Modifying Data
INSERT INTO
Adds new records to a table. There are two approaches:
-- Method 1: Specify columns and values
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
-- Method 2: Provide values for all columns (order matters!)
INSERT INTO table_name VALUES (value1, value2, value3);
-- Example:
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Juan dela Cruz', 'CDO', 'Philippines');
UPDATE
Modifies existing records. Always use WHERE to avoid updating every row!
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
-- Example:
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;
DELETE
Removes records from a table. Without WHERE, all rows are deleted!
DELETE FROM table_name WHERE condition;
-- Example:
DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste';
-- ⚠️ Delete ALL rows (dangerous):
DELETE FROM table_name;
8. Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single result.
Function Description
COUNT(col) Counts the number of rows matching a condition
SUM(col) Adds up all values in a numeric column
AVG(col) Returns the average of a numeric column
MIN(col) Returns the smallest value in a column
MAX(col) Returns the largest value in a column
SELECT COUNT(CustomerID) FROM Customers;
SELECT AVG(Price) FROM Products;
SELECT SUM(Quantity) FROM OrderDetails;
SELECT MIN(Price) FROM Products WHERE CategoryID = 1;
SELECT MAX(Price) FROM Products;
9. SQL JOINs
JOINs combine rows from two or more tables based on a related column.
JOIN Type Description
INNER JOIN Returns rows where there is a match in BOTH tables
LEFT JOIN Returns ALL rows from the left table + matching from right
RIGHT JOIN Returns ALL rows from the right table + matching from left
FULL OUTER JOIN Returns all rows when there is a match in either table
INNER JOIN
SELECT [Link], [Link]
FROM Orders
INNER JOIN Customers ON [Link] = [Link];
LEFT JOIN
SELECT [Link], [Link]
FROM Customers
LEFT JOIN Orders ON [Link] = [Link]
ORDER BY [Link];
RIGHT JOIN
SELECT [Link], [Link]
FROM Orders
RIGHT JOIN Employees ON [Link] = [Link]
ORDER BY [Link];
10. UNION Operator
Combines the result sets of two or more SELECT statements into one. Rules:
• Each SELECT must have the same number of columns
• Columns must have compatible data types
• Columns must be in the same order
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
-- Example (unique cities from both tables):
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
-- UNION ALL (includes duplicates):
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;
11. SELECT INTO
Copies data from one table into a new table.
-- Copy all columns to a new table:
SELECT * INTO new_table FROM old_table WHERE condition;
-- Example — create a backup:
SELECT * INTO CustomersBackup FROM Customers;
-- Copy to a different database:
SELECT * INTO CustomersBackup IN '[Link]' FROM Customers;
12. SQL Injection Warning
SQL injection is one of the most common web security vulnerabilities. It occurs when malicious SQL code is inserted
into an input field and unknowingly executed by the database.
Example of a vulnerable query:
-- A user enters: 105 OR 1=1
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
-- This returns ALL users because 1=1 is always true!
How to prevent SQL injection:
• Use prepared statements / parameterized queries
• Never concatenate raw user input directly into SQL strings
• Validate and sanitize all user inputs on the server side
• Limit database user permissions (principle of least privilege)
Quick Reference — SQL Commands
Command / Keyword Purpose
CREATE DATABASE db; Create a new database
DROP DATABASE db; Delete a database
CREATE TABLE t (...); Create a new table
DROP TABLE t; Delete a table
ALTER TABLE t ADD col Add a column to a table
type;
ALTER TABLE t DROP Remove a column from a table
COLUMN col;
SELECT * FROM t; Retrieve all data from a table
SELECT col FROM t Retrieve filtered data
WHERE cond;
INSERT INTO t (cols) Insert a new record
VALUES (...);
UPDATE t SET col=val Update existing records
WHERE cond;
DELETE FROM t WHERE Delete records
cond;
SELECT DISTINCT col Retrieve unique values only
FROM t;
ORDER BY col ASC|DESC Sort results
LIKE '%pattern%' Search for a pattern
BETWEEN val1 AND val2 Filter values in a range
INNER JOIN t2 ON Join matching rows from two tables
[Link]=[Link]
LEFT JOIN t2 ON Join — keep all left table rows
[Link]=[Link]
UNION Combine results of two SELECT queries
COUNT() / SUM() / Aggregate functions
AVG()
MIN() / MAX() Smallest / largest value in a column
PRIMARY KEY Unique identifier for each row
FOREIGN KEY REFERENCES Link to another table's primary key
t(col)
NOT NULL Column value is required
AUTO_INCREMENT Auto-generate unique numbers