What is SQL

SQL(Structured Query Language) is a langauge used to interact (managing and manipulating data) with relational databases. SQL stores data in tables. Tables are like excel sheets, where Columns define the type of data (name, age, email etc) and Rows are the actual entires.

Actually we write statements or instruction in sql. A SQL statement is a command written in SQL that performs an action on the database. exmaple - SELECT * FROM users;.

Statements are broadly classified into two main types:

  1. Data Definition Language (DDL) - These statements define or modify the structure of the database and tables. Commands like CREATE, ALTER, DROP, TRUNCATE are used

  2. Data Manipulation Language (DML) - These statements deal with the data itself — inserting, reading, updating, or deleting. Commands like SELECT, INSERT, UPDATE, DELETE are used.


Essential SQL Commands

While learning SQL, let's imagine we are managing a bizarre intergalactic pet shop that sells 👽 alien creatures to different planets.

Commands related to databases

1. List all databases

Syntax:

SHOW DATABASES;

2. Create a Database

Syntax:

CREATE DATABASE <name_of_database>;

Example

CREATE DATABASE intergalactic_pet_shop;

To avoid an error if the database already exists, we use

CREATE DATABASE IF NOT EXISTS intergalactic_pet_shop;

3. Set the specified database as the default or current database

Syntax:

Use <datanase_name>;

4. Delete database

Syntax:

DROP DATABASE <database_name>;


Commands related to tables

1. Create table in the database

Syntax:

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

Example: Create creatures Table

CREATE TABLE creatures (
  id INT AUTO_INCREMENT PRIMARY KEY, -- Unique Id
  name VARCHAR(100) NOT NULL UNIQUE, -- Name of the creature
  species VARCHAR(100) NOT NULL, -- creature species
  origin_planet VARCHAR(100), -- Where the creature came from
  is_dangerous BOOLEAN DEFAULT FALSE, 
  about TEXT,
  temperament VARCHAR(50), --e.g., 'playful', 'aggressive', 'lazy'
  size ENUM('tiny', 'small', 'medium', 'large', 'gigantic'),
  price DECIMAL(10,2),
  status ENUM('available', 'sold') DEFAULT 'available',
  image_url TEXT,
  lifespan_years INT,
  habitat_needs Text, -- If aliens need special conditions (water, ice, lava, etc.)
  birthday DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Example: Create customers Table

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150) UNIQUE,
  credits DECIMAL(12,2) DEFAULT 10000.00,
  place_of_residence VARCHAR(150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Example: Create purchases Table

CREATE TABLE purchases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  creature_id INT,
  purchase_price DECIMAL(10,2),
  purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  delivery_planet VARCHAR(100),
  review TEXT,
  happiness_rating INT CHECK (happiness_rating BETWEEN 1 AND 5),
  delivery_via ENUM('teleportation', 'space_shuttle', 'wormhole_express', 'manual_drone'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- Foreign keys
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (creature_id) REFERENCES creatures(id) 
);

2. Show tables in current database

Syntax:

SHOW TABLES;
tables

3. Detailed Information of table

Syntax:

SHOW <table_name>;

Example:

SHOW purchases;

4. Insert data into the table

Syntax:

INSERT INTO <table_name> (column1, column2,....) VALUES (val1, val2,.....)

-- string should in single quote

Example : creatures table

INSERT INTO creatures 
(id, name, species, origin_planet, is_dangerous, about, temperament, size, price, status, image_url, lifespan_years, habitat_needs, birthday, created_at, updated_at)
VALUES
(1, 'Zoggo', 'Blorb', 'Neptune-5', TRUE, 'Loves plasma storms.', 'aggressive', 'large', 1999.99, 'available',
 '/zoggo.jpg', 120, 'Needs high-voltage plasma field and metal walls', '2001-07-20', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Flibbo', 'Snarfle', 'Europa', FALSE, 'Floats peacefully in zero gravity.', 'playful', 'small', 899.99, 'available',
 '/flibbo.jpg', 40, 'Zero gravity tank with chill vapor mist', '2018-03-14', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Chomp', 'Fangzor', 'Venus', TRUE, 'Eats anything. Literally.', 'aggressive', 'gigantic', 3099.49, 'available',
 '/chomp.jpg', 85, 'Toxic swamp chamber, no glass allowed', '1997-11-05', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 'Ploof', 'Mellowmite', 'Titan', FALSE, 'Sleeps 23 hours a day.', 'lazy', 'medium', 499.99, 'available',
 '/ploof.jpg', 15, 'Soft moss bed, low light, warm vapor', '2020-01-01', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Example : customers table

INSERT INTO customers (
  id, name, email, credits, place_of_residence, created_at, updated_at
)
VALUES
(1, 'Zarnok Vee', 'zarnok@jupiterhub.space', 18000.50, 'Dome 9, Floating City, Jupiter', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Lunessa Ray', 'lunessa@lunarzone.gal', 9500.00, 'Sector E5, Moon Colony Alpha', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Example : purchases table

INSERT INTO purchases (
  id, customer_id, creature_id, purchase_price, purchase_date,
  delivery_planet, review, happiness_rating, delivery_via, created_at, updated_at
)
VALUES
(1, 1, 2, 899.99, '2025-04-10 14:45:00', 'Jupiter', 'Flibbo floats into my heart 💙', 5, 'teleportation', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 2, 4, 499.99, '2025-04-11 09:20:00', 'Moon', 'Ploof is like a fuzzy space pillow.', 4, 'manual_drone', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

5. Read data from table

Syntax:

SELECT column1, column2, ... FROM <table_name> 
-- or
SELECT * FROM <table_name> -- * means select all columns.

Example:

SELECT name, price FROM creatures;
select
  • Filtering rows based on conditions (WHERE clause) Example : Show me the names of all dangerous creatures in the shop. 
    SELECT name FROM creatures WHERE is_dangerous = TRUE;

    Example : Give me creatures that are dangerous but small in size.

    SELECT name FROM creatures WHERE is_dangerous = TRUE AND size = "small";

    Example : Give me creatures that are from Venus or Titan.

    SELECT name, origin_planet 
FROM creatures 
WHERE origin_planet = "Venus" OR origin_planet = "Titan";

    Example : Give me dangerous creatures that are either playful or aggressive.

    SELECT name, temperament 
FROM creatures 
WHERE is_dangerous = TRUE AND (temperament = "playful" OR temperament = "aggressive");

    Example : Give me creatures that life span more than 100 years.

    SELECT name, species, lifespan_years
FROM creatures
WHERE lifespan_years > 100;

  • Sort Results

    Example : Show creature names and prices, sorted from most expensive to least.

    SELECT name, price FROM creatures ORDER BY price DESC;

    Example : Show creature names and prices, sorted from most least to expensive.

    SELECT name, price FROM creatures ORDER BY price ASC;

    Example : Sort dangerous creatures by size descending (gigantic to tiny), and if sizes match, then by price ascending.

    SELECT name, price 
FROM creatures 
WHERE is_dangerous = TRUE 
ORDER BY size DESC, price ASC;

  • Limit numbers of rows

    Example : Show only the first 2 customers

    SELECT * FROM customers LIMIT 2;

    Example : Skip first 2 most expensive creatures and return the next 2 only.

    SELECT name, price FROM creatures ORDER BY price DESC LIMIT 2 OFFSET 2;

  • Remove duplicates data (if any)

    Example : Show a unique list of origin planets (no repeats).

    SELECT DISTINCT origin_planet FROM creatures;

    Example : Show unique combinations of origin_planet and habitat_needs for dangerous creatures.

    SELECT DISTINCT origin_planet, habitat_needs
FROM creatures
WHERE is_dangerous = TRUE;

  • Search conditions (IN / BETWEEN / LIKE )

    Example : Search all creatures whose size is either 'small' or 'tiny'.

    SELECT name FROM creatures WHERE size in ('small', 'tiny');

    Use IN when you want to match any value from a list.


    Example : Get creatures whose price falls between 500 and 2000, including both ends (i.e., 500 and 2000 are included) (inclusive).

    SELECT name, price FROM creatures WHERE price BETWEEN 500 AND 2000;

    Use BETWEEN to match a value within a range (inclusive).


    Example : Find creature names that start with the letter 'Z' (like 'Zoggo').

    SELECT name FROM creatures WHERE name LIKE 'Z%';

-- 'z' - start with z
-- `%` - any char after that

    Use LIKE for pattern matching.


    Example : Get creatures from Europa and Titan planets, price between 500–2000, and names that contain "o".

    SELECT name, price, origin_planet 
FROM creatures 
WHERE origin_planet 
  IN ('Europa', 'Titan') AND 
  price BETWEEN 500 AND 2000 AND
  name like '%o%';

  • Grouping

    Example : Group creatures by habitat, and show only habitats that have more than 1 creature

    SELECT habitat_needs, count(*) AS total_creatures
FROM creatures GROUP BY habitat_needs HAVING COUNT(*) > 1;
-- AS rename the column field
-- GROUP BY - Make groups
-- HAVING -  Filter after grouping is done. It like WHERE but fof groups
-- Often we use aggregate function ike COUNT(), SUM(), AVG(), MAX(), MIN() for filtering

    Example : Among non-dangerous creatures - Group them by habitat and show only habitats that have more than 1 creature and thier average price is less than 1500

    SELECT habitat_needs, COUNT(*) AS total_creatures, AVG(price) AS avg_price
FROM creatures
WHERE is_dangerous = FALSE
GROUP BY habitat_needs
HAVING COUNT(*) > 1 AND AVG(price) < 1500;

6. Update inserted data

Example : Changes Zoggo's price to 1599.99

UPDATE creatures
SET price = 1599.99
WHERE name = 'Zoggo';

Example : Marks all long-living creatures as temperament chill and is_dangerous false.

UPDATE creatures
SET temperament = 'chill', is_dangerous = FALSE
WHERE lifespan_years > 100;

Exmaple : Boosts small creatures to medium size

UPDATE creatures
SET size = 'medium'
WHERE size IN ('tiny', 'small');

7. Delete inserted data

Example : Deletes all purchases with a bad rating (2 or lower) 😂

DELETE FROM purchases
WHERE happiness_rating <= 2;

Example: Deletes all creatures whose names start with Z

DELETE FROM creatures
WHERE name LIKE 'Z%';

Example : Delete entire customers data

DELETE FROM customers;

8. Altering tables

Example : Adds a new column rarity_level to the creatures table

ALTER TABLE creatures
ADD COLUMN rarity_level VARCHAR(50);

Example : Changes the price column to allow larger values.

ALTER TABLE creatures
MODIFY COLUMN price DECIMAL(13,2);

Example : Remove a column

ALTER TABLE creatures
DROP COLUMN image_url;

Example : Rename the table itself

ALTER TABLE creatures TO alien_creatures;

Example : Ensures every customer must have an email.

ALTER TABLE customers
MODIFY COLUMN email VARCHAR(150) NOT NULL;

Example : Links purchases.customer_id to customers.id

ALTER TABLE purchases
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);