UGC NET Computer Science & Applications (087)

Welcome to UGC NET Computer Science & Applications (087). We will study SQL: Where vs Having, Joins, Nulls and Query Optimisation, work through an example, and practise using the course's learning material. Paper I plus Computer Science & Applications Paper II preparation across algorithms, systems, databases, networks and AI. Sign in and use the study header to open your authorised course. Select this published lesson. Start Course Tutor with Auto audio on: it teaches continuously, answers a typed question, then resumes. Null requires particular care because it means unknown rather than zero or empty. Any arithmetic or comparison involving null yields unknown, so a row is returned only when a condition evaluates to true, and unknown behaves like false for the purpose of returning rows but not for negation. A query must list every department together with the number of its employees earning above a threshold, including departments with no such employee. A candidate writes an outer join from departments to employees, places the salary condition in the where clause, groups by department and counts. Why is the result wrong? Trace the logical evaluation order. The outer join executes first, producing every department, with null employee columns for departments having no employees at all. Next the where clause filters rows. The condition tests whether the salary exceeds the threshold. For the padded rows the salary is null, so the comparison evaluates to unknown, not true, and those rows are discarded. Departments with no qualifying employee therefore vanish. The outer join has been undone. The grouping and counting that follow operate on a set from which the very rows the question asked for have already been removed. The correction is to move the salary condition into the join condition, so that it restricts which employees are matched rather than which result rows survive. Departments with no matching employee then remain, carrying null employee columns. A second correction is needed in the count: counting all rows would return one for those departments, since the padded row exists, so the count must be taken over an employee column, because aggregate functions ignore nulls and will correctly return zero. Which mistake would change the reasoning in this example? The first mistake is placing a condition on the outer relation in the where clause, which converts an outer join to an inner one. The second is comparing a value to null with an equality operator instead of the is null predicate. Ask Course Tutor: please explain this step from the example. The condition tests whether the salary exceeds the threshold. Why does it follow? Add what you tried, so the explanation addresses your difficulty. Now try the source practice. Why can an aggregate function not appear in the where clause? Pause before the answer. The source answer is: Because the where clause is evaluated before grouping, at a point when individual rows have not yet been collected into groups, so there is no group over which an aggregate could be computed; the having clause exists precisely to filter after grouping. Explain the key idea in your own words, revisit any mistaken step, then continue to the next lesson. Use Stop to end Course Tutor when you finish.