Elance Microsoft SQL Server Test Answers 2015



Which of the following aggregate functions are supported by the Transact-SQL language?
MIN
AVG
MAX
All of these are supported


True or False? Every row in a FileTable represents a file or a directory.
True
False


Which of the following is used to change tracking?
Use triggers
None of these
Use triggers or use change data capture (CDC)
Use change data capture (CDC)


What does DBCC stand for?
Database Communication Checker
Database Clustering Checker
Database Consistency Commands
Database Connection Checker


What is a "CROSS APPLY" used for?
Synchronizing changes between two databases
Updating a report file
Performing joins between calculated sets
Setting user permissions


Which sql function finds the length of a string field?
STRLEN
LENGTH
LEN


a FOREIGN KEY constraint can reference a UNIQUE constraint
False
True


Which of the following statements will remove a UDF?
DROP FUNCTION
ALTER FUNCTION
CREATE FUNCTION
All of these


Which of the following is a benefit of SQL Server replication?
Load Processing
Redundancy
Offline Processing
All of these


What does COALESCE do?
Combines multiple T-SQL statements into one.
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
The same as UNION ALL.
The same as RIGHT JOIN.
The same as LEFT JOIN.


What is the default isolation level in SQL Server?
Serializable
Repeatable Read
Snapshot
Read Commited


Which of the following are not default database schemas?
dbo
pte
guest
sys


Differential backups do what?
Backs up only the information in the selected FileTable
Copy only the information that has changed since the last full backup
Backs up all the information in the SQL Server
None of these


What is the basic difference between varchar and nvarchar?
nvarchar supports large blob.
varchar supports unicode data.
nvarchar supports unicode data.
nvarchar performs faster then varchar.


True or false: A stored procedure can recursively call itself.
True
False


The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.
TIMESTAMP
FILESTREAM
SQL_VARIANT
UNIQUEIDENTIFIER


What is order of precedence of order by group by?
group by then order by
order by then group by


True or False? A table can have more than one primary key.
True
False


What clause allows you to filter the result of a column that has been aggregated?
HAVING
WHERE
NOT IN
NOT LIKE


Data Transformation Services do what?
They provide a window into the inner workings of the database
They are used to control the MSSQLServer, MSDTC, and SQLServerAgent processes
They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats
They are the main administrative console for SQL Server installations


Consider the following queries: A: select * from [tbl1] union select * from [tbl2]; B: select * from [tbl1] union all select * from [tbl2]; What is the difference between these two queries?
Query A will return both result sets with duplicates removed; Query B will return both full result sets.
Query A will return both full result sets; Query B will return both result sets with duplicates removed.
Query A and B return the same result set.


Which transaction isolation level allows dirty reads?
SERIALIZABLE
READ UNCOMMITED
SNAPSHOT
READ COMMITED


You can create index on views.
False
True


What is the "WITH RECOMPILE" statement used for?
Causes the SQL engine to recompile itself from source
Converts a SQL statement into machine code
Binds a CLR static method to a stored procedure
Causes SQL Server to rebuild the query plan for a stored procedure on every call


What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?
Data Import & Export
SSIS
Analysis Services
Backup Restore


Which of the following allows you to modify (update or delete) all values of the parent table?
SET NULL
NO ACTION
CASCADE
SET DEFAULT


To run Schedule Job in SQL Server, which services used?
SQL Server Agent Service
Option 1 & 2
None of the above
SQL Server Reporting Service
SQL Server Browser Service


When creating indexes in SQL, what two main factors must be considered?
Performance versus disk space
Memory versus disk space
Tables versus functions
Network bandwidth versus memory


Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?
SMALLDATETIME2FROMPARTS
TIMEFROMPARTS
SMALLDATETIMEFROMPARTS
DATEFROMPARTS
All of these


When you are dealing with lots of NULL Values in columns,which datatype is better?
Fixed Length
Variable Length


What are the 3 Transact-SQL statements that are related to authorization?
ADMIT, REMOVE, REVOKE
ALLOW, DENY, REVOKE
ALLOW, DENY, REMOVE
GRANT, DENY, and REVOKE


In this statement, "SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ", the JOIN term is the same as:
LEFT INNER JOIN
INNER JOIN
FULL OUTER JOIN
LEFT OUTER JOIN


What does the "IN" clause do?
Used to specify which table to iterate over in a loop using T-SQL
Compares a specified value against a list of values and is true if a match is found
Searches a file for records belonging to a table
Checks whether a table contains a reference to another table


What is the role of a processadmin user?
Manages processes running in an instance of SQL Server
Executes the BULK INSERT statement
Manages server logins
Creates and alters databases


In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.
True
False


Database Engine default Isolation Level
Read committed
Serializable
Read uncommitted
Repeatable read


What is the default port of Microsoft SQL Server (for TCP)?
1434
1343
1143
1443
1433


What does the Query Analyzer, or SQL Server Management Studio, tool do?
Quickly pulls information out of a database in response to a user request
All of these
Tests queries before implementing them in other applications
Creates/modifies stored procedures and execute administrative tasks


In which context is the ROW_NUMBER() function valid?
select ROW_NUMBER();
select ... inner join [tbl] on ROW_NUMBER() = [tbl].[id];
select ... ROW_NUMBER() as [row] ... group by [group_val] order by [id];
select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;


Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function
When casting to desired data type failed it will result Error
When casting to desired data type failed it will result 0
When casting to desired data type failed it will result NULL
When casting to desired data type failed it will result Empty


To Improve stored procedure performance,which statement below is used to write beginning of SP?
SET NOCOUNT OFF
SET STATISTICS IO ON
SET NOCOUNT ON
SET STATISTICS IO OFF
SET STATISTICS TIME ON


Which of the following indices does NOT change the physical order of the rows in the table?
Clustered Indices
Both Nonclustered Indices and Clustered Indices
Nonclustered Indices


Which of the following acts as the Database Engine's security system?
Both Windows security and SQL Server security
Windows security
SQL Server security


Which is the slowest?
INDEX scan
TABLE scan


To find all employees whose name ends with 'ith' complete this sql statement: "SELECT * FROM employees WHERE name " ...
LIKE "%ith%"
LIKE "_ith"
IN ('ith')
LIKE "%ith"


A sysadmin user is allowed to perform what actions?
Set user accounts
Edit databases
Any action in SQL server
Change security settings


What is the default port number for SQL Server?
1433
5022
1434
5023


What is CTE
Common Table Excess
Common Table Experiences
Common Table Expresssion
Common Table Express


What is a cursor in SQL Server?
A special T-SQL construct used to iterate through the individual records returned by a query
A collection of icons that can be applied to the mouse pointer on the screen
A remote connection to another SQL Server
A user interface component contained within the SQL Server Management Studio application


Which clause would you use to perform conditional filtering on aggregate data in a query that uses "GROUP BY"?
CONTAINS
HAVING
WHERE
HAS
FILTER BY


Which of the following statements are related to transactions?
All of these
ROLLBACK [WORK]
COMMIT [WORK]
BEGIN TRANSACTION


"Primary Key" can only be created on a column that doesn't accept NULL
True
False


What does "UDF" stand for?
User Data Form
Universal Data File
User Defined Function
USB Device File
User Data Filter


Which statement adds a row to a table?
ADD
UPDATE
RESERT
INSERT INTO


What is the correct syntax for a CTE?
with select [column] from myCTE ([column]) as (select ...);
select [column] from cte myCTE ([column]) as (select ...);
with myCTE ([column]) as (select ...) select [column] from myCTE;
using cte myCTE ([column]) as (select ...) select [column] from myCTE;


A sysadmin password requires at least 8 characters and which of the following character types?
All of these
Uppercase letters
Non-alphabetic
Lowercase letters


The system database that holds information on SQL Server Agent Jobs is?
master
msdb
tempdb
model


True or False? The EXEC keyword and the EXEC() function are equivalent to each other.
True
False


Which Transact-SQL statement should you include at the beginning of a stored procedure to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back?
SET ARITHABORT ON
SET XACT_ABORT ON
TRY
SET XACT_ABORT OFF


"SELECT name FROM t1 UNION SELECT name FROM t2 " is the same as "SELECT DISTINCT name FROM t1 UNION ALL SELECT DISTINCT name FROM t2"
True
False


Given a table with the following schema: create table table1 (field1 int) The table contains 1000 records. What would the following sql statement result in: update top (100) table1 set field1 = 1
Produce and error
Update the field1 column to the value 1 in only 100 records in the table
Update the field1 column to the value 1 in all 1000 records in the table


GETDATE() is a ....... function?
Server Level
Database Level
Deterministic
Nondeterministic
User Defined


Which of the following SQL Server built-in functions is nondeterministic?
CONVERT()
LEN()
COUNT()
GETDATE()
ISNULL()


Which of the following is NOT an aggregate function in SQL Server?
AVG
STDEV
COUNT
SUM
RANK


"Unique Constraint" can be created on a column that accepts NULL
False
True


A ________ is an alphanumerical, hexadecimal, or numberic constant.
delimiters
figurative value
literal value
identifiers


A relationship that identifies that a column contains primary key values from another table is referred as what?
Associate key
Foreign key
Alien key
Join key
Sibling key


Which of the following statements removes one or more previously granted or denied permissions?
REVOKE
DENY and REVOKE
REVOKE and REMOVE
REMOVE


All Database Backup/Restore Related information stored in which system database?
Distribution Database
MSDB
MODEL
TEMPDB
MASTER


"SELECT UNIQUE city FROM customers" will return what data set?
Unique values of city from the customers table.
An error.
Unique values of customer from the table city.


Which of the following best describes the flow for the execution of a CLR stored procedure?
Source code > Object code > Executable code > Procedure as database object
Source code > Object code > Procedure as database object
Source code > Executable code > Procedure as database object
Object code > Executable code > Procedure as database object


TRUE OR FALSE: Is it possible to execute a Stored Procedure in a UDF.
False
TRUE


Which of the following storage options can be used to store LOBs and save storage?
None of these
Only in Sparce columns
Both FILESTREAM and Sparse columns
Only in FILESTREAM


True or False? You cannot execute dynamic SQL code from a function.
False
True


Which versions of SQL Server support table partitioning?
Enterprise, Standard editions
Enterprise edition
Enterprise, Standard, Express editions
Web edition


Which statement is allowed inside of a User Defined Function?
TRY....CATCH
EXECUTE
DELETE
DECLARE
INSERT


True or false? Adding TOP to a select always improves performance.
True
False


Which of the following statements prevents users from preforming actions?
REVOKE
REVOKE and REMOVE
DENY and REVOKE
DENY


True or false: You can modify data in a table using a User Defined Function.
False
True


You are querying a table that contains a field with the data type XML and need only rows that have a particular value in the XML data. Which of the following would not result in an error
SELECT * FROM #table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE '%smith%'
Both methods would return an error
Neither method would return an error
SELECT * FROM #table1 where xml_field1 like '%smith%'


In SQL Server 2012 introduces what new feature?
Always On
Log Shipping
Online Index Rebuilds
Online Index Reorgs
Mirroring


Which of the following stores FileStream data?
Filegroup
Directory
FileTable
None of these


Which of the following relationships requires a reference table?
Hierarchical
Many to many
One to one
One to many


The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.
PREVIOUS_ROW
LEAD
FIRST_VALUE
SELECT


Can a unique key with not null constraint make it a primary key?
FALSE
TRUE


True or false? Intersection tables should have an autoincrement column as the primary index
True
False


What is the name of the database that gets created in SQL Server 2012 when you create an "Integration Services Catalog"?
SSISCatalog_Temp
SSISDB
SSISTempDB
SSISCatalog
SSISDB_Temp


Which of the following operators allows the specification of two or more expressions to be used for a query search?
IN
WHERE
OR
AND


SQL Server will create an Unique Non Clustered index, by default, when a Primary Key is created
True
False


Which of the following is not a valid string constant or hexadecimal constant?
'Boston'
'02143'
"Somerville, MA 02143"
'New York"


Which of the following is not considered a Boolean Operator?
IN
WHERE
AND
OR


What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL
Data of employees where name = null
Syntax error
1
Header without any data
The query will return incorrect value


The command DBCC CHECKDB allows you to do what?
Locate the database
None of these
Check the amount of pages
Check the average page density


What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?
CompoundString
CurveString
CompoundCurve
MultiString
StringCollection


Which of the following is not a valid data type in SQL Server 2012?
datetimeoffset
smallmoney
datetime2
smalldatetime2
hierarchyid


What does the EOMONTH function do?
Adds the specified number of weeks to the input start date and then returns the last day of the month specified
Adds the specified number of months to the input start date and then returns the last day of the month for the resulting date
Returns the last day of the month specified
None of these


Can you select the built in Server Roles for SQL Server from the list below?
diskadmin
backupoperator
accessadmin
datawriter
ddladmin


When retrieving data, which command may be used to rotate a set of column names from horizontal across the top to vertically, appearing as values among the result set
UNPIVOT
There is no specialized command for performing this operation in SQL Server, however effect can be duplicated using other means
PIVOT


The SETCERT.EXE utility can be used to do what?
All of these
Create a security certificate for the SQL server
Specify the certificate used by the SQL server
Cross check a security certificate with the owner organization


By default, SQL Server treats NULL as what data type
INT
NVARCHAR
NULL does not have data type by default
BIT
UNIQUEIDENTIFIER


True or false? Foreign keys always point to other tables.
True
False

No comments:

Post a Comment