SQL, Data Modeling, and PL/SQL Quiz

PL/SQL automatically raises NO_DATA_FOUND exception when a SELECT INTO statement returns no rows. This is a predefined exception that triggers when a single-row query fails to find any matching data. Unlike explicit error checking, this built-in mechanism handles empty result sets gracefully.

In PL/SQL, exceptions have scope similar to variables. You can declare an exception with the same name in two different blocks (nested or separate). The exception declared in the inner block will hide the one in the outer block, or they will simply exist independently in their respective scopes.

ISNULL(@variable,'5') returns '5' when @variable is NULL, otherwise returns @variable itself. COALESCE(@variable,'5') does the same by returning the first non-NULL value from its arguments. NULLIF returns NULL if two values are equal - the opposite behavior needed here, and NONNULL is not a valid SQL function.

DENSE_RANK() assigns consecutive ranks (1,2,2,2,3) without gaps after tied values, making it ideal for the requested pattern. ROW_NUMBER() always produces unique sequential numbers (1,2,3,4,5), while RANK() leaves gaps after ties (1,2,2,2,5). NTILE() divides rows into specified number of groups, producing a different pattern entirely.

Building a data warehouse involves Requirement gathering, Data modeling, and Data profiling as core steps. Data mining is an analytical technique performed on data, not a step in building the warehouse infrastructure itself.

Data Integration is the process of extracting data from multiple sources, cleansing it, applying business rules, and loading it into a unified target like a data warehouse. Data Profiling is analysis, Data Modelling is design, and Data Staging is temporary storage.

A snowflake schema can be converted to a star schema by denormalizing - eliminating the normalized dimension tables and merging them into single dimension tables. This is a valid transformation, though it may increase redundancy.

Data modeling is a broad concept that includes conceptual, logical, and physical modeling of data. Dimensional modeling is a specific data modeling technique optimized for data warehousing and business intelligence (using facts and dimensions). They are not the same.

All three listed techniques - Conceptual modelling, Logical modelling, and Physical modelling - are valid ER modelling techniques. Therefore 'None of the above' is the correct answer since all options ARE techniques.

The SQL EXCEPT operator returns rows from the first query that are not in the second query. In Oracle SQL, this operation is called MINUS. INSTEAD OF is for triggers, NOT IN is a comparison operator, and INTERSECT finds common rows.

The WHERE clause filters individual rows before grouping, then GROUP BY aggregates those rows into groups, and finally HAVING filters the aggregated groups based on group-level conditions. This order ensures efficient query execution.

The first INSERT succeeds because no rows exist. The second INSERT fails because it violates the Unique Primary Index on column A - row (1,2) already exists with A=1. The UPDATE would affect 0 rows since the second INSERT failed.

In SQL, when both operands are integers, division performs integer division. 3 divided by 4 equals 0 (the decimal part is truncated), and 0 multiplied by 100 equals 0.

SUBSTRING('RALPH JOHNSON' FROM 6 FOR 5) extracts 5 characters starting at position 6, which yields ' JOHN'. The INDEX function then searches for 'OHN' within ' JOHN'. 'OHN' starts at the 3rd character of ' JOHN' (space is 1, J is 2, O is 3), returning 3.

In Teradata, a Global Temporary Table (GTT) stores its definition (DDL) in the data dictionary, which is shared by all users. However, the data inserted into the table is materialized in temp space and is private to the session, meaning users do not share data.

Both HAVING and QUALIFY filter results after aggregation. HAVING filters grouped aggregate results, while QUALIFY filters results based on window function calculations. In Teradata SQL, they serve similar filtering purposes for aggregated data.

The NULLIF(X, Y) function compares two expressions. If they are equal, the function returns NULL. If they are not equal, it returns the first expression (X). COALESCE returns the first non-null value in its list.