D492 Data Analytics Applications - Set 5 - Part 2

Correct Answer: To filter rows after aggregation

The HAVING clause is used to filter rows after aggregation, often used with GROUP BY. It is similar to the WHERE clause but specifically used with aggregated data.

Correct Answer: To sort rows in ascending or descending order

The ORDER BY clause is used to sort rows in ascending or descending order based on one or more columns. By default, it sorts in ascending order unless DESC is specified.

Correct Answer: A field that links two tables by referencing the primary key of another table

A foreign key is a column or group of columns that links two tables by referencing the primary key in another table, ensuring referential integrity between them.

Correct Answer: NOT LIKE

The NOT LIKE operator is used to select rows that do not match a specified pattern. LIKE is used to match a pattern, while IN and BETWEEN check for values in a list or range, respectively.

Correct Answer: AVG()

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

Correct Answer: A join that returns all rows from both tables, regardless of match

A CROSS JOIN returns the Cartesian product of two tables, meaning it returns all combinations of rows from both tables. It does not require matching values between the tables.

Correct Answer: YEAR()

The YEAR() function extracts the year from a date. DATE() is used to format a date, NOW() returns the current date and time, and EXTRACT() can also extract specific parts of a date.

Correct Answer: ALTER TABLE

The ALTER TABLE statement is used to modify the structure of an existing table, including adding or removing columns. DROP COLUMN is part of the ALTER TABLE statement and not a standalone command.

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

The COUNT() function returns the total number of rows in a result set. It is commonly used to count rows that meet a specific condition. SUM() adds values, MAX() returns the highest value, and AVG() calculates the average.

Correct Answer: Returns TRUE if a subquery returns any rows

The EXISTS operator checks if a subquery returns any rows and returns TRUE if it does. It is used to verify the existence of records in a subquery.

Correct Answer: CREATE

The CREATE keyword is used to create a new table in a database. ALTER modifies an existing table, INSERT adds new rows, and UPDATE modifies existing rows.

Correct Answer: BETWEEN

The BETWEEN operator is used to select values within a specified range, often with numeric or date values. It includes the boundary values in the result set.

Correct Answer: ORDER BY DESC

The ORDER BY DESC clause sorts the result set in descending order. WHERE filters rows, GROUP BY groups rows, and HAVING filters groups after aggregation.

Correct Answer: To remove a table or database

The DROP statement is used to remove a table or database from the system entirely. DELETE removes rows from a table, ALTER modifies a table structure, and UPDATE changes data in existing rows.

Correct Answer: MOD()

The MOD() function returns the remainder of a division. It is commonly used to determine divisibility or perform calculations involving cyclic patterns.

Correct Answer: To combine rows from two or more tables based on related columns

The JOIN clause is used to combine rows from multiple tables based on related columns, typically a foreign key and a primary key. It does not insert data or sort the result set.

Correct Answer: ALTER

The ALTER keyword is used to modify the structure of an existing table, such as adding or removing columns. INSERT adds data, CREATE creates new objects, and DROP removes objects.

Correct Answer: NOW()

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

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 within the same set of rows.

Correct Answer: DISTINCT

The DISTINCT function removes duplicate rows from a result set, ensuring that only unique rows are returned. GROUP BY groups rows, ORDER BY sorts rows, and HAVING filters groups after aggregation.