Task Manager

Task have been created by all the requirements. (almoust)

Installing

1. Choose the right directory and clone the repo:

git clone https://github.com/StanislavJPG/task_manager.git

2. Create venv

py -m venv env

3. Activate venv

env\Scripts\activate

The same venv creation on Unix/Linux/macOS

Create venv

python3 -m venv env

Activate venv

source env/bin/activate

4. Choose the right manage.py project directory:

cd task_manager

5. Install the project dependencies with:

pip install -r requirements.txt

6. Create your own .env file in task_manager directory to store your secret variables:

Secret key example: wwf*2#86t64!fgh6yav$aoeuo@u2o@fy&*gg76q!&%6x_wbduad

SECRET_KEY=...

7. Make migrations:

python manage.py migrate

Running

1. Run your server locally:

python manage.py runserver

2. Open your project locally:

http://127.0.0.1:8000

And here you go!

SQL

Tasks

1. get all statuses, not repeating, alphabetically ordered

SELECT DISTINCT 
    status 
FROM 
    tasks
ORDER BY 
    status ASC

2. get the count of all tasks in each project, order by tasks count descending

SELECT 
    p.id AS project_id, 
    p.name AS project_name, 
    COUNT(t.id) AS task_count
FROM 
    projects p
JOIN 
    tasks t ON p.id = t.project_id
GROUP BY 
    p.id, p.title
ORDER BY 
    task_count DESC

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

SELECT 
    p.id AS project_id, 
    p.name AS project_name, 
    COUNT(t.id) AS task_count
FROM 
    projects p
JOIN 
    tasks t ON p.id = t.project_id
GROUP BY 
    p.id, p.title
ORDER BY 
    project_name

4. get the tasks for al projects having the name beginning with "N" letter

SELECT 
    *
FROM 
    tasks t
WHERE 
    t.name LIKE 'N%'

5. get the list of al 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.name,
    COUNT(t.id) as task_count
FROM
    projects p
LEFT JOIN
    tasks t
ON p.id = t.project_id
WHERE 
    p.name LIKE '%a%'
GROUP BY p.id, p.title

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

SELECT
    t.name
FROM
    tasks t
GROUP BY 
    t.name
HAVING 
    COUNT(t.name) > 1
ORDER BY 
    t.name ASC

7. get the list of tasks having several exact matches of both name and status, from the project 'Delivery’. Order by matches count

SELECT
    t.name,
    t.status
FROM
    tasks t
INNER JOIN
    projects p
ON t.project_id = p.id
WHERE
    p.name = 'Delivery'
GROUP BY
    t.name, t.status
HAVING
    COUNT(t.status) > 1 AND COUNT(t.name) > 1
ORDER BY
    COUNT(t.name) DESC

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

SELECT DISTINCT
    t.project_id,
    p.name
FROM
    projects p
INNER JOIN
    tasks t
ON p.id = t.project_id
WHERE 
    t.status = 'completed'
GROUP BY 
    t.project_id, p.name
HAVING
    COUNT(t.id) > 10
ORDER BY 
    t.project_id