SQL Notes: DROP, TRUNCATE, and Indexes

DROP vs. TRUNCATE

DROP: Permanently removes a table or database object (e.g., index, view) from the database, including its data and structure.
TRUNCATE: Removes all data rows from a table, but retains the table structure (columns, constraints, indexes). Faster than DROP as it doesn't log individual
row deletions.

Example: DROP

DROP TABLE Customers;

Example: TRUNCATE

TRUNCATE TABLE Customers;

Indexes in SQL
Indexes are specialized lookup tables that speed up data retrieval by allowing the database to quickly locate rows with specific column values.

Benefits of Indexes

Faster query execution

Quick data access
Improved sorting and grouping
Enforce unique values
Optimize joins

Drawbacks of Indexes

Consume extra storage space

Slow down INSERT/UPDATE operations
Require maintenance

Types of Indexes

Index Type Purpose Best Use Case

Primary Key Uniquely identifies rows User ID, Employee ID

Ensures column values are

Unique Index Email, SSN
unique

Clustered Index Organizes physical data rows Date, Transaction ID

Non-Clustered Index Separate structure for fast lookup First name, Product name

Composite Index Index on multiple columns First + Last name

Full-Text Index Efficient text field searches Product descriptions

Bitmap Index Efficient for low unique values Gender, Yes/No columns

Spatial Index For spatial/geolocation data Maps, Coordinates

Index Usage Guidelines

Use Primary Key Index for unique record identifiers.

Use Unique Index for fields like email to prevent duplicates.
Use Clustered Index on date/time for chronological queries.
Use Non-Clustered Index for frequently searched non-primary fields.

SQL Query Examples

Filtering with IN

SELECT * FROM Customers

WHERE City IN ('Paris', 'London');

Pattern Matching with LIKE

SELECT * FROM Customers

WHERE City LIKE 's%';

Range Filtering with BETWEEN

SELECT * FROM Products

WHERE Price BETWEEN 50 AND 60;

Sorting with ORDER BY

SELECT * FROM Customers

ORDER BY Country, CustomerName;

Sorting with Multiple Directions

SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

Complex Conditions with AND/OR

SELECT * FROM Customers

WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');

Precedence in Conditions

SELECT * FROM Customers

WHERE Country = 'Spain' AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';

Negating Conditions with NOT

SELECT * FROM Customers

WHERE NOT CustomerId < 50;

Inserting Data

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway');

Checking for Non-Null Values

SELECT CustomerName, ContactName, Address

FROM Customers
WHERE Address IS NOT NULL;

Updating Data
 UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

Deleting Data

DELETE FROM Customers

WHERE CustomerName = 'Alfreds Futterkiste';

Limiting Results

SELECT * FROM Customers

LIMIT 3;

SELECT TOP 3 * FROM Customers;

SELECT * FROM Customers

FETCH FIRST 3 ROWS ONLY;

Limiting by Percentage

SELECT TOP 50 PERCENT * FROM Customers;

SELECT * FROM Customers

FETCH FIRST 50 PERCENT ROWS ONLY;

Combining LIMIT with ORDER BY and WHERE

SELECT TOP 3 * FROM Customers

WHERE Country = 'Germany'
ORDER BY CustomerName DESC;

SELECT * FROM Customers

WHERE Country = 'Germany'
ORDER BY CustomerName DESC
LIMIT 3;

SELECT * FROM Customers

WHERE Country = 'Germany'
ORDER BY CustomerName DESC
FETCH FIRST 3 ROWS ONLY;
SQL Notes: Aggregate Functions and
Wildcards

Aggregate Functions
Aggregate functions are used with the GROUP BY clause in a SELECT statement to compute a single
value for each group of rows. These functions ignore NULL values, except for COUNT() .

Common Aggregate Functions

MIN(): Returns the smallest value in a column.

MAX(): Returns the largest value in a column.
COUNT(): Returns the number of rows in a set.
SUM(): Returns the total sum of a numerical column.
AVG(): Returns the average value of a numerical column.

Using Aliases with Aggregate Functions

Use the AS keyword to assign a custom name (alias) to the result of an aggregate function.

Example: Finding the Smallest Price

SELECT MIN(Price) AS SmallestPrice

FROM Products;

Example: Counting Distinct Records by Category

SELECT COUNT(DISTINCT Products) AS [Number of records], CategoryID

FROM Products
GROUP BY CategoryID;

Example: Summing Total Amount with JOIN

Page 1 of 3
 SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON [Link] = [Link];

Example: Average Price by Category

SELECT AVG(Price) AS AveragePrice, CategoryID

FROM Products
GROUP BY CategoryID;

Example: Filtering Above Average Price with Subquery

SELECT * FROM Products

WHERE Price > (SELECT AVG(Price) FROM Products);

Wildcards in SQL
Wildcards are used with the LIKE operator to match patterns in string data.

Wildcard Characters

Wildcard Description Example Usage

% Represents zero or more characters bl% finds bl, black, blue, blob

_ Represents a single character h_t finds hot, hat, hit

[] Represents any single character within brackets h[oa]t finds hot, hat

^ or ! Represents any character not in brackets h[!oa]t finds hit, not hot/hat

- Represents a range of characters c[a-b]t finds cat, cbt

# Represents any single numeric character 2#5 finds 205, 215, 225, etc.

Wildcard Query Examples

Example: Customers with 'or' in Name

Page 2 of 3
 SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

Example: Customers Starting with 'a' and at Least 3 Characters

SELECT * FROM Customers

WHERE CustomerName LIKE 'a__%';

Example: Customers with 'r' in Second Position

SELECT * FROM Customers

WHERE CustomerName LIKE '_r%';

Example: Customers Starting with 'b', 's', or 'p'

SELECT * FROM Customers

WHERE CustomerName LIKE '[bsp]%';

Example: Customers Starting with 'a' to 'f'

SELECT * FROM Customers

WHERE CustomerName LIKE '[a-f]%';

Page 3 of 3
SQL Notes: INSERT INTO SELECT, CASE,
Stored Procedures, and DDL

INSERT INTO SELECT

Copies data from one table and inserts it into another. Data types in source and target tables must
match. Existing records in the target table remain unaffected.

Example: Copy German Suppliers to Customers

INSERT INTO Customers (CustomerName, City, Country)

SELECT SupplierName, City, Country FROM Suppliers
WHERE Country = 'Germany';

CASE Expression
Evaluates conditions and returns a value when the first condition is met (like an if-then-else statement).
If no conditions are true, it returns the ELSE clause value.

Example: Categorize Order Quantities

SELECT OrderID, Quantity,

CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

Example: Sort by City or Country

Sort by City if not null, otherwise by Country .

Page 1 of 6
 SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);

Handling NULL Values

Functions to handle NULL values by returning an alternative value.

MySQL: IFNULL and COALESCE

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))

FROM Products;

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)

FROM Products;

SQL Server: ISNULL

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))

FROM Products;

Stored Procedures
Prepared SQL code saved for reuse. Can accept parameters to customize behavior.

Syntax

CREATE PROCEDURE procedure_name

AS
sql_statement
GO;

Page 2 of 6
 EXEC procedure_name;

Example: Parameterized Stored Procedure

Create and execute a procedure to select customers by city and postal code.

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarc

AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

SQL Comments
Single-line: -- (double dash)
Multi-line: /* */

SQL Operators

Arithmetic Operators

Operator Description

+ Add

- Subtract

* Multiply

/ Divide

% Modulo

Bitwise Operators

Operator Description

& Bitwise AND

` `

Page 3 of 6
 Operator Description

^ Bitwise exclusive OR

Comparison Operators

Operator Description

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

Compound Operators

Operator Description

+= Add equals

-= Subtract equals

*= Multiply equals

/= Divide equals

%= Modulo equals

&= Bitwise AND equals

^= Bitwise exclusive equals

` =`

Logical Operators

Operator Description

ALL TRUE if all subquery values meet the condition

AND TRUE if all conditions are TRUE

ANY TRUE if any subquery value meets the condition

Page 4 of 6
 Operator Description

BETWEEN TRUE if operand is within a range

EXISTS TRUE if subquery returns one or more records

IN TRUE if operand equals one in a list

LIKE TRUE if operand matches a pattern

NOT Negates a condition

OR TRUE if any condition is TRUE

SOME Same as ANY

Data Definition Language (DDL)

Create a Database

CREATE DATABASE databasename;

Backup a Database (SQL Server)

Create a full or differential backup (differential backs up only changes since the last full backup).

BACKUP DATABASE testDB

TO DISK = 'D:\backups\[Link]'
WITH DIFFERENTIAL;

Create a Table

CREATE TABLE Persons (

PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Alter a Table
Page 5 of 6
 Add a column:

ALTER TABLE table_name

ADD column_name datatype;

Drop a column:

ALTER TABLE table_name

DROP COLUMN column_name;

Rename a column:

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

Change a column's data type:

ALTER TABLE table_name

ALTER COLUMN column_name datatype;

Example: Change Data Type of a Column

Change DateOfBirth in Persons to year .

ALTER TABLE Persons

ALTER COLUMN DateOfBirth year;

Page 6 of 6
SQL Notes: Constraints, Indexes, and Auto-
Increment

UNIQUE Constraint
Ensures all values in a column or set of columns are unique. Unlike a PRIMARY KEY , a table can have
multiple UNIQUE constraints, but only one PRIMARY KEY .

Example: UNIQUE Constraint on a Single Column

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);

Example: Named UNIQUE Constraint on Multiple Columns

ALTER TABLE Persons

ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);

PRIMARY KEY Constraint

Uniquely identifies each record in a table. Must contain unique values and cannot contain NULL values.
A table can have only one PRIMARY KEY , which can consist of one or multiple columns.

Example: PRIMARY KEY on a Single Column

CREATE TABLE Persons (

ID int NOT NULL,

Page 1 of 5
 LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

Example: Named PRIMARY KEY on Multiple Columns

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)
);

FOREIGN KEY Constraint

Prevents actions that would break links between tables. A FOREIGN KEY in one table references the
PRIMARY KEY in another table. The table with the FOREIGN KEY is the child table, and the
referenced table is the parent table.

Example: FOREIGN KEY in Orders Table

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Example: Named FOREIGN KEY on Multiple Columns

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,

Page 2 of 5
 PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(P
);

CHECK Constraint
Limits the range of values that can be placed in a column or set of columns. Can be applied to a single
column or based on values in multiple columns.

Example: CHECK Constraint on a Single Column

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age >= 18)
);

Example: Named CHECK Constraint on Multiple Columns

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age >= 18 AND City = 'Sandnes')
);

Example: Add CHECK Constraint

ALTER TABLE Persons

ADD CHECK (Age >= 18);

Example: Named CHECK Constraint with ALTER

Page 3 of 5
 ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age >= 18 AND City = 'Sandnes');

Example: Drop CHECK Constraint

ALTER TABLE Persons

DROP CONSTRAINT CHK_PersonAge;

ALTER TABLE Persons

DROP CHECK CHK_PersonAge;

DEFAULT Constraint
Sets a default value for a column when no value is specified for new records.

Example: DEFAULT Value for City

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

Indexes
Indexes improve data retrieval speed by creating a lookup structure. They are invisible to users but
enhance query performance.

Create an Index

CREATE INDEX index_name

ON table_name (column1, column2, ...);

Page 4 of 5
Drop an Index

ALTER TABLE table_name

DROP INDEX index_name;

AUTO_INCREMENT
Automatically generates a unique number for a column (often the primary key) when a new record is
inserted. By default, starts at 1 and increments by 1.

Example: AUTO_INCREMENT for Primary Key

CREATE TABLE Persons (

Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);

Example: Set AUTO_INCREMENT Starting Value

ALTER TABLE Persons AUTO_INCREMENT = 100;

Example: Insert with AUTO_INCREMENT

The Personid will be automatically generated.

INSERT INTO Persons (FirstName, LastName)

VALUES ('Lars', 'Monsen');

Page 5 of 5
SQL Notes: Joins, GROUP BY, Subqueries, and
More

Subqueries with NOT IN

Find customers who have not placed any orders.

SELECT * FROM Customers

WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

BETWEEN for Range Filtering

Filter products by ProductName in alphabetical range and sort results.

SELECT * FROM Products

WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Chef Anton''s Cajun Sea
ORDER BY ProductName;

Filter orders by date range (two equivalent formats).

SELECT * FROM Orders

WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;

SELECT * FROM Orders

WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

String Concatenation
Combine address fields into a single column using CONCAT or + operator.

SELECT CustomerName, CONCAT(Address, ', ', PostalCode, ', ', City, ', ',
FROM Customers;

Page 1 of 6
 SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Co
FROM Customers;

SQL Joins
Joins combine rows from two or more tables based on a related column.

INNER JOIN: Returns records with matching values in both tables.

LEFT (OUTER) JOIN: Returns all records from the left table, and matched records from the right
table (NULL for non-matches).
RIGHT (OUTER) JOIN: Returns all records from the right table, and matched records from the left
table (NULL for non-matches).
FULL (OUTER) JOIN: Returns all records when there is a match in either table.

Example: INNER JOIN Across Multiple Tables

Join Orders , Customers , and Shippers to retrieve order details.

SELECT [Link], [Link], [Link]

FROM ((Orders
INNER JOIN Customers ON [Link] = [Link])
INNER JOIN Shippers ON [Link] = [Link]);

Example: LEFT JOIN

Return all customers, even those without orders.

SELECT [Link], [Link]

FROM Customers
LEFT JOIN Orders ON [Link] = [Link]
ORDER BY [Link];

Example: RIGHT JOIN

Return all employees, even those without orders.

SELECT [Link], [Link], [Link]

FROM Orders

Page 2 of 6
 RIGHT JOIN Employees ON [Link] = [Link]
ORDER BY [Link];

Example: FULL OUTER JOIN

Return all customers and orders, including non-matching rows.

SELECT [Link], [Link]

FROM Customers
FULL OUTER JOIN Orders ON [Link] = [Link]
ORDER BY [Link];

Example: Self-Join to Find Customers in Same City

Find pairs of customers in the same city using a self-join (excludes same customer with <> ).

SELECT [Link] AS CustomerName1, [Link] AS CustomerName2,

FROM Customers A, Customers B
WHERE [Link] <> [Link]
AND [Link] = [Link]
ORDER BY [Link];

UNION Operator
Combines the result sets of two or more SELECT statements. Each SELECT must have the same
number of columns, similar data types, and columns in the same order.

Example: UNION with Aliases

Combine Customers and Suppliers into a single result with a Type column.

SELECT 'Customer' AS Type, ContactName, City, Country

FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

Page 3 of 6
GROUP BY Clause
Groups rows with identical values into summary rows, often used with aggregate functions.

Example: Count Customers by Country

List the number of customers per country, sorted by count in descending order.

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Example: Count Orders by Shipper

List the number of orders per shipper.

SELECT [Link], COUNT([Link]) AS NumberOfOrders

FROM Orders
LEFT JOIN Shippers ON [Link] = [Link]
GROUP BY ShipperName;

HAVING Clause
Filters grouped results (used with aggregate functions since WHERE cannot be used).

Example: Countries with More Than 5 Customers

List countries with more than 5 customers, sorted by count.

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Example: Employees with Many Orders

Page 4 of 6
List employees "Davolio" or "Fuller" with more than 25 orders.

SELECT [Link], COUNT([Link]) AS NumberOfOrders

FROM Orders
INNER JOIN Employees ON [Link] = [Link]
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT([Link]) > 25;

EXISTS Operator
Tests for the existence of records in a subquery, returning TRUE if the subquery returns one or more
rows.

Example: Suppliers with Products Priced at 22

List suppliers who have products priced at 22.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE [Link]

ANY and ALL Operators

ANY: Returns TRUE if any subquery value meets the condition.
ALL: Returns TRUE if all subquery values meet the condition.

Example: ANY with Quantity

List products where any order detail has a quantity greater than 1000 (returns FALSE as no quantities
exceed 1000).

SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 1000);

Page 5 of 6
Example: ALL with Quantity

List products where all order details have a quantity of 10 (returns FALSE as quantities vary).

SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);

SELECT INTO
Copies data from one or more tables into a new table.

Example: Copy Customers and Orders to a New Table

Create a backup table with customer names and order IDs.

SELECT [Link], [Link]

INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON [Link] = [Link];

Page 6 of 6
SQL Notes: Date Types, Views, SQL Injection,
Data Types, and Functions

MySQL Date and Time Data Types

MySQL supports the following data types for storing date or date/time values:

Data Type Format Description

DATE YYYY-MM-DD Stores a date

DATETIME YYYY-MM-DD HH:MI:SS Stores a date and time

TIMESTAMP YYYY-MM-DD HH:MI:SS Stores a date and time, auto-updates

YEAR YYYY or YY Stores a year

Example: Query by Date

SELECT * FROM Orders WHERE OrderDate = '2008-11-11';

SQL Views
A view is a virtual table based on the result of a SELECT query, containing rows and columns like a real
table. Views can include SQL statements and functions to present data as if from a single table.

Create a View

CREATE VIEW [Brazil Customers] AS

SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';

Query a View

Page 1 of 12
 SELECT * FROM [Brazil Customers];

Update a View

Use CREATE OR REPLACE VIEW to modify an existing view.

CREATE OR REPLACE VIEW [Brazil Customers] AS

SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';

SQL Injection
SQL injection is a hacking technique where malicious code is inserted into SQL statements, typically via
web page input, potentially compromising the database.

MS SQL Server Data Types

String Data Types

Data Type Description Max Length Storage

char(n) Fixed-length non-Unicode 8,000 n bytes

varchar(n) Variable-length non-Unicode 8,000 n bytes + 2 bytes

varchar(max) Variable-length non-Unicode 2 GB

nchar(n) Fixed-length Unicode 4,000 2 * n bytes

nvarchar(n) Variable-length Unicode 4,000 2 * n bytes + 2 bytes

nvarchar(max) Variable-length Unicode 2 GB

binary(n) Fixed-length binary 8,000 n bytes

varbinary(n) Variable-length binary 8,000 actual length + 2 bytes

varbinary(max) Variable-length binary 2 GB

Numeric Data Types

Page 2 of 12
 Data Type Description Storage

bit Integer (0, 1, or NULL)

tinyint Whole numbers 0 to 255 1 byte

smallint Whole numbers -32,768 to 32,767 2 bytes

int Whole numbers -2,147,483,648 to 2,147,483,647 4 bytes

bigint Whole numbers ±9,223,372,036,854,775,807 8 bytes

decimal(p,s) Fixed precision and scale, p: 1-38, s: 0-p 5-17 bytes

numeric(p,s) Same as decimal 5-17 bytes

smallmoney Monetary -214,748.3648 to 214,748.3647 4 bytes

money Monetary ±922,337,203,685,477.5807 8 bytes

float(n) Floating precision, n=24 (4 bytes) or 53 (8 bytes) 4 or 8 bytes

real Floating precision -3.40E+38 to 3.40E+38 4 bytes

Date and Time Data Types

Data Type Description Storage

datetime Jan 1, 1753 to Dec 31, 9999, 3.33ms accuracy 8 bytes

datetime2 Jan 1, 0001 to Dec 31, 9999, 100ns accuracy 6-8 bytes

smalldatetime Jan 1, 1900 to Jun 6, 2079, 1min accuracy 4 bytes

date Jan 1, 0001 to Dec 31, 9999 3 bytes

time Time only, 100ns accuracy 3-5 bytes

datetimeoffset Same as datetime2 with time zone offset 8-10 bytes

timestamp Unique number updated on row changes

Other Data Types

Data Type Description

sql_variant Stores up to 8,000 bytes of various types

uniqueidentifier Stores a GUID

xml Stores XML data, max 2 GB

cursor Reference to a cursor for database operations

Page 3 of 12
 Data Type Description

table Stores a result-set for later processing

SQL Keywords

Keyword Description

ADD Adds a column to an existing table

ADD CONSTRAINT Adds a constraint to an existing table

ALL TRUE if all subquery values meet the condition

ALTER Modifies table columns or data types

ALTER COLUMN Changes a column's data type

ALTER TABLE Modifies a table (add/delete/modify columns)

AND TRUE if both conditions are true

ANY TRUE if any subquery value meets the condition

AS Renames a column or table with an alias

ASC Sorts results in ascending order

BACKUP DATABASE Creates a backup of a database

BETWEEN Selects values within a range

CASE Outputs based on conditions

CHECK Limits values in a column

COLUMN Modifies a column in a table

CONSTRAINT Adds or deletes a constraint

CREATE Creates a database, index, view, table, or procedure

CREATE DATABASE Creates a new SQL database

CREATE INDEX Creates an index (allows duplicates)

CREATE OR REPLACE
VIEW Updates a view

CREATE TABLE Creates a new table

CREATE PROCEDURE Creates a stored procedure

CREATE UNIQUE INDEX Creates a unique index (no duplicates)

Page 4 of 12
 Keyword Description

CREATE VIEW Creates a view from a SELECT statement

DATABASE Creates or deletes a database

DEFAULT Sets a default value for a column

DELETE Deletes rows from a table

DESC Sorts results in descending order

DISTINCT Selects only distinct values

DROP Deletes a column, constraint, database, index, table, or view

DROP COLUMN Deletes a column from a table

DROP CONSTRAINT Deletes a constraint

DROP DATABASE Deletes a database

DROP DEFAULT Deletes a DEFAULT constraint

DROP INDEX Deletes an index

DROP TABLE Deletes a table

DROP VIEW Deletes a view

EXEC Executes a stored procedure

EXISTS Tests for subquery records

FOREIGN KEY Links two tables via a key

FROM Specifies the table for data selection or deletion

FULL OUTER JOIN Returns all rows with matches in either table

GROUP BY Groups rows for aggregate functions

HAVING Filters grouped results with aggregate functions

IN Matches values in a list

INDEX Creates or deletes an index

INNER JOIN Returns rows with matching values in both tables

INSERT INTO Inserts new rows into a table

INSERT INTO SELECT Copies data from one table to another

IS NULL Tests for NULL values

IS NOT NULL Tests for non-NULL values

Page 5 of 12
 Keyword Description

JOIN Joins tables

LEFT JOIN Returns all rows from the left table, with matching rows from the
right

LIKE Searches for a pattern in a column

LIMIT Limits the number of records returned

NOT Negates a condition

NOT NULL Enforces non-NULL values in a column

OR TRUE if any condition is true

ORDER BY Sorts results in ascending or descending order

OUTER JOIN Returns all rows with matches in either table

PRIMARY KEY Uniquely identifies records in a table

PROCEDURE Defines a stored procedure

RIGHT JOIN Returns all rows from the right table, matching left table rows

ROWNUM Limits the number of records returned

SELECT Selects data from a database

SELECT DISTINCT Selects only distinct values

SELECT INTO Copies data into a new table

SELECT TOP Limits the number of records returned

SET Specifies columns and values for updates

TABLE Creates or modifies a table

TOP Limits the number of records returned

TRUNCATE TABLE Deletes data inside a table, but not the table itself

UNION Combines SELECT statements (distinct values)

UNION ALL Combines SELECT statements (allows duplicates)

UNIQUE Ensures unique values in a column

UPDATE Updates existing rows in a table

VALUES Specifies values for INSERT INTO

VIEW Creates, updates, or deletes a view

WHERE Filters records based on a condition

Page 6 of 12
MySQL String Functions

Function Description

ASCII Returns ASCII value of a character

CHAR_LENGTH Returns string length in characters

CHARACTER_LENGTH Same as CHAR_LENGTH

CONCAT Concatenates expressions

CONCAT_WS Concatenates expressions with a separator

FIELD Returns index of a value in a list

FIND_IN_SET Returns position of a string in a list

FORMAT Formats a number (e.g., "#,###,###.##")

INSERT Inserts a string at a specified position

INSTR Returns position of first occurrence of a substring

LCASE Converts to lower-case

LEFT Extracts characters from left

LENGTH Returns string length in bytes

LOCATE Returns position of first occurrence of a substring

LOWER Converts to lower-case

LPAD Left-pads a string to a specified length

LTRIM Removes leading spaces

MID Extracts a substring

POSITION Returns position of a substring

REPEAT Repeats a string

REPLACE Replaces all occurrences of a substring

REVERSE Reverses a string

RIGHT Extracts characters from right

RPAD Right-pads a string to a specified length

RTRIM Removes trailing spaces

SPACE Returns a string of spaces

Page 7 of 12
 Function Description

STRCMP Compares two strings

SUBSTR Extracts a substring

SUBSTRING Extracts a substring

SUBSTRING_INDEX Returns substring before a delimiter

TRIM Removes leading and trailing spaces

UCASE Converts to upper-case

UPPER Converts to upper-case

MySQL Numeric Functions

Function Description

ABS Absolute value

ACOS Arc cosine

ASIN Arc sine

ATAN Arc tangent

ATAN2 Arc tangent of two numbers

AVG Average value

CEIL Smallest integer >= number

CEILING Same as CEIL

COS Cosine

COT Cotangent

COUNT Number of records

DEGREES Converts radians to degrees

DIV Integer division

EXP e raised to a power

FLOOR Largest integer <= number

GREATEST Greatest value in a list

LEAST Smallest value in a list

LN Natural logarithm

Page 8 of 12
 Function Description

LOG Logarithm with specified base

LOG10 Logarithm base 10

LOG2 Logarithm base 2

MAX Maximum value

MIN Minimum value

MOD Remainder of division

PI Returns PI

POW Power

POWER Same as POW

RADIANS Converts degrees to radians

RAND Random number

ROUND Rounds to specified decimal places

SIGN Sign of a number

SIN Sine

SQRT Square root

SUM Sum of values

TAN Tangent

TRUNCATE Truncates to specified decimal places

MySQL Date Functions

Function Description

ADDDATE Adds a time/date interval to a date

ADDTIME Adds a time interval to a time/datetime

CURDATE Current date

CURRENT_DATE Current date

CURRENT_TIME Current time

CURRENT_TIMESTAMP Current date and time

CURTIME Current time

Page 9 of 12
 Function Description

DATE Extracts date part from datetime

DATEDIFF Number of days between two dates

DATE_ADD Adds a time/date interval to a date

DATE_FORMAT Formats a date

DATE_SUB Subtracts a time/date interval from a date

DAY Day of the month

DAYNAME Weekday name

DAYOFMONTH Day of the month

DAYOFWEEK Weekday index

DAYOFYEAR Day of the year

EXTRACT Extracts a part from a date

FROM_DAYS Date from numeric date value

HOUR Hour part of a date

LAST_DAY Last day of the month

LOCALTIME Current date and time

LOCALTIMESTAMP Current date and time

MAKEDATE Creates a date from year and days

MAKETIME Creates a time from hour, minute, second

MICROSECOND Microsecond part of a time/datetime

MINUTE Minute part of a time/datetime

MONTH Month part of a date

MONTHNAME Name of the month

NOW Current date and time

PERIOD_ADD Adds months to a period

PERIOD_DIFF Difference between two periods

QUARTER Quarter of the year

SECOND Seconds part of a time/datetime

SEC_TO_TIME Time from seconds

Page 10 of 12
 Function Description

STR_TO_DATE Date from string and format

SUBDATE Subtracts a time/date interval from a date

SUBTIME Subtracts a time interval from a datetime

SYSDATE Current date and time

TIME Extracts time part from datetime

TIME_FORMAT Formats a time

TIME_TO_SEC Converts time to seconds

TIMEDIFF Difference between two time/datetime values

TIMESTAMP Returns a datetime value

TO_DAYS Days since "0000-00-00"

WEEK Week number

WEEKDAY Weekday number

WEEKOFYEAR Week number

YEAR Year part of a date

YEARWEEK Year and week number

MySQL Advanced Functions

Function Description

BIN Binary representation of a number

BINARY Converts a value to a binary string

CASE Returns a value based on conditions

CAST Converts a value to a specified datatype

COALESCE Returns first non-NULL value in a list

CONNECTION_ID Unique connection ID for current connection

CONV Converts a number between numeric bases

CONVERT Converts a value to a specified datatype or character set

CURRENT_USER User name and host name for current MySQL account

DATABASE Name of the current database

Page 11 of 12
 Function Description

IF Returns a value based on a condition

IFNULL Returns a value if expression is NULL, else returns expression

ISNULL Returns 1 if expression is NULL, else 0

LAST_INSERT_ID AUTO_INCREMENT ID of last inserted row

NULLIF Returns NULL if two expressions are equal, else first expression

SESSION_USER Current MySQL user name and host name

SYSTEM_USER Current MySQL user name and host name

USER Current MySQL user name and host name

VERSION Current MySQL database version

Page 12 of 12