D492 Data Analytics Applications - Set 3 - Part 2

Correct Answer: Extracts a portion of a string

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

Correct Answer: LIKE

The LIKE operator is used to check if a value matches a specified pattern, often using wildcards such as % or _. BETWEEN selects values within a range, IN checks if a value is in a list, and EXISTS checks if a subquery returns rows.

Correct Answer: A join between a table and itself

A self join occurs when a table is joined with itself to compare rows within the same table. It is useful for hierarchical data or comparisons between rows.

Correct Answer: AS

The AS keyword is used to rename a column or table in a query result. FROM specifies the table, and RENAME is used to change the name of a table or column but not in a query result.

Correct Answer: GRANT

The GRANT statement is used to assign specific privileges to users, such as SELECT, INSERT, or DELETE. REVOKE is used to remove those privileges, while ALTER modifies database objects, and INSERT adds new data.

Correct Answer: To combine rows from multiple tables based on related columns

The JOIN clause is used to combine rows from two or more tables based on a related column. It does not remove rows, create new tables, or retrieve unique rows.

Correct Answer: MOD()

The MOD() function returns the remainder of a division. It is useful for checking divisibility or cycling through values. POW() raises a number to a power, SQRT() returns the square root, and ROUND() rounds a number.

Correct Answer: Removes duplicate rows from the result set

The DISTINCT keyword removes duplicate rows from the result set, ensuring that only unique rows are returned. It does not affect sorting or joining tables.

Correct Answer: CROSS JOIN

A CROSS JOIN combines all rows from two tables without any conditions, resulting in the Cartesian product of the tables. It does not match rows based on a condition, unlike INNER JOIN, LEFT JOIN, or RIGHT JOIN.

Correct Answer: To modify the structure of an existing table

The ALTER statement is used to modify the structure of an existing table, such as adding or removing columns. INSERT adds rows, DELETE removes rows, and CREATE creates new databases or tables.

Correct Answer: A query that is nested inside another query

A subquery is a query that is nested inside another query, typically within the WHERE, FROM, or SELECT clauses. It is used to perform additional filtering or calculations within the main query.

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. CONCAT() joins strings, SUBSTRING() extracts part of a string, and LENGTH() returns the number of characters in a string.

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: NOW()

The NOW() function returns the current date and time. CURDATE() returns only the current date, DATE() extracts the date part from a timestamp, and TIME() extracts the time part.

Correct Answer: HAVING

The HAVING clause is used to filter rows after aggregation, typically used with GROUP BY. WHERE filters rows before aggregation, while ORDER BY sorts the result set.

Correct Answer: INNER JOIN

An INNER JOIN returns only rows that have matching values in both tables. LEFT JOIN and FULL JOIN include non-matching rows, while CROSS JOIN returns the Cartesian product of both tables.

Correct Answer: Raises a number to the power of another number

The POW() function raises a number to the power of another number. LOG() calculates the natural logarithm, ROUND() rounds a number, and ABS() returns the absolute value.

Correct Answer: IN

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

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.