Introduction to SQL

-- This is your first SQL command! 🎉
SELECT 'Hello, SQL World!' AS greeting;

-- Copy and run this to create your task tracker! 🛠️
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    priority INTEGER DEFAULT 3,
    is_complete BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Challenge: Create a categories table
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    color TEXT DEFAULT 'blue'
);

-- Add your first task! 🎯
INSERT INTO tasks (title, description, priority)
VALUES ('Learn SQL', 'Complete the SQL lesson on Telebort', 1);

-- Add several tasks in one command! 🚀
INSERT INTO tasks (title, description, priority)
VALUES 
    ('Build a web app', 'Create my first database-driven app', 2),
    ('Practice SQL daily', 'Spend 30 minutes on SQL exercises', 1),
    ('Watch SQL tutorials', 'Find advanced SQL content', 3);

-- Your turn! Add 3 real tasks from your life
INSERT INTO tasks (title, description, priority)
VALUES 
    ('Your task 1', 'Description here', 1),
    ('Your task 2', 'Description here', 2),
    ('Your task 3', 'Description here', 3);

-- Show me everything! 📋
SELECT * FROM tasks;

-- Just show titles and priorities
SELECT title, priority FROM tasks;

-- Make it prettier with aliases
SELECT 
    title AS "Task Name",
    priority AS "Urgency Level",
    is_complete AS "Done?"
FROM tasks;

-- Find all urgent tasks (priority 1)
SELECT * FROM tasks 
WHERE priority = 1;

-- Find incomplete tasks
SELECT * FROM tasks 
WHERE is_complete = FALSE;

-- Find tasks with "SQL" in the title
SELECT * FROM tasks 
WHERE title LIKE '%SQL%';

-- Show tasks by priority (most urgent first)
SELECT * FROM tasks 
ORDER BY priority ASC;

-- Show newest tasks first
SELECT * FROM tasks 
ORDER BY created_at DESC;

-- Get your top 3 urgent tasks
SELECT * FROM tasks 
WHERE is_complete = FALSE
ORDER BY priority ASC
LIMIT 3;

-- Find your most important incomplete tasks
SELECT 
    title AS "📌 Task",
    CASE priority
        WHEN 1 THEN '🔴 URGENT'
        WHEN 2 THEN '🟡 Important'
        WHEN 3 THEN '🟢 Normal'
    END AS "Priority Level"
FROM tasks
WHERE is_complete = FALSE
ORDER BY priority ASC;

-- Complete your first task! ✅
UPDATE tasks 
SET is_complete = TRUE 
WHERE id = 1;

-- Make a task more urgent
UPDATE tasks 
SET priority = 1 
WHERE title = 'Build a web app';

-- Update multiple fields at once
UPDATE tasks 
SET priority = 2,
    description = 'This is now more important!'
WHERE id = 3;

-- Mark all priority 3 tasks as complete
UPDATE tasks 
SET is_complete = TRUE 
WHERE priority = 3;

-- Add "URGENT: " to all priority 1 task titles
UPDATE tasks 
SET title = 'URGENT: ' || title 
WHERE priority = 1 AND title NOT LIKE 'URGENT:%';

-- DANGEROUS: Updates ALL tasks
UPDATE tasks SET priority = 1;  -- 😱 Don't do this!

-- SAFE: Updates specific tasks
UPDATE tasks SET priority = 1 WHERE id = 5;  -- ✅ Much better!

-- Delete task with ID 5
DELETE FROM tasks 
WHERE id = 5;

-- Clean up all completed tasks
DELETE FROM tasks 
WHERE is_complete = TRUE;

-- Delete old completed tasks (if you had a due_date column)
DELETE FROM tasks 
WHERE is_complete = TRUE 
AND created_at < date('now', '-30 days');

-- NEVER do this (deletes EVERYTHING):
DELETE FROM tasks;  -- 😱 NO!

-- ALWAYS use WHERE:
DELETE FROM tasks WHERE id = 10;  -- ✅ YES!

-- Test with SELECT first:
SELECT * FROM tasks WHERE priority = 3;  -- Check what you'll delete
DELETE FROM tasks WHERE priority = 3;    -- Then delete if correct

-- 1. Task Summary Report
SELECT 
    COUNT(*) AS "Total Tasks",
    SUM(CASE WHEN is_complete = TRUE THEN 1 ELSE 0 END) AS "Completed",
    SUM(CASE WHEN is_complete = FALSE THEN 1 ELSE 0 END) AS "Pending"
FROM tasks;

-- 2. Tasks by Priority
SELECT 
    CASE priority
        WHEN 1 THEN '🔴 Urgent'
        WHEN 2 THEN '🟡 Important' 
        WHEN 3 THEN '🟢 Normal'
    END AS "Priority Level",
    COUNT(*) AS "Number of Tasks"
FROM tasks
GROUP BY priority
ORDER BY priority;

-- 3. Completion Rate
SELECT 
    ROUND(
        CAST(SUM(CASE WHEN is_complete = TRUE THEN 1 ELSE 0 END) AS FLOAT) / 
        COUNT(*) * 100, 2
    ) || '%' AS "Completion Rate"
FROM tasks;

-- Add a due date column
ALTER TABLE tasks 
ADD COLUMN due_date DATE;

-- Add tags for better organization
CREATE TABLE tags (
    id INTEGER PRIMARY KEY,
    task_id INTEGER,
    tag_name TEXT,
    FOREIGN KEY (task_id) REFERENCES tasks(id)
);

-- Create a view for easy access to urgent tasks
CREATE VIEW urgent_tasks AS
SELECT * FROM tasks
WHERE priority = 1 AND is_complete = FALSE
ORDER BY created_at;

-- Now you can simply query:
SELECT * FROM urgent_tasks;

-- Calculate your productivity score
SELECT 
    'Your Productivity Score: ' || 
    CAST(
        (SUM(CASE WHEN is_complete = TRUE THEN priority ELSE 0 END) * 10) 
        AS TEXT
    ) || ' points!' AS score,
    'Tasks completed: ' || SUM(CASE WHEN is_complete = TRUE THEN 1 ELSE 0 END) AS completed,
    'High priority completed: ' || 
        SUM(CASE WHEN is_complete = TRUE AND priority = 1 THEN 1 ELSE 0 END) AS urgent_done
FROM tasks;

-- Create projects table
CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    start_date DATE,
    deadline DATE,
    status TEXT DEFAULT 'Active'
);

-- Update tasks to link to projects
ALTER TABLE tasks 
ADD COLUMN project_id INTEGER REFERENCES projects(id);

-- Create team members table
CREATE TABLE team_members (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    role TEXT
);

-- Create task assignments
CREATE TABLE task_assignments (
    task_id INTEGER REFERENCES tasks(id),
    member_id INTEGER REFERENCES team_members(id),
    assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (task_id, member_id)
);

-- Project Dashboard Query
SELECT 
    p.name AS "Project",
    COUNT(t.id) AS "Total Tasks",
    SUM(CASE WHEN t.is_complete = TRUE THEN 1 ELSE 0 END) AS "Completed",
    ROUND(
        CAST(SUM(CASE WHEN t.is_complete = TRUE THEN 1 ELSE 0 END) AS FLOAT) / 
        COUNT(t.id) * 100, 2
    ) || '%' AS "Progress"
FROM projects p
LEFT JOIN tasks t ON p.id = t.project_id
GROUP BY p.id, p.name;

-- Team Workload Report
SELECT 
    tm.name AS "Team Member",
    COUNT(ta.task_id) AS "Assigned Tasks",
    SUM(CASE WHEN t.is_complete = FALSE THEN 1 ELSE 0 END) AS "Pending Tasks"
FROM team_members tm
LEFT JOIN task_assignments ta ON tm.id = ta.member_id
LEFT JOIN tasks t ON ta.task_id = t.id
GROUP BY tm.id, tm.name
ORDER BY "Pending Tasks" DESC;

-- DO: Use meaningful names
CREATE TABLE user_profiles (...)  -- Clear and descriptive

-- DO: Always use WHERE with UPDATE/DELETE
UPDATE tasks SET priority = 1 WHERE id = 5;

-- DO: Test with SELECT before DELETE
SELECT * FROM tasks WHERE created_at < '2023-01-01';

-- DO: Use transactions for related changes
BEGIN TRANSACTION;
-- Your changes here
COMMIT;

-- DON'T: Use vague names
CREATE TABLE t1 (...)  -- What is t1?

-- DON'T: Forget WHERE clause
DELETE FROM tasks;  -- Deletes EVERYTHING!

-- DON'T: Store calculated values
-- Instead of storing age, store birth_date and calculate age

-- DON'T: Use SELECT * in production
-- Be specific about columns you need

-- ✅ STANDARD: Every database application needs these four operations
CREATE TABLE tasks (...);     -- CREATE: Structure your data
INSERT INTO tasks (...);      -- CREATE: Add new records  
SELECT * FROM tasks;          -- READ: Retrieve data
UPDATE tasks SET ...;         -- UPDATE: Modify existing data
DELETE FROM tasks WHERE ...;  -- DELETE: Remove data

-- ✅ GOOD: Always use constraints to protect data quality
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,           -- Unique identifier required
    title TEXT NOT NULL,              -- Essential fields cannot be empty
    priority INTEGER CHECK(priority BETWEEN 1 AND 3),  -- Validate ranges
    is_complete BOOLEAN DEFAULT FALSE -- Sensible defaults
);

-- ❌ AVOID: Tables without constraints
CREATE TABLE tasks (
    id INTEGER,
    title TEXT,
    priority INTEGER
);  -- No validation, allows bad data

-- ✅ PROFESSIONAL: Clear, readable SQL
SELECT 
    title AS "Task Name",
    priority AS "Urgency Level",
    created_at AS "Date Created"
FROM tasks
WHERE is_complete = FALSE
  AND priority = 1
ORDER BY created_at DESC;

-- ❌ AMATEUR: Hard to read and maintain
select title,priority,created_at from tasks where is_complete=false and priority=1 order by created_at desc;

-- ✅ PROFESSIONAL: Use transactions for related operations
BEGIN TRANSACTION;
    UPDATE tasks SET is_complete = TRUE WHERE id = 1;
    INSERT INTO task_history (task_id, action, timestamp) 
    VALUES (1, 'completed', CURRENT_TIMESTAMP);
COMMIT;

-- ❌ RISKY: Individual operations without transaction
UPDATE tasks SET is_complete = TRUE WHERE id = 1;
INSERT INTO task_history (task_id, action, timestamp) 
VALUES (1, 'completed', CURRENT_TIMESTAMP);
-- If second operation fails, data becomes inconsistent

-- ✅ FAST: Index commonly searched columns
CREATE INDEX idx_task_priority ON tasks(priority);
CREATE INDEX idx_task_status ON tasks(is_complete);
CREATE INDEX idx_task_date ON tasks(created_at);

-- ✅ EFFICIENT: Select only needed columns
SELECT title, priority FROM tasks WHERE is_complete = FALSE;

-- ❌ SLOW: Don't use SELECT * in production applications
SELECT * FROM tasks WHERE is_complete = FALSE;

-- ✅ SECURE: Always validate input (prevent SQL injection)
-- In real applications, use parameterized queries
-- Example of safe query structure:
SELECT * FROM tasks WHERE user_id = ? AND title = ?;

-- ❌ DANGEROUS: Never build queries with string concatenation
-- query = "SELECT * FROM tasks WHERE title = '" + userInput + "'";

-- ❌ Error: Missing comma
INSERT INTO tasks (title description priority)
VALUES ('Learn SQL' 'Complete lesson' 1);

-- ✅ Fix: Add commas between columns and values
INSERT INTO tasks (title, description, priority)
VALUES ('Learn SQL', 'Complete lesson', 1);

-- ❌ Error: Typo in column name
SELECT title, prioritiy FROM tasks;  -- 'prioritiy' is misspelled

-- ✅ Fix: Check exact column names
SELECT title, priority FROM tasks;

-- 💡 Tip: Use .schema or DESCRIBE to see column names
.schema tasks  -- SQLite
DESCRIBE tasks;  -- MySQL

-- ❌ Error: Table name wrong
SELECT * FROM task;  -- Should be 'tasks' (plural)

-- ✅ Fix: Use correct table name
SELECT * FROM tasks;

-- 💡 Tip: List all tables
.tables  -- SQLite
SHOW TABLES;  -- MySQL

-- ❌ Error: Missing WHERE clause (safety feature)
DELETE FROM tasks;  -- Deletes ALL tasks!

-- ✅ Fix: Always use WHERE for DELETE/UPDATE
DELETE FROM tasks WHERE id = 5;
DELETE FROM tasks WHERE is_complete = TRUE;

-- 💡 Tip: Test with SELECT first
SELECT * FROM tasks WHERE is_complete = TRUE;  -- Check what will be deleted
DELETE FROM tasks WHERE is_complete = TRUE;    -- Then delete if correct

-- ❌ Error: Wrong data type
INSERT INTO tasks (priority) VALUES ('high');  -- 'high' is text, priority expects number

-- ✅ Fix: Use correct data type
INSERT INTO tasks (priority) VALUES (1);  -- 1 for high priority

-- Or create enum-style validation
CREATE TABLE tasks (
    priority TEXT CHECK(priority IN ('high', 'medium', 'low'))
);

-- Test with one record before batch operations
INSERT INTO tasks (title) VALUES ('Test Task');
SELECT * FROM tasks WHERE title = 'Test Task';
-- Only proceed if this works correctly

-- Find urgent incomplete tasks for today's focus
SELECT 
    title,                    -- Task name for display
    priority,                 -- 1=urgent, 2=important, 3=normal
    created_at               -- When task was added
FROM tasks
WHERE is_complete = FALSE    -- Only unfinished tasks
  AND priority = 1;          -- Only urgent tasks

-- Always backup before major changes
.backup tasks_backup.db  -- SQLite

-- Use transactions for multiple operations
BEGIN TRANSACTION;
-- Your operations here
COMMIT;  -- or ROLLBACK if something goes wrong

-- Table Creation
CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column_name DATA_TYPE CONSTRAINTS
);

-- Data Operations (CRUD)
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
SELECT column1, column2 FROM table_name WHERE condition;
UPDATE table_name SET column1 = 'new_value' WHERE condition;
DELETE FROM table_name WHERE condition;

-- Table Structure Changes
ALTER TABLE table_name ADD COLUMN column_name DATA_TYPE;
DROP TABLE table_name;

-- Comparison Operators
WHERE priority = 1                    -- Exact match
WHERE priority > 1                    -- Greater than
WHERE priority <= 2                   -- Less than or equal
WHERE priority BETWEEN 1 AND 2        -- Range
WHERE priority IN (1, 2)              -- List match
WHERE title LIKE '%SQL%'              -- Pattern match
WHERE description IS NULL             -- Check for null
WHERE description IS NOT NULL         -- Check for not null

-- Logical Operators
WHERE priority = 1 AND is_complete = FALSE       -- Both conditions
WHERE priority = 1 OR priority = 2               -- Either condition
WHERE NOT is_complete = TRUE                     -- Negation

NOT NULL              -- Field must have a value
UNIQUE                -- Value must be unique across table
PRIMARY KEY           -- Unique identifier for record
DEFAULT value         -- Default value if none provided
CHECK(condition)      -- Validate data meets condition
FOREIGN KEY           -- Reference to another table

COUNT(*)              -- Count all rows
COUNT(column)         -- Count non-null values
SUM(column)           -- Add up numeric values
AVG(column)           -- Calculate average
MIN(column)           -- Find minimum value
MAX(column)           -- Find maximum value

ORDER BY column ASC   -- Sort ascending (default)
ORDER BY column DESC  -- Sort descending
LIMIT 10              -- Show only first 10 results
LIMIT 10 OFFSET 20    -- Skip 20, show next 10 (pagination)

-- SQLite specific
.tables               -- List all tables
.schema table_name    -- Show table structure
.help                 -- Show available commands

-- Universal SQL
DESCRIBE table_name;  -- Show table structure (MySQL/PostgreSQL)
SHOW TABLES;          -- List tables (MySQL)