SQL MCQ Set 1
1. Setting the SORT_IN_TEMPDB option to ON can direct the SQL Server Database Engine to use ________ to store the intermediate sort results.
a) tempdb
b) master
c) msdb
d) model
Answer
Answer: a [Reason:] It is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.
2. Point out the correct statement :
a) The Database Engine merges the sorted runs of index leaf rows into a single, sorted stream
b) SQL Server also supports full-text indexes and XML indexes, but those are relevant only for specific data types
c) The fill-factor value is a percentage from 1 to 50
d) All of the mentioned
Answer
Answer: a [Reason:] The sort merge component of the Database Engine starts with the first page of each sort run, finds the lowest key in all the pages, and passes that leaf row to the index create component.
3. Which of the following rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index with a fill factor of 80 on the HumanResources.Employee table ?
a)
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources REBUILD WITH (FILLFACTOR = 80);
b)
CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee REBUILD WITH (FILLFACTOR = 80);
c)
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee REBUILD WITH (FILLFACTOR = 80);
d) All of the mentioned
Answer
Answer: c [Reason:] The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.
4. Which of the following guideline is considered during column design ?
a) Keep the length of the index key short for non clustered indexes
b) An xml data type can only be a key column only in an XML index
c) Examine column which have composite primary keys
d) None of the mentioned
Answer
Answer: b [Reason:] QL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads.
5. Point out the wrong statement :
a) SQL Server supports only two index types: clustered and non-clustered
b) Appropriate indexes can make a world of difference in performance
c) SET option requires ALTER permission on the table or view
d) None of the mentioned
Answer
Answer: a [Reason:] There are plenty of indexes in SQL Server such as full text,columnstore index in SQL Server 2012
6. By default, indexes are stored in the ______ filegroup as the base table on which the index is created.
a) same
b) different
c) may be same or different
d) none of the mentioned
Answer
Answer: a [Reason:] A nonpartitioned clustered index and the base table always reside in the same filegroup.
7. Specifying the order in which key values are stored in an index is useful when queries referencing the table have :
a) ORDER BY clauses
b) SORT clauses
c) WHERE clauses
d) None of the mentioned
Answer
Answer: a [Reason:] In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient.
8. Every table should have a ________ index defined on the column, or columns.
a) CLUSTERED
b) NON CLUSTERED
c) FULL TEXT
d) All of the mentioned
Answer
Answer: a [Reason:] Clustered indexes sort and store the data rows in the table based on their key values.
9. Which of the the following option is immediately applied to the index by using the SET clause in the ALTER INDEX statement ?
a) ALLOW_PAGE_LOCKS
b) ALLOW_COLUMN_LOCKS
c) STATISTICS_RECOMPUTE
d) None of the mentioned
Answer
Answer: a [Reason:] ALTER INDEX statement requires ALTER permission on the table or view.
10. Which of the following code snippet demonstrates ALTER statement with STATISTICS_NORECOMPUTE in ONLINE state ?
a)
ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, STATISTICS_RECOMPUTE = ON);
b)
ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
c)
ALTER INDEX ALL ON Production.Product BUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
d) All of the mentioned
Answer
Answer: b [Reason:] The following options can be set when you rebuild an index by using either ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING: PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE, ONLINE, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, MAXDOP, and DROP_EXISTING.
SQL MCQ Set 2
1. SSMS provides different options to develop your SSIS package starting with ___________ wizard.
a) Import and Export
b) Copy and Import
c) Export and BulkInsert
d) None of the mentioned
Answer
Answer: a [Reason:] With SSIS wizards,we can create a structure on how the data flow should happen and make a package and deploy it based on our needs to execute in any environment.
2. Point out the wrong statement :
a) Prior to SSIS, Data Transformation Services (DTS) in SQL Server 2000 performs the tasks with fewer features
b) SSIS is used to perform operations like loading the data based on the need, performing different transformations on the data like doing calculations
c) Business Intelligence Development Studio (BIDS) is a tool which can be used to develop only SSIS packages
d) All of the mentioned
Answer
Answer: c [Reason:] BIDS is available with SQL Server as an interface which provides the developers to work on the work flow of the process that can be made step by step.
3. Which of the following is the best practice related to SSIS ?
a) Plan for capacity by understanding resource utilization.
b) SSIS is an in-memory pipeline, so ensure that all transformations occur in memory
c) Baseline source system extract speed
d) All of the mentioned
Answer
Answer: a [Reason:] SQL Server Integration Services is designed to process large amounts of data row by row in memory with high speed. Because of this, it is important to understand resource utilization, i.e., the CPU, memory, I/O, and network utilization of your packages.
4. If SSIS is not able to drive close to 100% CPU load, this may be indicative of:
a) Software Contention
b) Application contention
c) Processor contention
d) None of the mentioned
Answer
Answer: b [Reason:] In Application contention, SQL Server is taking on more processor resources, making them unavailable to SSIS.
5. Point out the wrong statement :
a) SSIS moves data as fast as your network is able to handle it
b) it is important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput
c) Integration Services canbe tuned beyond the speed of your source
d) All of the mentioned
Answer
Answer: c [Reason:] You cannot transform data faster than you can read it in SSIS.
6. The key counters for Integration Services and SQL Server are :
a) Process Bytes
b) Public Bytes
c) Private Set
d) All of the mentioned
Answer
Answer: a [Reason:] Process byte is the amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.
7. _________ represents to total memory pressure on the system in SSIS.
a) Page sec
b) Page Reads
c) Memory reads
d) All of the mentioned
Answer
Answer: d [Reason:] If page reads consistently goes above 500, the system is under memory pressure.
8. The following Network perfmon counters can help you tune your topology :
a) Total Bandwidth
b) Current Bandwidth
c) Bandwidth bound
d) All of the mentioned
Answer
Answer: b [Reason:] Current Bandwidth counter provides an estimate of current bandwidth.
9. What do you understand by design limitation in SSIS package ?
a) Making use of parallelism
b) Not making use of parallelism
c) Package uses few single-threaded tasks
d) None of the mentioned
Answer
Answer: b [Reason:] The design limitation of SSIS package is not making use of parallelism.
10. ___________ represents how many network transfers per second are occurring.
a) Network Bytes
b) Network Transfers
c) Memory reads
d) None of the mentioned
Answer
Answer: b [Reason:] If Network Transfers/sec is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
SQL MCQ Set 3
1. When was the first version of Microsoft SQL Server released ?
a) 1983
b) 1988
c) 1990
d) 1991
Answer
Answer: b [Reason:] Microsoft released its first version of SQL Server in the year of 1988. This version was developed jointly by Microsoft and Sybase for the OS/2 platform.
2. Which of the following companies originally worked together to create and market the first version of SQL Server?
a) Microsoft
b) Sybase
c) Ashton-Tate
d) All of the Mentioned
Answer
Answer: d [Reason:] First version of SQL Server was designed for the OS/2 platform and was developed jointly by Microsoft and Sybase along with Ashton-Tate.
3. Which of the SQL Server RTM included native support for managing XML data ?
a) 7.0
b) 6.5
c) 8
d) 9
Answer
Answer: d [Reason:] Support for XML is integrated into all the components from SQL Server 2005 RTM 9.
4. Codename for SQL Server 2012 is __________
a) Kilimanjaro
b) Katmai
c) Denali
d) Hekaton
Answer
Answer: c [Reason:] Codenames for SQL Server 2005, 2008, 2008 R2, 2012, 2014 are Yukon, Katmai,Kilimanjaro, Denali and Hekaton respectively.
5. SQL Server 2005 has following features
a) Dynamic Management Views
b) FILESTREAM
c) Powerpivot
d) In-memory capability
Answer
Answer: a [Reason:] SQL Server 2005 introduced DMVs (Dynamic Management Views), which are specialized views and functions that return server state information. FILESTREAM, Powerpivot and in-memory capability were introduced in 2008,2008 R2 and 2014 respectively.
6. _________ is a software application first launched with the Microsoft SQL Server 2005.
a) Enterprise Manager
b) Query Analyzer
c) Business Intelligence Development Studio
d) SQL Server Management Studio
Answer
Answer: d [Reason:] SQL Server Management Studio (SSMS) is an integrated environment which combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment.
7. LocalDB was introduced in which of the following versions of SQL Server ?
a) 2012
b) 2008
c) 2014
d) 2008 R2
Answer
Answer: a [Reason:] LocalDB was introduced in SQL Server 2012 CTP3. It?s basically a new version of SQL Express, with same functionality, dedicated to developers.
8. _________ is free database software running free SQL Server technology.
a) SQL Server Express
b) SQL Server Workgroup
c) SQL Server Enterprise
d) SQL Server Web
Answer
Answer: a [Reason:] SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine.
9. __________is the first true GUI-based database server.
a) SQL Server 7.0
b) SQL Server 6.5
c) SQL Server 2005
d) SQL Server 2008
Answer
Answer: a [Reason:] SQL Server 7.0 provides graphical user interface to interactively build a query and view the result for data source types.
10. Which was the first version of SQL Server to introduce in-memory capability ?
a) SQL Server 2012
b) SQL Server 2014
c) SQL Server 2005
d) SQL Server 2008
Answer
Answer: b [Reason:] SQL Server 2014 provides a new in-memory capability for tables that can fit entirely in memory (also known as Hekaton).
SQL MCQ Set 4
1. What type of join is needed when you wish to include rows that do not have matching values?
a) Equi-join
b) Natural join
c) Outer join
d) All of the Mentioned
Answer
Answer: c [Reason:]OUTER JOIN is the only join which shows the unmatched rows.
2. What type of join is needed when you wish to return rows that do have matching values?
a) Equi-join
b) Natural join
c) Outer join
d) All of the Mentioned
Answer
Answer: d [Reason:] Outer join returns the row having matching as well as non matching values.
3. Which of the following is one of the basic approaches for joining tables?
a) Subqueries
b) Union Join
c) Natural join
d) All of the Mentioned
Answer
Answer: d [Reason:]The SQL subquery is a SELECT query that is embedded in the main SELECT statement.In many cases, a subquery can be used instead of a JOIN.
4. The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID?
a) Equi-join
b) Natural join
c) Outer join
d) Cartesian join
Answer
Answer: a [Reason:] Equi-join joins only same data entry field .For example one table contains department id and another table should contain department id.
5. A UNION query is which of the following?
a) Combines the output from no more than two queries and must include the same number of columns
b) Combines the output from no more than two queries and does not include the same number of columns
c) Combines the output from multiple queries and must include the same number of columns
d) Combines the output from multiple queries and does not include the same number of columns
Answer
Answer: c [Reason:] A single UNION can combine only 2 sql query at a time.
6. Which of the following statements is true concerning subqueries?
a) Involves the use of an inner and outer query
b) Cannot return the same result as a query that is not a subquery
c) Does not start with the word SELECT
d) All of the mentioned
Answer
Answer: a [Reason:] Subquery—also referred to as an inner query or inner select—is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery.
7. Which of the following is a correlated subquery?
a) Uses the result of an inner query to determine the processing of an outer query
b) Uses the result of an outer query to determine the processing of an inner query
c) Uses the result of an inner query to determine the processing of an inner query
d) Uses the result of an outer query to determine the processing of an outer query
Answer
Answer: a [Reason:] A ‘correlated sub-query’ is a term used for specific types of queries in SQL in computer databases. It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause.
8. How many tables may be included with a join?
a) One
b) Two
c) Three
d) All of the Mentioned
Answer
Answer: d [Reason:] Join can be used for more than one table. For ‘n’ tables the no of join conditions required are ‘n-1’.
9. The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T?
a) Equi-join
b) Natural join
c) Outer join
d) Cartesian join
Answer
Answer: d [Reason:]Cartesian Join is simply the joining of one or more table which return the product of all the rows in these tables.
10. Which is not a type of join in T-SQL?
a) Equi-join
b) Natural join
c) Outer join
d) Cartesian join
Answer
Answer: b [Reason:] A NATURAL JOIN is an inner join where the RDBMS automatically selects the join columns based on common columns names. Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator.
SQL MCQ Set 5
1. Which of the following resource can be locked in SQL Server ?
a) TID
b) RID
c) PID
d) SID
Answer
Answer: b [Reason:] RID stands for Row identifier and is used to lock a single row within a table.
2. Point out the correct statement :
a) Deadlocking is same as normal blocking
b) No other transactions can read or modify data locked with shared lock
c) A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources
d) All of the mentioned
Answer
Answer: c [Reason:] Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system.
3. ______________ allow concurrent transactions to read (SELECT) a resource.
a) Update locks
b) Shared locks
c) Exclusive Locks
d) All of the mentioned
Answer
Answer: b [Reason:] Shared (S) locks on a resource are released as soon as the data has been read.
4. Which is the most restrictive isolation level in transaction ?
a) Repeatable Read
b) Snapshot
c) Read Committed
d) SERIALIZABLE
Answer
Answer: d [Reason:] When SERIALIZABLE isolation level is set – a range lock is placed on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.
5. Point out the wrong statement :
a) Schema modification (Sch-M) locks are used when a table data definition language operationis being performed
b) By default, SQL Server transactions times out after a certain time
c) Disallowing a locking level can affect concurrency adversely
d) Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required
Answer
Answer: b [Reason:] SQL Server transactions do not time out (unless LOCK_TIMEOUT is set)
6. __________ locks allow processes to bulk copy data concurrently into the same table.
a) Bulk update
b) Bulk import
c) Bulk export
d) Bulk copy
Answer
Answer: a [Reason:] Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified
7. Which of the following locking hint is similar to NOLOCK ?
a) PAGLOCK
b) READCOMMITTED
c) READUNCOMMITTED
d) SERIALIZABLE
Answer
Answer: c [Reason:] Do not issue shared locks and do not honor exclusive locks.
8. Which of the following mode indicates the intention of the transaction to read all of the resources lower in the hierarchy ?
a) IS
b) IX
c) SIX
d) All of the mentioned
Answer
Answer: c [Reason:] SIX stands for Shared with intent exclusive and can modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources.
9. Which of the following lock is incompatible with all lock modes ?
a) Intent exclusive (IX)
b) Schema stability
c) Schema modification
d) Bulk update
Answer
Answer: c [Reason:] SQL Server allows only one Sch-M lock on an object at a time.
10. Which of the following mode has exclusive range and exclusive resource lock ?
a) RangeX_X
b) RangeS_U
c) RangeS_S
d) RangeI_N
Answer
Answer: a [Reason:] RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index.
Synopsis and Project Report
You can buy synopsis and project from distpub.com. Just visit https://distpub.com/product-category/projects/ and buy your university/institute project from distpub.com
