Essential SQL Commands: A Quick Reference Guide

The * wildcard in SQL selects all columns from a table without listing them individually.

Returns all columns from the users table (e.g., id, name, email, age, etc.).

SELECT * FROM users;

In SQL, the LIKE operator is commonly used to search for a specified pattern in a column. It's especially handy when you want to filter results based on partial matches.

This example demonstrates how various MySQL data types can be used when creating a user_profile table:

CREATE TABLE user_profile (
    user_id         INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(100) UNIQUE NOT NULL,
    account_status  ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    preferences     JSON,
    is_verified     BOOLEAN DEFAULT FALSE,
    signup_date     DATETIME DEFAULT CURRENT_TIMESTAMP
);
 

Here’s how to define primary and foreign keys when creating tables:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
 
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_total DECIMAL(8,2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Explanation:

• The users table uses user_id as its PRIMARY KEY. It guarantees that each user in the users table is uniquely identified. This ensures data integrity and prevents duplicate users.
• The orders table references user_id from the users table as a FOREIGN KEY, linking each order to a valid user. It creates a relationship between the orders and users tables.

What this gives us:

• Relational structure: We can easily connect a user to all their orders.
• Referential integrity: The database won’t allow an order to exist with a user_id that doesn’t exist in the users table.
• Cleaner data: No more orphaned orders or accidental inconsistencies.

💡 With these keys in place, SQL joins become safer and more meaningful — because the database guarantees the connection between rows is valid.

Example: Given two tables, one containing city names with their country codes and the other containing country names with their country codes, we can use SQL JOIN operations to select and combine data from both tables.

Cities table:

Countries table:

Examples Using Different Joins:

SELECT c.city_name, co.country_name
FROM cities AS c
INNER JOIN countries AS co
ON c.country_code = co.country_code;

This uses c as the alias for cities and co for countries.

We can replace INNER JOIN in the query above with LEFT JOIN, RIGHT JOIN, or use a FULL OUTER JOIN (with a workaround in MySQL) to see different results based on the type of join.

• INNER JOIN: Returns only the cities with a matching country_code in both tables. Result: Tokyo/Japan, and Sydney/Australia and Dublin/Ireland.

• LEFT JOIN: Returns all the rows from the left table (cities), and the matching rows from the right table (countries). If there is no match in the right table, the query will return NULL for the columns from the right table. Result: All cities, but New York, Paris, and Los Angeles with NULL in the country_namecolumn.

• RIGHT JOIN: Returns all rows from the right table (countries) and the matching rows from the left table (cities). Result: Dublin, Tokyo and Sydney with their country names and NULL as the city_name for Italy.

Inserting multiple rows at once:

INSERT INTO users (name, age)
VALUES
  ('John', 25),
  ('Jane', 30),
  ('Bob', 22),
  ('Alice', 28);

Here’s a simple example demonstrating how transactions work:

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
 
-- If everything is fine, then run:
-- COMMIT;
 
-- If something goes wrong, then run:
-- ROLLBACK;

Explanation: This example simulates a money transfer between two users. The START TRANSACTION begins a transaction block. If both UPDATE statements succeed, COMMIT saves the changes. If an error occurs (e.g., user_id 2 doesn’t exist), ROLLBACK undoes both updates to maintain data integrity.