Activity 2: Your First SQL Queries

-- Create a simple tasks table
CREATE TABLE tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    description TEXT,
    completed BOOLEAN DEFAULT false,
    created_date DATE
);

-- Add sample tasks
INSERT INTO tasks (title, description, created_date) VALUES
('Learn SQL', 'Complete database fundamentals course', '2024-01-15'),
('Build project', 'Create a web application', '2024-01-16'),
('Practice queries', 'Write 10 different SQL queries', '2024-01-17'),
('Study database design', 'Learn about normalization', '2024-01-18'),
('Review concepts', 'Go through all course materials', '2024-01-19');

-- Add a new task
INSERT INTO tasks (title, description, created_date) 
VALUES ('Review database concepts', 'Study for upcoming test', '2024-01-20');

-- View all tasks
SELECT * FROM tasks;

-- View specific columns
SELECT title, completed FROM tasks;

-- Filter with WHERE
SELECT title FROM tasks WHERE completed = false;

-- Mark a task as completed
UPDATE tasks 
SET completed = true 
WHERE title = 'Learn SQL';

-- Update multiple fields
UPDATE tasks 
SET completed = true, description = 'Successfully completed!' 
WHERE id = 1;

-- Remove a specific task
DELETE FROM tasks WHERE id = 5;

-- Remove completed tasks
DELETE FROM tasks WHERE completed = true;