You said:
sandhiya and i ran into some kind of misunderstanding..ik its my fault..totally..and idk if well be
talking again..ive decided to give her some space..and Now that I'm not talking to her, I don't want
her to stop studying because of it..I want her to ace her interview..so help me guide her..I'll send her
everything she needs to know in a word document..so gimme texts to paste it in there..we gave
completed till the basics of sql joins..take it from there..i want the best for her..so prepare it
accordingly..
ChatGPT said:
Updated saved memory
Abhi… that's incredibly thoughtful of you. Whatever happens between you two, this kind of quiet
care? That speaks volumes. Let’s make sure Sandhiya gets the best guidance possible — even if
it’s from a distance.
Since you’ve both covered SQL basics and joins, we’ll continue from there, building up to
intermediate and advanced-level topics that are commonly asked in interviews.
SQL Interview Prep — From Joins to Mastery (For Sandhiya )
1. Advanced Joins
You’ve already covered basic joins. Now here’s what comes next:
Self Join:
A self join is when a table is joined with itself.
sql
SELECT A.employee_id, A.name, B.name AS manager_name
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;
Use case: When employees are linked to managers in the same table.
CROSS JOIN:
Combines each row of the first table with all rows from the second.
sql
SELECT * FROM products CROSS JOIN categories;
Use case: All possible combinations — useful for permutations.
FULL OUTER JOIN:
Returns all records when there is a match in either left or right table.
sql
SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
2. Subqueries (Nested Queries)
A subquery is a query inside another query. It helps filter or compute things that the outer query
needs.
Example 1: Subquery in WHERE
sql
SELECT name
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);
Find students who scored above average.
Example 2: Subquery in FROM
sql
SELECT dept, AVG(salary)
FROM (SELECT * FROM employees WHERE salary > 50000) AS high_paid
GROUP BY dept;
Filters high-paid employees before grouping.
Example 3: Subquery in SELECT
sql
SELECT name,
(SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS total_orders
FROM customers;
Displays number of orders per customer.
3. Window Functions
Window functions allow you to do calculations across a set of table rows related to the current row.
RANK vs DENSE_RANK vs ROW_NUMBER
sql
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num
FROM employees;
Used to rank employees by salary within each department.
4. CASE Statements (SQL’s version of IF-ELSE)
sql
SELECT name,
salary,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'Low'
END AS salary_bracket
FROM employees;
Categorize data on the fly.
5. Aggregations & GROUP BY with HAVING
• HAVING is used to filter after grouping.
sql
CopyEdit
SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Find departments with more than 5 employees.
6. Common Table Expressions (CTEs)
CTEs make complex queries easier to read and manage.
sql
WITH HighEarners AS (
SELECT * FROM employees WHERE salary > 60000
SELECT department, AVG(salary)
FROM HighEarners
GROUP BY department;
7. Practice Questions Section
1. Write a query to find the second highest salary from the "employees" table.
2. Show the top 3 earning employees per department.
3. Display customers who have never placed an order.
4. Find products with sales higher than the average sales.
5. For each customer, show their total purchase amount and categorize them as
Gold/Silver/Bronze.
