1Z0-006 SQL Fundamentals & Data Manipulation — Questions and Answers

Question 1: Which SQL statement is used to add a new row to a table?

UPDATE
INSERT (Correct answer)
MERGE
ALTER

Correct answer: INSERT

The `INSERT` statement is the standard SQL command used to add one or more new rows (records) into a table. It specifies the table name and the values to be inserted into its columns. This is distinct from `UPDATE`, which modifies existing rows, or `ALTER`, which modifies the table structure itself.

Question 2: To modify existing data in a table, you would use the _____ statement:

DELETE
SELECT
UPDATE (Correct answer)
TRUNCATE

Correct answer: UPDATE

The `UPDATE` statement in SQL is specifically designed to modify existing records in a table. It allows you to change the values of one or more columns for rows that meet a specified condition. This is different from `DELETE`, which removes rows, or `SELECT`, which retrieves data, making `UPDATE` the correct choice for altering current data.

Question 3: Which clause filters rows returned by a SELECT query based on a condition?

GROUP BY
ORDER BY
WHERE (Correct answer)
HAVING

Correct answer: WHERE

The WHERE clause is used in a SELECT statement to specify conditions that individual rows must meet to be included in the result set. It filters data based on specific criteria before any grouping or ordering occurs. This allows users to retrieve only the relevant subset of data from a table.

Question 4: The SQL wildcard character that substitutes for any sequence of characters in a LIKE pattern is:

_
% (Correct answer)
#
*

Correct answer: %

The percent sign (%) is the SQL wildcard character that matches any sequence of zero or more characters in a LIKE pattern. It is commonly used for partial string matching, allowing queries to find values that contain, start with, or end with a specific substring. For example, 'A%' matches any string beginning with 'A'.

Question 5: Which function returns the number of rows in a group or entire table?

SUM()
COUNT() (Correct answer)
MAX()
AVG()

Correct answer: COUNT()

The COUNT() aggregate function is specifically designed to return the number of rows. When used with COUNT(*), it counts all rows in a group or the entire table, including those with NULL values. If a column name is specified, like COUNT(column_name), it counts only non-null values in that column within the specified scope.

Question 6: To remove all rows from a table quickly while preserving its structure, use:

DELETE with no WHERE
DROP TABLE
TRUNCATE TABLE (Correct answer)
ALTER TABLE

Correct answer: TRUNCATE TABLE

TRUNCATE TABLE is a Data Definition Language (DDL) command that quickly removes all rows from a table while preserving its structure. Unlike DELETE, TRUNCATE deallocates the space occupied by the table data, making it a much faster operation, especially for large tables. It also implicitly commits the transaction and cannot be rolled back.

Question 7: What keyword specifies an alias for a column in the SELECT list?

LIKE
AS (Correct answer)
INTO
BY

Correct answer: AS

The AS keyword is used in the SELECT list to assign a temporary, more descriptive name (an alias) to a column or an expression in the query's output. While often optional, using AS improves the readability of the result set and can make complex queries easier to understand. For example, `SELECT employee_name AS Name FROM employees;`.

Question 8: A subquery enclosed in parentheses that returns a single value used in an outer query is called:

Correlated subquery
Scalar subquery (Correct answer)
Inline view
Set operator

Correct answer: Scalar subquery

A scalar subquery is a subquery that returns a single value (one row and one column) to the outer query. It can be used anywhere an expression is valid, such as in the SELECT list, WHERE clause, or HAVING clause. This allows for dynamic calculation or lookup of a single data point within a larger query.

Question 9: Which operator combines the results of two SELECT statements and removes duplicates?

UNION (Correct answer)
UNION ALL
INTERSECT
MINUS

Correct answer: UNION

The UNION set operator combines the result sets of two or more SELECT statements into a single result set. A key characteristic of UNION is that it automatically eliminates duplicate rows from the combined output. The SELECT statements must have the same number of columns and compatible data types for the operation to succeed.

1Z0-006 SQL Fundamentals & Data Manipulation

Which SQL statement is used to add a new row to a table?