D492 Data Analytics Applications - Set 4 - Part 2

Correct Answer: COUNT()

The COUNT() function returns the number of rows that match a specified condition. SUM() adds values, MAX() returns the highest value, and AVG() calculates the average.

Correct Answer: A table where rows are assigned to buckets based on a hash function

A hash table assigns rows to buckets based on a hash function, which is used to improve query performance by quickly locating rows based on the hash key.

Correct Answer: DISTINCT

The DISTINCT clause removes duplicate rows from the result set. GROUP BY groups rows by specified columns, HAVING filters groups after aggregation, and LIMIT restricts the number of rows returned.

Correct Answer: A table where the rows are physically ordered by a specific column

A sorted table has its rows physically ordered based on a specific column, usually the primary key. This can improve query performance for certain types of queries.

Correct Answer: WHERE

The WHERE clause specifies the condition for selecting rows in a query. It is used to filter rows before they are returned by the query. SELECT retrieves data, FROM specifies the table, and ORDER BY sorts the result set.

Correct Answer: ABS()

The ABS() function returns the absolute value of a number. ROUND() rounds a number, POW() raises a number to a power, and SQRT() returns the square root.

Correct Answer: To group rows that share the same values in specified columns

The GROUP BY clause groups rows that have the same values in specified columns, often used with aggregate functions to summarize data. It does not sort rows or combine tables.

Correct Answer: AS

The AS keyword is used to rename a column or table in the result set. RENAME is used to change the name of a table but not in a query result. SELECT is used to retrieve data.

Correct Answer: CONCAT()

The CONCAT() function is used to join two or more strings together. LENGTH() returns the length of a string, TRIM() removes spaces, and SUBSTRING() extracts part of a string.

Correct Answer: To undo changes made during a transaction

The ROLLBACK command undoes changes made during a transaction, returning the database to its previous state. COMMIT saves changes, and DROP deletes tables or databases.

Correct Answer: Ascending

The default sorting order for the ORDER BY clause in SQL is ascending. If descending order is needed, the DESC keyword must be specified.

Correct Answer: INNER JOIN

An INNER JOIN returns only the rows that match in both tables. LEFT JOIN and FULL JOIN include unmatched rows, while CROSS JOIN returns the Cartesian product of both tables.

Correct Answer: LOG()

The LOG() function returns the natural logarithm of a number. POW() raises a number to a power, SQRT() returns the square root, and ABS() returns the absolute value.

Correct Answer: TRIM()

The TRIM() function removes leading and trailing spaces from a string. LENGTH() returns the length of a string, CONCAT() joins strings, and SUBSTRING() extracts part of a string.

Correct Answer: IN

The IN operator is used to check if a value is within a list of values. BETWEEN selects values within a range, LIKE is used for pattern matching, and EXISTS checks for the existence of rows in a subquery.

Correct Answer: UNION ALL

The UNION ALL operator combines the result sets of two queries and includes all duplicate rows. UNION removes duplicates, INTERSECT returns common rows, and JOIN combines rows from different tables.

Correct Answer: To calculate the total number of rows in a result set

The COUNT() function returns the total number of rows in a result set. AVG() calculates the average, MAX() returns the highest value, and SUM() adds up numeric values.

Correct Answer: Checks if a subquery returns any rows

The EXISTS operator returns TRUE if a subquery returns one or more rows. It is used to check for the presence of records in a subquery.

Correct Answer: UNION

The UNION operator combines the result sets of two queries and removes duplicates. INTERSECT returns only the rows that are common to both queries, and UNION ALL combines the result sets without removing duplicates.

Correct Answer: Filters groups after aggregation

The HAVING clause filters groups of rows after they have been aggregated, typically used with GROUP BY. It is similar to the WHERE clause but used for aggregated data.