SQL Join Exercise

 CREATE TABLE freelancer (

id INT PRIMARY KEY,

first_name VARCHAR(120),

last_name VARCHAR(120)

);

INSERT INTO freelancer (id, first_name, last_name) VALUES (1, 'Bob', 'Franklin');

INSERT INTO freelancer (id, first_name, last_name) VALUES (2, 'Dionne', 'Ravanelli');

INSERT INTO freelancer (id, first_name, last_name) VALUES (3, 'Marek', 'Lewandowski');

Insert Into Freelancer (Id, First_Name, Last_Name) Values (4, 'Francois', 'Cousteau');

INSERT INTO freelancer (id, first_name, last_name) VALUES (5, 'Emma', 'Biesa');

Select * From Freelancer;

CREATE TABLE task_category (

id INT PRIMARY KEY,

task_type VARCHAR(120),

task_subtype VARCHAR(120)

);

INSERT INTO task_category (id, task_type, task_subtype) VALUES (1, 'Blog article', 'SQL');

INSERT INTO task_category (id, task_type, task_subtype) VALUES (2, 'Blog article', 'Python');

INSERT INTO task_category (id, task_type, task_subtype) VALUES (3, 'Blog article', 'Career');

Insert Into Task_Category (Id, Task_Type, Task_Subtype) Values (4, 'Social media post', 'LinkedIn');

INSERT INTO task_category (id, task_type, task_subtype) VALUES (5, 'Social media post', 'Other social media');

select * from Task_Category;

CREATE TABLE task (

id INT PRIMARY KEY,

task_category_id INT,

title VARCHAR (120),

freelancer_id INT,

date_assigned DATE,

due_date DATE,

completed_date DATE

);

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (1, 2, 'Working With Pandas in Python', 5, TO_DATE('2023-11-30', 'YYYY-MM-DD'), TO_DATE('2023-12-15', 'YYYY-MM-DD'), TO_DATE('2023-12-15', 'YYYY-MM-DD'));

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (2, 4, 'Promote Advanced SQL Learning Track', 4, TO_DATE('2023-12-18', 'YYYY-MM-DD'), TO_DATE('2023-12-20', 'YYYY-MM-DD'), TO_DATE('2023-12-20', 'YYYY-MM-DD'));

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (3, 1, 'Working With LEFT JOIN in SQL', 1, TO_DATE('2023-12-08', 'YYYY-MM-DD'), TO_DATE('2024-03-01', 'YYYY-MM-DD'), NULL);

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (4, 3, 'What Does Data Analyst Do?', 2, TO_DATE('2023-12-20', 'YYYY-MM-DD'), TO_DATE('2024-02-01', 'YYYY-MM-DD'), TO_DATE('2024-02-10', 'YYYY-MM-DD'));

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (5, 4, 'Promote Working With Pandas in Python', 4, TO_DATE('2024-01-15', 'YYYY-MM-DD'), TO_DATE('2024-01-18', 'YYYY-MM-DD'), TO_DATE('2024-01-18', 'YYYY-MM-DD'));

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (6, 2, 'Python Libraries You Should Know', 1, TO_DATE('2024-01-15', 'YYYY-MM-DD'), TO_DATE('2024-02-15', 'YYYY-MM-DD'), TO_DATE('2024-02-15', 'YYYY-MM-DD'));

Insert Into Task (Id, Task_Category_Id, Title, Freelancer_Id, Date_Assigned, Due_Date, Completed_Date) Values (7, 1, 'Using COUNT in SQL', 2, To_Date('2024-01-20', 'YYYY-MM-DD'), To_Date('2024-02-15', 'YYYY-MM-DD'), To_Date('2024-02-15', 'YYYY-MM-DD'));

INSERT INTO task (id, task_category_ID, title, freelancer_id, date_assigned, due_date, completed_date) VALUES (8, 1, 'Filtering Data in SQL', 5, TO_DATE('2024-02-20', 'YYYY-MM-DD'), NULL, NULL);

select * from task;

Select * From Freelancer;

Alter Table Freelancer

rename column fid to freelancer_id;

Select * From Task;

Alter Table Task

rename column tid to task_id;

Select * From Task_Category;

Alter Table Task_Category

rename column id to task_category_id;

/*

Question 1: Select Freelancers and Their Task Info.

Select

First_Name, Last_Name, Title, Due_Date, Task_Type, Task_Subtype

and

Include freelancers that don’t have any tasks assigned.

*/

Select * From Task;

Select

F.First_Name, F.Last_Name,

T.Title, T.Due_Date,

Tc.Task_Type,Tc.Task_Subtype

From Freelancer F Join Task T

On F.Freelancer_Id = T.Freelancer_Id Join Task_Category Tc

On T.Task_Category_Id = Tc.Task_Category_Id;

/*

Question 2: Select Freelancer and Task Info

select

Freelancers’ first and last names.

The titles of their assigned tasks.

Task type and subtype.

Task due dates.

Include all freelancers, even those that don’t have any tasks.

*/

Select F.First_Name,F.Last_Name, T.Title, T.Due_Date From Freelancer F Left Join Task T

On F.Freelancer_Id = T.Freelancer_Id left Join Task_Category Tc

On T.Task_Category_Id = Tc.Task_Category_Id;

/*

Question 3: Select Freelancer Info for Projects Due in 2024

All freelancers

Their task titles

Tasks’ due dates

Include only projects with a due date in 2024.

*/

Select

F.First_Name,

F.Last_Name,

T.Task_Id,

T.Title,

T.Due_Date

From Freelancer F Left Join Task T

On F.Freelancer_Id = T.Freelancer_Id and t.due_date >= '01-01-2024';