Relational Algebra
What does σ do?
Filters rows
Which keyword is used to filter rows?
WHERE
Which join returns only matching rows?
INNER JOIN
Which function counts rows?
COUNT()
What does a rectangle represent?
Entity
What does this return? σ gpa > 3.0 (Students)
Students with GPA > 3.0
What does this return? SELECT COUNT(*) FROM Students WHERE gpa > 3.5;
Number of students with GPA > 3.5
What happens if JOIN has no condition?
Cartesian product
When do we use HAVING?
To filter grouped data
What is weak entity?
Entity that depends on another entity
Which relational algebra operation returns only the tuples that are present in both relations and requires them to be union-compatible?
Intersection (∩)
Which SQL clause is evaluated after GROUP BY and is used to filter aggregated results?
HAVING
Which JOIN can produce NULL values in columns of one table when there is no matching row in the other table?
OUTER JOIN
Which SQL function would you use to count only non-NULL values in a specific column?
COUNT(column_name)
What type of relationship occurs when one entity instance can be associated with multiple instances of another entity, but not vice versa?
One-to-Many (1:N)
How can pushing projection (π) before selection (σ) change the result?
If σ uses attributes removed by π, it loses information and gives incorrect result.
Why can you not use aggregate functions like SUM() or AVG() in the WHERE clause?
Because WHERE filters rows before aggregation, and aggregates are calculated after grouping.
What happens to the result of a LEFT JOIN if a condition on the right table is placed in the WHERE clause instead of the ON clause?
Rows from the left table with NULLs in the right table are filtered out, effectively turning the LEFT JOIN into an INNER JOIN.
Explain the difference between this two queries:1)SELECT dept, AVG(gpa) FROM Students GROUP BY dept HAVING AVG(gpa) > 3.5; 2)SELECT dept, AVG(gpa) FROM Students WHERE gpa > 3.5 GROUP BY dept;
First query averages all GPAs per dept and keeps only depts with AVG > 3.5; second query averages only GPAs > 3.5, may include departments with average < 3.5.
How would you model students who can have zero or multiple advisors, and advisors can mentor multiple students?
Many-to-many relationship using junction table(also called bridge table) StudentAdvisor(student_id FK, advisor_id FK); optionality allows zero advisors.
How do you express an Intersection (∩\cap∩) using only the Difference (-) operator?
R∩S=R−(R−S).
What is the fundamental difference between Projection (π) in relational algebra and a standard SELECT in SQL?
In relational algebra, projection removes duplicates automatically; in SQL, duplicates remain unless DISTINCT is used.
How does a FULL OUTER JOIN differ from combining LEFT JOIN and RIGHT JOIN with UNION?
Both produce all rows from both tables, but FULL OUTER JOIN handles duplicates and NULLs automatically; LEFT+RIGHT JOIN requires careful deduplication to avoid double-counting common rows.
How would you find students enrolled in all courses using aggregation?
By using aggregation with GROUP BY and HAVING to compare the number of courses a student is enrolled in to the total number of courses
How can you model optional relationships in ERD?
Use 0..1 cardinality on the optional side.