SQL Tricks Sub Query & CTE

SQL Tricks Sub Query & CTE.sql

https://console.clever-cloud.com/users/me/addons/addon_1315c43c-935c-4889-b9ab-1c64f93bcf61
Database@123

SQL execution:
FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY


1. WITH also known as a Common Table Expression (CTE):
> powerful tool for simplifying complex SQL queries and improving query readability.
WITH cte_name (column1, column2, ...) AS (
  -- CTE query definition here--
)
2. you cannot call `WITH` statement in `WHERE`

Eg:

WITH RecursiveManagerCTE AS (
  SELECT employee_id, first_name, last_name, manager_id, salary
  FROM employees
  WHERE manager_id IS NULL -- Find top-level managers
  UNION ALL
  SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, e.salary
  FROM employees e
  JOIN RecursiveManagerCTE r ON e.manager_id = r.employee_id
)
 
SELECT manager_id, SUM(salary) AS total_salary_cost
FROM RecursiveManagerCTE
GROUP BY manager_id;

-------------------------------------------------------------------------------------------------------------------

Union and Union ALL

UNION: This keyword combines the results of the first SELECT query with the results of the second SELECT query WITH duplicate rows
UNION ALL: Includes all rows from both tables, including duplicates.
------------------------------------------------------------------------------------------------------------------
GROUP BY
1. The best thing of `GROUP BY` is it maintains the initial schema of table and add new aggregated results/columns on top of it.


Q1. Find most expensive dish::::::::::::::::::::::::::::::::::::::::::::::
> SELECT types, max(price), name FROM `dishes`
  GROUP BY types;
Now possiblity is there more than 1 names could have that max price, but the query above
return only the 1st matched name, how to get all the dishes who have max(price) like rice also 1k and daal also 1k

Correct Ans:
WITH sub AS (
    SELECT department, max(sal)
    FROM `dishes`
    GROUP BY types
)

SELECT * FROM `dishes`
WHERE (department, sal) IN sub;

Q2. Find all the duplicate emails:::::::::::::::::::::::::::::::::::::::::::
SELECT email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1

Q3. 
-------------------------------------------------------------------------------------------------------------------

SubQuery (OR Inner Query):
==> can be use in `SELECT`, `WHERE`, `FROM`, `HAVING` and `JOIN`
==> There is no effeect of using `ORDER BY` inside sub query.
==> keywords that can be used with subquery `EXISTS`, `NOT EXISTS`, `ANY` OR `ALL`

types:
1. scaler subquery --> which return the single value
eg:::::::::::::::::::
> https://youtu.be/nJIEIzF7tDw
find the employee whos salary more than the average salary?
1 way:
SELECT * FROM employee
where salary > (select avg(salary) from employee);

2 way: (with this we can also print the average salary)
SELECT * 
FROM `employee` AS e
JOIN (SELECT AVG(salary) as avg_sal from dishes) as sub
on e.salary > sub.avg_sal;

2. Multiple Row SubQuery................................................
1. Single col
2. Multiple col

eg:::::::::::::::::::
1.
Find the Employee(s) who earn the highest salary in each department.
WITH sub AS (
    SELECT department, max(sal)
    FROM `dishes`
    GROUP BY types
)

SELECT * FROM `emp`
WHERE (department, sal) IN sub;

2.
find the department which dont have any employess

WITH sub AS (
    SELECT DISTINCT dept_name
    FROM `emp`
)

SELECT dept_name
FROM department
WHERE dept_name NOT IN sub;


3. Correlated SubQuery.............................................................................................
subquery who has reference to outer query

SELECT *
FROM Suppliers AS s
WHERE EXISTS (
	SELECT *
  FROM Products as p
  WHERE p.id = s.id AND Price IN (18, 5)
);

NOW the above is coreallted as it has in-out relation, now twist is as the condition fullfilled, 
**Exists return ids to where**


find the department which dont have any employess using  Correlated Sq:







----------------------------------------------------------------------------------------------------------------------------------
Data stored in differenrt tables and these tables are associated with some kind of key/references.

Join is linking the data based on some kind of relationship




Mistake in Interview:

1. you cannot put where at the end:
2. for `BETWEEN` start and end are included
3. FILTER date of year 2020 -> where time_stamp like '2020%'
4. WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 2;
5. `LENGTH()` tells you the no. of character (including white space), also it convert the number to char then count the character
6. SELECT IF(employee_id%2 != 0 AND name LIKE "M%", salary*2, 0)


SELECT product_id, SUM(unit) as total_unit
FROM `Orders`
WHERE order_date BETWEEN "2020-01-30" AND "2020-03-01"
GROUP BY product_id
HAVING total_unit > 100

Q2. > https://leetcode.com/problems/bank-account-summary-ii/
Q3. > https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/
Q4. > 


Important:
>> LIMIT 1 OFFSET 0 >> GIVEST YOU THE 1 AND 1ST row.

https://leetcode.com/problems/percentage-of-users-attended-a-contest/description/