SQL Essentials: Mastering Database Operations

SQL is the language you use to create, read, change and protect data in a relational database such as MySQL, MariaDB or PostgreSQL. A small set of commands covers almost everything a website or business app needs. This guide walks through them with one running example, from simple queries to joins, grouping and window functions, and ends with how to run SQL on Domain India hosting.

1. The five groups of SQL commands

The examples below use MySQL and MariaDB syntax. Most of it works unchanged in PostgreSQL and SQLite.

2. Create the example tables (DDL)

CREATE TABLE customers (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  city       VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  ordered_at  DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

• A primary key identifies each row uniquely.
• A foreign key makes sure every order belongs to a real customer.
• Use DECIMAL for money, never FLOAT, which rounds.
• Use the InnoDB engine: it supports transactions and foreign keys.

Change a table later with ALTER TABLE, for example ALTER TABLE customers ADD email VARCHAR(255);. DROP TABLE deletes a table and all its data, and TRUNCATE empties it; neither can be undone without a backup.

3. Add and change data (DML)

INSERT INTO customers (name, city) VALUES
  ('Asha Traders', 'Pune'),
  ('Ravi Stores', 'Chennai');

INSERT INTO orders (customer_id, amount, ordered_at) VALUES
  (1, 2500.00, '2026-09-01'),
  (1, 1200.00, '2026-09-15'),
  (2, 800.00,  '2026-09-10');

UPDATE customers SET city = 'Mumbai' WHERE id = 2;
DELETE FROM orders WHERE id = 3;

4. Read data (SELECT)

SELECT name AS customer, city
FROM customers
WHERE city IN ('Pune', 'Mumbai')
  AND name LIKE 'A%'
ORDER BY name ASC
LIMIT 10;

• AS gives a column or table an alias, a shorter or clearer name in the result.
• WHERE filters rows: =, <>, <, >, BETWEEN, IN, LIKE and IS NULL.
• ORDER BY sorts, ascending by default; add DESC for descending. Sort by several columns with ORDER BY city, name.
• LIMIT returns only the first rows, which is useful for pages of results.
• Name the columns you need rather than using SELECT *. The query is clearer and moves less data.

5. Summarise data: aggregates, GROUP BY and HAVING

Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN and MAX.

SELECT customer_id,
       COUNT(*)    AS orders,
       SUM(amount) AS total
FROM orders
WHERE ordered_at >= '2026-09-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total DESC;

WHERE filters rows before grouping; HAVING filters groups after. Every column in the SELECT list must either be inside an aggregate or listed in GROUP BY. MySQL enforces this with the ONLY_FULL_GROUP_BY mode, explained in what is SQL mode.

6. Combine tables with JOIN

-- Every customer with their order total, including customers with no orders
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Customers who have never ordered (an anti-join)
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

MySQL and MariaDB have no FULL OUTER JOIN; combine a LEFT and a RIGHT join with UNION instead. Index the columns you join on, or joins slow down as tables grow.

7. Window functions

Window functions calculate across related rows without collapsing them into one, as GROUP BY does. They work in MySQL 8.0 and later, MariaDB 10.2 and later, PostgreSQL and SQLite.

SELECT customer_id, ordered_at, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS running_total,
       RANK()      OVER (ORDER BY amount DESC)                         AS size_rank
FROM orders;

Useful ones: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG() and LEAD() (the previous or next row's value), and SUM() or AVG() with OVER for running totals and moving averages.

8. Transactions (TCL)

A transaction makes several changes succeed or fail together:

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both

Transactions need a transactional engine such as InnoDB. DROP, ALTER and other DDL commands commit immediately in MySQL and can't be rolled back.

9. Users and permissions (DCL)

On your own server, you create users and grant only what each one needs:

CREATE USER 'app'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';
REVOKE DELETE ON shop.* FROM 'app'@'localhost';

Give an application the least privilege it needs, never the root account. On shared hosting you create database users and assign their privileges in the control panel instead; see how to create a MySQL database user.

10. Running SQL on Domain India

• Shared hosting (cPanel, DirectAdmin and Webuzo) includes MariaDB/MySQL databases. Create them in the control panel. On cPanel and DirectAdmin, run SQL in phpMyAdmin, which also shows your server's exact version. On cPanel, phpMyAdmin imports files up to 50 MB. See how to access and manage your MySQL database.
• Desktop clients: port 3306 is closed to outside connections, so connect through an SSH tunnel. Jailed SSH access is available on every shared hosting plan; it is off by default, so ask support to enable it. See how to connect to the MySQL database.
• App Platform: apps come with a PostgreSQL database. See PostgreSQL on the App Platform.
• VPS: a self-managed server where you install and tune any database yourself.

For indexing, EXPLAIN and deeper MySQL features, continue with mastering MySQL. Prices on the card are live and exclude 18% GST.

Frequently asked questions

Ready to try your first queries? Open phpMyAdmin from your hosting services, or open a ticket if you need help with a database on your account.