Farhad Rahimi Klie

Posted on Nov 12

Mastering SQL — From Zero to Hero

#sql #database #tutorial #beginners

Structured Query Language (SQL) is the backbone of modern data-driven applications. Whether you're building a small app or a massive global platform, understanding SQL means you can shape, query, and manage your data with precision and confidence.

If you're a developer, data analyst, or just someone curious about databases — you’re in the right place. Let’s master SQL step by step. 💡

🧠 What is SQL?

SQL (Structured Query Language) is a standard language used to interact with relational databases like:

MySQL
PostgreSQL
SQLite
SQL Server
Oracle DB

With SQL you can:

✅ Store data
✅ Retrieve data
✅ Update or delete data
✅ Manage database structure
✅ Control user access

🗄️ What is a Database?

A database is an organized collection of data.
A table contains rows (records) and columns (fields).

Example table:

id	name	age	city
1	Alice	22	London
2	Bob	25	New York

⚙️ Core SQL Concepts

SQL is divided into multiple categories:

Category	Purpose
DDL	Defines structure (tables, schemas)
DML	Handles data (insert, update, delete)
DQL	Query data (SELECT)
DCL	Access control (GRANT, REVOKE)
TCL	Transaction control (COMMIT, ROLLBACK)

🏁 Let’s Start Coding SQL!

1️⃣ Creating a Database

CREATE DATABASE company;

2️⃣ Using the Database

USE company;

3️⃣ Creating a Table

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

4️⃣ Inserting Data

INSERT INTO employees (name, position, salary, hire_date)
VALUES 
('John', 'Developer', 5000.00, '2023-01-10'),
('Sara', 'Designer', 4500.00, '2022-11-05'),
('Mike', 'Manager', 7000.00, '2021-06-20');

5️⃣ Retrieving Data (SELECT)

SELECT * FROM employees;

To select specific columns:

SELECT name, salary FROM employees;

6️⃣ Filtering Data (WHERE)

SELECT * FROM employees WHERE salary > 5000;

7️⃣ Sorting Output (ORDER BY)

SELECT * FROM employees ORDER BY salary DESC;

8️⃣ LIMIT Results

SELECT * FROM employees ORDER BY salary DESC LIMIT 2;

9️⃣ Updating Data

UPDATE employees SET salary = 5500 WHERE name = 'John';

🔟 Deleting Data

DELETE FROM employees WHERE id = 3;

🔗 Relationships & Keys

Primary Key

Uniquely identifies each row:

id INT PRIMARY KEY

Foreign Key

Used to connect two tables:

department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)

📊 Aggregate Functions

Function	Description
COUNT()	Number of rows
AVG()	Average value
SUM()	Total sum
MAX()	Maximum value
MIN()	Minimum value

Example:

SELECT AVG(salary) FROM employees;

🧠 Grouping Data

SELECT position, COUNT(*) 
FROM employees
GROUP BY position;

🔍 Filter Groups (HAVING)

SELECT position, COUNT(*)
FROM employees
GROUP BY position
HAVING COUNT(*) > 1;

🔀 JOINs (Combining Tables)

INNER JOIN

SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

LEFT JOIN

SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

🧩 Subqueries

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

💾 Transactions

BEGIN;
UPDATE employees SET salary = salary - 500 WHERE id = 1;
UPDATE employees SET salary = salary + 500 WHERE id = 2;
COMMIT;

Rollback if needed:

ROLLBACK;

⚡ Indexing for Performance

CREATE INDEX idx_name ON employees(name);

✅ Faster lookups
⚠ Slightly slower inserts/updates

🛡 Views (Virtual Tables)

CREATE VIEW high_paid AS
SELECT name, salary FROM employees WHERE salary > 6000;

Use it:

SELECT * FROM high_paid;

👤 User Permissions

GRANT SELECT, INSERT ON company.* TO 'user'@'localhost';
REVOKE INSERT ON company.* FROM 'user'@'localhost';

✅ Final Thoughts

SQL is not just a query language — it's a must-have superpower in tech today. Master these fundamentals and you can:

🔥 Build scalable apps
🔥 Analyze massive datasets
🔥 Optimize database performance
🔥 Become a backend or data engineer

DEV Community