D492 Data Analytics Applications - Set 1 - Part 2

Correct Answer: Combines the result sets of two queries and removes duplicates

The UNION operator combines the result sets of two queries and removes duplicate rows. If duplicates should be included, the UNION ALL operator is used. Grouping and combining rows from tables are done by other SQL operations.

Correct Answer: Reverts changes made during a transaction

The ROLLBACK command is used to undo changes made during a transaction. COMMIT saves changes, while DELETE and UPDATE perform specific modifications on data.

Correct Answer: BETWEEN

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

Correct Answer: GRANT

The GRANT statement is used to provide specific privileges to a user in SQL. REVOKE removes those privileges, ALTER modifies database objects, and INSERT adds new data.

Correct Answer: Foreign Key

A foreign key ensures referential integrity by linking two tables together. It ensures that a referenced value in another table exists. A primary key ensures uniqueness, while an index speeds up queries and a trigger executes an action automatically based on a condition.

Correct Answer: LENGTH()

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

Correct Answer: ORDER BY DESC

The ORDER BY DESC keyword sorts the result set in descending order. ORDER BY ASC sorts in ascending order, GROUP BY groups rows based on column values, and HAVING filters rows based on aggregate functions.

Correct Answer: FULL JOIN

A FULL JOIN returns all rows from both tables, with NULLs for non-matching rows. INNER JOIN returns only matching rows, LEFT JOIN returns all rows from the left table, and CROSS JOIN returns the Cartesian product of two tables.

Correct Answer: Removes all rows from a table but keeps the table structure

The TRUNCATE command deletes all rows from a table but keeps the structure intact. DELETE can remove specific rows based on conditions, while DROP deletes the table itself.

Correct Answer: Saves changes made to the database during a transaction

The COMMIT command is used to save all changes made during a transaction to the database. ROLLBACK undoes changes, while DELETE and ALTER modify specific aspects of the data or table.

Correct Answer: HAVING

The HAVING clause filters groups created by the GROUP BY clause. WHERE filters rows before grouping, ORDER BY sorts the result set, and DISTINCT removes duplicates.

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

The EXISTS operator returns TRUE if the subquery returns one or more rows. If no rows are returned, it returns FALSE. It is often used to check for the presence of records.

Correct Answer: CROSS JOIN

A CROSS JOIN combines every row from two or more tables without any conditions, resulting in the Cartesian product of the tables. INNER JOIN, LEFT JOIN, and RIGHT JOIN require conditions to match rows from the tables.

Correct Answer: TRIM()

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

Correct Answer: Ascending

The default sorting order for the ORDER BY clause in SQL is ascending. To sort in descending order, you would use the DESC keyword. Sorting is not random, and alphabetical order is only relevant when sorting string data in ascending or descending order.

Correct Answer: LEFT JOIN

A LEFT JOIN selects all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table. RIGHT JOIN does the opposite, INNER JOIN selects only matching records, and FULL JOIN returns all records from both tables.

Correct Answer: ALTER TABLE

The ALTER TABLE statement is used to add a new column to an existing table. ADD COLUMN is not a valid standalone SQL command, CREATE TABLE is used to create a new table, and INSERT INTO adds new rows to a table.

Correct Answer: CONCAT()

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

Correct Answer: A virtual table based on a SQL query

A view is a virtual table in SQL, created based on the result of a query. It does not store data itself but retrieves data from the underlying tables. Temporary tables are created using different SQL commands, and views do not store only primary keys or duplicate rows.

Correct Answer: NOW()

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