Learning SQL

Table of contents

• Introduction
• Beginner guide
  • 1. What is SQL?
  • 2. Basic SQL Commands
    • a. SELECT
    • b. WHERE
    • c. INSERT INTO
    • d. UPDATE
    • e. DELETE
    • f. CREATE TABLE
    • g. ALTER TABLE
    • h. DROP TABLE
  • 3. Filtering and Sorting Data
    • a. AND, OR, NOT
    • b. ORDER BY
    • c. LIMIT
  • 4. Aggregating Data
    • a. COUNT
    • b. SUM
    • c. AVG
    • d. MIN and MAX
    • e. GROUP BY
    • f. HAVING
  • 5. Joins
    • a. INNER JOIN
    • b. LEFT JOIN
    • c. RIGHT JOIN
    • d. FULL OUTER JOIN
  • 6. Subqueries
  • 7. Indexes
  • 8. Transactions
• Conclusion

Introduction

SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. It allows you to perform tasks such as querying data, updating records, inserting new data, and deleting data.

Beginner guide

Below is a beginner-friendly guide to SQL:

1. What is SQL?

SQL is used to interact with relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. It is declarative, meaning you tell the database what you want, and the database figures out how to do it.

2. Basic SQL Commands

Here are the most common SQL commands:

a. SELECT

Used to retrieve data from a database.

SELECT column1, column2 FROM table_name;

• Example: Get all names from a users table.

SELECT name FROM users;

• To select all columns, use *:

SELECT * FROM users;

b. WHERE

Filters records based on a condition.

SELECT column1, column2 FROM table_name WHERE condition;

• Example: Get users with the name “John”.

SELECT * FROM users WHERE name = 'John';

c. INSERT INTO

Adds new records to a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

• Example: Add a new user.

INSERT INTO users (name, age) VALUES ('Alice', 25);

d. UPDATE

Modifies existing records in a table.

UPDATE table_name SET column1 = value1 WHERE condition;

• Example: Update the age of a user named “Alice”.

UPDATE users SET age = 26 WHERE name = 'Alice';

e. DELETE

Removes records from a table.

DELETE FROM table_name WHERE condition;

• Example: Delete a user named “Bob”.

DELETE FROM users WHERE name = 'Bob';

f. CREATE TABLE

Creates a new table in the database.

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

• Example: Create a users table.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

g. ALTER TABLE

Modifies an existing table (e.g., add or delete columns).

ALTER TABLE table_name ADD column_name datatype;

• Example: Add an email column to the users table.

ALTER TABLE users ADD email VARCHAR(100);

h. DROP TABLE

Deletes an entire table.

DROP TABLE table_name;

• Example: Delete the users table.

DROP TABLE users;

3. Filtering and Sorting Data

a. AND, OR, NOT

Combine multiple conditions in a WHERE clause.

SELECT * FROM users WHERE age > 20 AND name = 'John';

b. ORDER BY

Sorts the result set in ascending (ASC) or descending (DESC) order.

SELECT * FROM users ORDER BY age DESC;

c. LIMIT

Restricts the number of rows returned.

SELECT * FROM users LIMIT 10;

4. Aggregating Data

a. COUNT

Counts the number of rows.

SELECT COUNT(*) FROM users;

b. SUM

Calculates the sum of a numeric column.

SELECT SUM(age) FROM users;

c. AVG

Calculates the average of a numeric column.

SELECT AVG(age) FROM users;

d. MIN and MAX

Finds the minimum and maximum values in a column.

SELECT MIN(age), MAX(age) FROM users;

e. GROUP BY

Groups rows that have the same values into summary rows.

SELECT name, COUNT(*) FROM users GROUP BY name;

f. HAVING

Filters groups based on a condition (used with GROUP BY).

SELECT name, COUNT(*) FROM users GROUP BY name HAVING COUNT(*) > 1;

5. Joins

Joins are used to combine rows from two or more tables based on a related column.

a. INNER JOIN

Returns records that have matching values in both tables.

SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

b. LEFT JOIN

Returns all records from the left table and matched records from the right table.

SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

c. RIGHT JOIN

Returns all records from the right table and matched records from the left table.

SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

d. FULL OUTER JOIN

Returns all records when there is a match in either left or right table.

SELECT users.name, orders.order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

6. Subqueries

A subquery is a query nested inside another query.

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

7. Indexes

Indexes improve the speed of data retrieval.

CREATE INDEX idx_name ON users (name);

8. Transactions

Transactions ensure data integrity by grouping multiple operations into a single unit.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Conclusion

SQL is a very important subject to know at a theoretical level to understand how to handle databases, these days we have ORMs that make the work easy for us, but sometimes we need to use raw SQL to get exactly what we want or for debugging purposes when using an ORM which is very popular in Javascript, but for other environments we might not be so lucky and we will need the knowledge.

See you on the next post.

Sincerely,

Eng. Adrian Beria.