D465 Data Applications - Set 1 - Part 2

Correct Answer: ORDER BY

The ORDER BY clause sorts the rows returned by a query, ascending by default or descending with the DESC keyword.

Correct Answer: HAVING

HAVING filters grouped results after aggregation, whereas WHERE filters individual rows before grouping occurs.

Correct Answer: INSERT INTO

INSERT INTO adds one or more new rows of data to an existing table, specifying columns and their values.

Correct Answer: UPDATE

UPDATE changes values in existing rows, typically combined with a WHERE clause to target specific rows. ALTER changes table structure, not data.

Correct Answer: DELETE

DELETE removes rows from a table, filtered by a WHERE clause. DROP removes an entire table or object, not individual rows.

Correct Answer: COUNT

COUNT returns the number of rows matching the query. COUNT(*) counts all rows, while COUNT(column) counts non-null values in that column.

Correct Answer: AVG

AVG computes the arithmetic mean of the values in a numeric column, ignoring null values.

Correct Answer: MAX

MAX returns the maximum value found in the specified column. MIN returns the smallest.

Correct Answer: Only rows with matching values in both tables

An INNER JOIN returns only the rows where the join condition is satisfied in both tables, excluding unmatched rows.

Correct Answer: All rows from the right table plus matching rows from the left

A RIGHT JOIN returns every row from the right table, with matching data from the left where available and NULL where there is no match.

Correct Answer: UNION

UNION combines the results of two SELECT statements and removes duplicate rows. UNION ALL keeps duplicates.

Correct Answer: IN

The IN operator checks whether a value matches any value in a specified list, a concise alternative to multiple OR conditions.

Correct Answer: BETWEEN

BETWEEN selects values within an inclusive range, for example WHERE age BETWEEN 18 AND 65.

Correct Answer: Comparisons with NULL return unknown, so IS NULL must be used

NULL represents missing data, and comparisons with NULL yield unknown rather than true or false, so IS NULL or IS NOT NULL must be used to test for it.

Correct Answer: A column or set of columns that uniquely identifies each row

A primary key uniquely identifies each row in a table, cannot contain NULL, and there is only one per table.

Correct Answer: A column that references the primary key of another table

A foreign key references the primary key of another table, enforcing referential integrity between related records.

Correct Answer: Organizing data to reduce redundancy and improve integrity

Normalization structures tables to minimize redundant data and dependency problems, reducing anomalies during insert, update, and delete operations.

Correct Answer: Speeding up data retrieval at the cost of extra storage

An index is a data structure that lets the database locate rows quickly without scanning the whole table, improving read speed while adding storage and write overhead.

Correct Answer: A query nested inside another query

A subquery is a query embedded within another query, often used in a WHERE or FROM clause to provide intermediate results.

Correct Answer: COUNTIF

COUNTIF counts the number of cells in a range that satisfy a specified condition, such as counting values greater than 100.