todo_app
This app on heroku: http://still-falls-8267.herokuapp.com/
Test user: user@gmail.com, 12345678
Answers for SQL requests:
- get all statuses, not repeating, alphabetically ordered
Select Distinct status from tasks ORDER BY status
2.get the count of all tasks in each project, order by tasks count descending
SELECT p.name,count(*) as 'tasks' From projects as p LEFT JOIN tasks as t ON p.project_id=t.project_id Group by p.project_id DESC
3.get the count of all tasks in each project, order by projects names
SELECT p.name,count(*) as 'tasks' From projects as p LEFT JOIN tasks as t ON p.project_id=t.project_id Group by p.project_id Order by p.name
4.get the tasks for all projects having the name beginning with “N” letter
SELECT p.name as 'project_name',t.name as 'task_name' From projects as p LEFT JOIN tasks as t ON p.project_id=t.project_id where p.name like 'N%'
5.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 p.project_id,p.name as 'project_name',t.name as 'task_name',count(t.task_id) From projects as p LEFT JOIN tasks as t ON p.project_id=t.project_id Group by p.name having p.name like '%a%' and t.name<>'NULL'
6.get the list of tasks with duplicate names. Order alphabetically
SELECT t.name, count(t.name) as "quantity" From tasks t Group By t.name having count(t.name)>1 Order by t.name
7.get the list of tasks having several exact matches of both name and status, from the project ‘Garage’. Order by matches count
SELECT * FROM projects p Left Join tasks t on p.project_id=t.project_id Group by t.name, t.status Having p.project_id=(Select project_id From projects where name='Garage') AND count(t.name)>1
8.get the list of project names having more than 10 tasks in status ‘completed’. Order by project_id
Select pr.name From projects pr right Join (select *,count(t.status) from tasks t group by t.project_id,t.status Having count(t.status)>9 and t.status='true' Order by t.project_id) tab On pr.project_id=tab.project_id