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