13 - Subqueries in MySQL

 13 - Subqueries in MySQL

https://youtu.be/Vj6RqA_X-IE?si=8hSBKjhytr_zGOD5

TOTAL: 11

--  Subqueries

-- So subqueries are queries within queries. Let's see how this looks.

SELECT *

FROM employee_demographics;

-- Now let's say we wanted to look at employees who actually work in the Parks and Rec Department, we could join tables together or we could use a subquery

-- We can do that like this:

SELECT *

FROM employee_demographics

WHERE employee_id IN

            (SELECT employee_id

                FROM employee_salary

                WHERE dept_id = 1);

               

-- So we are using that subquery in the where statement and if we just highlight the subquery and run it it's basically a list we are selecting from in the outer query

SELECT *

FROM employee_demographics

WHERE employee_id IN

            (SELECT employee_id, salary

                FROM employee_salary

                WHERE dept_id = 1);

--  now if we try to have more than 1 column in the subquery we get an error saying the operand should contain 1 column only

-- We can also use subqueries in the select and the from statements - let's see how we can do this

-- Let's say we want to look at the salaries and compare them to the average salary

SELECT first_name, salary, AVG(salary)

FROM employee_salary;

-- if we run this it's not going to work, we are using columns with an aggregate function so we need to use group by

-- if we do that though we don't exactly get what we want

SELECT first_name, salary, AVG(salary)

FROM employee_salary

GROUP BY first_name, salary;

-- it's giving us the average PER GROUP which we don't want

-- here's a good use for a subquery

-- cau subquery nay thay the cho cau GROUP BY o tren -> ket qua tuong tu nhau

SELECT first_name,

salary,

(SELECT AVG(salary)

    FROM employee_salary)

FROM employee_salary;

-- We can also use it in the FROM Statement

-- when we use it here it's almost like we are creating a small table we are querying off of

SELECT *

FROM (SELECT gender, MIN(age), MAX(age), COUNT(age),AVG(age)

FROM employee_demographics

GROUP BY gender)

;

-- now this doesn't work because we get an error saying we have to name it

SELECT gender, AVG(Min_age)

FROM (SELECT gender, MIN(age) Min_age, MAX(age) Max_age, COUNT(age) Count_age ,AVG(age) Avg_age

FROM employee_demographics

GROUP BY gender) AS Agg_Table

GROUP BY gender

;