AIOPS/MLOps - SQL Fundamentals

Beginner's Guide

Structured Query Language (SQL)

In this demo, we will be using PostgreSQL and pgAdmin. Most SQL databases share many of the same core queries. The idea is to understand SQL fundamentals with ease.

1. To Create a Database

Code:

CREATE DATABASE sql_demo;

In the PostgreSQL terminal, connect to the database using:

\connect sql_demo

2. Create the Users Table

Let's create a table to store user information.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL,
    age INTEGER,
    location VARCHAR(50)
);

Insert sample users into the table:

INSERT INTO users (user_id, user_name, age, location)
VALUES
(1, 'John', 28, 'Portugal'),
(2, 'Mary', 34, 'India'),
(3, 'David', 41, 'UK'),
(4, 'Sarah', 25, 'Germany'),
(5, 'Michael', 38, 'USA'),
(6, 'Emma', 30, 'Portugal'),
(7, 'Daniel', 45, 'India'),
(8, 'Sophia', 27, 'UK'),
(9, 'James', 32, 'Germany'),
(10, 'Olivia', 29, 'USA');

3. Create the Products Table

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0)
);

Insert sample products:

INSERT INTO products (product_id, product_name, category, price)
VALUES
(101, 'Washing Machine', 'Home Appliances', 450.00),
(102, 'Refrigerator', 'Home Appliances', 650.00),
(103, 'Microwave Oven', 'Home Appliances', 120.00),
(104, 'Laptop', 'Electronics', 950.00),
(105, 'Smartphone', 'Electronics', 700.00),
(106, 'Headphones', 'Electronics', 80.00),
(107, 'Television', 'Electronics', 550.00),
(108, 'Coffee Machine', 'Home Appliances', 150.00),
(109, 'Running Shoes', 'Fashion', 90.00),
(110, 'T-Shirt', 'Fashion', 25.00),
(111, 'Vacuum Cleaner', 'Home Appliances', 200.00),
(112, 'Dishwasher', 'Home Appliances', 500.00);

4. Create the Purchases Table

This table connects users to the products they purchase.

CREATE TABLE purchases (
    purchase_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    purchase_date DATE NOT NULL
);

Insert sample purchases:

INSERT INTO purchases
    (purchase_id, user_id, product_id, quantity, purchase_date)
VALUES
(1, 1, 101, 1, '2026-01-05'),
(2, 1, 104, 1, '2026-01-06'),
(3, 1, 106, 2, '2026-01-07'),
(4, 2, 101, 1, '2026-01-08'),
(5, 2, 105, 1, '2026-01-09'),
(6, 3, 102, 1, '2026-01-10'),
(7, 3, 104, 1, '2026-01-11'),
(8, 4, 101, 1, '2026-01-12'),
(9, 4, 109, 2, '2026-01-13'),
(10, 5, 105, 1, '2026-01-14'),
(11, 5, 107, 1, '2026-01-15'),
(12, 6, 101, 1, '2026-01-16'),
(13, 6, 103, 1, '2026-01-17'),
(14, 7, 102, 1, '2026-01-18'),
(15, 7, 111, 1, '2026-01-19'),
(16, 8, 101, 1, '2026-01-20'),
(17, 8, 110, 3, '2026-01-21'),
(18, 9, 104, 1, '2026-01-22'),
(19, 9, 108, 1, '2026-01-23'),
(20, 10, 101, 1, '2026-01-24');

Now that we have created our tables, let's learn SQL concepts individually.

5. SELECT Statement

The SELECT statement is used to retrieve information from a table.

SELECT * FROM products;

To retrieve only particular columns:

SELECT product_id, product_name FROM products;

6. WHERE Clause

The WHERE clause filters records based on a condition. For example, retrieve products in the Electronics category:

SELECT *
FROM products
WHERE category = 'Electronics';

7. ORDER BY

ORDER BY sorts results. The default order is ascending (ASC). Use DESC for descending order.

SELECT *
FROM products
WHERE category = 'Electronics'
ORDER BY product_name ASC;

SELECT *
FROM products
WHERE category = 'Electronics'
ORDER BY product_name DESC;
Note: The typical SQL clause order is SELECT, FROM, WHERE, ORDER BY.

8. Arithmetic Operators

You can perform mathematical calculations in SQL. For example, calculate a value equal to 20% of the price:

SELECT *,
       price * 0.2 AS discounted_price
FROM products
WHERE category = 'Electronics'
ORDER BY product_name DESC;

Add 20 to the price:

SELECT *,
       price + 20 AS hiked_price
FROM products
WHERE category = 'Electronics'
ORDER BY product_name DESC;

Other arithmetic examples:

SELECT *, price + 20 - 40 AS adjusted_price
FROM products
WHERE category = 'Electronics';

SELECT *, price % 20 AS remainder_price
FROM products
WHERE category = 'Electronics';

9. DISTINCT

DISTINCT returns unique values, removing duplicates from the result.

SELECT DISTINCT category
FROM products;

10. Comparison Operators

Comparison operators compare values:

  • > — greater than
  • < — less than
  • >= — greater than or equal to
  • <= — less than or equal to
  • != and <> — not equal to
SELECT * FROM products WHERE price > 450;
SELECT * FROM products WHERE price >= 650;
SELECT * FROM products WHERE price != 650;
SELECT * FROM products WHERE price <> 650;

11. NOT Operator

NOT reverses a Boolean condition.

SELECT *
FROM products
WHERE NOT price != 650;

12. AND and OR

AND requires both conditions to be true. OR requires at least one condition to be true.

SELECT *
FROM products
WHERE price >= 650 AND category = 'Electronics';

SELECT *
FROM products
WHERE price >= 650 OR category = 'Electronics';

13. IN Operator

IN is a convenient alternative to combining multiple equality checks with OR.

SELECT *
FROM products
WHERE category IN ('Electronics', 'Home Appliances');

14. BETWEEN Operator

BETWEEN selects values within a range, including both endpoints.

SELECT *
FROM products
WHERE price BETWEEN 100 AND 500;

15. LIKE Operator

LIKE matches text patterns using wildcards:

  • d% — starts with d
  • %d — ends with d
  • %d% — contains d
SELECT *
FROM products
WHERE category LIKE '%cs';

SELECT *
FROM products
WHERE category LIKE '%c%';

16. Regular Expression Operator (~)

PostgreSQL supports regular expressions for advanced text matching.

  • ^d — starts with d
  • d$ — ends with d
  • ~* — case-insensitive regular-expression match
SELECT * FROM products WHERE category ~* '^e';
SELECT * FROM products WHERE category ~* 's$';
SELECT * FROM products WHERE category ~* '^h.[a-z]';
SELECT * FROM products WHERE category ~* '^..e';

17. LIMIT and OFFSET

LIMIT restricts the number of rows returned. OFFSET skips a specified number of rows, which is useful for pagination.

SELECT * FROM products LIMIT 5;

SELECT * FROM products LIMIT 5 OFFSET 2;

18. JOIN

An INNER JOIN returns rows with matching values in both tables.

SELECT * FROM products;

SELECT * FROM purchases;

SELECT *
FROM products
JOIN purchases
ON products.product_id = purchases.product_id;

You can use aliases to make queries shorter:

SELECT *
FROM products AS p
JOIN purchases AS pr
ON p.product_id = pr.product_id;

19. Multiple JOINs

Join three tables to display user names, product names, and purchased quantities.

SELECT
    u.user_name,
    p.product_name,
    pu.quantity
FROM users AS u
JOIN purchases AS pu
    ON u.user_id = pu.user_id
JOIN products AS p
    ON pu.product_id = p.product_id;

20. GROUP BY

GROUP BY groups rows that share the same values.

SELECT category
FROM products
GROUP BY category;

21. Count Users Who Bought Each Product

This query counts the distinct users who purchased each product.

SELECT
    p.product_name,
    COUNT(DISTINCT pu.user_id) AS number_of_users
FROM products AS p
JOIN purchases AS pu
    ON p.product_id = pu.product_id
GROUP BY p.product_id, p.product_name
ORDER BY number_of_users DESC;

22. LEFT JOIN and RIGHT JOIN

First, insert a user who has no matching purchase records.

INSERT INTO users (user_id, user_name, age, location)
VALUES (0, 'Alex', 26, 'Portugal');

An INNER JOIN will not show Alex because he has no matching purchase.

SELECT
    u.user_id,
    u.user_name,
    pu.product_id,
    pu.quantity
FROM users AS u
JOIN purchases AS pu
    ON u.user_id = pu.user_id
ORDER BY u.user_id;

A LEFT JOIN includes all rows from the left table, even when there is no match in the right table.

SELECT
    u.user_id,
    u.user_name,
    pu.product_id,
    pu.quantity
FROM users AS u
LEFT JOIN purchases AS pu
    ON u.user_id = pu.user_id
ORDER BY u.user_id;

A RIGHT JOIN works similarly, but preserves all rows from the right table.

23. UPDATE

Update Alex's user ID:

UPDATE users
SET user_id = 11
WHERE user_name = 'Alex';

24. DELETE

Before deleting records, check which rows will be affected.

SELECT *
FROM users
WHERE user_name = 'Alex';

Delete only Alex's record:

DELETE FROM users
WHERE user_name = 'Alex';
Important: A DELETE statement without a WHERE clause deletes every row in the table. Always check the target rows before running a deletion.

25. DROP Tables and Databases

DROP TABLE deletes a table. DROP DATABASE deletes a database. Use these commands carefully because the operation can permanently remove data.

DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS purchases CASCADE;

Force-delete the database only when you are certain it is no longer needed.

DROP DATABASE sql_demo WITH (FORCE);

Comments

Post a Comment