Querying Microsoft SQL Server 2012 (70-461) - Exam Preparation Questions – Part 3

Quiz-summary

0 of 15 questions completed

Questions:

The Exam 70-461 exam is intended for SQL Server database administrators, system engineers and developers with two or more years of experience, who are seeking to validate their skills and knowledge in writing queries.

You have already completed the quiz before. Hence you can not start it again.

Quiz is loading...

You must sign in or sign up to start the quiz.

You have to finish following quiz, to start this quiz:

Results

0 of 15 questions answered correctly

Your time:

You have reached 0 of 0 points, (0)

Answered
Review

Question 1 of 15

1. Question
1 points
What will be the possible output of following T-SQL statement?

DECLARE @value sql_variant=’2014-07-21 21:19:01.9414542 +01:00′;

SELECT

CASE WHEN TRY_CONVERT(time,@value) IS NULL THEN ‘Cast failed’

ELSE TRY_CONVERT(time,@value)

END;
- TRUE
- FALSE
- 21:19:01.9414542
- Cast failed
Correct

Incorrect

TRY_CONVERT() function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
Question 2 of 15

2. Question
1 points
Which of the following statement is correct for following T-SQL SET Statement?

SET DATEFIRST 7;
- Sets the first day of the week to Monday
- Sets the first day of the week to Friday
- Sets the first day of the week to Saturday
- Sets the first day of the week to Sunday
Correct

Incorrect

SET DATEFIRST SET statement sets the first day of the week to a number from 1 through 7.
Question 3 of 15

3. Question
1 points
Which of the below statements is true about the T-SQL CREATE TYPE DDL Statement in SQL Server 2012?
- CREATE TYPE creates an index on a specified table or view on a specified table.
- CREATE TYPE creates a sequence object and specifies its properties.
- CREATE TYPE creates an alias data type or a user-defined type in the current database.
- CREATE TYPE creates query optimization statistics.
Correct

Incorrect

CREATE TYPE creates an alias data type or a user-defined type in the current database. The implementation of an alias data type is based on a SQL Server native system type.
Question 4 of 15

4. Question
1 points
What will be the possible output of following T-SQL statement?

SET ARITHABORT OFF;

SET ANSI_WARNINGS OFF;

SET ARITHIGNORE ON;

SELECT 1 / 0 AS DivideByZero;
- NULL
- 0
- 1
- Error Message : Divide by zero error encountered.
Correct

Incorrect

When SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.
Question 5 of 15

5. Question
1 points
What will be the correct output of following T-SQL statement?

DECLARE @ToFind varchar(50)=null;

DECLARE @ToSearch varchar(100)=’Querying SQL Server 2012 – 1000 Exam questions and answers’;

SELECT CHARINDEX(@ToFind, @ToSearch);
- NULL
- 9
- 1
- 0
Correct

Incorrect

CHARINDEX() function Searches an expression for another expression and returns its starting position if found. Eg: – CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
Question 6 of 15

6. Question
1 points
What will be the correct output of following T-SQL statement?

CREATE TABLE tbl_Test(Col1 smalldatetime);

SELECT COL_LENGTH(‘tbl_Test’,’Col1′);

DROP TABLE tbl_Test;
- 1
- 2
- 4
- 8
Correct

Incorrect

COL_LENGTH() function returns the defined length, in bytes, of a column.
Question 7 of 15

7. Question
1 points
Which statement concerning the SPARSE columns in SQL Server 2012 below is INCORRECT?
- SPARSE column can be part of a table variable.
- SPARSE column must be nullable and cannot have the ROWGUIDCOL properties.
- SPARSE columns are incompatible with data compression.
- SPARSE column can have a default constraint value.
Correct

Incorrect

SPARSE column cannot have a default value.
Question 8 of 15

8. Question
1 points
Which of the below statements is correct about the T-SQL HASH GROUP Query Hint?
- HASH GROUP Query Hint specifies that aggregations described in the GROUP BY, or DISTINCT clause of the query should use hashing.
- HASH GROUP Query Hint specifies that aggregations described in the GROUP BY, or DISTINCT clause of the query should use ordering.
- HASH GROUP Query Hint specifies that all UNION operations are performed by merging UNION sets.
- HASH GROUP Query Hint specifies that all join operations are performed by LOOP JOIN in the whole query.
Correct

Incorrect

Query hints specify that the indicated hints should be used throughout the query. Query hints affect all operators in the statement and are implemented using the OPTION clause.
Question 9 of 15

9. Question
1 points
Which of the following T-SQL clause is used to suppress duplicates in a SELECT statement?
- INTERSECT
- EXCEPT
- DISTINCT
- UNIQUE
Correct

Incorrect

SELECT clause specifies the columns to be returned by the query. DISTINCT statement is used to return only distinct values.
Question 10 of 15

10. Question
1 points
What will be the correct output of following T-SQL statement?

DECLARE @value1 int;

SET @value1 = 2;

SET @value1 |=1;

SELECT @value1;
- 1
- 2
- 3
- NULL
Correct

Incorrect

|= (Bitwise OR EQUALS) operator performs a bitwise logical OR operation between two specified integer values as translated to binary expressions within Transact-SQL statements.
Question 11 of 15

11. Question
1 points
Which of the following is the correct output for the following T-SQL statement?

DECLARE @x int = 20, @y int = 30, @z int = 40;

SELECT IIF ( @y BETWEEN @x AND @z AND @z <= @x, 'TRUE', 'FALSE' );
- NULL
- TRUE
- FALSE
- Error Message : Incorrect syntax..........
Correct

Incorrect

BETWEEN logical operator specifies a range to test.
Question 12 of 15

12. Question
1 points
What will be the default output of following T-SQL statement?

DECLARE @date datetime2 = ‘2014-01-10 23:00:00’;

SELECT DATEPART(hour, @date);
- 23
- 2014-01-10 23:00:00
- 2014-01-10
- 23:00:00
Correct

Incorrect

DATEPART() function returns an integer that represents the specified datepart of the specified date.
Question 13 of 15

13. Question
1 points
You’re planning to modify an existing table called tbl_Patients_History. Your manager has asked you to check the index details on that table. Which of the following system stored procedure you use to retrieve those information?
- sp_helpindex
- sp_indexoption
- sp_lock
- sp_configure
Correct

Incorrect

sp_helpindex lists any indexes on a table, including indexes created by defining unique or primary key constraints defined by a create table or alter table statement.
Question 14 of 15

14. Question
1 points
How can you return all the records from a table named “Employees” sorted descending by the total character length of the “First_Name” and “Last_Name”?
- SELECT * FROM [Employees] ORDER By LEN([First_Name]+[Last_Name]) ASC;
- SELECT * FROM [Employees] ORDER By LEN([First_Name]+[Last_Name]) DESC;
- SELECT * FROM [Employees] ORDER By LEN([First_Name],[Last_Name]) DESC;
- SELECT * FROM [Employees] ORDER By LEN([First_Name][Last_Name]) DESC;
Correct

Incorrect

SELECT clause specifies the columns to be returned by the query. The ORDER BY keyword is used to sort the result set. LEN() function Returns the number of characters of the specified string expression
Question 15 of 15

15. Question
1 points
Which statement concerning the common table expression (CTE) in SQL Server 2012 below is INCORRECT?
- Common Table Expressions offer to create a recursive query.
- CTE substitute for a view when the general use of a view is not required.
- CTE offers the advantages of improved readability of complex queries.
- CTEs cannot be defined in triggers.
Correct

Incorrect

CTEs can be defined in any functions, stored procedures, triggers or views.

Querying Microsoft SQL Server 2012 (70-461) – Exam Preparation Questions – Part 3

Quiz-summary

Information

Results

Categories

1. Question

2. Question

3. Question

4. Question

5. Question

6. Question

7. Question

8. Question

9. Question

10. Question

11. Question

12. Question

13. Question

14. Question

15. Question

Querying Microsoft SQL Server 2012 (70-461) – Exam Preparation Questions – Part 3

Quiz-summary

Information

Results

Categories

1. Question

2. Question

3. Question

4. Question

5. Question

6. Question

7. Question

8. Question

9. Question

10. Question

11. Question

12. Question

13. Question

14. Question

15. Question

Share this: