SQL MCQ Number 01412

SQL MCQ Set 1

1. Syntax for using linked server stored procedure in SQL Server is :
a)

sp_linkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ]

b)

sp_addlinked [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ]

c)

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ]

d)

sp_addlinkedser [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ]

2. Point out the correct statement :
a) To access a mirrored database, a connection string must contain the database name
b) When the linked server is defined against an instance of SQL Server, catalog refers to the default database to which the linked server is mapped
c) [ @location= ] ‘location’ is the location of the database as interpreted by the OLE DB provider in sp_addlinkedserver
d) All of the mentioned

3. sp_addlinkedserver command creates a distributed server entry called _________ in the following code :

sp_addlinkedserver
        @server = "Northwinds",
        @srvproduct = "Access 97",
        @Provider =
                "Microsoft.Jet.OLEDB.3.51",
                @datasrc =
                "c:brianwritingsqlservermagnov98nwind.mdb"

a) Access 97
b) Microsoft.Jet.OLEDB.3.51
c) nwind
d) Northwinds

4. Which of the following command tells that all local users are logged in as “sa” in the following code ?

EXEC sp_addlinkedsrvlogin
@rmtsrvname = "Northwinds",
@useself = "false",
@locallogin = "sa",
@rmtuser = "Admin",
@rmtpassword = NULL

a) sp_addlinkedsrv
b) sp_addlinkedserver
c) sp_addlinkedsrvlogin
d) None of the mentioned

5. Point out the wrong statement :
a) SQL Server cannot use execution strategies that involve using the procedures of the Table provider to evaluate predicates
b) For a distributed query, the comparison semantics for all character data is defined by the character se
c) SQL Server can delegate comparisons and ORDER BY operations on character columns to a provide
d) None of the mentioned

6. Which of the following function mainly used for bulk load data is referenced in the following code ?

SELECT
RemoteOrders.*
FROM
OpenRowset("Microsoft.Jet.OLEDB.3.51",
"c:brianwritingsqlservermagnov98nwind.mdb"; "admin"; ,
"select * from orders") RemoteOrders
LEFT JOIN MasterOrders ON
RemoteOrders.OrderId = MasterOrders.OrderId
WHERE
MasterOrders.OrderID = NULL

a) OpenRowset
b) Microsoft.Jet.OLEDB
c) admin
d) All of the mentioned

7. Which of the following SQL syntax elements is dictated by the SQL dialect levels ?
a) LIKE support
b) Parameter marker support
c) Nested query support
d) None of the mentioned

8. Which of the following function will be preferred for faster execution in following code ?

SELECT
RemoteOrders.*
FROM
________(Northwinds, "select * from orders")
RemoteOrders LEFT JOIN MasterOrders ON
RemoteOrders.OrderId = MasterOrders.OrderId
WHERE
MasterOrders.OrderID = NULL

a) OpenRow
b) OpenSQL
c) OpenQuery
d) All of the mentioned

9. Which of the following query combines data from more than one SQL Server instance ?
a)

 USE SCRATCH
   GO
  SELECT TOP 1000 *
  FROM REMOTE.Scratch.dbo.Table1 T1
  INNER JOIN REMOTE.Scratch.dbo.Table2 T2
  ON T1.ID
  = T2.ID

b)

USE SCRATCH
   GO
  SELECT *
  FROM dbo.Table1 T1
  INNER JOIN REMOTE.Scratch.dbo.Table2 T2
  ON T1.ID
  = T2.ID
  WHERE T1.GUIDSTR < '001'

c)

 USE SCRATCH
   GO
  SELECT TOP 500 *
  FROM REMOTE.Scratch.dbo.Table1 T1
  OUTER JOIN REMOTE.Scratch.dbo.Table2 T2
  ON T1.ID
  = T2.ID

d) None of the mentioned

10.The easiest way to start is to create a linked server ‘localhost’ by using :
a) sp_addlinkedserv ‘localhost’
b) sp_addlinkedsrvlogin ‘localhost’
c) sp_addlinkedserverlogin ‘localhost’
d) sp_addlinkedserver ‘localhost’

SQL MCQ Set 2

1. Trigger is special type of __________ procedure.
a) Stored
b) Function
c) View
d) Table

2. Point out the correct statement :
a) Triggers are database object
b) Three types of triggers are present in SQL Server
c) A DDL trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server
d) None of the mentioned

3. How many types of triggers are present in SQL Server ?
a) 4
b) 5
c) 8
d) 9

4. How many types of DML triggers are present in SQL Server ?
a) 1
b) 3
c) 5
d) None of of the mentioned

5. Point out the wrong statement :
a) We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed
b) DML Triggers are used to evaluate data after data manipulation using DML statements
c) INSTEAD OF triggers cause their source DML operation to skip
d) AFTER triggers cause their source DML operation to skip

6. AFTER trigger in SQL Server can be applied to :
a) Table
b) Views
c) Table and Views
d) function

7. DML triggers in SQL Server is applicable to :
a) Insert
b) Update
c) Delete
d) All of the mentioned

8. Triggers created with FOR or AFTER keywords is :
a) AFTER
b) INSTEAD OF
c) CLR
d) All of the mentioned

9. Which of the following is not a typical trigger action ?
a) Insert
b) Select
c) Delete
d) All of the mentioned

10. Triggers can be enabled or disabled with the ________ statement.
a) ALTER TABLE statement
b) DROP TABLE statement
c) DELETE TABLE statement
d) None of the mentioned

SQL MCQ Set 3

1. Which of the following is way to build dynamic sql statements ?
a) Writing a query with parameters
b) Using sp_executesql
c) Using EXEC
d) All of the mentioned

2. Point out the correct statement :
a) ODBC has the call ExecDirect
b) Few database systems provide the facilities for running SQL code directly against the database engine
c) The SQL code is not stored in the source program, but rather it is generated based on user input
d) None of the mentioned

3. Dynamic SQL Statements in SQL Server can be easily built using :
a) Cursor
b) Stored procedure
c) Function
d) All of the mentioned

4. The calling syntax for sp_executesql is as follows:
a) sp_execute <@stmt> [<@param1 data_type>,<@param2 data_type>, …].
b) sp_sql <@stmt> [<@param1 data_type>,<@param2 data_type>, …].
c) sp_executesql <@stmt> [<@param1 data_type>,<@param2 data_type>, …].
d) sp_executesql [<@param1 data_type>,<@param2 data_type>, …].

5. Point out the wrong statement :
a) The @stmt parameter in sp_executesql is a Unicode string containing valid SQL commands
b) The input type @Type is passed as the first parameter to sp_executesql
c) We can specify the parameters for both input and output in sp_executesql
d) None of the mentioned

6. Below Code is procedure for dynamic SQL using ___________ parameter.

CREATE PROCEDURE GetArticle 
               @ArticleID INT 
        AS 
        SELECT ArticleTitle, ArticleBody FROM 
        Articles
        WHERE ArticleID = @ArticleID GO -

a) input and output
b) input
c) output
d) All of the mentioned

7. The Dynamic SQL Queries in a variable are __________ until they are executed.
a) Compiled
b) Parsed
c) Checked for errors
d) All of the mentioned

8. The basic syntax for using EXECUTE command:
a) SP_EXECUTE(@SQLStatement)
b) EXEC_SQL(@SQLStatement)
c) EXECUTE(@SQLStatement)
d) All of the mentioned

SET @SQLQuery = 'SELECT * FROM tblEmployees
WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10))
EXECUTE(@SQLQuery)

.