Heroku: https://garage-todo.herokuapp.com/ SQL task ..................................................................................
Given tables: ★ tasks (id, name, status, project_id) ★ projects (id, name)
- get all statuses, not repeating, alphabetically ordered
- SELECT DISTINCT status FROM TASKS ORDER BY status;
- get the count of all tasks in each project, order by tasks count descending
- SELECT projects.id, COUNT(tasks) AS amount FROM tasks RIGHT JOIN projects ON tasks.project_id = projects.id GROUP BY projects.id ORDER BY task_count DESC
- get the count of all tasks in each project, order by projects names
- SELECT projects.name, COUNT(*) AS amount FROM tasks INNER JOIN projects GROUP BY projects.name ORDER BY projects.name
- get the tasks for all projects having the name beginning with “N” lette
- SELECT * FROM tasks INNER JOIN projects ON (tasks.project_id = projects.id) WHERE projects.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 projects.name, COUNT(tasks.id) AS amount FROM projects RIGHT JOIN tasks ON projects.id = tasks.project_id WHERE projects.name LIKE '%a%' GROUP BY projects.name
- get the list of tasks with duplicate names. Order alphabetically
- SELECT name, COUNT() FROM tasks GROUP BY name HAVING COUNT() > 1 ;
- get the list of tasks having several exact matches of both name and status, from the project ‘Garage’. Order by matches count
- SELECT tasks.name FROM tasks RIGHT JOIN projects ON tasks.projects_id = projects.id WHERE projects.name = 'Garage' GROUP BY tasks.name, tasks.status HAVING COUNT(tasks) >1 ORDER BY COUNT(tasks) ;
- get the list of project names having more than 10 tasks in status ‘completed’. Orderby project_id
- SELECT projects.name, COUNT(tasks.id) as count FROM projects RIGHT JOIN tasks ON projects.id = tasks.project_id WHERE tasks.status = 'completed' GROUP BY projects.name HAVING count > 10 ORDER BY projects.id; Ivan Kudlatov