Introduction to SQL Questions and Answers

Answer: A

Correct answer: Reduced training costs

A standard language means the skill transfers. Someone who knows SQL can work with Oracle, PostgreSQL, MySQL or SQL Server without learning a new query language for each, so training costs fall and staff are easier to recruit.

The second option states the opposite of a benefit: a standard reduces dependence on a single vendor, since applications can be moved. And applications are certainly still needed - SQL queries the data; it does not supply the interface or the business logic around it.

Answer: B

Correct answer: True

WHERE is applied to individual rows before GROUP BY forms the groups, so it cannot refer to an aggregate. HAVING is applied to the groups afterwards, which is why a condition such as COUNT(*) greater than 5 must go there.

Answer: B

Correct answer: False

Some subqueries have no join equivalent. A correlated NOT EXISTS, or a comparison against an aggregate of a related set such as WHERE salary > (SELECT AVG(salary) FROM emp), cannot be expressed as a join.

The reason is structural: a join produces rows by combining them, whereas a subquery can return a single computed value or merely test whether any row exists. Many subqueries can be rewritten as joins, and doing so sometimes helps the optimiser - but the statement claims all of them can, and that is what makes it false.

Answer: B

Correct answer: limits the row data are returned.

WHERE is a filter on rows: it tests each candidate row against a condition and keeps those for which the condition is true.

Which columns come back is decided in the SELECT list instead. The distinction is worth holding onto because the two operations are separate in relational terms - selection chooses rows, projection chooses columns - and SQL expresses them in different clauses. Since WHERE does only the first, the option claiming both is wrong.

Answer: B

Correct answer: False

DISTINCT and ALL qualify the SELECT list as a whole and may appear only once, immediately after SELECT. They are mutually exclusive, and ALL - keep duplicates - is the default.

What can appear more than once is DISTINCT inside aggregate functions, as in COUNT(DISTINCT city) alongside SUM(DISTINCT amount). That is a different construct with a different scope: it de-duplicates the values fed to one function rather than the rows of the result.

Answer: A

Correct answer: COUNT, SUM, AVG, MAX, MIN

These five are the standard aggregate functions, each reducing a set of rows to a single value: how many, the total, the mean, the largest and the smallest.

They are usually paired with GROUP BY, which computes one such value per group, and with HAVING, which filters on the result. Two points of behaviour are worth remembering: they ignore nulls, except COUNT(*) which counts rows regardless; and MULT, DIV and NAME in the distractors are not SQL functions at all.

Answer: B

Correct answer: False

A WHERE condition can refer to as many values as needed. IN takes a list, BETWEEN takes two bounds, and AND and OR combine any number of conditions across any number of columns.

A condition may also refer to values it does not name, through a subquery: WHERE id IN (SELECT id FROM other) tests against a whole set computed at run time. The single-value comparison is merely the simplest case, not the limit of the clause.

Answer: C

Correct answer: INSERT

INSERT is the SQL statement that adds rows to a table, in the form INSERT INTO table (col1, col2) VALUES (v1, v2), or with a SELECT in place of VALUES to add many rows from a query.

The distractors trade on the difference between data and structure. CREATE belongs to the data definition language and makes the table itself, not a row in it; ADD appears only as part of ALTER TABLE ... ADD COLUMN, which again changes structure; and MAKE is not SQL at all.

Answer: D

Correct answer: Drop table

DROP TABLE removes the relation entirely - its rows, its definition, its indexes and its constraints - leaving no object of that name.

DELETE is the near-miss and the distinction is important: it removes rows but keeps the table, so an empty relation remains ready for new data. DROP is a DDL statement and DELETE a DML one. Purge and Remove are not standard SQL for this purpose.

Answer: D

Correct answer: question mark (?); asterisk (*)

Microsoft Access departs from the SQL standard here. It uses * for any sequence of characters and ? for exactly one - the same characters MS-DOS used for file matching.

Standard SQL uses % and _ for those two roles. The difference is a practical trap: a query written in Access and moved to another engine does not fail with an error but simply returns nothing, because * is then matched as a literal asterisk. Access in ANSI-92 mode accepts the standard characters instead.