SQL Quickstart

Introduction

SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in relational databases. This quickstart guide will help you understand the fundamentals of SQL and get you writing queries quickly.

SQL is case-insensitive for keywords, but it's a common convention to write SQL keywords in uppercase for better readability.

What is SQL?

SQL is a declarative programming language designed for managing data held in a relational database management system (RDBMS). Unlike procedural languages, you specify what you want, not how to get it.

Key Concepts

Database: A collection of related tables
Table: A collection of related data entries consisting of rows and columns
Row (Record): A single entry in a table
Column (Field): A single data point in a table
Primary Key: A unique identifier for each row
Foreign Key: A reference to a primary key in another table

Basic SQL Syntax

SQL statements typically follow this pattern:

SELECT column1, column2
FROM table_name
WHERE condition;

Important

Always end SQL statements with a semicolon (;) - it's required in most database systems.

Essential SQL Commands

SELECT - Retrieving Data

The SELECT statement is used to query data from a database.

Basic SELECT

-- Select all columns from users table
SELECT * FROM users;

-- Select specific columns
SELECT first_name, last_name, email FROM users;

-- Select with alias
SELECT first_name AS name, email AS contact FROM users;

WHERE - Filtering Data

Use WHERE to specify conditions for filtering rows.

WHERE Examples

-- Single condition
SELECT * FROM users WHERE age > 25;

-- Multiple conditions with AND
SELECT * FROM users WHERE age > 25 AND city = 'New York';

-- Multiple conditions with OR
SELECT * FROM users WHERE city = 'New York' OR city = 'Los Angeles';

-- Pattern matching with LIKE
SELECT * FROM users WHERE first_name LIKE 'J%';

ORDER BY - Sorting Results

ORDER BY Examples

-- Ascending order (default)
SELECT * FROM users ORDER BY age;

-- Descending order
SELECT * FROM users ORDER BY age DESC;

-- Multiple columns
SELECT * FROM users ORDER BY city, age DESC;

LIMIT - Restricting Results

LIMIT Examples

-- Get first 10 users
SELECT * FROM users LIMIT 10;

-- Skip first 10, get next 10 (pagination)
SELECT * FROM users LIMIT 10 OFFSET 10;

Common Operators

Comparison Operators

Operator	Description	Example
`=`	Equal	`age = 25`
`!=` or `<>`	Not equal	`age != 25`
`>`	Greater than	`age > 25`
`<`	Less than	`age < 25`
`>=`	Greater than or equal	`age >= 25`
`<=`	Less than or equal	`age <= 25`

Logical Operators

-- AND - both conditions must be true
SELECT * FROM users WHERE age > 18 AND age < 65;

-- OR - at least one condition must be true
SELECT * FROM users WHERE city = 'NYC' OR city = 'LA';

-- NOT - negates a condition
SELECT * FROM users WHERE NOT city = 'Chicago';

-- IN - matches any value in a list
SELECT * FROM users WHERE city IN ('NYC', 'LA', 'Chicago');

-- BETWEEN - within a range
SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- IS NULL / IS NOT NULL - checks for null values
SELECT * FROM users WHERE phone IS NOT NULL;

Pattern Matching with LIKE

The LIKE operator uses wildcards for pattern matching:

% - matches any sequence of characters
_ - matches any single character

LIKE Examples

-- Names starting with 'A'
SELECT * FROM users WHERE first_name LIKE 'A%';

-- Names ending with 'son'
SELECT * FROM users WHERE last_name LIKE '%son';

-- Names containing 'ann'
SELECT * FROM users WHERE first_name LIKE '%ann%';

-- Exactly 4 characters
SELECT * FROM users WHERE first_name LIKE '____';

-- Second letter is 'a'
SELECT * FROM users WHERE first_name LIKE '_a%';

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value.

Common Aggregate Functions

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;

-- Sum values
SELECT SUM(salary) FROM employees;

-- Average
SELECT AVG(age) FROM users;

-- Min and Max
SELECT MIN(age), MAX(age) FROM users;

Grouping Data

Use GROUP BY to group rows with the same values and perform aggregate functions on each group.

GROUP BY Examples

-- Count users by city
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city;

-- Average age by city
SELECT city, AVG(age) as avg_age
FROM users
GROUP BY city;

-- Filter groups with HAVING
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

GROUP BY vs HAVING

Use WHERE to filter rows before grouping, and HAVING to filter groups after grouping.

Next Steps

Now that you understand the basics of SQL queries, you're ready to explore:

Data types and table structures
Creating and modifying tables
Advanced queries with joins
Database design principles