Introduction to SQL Questions and Answers

Answer: D

Correct answer: All of the above.

All three are part of defining a table, and each answers a different question.

Data types fix what may be stored in a column and how much space it takes. Primary keys identify each row uniquely and are what foreign keys elsewhere refer to. Default values decide what a column holds when an INSERT does not mention it, which is how a column can be NOT NULL without every statement supplying it. Constraints and indexes belong to the same design decision.

Answer: C

Correct answer: DISTINCT

SELECT DISTINCT removes duplicate rows from the result, comparing the whole selected row rather than any one column.

Note the cost: the engine must sort or hash the result to identify duplicates, so DISTINCT is not free on a large result set. It is also often a symptom rather than a fix - duplicates in a join usually mean the join condition is incomplete, and correcting that is better than filtering the result. UNIQUE is a constraint on a table, not a query qualifier.

Answer: A

Correct answer: True

ORDER BY is a clause of the SELECT statement and comes last, after WHERE, GROUP BY and HAVING - sorting is the final operation applied to what those have produced.

It is worth combining deliberately rather than by habit: without it the result has no defined order at all, because a relation is a set. Any order observed without it is incidental to how the engine happened to read the data, and it can change when the table grows or an index is added.

Answer: B

Correct answer: False

The sentence describes DDL, the Data Definition Language, not DML. Creating, altering and dropping tables and establishing constraints are all structural operations.

DML works with the data inside those structures: SELECT, INSERT, UPDATE and DELETE. The distinction has a practical edge beyond terminology - in most engines DDL statements commit implicitly and cannot be rolled back, while DML statements participate in a transaction.

Answer: B

Correct answer: An exact match is not possible in a SELECT statement.

Wildcards exist for the case where the exact value is unknown or where a pattern rather than a value is wanted - every surname beginning with "Mc", every product code with a particular third character.

Where the value is known exactly, = is both simpler and faster, since an equality test can use an index while a pattern beginning with a wildcard generally cannot. Wildcards belong to LIKE in a WHERE clause, so they have no place in a CREATE statement, which defines structure rather than selecting data.

Answer: D

Correct answer: All of the above.

SQL was designed as a complete language for relational databases, so it covers all three.

The data definition half defines structures - CREATE, ALTER, DROP. The manipulation half queries and changes data - SELECT, INSERT, UPDATE, DELETE. Defining the data structures is what the first half achieves. That combination is what made SQL displace the separate languages the earlier database models required, and it is why one standard covers both syntax and semantics.

Answer: B

Correct answer: Clear relation entries

DELETE FROM r with no WHERE clause removes every tuple in the relation, leaving the relation itself in place - defined, empty, and ready for new rows.

That is the precise sense in which the entries are cleared rather than the relation removed: DROP TABLE r would be needed for the latter. Nor are fields affected, since DELETE works on whole rows and never on the column structure. TRUNCATE TABLE achieves the same emptying more quickly, without logging each row.

Answer: A

Correct answer: True

COUNT, SUM, AVG, MAX and MIN are the five aggregate functions defined by the standard, each reducing a set of rows to a single value.

They are typically used with GROUP BY, which computes one value per group, and filtered with HAVING. All five ignore nulls, with the exception of COUNT(*), which counts rows whatever they contain. Individual products add many more - standard deviation, string aggregation, statistical functions - but those five are the standard set.

Answer: B

Correct answer: False

There is no ADD statement in SQL. The statement that enters rows is INSERT - a single row with VALUES, or many at once with a SELECT supplying them, which is exactly the behaviour the sentence describes.

ADD appears only as a keyword within ALTER TABLE ... ADD COLUMN, which changes the table's structure rather than its contents. The sentence is a correct description of INSERT attached to the wrong keyword.

Answer: A

Correct answer: A virtual table that can be accessed via SQL commands

A view is a stored query that behaves like a table. It has a name, columns and rows, and can be queried with SELECT exactly as a table can - but it stores no data of its own, deriving its contents from the underlying tables each time it is referenced.

That is what "virtual" means here, and it is the difference from a base table, which does hold data. Views serve two purposes in practice: they hide complexity, presenting a join as a single object, and they restrict access, exposing only chosen rows and columns.