SQL Joins and Functions

A Cartesian product occurs when every row from one table is combined with every row from another table. This happens when a join condition is omitted (resulting in a cross join), when a join condition is invalid causing SQL to default to Cartesian behavior, or when explicitly defined. Option D correctly captures all these scenarios.

Table name prefixes are only required when column names are ambiguous across multiple tables in the join. If a column name exists in only one table, SQL can resolve it without the table prefix. The statement claims it's 'mandatory', which is false.

To join n tables together, you need n-1 join conditions. Think of it as a chain: each new table requires one join to connect it to the existing set. For 10 tables: 10 - 1 = 9 joins. This applies to both explicit JOIN syntax and WHERE clause joins.

Arithmetic expressions with null values evaluate to null because null represents unknown/missing data. Any operation involving null propagates the null - for example, 5 + NULL or 10 * NULL both return NULL, not 5 or 0. This is standard SQL behavior.

The concatenation operator in SQL is the double pipe (||). It joins two or more strings together. For example, 'Hello' || ' ' || 'World' results in 'Hello World'. The other operators are arithmetic: +, -, * for addition, subtraction, and multiplication respectively.

SQL SELECT statements have three main capabilities: Projection (SELECTing specific columns), Selection (filtering rows with WHERE), and Join (combining data from multiple tables). Option D correctly identifies that SELECT statements can do all three.

The asterisk (*) wildcard in SQL selects all columns from a table. 'SELECT * FROM EMP' is the correct syntax. Options A and B are not valid SQL syntax - 'Select all columns' is not valid SQL, and 'distinct columns' is incorrect syntax.

The SQL statement uses typographic (smart) quotes instead of standard ASCII quotes around 'Scott'. The string "Scott" uses opening and closing quote characters instead of straight quotes, which will cause a syntax error in SQL.

SQL ORDER BY can absolutely sort by column aliases. You can assign an alias in the SELECT clause and then use that alias in ORDER BY. For example, 'SELECT salary*12 AS annual_sal FROM emp ORDER BY annual_sal' works perfectly.

SQL functions are categorized into Single-row functions (operate on each row independently) and Multiple-row functions (operate on groups of rows). Single-row includes character, number, date, conversion functions. Multiple-row includes aggregate functions like SUM, COUNT, AVG, etc.

Single-row functions operate on single rows and return only one result per row. In contrast, multiple-row (or group) functions manipulate groups of rows to return one result per group. Therefore, the statement that single-row functions can return multiple results per row is false.

Standard SQL function categories are Character (UPPER, LOWER, SUBSTR), Number (ROUND, TRUNC, MOD), Date (SYSDATE, ADD_MONTHS, LAST_DAY), and Conversion (TO_CHAR, TO_DATE). There is no separate 'String Function' category - character functions handle string manipulation.

TRUNC truncates without rounding, so 99.57 becomes 99.5. ROUND follows standard rounding rules (5 rounds up), so 99.57 becomes 99.6.

An outer join retrieves all rows that satisfy the join condition plus those rows from one or both tables that do not have a matching row in the other table. Inner joins only return records with a direct match, while outer joins are specifically used to preserve unmatched records that would otherwise be excluded.

In Oracle SQL syntax, the (+) operator placed after a column name indicates an outer join. For example, WHERE table1.column1 (+) = table2.column2 creates a left outer join on table2.

A self-join is a regular join in which a table is joined with itself, which is useful for querying hierarchical data or comparing rows within the same table. Other joins like EquiJoin, OuterJoin, and InnerJoin describe the matching condition or inclusion of unmatched rows, but do not inherently mean joining a table to itself.

In SQL, string literals are enclosed in single quotes, not double quotes. Double quotes are used for database object names (identifiers) that may contain spaces or special characters, or for case-sensitive identifiers.