SQL and Database Concepts Quiz

RAID 1 (mirroring) combined with Fallback (Teradata's secondary copy of each row) provides the maximum level of data protection. RAID 1 protects against disk failure, Fallback protects against data corruption. RAID 5 with no fallback is less protection, and RAID 1 without fallback loses Teradata's additional protection layer.

Teradata hashes the primary index value to determine row distribution. Because the hash function is deterministic, duplicate NUPI values generate the identical row hash and will always be sent to the same AMP.

Teradata uses a shared-nothing architecture where each node (with its AMPs) has its own memory and disk storage, and doesn't share resources with other nodes. This enables linear scalability. Shared memory (SMP) and shared everything are different architectures where resources are shared across processors.

A Non-Unique Primary Index (NUPI) can group rows with the same index value on the same AMP, which makes query access, aggregations, and joins on those columns highly effective, despite not guaranteeing uniform distribution.

A Unique Secondary Index (USI) value is hashed to determine which AMP contains the index subtable. Since USI enforces uniqueness, Teradata distributes USI rows across exactly 2 AMPs (one for the index, one as backup) to ensure single-AMP access during joins. This is more efficient than all-AMP or multi-AMP access, but more redundancy than 1-AMP.

In Teradata, temporary space refers to permanent disk space that is currently unused and can be borrowed for spool operations. It is not assigned at the table level but is managed at the system level. The key distinction is that temporary space is reusable permanent storage, not a separate allocation.

TDQM (Teradata Query Management) is the component that enforces query limits based on predefined thresholds for resource usage. Teradata Manager is an administrative tool but does not directly limit queries. BTEQ is a query utility, and QueryMan is a legacy query interface.

Different database systems use different function names for string length operations. Sybase uses char_length(), while Microsoft SQL Server and Adaptive Server Enterprise use len() to return the number of characters in a string expression. This is a common migration consideration when converting between database platforms.

SQL standards specify that NULL comparisons must use IS NULL or IS NOT NULL syntax rather than equality operators. The Sybase operator "<> null" (not equal to null) must be converted to the standard SQL "is not null". Using "!= null" or keeping "<> null" will not work correctly in standard SQL because NULL requires special handling.

Different database systems and SQL dialects use different string delimiters. Sybase and some older SQL variants use double quotes (") for string literals, while standard SQL and most modern databases use single quotes (') for string literals. Converting from Sybase to SQL requires changing all string delimiters from double to single quotes.

Sybase behaves differently with trailing spaces in char fields. In migrations or compatibility adjustments, right-trimming the pattern (exp2) using rtrim ensures matching behavior matches expected standard patterns without trailing space mismatches.

The non-standard *= outer join operator was deprecated in SQL Server 2005 and is not supported in SQL Server 2008 compatibility modes under level 100, which requires the standard ANSI LEFT OUTER JOIN.

EMPLOYEE_ID is a Primary Key with NOT NULL constraint, so it cannot be NULL. Option A violates this constraint. Option B fails because it omits the required Primary Key column. Option C works because it provides all three values with employee_id='1000' (as string), first_name='JOHN', and last_name='NULL' as a string literal. Option D has syntax errors (missing quote, wrong order).

To solve this question, the user needs to understand SQL syntax for granting privileges to a specific role or user.

Now, let's go through each option and explain why it is right or wrong:

A. GRANT select, insert, update ON student_grades TO manager

This option grants SELECT, INSERT, and UPDATE privileges on the STUDENT_GRADES table to a user named "manager". However, it does not grant the ability to pass on these privileges to others.

B. GRANT select, insert, update ON student_grades TO ROLE manager

This option is similar to Option A, but grants privileges to a role named "manager" instead of a specific user. However, it still does not grant the ability to pass on these privileges to others.

C. GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;

This option is incorrect because there is no "MODIFY" privilege in SQL. It should be "UPDATE" instead.

D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;

This option is correct. It grants SELECT, INSERT, and UPDATE privileges on the STUDENT_GRADES table to a user named "manager" and also includes the "WITH GRANT OPTION" clause, which allows this user to pass on these privileges to others.

Therefore, the correct answer is:

The Answer is: D

When a table name exists in both your local schema and as a public synonym, SQL always gives priority to the local schema table. The database first looks for objects in your own schema before checking public synonyms. This means your query will return data from your own HR table, not the public synonym pointing to HUMAN_RESOURCES.

Views can be explicitly created as read-only (using constructs like WITH READ ONLY in Oracle). Views can contain ORDER BY, GROUP BY, and do not strictly require aliases for all columns unless expressions or duplicates exist.

To solve this question, the user needs to know SQL syntax and the concept of NULL values. The user must use the proper SQL function to ensure that all employees' values are displayed in the calculated columns.

Option A: SELECT last_name, 12salary commission_pct FROM emp;
This option is incorrect because it does not handle NULL values. If any employee has a NULL value in the commission_pct column, the entire expression would result in NULL, and no value would be displayed.

Option B: SELECT last_name, 12salary (commission_pct,0) FROM emp;
This option is incorrect because it contains a syntax error. The expression (commission_pct, 0) is not valid SQL syntax.

Option C: SELECT last_name, 12salary(nvl(commission_pct,0)) FROM emp;
This option is correct. The NVL function is used to handle NULL values in the commission_pct column. The function replaces any NULL value with 0, ensuring that all employees have a value in the calculated column.

Option D: SELECT last_name, 12salary(decode(commission_pct,0)) FROM emp;
This option is incorrect because the DECODE function is not used correctly. The DECODE function should have a second argument to return if the first argument is not equal to 0. Without the second argument, the function would not handle any non-zero values in the commission_pct column.

Therefore, the correct answer is:

The Answer is: C. SELECT last_name, 12salary(nvl(commission_pct,0)) FROM emp;

Explanation:
Aggregate functions operate on a set of rows and return a single result. The two valid aggregate functions on the START_DATE column are:
C. COUNT(start_date) - This function returns the number of rows where START_DATE is not null.

Option A is invalid because the SUM function is used to add numeric values and cannot be used with a DATE data type.
Option B is invalid because the AVG function will not work with DATE, you can convert DATE to NUMERIC then it can be used, but without Casting it will not work
Option D is invalid because the AVG function can only be used with a single column, and the argument passed in should be numeric.

Explanation:
To display the name and annual salary multiplied by the commission_pct for all employees, we need to use the multiplication (*) operator. For records that have a NULL commission_pct, a zero must be displayed against the calculated column, we need to use the NVL function.

Option A) SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;
This option is incorrect because it does not account for NULL commission_pct values and does not use the NVL function.

Option B) SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;
This option is incorrect because the IFNULL function is not valid in Oracle SQL, and it does not use the NVL function.

Option C) SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;
This option is incorrect because the NVL2 function is not valid in Oracle SQL. It should be replaced with the NVL function.

Option D) SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;
This option is correct because it uses the multiplication operator to calculate the annual salary multiplied by the commission_pct and the NVL function to handle NULL commission_pct values.

Therefore, the correct answer is D.