SQL Fundamentals: A Complete Guide to Database Queries

SQL (Structured Query Language) is the backbone of database management and a crucial skill for any developer. Whether you're building web applications, analyzing data, or managing enterprise systems, understanding SQL fundamentals is essential for effective database interactions.

In my experience at Bell Canada, working with large-scale databases and complex queries, I've learned that mastering these SQL fundamentals is crucial for building robust, scalable applications. Let's dive into the core concepts that every developer should know.

1. Creating Your First SQL Table

The foundation of any database is its tables. SQL provides several data types to structure your data effectively:

• TEXT - For string data
• INTEGER - For whole numbers
• REAL - For decimal numbers
• NUMERIC - For precise numeric data
• INTEGER PRIMARY KEY - For unique identifiers

Here's how to create a simple table and insert data:

CREATE TABLE groceries (
  id INTEGER PRIMARY KEY, 
  name TEXT, 
  quantity INTEGER
);

INSERT INTO groceries VALUES(1, "Bananas", 4);

2. Querying Data with SELECT

Once you have data in your tables, you need to retrieve it efficiently. The SELECT statement is your primary tool for data retrieval.

CREATE TABLE groceries (
  id INTEGER PRIMARY KEY, 
  name TEXT, 
  quantity INTEGER, 
  aisle INTEGER
);

INSERT INTO groceries VALUES (1, "Bananas", 4, 7);
INSERT INTO groceries VALUES (2, "Peanut Butter", 1, 2);
INSERT INTO groceries VALUES (3, "Dark Chocolate Bars", 2, 2);
INSERT INTO groceries VALUES (4, "Ice cream", 1, 12);
INSERT INTO groceries VALUES (5, "Cherries", 6, 2);
INSERT INTO groceries VALUES (6, "Chocolate syrup", 1, 4);

-- Select all items from aisles greater than 5, ordered by aisle
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle;

The WHERE clause filters your results, while ORDER BY sorts them. This combination is essential for retrieving exactly the data you need.

3. Aggregating Data for Analysis

Aggregate functions are powerful tools for data analysis. They allow you to perform calculations across multiple rows and group results meaningfully.

-- Sum quantities by aisle
SELECT aisle, SUM(quantity) FROM groceries GROUP BY aisle;

Common aggregate functions include:

• SUM() - Total of numeric values
• COUNT() - Number of rows
• AVG() - Average value
• MIN()/MAX() - Minimum/Maximum values

4. Complex Queries with AND/OR

Real-world applications often require multiple conditions. The AND and OR operators allow you to create sophisticated filters.

CREATE TABLE exercise_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type TEXT,
  minutes INTEGER, 
  calories INTEGER,
  heart_rate INTEGER
);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) 
VALUES ("biking", 30, 100, 110);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) 
VALUES ("biking", 10, 30, 105);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) 
VALUES ("dancing", 15, 200, 120);

-- Multiple conditions with AND
SELECT * FROM exercise_logs 
WHERE calories > 50 AND minutes < 30;

-- Alternative conditions with OR
SELECT * FROM exercise_logs 
WHERE calories > 50 OR heart_rate > 100;

5. Subqueries and the IN Operator

Subqueries allow you to use the result of one query as input for another. This is particularly useful for dynamic filtering based on related data.

-- Create a reference table for doctor's favorites
CREATE TABLE drs_favorites (
  id INTEGER PRIMARY KEY,
  type TEXT,
  reason TEXT
);

INSERT INTO drs_favorites(type, reason) 
VALUES ("biking", "Improves endurance and flexibility.");
INSERT INTO drs_favorites(type, reason) 
VALUES ("hiking", "Increases cardiovascular health.");

-- Use subquery to filter exercises
SELECT * FROM exercise_logs 
WHERE type IN (
  SELECT type FROM drs_favorites
);

-- More specific filtering with LIKE operator
SELECT * FROM exercise_logs 
WHERE type IN (
  SELECT type FROM drs_favorites 
  WHERE reason LIKE "%cardiovascular%"
);

6. Advanced Filtering with HAVING

The HAVING clause is used to filter grouped results, unlike WHERE which filters individual rows before grouping.

-- Find exercise types with total calories > 150
SELECT type, SUM(calories) AS total_calories 
FROM exercise_logs
GROUP BY type
HAVING total_calories > 150;

-- Find exercise types with average calories > 70
SELECT type, AVG(calories) AS avg_calories 
FROM exercise_logs
GROUP BY type
HAVING avg_calories > 70;

-- Find exercise types that appear at least twice
SELECT type FROM exercise_logs 
GROUP BY type 
HAVING COUNT(*) >= 2;

Key Takeaways