Introduction to SQL Questions and Answers

Q: The result of every SQL query is a table.

Correct answer: TrueEvery SELECT returns a relation - named columns and zero or more rows - and that is a table, even though it is not stored anywhere.This is the relational model's closure property, and it is what makes SQL composable: because the result of a query is the same kind of thing as its input, a query can feed another query. Subqueries, derived tables in the FROM clause and views all depend on it. Even an aggregate with no GROUP BY returns a table, of one row and one column.

Q: The format SELECT-FROM-WHERE is the fundamental framework of SQL SELECT statements.

Correct answer: TrueSELECT names the columns wanted, FROM names the source, and WHERE filters the rows. Every query is built on that skeleton, with GROUP BY, HAVING and ORDER BY added as needed.Note that the written order and the logical order differ: the engine resolves FROM first, then WHERE, and reaches SELECT only afterwards. That is why an alias defined in the SELECT list cannot be used in the WHERE clause - at that point it does not yet exist.

Q: The SQL keyword(s) ________ is used with wildcards.

Correct answer: LIKE onlyWildcards are meaningful only to LIKE, which performs pattern matching: WHERE name LIKE 'Sm%' finds every name beginning with those two letters.IN and NOT IN test membership of an explicit list of values, and a wildcard inside such a list is simply a literal character with no special meaning. So WHERE state IN ('V%') matches the two-character string V% and nothing else - a mistake that fails silently, returning no rows rather than an error.

Q: To establish a range of values, < and > can be used.

Correct answer: TrueThe 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.

Q: Create table employee (name varchar ,id integer) What type of statement is this ?

Correct answer: DDLCREATE 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).

Q: If you are going to use a combination of three or more AND and OR conditions, it is often easier to use the IN and NOT IN operators.

Correct answer: TrueA 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.

Q: Indexes can usually be created for both primary and secondary keys.

Correct answer: TrueAn 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.

Q: To sort the results of a query use:

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.

Q: In an SQL SELECT statement querying a single table, according to the SQL-92 standard the asterisk (*) means that:

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.

Q: Each index consumes extra storage space and also requires overhead maintenance time whenever indexed data change value.

Correct answer: TrueAn 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.

71.

The result of every SQL query is a table.

A.True
B.False

Answer: A

Correct answer: True

Every SELECT returns a relation - named columns and zero or more rows - and that is a table, even though it is not stored anywhere.

This is the relational model's closure property, and it is what makes SQL composable: because the result of a query is the same kind of thing as its input, a query can feed another query. Subqueries, derived tables in the FROM clause and views all depend on it. Even an aggregate with no GROUP BY returns a table, of one row and one column.

Discuss in Forum

72.

The format SELECT-FROM-WHERE is the fundamental framework of SQL SELECT statements.

A.True
B.False

Answer: A

Correct answer: True

SELECT names the columns wanted, FROM names the source, and WHERE filters the rows. Every query is built on that skeleton, with GROUP BY, HAVING and ORDER BY added as needed.

Note that the written order and the logical order differ: the engine resolves FROM first, then WHERE, and reaches SELECT only afterwards. That is why an alias defined in the SELECT list cannot be used in the WHERE clause - at that point it does not yet exist.

Discuss in Forum

73.

The SQL keyword(s) ________ is used with wildcards.

A.LIKE only
B.IN only
C.NOT IN only
D.IN and NOT IN

Answer: A

Correct answer: LIKE only

Wildcards are meaningful only to LIKE, which performs pattern matching: WHERE name LIKE 'Sm%' finds every name beginning with those two letters.

IN and NOT IN test membership of an explicit list of values, and a wildcard inside such a list is simply a literal character with no special meaning. So WHERE state IN ('V%') matches the two-character string V% and nothing else - a mistake that fails silently, returning no rows rather than an error.

Discuss in Forum

74.

To establish a range of values, < and > can be used.

A.True
B.False

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.

Discuss in Forum

75.

Create table employee (name varchar ,id integer) What type of statement is this ?

A.DML
B.DDL
C.View
D.Integrity constraint

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).

Discuss in Forum

76.

If you are going to use a combination of three or more AND and OR conditions, it is often easier to use the IN and NOT IN operators.

A.True
B.False

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.

Discuss in Forum

77.

Indexes can usually be created for both primary and secondary keys.

A.True
B.False

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.

Discuss in Forum

78.

To sort the results of a query use:

A.SORT BY.
B.GROUP BY.
C.ORDER BY.
D.None of the above is correct.

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.

Discuss in Forum

79.

In an SQL SELECT statement querying a single table, according to the SQL-92 standard the asterisk (*) means that:

A.all columns of the table are to be returned.
B.all records meeting the full criteria are to be returned.
C.all records with even partial criteria met are to be returned.
D.None of the above is correct.

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.

Discuss in Forum

80.

Each index consumes extra storage space and also requires overhead maintenance time whenever indexed data change value.

A.True
B.False

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.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Introduction to SQL Questions and Answers || Introduction to SQL MCQ Questions and Answers || Introduction to SQL GK Questions and Answers || Introduction to SQL GK MCQ Questions || Introduction to SQL Multiple Choice Questions and Answers || Introduction to SQL GK || GK on Introduction to SQL || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Introduction to SQL Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications