The “BIG SIX” Elements of a SQL Select Statement
SELECT - Identifies the column(s) you want your query to select for your results
FROM - Identifies the table(s) your query will pull data from
WHERE - (Optional) Specifies record-filtering criteria for filtering your results
GROUP BY - (Optional) Specifies how to group the data in your results
HAVING - (Optional) Specifies group-filtering criteria for filtering your results
ORDER BY - (Optional) Specifies the order in which your query results are displayed
Here’s an easy, newbie-friendly way to remember SQL table-creation syntax — using a
simple formula and a mental checklist so you never blank out again.
⭐ The Super Simple Formula
Just remember:
CREATE → TABLE → Name → (Columns);
And inside the parentheses, each column follows:
ColumnName DataType Constraints
That’s really it.
🧠 Memory Hook: “NTC”
Think Name → Type → Constraint
Every column line follows this order:
None
column_name data_type constraint
Example:
None
id INT PRIMARY KEY
name VARCHAR(50) NOT NULL
🧩 The Full Pattern (Template You Can
Memorize)
None
CREATE TABLE table_name (
column1 data_type constraint,
column2 data_type constraint,
column3 data_type constraint
);
If you can remember this single template, you can create ANY table.
📝 Ultra-Simple Example
Try memorizing this structure, it appears in almost all SQL lessons:
None
CREATE TABLE students (
student_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
age INT,
email VARCHAR(100) UNIQUE
);
Keep this in your mind as your “default example,” and just edit it (table name + columns).
🎯 Shortcut Tricks to Never Forget
1. CREATE TABLE (...)
Like you’re making a container with things inside.
2. Columns go one per line
Column → type → rule.
3. Always end with );
A lot of beginners forget this.
4. Commas after each column EXCEPT the last
Like writing a list in English.
👍 If You Want a 10-second Recall Phrase:
“Create table, name it, put columns inside, each as Name-Type-Constraint.”
If you want, I can make you a mnemonic, flashcard-style sheet, or a practice exercise to
cement it.
SQL DATA TYPES
SQL data types categorize the kind of values that can be stored in a database column.
While specific implementations may vary slightly between different database systems
(like MySQL, PostgreSQL, SQL Server), the core categories and common types are
generally consistent.
Common SQL Data Type Categories:
● Numeric Data Types: Used for storing various forms of numbers.
● Integers:
● INT, INTEGER: Standard integer type.
● SMALLINT, TINYINT, BIGINT: Smaller or larger integer ranges.
● Fixed-point Numbers:
● DECIMAL(p,s), NUMERIC(p,s): Precise decimal numbers with a specified
precision (p) and scale (s).
● Floating-point Numbers:
● FLOAT, REAL, DOUBLE PRECISION: Approximate floating-point numbers.
● Date and Time Data Types: Used for storing date and time information.
● DATE: Stores a date (year, month, day).
● TIME: Stores a time of day (hour, minute, second).
● DATETIME, TIMESTAMP: Stores both date and time.
● YEAR: Stores a year.
● String (Character and Text) Data Types: Used for storing text and characters.
● CHAR(n): Fixed-length string of n characters.
● VARCHAR(n): Variable-length string with a maximum length of n
characters.
● TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT: For storing large blocks of
text.
● NCHAR, NVARCHAR: For storing Unicode character strings.
● Binary Data Types: Used for storing binary data, such as images or files.
● BINARY, VARBINARY: Fixed or variable-length binary strings.
● BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB: Binary Large Objects for
storing large amounts of binary data.
● Other Data Types:
● BOOLEAN, BIT: For storing true/false or bit values.
● XML: For storing XML data.
● JSON: For storing JSON data (in some databases).
● ENUM: For a predefined list of string values.
● GEOMETRY, GEOGRAPHY: For spatial data (in some databases).
Here are the primary types of SQL constraints:
● NOT NULL: This constraint ensures that a column cannot store NULL values.
Every row must have a value for that specific column.
● UNIQUE: This constraint guarantees that all values in a column (or a set of
columns) are distinct. No two rows can have the same value in a column with a
UNIQUE constraint.
● PRIMARY KEY: This constraint uniquely identifies each row in a table. It
combines the properties of NOT NULL and UNIQUE, meaning it cannot contain
NULL values and all values must be unique. A table can only have one PRIMARY
KEY.
● FOREIGN KEY: This constraint establishes a link between two tables. It refers to
the PRIMARY KEY in another table, ensuring referential integrity by preventing
actions that would destroy the links between related tables.
● CHECK: This constraint enforces a specific condition that all values in a column
must satisfy. For example, a CHECK constraint can ensure that age values are
always greater than 18.
● DEFAULT: This constraint provides a default value for a column when no value is
explicitly specified during an INSERT operation.
● INDEX: While not strictly a constraint in the same sense as the others, INDEXes
are often used to improve the performance of data retrieval and can implicitly
enforce uniqueness or order, depending on their configuration (e.g., unique
indexes).
ADDING/REMOVING FROM A TABLE
Absolutely — here are super clear, beginner-friendly notes on adding to and removing from
tables in SQL. These match the same style as the CREATE TABLE notes so everything stays
easy to memorize.
📌 ADDING TO A TABLE
This can mean adding columns, adding rows, or adding data to existing rows.
⭐ 1. Add a New Column (ALTER TABLE)
Formula:
None
ALTER TABLE table_name
ADD column_name data_type constraint;
Memory Trick:
ALT + ADD → adds a column.
Example:
None
ALTER TABLE students
ADD birthdate DATE;
⭐ 2. Add New Rows (Records) — INSERT
Formula:
None
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Memory Trick:
INSERT INTO → Columns → VALUES → Data.
Like matching items into slots.
Example:
None
INSERT INTO students (student_id, full_name, age)
VALUES (1, 'Rafael Vicuna', 21);
⭐ 3. Update Existing Rows — UPDATE
Formula:
None
UPDATE table_name
SET column = value
WHERE condition;
Memory Trick:
UPDATE → SET → WHERE.
(Always have a WHERE so you don’t update everything!)
Example:
None
UPDATE students
SET age = 22
WHERE student_id = 1;
📌 REMOVING FROM A TABLE
This can mean removing columns, removing rows, or removing the whole table.
⭐ 4. Remove a Column — ALTER TABLE
DROP
Formula:
None
ALTER TABLE table_name
DROP COLUMN column_name;
Memory Trick:
ALT + DROP → drops a column.
Example:
None
ALTER TABLE students
DROP COLUMN birthdate;
⭐ 5. Delete Rows (Records) — DELETE
Formula:
None
DELETE FROM table_name
WHERE condition;
Memory Trick:
DELETE FROM → WHERE.
(Missing WHERE deletes everything — be careful!)
Example:
None
DELETE FROM students
WHERE student_id = 1;
⭐ 6. Delete All Rows — TRUNCATE
Formula:
None
TRUNCATE TABLE table_name;
Memory Trick:
Think of it as “wipe the table clean” but keep its structure.
⭐ 7. Delete Entire Table — DROP
Formula:
None
DROP TABLE table_name;
Memory Trick:
DROP = destroy the table forever.
🧠 Quick Summary Table (Very Handy for
Memorization)
Action Command Memory Hook
Add column ALTER TABLE ... ADD ALT + ADD
Add row INSERT INTO ... VALUES Insert items into slots
Update row UPDATE ... SET ... WHERE SET + WHERE
Remove column ALTER TABLE ... DROP ALT + DROP
COLUMN
Remove row DELETE FROM ... WHERE Always WHERE
Remove all rows TRUNCATE TABLE Wipe clean
Remove table DROP TABLE Destroy
If you want, I can also make flashcards, a one-page cheat sheet, or a memory drill exercise
to help you memorize these permanently.
MY OWN PERSONAL SOLUTION (to one of the SQL 2 exercises)