d492 Data Analytics Applications - Set 5 - Part 1

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

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions like SUM(), COUNT(), or AVG().

Correct Answer: INTERSECT

The INTERSECT operator returns only the rows that are common between two result sets. UNION combines all rows and removes duplicates, while JOIN and CROSS JOIN combine rows from multiple tables.

Correct Answer: To enforce unique values in a column

A PRIMARY KEY uniquely identifies each row in a table and ensures that no duplicate values exist. It is critical for maintaining data integrity in a relational database.

Correct Answer: Combines rows from two result sets and includes duplicates

The UNION ALL operator combines rows from two result sets without removing duplicates. If duplicates should be removed, the UNION operator is used.

Correct Answer: LENGTH()

The LENGTH() function returns the number of characters in a string. CONCAT() is used to join strings, SUBSTRING() extracts a portion of a string, and TRIM() removes leading and trailing spaces.

Correct Answer: To delete specific rows from a table

The DELETE statement is used to remove specific rows from a table based on a condition. It does not delete the table itself or the entire database.

Correct Answer: ROUND()

The ROUND() function is used to round a number to a specified number of decimal places. SQRT() returns the square root, ABS() returns the absolute value, and MOD() returns the remainder of a division.

Correct Answer: HAVING

The HAVING clause is used to filter rows after they have been grouped, often used with the GROUP BY clause. WHERE filters rows before aggregation, while ORDER BY sorts rows and LIMIT restricts the number of rows returned.

Correct Answer: To revert changes made during a transaction

The ROLLBACK command is used to undo changes made during a transaction, restoring the database to its previous state. COMMIT saves changes, and DELETE and ALTER perform specific modifications on data.

Correct Answer: A virtual table based on the result of a query

A view is a virtual table in SQL that is created based on the result of a query. It does not store data itself but retrieves data from the underlying tables.

Correct Answer: SQRT()

The SQRT() function returns the square root of a number. POW() raises a number to a power, LOG() returns the natural logarithm, and ROUND() rounds a number to a specified number of decimal places.

Correct Answer: To modify the structure of an existing table

The ALTER TABLE statement is used to modify the structure of an existing table, such as adding or removing columns. It does not insert rows or delete rows from the table.

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: To remove leading and trailing spaces from a string

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

Correct Answer: IN

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

Correct Answer: CONCAT()

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

Correct Answer: To check if a subquery returns one or more rows

The EXISTS operator returns TRUE if a subquery returns one or more rows. It is commonly used to check for the presence of records that match certain criteria.

Correct Answer: Selects values within a specific range

The BETWEEN operator is used to select values that fall within a specific range, including the boundary values. It is often used with numeric or date values.

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

The TIME() function returns the current time. CURDATE() returns the current date, NOW() returns both the current date and time, and DATE() is used to extract the date portion from a timestamp.