Assignment 4 || SQL JOINS || SQL Tutorial || Practice

In this video , I have discussed Assignment 4 . This assignment consist 10 problem statement .
TablesData - drive.google.c...
Don't forget to follow and share our channel.
#sqlpracticeset #sqlquestions #altercommand #tablemodification #ascommand #tablebackupcommand #sqlimportantcommands #dsasimplified #sql #sqltutorial #sqlbasics #sqladvance #dbms #structuredquerylanguage #dsasimplifiedandtech #sqlbasictoadvance
#datatypeinsql #databaseoperation #sqlwork

[@ankitpurshottam3442]

1) select e.employee_id , e.first_name || ' ' || e.last_name as full_name ,e.email ,d.department_name
from employees as e
left join departments as d on e.department_id = d.department_id
-----------------------------------------------------------------------------------------------------
2) select e.employee_id , e.first_name || ' ' || e.last_name as full_name ,e.email ,j.job_title
from employees as e
left join jobs as j on e.job_id = j.job_id
-----------------------------------------------------------------------------------------------------
3) select e.first_name,
e.employee_id , o.order_name,
count(o.order_id) as "total orders"
from employees as e
left join orders as o ON e.employee_id = o.employee_id
group by e.employee_id,e.first_name,o.order_name
order by e.first_name
-----------------------------------------------------------------------------------------------------
4) select e.*,count(o.order_id)
from employees as e
left join orders as o ON e.employee_id = o.employee_id
group by e.employee_id
HAVING COUNT(o.order_id)>0;
select e.*,count(o.order_id)
from employees as e
left join orders as o on e.employee_id = o.employee_id
group by e.employee_id,o.order_id
having count(o.order_id)>1
select e.first_name,
e.employee_id , o.order_name,
count(o.order_id) as "total orders"
from employees as e
left join orders as o ON e.employee_id = o.employee_id
group by e.employee_id,e.first_name,o.order_name
order by e.first_name
-------------------------------------------------------------------------------------------------------
5) select d.department_name,count(e.employee_id) as "total employees"
from departments as d
left join employees as e ON d.department_id = e.department_id
group by d.department_name
--------------------------------------------------------------------------------------------------------
6) SELECT e.employee_id, e.first_name, COUNT(o.order_id) AS order_count
FROM employees AS e
LEFT JOIN orders AS o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name
HAVING COUNT(o.order_id) > 1;
------------------------------------------------------------------------------------------------
7) select e.first_name,d.department_name,j.job_title
from employees as e
left join departments as d on e.department_id = d.department_id
left join jobs as j on e.job_id = j.job_id
where
(d.department_name = 'IT' and j.job_title = 'Software Developer')
or
(d.department_name = 'Sales' and j.job_title = 'Sales Executive')
-------------------------------------------------------------------------------------------------
8) SELECT e.employee_id, e.first_name, COUNT(o.order_id) AS order_count
FROM employees AS e
LEFT JOIN orders AS o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name
HAVING COUNT(o.order_id) > 1;
---------------------------------------------------------------------------------------------------------
9) SELECT e.first_name,
SUM(o.order_price * o.quantity) AS "total price"
FROM employees AS e
LEFT JOIN orders AS o ON e.employee_id = o.employee_id
GROUP BY e.first_name;
select e.first_name,sum(order_price * quantity) as "total price"
from employees as e
left join orders as o ON e.employee_id = o.employee_id
group by e.first_name
having e.first_name ilike 'linda'
select e.*,o.*
from employees as e
left join orders as o ON e.employee_id = o.employee_id
where e.first_name ilike 'linda'

[@dsatechsimplified]

Great Job Ankit .
Few updates from my side
1 ) e.first_name || ' ' || e.last_name -> Here || does not work in MYSQL . It works properly in PostgreSQL , Oracle , SQLite . To implement this in MYSQL , use CONCAT() function .
2) In ques4 , we only have to find the employees who placed an order not their orders count . You can use here "INNER JOIN " . But you did a great job by solving it in various complex ways.
Unexpected but you did solve question 9. I am truly impressed.
Keep learning and practice more questions in SQL.