★ tasks (id, name, status, project_id)
★ projects (id, name)
-
get all statuses, not repeating, alphabetically ordered
SELECT DISTINCT status FROM task ORDER BY status ASC;
-
get the count of all tasks in each project, order by tasks count descending
SELECT DISTINCT project_id, count(id) AS projecsCount FROM task GROUP BY project_id ORDER BY projecsCount DESC;
-
get the count of all tasks in each project, order by projects names
SELECT project.name, count(task.id) FROM project LEFT JOIN task ON project_id = project.id GROUP BY project_id ORDER BY project.name;
-
get the tasks for all projects having the name beginning with “N” letter
SELECT * FROM task WHERE name LIKE 'N%';
-
get the list of all projects containing the ‘a’ letter in the middle of the name, and show the tasks count near each project. Mention that there can exist projects without tasks and tasks with project_id=NULL
SELECT project.name, count(task.id) FROM project LEFT JOIN task ON task.project_id = project.id WHERE project.name LIKE '%a%' GROUP BY project_id;
-
get the list of tasks with duplicate names. Order alphabetically
SELECT name FROM task GROUP BY name HAVING count(name)>1 ORDER BY name ASC;
-
get the list of tasks having several exact matches of both name and status, from the project ‘Garage’. Order by matches count
SELECT name, count(name) AS mathces FROM task WHERE task.project_id = (SELECT project.id FROM project WHERE project.name LIKE 'Garage') GROUP BY task.name, task.status HAVING count(*) > 1 ORDER BY mathces ASC;
-
get the list of project names having more than 10 tasks in status ‘completed’. Order by project_id
SELECT project.id, project.name, count(project_id) AS countoftasks FROM project LEFT JOIN task ON task.project_id = project.id WHERE task.status = 'completed' GROUP BY project_id HAVING countoftasks > 10