CRUD : Create as Insert, Read as Select, Update, Delete
Difference between Delete Truncate and Drop
If to remove every row then use truncate because its faster, resets identity but can’t be rolled back
“Avoid SELECT * in production — Why
“Avoid SELECT * in production — it transfers extra data and can hurt performance or rely on unpredictable schema order
Where vs Having
Where filters before aggregation. It can’t filter out groups
Having filters groups after Aggregation
Why group by not working
Columns in group by must in select or Aggregate like count sum avg
Inner Join
Returns rows that have matching values in both tables. Rest are excluded
Left join
All rows from left table and matched rows from right table. If no match in right table, those columns are null
Right join
All rows from right table and matched rows from left table. If no match then left columns are null
Full outer join
All rows from both tables. If no match then missing columns are null
Sub query
Query inside another query used to fetch Intermediate results. Dangerous because they may run for every outer row and make it slow
CTE
Common table expression is named temporary result set that can be read by query for readability and recursion. They dont improve performance
Aggregate
They perform calculations on a set of rows and give single value. They can’t be combined by non Aggregate select without group by
Distinct vs GroupBy
Distinct can be slow for large tables. Group By gives better performance
Exists vs In
Exists checks if a sub query returns any row. It stops at the first match.
In compares against a list of values for the column
Exists is better for large sub queries because in is slow if list is huge or contains nulls
Second highest salary
WITH SalaryRank AS (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM Employees
)
SELECT salary FROM SalaryRank WHERE rn = 2;
Delete duplicates
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM Users
)
DELETE FROM CTE WHERE rn > 1;
