- Pictorial Reference
- Inner join: Rows common in both T1 and T2
- Left outer join: All rows in T1
- Right outer join: All rows in T2
- Full join: All rows in T1 and T2
- Cross join: All rows in T1 * all rows in T2
- Primary key: Uniquely identify the row. Cannot be null.
- Candidate key: Can be chosen as primary key
- Composite key: Combination of multiple columns
- Foreign key: Column referencing other table's primary key
- Improves speed of data retrieval.
- Primary and foreign keys are indexed by default.
- Non-clustered index: Rows are unordered (stored in heap). While index is stored separately as sorted column.
- Clustered index: The rows themselves are ordered (thus there can be only 1 clustered index per table).
- Composite index: Index on multiple columns (c1,c2,c3). Order of columns should match the where clauses for maximum efficiency.
- Cardinality: Uniqueness of rows (eg: PassportID vs Gender column values).
- Bitmap-index: Used when cardinality is very low (eg: Gender column).
- Data structures used: Bit arrays, hashmaps or B+Trees (most common).
- Union: Merges content of 2 structurally compatible tables
- Group By: Used to aggregate (avg, sum, count) values. Aggregate column should be same as group-by column.
- Having: Adding where clauses on top of group-by.
- Find all departments with sales more than 1000
SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
- Print all employee ids with their manager ids
SELECT e1.emp_id, e1.emp_mgr_id
FROM employee e1 LEFT JOIN employee e2
ON e1.emp_mgr_id = e2.emp_id
- Print all manager names with count of directs
SELECT e2.ename, count(e1.ename)
FROM employee_s e1 LEFT OUTER JOIN employee_s e2
ON e1.manager_id = e2.eid
group by e2.ename;
- Print 10th highest salary
SELECT Salary FROM
( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10 )
AS Emp ORDER BY Salary LIMIT 1;
