/**
 * Some sample SQL queries for the task manager application
 *
 * Tables:
 * (1) list: id (PK), name
 * (2) task: id (PK), list_id (FK), description, deadline, status
 */

-- General queries (selects all columns and rows) 
SELECT * FROM list;
SELECT * FROM task;


-- Select description and deadline columns and all rows
SELECT description, deadline
FROM task;


-- Select description and deadline columns from task 1
SELECT description, deadline
FROM task
WHERE id = 1;


-- Select incomplete tasks on list 1 (optional renaming)
SELECT description, deadline
FROM task
WHERE list_id = 1 AND status = 0;

SELECT description AS 'Incomplete tasks', deadline
FROM task
WHERE list_id = 1 AND status = 0;


-- Select list name and detailed task information by joining tables
SELECT list.name, task.description, task.deadline, task.status
FROM list
INNER JOIN task ON task.list_id = list.id;


-- Add a 'completion_date' column to task
ALTER TABLE task
ADD completion_date DATETIME DEFAULT NULL;


-- Denote that task 1 has been completed right now
UPDATE task
SET status = 1, completion_date = CURRENT_TIMESTAMP
WHERE id = 1;

SELECT * FROM task;


-- Delete completed tasks
DELETE FROM task
WHERE status = 1;

SELECT * FROM task;
