Back to Data & Databases Next: NoSQL Databases

SQL Basics

Learn Structured Query Language (SQL) to work with relational databases. Master data retrieval, manipulation, and database design fundamentals.

🗃️ What is SQL?

SQL (Structured Query Language) is a standardized language for managing and manipulating relational databases. It allows you to create, read, update, and delete data stored in tables with defined relationships.

Key Features:

• Declarative language (what, not how)
• Standardized across database systems
• Powerful for complex data relationships
• ACID compliance for data integrity
• Mature ecosystem and tooling

Common Use Cases:

• Web application backends
• Business data management
• Reporting and analytics
• Financial systems
• Content management systems

Core SQL Concepts

Basic Queries

SELECT, INSERT, UPDATE, DELETE operations

Table Relationships

Primary keys, foreign keys, and joins

Data Types

VARCHAR, INTEGER, DATE, BOOLEAN, etc.

Constraints

NOT NULL, UNIQUE, CHECK constraints

Essential SQL Commands

📝 Basic SQL Operations

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email, age) 
VALUES ('Alice Johnson', 'alice@example.com', 28);

INSERT INTO users (name, email, age) 
VALUES 
    ('Bob Smith', 'bob@example.com', 35),
    ('Carol Davis', 'carol@example.com', 42);

-- Select data
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 30;
SELECT COUNT(*) FROM users;

-- Update data
UPDATE users 
SET age = 29 
WHERE name = 'Alice Johnson';

-- Delete data
DELETE FROM users WHERE age < 25;

Advanced SQL Concepts

🔗 Joins and Relationships

-- Create related tables
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_name VARCHAR(100),
    quantity INTEGER,
    price DECIMAL(10,2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Inner Join - Get users with their orders
SELECT u.name, u.email, o.product_name, o.quantity, o.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left Join - Get all users, including those without orders
SELECT u.name, u.email, o.product_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Aggregate functions with GROUP BY
SELECT u.name, COUNT(o.id) as order_count, SUM(o.price) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0;

-- Subqueries
SELECT name, email 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM orders 
    WHERE price > 100
);

Popular SQL Databases

PostgreSQL

Advanced open-source relational database

Advantages:

ACID compliantJSON supportExtensibleGreat performance

Best For:

Complex applications, data analytics, web applications

MySQL

Popular open-source database

Advantages:

FastReliableEasy to useWide support

Best For:

Web applications, content management, e-commerce

SQLite

Lightweight, serverless database

Advantages:

No setupPortableFastReliable

Best For:

Mobile apps, prototyping, small applications

SQL Server

Microsoft's enterprise database

Advantages:

Enterprise featuresIntegrationSecurityScalability

Best For:

Enterprise applications, .NET ecosystem, business intelligence

🎯 SQL Learning Path

Basic Queries (1-2 weeks)

SELECT, INSERT, UPDATE, DELETE, WHERE clauses

Database Design (2-3 weeks)

Tables, relationships, normalization, constraints

Advanced Queries (3-4 weeks)

JOINs, subqueries, aggregate functions, window functions

Performance & Administration (Ongoing)

Indexing, query optimization, backup, security

📚 SQL Learning Resources

Free Resources:

• W3Schools SQL Tutorial
• SQLBolt (Interactive lessons)
• PostgreSQL Tutorial
• MySQL Documentation
• SQLite Tutorial

Practice Platforms:

• HackerRank SQL challenges
• LeetCode Database problems
• SQLZoo interactive tutorials
• DB Fiddle (online SQL editor)
• Sample databases (Northwind, Sakila)

Back to Data & Databases Next: NoSQL Databases