Database and SQL

The correct SQL syntax uses ORDER BY (not SORT BY) with DESC for descending order. Options A and B use SORT BY which is incorrect. Option D has incomplete DESC keyword and missing BY. The standard syntax is: SELECT * FROM table ORDER BY column DESC.

The correct SQL syntax for inserting a record is INSERT INTO table_name VALUES (value1, value2...). Option C shows this correctly. Options A and B have incorrect syntax (INSERT VALUES instead of INSERT INTO VALUES, and INSERT ... INTO instead of INSERT INTO ...).

When inserting into specific columns, use INSERT INTO table_name (column1, column2...) VALUES (value1, value2...). Option C correctly shows inserting into the LastName column only. Options A and B have incorrect syntax.

The standard SQL syntax to delete specific records from a table is 'DELETE FROM table_name WHERE condition'. Thus, 'DELETE FROM Persons WHERE FirstName = 'Peter'' is correct.

To count records in SQL, use the COUNT(*) aggregate function. Option B shows the correct syntax. COUNT() without asterisk (option C) is incomplete, and SELECT COLUMNS() is not valid for counting records.

The DISTINCT keyword in SQL removes duplicate values and returns only unique values. Option B is correct. SELECT UNIQUE and SELECT DIFFERENT are not valid SQL keywords.

The OR operator returns rows where ANY condition is true, while AND returns rows where ALL conditions are true. This is the standard behavior in SQL and most programming languages.

The standard SQL syntax to modify data uses the UPDATE statement combined with SET to assign the new value and a WHERE clause to specify which rows to change.

With conformed dimensions, the best practice is to query each fact table separately against the shared dimension (individual queries). A single query joining both facts directly would create a snowflake pattern that can cause performance issues and incorrect results due to fact table explosion (cartesian product of fact rows).

AUD$ is the audit trail table in Oracle that can be modified, typically for maintenance purposes like truncating old audit records. Other SYS tables like COL$ and ROW$ are data dictionary tables that should never be directly modified.

In Oracle SQL, table/column names cannot be reserved words (like SELECT, FROM, etc.) without quoting. Column aliases (defined in SELECT clause) cannot be used in WHERE clause because WHERE is processed before SELECT. Names are case-insensitive and must start with a letter.

DML (Data Manipulation Language) commands operate on existing data within database objects. SELECT retrieves data, INSERT adds new rows, UPDATE modifies existing data, and DELETE removes rows. CREATE and ALTER are DDL (Data Definition Language) commands that define or modify database structures, not manipulate data.

Indexes are beneficial when columns contain wide range of values (high cardinality), when tables are large (performance impact is significant), and when columns contain many NULL values (indexes can efficiently skip NULLs). Columns that are infrequently used don't benefit from indexing as the query performance gain wouldn't justify the storage and maintenance overhead.

An index scan occurs when DB2 accesses and searches the index structure itself to locate the requested rows, rather than scanning the entire tablespace.

A composite key is a key that consists of two or more columns used together to uniquely identify a row in a table. Unlike a simple key that uses a single column, composite keys combine multiple columns to ensure uniqueness when no single column can uniquely identify records.