Introduction to SQL Questions and Answers

Answer: A

Correct answer: True

A chain of OR conditions on one column collapses into a single IN: state = 'VA' OR state = 'MD' OR state = 'DC' becomes state IN ('VA','MD','DC'), and its negation becomes NOT IN.

The gain is not only brevity. Long AND and OR chains require careful parenthesising, since AND binds more tightly than OR and a missing bracket changes the meaning without any error. IN removes that hazard by expressing the whole set at once.

Answer: A

Correct answer: True

An index can be created on any column or set of columns, whatever its role in the design. The primary key is normally indexed automatically, since the engine needs an index to enforce uniqueness efficiently, and secondary keys are indexed on demand.

The reason for indexing a secondary key is the same as for the primary: a column frequently used in WHERE clauses or joins is expensive to search without one. The cost is space and slower writes, since every insert, update and delete must maintain each index.

Answer: C

Correct answer: ORDER BY.

ORDER BY is the only clause that sorts a result. It comes last in the statement, after WHERE, GROUP BY and HAVING, because sorting is the final step applied to what those have produced.

This is worth stating plainly: a relation is a set, so without ORDER BY the rows come back in whatever order the engine finds convenient, and that order can change when data grows or an index is added. Code that depends on an unspecified order works until it does not.

Answer: A

Correct answer: all columns of the table are to be returned.

The asterisk is shorthand in the SELECT list, standing for every column of the source in the order the table defines them. It says nothing about which rows come back - that is the WHERE clause's work, and the other options describe row filtering.

Convenient as it is, SELECT * is poor practice in application code: adding a column to the table silently changes the result set, and the query fetches data the program does not use. Naming the columns explicitly avoids both.

Answer: B

Correct answer: True

An index is a separate structure holding a copy of the indexed values with pointers to their rows, so it occupies storage of its own - on a wide multi-column index, a substantial fraction of the table's size.

It must also be kept current. Every INSERT, DELETE and every UPDATE touching an indexed column has to update the index as well as the table, so writes slow as indexes accumulate. That is the trade an index makes: faster reads paid for in space and write time, which is why indexing every column is as poor a design as indexing none.

Answer: A

Correct answer: DDL

DDL is the Data Definition Language - the subset of SQL that defines and alters the structures data lives in: CREATE, ALTER, DROP, TRUNCATE.

DML, the Data Manipulation Language, works on the data within those structures: SELECT, INSERT, UPDATE, DELETE. The division is the standard way of describing SQL, with DCL for permissions as a third part. HTML and XML are markup languages and have nothing to do with database definition.

Answer: B

Correct answer: False

SUM and AVG do require numeric columns, since adding and averaging are arithmetic. MIN and MAX do not: they need only an ordering, so they work on character strings, dates and times as well.

MIN(name) returns the alphabetically first value under the column's collation, and MAX(order_date) the latest date - both common in practice. The statement is false because it groups four functions under a restriction that applies to only two of them.

Answer: B

Correct answer: False

Access is one product among several, and by no means the dominant one. Server-class systems - Oracle, SQL Server, PostgreSQL, MySQL - carry the great majority of organisational data, and Access is a desktop tool suited to small single-user or workgroup databases.

What is genuinely critical is SQL itself, since it is standard and transfers between products. Access programming means VBA and its own object model, which are specific to it and do not carry over.

Answer: A

Correct answer: SELECT, FROM, WHERE

The written order is SELECT, then FROM, then WHERE, and it is fixed - the clauses cannot be rearranged.

Worth knowing is that the logical order of evaluation is different: FROM first to establish the source, then WHERE to filter rows, then GROUP BY and HAVING, and only then SELECT to choose columns, with ORDER BY last. That is why a column alias defined in SELECT cannot be used in WHERE: at that point it does not yet exist.

Answer: B

Correct answer: False

SELECT DISTINCT does the opposite: it removes duplicate rows from the result, returning each distinct combination of the selected values once.

To see duplicates, simply omit it - a plain SELECT returns every row that matches, repetitions included. Note also that DISTINCT applies to the whole selected row rather than to any one column, so SELECT DISTINCT city, state removes only rows where both values repeat.