Questions and answers for exam Preparation

MCQs

Total Questions : 10

Question 1. Select ID, GPA from student grades order by GPA ____________
Inorder to give only 10 rank on the whole we should use :

Limit 10
Upto 10
Only 10
Max 10

Discuss Question

Answer: Option A. -> Limit 10

Limit clause does not support partitioning, so we cannot get the top n within each partition without performing ranking; further, if more than one student gets the same GPA, it is possible that one is included in the top 10, while another is excluded.

Question 2. Windowing function was added in which of the following versions of SQL Server ?

2003
2005
2008
None of the mentioned

Discuss Question

Answer: Option C. -> 2008

SQL Server has had only a partial implementation up to now, but it has come up in SQL 2012.

Question 3. Which of the following is not the function of client ?

Compile queries
Query optimization
Receive queries
Result formatting and presentation

Discuss Question

Answer: Option B. -> Query optimization

Query optimization is used to improve the quality.

Question 4. Which of the the function is not a ranking window function ?

RANK
NTILE
ROW_NUMBER
None of the mentioned

Discuss Question

Answer: Option A. -> RANK

Since SQL Server 2005 we have had support for some window functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.

Question 5. Purpose of foreign key constraint in SQL Server is :

FOREIGN KEY constraints identify and enforce the relationships between tables
A foreign key in one table points to a candidate key in another table
You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value
None of the mentioned

Discuss Question

Answer: Option A. -> FOREIGN KEY constraints identify and enforce the relationships between tables

Question 6. Which of the following constraint does not enforce uniqueness ?

UNIQUE
Primary key
Foreign key
None of the mentioned

Discuss Question

Answer: Option C. -> Foreign key

FOREIGN KEY constraints identify and enforce the relationships between tables.

Question 7. The command ________________ such tables are available only within the transaction executing the query, and are dropped when the transaction finishes.

Create table
Create temporary table
Create view
Create label view

Discuss Question

Answer: Option B. -> Create temporary table

DDL statement is used alter structure of table.

Question 8. Which of the following error message will be displayed if ORDER CLAUSE is not mentioned in ROW NUMBER function ?

The ranking function ROW_NUMBER" must have an ORDER BY clause."
The ranking function ROW_NUMBER" must have an OVER BY clause."
The ranking function ROW_NUMBER" must have an PARTITION BY clause."
All of the mentioned

Discuss Question

Answer: Option C. -> The ranking function ROW_NUMBER" must have an PARTITION BY clause."

ORDER BY CLAUSE is not optional clause in ROW NUMBER function.

Question 9. Which of the following is the simplest ranking function ?

RANK
NTILE
ROW_NUMBER
None of the mentioned

Discuss Question

Answer: Option C. -> ROW_NUMBER

The ROW_NUMBER ranking function is the simplest of the ranking functions.Its purpose in life is to provide consecutive numbering of the rows in the result set by the order selected in the OVER clause for each partition specified in the OVER clause.

Question 10. Select * from employee where salary>10000 and dept_id=101;
Which of the following fields are displayed as output?