Basic Queries

Learn the fundamentals of SQL querying with SELECT statements, WHERE clauses, and result formatting.

SELECT Statements

Basic SELECT

-- Select all columns
SELECT * FROM logs;

-- Select specific columns
SELECT timestamp, level, message FROM logs;

-- Select with aliases
SELECT timestamp AS ts, level AS log_level FROM logs;

DISTINCT

SELECT DISTINCT level FROM logs;
SELECT DISTINCT user_id, session_id FROM events;

WHERE Clauses

Comparison Operators

-- Equality and inequality
SELECT * FROM logs WHERE level = 'error';
SELECT * FROM logs WHERE status_code != 200;
SELECT * FROM logs WHERE response_time > 1000;

-- Range comparisons
SELECT * FROM logs WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-02';
SELECT * FROM logs WHERE status_code IN (400, 401, 403, 404);

Boolean Logic

-- AND, OR, NOT
SELECT * FROM logs 
WHERE level = 'error' AND service = 'api-gateway';

SELECT * FROM logs 
WHERE level = 'error' OR level = 'fatal';

SELECT * FROM logs 
WHERE NOT level = 'debug';

Pattern Matching

-- LIKE operator
SELECT * FROM logs WHERE message LIKE '%connection%';
SELECT * FROM logs WHERE user_id LIKE 'admin_%';

-- NULL checks
SELECT * FROM logs WHERE error_code IS NULL;
SELECT * FROM logs WHERE error_code IS NOT NULL;

Sorting and Limiting

ORDER BY

-- Single column sorting
SELECT * FROM logs ORDER BY timestamp DESC;

-- Multiple column sorting
SELECT * FROM logs 
ORDER BY level ASC, timestamp DESC;

LIMIT

-- Limit results
SELECT * FROM logs LIMIT 100;

-- Combine with ORDER BY
SELECT * FROM logs 
ORDER BY timestamp DESC 
LIMIT 50;

CASE Expressions

-- Simple CASE
SELECT 
  level,
  CASE level
    WHEN 'error' THEN 'Critical'
    WHEN 'warn' THEN 'Warning'
    ELSE 'Normal'
  END as severity
FROM logs;

-- Searched CASE
SELECT 
  response_time,
  CASE 
    WHEN response_time < 100 THEN 'Fast'
    WHEN response_time < 500 THEN 'Normal'
    ELSE 'Slow'
  END as performance
FROM logs;

Example Queries

Error Analysis

-- Find recent errors
SELECT timestamp, service, message 
FROM logs 
WHERE level = 'error' 
  AND timestamp >= '2024-01-01'
ORDER BY timestamp DESC 
LIMIT 20;

Performance Monitoring

-- Slow requests by service
SELECT service, response_time, user_id
FROM logs 
WHERE response_time > 1000
  AND timestamp >= NOW() - INTERVAL '1 hour'
ORDER BY response_time DESC;

User Activity

-- Active users with error classification
SELECT 
  user_id,
  CASE 
    WHEN level IN ('error', 'fatal') THEN 'Has Errors'
    WHEN level = 'warn' THEN 'Has Warnings'
    ELSE 'Normal Activity'
  END as status
FROM logs 
WHERE timestamp >= '2024-01-01'
ORDER BY user_id;