D492 Data Analytics Applications - Set 2 - Part 2

Correct Answer: DELETE removes specific rows, while TRUNCATE removes all rows

The DELETE command removes specific rows based on a condition, while TRUNCATE removes all rows from a table without keeping any specific rows. Neither command removes columns or tables.

Correct Answer: A join between a table and itself

A self join occurs when a table is joined with itself. It is used to compare rows within the same table. It does not involve identical rows or eliminate duplicates.

Correct Answer: UNION ALL

The UNION ALL clause combines rows from two queries without removing duplicates. UNION removes duplicates, INTERSECT selects common rows, and JOIN combines rows based on related columns.

Correct Answer: Volatile memory

Volatile memory, such as RAM, loses its data when the power is turned off. Non-volatile memory retains data without power.

Correct Answer: UPDATE

The UPDATE command is used to modify the data in an existing row. INSERT adds new rows, SELECT retrieves data, and DELETE removes rows.

Correct Answer: Returns the remainder of a division

The MODULO function returns the remainder of a division. It is commonly used to check divisibility or cycle through a series of values.

Correct Answer: LIMIT

The LIMIT keyword is used to specify the maximum number of rows to return in the result set. SELECT retrieves data, GROUP BY groups rows, and DISTINCT removes duplicate rows.

Correct Answer: Returns all rows from the left table and the matching rows from the right table

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Correct Answer: CONCAT()

The CONCAT() function combines multiple strings into one. SUBSTRING() extracts a portion of a string, LENGTH() returns the number of characters in a string, and TRIM() removes leading and trailing spaces.

Correct Answer: Returns TRUE if a subquery returns at least one row

The EXISTS operator returns TRUE if a subquery returns at least one row and FALSE if it does not return any rows.

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

A FULL JOIN returns all rows from both tables, with NULLs for non-matching rows. It ensures that no data from either table is excluded.

Correct Answer: HAVING

The HAVING clause filters rows based on the result of an aggregate function, such as SUM() or COUNT(). WHERE filters rows before aggregation, while GROUP BY groups rows and ORDER BY sorts them.

Correct Answer: INNER JOIN

An INNER JOIN returns only rows that have matching values in both tables. FULL JOIN returns all rows from both tables, LEFT JOIN returns all rows from the left table, and CROSS JOIN returns the Cartesian product of two 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: To remove leading and trailing spaces from a string

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

Correct Answer: RAND()

The RAND() function generates a random number between 0 and 1. ABS() returns the absolute value, POW() raises a number to a power, and SQRT() returns the square root.

Correct Answer: A join that returns all combinations of rows from two tables

A CROSS JOIN returns the Cartesian product of two tables, which means it combines every row of the first table with every row of the second table. It does not filter or eliminate duplicates.

Correct Answer: IN

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

Correct Answer: To create a virtual table based on a query

The CREATE VIEW statement creates a virtual table (view) based on the result of a query. It does not store data but provides a way to simplify complex queries. Temporary tables are created with a different statement.

Correct Answer: DISTINCT

The DISTINCT clause is used to remove duplicate rows from the result set. GROUP BY groups rows, ORDER BY sorts rows, and WHERE filters rows based on a condition.