Introduction to SQL Questions and Answers

Answer: A

Correct answer: True

The comparison operators establish a range directly: WHERE salary > 30000 AND salary < 50000 bounds the values on both sides.

They differ from BETWEEN in one respect that matters. BETWEEN is inclusive of both endpoints, whereas < and > are strict, so the two forms are not interchangeable at the boundaries - >= AND <= is what matches BETWEEN. Using the operators explicitly is the clearer choice when one end should be included and the other not.

Answer: B

Correct answer: DDL

CREATE TABLE defines a new relation, naming its attributes and their domains, so it is a Data Definition Language statement.

The test is what the statement acts on: DDL changes the structure of the database, DML changes the data within it. Nothing is stored by this statement - it produces an empty table awaiting rows. The declaration name varchar is worth noticing as incomplete in standard SQL, which requires a length as varchar(n).

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.