SQL, Databases & Pandas - Evaluation (Attempt 2)

PARTITION BY student_name confines the LAG calculation to each student's own rows, so the previous-month lookup never crosses into another student's data. Omitting PARTITION BY would let the previous row belong to a completely different student, an AVG window computes an average rather than a prior-month lag value, and a plain GROUP BY would collapse the monthly rows instead of preserving row-by-row comparisons.

DENSE_RANK assigns consecutive ranks without skipping any after a tie: 88 gets rank 1, both students scoring 85 tie for rank 2, and the next distinct score (82) gets rank 3 -- not rank 4, which is what RANK would assign because it skips a rank after a tie.

LAG looks backward in the time sequence and pulls in the value from the previous row, which is exactly what is needed to compare this month against last month. LEAD instead looks forward to a future row, RANK only assigns an ordering position rather than retrieving a sales value, and GROUP BY would collapse the monthly rows entirely rather than aligning a prior month's value alongside the current one.

The OVER clause with PARTITION BY computes the aggregate per group while appending it as a new column, keeping every original employee row intact. GROUP BY, by contrast, collapses rows into one per department, so it cannot preserve individual employee rows; SELECT DISTINCT only removes duplicate salary values and does not compute an average; sorting by salary does not compute or attach any departmental average.

The ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING window correctly captures the current day plus the two days on either side, and moving averages exist precisely to smooth out sharp fluctuations and reveal the underlying trend. This clause does require an ORDER BY inside OVER() to define the row sequence PRECEDING/FOLLOWING refers to, and it does not require any prior GROUP BY aggregation to be applied.

Using PARTITION BY batch correctly restarts the ranking for each batch, and this works whether RANK() (which skips ranks after ties) or DENSE_RANK() (which does not skip ranks) is used. Leaving out PARTITION BY entirely ranks across the whole dataset rather than per batch, and swapping the roles of batch and score in the partition/order clauses ranks by batch within each score value instead of the intended batch-specific score ranking.

A LEFT JOIN keeps every row from the left table (Students); when there is no matching row in the right table (Marks), the right table's columns are filled with NULL instead of being dropped or replaced with a numeric placeholder like zero, and no other student's data is ever substituted in.

This is the anti-join pattern: a LEFT JOIN keeps every show, and shows with no matching watch_history row will have NULL in the joined watch_history columns, so filtering for those NULLs isolates never-watched shows. An INNER JOIN would only return shows that already have at least one watch record, a CROSS JOIN produces every possible show-watch combination without filtering for absence, and combining the two tables with UNION ALL does not test for a missing relationship at all.

UNION combines rows from both tables and removes duplicates, producing exactly the distinct set of users who logged in on either day. UNION ALL would keep the overlapping users twice, INTERSECT would return only the users common to both days, and EXCEPT would return only users present in one table but missing from the other.

A CROSS JOIN returns the Cartesian product of both tables, so its row count is naturally the number of types multiplied by the number of sizes, and because that output can grow very large it should be used deliberately rather than by default. Omitting the ON condition from an INNER JOIN is not the standard way to build a Cartesian product -- CROSS JOIN is the construct built for exactly this purpose.

Set operations require matching column count, identical column names in the same order, and compatible data types; here the order differs (student_id first vs name first) and the third columns differ in both name and likely data type, so simply matching the column count is not enough -- the tables must be aligned first. It is not enough to just have the same number of columns for the operation to be guaranteed correct.

In SQLite (as in MySQL, PostgreSQL, and SQL Server), NULL values are treated as the smallest possible value in ascending order, so rows with a NULL age sort to the very beginning rather than the end -- they are neither scattered randomly nor dropped from the output. (Note: not every SQL engine agrees -- Oracle sorts NULLs last in ascending order by default.)

DISTINCT removes duplicate values so each unique city appears only once. BETWEEN filters values within a numeric range, LIMIT restricts the number of rows returned (without removing duplicates), and ORDER BY only changes the sort order.

SQL's execution order runs FROM and WHERE before SELECT, so any alias defined in SELECT (like total_cost) does not exist yet when WHERE is evaluated -- the fix is to repeat the full expression directly inside WHERE. SQL does support computed columns via expressions in SELECT, AS is valid alias syntax, and WHERE can filter on any expression, not only primary key columns.

The first CTE aggregates sales by region, the second CTE filters that aggregated result down to regions above 100000, and the final SELECT orders those filtered region names by total sales descending -- returning multiple qualifying regions, not raw unaggregated rows, not a syntax error (chaining CTEs where one builds on another is valid), and not only a single top region.

Chaining CTEs lets each sequential step (filtering, then aggregating, then ranking) be written as its own named, readable stage that the next stage builds on, which is exactly the multi-step maintainability CTEs are used for. A single WHERE clause cannot perform aggregation or ranking, a self CROSS JOIN produces a Cartesian product unrelated to sequential filtering and ranking, and a trailing DISTINCT only removes duplicate rows without organizing any of the three steps.

Splitting a single overloaded table into separate logical tables linked by primary and foreign keys stores each phone number and each price exactly once, eliminating both the redundant repeated writes that cause update anomalies and the risk of the same fact being recorded inconsistently. A notes column only documents the problem without preventing it, a duplicate spreadsheet doubles the redundancy instead of removing it, and sorting only makes an existing inconsistency easier to notice, not impossible to create.

First Normal Form requires every cell to hold exactly one atomic value; a comma-separated list violates this, and the fix is to break the list into separate rows. Removing the column loses information rather than fixing the structure, adding a foreign key alone does not make the existing cell atomic, and merging the column into the primary key does not address non-atomic values at all.

When a non-key column depends on only part of a composite primary key rather than the whole key, that is a partial dependency and a Second Normal Form violation, fixed by moving each such column into a table keyed by the specific part of the composite key it depends on. There is no non-atomic value here to trigger a First Normal Form issue, no reference between two unrelated attributes to enforce via a foreign key, and composite keys do not automatically guarantee any normal form.

A non-key column depending on only part of a composite primary key (here, Course_ID, which is part of the Student_ID + Course_ID composite key) rather than the whole key is a partial dependency and a Second Normal Form violation -- not a transitive dependency, which requires a non-key column to depend on another non-key column instead of on part of the key. The fix is to move Course_Name and Department_Head_Name into their own Courses table keyed by Course_ID. Having a defined primary key alone does not guarantee 2NF compliance, and separating dependent data into its own table reduces redundancy rather than increasing it.

NOT NULL ensures a column can never have a missing value, which is exactly the guarantee needed for a balance column. UNIQUE only prevents duplicate values, DEFAULT only supplies a fallback when no value is given (it doesn't block an explicit NULL), and FOREIGN KEY only links a column to another table's primary key.

A Primary Key is a column (or set of columns) whose value uniquely identifies each record in a table, similar to how an Aadhaar or passport number uniquely identifies a person. It is not about storing large text, it cannot hold duplicate values, and it is not a mechanism for linking separate, unrelated databases.

INTEGER stores whole numbers, DECIMAL stores exact numeric values with decimals, and BIGINT stores integers larger than what a standard integer type can hold -- all three are numeric types. VARCHAR is a non-numeric, variable-length string type used for text, not numbers.

Referential integrity requires foreign keys to match an existing primary key, entity integrity requires a non-null primary key on every table, and domain integrity restricts each column to its appropriate data type and format. Allowing a foreign key to point to an already-deleted row is precisely what referential integrity is designed to prevent, not something domain integrity permits.

transform() creates a new column without reducing the row count, so all 500 rows remain, and each row's new column holds the average salary of the department that row belongs to, not a single collapsed table, not an overall dataset-wide average, and not an expanded cross-product of departments and employees.

Combining multiple row conditions in pandas requires the & operator with each condition wrapped in parentheses, not Python's and keyword, because and is not defined for element-wise comparison across an entire Series and raises an error rather than silently succeeding, returning everything, or returning only a column.

A left merge keeps every row from the left DataFrame (orders here) and fills in NaN wherever no matching customer_id exists in customers. Keeping only matching rows describes an inner merge, preserving every row of the right table instead describes a right merge, and keeping every row from both regardless of matches describes an outer merge.

pd.concat() stacks rows from both DataFrames and behaves like UNION ALL by default (keeping duplicates), calling drop_duplicates() afterward removes those duplicates to reproduce UNION, and isin() identifies elements common to both DataFrames the way INTERSECT does. The EXCEPT-style comparison (rows present in one table but not the other) is instead achieved with filtering logic, not with pd.merge().

np.select() evaluates a list of conditions against a matching list of values and applies the first one that is True, and it supports a default value for rows matching none of them -- exactly what handling the 'Junior' fallback needs. It is designed to handle more than two conditions in one call (that is its whole purpose versus the simpler, binary-condition np.where()), so it is not limited to only two conditions.

str.startswith('B') checks for cities beginning with 'B', str.contains('pur', case=False) finds cities containing 'pur' case-insensitively, and str.lower() is a valid way to normalize case before comparing strings. isna() checks for missing values and has nothing to do with detecting a starting letter.