SQL Basics - Complete Guide for Beginners

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. This guide covers all the fundamentals you need to get started with SQL.

What is SQL? #

SQL is a programming language designed for managing data in relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL Server, and Oracle.

Basic SQL Commands #

SQL commands are divided into categories:

DDL (Data Definition Language) - CREATE, ALTER, DROP
DML (Data Manipulation Language) - SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language) - GRANT, REVOKE
TCL (Transaction Control Language) - COMMIT, ROLLBACK

Creating Databases and Tables #

Create Database #

CREATE DATABASE company;

USE company;

Create Table #

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  salary DECIMAL(10, 2),
  department VARCHAR(50),
  hire_date DATE,
  is_active BOOLEAN DEFAULT TRUE
);

Data Types #

Numeric:

INT - Integer
DECIMAL(10,2) - Decimal with precision
FLOAT - Floating point number

String:

VARCHAR(255) - Variable length string
CHAR(10) - Fixed length string
TEXT - Large text

Date/Time:

DATE - Date (YYYY-MM-DD)
TIME - Time (HH:MM:SS)
DATETIME - Date and time
TIMESTAMP - Timestamp

Boolean:

BOOLEAN - True/False

Inserting Data #

Insert Single Row #

INSERT INTO employees (first_name, last_name, email, salary, department, hire_date)
VALUES ('John', 'Doe', 'john@example.com', 75000, 'Engineering', '2024-01-15');

Insert Multiple Rows #

INSERT INTO employees (first_name, last_name, email, salary, department, hire_date)
VALUES
  ('Jane', 'Smith', 'jane@example.com', 80000, 'Engineering', '2024-02-01'),
  ('Bob', 'Johnson', 'bob@example.com', 65000, 'Sales', '2024-03-10'),
  ('Alice', 'Williams', 'alice@example.com', 90000, 'Management', '2024-01-20');

Selecting Data #

Select All Columns #

SELECT * FROM employees;

Select Specific Columns #

SELECT first_name, last_name, salary
FROM employees;

WHERE Clause #

-- Equal to
SELECT * FROM employees
WHERE department = 'Engineering';

-- Greater than
SELECT * FROM employees
WHERE salary > 70000;

-- Multiple conditions (AND)
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;

-- Multiple conditions (OR)
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Sales';

-- IN operator
SELECT * FROM employees
WHERE department IN ('Engineering', 'Sales', 'Management');

-- BETWEEN operator
SELECT * FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- LIKE operator (pattern matching)
SELECT * FROM employees
WHERE email LIKE '%@example.com';

-- IS NULL
SELECT * FROM employees
WHERE department IS NULL;

-- NOT NULL
SELECT * FROM employees
WHERE department IS NOT NULL;

ORDER BY #

-- Ascending (default)
SELECT * FROM employees
ORDER BY salary;

-- Descending
SELECT * FROM employees
ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

LIMIT #

-- First 5 employees
SELECT * FROM employees
LIMIT 5;

-- Skip 5, then get 5 (pagination)
SELECT * FROM employees
LIMIT 5 OFFSET 5;

DISTINCT #

-- Get unique departments
SELECT DISTINCT department
FROM employees;

Updating Data #

-- Update single row
UPDATE employees
SET salary = 85000
WHERE id = 1;

-- Update multiple columns
UPDATE employees
SET salary = 70000, department = 'Engineering'
WHERE id = 2;

-- Update multiple rows
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

Deleting Data #

-- Delete specific row
DELETE FROM employees
WHERE id = 1;

-- Delete with condition
DELETE FROM employees
WHERE department = 'Sales' AND salary < 50000;

-- Delete all rows (careful!)
DELETE FROM employees;

Aggregate Functions #

-- Count
SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department) FROM employees;

-- Sum
SELECT SUM(salary) FROM employees;

-- Average
SELECT AVG(salary) FROM employees;

-- Min and Max
SELECT MIN(salary), MAX(salary) FROM employees;

GROUP BY #

-- Count employees by department
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

-- Average salary by department
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- Multiple columns
SELECT department, YEAR(hire_date) as hire_year, COUNT(*) as count
FROM employees
GROUP BY department, YEAR(hire_date);

HAVING Clause #

HAVING filters groups (use with GROUP BY), while WHERE filters rows.

-- Departments with more than 5 employees
SELECT department, COUNT(*) as count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Departments with average salary > 70000
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

Joins #

Inner Join #

Returns rows with matching values in both tables.

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  location VARCHAR(100)
);

-- Inner join
SELECT e.first_name, e.last_name, d.name as department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Left Join #

Returns all rows from left table and matching rows from right table.

SELECT e.first_name, e.last_name, d.name as department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Right Join #

Returns all rows from right table and matching rows from left table.

SELECT e.first_name, e.last_name, d.name as department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Full Outer Join #

Returns all rows from both tables.

SELECT e.first_name, e.last_name, d.name as department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Self Join #

Join table to itself.

-- Find employees with same salary
SELECT e1.first_name, e1.last_name, e2.first_name, e2.last_name
FROM employees e1
INNER JOIN employees e2 ON e1.salary = e2.salary AND e1.id != e2.id;

Subqueries #

In WHERE Clause #

-- Employees earning above average
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Employees in departments with >5 people
SELECT *
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING COUNT(*) > 5
);

In FROM Clause #

SELECT dept_name, avg_salary
FROM (
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
) as dept_avg
WHERE avg_salary > 70000;

String Functions #

-- Concatenate
SELECT CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

-- Uppercase
SELECT UPPER(first_name) FROM employees;

-- Lowercase
SELECT LOWER(email) FROM employees;

-- Substring
SELECT SUBSTRING(email, 1, 5) FROM employees;

-- Length
SELECT first_name, LENGTH(first_name) as name_length
FROM employees;

-- Replace
SELECT REPLACE(email, '@example.com', '@company.com')
FROM employees;

-- Trim whitespace
SELECT TRIM(first_name) FROM employees;

Date Functions #

-- Current date and time
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

-- Extract parts
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date)
FROM employees;

-- Date arithmetic
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) as anniversary
FROM employees;

SELECT DATEDIFF(NOW(), hire_date) as days_employed
FROM employees;

-- Format date
SELECT DATE_FORMAT(hire_date, '%M %d, %Y')
FROM employees;

Constraints #

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),
  category VARCHAR(50) DEFAULT 'General',
  sku VARCHAR(50) UNIQUE,
  supplier_id INT,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

Constraints:

PRIMARY KEY - Unique identifier
FOREIGN KEY - References another table
UNIQUE - No duplicate values
NOT NULL - Cannot be null
CHECK - Custom validation
DEFAULT - Default value

Indexes #

Improve query performance:

-- Create index
CREATE INDEX idx_email ON employees(email);
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

-- Drop index
DROP INDEX idx_email ON employees;

Altering Tables #

-- Add column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- Modify column
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(15);

-- Rename column
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;

-- Drop column
ALTER TABLE employees
DROP COLUMN phone_number;

-- Add constraint
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id);

Views #

Virtual tables based on queries:

-- Create view
CREATE VIEW high_earners AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 80000;

-- Use view
SELECT * FROM high_earners;

-- Drop view
DROP VIEW high_earners;

Transactions #

START TRANSACTION;

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

-- If everything is okay
COMMIT;

-- If something went wrong
ROLLBACK;

Common Patterns #

Pagination #

-- Page 1 (items 1-10)
SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 0;

-- Page 2 (items 11-20)
SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 10;

Find Duplicates #

SELECT email, COUNT(*) as count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

Delete Duplicates #

DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.id > e2.id AND e1.email = e2.email;

Rank/Row Number #

SELECT
  first_name,
  last_name,
  salary,
  RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

Running Total #

SELECT
  hire_date,
  COUNT(*) OVER (ORDER BY hire_date) as cumulative_hires
FROM employees;

Best Practices #

Use meaningful names for tables and columns
Always use WHERE with UPDATE and DELETE
Index frequently queried columns
**Avoid SELECT *** in production (specify columns)
Use transactions for related operations
Normalize your database to reduce redundancy
Use constraints to maintain data integrity
Back up your database regularly
Use prepared statements to prevent SQL injection
Test queries on sample data first

SQL vs NoSQL #

Aspect	SQL	NoSQL
Structure	Fixed schema	Flexible schema
Scaling	Vertical	Horizontal
Transactions	ACID compliant	Eventually consistent
Use Case	Complex queries	Large scale, high write
Examples	MySQL, PostgreSQL	MongoDB, Redis

SQL is the foundation of data management. Understanding these fundamentals will allow you to work with any relational database system.