sql_task

Given tables:
★ tasks (id, name, status, project_id)
★ projects (id, name)

Write the queries for:

1. 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 project_id, COUNT(id) AS Tasks
FROM tasks 
GROUP BY project_id 
ORDER BY Tasks DESC;
_______________________________________________________________________________________________________________________

3. get the count of all tasks in each project, order by projects names

SELECT projects.name AS Project, COUNT(tasks.id) AS Tasks
FROM (projects INNER JOIN tasks ON projects.id = tasks.project_id)
GROUP BY Project
ORDER BY Project;
_______________________________________________________________________________________________________________________

4. get the tasks for all projects having the name beginning with “N” letter

SELECT projects.name, tasks.name AS TaskName 
FROM (projects INNER JOIN tasks ON projects.id = tasks.project_id)
WHERE TaskName 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 projects.name AS Project, COUNT(tasks.id) AS Tasks
FROM (projects INNER JOIN tasks ON projects.id = tasks.project_id)
WHERE substr( Project,2, (length( Project) - 2)) LIKE '%p%'
AND tasks.project_id IS NOT NULL 
GROUP BY Project
HAVING Tasks > 0;
_______________________________________________________________________________________________________________________

6. get the list of tasks with duplicate names. Order alphabetically

SELECT name FROM tasks
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY 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 tasks.name
FROM (projects INNER JOIN tasks ON projects.id = tasks.project_id)
WHERE projects.name = 'Garage'
GROUP BY tasks.name, tasks.status
HAVING COUNT(*) > 1
ORDER BY count(tasks.status) DESC

_______________________________________________________________________________________________________________________

8. get the list of project names having more than 10 tasks in status ‘completed’. Order
by project_id

SELECT projects.name
FROM (projects INNER JOIN tasks ON projects.id = tasks.project_id)
GROUP BY projects.id
HAVING COUNT(tasks.status = 'complete') > 10
ORDER BY projects.id