DBMS Review

COUNT(*)

Counts all rows, including rows with NULL values.

COUNT(column_name)

Counts only rows where the column is NOT NULL.

COUNT(DISTINCT column)

Counts unique non-null values in a column.

SUM(column)

Adds all numeric values in a column; ignores NULL.

AVG(column)

Calculates average of non-null values.

MIN(column)

Returns the smallest non-null value.

MAX(column)

Returns the largest non-null value.

SUM(isActive) (boolean column)

Counts TRUE values because TRUE=1, FALSE=0.

COUNT(isActive) (boolean column)

Counts TRUE + FALSE values but excludes NULL.

How to count only active users (TRUE)

SELECT COUNT(*) FROM Users WHERE isActive = TRUE;

How to count only inactive users (FALSE)

SELECT COUNT(*) FROM Users WHERE isActive = FALSE;

What does AS active_count do?

Creates an alias and renames the output column to "active_count".

What is an alias in SQL?

A temporary name given to a column or table using AS.

What does DISTINCT do in SQL?

Returns unique values from a column.

What does GROUP BY do?

Groups rows by shared values so aggregates can be applied.

What does HAVING do?

Filters groups after aggregation.

What does WHERE do?

Filters rows before grouping or aggregation.

Primary Key (PK)

A unique identifier for each row; cannot be NULL.

Foreign Key (FK)

A column that references a primary key in another table.

Candidate Key

A field or combination of fields that can uniquely identify a row.

Composite Key

A key made of two or more columns.

1NF Definition

No repeating groups; all values are atomic.

2NF Definition

In 1NF and no partial dependencies.

3NF Definition

In 2NF and no transitive dependencies.

BCNF Definition

Every determinant must be a candidate key.

Partial Dependency

A non-key attribute depends on only part of a composite key.

Transitive Dependency

A non-key attribute depends on another non-key attribute.

INNER JOIN meaning

Returns rows with matching values in both tables.

LEFT JOIN meaning

Returns all rows from the left table and matching rows from the right.

RIGHT JOIN meaning

Returns all rows from the right table and matching rows from the left.

FULL OUTER JOIN meaning

Returns all rows from both tables, matched or unmatched.

JOIN scenario: Employees with Managers

SELECT e.name, m.name FROM Employees e LEFT JOIN Employees m ON e.managerid = m.empid;

JOIN scenario: List customers who placed orders

SELECT c.name, o.orderid FROM Customers c JOIN Orders o ON c.customerid = o.customer_id;

JOIN scenario: Students with their Courses

SELECT s.name, c.coursename FROM Students s JOIN Enrollments e ON s.id = e.studentid JOIN Courses c ON e.course_id = c.id;

JOIN scenario: Departments with avg salary

SELECT d.name, AVG(e.salary) FROM Departments d JOIN Employees e ON e.deptid = d.deptid GROUP BY d.name;

JOIN scenario: Products with No Orders

SELECT p.productname FROM Products p LEFT JOIN Orders o ON p.id = o.productid WHERE o.product_id IS NULL;

Subquery: Employees earning above department average

SELECT e.name, e.salary FROM Employees e JOIN (SELECT deptid, AVG(salary) AS avgsalary FROM Employees GROUP BY deptid) x ON e.deptid = x.deptid WHERE e.salary > x.avgsalary;

Query: Customers with more than 5 orders

SELECT customerid FROM Orders GROUP BY customerid HAVING COUNT(*) > 5;

Window function: Top 3 salaries per department

SELECT name, deptid, salary FROM (SELECT name, deptid, salary, ROWNUMBER() OVER (PARTITION BY deptid ORDER BY salary DESC) AS rn FROM Employees) x WHERE rn <= 3;