Introduction to SQL Questions and Answers

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.

Q: SQL data definition commands make up a(n) ________ .

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

Q: SUM, AVG, MIN, and MAX can only be used with numeric columns.

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

Q: Microsoft Access has become ubiquitous, and being able to program in Access is a critical skill.

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

Q: Which of the following is the correct order of keywords for SQL SELECT statements?

Correct answer: SELECT, FROM, WHEREThe 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.

Q: SELECT DISTINCT is used if a user wishes to see duplicate columns in a query.

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

Q: An attribute A of datatype varchar(20) has the value “Avi” . The attribute B of datatype char(20) has value ”Reed” .Here attribute A has ____ spaces and attribute B has ____ spaces .

Correct answer: 3, 20The attributes are asked for as stored, not as supplied. A is varchar(20) holding "Avi", and a varying-length type stores only the characters given, so it occupies 3.B is char(20) holding "Reed". A fixed-length type pads to its full width with trailing spaces, so it occupies 20 whatever the value's length. That padding has a consequence beyond storage: comparisons and concatenations may carry the trailing spaces, which is why char is reserved for values that really are of fixed width.

Q: Scalar aggregate are multiple values returned from an SQL query that includes an aggregate function.

Correct answer: FalseA scalar aggregate is a single value, produced when an aggregate function is applied to a whole table with no GROUP BY - SELECT COUNT(*) FROM orders returns one row of one column.The multiple-value case is the vector aggregate, produced when GROUP BY divides the rows into groups and the function is computed once per group. The statement swaps the two definitions, which is what makes it false; "scalar" means single-valued in this context as it does elsewhere.

Q: SQL query and modification commands make up a(n) ________ .

Correct answer: DMLDML is the Data Manipulation Language - the subset of SQL that works with the data itself: SELECT to query it and INSERT, UPDATE and DELETE to change it.DDL, by contrast, defines the structures the data lives in, through CREATE, ALTER and DROP. The practical difference is not only conceptual: in most engines DDL statements commit implicitly, while DML statements participate in a transaction and can be rolled back.

Q: The SQL statement: SELECT Name, COUNT(*) FROM NAME_TABLE; counts the number of name rows and displays this total in a table with a single row and a single column.

Correct answer: FalseThe statement mixes an ungrouped column with an aggregate. Name has one value per row while COUNT(*) has one value for the whole table, and there is no rule for pairing them.Standard SQL rejects the query, and so do most engines in their default configuration. Either drop the column, giving SELECT COUNT(*) FROM NAME_TABLE and a single row, or add GROUP BY Name, giving one row per distinct name with its own count - but not the single row the statement describes.

81.

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

82.

SQL data definition commands make up a(n) ________ .

A.DDL
B.DML
C.HTML
D.XML

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.

Discuss in Forum

83.

SUM, AVG, MIN, and MAX can only be used with numeric columns.

A.True
B.False

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.

Discuss in Forum

84.

Microsoft Access has become ubiquitous, and being able to program in Access is a critical skill.

A.True
B.False

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.

Discuss in Forum

85.

Which of the following is the correct order of keywords for SQL SELECT statements?

A.SELECT, FROM, WHERE
B.FROM, WHERE, SELECT
C.WHERE, FROM,SELECT
D.SELECT,WHERE,FROM

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.

Discuss in Forum

86.

SELECT DISTINCT is used if a user wishes to see duplicate columns in a query.

A.True
B.False

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.

Discuss in Forum

87.

An attribute A of datatype varchar(20) has the value “Avi” . The attribute B of datatype char(20) has value ”Reed” .Here attribute A has ____ spaces and attribute B has ____ spaces .

A.3, 20
B. 20, 4
C.20 , 20
D.3, 4

Answer: A

Correct answer: 3, 20

The attributes are asked for as stored, not as supplied. A is varchar(20) holding "Avi", and a varying-length type stores only the characters given, so it occupies 3.

B is char(20) holding "Reed". A fixed-length type pads to its full width with trailing spaces, so it occupies 20 whatever the value's length. That padding has a consequence beyond storage: comparisons and concatenations may carry the trailing spaces, which is why char is reserved for values that really are of fixed width.

Discuss in Forum

88.

Scalar aggregate are multiple values returned from an SQL query that includes an aggregate function.

A.True
B.False

Answer: B

Correct answer: False

A scalar aggregate is a single value, produced when an aggregate function is applied to a whole table with no GROUP BY - SELECT COUNT(*) FROM orders returns one row of one column.

The multiple-value case is the vector aggregate, produced when GROUP BY divides the rows into groups and the function is computed once per group. The statement swaps the two definitions, which is what makes it false; "scalar" means single-valued in this context as it does elsewhere.

Discuss in Forum

89.

SQL query and modification commands make up a(n) ________ .

A.DDL
B.DML
C.HTML
D.XML

Answer: B

Correct answer: DML

DML is the Data Manipulation Language - the subset of SQL that works with the data itself: SELECT to query it and INSERT, UPDATE and DELETE to change it.

DDL, by contrast, defines the structures the data lives in, through CREATE, ALTER and DROP. The practical difference is not only conceptual: in most engines DDL statements commit implicitly, while DML statements participate in a transaction and can be rolled back.

Discuss in Forum

90.

The SQL statement: SELECT Name, COUNT(*) FROM NAME_TABLE; counts the number of name rows and displays this total in a table with a single row and a single column.

A.True
B.False

Answer: B

Correct answer: False

The statement mixes an ungrouped column with an aggregate. Name has one value per row while COUNT(*) has one value for the whole table, and there is no rule for pairing them.

Standard SQL rejects the query, and so do most engines in their default configuration. Either drop the column, giving SELECT COUNT(*) FROM NAME_TABLE and a single row, or add GROUP BY Name, giving one row per distinct name with its own count - but not the single row the statement describes.

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