d492 Data Analytics Applications - Set 4 - Part 1

Correct Answer: DISTINCT

The DISTINCT keyword is used in SQL to return only unique values from a column, eliminating duplicates. UNIQUE is not a keyword for filtering query results, ALL returns all values including duplicates, and EXCEPT is used for set operations.

Correct Answer: Foreign key values must either be NULL or match some primary key value

Referential integrity ensures that foreign key values must either be NULL or match an existing value in the referenced primary key. It enforces the relationship between tables in a relational database.

Correct Answer: ORDER BY

The ORDER BY clause is used to sort rows in ascending order by default, or descending order if DESC is specified. ORDER BY ASC is redundant, while SORT BY and ASCENDING are not valid SQL clauses.

Correct Answer: MIN()

The MIN() function returns the lowest value from a column. MAX() returns the highest value, AVG() calculates the average, and SUM() adds numeric values.

Correct Answer: A table without any particular order imposed on rows

A heap table is a table in which rows are stored without any specific order. It is commonly used in relational databases where the insertion order is not predefined.

Correct Answer: To return the remainder of a division

The MOD() function returns the remainder of a division between two numbers. It is often used to determine if one number is divisible by another.

Correct Answer: NOT LIKE

The NOT LIKE operator is used to select rows that do not match a specified pattern. LIKE selects rows that match a pattern, BETWEEN filters based on a range, and NOT IN checks for values not present in a list.

Correct Answer: LIMIT

The LIMIT clause restricts the number of rows returned by a query. WHERE filters rows, COUNT is an aggregate function that returns the number of rows, and HAVING filters groups after aggregation.

Correct Answer: To return only matching rows from two tables

An INNER JOIN returns only the rows that have matching values in both tables. It does not include unmatched rows, which are handled by LEFT, RIGHT, or FULL joins.

Correct Answer: SUM()

The SUM() function is used to calculate the total of all numeric values in a column. AVG() calculates the average, COUNT() returns the number of rows, and MAX() returns the highest value.

Correct Answer: An index that improves search performance but does not alter the physical order of data

A non-clustered index improves search performance by creating a separate structure to store index data, without altering the physical order of the rows in the table.

Correct Answer: Combines rows from two or more tables based on related columns

The JOIN clause is used to combine rows from two or more tables based on related columns, such as matching a foreign key to a primary key.

Correct Answer: Combines rows from two queries, removing duplicates

The UNION operator combines the result sets of two queries and removes any duplicate rows. To keep duplicates, the UNION ALL operator is used.

Correct Answer: To filter rows after aggregation

The HAVING clause is used to filter rows after aggregation, commonly used with aggregate functions such as SUM() or COUNT(). It is often paired with GROUP BY.

Correct Answer: SQRT()

The SQRT() function returns the square root of a number. POW() raises a number to a power, MOD() returns the remainder of a division, and LOG() calculates the natural logarithm.

Correct Answer: A join that returns all rows from both tables, with NULLs for non-matching rows

A full outer join returns all rows from both tables, with NULL values for columns where there is no match between the two tables.

Correct Answer: To add new rows to a table

The INSERT statement is used to add new rows to a table. UPDATE modifies existing data, DELETE removes rows, and SELECT is used to query data.

Correct Answer: To link rows between two tables based on a related column

A FOREIGN KEY creates a relationship between two tables by linking a column in one table to a primary key in another table. It helps enforce referential integrity between related data.

Correct Answer: SUBSTRING()

The SUBSTRING() function extracts a portion of a string based on the specified starting position and length. CONCAT() joins strings, LENGTH() returns the length of a string, and TRIM() removes leading and trailing spaces.

Correct Answer: DROP

The DROP command is used to remove a table from a database completely. DELETE removes rows from a table, TRUNCATE deletes all rows but keeps the table structure, and ALTER modifies the structure of an existing table.