In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up.
A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.
SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact.When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.
Not all restore scenarios require a tail-log backup.You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database and do not need to restore it to a point of time after the most recent backup. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost.
We recommend that you take a tail-log backup in the following situations:
If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:
BACKUP LOG database_name TO <backup_device> WITH NORECOVERY
Note
To avoid an error, the NORECOVERY option is necessary.
If the database is offline and does not start.
Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:
BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR
If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.
BACKUP LOG option
Comments
NORECOVERY
Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup.
The log is truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified.
Important
We recommend that you avoid using NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERROR
Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.
Note
When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. For more information, see "Tail-Log Backups with Incomplete Backup Metadata," later in this topic.
The best way to insert valid data is with constraints. This also
known as data enforcing data or data integrity. We already know that
there are various types of constraints in SQL Server. They are:
If
we are trying to insert a second record with the marks of a value of
350 then when we run the above insert query it gives an error because:
The
INSERT statement conflicted with the CHECK constraint
"CK__Student__Marks__29572725". The conflict occurred in database
"Demo", table "dbo.Student", column 'Marks'.
How to insert 350 marks into marks column
Answer:
We need to disable the Check Constraint on the table.
How to do this stuff Answer:
By using an alter statement. Syntax Alter table "tablename" nocheck constraint "Check_Constraintname" Examaple
Try
to insert the preceding record. When we run the preceding insert query
the record is inserted successfully because the check constraint is
diabled on this time.
Try to insert the the invalid data on the Marks column and see the result.
Scenario 3
createtable SchoolName
(
ID int identity(1,1) primarykey,
Namevarchar(20) unique
)
Droptable Student
createtable Student
(
ID intprimarykey,
Namevarchar(20) unique ,
Marks intnotnullcheck (marks>500),
SchoolName intforeignkeyreferences SchoolName(ID)
)
I have created 2 tables. The SchoolName table is just like the Master table. It maintains all the school name Information. The Student table maintains the Schools information. In the Student table the SchoolName column references the ID column in the SchoolName table.
Use the following to insert data into the SchoolName table:
Run
the preceding query it throws an error because the SchoolName value 3
deos not exist in the ID column of the SchoolName table.
The
INSERT statement conflicted with the FOREIGN KEY constraint
"FK__Student__SchoolN__4222D4EF". The conflict occurred in database
"Demo", table "dbo.SchoolName", column 'ID'.
A CTE is a temporary view or Disposable View that exists only in the scope of
running query.
When to use CTE? CTE can be used to:
Create a recursive query.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Reference the resulting table multiple times in the same statement.
To find duplicates
CTE does not write anything to the tempdb, hence it has a higher performance compared with the other two options like Table Variable and Table
CTE is embedded into the query.
CTEs can be used in Stored Procedures, User Define Functions (UDFs), Triggers
and Views but not in Indexed Views.
Below is the syntax for a CTE :
In the database, you will have a physical table called
Sales.Customer and from the above query you are taking that table into a CTE
called AllmyCustomers which will be referred in a future query.
Difference between a CTE and Variable Table
Obviously, a CTE is different from a temporary table sice temporary
tables save data to the tempdb but the CTE will be kept in the memory. So are is the
differences between CTEs and table variables?
A table variable’s scope is for the batch, while a CTE’s scope is only
for the query.
To populate a table variable, you need to execute insert scripts,
but with CTEs the data will be populated at time of defining the CTE.
If you want to use recursion in table variables, you need to write
your own queries but with a CTE it is much easier to write recursive queries.
If you are using larger data sets in table variables it will use
tempdb to store data physically. However, a CTE always uses memory. This
can be an issue in electing for CTEs as you need to ensure that you are not using
larger data sets that will drain memory. The following graph shows tempdb write transactions/sec
for temp tables, table variables and CTE scenarios. You can simply see
that a CTE does not write anything to the tempdb, hence it has a higher performance compared with the
other two options.
Usages for CTE
There are a few scenarios where using CTEs is optimal:
Recursion
Recursion is typically a difficult development task. Let us look atan example, say we have following
data set:
IF EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'Employees')
DROP TABLE Employees
CREATE TABLE Employees
(EmployeeID INT PRIMARY KEY,
Name VARCHAR(50), ReportsTo INT)
The important point to remember is ReportsTo is foreign key
constraint to the EmployeeID column or as some of you call it, this table has a
self-relationship between two columns of the same data.
We will now populate this with the following sample data:
If you visualize your data, it will looks as below.
Richard is the boss of the company while Stephan and Cris
report directly to Richard.
Let us say you want to find out all the employees that come under
Stephan who has an employeeid of ’2′.
WITH EmployeeRecursion(Name,EmployeeID,ReportsTo)
AS
(
SELECT Name,EmployeeID,ReportsTo
From Employees WHERE EmployeeID = 2 -- @EmpID
UNION ALL
SELECT e.Name,e.EmployeeID,e.ReportsTo
From Employees e INNER JOIN EmployeeRecursion r
ON e.ReportsTo = r.EmployeeID
)
SELECT R.EmployeeID,R.Name,E.Name Boss
FROM EmployeeRecursion R
INNER JOIN Employees E ON R.ReportsTo = E.EmployeeID
This results in the below output :
Below is the query plan for the above CTE which shows
one index scan and two index seeks. This is after adding a non-clustered index
to the ReportsTo column
In recursive queries, you can set number of levels you need
to stop recursion by applying the MAXRECURSION option.
--Max Recursion
WITH EmployeeRecursion(Name,EmployeeID,ReportsTo)
AS
(
SELECT Name,EmployeeID,ReportsTo
From Employees WHERE EmployeeID = 2
UNION ALL
SELECT e.Name,e.EmployeeID,e.ReportsTo
From Employees e INNER JOIN EmployeeRecursion r
ON e.ReportsTo = r.EmployeeID
)
SELECT R.EmployeeID,R.Name,E.Name Boss
FROM EmployeeRecursion R
INNER JOIN Employees E ON R.ReportsTo = E.EmployeeID
OPTION (MAXRECURSION 2)
Though this returns the correct values indented, error
of level 16 will be generated triggering an exception.
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 2 has been exhausted
before statement completion.
This will prevent users to using the MAXRECUSION option. This
issue exists in all SQL Server versions up to SQL Server 2012. You can overcome
this by, introducing a level column to the recursion as shown below.
WITH EmployeeRecursion(Name,EmployeeID,ReportsTo,Level)
AS
(
SELECT Name,EmployeeID,ReportsTo,0 Level
From Employees WHERE EmployeeID = 2
UNION ALL
SELECT e.Name,e.EmployeeID,e.ReportsTo, Level + 1
From Employees e INNER JOIN EmployeeRecursion r
ON e.ReportsTo = r.EmployeeID
WHERE Level <2
)
SELECT R.EmployeeID,R.Name,E.Name Boss
FROM EmployeeRecursion R
INNER JOIN Employees E ON R.ReportsTo = E.EmployeeID
Finding Duplicates
CTEs can also assist in finding duplicates in our data by improving
both performance and readability. Lets through this by an example.
First we will create the below table with data
--Finding Duplicates
IF EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'Products')
DROP TABLE Products
CREATE TABLE Products
(ProductID INT PRIMARY KEY CLUSTERED,
ProductName VARCHAR(50),
Price NUMERIC(9,2)
)
INSERT INTO Products
VALUES
(1,'Mouse',20.15),
(2,'KeyBoard',15.75),
(3,'Monitor',214),
(4,'UPS',88),
(5,'Mouse',20.14),
(6,'Monitor',251),
(7,'Mouse Pad',5),
(8,'Monitor',265)
Now, you are given the task of finding duplicates:
Below is the traditional way of doing it.
SELECT * FROM Products
WHERE ProductID NOT IN
(
SELECT MIN(A.ProductID) FROM Products A
WHERE Products.ProductName = A.ProductName
);
Using CTEs will eliminate the subquery and improve the performance:
WITH DuplicateProducts
AS
(
SELECT MIN(ProductID) AS ProductID , ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(1) > 1
)
SELECT P.ProductID,P.ProductName
FROM Products P INNER JOIN DuplicateProducts cte
ON P.ProductName = cte.ProductName
AND P.ProductID >cte.ProductID;
SQL Certification MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Study Chapters & Materials and Sample Tests Questions
Certification Path:
So, you can start with any of the 3 exams at the bottom (first) level based upon your area of interest. A Dev can take 70-461, a DBA can start with 70-462, and a DataWarehouse Engineer can go with 70-463, and you can take them in any order.
– Exam 70-986: Not yet available – Exam 70-987: Not yet available
–> UPDATE: – MCSE (DP: 464/465 and BI: 466/467) exams are updated with SQL 2014 topics. – MCSA (461/462/463) exams will be having SQL 2012 content only.
Create and alter tables using T-SQL syntax (simple statements)
Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
Create and alter views (simple statements)
Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP
Design views
Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications
Create and modify constraints (simple statements)
Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
Create and alter DML triggers
Inserted and deleted tables; nested triggers; types of
triggers; update functions; handle multiple rows in a session;
performance implications of triggers
Use the ranking function to select top(X) rows for
multiple categories in a single query; write and perform queries
efficiently using the new (SQL 2005/8->) code items such as synonyms
and joins (except, intersect); implement logic which uses dynamic SQL
and system metadata; write efficient, technically complex SQL queries,
including all types of joins versus the use of derived tables; determine
what code may or may not execute based on the tables provided; given a
table with constraints, determine which statement set would load a
table; use and understand different data access technologies; case
versus isnull versus coalesce
Implement sub-queries
Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
Implement data types
Use appropriate data; understand the uses and limitations
of each data type; impact of GUID (newid, newsequentialid) on database
performance, when to use what data type for columns
Implement aggregate queries
New analytic functions; grouping sets; spatial aggregates; apply ranking functions
Query and manage XML data
Understand XML datatypes and their schemas and interop
w/, limitations and restrictions; implement XML schemas and handling of
XML data; XML data: how to handle it in SQL Server and when and when not
to use it, including XML namespaces; import and export XML; XML
indexing
Create and alter stored procedures (simple statements)
Write a stored procedure to meet a given set of
requirements; branching logic; create stored procedures and other
programmatic objects; techniques for developing stored procedures;
different types of storeproc result; create stored procedure for data
access layer; program stored procedures, triggers, functions with T-SQL
Modify data by using INSERT, UPDATE and DELETE statements
Given a set of code with defaults, constraints and
triggers, determine the output of a set of DDL; know which SQL
statements are best to solve common requirements; use output statement
Combine datasets
Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements
Work with functions
Understand deterministic, non-deterministic functions;
scalar and table values; apply built-in scalar functions; create and
alter user-defined functions (UDFs)
Understand statistics; read query plans; plan guides;
DMVs; hints; statistics IO; dynamic vs. parameterised queries; describe
the different join types (HASH, MERGE, LOOP) and describe the scenarios
they would be used in
Manage transactions
Mark a transaction; understand begin tran, commit and
rollback; implicit vs explicit transactions; isolation levels; scope and
type of locks; trancount
Evaluate the use of row-based operations vs. set-based operations
When to use cursors; impact of scalar UDFs; combine multiple DML operations
Implement error handling
Implement try/catch/throw; use set-based rather than row-based logic; transaction management
–> Exam Highlights:
– There were total 54 Questions
– Total time to attempt the paper was 3 Hrs.
– Total Marks: 1000
– Passing Marks: 700
– The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.
2. Work with Data | 27%
– New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].
– FETCH-OFFSET, [link].
– SEQUENCE, [link].
– Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link], [video].
– OVER() Window Function, [link]
– JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), [link].
– APPLY Operators (CROSS APPLY vs OUTER APPLY), [link].
– CTE and Sub-Queries, [link].
– PIVOT, [link].
– ROLLUP, CUBE & GROUPING SETS, [link].
– Dynamic SQL
– ANY, SOME, ALL
– CASE vs ISNULL vs COALESCE, [link].
– FOR XML RAW/AUTO/PATH [ELEMENTS], [link].
– Implementing XML Schemas and Handling of XML data
3. Modify Data | 24%
– Stored Procedure (with EXECUTE AS, RECOMPILE)
– MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT), [link].
– EXCEPT vs INTERSECT
– UNION vs UNION ALL, [link].
– SCALAR vs TABLE Valued Functions.
– Use of APPLY with UDFs, [link].
– VARCHAR(MAX) and .WRITE(), [link].
4. Troubleshoot & Optimize | 25%
– Using Statistics
– SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted), [link].
– TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT), [link].
– ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
– TRY/CATCH, [link].
– RAISE vs THROW [link]
– CURSORS (Row-Based) vs SET Based Approach
– Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)
– Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))
MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Practice Questions:
1.You've created a simple select statement that returns the customers first name and last name. This statement needs changing so that it only includes the customers first initial and their last name in the result set. There is no column for initial. Which function could you use to get the initial?
2.What is missing from the T-SQL statement below for creating and using a cursor?
DECLARE mycursor CURSOR FOR SELECT * FROM Vendor FETCH NEXT FROM mycursor;
3.DML triggers are used
to enforce business rules when data is modified and to extend the
integrity checking logic of Microsoft SQL Server constraints, defaults,
and rules. Which of the following is FALSE regarding DML triggers?
4.You're creating a new
query that will select rows from a products tables. The query works out
the count of products within each category by grouping on the category,
filtering by categories that contain more than one product and then
sorting the results in category order. In which order should these
clauses be used in the query?
5.Which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT OR CAST?
6.The !> operator is
a comparison operator that compares two expressions. However, if
ANSI_NULLS is set to ON and one of the operands is NULL, what will the
result be?
expression !> expression (NULL)
Note:
If
either or both operands are NULL and SET ANSI_NULLS is set to ON, the
result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if
one of the operands is NULL, and TRUE if both operands are NULL.
7.Your manager has
asked you to check the index stats for a particular table called
Address. You have executed the following query (see below) and it
reported that the avg_fragmentation_in_percent is 15%. What should you
do?
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'Test'), OBJECT_ID(N'Address'), NULL, NULL , 'DETAILED');
Note:
Rebuild Index if Index Fragmentation above 30%
ReOrganize Index if Index Fragmentation betwenn 10 to 30 %
8.You're designing a
new table that will hold information about medical records. Another
table called documents contains a foreign key that references this
table. To make sure information is cleaned up when a delete operation is
performed, you've added an ON DELETE CASCADE statement to the foreign
key relationship. What will happen when a row is deleted from the
medical records table?
9.A DML trigger is an
action programmed to execute when a data manipulation language (DML)
event occurs in the database server. DML events include UPDATE, INSERT,
or DELETE statements issued against a table or view. Which of the
following is TRUE regarding INSTEAD OF triggers?
10.Generates totals that
appear as additional summary columns at the end of the result set. When
used with BY, the ___ clause generates control-breaks and subtotals in
the result set.
11.A ___ is a special
kind of stored procedure that executes automatically when a user
attempts the specified data-modification statement on the specified
table.
12.You're designing a
new query that will eventually be used by a new software application.
It's important that the application knows the id value of the last row
that was inserted. Rather than re-run another query just to get the
maximum id value, your friend has told you to include an inbuilt
function. What might that function be?
Note:
SCOPE_IDENTITY
is the best answer. Although you could use @@IDENTITY, you need to be
careful here, since it's across scopes. You could get a value from a
trigger, instead of your current statement.
uestion 1 of 15
13.You've been told to
execute the following SQL command to create a new table called
employees. The id column of the new table will be an auto increment
column but what do the 2 numbers after the IDENTITY clause mean?
The
two arguments to the identity property are the seed and increment
values (respectively). The seed is the value that is used for the very
first row loaded into the table. The increment is the incremental value
that is added to the identity value of the previous row that was loaded.
14.What needs correcting in the statement below to make it a valid UPDATE statement?
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
15.Which of the following would be a correct way to call the stored procedure MyProc?
16.You're designing a
new query that will return all of the medical records from the patients
table. This could run into millions of rows so you need to find a way to
limit the results to include only the top 100 most recent records. What
is the best way to achieve this?
17.SQL Server 2012 uses the standard four-part naming convention when referencing database objects.
Which of the following is the correct example of the four-part naming?
18.Your manager has
asked you to extend one of the complex stored procedures used to
calculate time differences between lab test results. In particular,
during the stored procedures you need to check the exact minute value of
a datatime value. Your manager has suggested that you use the DATENAME
function and pass in the date and datepart argument. Which datepart
argument should you use to get the minute?
Note:
If you want to get the minute, you can either pass in mi or n as the argument to DATENAME.
19.You've created a
standard stored procedure that's not a CLR procedure. This procedure has
several parameters, some of which are output parameters. Which of the
following CANNOT be passed back as an output parameter from a stored
procedure?
20.You need to write a
new query that will return names from the employee table for people who
are not part of the contractors table (the two tables share a unique id,
similar to social security or NI number). Your query will use a sub
query to check that the employee is not in the contractors table. How
might you achieve this?
21.Which of the following is an example of a scalar subquery?
select max(price) from products
select sum(price) from products
select min(price) from products
All of these
Answer:
All of these
Comments:
A scalar subquery is a subquery that returns one value.
22.Which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT OR CAST?
varchar to decimal
decimal to bit
date to varchar
datetime to text
Answer:
datetime to text
Comments:
There
are very few conversions which are allowed to the Text data type. They
include varchar, char, nchar, nvarchar and ntext. Converting datetime to
text is not an allowed conversion using either CAST or CONVERT.
23.You're designing a
new SQL Server 2012 query for the HR department. The query will find
records from the persons table for people whose name starts with 'Ja'.
Which WHERE statement would be the correct choice?
24.You're designing a
new SQL Server 2012 table that will hold millions of records. The table
will be used by a finance application to show historical transactions.
The table only has 10 columns but it's vital that the table performs
well. How many primary keys should you create for the table for best
performance?
25.As part of a new
enterprise project, you're designing a new table to store financial
transactions. This table could eventually store millions of rows and so
storage space is very important. One of the columns in the table will
store either a 1 or 0 value. Which data type would be most appropriate?
26.You work as a database administrator at ABC.com. You are developing a
database that will be used by a web site analysis application name
ABCWeb1.The ABCWeb1 application must display the date and time each
visitor visits a page on a website as well as the date and time they
leave that web page. This data needs to be displayed in different date
and time formats.How would you accomplish this?
A. You should make use of a scalar user-defined function. B. You should make use of the SET CONTEXT_INFO statement in the stored procedure. C. You should make use of the DATETIMEOFFSET data type. D. You should make use of the FORMAT function. E. You should make use of the SET FORCEPLAN ON statement in the stored procedure. F. You should make use of a cursor.
Answer: D
27.Which of the following can be used to protect the code in a stored procedure? A. The ENCRYPBYKEY statement. B. The ENCRYPBYASYMKEY statement. C. The SET TRUSTWORTHY ON option. D. The SET XACT_ABORT ON statement. E. The ENCRYPTBYPASSPHRASE statement. F. The ENCRYPTBYCERT statement. G. The SIGNBYASYMKEY statement. H. The CRYPT_GEN_RANDOM statement.
Answer: A 28.You work as a database administrator at manufacturing company named
ABC.com. ABC.com has a SQL Server 2012 database named ProductionDB. The
ProductionDB database has a table named Sites that was created using the
following Transact-SQL code:CREATE TABLE Sites (SiteID int NOT NULL
PRIMARY KEY,Location int NOT NULL,Manager nvarchar(200) NOT NULL,Week
smallint NOT NULL,ItemsProduced int NOT NULL )You want to write the
Transact-SQL query that returns the number of items produced at each
location for each week. In addition, you want the result set to include a
column named PrevItemsProduced that holds the number of items produced
at each location for the week before.What Transact SQL statement would
accomplish this task? A. SELECT Location, Week, ItemsProduced,LEAD(Items
Produced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems
Produced FROM Sites B. SELECT Location, Week,
ItemsProduced,FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location
ORDER BY Week) ASPrevItems Produced FROM Sites C. SELECT Location, Week,
ItemsProduced,LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER
BY Week) ASPrevItemsProduced FROM Sites D. SELECT Location, Week,
ItemsProduced,LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location
ORDER BY Week) ASPrevItemsProduced FROM Sites E. SELECT Location, Week, ItemsProduced,CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites Answer: C 29.You work as a database developer at ABC.com. ABC has an in-house
application named ABCApp3 that runs a Transact-SQL query against a SQL
Server 2012 database.You want to run an execution plan against the query
that will provide detailed information on missing indexes.How would you
accomplish this task? A. You should make use of the READPAST hint in the queries. B. You should make use of the READCOMMITTED hint in the queries. C. You should make use of the SET SHOWPLAN_XML ON statement in the query. D. You should make use of the SET STATISTICS XML ON statement in the query. E. You should make use of the SET XACT_ABORT OFF statement in the query. F. You should make use of the SET CONTEXT_INFO statement in the query. Answer: C 30.You develop a Microsoft SQL Server 2012 server database that supports
an application. The application contains a table that has the following
definition:
CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the
ItemsInStore and ItemsInWarehouse values for each row. Which
Transact-SQL statement should you use? A. ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse B. ALTER TABLE InventoryADD ItemsInStore – ItemsInWarehouse = TotalItems C. ALTER TABLE InventoryADD TotalItems = ItemsInStore + ItemsInWarehouse D. ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse); Answer: A 31.You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale
of six digits. Which data type should you use? A. Float B. Money C. Smallmoney D. Numeric Answer: D 32.You administer a Microsoft SQL Server database that supports a
banking transaction management application. You need to retrieve a list
of account holders who live in cities that do not have a branch
location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster) D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster) Answer: AB 33.You develop three Microsoft SQL Server 2012 databases named
Database1, Database2, and Database3. You have permissions on both
Database1 and Database2. You plan to write and deploy a stored procedure
named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must
execute other stored procedures in the other databases. You need to
ensure that callers that do not have permissions on Database1 or
Database2 can execute the stored procedure. Which Transact-SQL statement
should you use? A. USE Database2 B. EXECUTE AS OWNER C. USE Database1 D. EXECUTE AS CALLER Answer: B 34.You administer a Microsoft SQL Server 2012 database that has multiple
tables in the Sales schema. Some users must be prevented from deleting
records in any of the tables in the Sales schema. You need to manage
users who are prevented from deleting records in the Sales schema. You
need to achieve this goal by using the minimum amount of administrative
effort. What should you do? A. Create a custom database role that includes the
users. Deny Delete permissions on the Sales schema for the custom
database role. B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role. C. Deny Delete permissions on each table in the Sales schema for each user. D. Create a custom database role that includes the
users. Deny Delete permissions on each table in the Sales schema for the
custom database role. Answer: A 35.You develop a database for a travel application. You need to design
tables and other database objects. You need to store media files in
several tables. Each media file is less than 1 MB in size. The media
files will require fast access and will be retrieved frequently. What
should you do? A. Use the CAST function. B. Use the DATE data type. C. Use the FORMAT function. D. Use an appropriate collation. E. Use a user-defined table type. F. Use the VARBINARY data type. G. Use the DATETIME data type. H. Use the DATETIME2 data type. I. Use the DATETIMEOFFSET data type. J. Use the TODATETIMEOFFSET function.
Answer : I
36.You develop a Microsoft SQL Server 2012 server database that supports
an application.The application contains a table that has the following
definition:CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY
KEY,ItemsInStore int NOT NULL,ItemsInWarehouse int NOT NULL)You need to
create a computed column that returns the sum total of the ItemsInStore
and ItemsInWarehouse values for each row.Which Transact-SQL statement
should you use? A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse B. ALTER TABLE Inventory ADD ItemsInStore – ItemsInWarehouse = TotalItemss C. ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse); Answer: A 37.You develop a database for a travel application. You need to design
tables and other database objects.You create the Airline_Schedules
table.You need to store the departure and arrival dates and times of
flights along with time zone information.What should you do? A. Use the CAST function. B. Use the DATE data type. C. Use the FORMAT function. D. Use an appropriate collation. E. Use a user-defined table type. F. Use the VARBINARY data type. G. Use the DATETIME data type. H. Use the DATETIME2 data type. I. Use the DATETIMEOFFSET data type. J. Use the TODATETIMEOFFSET function. Answer : I 38.You have a Microsoft SQL Server 2012 database that contains tables
named Customers and Orders.The tables are related by a column named
CustomerID.You need to create a query that meets the following
requirements:Returns the CustomerName for all customers and the
OrderDate for any orders that they have placed.Results must include
customers who have not placed any orders.Which Transact-SQL query should
you use? A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID Answer : D 39.You develop a Microsoft SQL Server 2012 database. The database is
used by two web applications that access a table named Products.You want
to create an object that will prevent the applications from accessing
the table directly while still providing access to the required data.You
need to ensure that the following requirements are met:Future
modifications to the table definition will not affect the applications’
ability to access data.The new object can accommodate data retrieval and
data modification.You need to achieve this goal by using the minimum
amount of changes to the existing applications.What should you create
for each application? A. views B. table partitions C. table-valued functions D. stored procedures
Answer: A 40.Question : 31
You develop a Microsoft SQL Server 2012 database.You need to create a
batch process that meets the following requirements:Returns a result
set based on supplied parameters.Enables the returned result set to
perform a join with a table.Which object should you use? A. Inline user-defined function B. Stored procedure C. Table-valued user-defined function D. Scalar user-defined function
You have three tables that contain data for
vendors, customers, and agents. You create a view that is used to look
up telephone numbers for these companies. The view has the following
definition:
You need to ensure that users can update only the phone numbers by using this view. What should you do?
A
Create an AFTER UPDATE trigger on the view.
B
Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C
Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
D
Create an INSTEAD OF UPDATE trigger on the view.
You are a database developer at an independent
software vendor. You create stored procedures that contain proprietary
code. You need to protect the code from being viewed by your customers.
Which stored procedure option should you use?
A
ENCRYPTION
B
ENCRYPTBYCERT
C
ENCRYPTBYKEY
D
ENCRYPTBYPASSPHRASE
Your database contains tables named Products and
ProductsPriceLog. The Products table contains columns named ProductCode
and Price. The ProductsPriceLog table contains columns named
ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores
the previous price in the OldPrice column and the new price in the
NewPrice column. You need to increase the values in the Price column of
all products in the Products table by 5 percent. You also need to log
the changes to the ProductsPriceLog table. Which Transact-SQL query
should you use?
A
UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price *
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B
UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;
SELECT ProductCode, Price, Price * 1.05 FROM Products
C
UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D
UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
You support a database structure shown in the exhibit. (Click the Exhibit button.)
You
need to write a query that displays the following details: Total sales
made by sales people, year, city, and country Sub totals only at the
city level and country level
A grand total of the sales amount
Which Transact-SQL query should you use?
A
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
B
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
C
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy,
SaleDate)), (Country, City), (Country), ())
D
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate
the
unique values of the ProductName field of a table-valued expression
into multiple columns in the output. Which Transact-SQL operator
should you use?
A
UNPIVOT
B
PIVOT
C
CROSS JOIN
D
CROSS APPLY
You administer a Microsoft SQL Server 2012 database
that has multiple tables in the Sales schema. Some users must be
prevented from deleting records in any of the tables in the Sales
schema. You need to manage users who are prevented from deleting
records in the Sales schema. You need to achieve this goal by using the
minimum amount of administrative effort. What should you do?
A
Create a custom database role that includes the users. Deny Delete
permissions on the Sales schema for the custom database role.
B
Create a custom database role that includes the users. Deny Delete permissions on each table in the
Sales schema for the custom database role.
C
Deny Delete permissions on each table in the Sales schema for each user.
D
Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:
Id is the Primary Key.
You
need to append the "This is in a draft stage" string to the Summary
column of the recent 10 entries based on the values in EntryDateTime.
Which Transact-SQL statement should you use?
A
UPDATE BlogEntry
SET Summary.WRITE(N' This is in a draft stage', 0, 0)
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
B
UPDATE BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM (
SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s
WHERE BlogEntry.Id = s.ID
C
UPDATE BlogEntry
SET Summary = CAST(N' This is in a draft stage' as nvarchar(max))
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
D
UPDATE TOP(10) BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0)
Your database contains two tables named
DomesticSalesOrders and InternationalSalesOrders. Both tables contain
more than 100 million rows. Each table has a Primary Key column named
SalesOrderId. The data
in the two tables is distinct from one
another. Business users want a report that includes aggregate
information about the total number of global sales and total sales
amounts. You need to ensure that your query executes in the minimum
possible time. Which query should you use?
A
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
B
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
C
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
D
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
You create a stored procedure that will update
multiple tables within a transaction. You need to ensure that if the
stored procedure raises a run-time error, the entire transaction is
terminated and rolled back. Which Transact-SQL statement should you
include at the beginning of the stored procedure?
A
SET XACT_ABORT ON
B
SET ARITHABORT OFF
C
BEGIN
D
SET ARITHABORT ON
E
TRY
F
SET XACT_ABORT OFF
You use Microsoft SQL Server 2012 database to
develop a shopping cart application. You need to invoke a table-valued
function for each row returned by a query. Which Transact-SQL operator
should you use?
A
PIVOT
B
CROSS APPLY
C
CROSS JOIN
D
UNPIVOT
You create a table that has the StudentCode,
SubjectCode, and Marks columns to record mid-year marks for students.
The table has marks obtained by 50 students for various subjects. You
need to retrieve the students who scored the highest marks for each
subject along with the marks. Which Transact-SQL query should you use?
A
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
B
SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks
C
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
D
SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
E
SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
F
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
G
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
H
SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
You develop a database for a travel application.
You need to design tables and other database objects. You create a view
that displays the dates and times of the airline schedules on a
report. You need to display dates and times in several international
formats. What should you do
A
Use a user-defined table type.
B
Use the TODATETIMEOFFSET function.
C
Use the VARBINARY data type.
D
Use the DATETIMEOFFSET data type.
E
Use the DATE data type.
F
Use the DATETIME data type.
G
Use the FORMAT function.
H
Use the CAST function.
I
Use an appropriate collation.
J
Use the DATETIME2 data type.
You administer a Microsoft SQL Server 2012 database
named ContosoDb. Tables are defined as shown in the exhibit. (Click
the Exhibit button.)
You
need to display rows from the Orders table for the Customers row
having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
A
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER
JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE
Customers. CustomerId = 1
FOR XML RAW
B
SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER
JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers. CustomerId= 1
FOR XML AUTO, ELEMENTS
C
SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')
D
SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate,
Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId=
Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')
E
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER
JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE
Customers. CustomerId= 1
FOR XML AUTO, ELEMENTS
F
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER
JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers=CustomerId = 1
FOR XML RAW, ELEMENTS
G
SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER
JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers. CustomerId= 1
FOR XML AUTO
H
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER
JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE
Customers. CustomerId = 1
FOR XML AUTO
You develop a Microsoft SQL Server 2012 database
that contains a heap named OrdersHistoncal. You write the following
Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?
A
TABLOCK
B
UPDLOCK
C
HOLDLOCK
D
ROWLOCK
E
XLOCK
You have a Microsoft SQL Server 2012 database that
contains tables named Customers and Orders. The tables are related by
a column named CustomerID. You need to create a query that meets the
following requirements:
Returns the CustomerName for all customers
and the OrderDate for any orders that they have placed. Results must
include customers who have not placed any orders.
Which Transact-SQL query should you use?
A
SELECT CustomerName, OrderDate
FROM Customers
CROSS JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
B
SELECT CustomerName, OrderDate
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
C
SELECT CustomerName, CrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
D
SELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
You develop a Microsoft SQL Server 2012 database.
You
need to create and call a stored procedure that meets the following
requirements: Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which
Transact-SQL statement or statements should you use? (Each correct
answer presents part of the solution. Choose all that apply.)
A
CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result
GO
B
CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT AS
DECLARE @Result INT SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerVaLue
FROM Customers
WHERE Customer= = @CustomerID RETURNS @Result
C
CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS
SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomerID RETURN
GO
DECLARE @customerRatingBycustomer INT DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745
, @CustomerRatingSyCustomer
F
EXECUTE dbo.GetCustomerRatIng 1745
You administer a Microsoft SQL Server 2012 database
named ContosoDb. The database contains a table named Suppliers and a
column named IsActive in the Purchases schema. You create a new user
named ContosoUser in ContosoDb. ContosoUser has no permissions to the
Suppliers table. You need to ensure that ContosoUser can delete rows
that are not active from Suppliers. You also need to grant ContosoUser
only the minimum required permissions. Which Transact-SQL statement
should you use?
A
GRANT SELECT ON Purchases.Suppliers TO ContosoUser
B
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
C
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
D
GRANT DELETE ON Purchases.Suppliers TO ContosoUser
You are developing a database application by using
Microsoft SQL Server 2012. An application that uses a database begins
to run slowly. You discover that during reads, the transaction
experiences blocking from concurrent updates. You need to ensure that
throughout the transaction the data maintains the original version. What
should you do?
A
Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
B
Cover the unique clustered index with a columnstore index.
C
Include a SET FORCEPLAN ON statement before you run the query.
D
Add a HASH hint to the query.
E
Include a SET STATISTICS PROFILE ON statement before you run the query.
F
Add an INCLUDE clause to the index.
G
Add a FORCESCAN hint to the Attach query.
H
Add a FORCESEEK hint to the query.
I
Enable the optimize for ad hoc workloads option.
J
Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
K
Add a LOOP hint to the query.
L
Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M
Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
N
Add a columnstore index to cover the query.
You develop a Microsoft SQL Server 2012 server
database that supports an application. The application contains a
table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You
need to create a computed column that returns the sum total of the
ItemsInStore and ItemsInWarehouse values for each row. The new column
is expected to be queried heavily, and you need to be able to index
the column. Which Transact-SQL statement should you use?
A
ALTER TABLE Inventory
ADD TotalItems AS ItemslnStore + ItemsInWarehouse
B
ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
C
ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
D
ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
You develop a Microsoft SQL Server 2012 database.
You create a view that performs the following tasks: Joins 8 tables
that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?
A
Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
B
Convert the view into an indexed view.
C
Convert the view into a Common Table Expression (CTE).
D
Convert the view into a table-valued function.
You administer a Microsoft SQL Server 2012 database
named ContosoDb. Tables are defined as shown in the exhibit. (Click
the Exhibit button.)
You
need to display rows from the Orders table for the Customers row
having the CustomerId value set to 1 in the following XML format:
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
B
SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate,
Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId=
Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')
C
SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId- 1
FOR XML AUTO
D
SELECT Name, Country, Orderld, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS
E
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW
F
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
G
SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')
H
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.
CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS
You generate a daily report according to the following query:
You need to improve the performance of the query. What should you do?
A
Drop the UDF and rewrite the report query as follows:
SELECT c.CustomerName FROM Sales.Customer c WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)
B
Rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.
CustomerID, 90))
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) RETURNS TABLE AS RETURN (
SELECT OrderDate
FROM Sales.SalesOrder
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())
C
Drop the UDF and rewrite the report query as follows:
SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())
D
Drop the UDF and rewrite the report query as follows:
WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())
You use Microsoft SQL Server 2012 to write code for
a transaction that contains several statements. There is high
contention between readers and writers on several tables used by your
transaction. You need to minimize the use of the tempdb space. You also
need to prevent reading queries from blocking writing queries. Which
isolation level should you use?
A
READ COMMITTED SNAPSHOT
B
REPEATABLE READ
C
SERIALIZABLE
D
SNAPSHOT
You develop a Microsoft SQL Server 2012 database
that has two tables named SavingAccounts and LoanAccounts. Both tables
have a column named AccountNumber of the nvarchar data type. You use a
third table named Transactions that has columns named TransactionId
AccountNumber, Amount, and
TransactionDate. You need to ensure that
when multiple records are inserted in the Transactions table, only the
records that have a valid AccountNumber in the SavingAccounts or
LoanAccounts are inserted. Which Transact-SQL statement should you use?
A
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END
B
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END
C
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END
D
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN END
END
You use Microsoft SQL Server 2012 to create a
stored procedure as shown in the following code segment. (Line numbers
are included for reference only.)
The
procedure can be called within other transactions. You need to ensure
that when the DELETE statement from the HumanResourcesJobCandidate
table succeeds, the modification is retained even if the insert into the
Audit.Log table fails. Which code segment should you add to line 14?
A
IF (XACT_STATE ( ) ) = 0
B
IF (XACT_STATE ( ) ) = 1
C
IF @@TRANCOUNT = l
D
IF @@TRANCOUNT = 0
You develop a Microsoft SQL Server 2012 server
database that supports an application. The application contains a
table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You
need to create a computed column that returns the sum total of the
ItemsInStore and ItemsInWarehouse values for each row. The new column
is expected to be queried heavily, and you need to be able to index
the column. Which Transact-SQL statement should you use?
A
ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
B
ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
C
ALTER TABLE Inventory
ADD TotalItems AS ItemslnStore + ItemsInWarehouse
D
ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
You are developing a database that will contain
price information. You need to store the prices that include a fixed
precision and a scale of six digits. Which data type should you use?
A
Smallmoney
B
Numeric
C
Money
D
Float
You have three tables that contain data for
vendors, customers, and agents. You create a view that is used to look
up telephone numbers for these companies. The view has the following
definition:
You need to ensure that users can update only the phone numbers by using this view. What should you do?
A
Create an INSTEAD OF UPDATE trigger on the view.
B
Create an AFTER UPDATE trigger on the view.
C
Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
D
Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
You develop a Microsoft SQL Server 2012 database
that contains a table named Products. The Products table has the
following definition:
You
need to create an audit record only when either the RetailPrice or
WholeSalePrice column is updated. Which Transact-SQL query should you
use?
A
CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR
EXISTS (SELECT WholeSalePnce FROM inserted)
- - Create Audit Records
B
CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice)
- - Create Audit Records
C
CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice)
- - Create Audit Records
D
CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice)
- - Create Audit Records
You develop three Microsoft SQL Server 2012
databases named Database1, Database2, and Database3. You have
permissions on both Database1 and Database2. You plan to write and
deploy a stored procedure named dbo.usp_InsertEvent in Database3.
dbo.usp_InsertEvent must execute other stored procedures in the other
databases. You need to ensure that callers that do not have
permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?
A
USE Database2
B
EXECUTE AS OWNER
C
EXECUTE AS CALLER
D
USE Database1
You develop a Microsoft SQL Server 2012 database.
You
need to create and call a stored procedure that meets the following
requirements: Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which
Transact-SQL statement or statements should you use? (Each correct
answer presents part of the solution. Choose all that apply.)
A
CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS
SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomerID RETURN
GO
B
EXECUTE dbo.GetCustomerRatIng 1745
C
DECLARE @customerRatingBycustomer INT DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745
, @CustomerRatingSyCustomer
CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT AS
DECLARE @Result INT SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerVaLue
FROM Customers
WHERE Customer= = @CustomerID RETURNS @Result
F
CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result
GO
A table named Profits stores the total profit made
each year within a territory. The Profits table has columns named
Territory, Year, and Profit. You need to create a report that
displays the profits made by each territory for each year and its
preceding year. Which Transact-SQL query should you use?
A
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits
B
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits
C
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits
D
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits
Your database contains two tables named
DomesticSalesOrders and InternationalSalesOrders. Both tables contain
more than 100 million rows. Each table has a Primary Key column named
SalesOrderId. The data
in the two tables is distinct from one
another. Business users want a report that includes aggregate
information about the total number of global sales and total sales
amounts. You need to ensure that your query executes in the minimum
possible time. Which query should you use?
A
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
B
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
C
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
D
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
You use Microsoft SQL Server 2012 database to
develop a shopping cart application. You need to invoke a table-valued
function for each row returned by a query. Which Transact-SQL operator
should you use?
A
UNPIVOT
B
PIVOT
C
CROSS APPLY
D
CROSS JOIN
You develop a database for a travel application.
You need to design tables and other database objects. You create a
stored procedure. You need to supply the stored procedure with multiple
event names and their dates as parameters. What should you do?
A
Use the DATE data type.
B
Use the CAST function.
C
Use an appropriate collation.
D
Use the DATETIMEOFFSET data type.
E
Use the VARBINARY data type.
F
Use the TODATETIMEOFFSET function.
G
Use a user-defined table type.
H
Use the DATETIME2 data type.
I
Use the DATETIME data type.
J
Use the FORMAT function.
You administer a Microsoft SQL Server database that
supports a shopping application. You need to retrieve a list of
customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct
answer presents a complete solution. Choose all that apply.)
A
SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
B
SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
C
SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D
SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button).
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.
You are the database developer at ABC.com.
ABC.com has a SQL Server 2012 database infrastructure that has a
database named ComDB with a table named Partners.
The Partners table was created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners] ( [CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL, [Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL, [Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL )
You develop a new table named Events using the following Transact-SQL code:
CREATE TABLE [dbo].[Events] ( [EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL, [EventDescription] [varchar] (2500),
[EventCordinator] [varchar] (150) NOT NULL )
How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?
A
You should add a Check Constraint on the Events table.
B
You should change the Events table to a partitioned table.
C
You should add a Unique Constraint on the Events table.
D
You should specify Events.CompanyID as a spars column.
E
You should add a Foreign Key Constraint on the Events table.
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You deploy a new server that has SQL Server 2012 installed. You need to create a table named
Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.
Which code segment should you use?
To answer, type the correct code in the answer area.
You administer a Microsoft SQL Server 2012
database that has Trustworthy set to On. You create a stored procedure
that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure. You need to
ensure that the stored procedure returns the required information when
User1 executes the stored procedure. You need to achieve this goal by
granting the minimum permissions required. What should you do? (Each
correct answer presents a complete solution. Choose all that apply.)
A
Grant the sysadmin role on the database to User1.
B
Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.
C
Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D
Grant the db_owner role on the database to User1.
E
Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
You work as a database administrator at
ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The
SalesDB database is shown in the following database diagram:
You create a view on the SalesDB using the following Transact-SQL code:
CREATE VIEW SalesV WITH SCHEMABINDINGS AS SELECT Products.ProductID,
Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price FROM OrderDetail
INNER JOIN Products ON derDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate GO
How should you alter this view to allow users to update data through the SalesV?
A
You should add a clustered index to the SalesV view.
B
You should add an INSTEAD OF trigger to the SalesV view.
C
You should add an AFTER UPDATE trigger to the SalesV view.
D
You should add a CHECK constraint to the SalesV view.
E
Create a columnstore index on all columns used in the SalesV view.
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You need to create a query that calculates the total sales of each
OrderId from the Sales.Details table. The solution must meet the
following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use? To answer, type the correct code in the answer area.
You are developing a database application
by using Microsoft SQL Server 2012. An application that uses a database
begins to run slowly. You discover that a large amount of memory is
consumed by single-use dynamic queries. You need to reduce procedure
cache usage from these statements without creating any additional
indexes. What should you do?
A
Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
B
Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
C
Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
D
Enable the optimize for ad hoc workloads option.
E
Add a columnstore index to cover the query.
F
Cover the unique clustered index with a columnstore index.
G
Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
H
Add a LOOP hint to the query.
I
Add an INCLUDE clause to the index.
J
Add a FORCESCAN hint to the Attach query.
K
Include a SET STATISTICS PROFILE ON statement before you run the query.
L
Add a HASH hint to the query.
M
Include a SET FORCEPLAN ON statement before you run the query.
N
Add a FORCESEEK hint to the query.
You create a view based on the following statement:
You grant the Select permission to User1.
You need to change the view so that it displays only the records that
were processed in the month prior to the current month. You need to
ensure that after the changes, the view functions correctly for User1.
Which Transact-SQL statement should you use?
A
Option B
B
Option C
C
Option D
D
Option A
You have three tables that contain data
for dentists, psychiatrists, and physicians. You create a view that is
used to look up their email addresses and phone numbers. The view has
the following definition:
You need to ensure that users can update only the phone numbers and email addresses by using this view.
What should you do?
A
Create an INSTEAD OF UPDATE trigger on the view.
B
Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C
Create an AFTER UPDATE trigger on the view.
D
Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement
You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:
You need to create a non-clustered index on the SalesOrderID column in
the OrderDetail table to include only rows that contain a value in the
SpecialOfferID column. Which four Transact-SQL statements should you
use? (To answer, move the appropriate statements from the list of
statements to the answer area and arrange them in the correct order.)
Select and Place:
A
You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:
You need to create a non-clustered index on the SalesOrderID column in
the OrderDetail table to include only rows that contain a value in the
SpecialOfferID column. Which four Transact-SQL statements should you
use?
(To answer, move the appropriate statements from the list of statements
to the answer area and arrange them in the correct order.)
Build List and Reorder:
A
You are developing a database application
by using Microsoft SQL Server 2012. An application that uses a database
begins to run slowly. You discover that during reads, the transaction
experiences blocking from concurrent updates. You need to ensure that
throughout the transaction the data maintains the original version.
What should you do?
A
Add an INCLUDE clause to the index.
B
Enable the optimize for ad hoc workloads option.
C
Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
D
Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
E
Add a HASH hint to the query.
F
Cover the unique clustered index with a columnstore index.
G
Include a SET STATISTICS PROFILE ON statement before you run the query.
H
Add a columnstore index to cover the query.
I
Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
J
Include a SET FORCEPLAN ON statement before you run the query.
K
Add a LOOP hint to the query.
L
Add a FORCESCAN hint to the Attach query.
M
Add a FORCESEEK hint to the query.
N
Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
You work as a database developer at
ABC.com. You want to create a Transact-SQL query will call a
table-valued function for every row the query returns.
How would you accomplish this task?
A
You should make use of a CONVERT function.
B
You should make use of a CROSS APPLY.
C
You should make use of a CAST function.
D
You should make use of an INNER JOIN.
E
You should make use of a Trigger.
F
You should make use of the FORMAT function.
G
You should make use of an OUTER JOIN.
H
You should make use of a UNION.
You use Microsoft SQL Server 2012 to
develop a database application. You create two tables by using the
following table definitions.
Which six Transact-SQL statements should you use? (To answer, move the
appropriate SQL statements from the list of statements to the answer
area and arrange them in the correct order.)
Select and Place:
A
You work as a database administrator at
manufacturing company named ABC.com. ABC.com has a SQL Server 2012
database named ProductionDB. The ProductionDB database has a table named
Sites that was created using the following Transact-SQL code:
CREATE TABLE Sites (
SiteID int NOT NULL PRIMARY KEY,
Location int NOT NULL,
Manager nvarchar(200) NOT NULL,
Week smallint NOT NULL,
ItemsProduced int NOT NULL )
You want to write the Transact-SQL query that returns the number of
items produced at each location for each week. In addition, you want the
result set to include a column named
PrevItemsProduced that holds the number of items produced at each location for the week before.
What Transact SQL statement would accomplish this task?
A
SELECT Location, Week, ItemsProduced, LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced FROM Sites
B
SELECT Location, Week, ItemsProduced, FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced FROM Sites
C
SELECT Location, Week, ItemsProduced, CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
D
SELECT Location, Week, ItemsProduced, LEAD(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced FROM Sites
E
SELECT Location, Week, ItemsProduced, LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced FROM Sites
You work as a Database Administrator (DBA)
at ABC.com. The company uses a Microsoft SQL Server 2012 environment
and has recently deployed SQL Server 2012 Reporting Services (SSRS).
The company will eventually use hundreds of SSRS reports. All the
reports will use the standard corporate branding, fonts and text styles.
Many of the reports will also use common functions that will be coded
by developers.
You need to recommend a reporting strategy that enables all reports to
use the corporate branding and fonts and the function code. You need to
be able to modify the corporate branding and function code if required
without recreating all the reports.
Where should the corporate branding, fonts and text styles and the function code should be stored?
A
The corporate branding, fonts and text styles and the function code should be stored in a web service.
B
The corporate branding, fonts and text styles and the function code should be stored in a report template.
C
The corporate branding, fonts and text styles and the function code should be stored in a SQL Server database.
D
The corporate branding, fonts and text styles and the function code should be stored in an assembly on an SSRS server.
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You have an application named App1. You have a parameter named @Count
that uses the int data type. App1 is configured to pass @Count to a
stored procedure. You need to create a stored procedure named
usp_Customers for App1. usp_Customers must meet the following
requirements:
NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.
A
Failed question
B
Passed question
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You need to create a query that calculates the total sales of each
OrderId from the Sales.Details table. The solution must meet the
following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use? To answer, type the correct code in the answer area.
A
Please review the explanation part for this answer
You work as a database administrator at
ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The
ProductsDB database is shown in the following database diagram:
You need to write a Transact-SQL query that displays all the products received by a single supplier in the following XML format:
Which of the following SELECT statement would you write?
A
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO, RAW
B
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO
C
SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID, p.UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID WHERE s.SupplierID = 22 FOR XML RAW
D
SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID, p.UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID WHERE s.SupplierID = 22 FOR XML
You work as a SQL Server 2012 database
administrator at ABC.com. You have developing a database named WebAnDB
that will be used by a web site analysis application. The WebAnDB
database has a table named Visitors that stores date and time data in a
column named Accessed.
You must now develop a stored procedure that will insert data into the
Accessed column. You want the stored procedure to store time zone data
as well.
How would you accomplish this?
A
You should make use of a cursor.
B
You should make use of the SET FORCEPLAN ON statement in the stored procedure.
C
You should make use of the SET CONTEXT_INFO statement in the stored procedure.
D
You should make use of a scalar user-defined function.
E
You should make use of the TODATETIMEOFFSET function.
F
You should make use of the DATETIMEOFFSET data type.
You administer a Microsoft SQL Server 2012
database that includes a table named Products. The Products table has
columns named Productld, ProductName, and CreatedDateTime. The table
contains a unique constraint on the combination of ProductName and
CreatedDateTime. You need to modify the Products table to meet the
following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?
A
WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime)
AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName
HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte
ON p.ProductName = cte.ProductName
B
WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime)
AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName
HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte
ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime
C
WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime)
AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName
HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte
ON p.ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime
D
WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime)
AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName )
DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName
You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB.
You are developing a stored procedure that takes a parameter named @date
that uses the varchar datatype. The @date parameter must be compared to
the value in a datetime column named OrderDate.
Which of the following WHERE clauses would be the most efficient WHERE clause to use?
A
WHERE OrderDate = CAST(datetime,@date)
B
WHERE OrderDate =@date
C
WHERE OrderDate = PARSE(@date AS Date)
D
WHERE OrderDate = CONVERT(datetime,@date)
E
WHERE OrderDate = CAST(@date AS datetime)
You administer a Microsoft SQL Server 2012
database. The database contains a Product table created by using the
following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table.
What should you do?
A
Implement row-level compression.
B
Implement Unicode Compression.
C
Implement page-level compression.
D
Convert all indexes to Column Store indexes.
Question #
You develop a Microsoft SQL Server 2012 database. You create a
view from the Orders and OrderDetails tables by using the following
definition:
CREATE VIEW vOrders WITH SCHEMABINDING
AS SELECT o.ProductID, o.OrderDate,
SUM(od.UnitPrice, * o.OrderQTY)
AS Amount FROM OrderDetails AS od
INNER JOIN Orders AS o
ON od.OrderID = o.OrderID
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY o.OrderDate, o.ProductID
GO
You need to improve the performance of the view by persisting data to disk. What should you do?
A
Create an INSTEAD OF trigger on the view.
B
Create an AFTER trigger on the view.
C
Modify the view to use the WITH VIEW_METADATA clause.
D
Create a clustered index on the view.
You develop a
Microsoft SQL Server 2012 server database that supports an application.
The application contains a table that has the following definition:
CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the
ItemsInStore and ItemsInWarehouse values for each row. Which
Transact-SQL statement should you use?
A
ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
B
ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItems
C
ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
D
ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);
You develop a
database for a travel application. You need to design tables and other
database objects. You create the Airline_Schedules table. You need to
store the departure and arrival dates and times of flights along with
time zone information. What should you do?
A
Use the CAST function.
B
Use the DATE data type.
C
Use the FORMAT function.
D
Use an appropriate collation.
E
Use a user-defined table type.
F
Use the VARBINARY data type.
G
Use the DATETIME data type.
H
Use the DATETIME2 data type.
I
Use the DATETIMEOFFSET data type.
J
Use the TODATETIMEOFFSET function.
You develop a
database for a travel application. You need to design tables and other
database objects. You create a stored procedure. You need to supply the
stored procedure with multiple event names and their dates as
parameters. What should you do?
A
Use the CAST function.
B
Use the DATE data type.
C
Use the FORMAT function.
D
Use an appropriate collation.
E
Use a user-defined table type.
F
Use the VARBINARY data type.
G
Use the DATETIME data type.
H
Use the DATETIME2 data type.
I
Use the DATETIMEOFFSET data type.
J
Use the TODATETIMEOFFSET function.
You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo. ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?
A
BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH
B
BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
C
BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH
D
BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?
A
Convert the view into an indexed view.
B
Convert the view into a table-valued function.
C
Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
D
Convert the view into a Common Table Expression (CTE).
You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column. Retain only the newest Products row.
Which Transact-SQL query should you use?
A
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
B
WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
C
WITH CTEDupRecords
AS (
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
AND p.CreatedDateTime > cte.CreatedDateTime
D
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
cte.ProductName = p.ProductName
AND cte.CreatedDateTime > p.CreatedDateTime
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
Status information must be logged to a status table.
If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?
A
Scalar user-defined function
B
Table-valued user-defined function
C
Inline user-defined function
D
Stored procedure
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?
A
SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
B
SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
C
SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks
D
SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
E
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
F
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
G
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
H
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?
A
Reference a user-defined function within the computed column.
B
Add a default constraint to the computed column that implements hard-coded values.
C
Create a BEFORE trigger that maintains the state of the computed column.
D
Add a default constraint to the computed column that implements hard-coded CASE statements.
You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
Always starts with 1.
Starts again from 1 after it reaches 100. Which Transact-SQL statement should you use?
A
CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100
CYCLE
UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence
B
CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1
DROP SEQUENCE CustomerSequence
C
CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence
D
CREATE SEQUENCE CustomerSequence AS int
START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence
You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?
A
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
B
SET IMPLICIT_TRANSACTIONS ON
C
SET XACT_ABORT OFF
D
SET IMPLICIT TRANSACTIONS OFF
E
SET XACT_ABORT ON
F
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?
A
DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(ERRORSTATE() AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
GO
B
DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
GO
C
EXEC DeleteJobCandidate
IF (ERROR_STATE() != 0)
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO
D
EXEC DeleteJobCandidate
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO
You need to write a query that displays the following details: Total sales made by sales people, year, city, and country Sub totals only at the city level and country level
A grand total of the sales amount
Which Transact-SQL query should you use?
A
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
B
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
C
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
D
SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy,
SaleDate)), (Country, City), (Country), ())
You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?
A
CREATE TABLE DocumentStore AS FileTable
B
CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL
)
GO
C
CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL
)
GO
D
CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO
Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each
employee that will execute the stored procedure based on the country of residence. Which approach should you use?
A
An UPDATE statement that includes CASE
B
a recursive stored procedure
C
Trigger
D
Cursor
E
The foreach SQLCLR statement
You
administer a Microsoft SQL Server 2012 database. The database contains a table
named Employee. Part of the Employee table is shown in the exhibit. (Click the
Exhibit button.)
Confidential information about the employees is stored in a separate
table named EmployeeData. One record exists within EmployeeData for each record
in the Employee table. You need to assign the
appropriate constraints and table properties to ensure data integrity and
visibility. On which column in the
Employee table should you a create a unique constraint?
A
MiddleName
B
FirstName
C
LastName
D
DepartmentID
E
EmployeelD
F
DateHired
G
EmployeeNum
H
JobTitle
I
ReportsToID
You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and
SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)
You
write the following Transact-SQL query:
You
discover that the performance of the query is slow. Analysis of the query plan
shows table scans where the estimated rows do not match the actual rows for
SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to
improve the performance of the query. What should you do?
A
Add a clustered index on SalesOrderId in
SalesOrderHeader.
B
Use a FORCESEEK hint in the query.
C
Update statistics on SalesOrderId on both tables.
D
Use a FORCESCAN hint in the query.
You are a
database developer of a Microsoft SQL Server 2012 database. The database
contains a table named Customers that has the following definition:
You need
to ensure that the CustomerId column in the Orders table contains only values
that exist in the
CustomerId column of the Customer table. Which Transact-SQL statement should
you use?
You
develop a Microsoft SQL Server 2012 database that contains tables named
Employee and Person. The tables have the following definitions:
You
create a view named VwEmployee as shown in the following Transact-SQL statement
Users are
able to use single INSERT statements or INSERT...SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert
records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?
A
CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName)
INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID,
@EmployeeNumber
End
B
CREATE TRIGGER TrgVwEmployee
ON VwEmployee
FOR INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM
inserted
END
C
CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM VwEmployee
INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM
VwEmployee
End
D
CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM
inserted
END
You
are a database developer of a Microsoft SQL Server 2012 database. You are
designing a table that will store Customer data from different sources. The
table will include a column that contains the CustomerID from the source system
and a column that contains the SourceID. A sample of this data is as shown in
the following table
You need
to ensure that the table has no duplicate CustomerID within a SourceID. You
also need to ensure that the data in the table is in the order of SourceID and
then CustomerID. Which Transact- SQL statement should you use?
A
CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
B
CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY
CLUSTERED (SourceID, CustomerID));
C
CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);
D
CREATE TABLE Customer
(SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);
You are a
database developer for an application hosted on a Microsoft SQL Server 2012
server. The database contains two tables that have the following definitions:
Global
customers place orders from several countries. You need to view the country
from which each customer has placed the most orders. Which Transact-SQL query
do you use?
A
SELECT c.CustomerID, c.CustomerName,
o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC
B
SELECT c.CustomerID, c.CustomerName,
o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
C
SELECT c.CustomerID, c.CustomerName,
o.ShippingCountry
FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs
WHERE Rnk = 1
D
SELECT c.CustomerID, c.CustomerName,
o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.
BookGUID will have a constraint to ensure that it always has a value
when new rows are inserted into dbo.Book. You need to ensure that the
new column is assigned a GUID for existing rows. Which four Transact-
SQL statements should you use? (To answer, move the appropriate SQL
statements from the list of statements to the answer area and arrange
them in the correct order.)
Build List and Reorder:
A
Which of the following datatypes has a fixed precision and a scale of six digits?
A
Money
B
SmallInt
C
Numeric
D
VarInt
E
Double
F
Float
G
Int
You administer a Microsoft SQL Server 2012
database named ContosoDb. Tables are defined as shown in the exhibit.
(Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row
having the CustomerId value set to 1 in the following XML format.
Which Transact-SQL query should you use?
A
SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM
Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML PATH ('Customers')
B
SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN
Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
C
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId = Customers.CustomerId WHERE
Customers.CustomerId = 1 FOR XML AUTO
D
SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount FROM Orders INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML PATH ('Customers')
E
SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN
Customers ON Orders.CustomerId= Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML AUTO
F
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS
G
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId - Customers.CustomerId WHERE
Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
H
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN
Customers ON Orders.CustomerId = Customers-CustomerId WHERE
Customers.CustomerId = 1 FOR XML RAW
You administer a Microsoft SQL Server 2012
server. You plan to deploy new features to an application. You need to
evaluate existing and potential clustered and non-clustered indexes that
will improve performance.
What should you do?
A
Query the sys.dm_db_index_usage_stats DMV.
B
Query the sys.dm_db_missing_index_details DMV.
C
Use the Database Engine Tuning Advisor.
D
Query the sys.dm_db_missing_index_columns DMV.
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You have the following query:
You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.
A
Passed question
B
Failed question
You administer a Microsoft SQL Server 2012
database named ContosoDb. Tables are defined as shown in the exhibit.
(Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row
having the CustomerId value set to 1 in the following XML format:
SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML AUTO
B
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
C
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
D
SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
E
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML RAW
F
SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
G
SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
H
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1 FOR XML AUTO
One of the cube requirements states that the cube should include aggregation designs.
How should you meet this requirement?
A
By using the CREATE MINING MODEL statement.
B
By running the Usage-Based Optimization Wizard.
C
By dividing the cube into monthly partitions.
D
By using the CREATE SET statement.
E
By using the Aggregation Design Wizard.
You need to create a view named
uv_CustomerFullNames. The view must prevent the underlying structure of
the customer table from being changed. Part of the correct T-SQL
statement has been provided in the answer area. Provide the complete
code.
A
Please review the explanation part for this answer
You use Microsoft SQL Server 2012 to
develop a database application. You create a stored procedure named
DeleteJobCandidate. You need to ensure that if DeleteJobCandidate
encounters an error, the execution of the stored procedure reports the
error number. Which Transact-SQL statement should you use?
A
EXEC DeleteJobCandidate PRINT N'Error = ' +
CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' +
CAST(@@ROWCOUNT AS NVARCHAR(8));
GO
B
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate SELECT @ErrorVar = ERROR_STATE(),
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(ERRORSTATE()
AS NVARCHAR(8)) + N', Rows Deleted = ' +
CAST(@@RowCountVar AS NVARCHAR(8));
GO
C
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR,
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
GO
D
EXEC DeleteJobCandidate IF (ERROR_STATE() != 0)
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO
You use Microsoft SQL Server 2012 to
develop a database application. Your application sends data to an
NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL
statement that will find out the success of a cast to a decimal (36,9).
Which code segment should you use?select
A
SELECT CASE WHEN convert(decimal(36,9),
@var) IS NULL THEN 'True' ELSE 'False' END AS BadCast
B
BEGIN TRY SELECT convert(decimal(36,9), @var)
AS Value, 'True' AS BadCast END TRY BEGIN CATCH
SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast END CATCH
C
TRY( SELECT convert(decimal(36,9), @var)
SELECT 'True' AS BadCast ) CATCH( SELECT 'False' AS BadCast )
D
SELECT IF(TRY_PARSE(@var AS decimal(36,9))
IS NULL, 'True', 'False') AS BadCast
You work as a database administrator at
ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The
SalesDB is shown in the following database diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
Which of the following SELECT statement would you write?
A
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML
B
SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID WHERE cu.CustomerID = 1001 FOR XML RAW
C
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML AUTO, RAW
D
SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value],
Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML AUTO
You are the database developer at ABC.com.
ABC.com has a SQL Server 2012 database infrastructure that has a
database named ComDB with a table named Partners.
The Partners table was created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners] ( [CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL, [Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL, [Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL )
You develop a new table named Events using the following Transact-SQL code:
CREATE TABLE [dbo].[Events] ( [EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL, [EventDescription] [varchar] (2500),
[EventCordinator] [varchar] (150) NOT NULL )
How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?
A
You should specify Events.CompanyID as a spars column.
B
You should add a Unique Constraint on the Events table.
C
You should add a Check Constraint on the Events table.
D
You should add a Foreign Key Constraint on the Events table.
E
You should change the Events table to a partitioned table.
You use a Microsoft SQL Server 2012
database. You need to create an indexed view within the database for a
report that displays Customer Name and the total revenue for that
customer. Which four T-SQL statements should you use? (To answer, move
the appropriate SQL statements from the list of statements to the answer
area and arrange them in the correct order.)
Build List and Reorder:
A
You are developing a database that will
contain price information. You need to store the prices that include a
fixed precision and a scale of six digits. Which data type should you
use?
A
Money
B
Smallmoney
C
Float
D
Numeric
You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB.
You are developing a stored procedure that takes a parameter named @date
that uses the varchar datatype. The @date parameter must be compared to
the value in a datetime column named OrderDate.
Which of the following WHERE clauses would be the most efficient WHERE clause to use?
A
WHERE OrderDate = CAST(@date AS datetime)
B
WHERE OrderDate = CONVERT(datetime,@date)
C
WHERE OrderDate = CAST(datetime,@date)
D
WHERE OrderDate = PARSE(@date AS Date)
E
WHERE OrderDate =@date
Your database contains a table named
SalesOrders. The table includes a DATETIME column named OrderTime that
stores the date and time each order is placed. There is a non-clustered
index on the OrderTime column. The business team wants a report that
displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the
most efficient manner. Which Transact-SQL query should you use?
A
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime >= CONVERT(DATE, GETDATE())
AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
B
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = GETDATE()
C
SELECT COUNT(*) FROM SalesOrders
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
D
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = CONVERT(DATE, GETDATE())
You develop a
database for a travel application. You need to design tables and other
database objects. You create a stored procedure. You need to supply the
stored procedure with multiple event names and their dates as
parameters. What should you do?
A
Use the CAST function.
B
Use the DATE data type.
C
Use the FORMAT function.
D
Use an appropriate collation.
E
Use a user-defined table type.
F
Use the VARBINARY data type.
G
Use the DATETIME data type.
H
Use the DATETIME2 data type.
I
Use the DATETIMEOFFSET data type.
J
Use the TODATETIMEOFFSET function.
You develop a
database for a travel application. You need to design tables and other
database objects. You create the Airline_Schedules table. You need to
store the departure and arrival dates and times of flights along with
time zone information. What should you do?
A
Use the CAST function.
B
Use the DATE data type.
C
Use the FORMAT function.
D
Use an appropriate collation.
E
Use a user-defined table type.
F
Use the VARBINARY data type.
G
Use the DATETIME data type.
H
Use the DATETIME2 data type.
I
Use the DATETIMEOFFSET data type.
J
Use the TODATETIMEOFFSET function.
You administer a Microsoft SQL Server 2012
database that contains a table named OrderDetail. You discover that the
NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need
to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
B
CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
C
ALTER INDEX ALL ON OrderDetail REBUILD
D
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
You work as a SQL Server 2012 database
developer at ABC.com. You are developing a stored procedure that updates
rows in several tables.
You want the entire transaction to be rolled back should the stored procedure cause a run-time error.
How would you accomplish this?
A
You should make use of RAISERROR in the stored procedure.
B
You should make use of a LOOP hint in the stored procedure.
C
You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
D
You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.
E
You should make use of the SET XACT_ABORT ON statement in the stored procedure.
F
You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
You create a view based on the following statement:
You grant the Select permission to User1 for this view. You need to
change the view so that it displays only the records that were processed
in the month prior to the current month. You need to ensure that after
the changes, the view functions correctly for User1. Which four
Transact-SQL statements should you use? (To answer, move the appropriate
SQL statements from the list of statements to the answer area and
arrange them in the correct order.)
Build List and Reorder:
A
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB with a table named Invoices.
Application developers are developing several in-house applications that
will access the Invoices table. You need to develop a solution that
will allow the applications to access the table indirectly while still
allowing them to update the Invoice table.
How would you accomplish this task?
A
You should drop and recreate the Invoices table as a partitioned table.
B
You should allow the applications access to the Invoices table via stored procedures.
C
You should create a view on the Invoices table.
D
You should create a columnstore index on all columns used by the applications.
You are employed as a SQL Server 2012
database developer at ABC.com. ABC.com has a database named SalesDB with
tables named Customer and Orders. The Customer and Orders tables were
created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers (
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL ) GO CREATE TABLE SalesDB.Orders (
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL ) GO
You must now create an OrderDetails table as shown in the following database diagram:
The TotalPrice column must be a computed column based on the product of the UnitPrice and
Quantity columns and the data must be stored in the table.
How would you create this table?
To answer, type the correct code in the answer area.
A
CREATE TABLE SalesDB.OrderDetails (
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice money NOT NULL,
TotalPrice AS (Quantity * UnitPrice) PERSISTED )
You administer a Microsoft SQL Server 2012
server. You plan to deploy new features to an application. You need to
evaluate existing and potential clustered and non-clustered indexes that
will improve performance.
What should you do?
A
Use the Database Engine Tuning Advisor.
B
Query the sys.dm_db_index_usage_stats DMV.
C
Query the sys.dm_db_missing_index_details DMV.
D
Query the sys.dm_db_missing_index_columns DMV.
CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are
developing a query for a database driven Web application that allows
visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket
players by the average votes over the last 12 months. You want the top
10 cricket players to have a rank of 1, the next 10 to have a rank of 2,
and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.
A
SELECT TOP 30 PlayerName, NTILE (3) OVER (ORDER BY AVG (Votes)
DESC) AS AveVotes FROM WeeklyVotes GROUP BY PlayerName
A table named Profits stores the total
profit made each year within a territory. The Profits table has columns
named Territory, Year, and Profit. You need to create a report that
displays the profits made by each territory for each year and its
previous year. Which Transact-SQL query should you use?
A
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory)
AS PrevProfit FROM Profits
B
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year)
AS PrevProfit FROM Profits
C
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory)
AS PrevProfit FROM Profits
D
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year)
AS PrevProfit FROM Profits
You administer a Microsoft SQL Server
database that supports a shopping application. You need to retrieve a
list of customers who live in territories that do not have a sales
person. Which Transact- SQL query or queries should you use? (Each
correct answer presents a complete solution. Choose all that apply.)
A
SELECT CustomerID FROM Customer WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
B
SELECT CustomerID FROM Customer WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
C
SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
D
SELECT CustomerID FROM Customer WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
You create a table that has the
StudentCode, SubjectCode, and Marks columns to record mid-year marks for
students. The table has marks obtained by 50 students for various
subjects. You need to retrieve the students who scored the highest marks
for each subject along with the marks. Which Transact-SQL query should
you use?
A
SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC)
AS Value FROM StudentMarks GROUP BY StudentCode
B
SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
C
SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS
Value FROM StudentMarks GROUP BY StudentCode
D
SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
E
SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC)
AS Value FROM StudentMarks GROUP BY StudentCode
F
SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
G
SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC)
AS Rank FROM StudentMarks
H
SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that
do not have a branch location. Which Transact-SQL query or queries
should you use? (Each correct answer presents a complete solution.
Choose all that apply.)
A
SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
B
SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
C
SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
D
SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You need to create a query that calculates the total sales of each
OrderId from the Sales.Details table. The solution must meet the
following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use? To answer, type the correct code in the answer area.
You administer a Microsoft SQL Server
database named Sales. The database is 3 terabytes in size. The Sales
database is configured as shown in the following table.
You discover that Sales_2.ndf is corrupt. You need to recover the
corrupted data in the minimum amount of time. What should you do?
A
Perform a filegroup restore.
B
Perform a file restore.
C
Perform a transaction log restore.
D
Perform a restore from a full backup.
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You need to create a query that returns a list of products from
Sales.ProductCatalog. The solution must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area
You develop a database application for a university. You need to create a
view that will be indexed that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the
appropriate SQL statements from the list of statements to the answer
area and arrange them in the correct order.)
Select and Place:
A
You use Microsoft SQL Server 2012 to
develop a database application. You need to create an object that meets
the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view
Which object should you use?
A
Inline function
B
Stored procedure
C
User-defined data type
D
Scalar-valued function
You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
The fields must use the minimum amount of space.
The fields must be an incrementing series of values.
The values must be unique among the four tables.
What should you do?
A
Create a SEQUENCE object that uses the INTEGER data type.
B
Create a ROWVERSION column.
C
Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID()
D
Create a TIMESTAMP column.
E
Use the INTEGER data type along with IDENTITY
You have an XML schema collection named
Sales.InvoiceSchema. You need to declare a variable of the XML type
named XML1. The solution must ensure that XML1 is validated by using
Sales.InvoiceSchema.
Which code segment should you use? To answer, type the correct code in the answer area.
A
DECLARE @XML1 XML(Sales.InvoiceSchema)
You administer a Microsoft SQL Server 2012
database that has Trustworthy set to On. You create a stored procedure
that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure. You need to
ensure that the stored procedure returns the required information when
User1 executes the stored procedure. You need to achieve this goal by
granting the minimum permissions required. What should you do? (Each
correct answer presents a complete solution. Choose all that apply.)
A
Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.
B
Grant the db_owner role on the database to User1.
C
Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D
Grant the sysadmin role on the database to User1.
E
Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
You have a view that was created by using the following code:
You need to create an inline table-valued function named
Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the
following requirements:
Use one-part names to reference columns.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
You have a view that was created by using the following code:
You need to create an inline table-valued function named
Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the
following requirements:
Use one-part names to reference columns.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
You use Microsoft SQL Server 2012 to
develop a database application. You create a stored procedure named
dbo.ModifyData that can modify rows. You need to ensure that when the
transaction fails, dbo.ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?
A
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
B
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
C
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH
D
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH
You develop a database for a travel
application. You need to design tables and other database objects. You
need to store media files in several tables. Each media file is less
than 1 MB in size. The media files will require fast access and will be
retrieved frequently. What should you do?
A
Use the DATETIME data type.
B
Use the TODATETIMEOFFSET function.
C
Use the VARBINARY data type.
D
Use a user-defined table type.
E
Use the FORMAT function.
F
Use the DATETIMEOFFSET data type.
G
Use the DATETIME2 data type.
H
Use the CAST function.
I
Use the DATE data type.
J
Use an appropriate collation.
Which of the following datatypes has a fixed precision and a scale of six digits?
A
Money
B
Int
C
VarInt
D
Numeric
E
SmallInt
F
Double
G
Float
You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view
that is used to look up their email addresses and phone numbers. The view has the following
definition:
You need to ensure that users can update only the phone numbers and email addresses by using
this view. What should you do?
A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Create an INSTEAD OF UPDATE trigger on the view.
C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an
index on the view.
D. Create an AFTER UPDATE trigger on the view.
You have a database that contains the tables shown in the exhibit.
You need to create a query that calculates the total sales of each OrderId from the Sales.Details
table. The solution must meet the following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use?
You have a database that contains the tables shown in the exhibit.
You need to create a view named uv_CustomerFullName to meet the following requirements:
The code must NOT include object delimiters.
The view must be created in the Sales schema.
Columns must only be referenced by using one-part names.
The view must return the first name and the last name of all customers. The view must prevent the underlying structure of the customer table from being changed.
The view must be able to resolve all referenced objects, regardless of the user's default
Schema.
Which code segment should you use?
To answer, type the correct code in the answer area.
You have a database that contains the tables shown in the exhibit.
You deploy a new server that has SQL Server 2012 installed. You need to create a table named
Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.
Which code
segment should you use?
To answer, type the correct code in the answer area.
You have a view that was created by using the following code:
You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must
meet the following requirements:
Accept the @T integer parameter.
Use one-part names to reference columns.
Filter the query results by SalesTerritoryID.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.
You have a database that contains the tables as shown in the exhibit.
You have the following query:
You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.
You have a database that contains the tables shown in the exhibit.
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard. Which code segment should you use?
To answer, type the correct code in the answer area.
You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of
the XML type named XML1. The solution must ensure that XML1 is validated by using
Sales.InvoiceSchema. Which code segment should you use?
To answer, type the correct code in the answer area.
You have a database that contains the tables as shown in the exhibit.
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution
must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area.
You have a database that contains the tables shown in the exhibit.
You have an application named Appl. You have a parameter named @Count that uses the int data
type. App1 is configured to pass @Count to a stored procedure. You need to create a stored
procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:
NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.
You are a database developer of a Microsoft SQL Server 2012 database.
You are designing a table that will store Customer data from different
sources. The table will include a column that contains the CustomerID
from the source system and a column that contains the SourceID. A sample
of this data is as shown in the following table
You need to ensure that the table has no
duplicate CustomerID within a SourceID. You also need to ensure that the
data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?
A
CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));
B
CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY,
CustomerName varchar(255) NOT NULL);
C
CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);
D
CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
SalesDB database with a view named SalesV. The SalesV view was created using the following
Transact-SQL code:
CREATE VIEW SalesDB.ProductsSalesV AS SELECT OrderID, ProductID,
ShipDate, OrderDate, Amount FROM SalesDB.Orders;
You want to create an inline table-valued function named fn_ABC that
accepts a @ProductID parameter of the integer data type. The inline
table-valued function should also allow for sales orders for each
product to be listed by the latest sale.
How would you create this inline table-valued function?
To answer, type the correct code in the answer area.
A
CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int ) RETURNS TABLE
AS RETURN ( SELECT OrderID, ProductID, ShipDate, OrderDate, Amount
FROM Sales. ProductsSalesV WHERE ProductID = @ProductID ORDER BY
OrderDate DESC );
You administer a Microsoft SQL Server 2012 database.
The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate
table named EmployeeData. One record exists within EmployeeData for each
record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Primary Key constraint for this table?
A
EmployeeNum
B
LastName
C
EmployeeID
D
ReportsToID
E
FirstName
F
Departments
G
MiddleName
H
DateHired
I
JobTitle
You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:
Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary
column of the recent 10 entries based on the values in EntryDateTime.
Which Transact-SQL statement should you use?
A
UPDATE TOP(10) BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0)
B
UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage',
NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
AS s WHERE BlogEntry.Id = s.ID
C
UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage',
0, 0) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
D
UPDATE BlogEntry SET Summary = CAST(N'
This is in a draft stage' as nvarchar(max))
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
Users have advised that they are not
receiving report subscriptions from SQLReporting01. You confirm that the
report subscriptions are not being delivered.
Which of the following actions should you perform to resolve the issue?
A
You should run the SQL Server 2012 Upgrade Wizard to upgrade the active node of the SSAS cluster.
B
You should start the SQL Server Agent on the active node of the SSAS cluster.
C
You should start the SQL Server Agent on SQLReporting01.
D
You should restore the ReportServerTempDB database on SQLReporting01.
You create a view based on the following statement:
You grant the Select permission to User1.
You need to change the view so that it displays only the records that
were processed in the month prior to the current month. You need to
ensure that after the changes, the view functions correctly for User1.
Which Transact-SQL statement should you use?
Option C
B
Option A
C
Option D
D
Option B
You are developing a SQL Server 2012
database for ABC.com. You need to create a computed column that returns
the data by referencing another table using an INNER JOIN.
How would you accomplish this?
A
You should make use of an inline function.
B
You should make use of a scalar user-defined function.
C
You should make use of a stored procedure.
D
You should make use of the FORMAT function.
E
You should make use of a table-valued user-defined function.
You need to create a query that calculates the total sales of each
OrderlD from a table named Sales.Details. The table contains two columns
named OrderlD and
ExtendedAmount. The solution must meet the following requirements:
Use one-part names to reference columns.
Start the order of the results from OrderlD.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderlD column and the calculated TotalSales column.
Provide the correct code in the answer area.
You administer a Microsoft SQL Server 2012
database that has Trustworthy set to On. You create a stored procedure
that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure. You need to
ensure that the stored procedure returns the required information when
User1 executes the stored procedure. You need to achieve this goal by
granting the minimum permissions required. What should you do? (Each
correct answer presents a complete solution. Choose all that apply.)
A
Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.
B
Grant the sysadmin role on the database to User1.
C
Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D
Grant the db_owner role on the database to User1.
E
Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
You develop a Microsoft SQL Server 2012
server database that supports an application. The application contains a
table that has the following definition:
CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the
ItemsInStore and ItemsInWarehouse values for each row. Which
Transact-SQL statement should you use?
A
ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);
B
ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
C
ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
D
ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItems
You work as a database administrator at ABC.com. ABC.com has a SQL
Server 2012 database named ProductsDB. The relevant part of the
ProductsDB is shown in the following database diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
Which of the following SELECT statement would you write?
A
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001 FOR XML AUTO, RAW
B
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001 FOR XML RAW
C
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001 FOR XML AUTO
D
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001 FOR XML
You Create a table that has the StudentCode,SubjectCode, and Marks
Columns to record mid-year for students. The Table has marks obtained by
50 Students for various Subjects. You need to ensure that the following
requirements are met:
Students must be ranked based on their average marks.
if one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned
Which Transact-SQL query should you use?
A SELECT StudentCode as Code,RANK() OVER(ORDER BY AVG(Marks) DESC) AS value FROM StudentMarks GROUP BY StudentCode
B SELEC Id,Name,Marks,DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
C SELECT StudentCode as Code,DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
D SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks Group by StudentCode
E SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode,Marks AS Marks,RANK() OVER(PARTITON BY SubjectCode
ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
F SELECT StudentCode as Code,Marks as value FROM (select StudentCode,Marks as marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
G SELECT StudentCode AS code,Marks Value FROM (SELECT StudentCode,Marks as Marks,RANK(0 OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
H SELECT StudentCode as Code,Marks As Value FROM
(SELECT StudentCode,Marks as Marks,RANK() OVER(PARTITON BY StudentCode
ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
INSERT INTO dbo.MyTableALL VALUES (24,38,36,96,21,65,32);
GO
SELECT * FROM dbo.MyTableALL
FOR XML in SQL Server: Ref: https://msdn.microsoft.com/en-IN/library/ms178107.aspx
A SELECT query returns results as a rowset. You can optionally retrieve
formal results of a SQL query as XML by specifying the FOR XML clause in the
query. The FOR XML clause can be used in top-level queries and in sub queries.
The top-level FOR XML clause can be used only in the SELECT statement. In sub
queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It
can also be used in assignment statements.
In a FOR XML clause, you specify one of these modes:
RAW
AUTO
EXPLICIT
PATH
The RAW mode generates a single <row> element per row in the rowset
that is returned by the SELECT statement. You can generate XML hierarchy by
writing nested FOR XML queries.
The AUTO mode generates nesting in the resulting XML by using heuristics
based on the way the SELECT statement is specified. You have minimal control
over the shape of the XML generated. The nested FOR XML queries can be written
to generate XML hierarchy beyond the XML shape that is generated by AUTO mode
heuristics.
The EXPLICIT mode allows more control over the shape of the XML. You can mix
attributes and elements at will in deciding the shape of the XML. It requires a
specific format for the resulting rowset that is generated because of query
execution. This rowset format is then mapped into XML shape.
The power of
EXPLICIT mode is to mix attributes and elements at will, create wrappers and
nested complex properties, create space-separated values (for example, OrderID
attribute may have a list of order ID values), and mixed contents.
Sample:
SELECT TOP 1 CustomerID
FROM Sales.Customer x
FOR XML AUTO
Output:
<x CustomerID="1" />
SELECT TOP 1 CustomerID
FROM Sales.Customer x
FOR XML RAW
Output:
<row CustomerID="1" />
SELECT TOP 1 CustomerID
FROM Sales.Customer x
FOR XML PATH
Output:
<row>
<CustomerID>1</CustomerID>
</row>
Also see more sample
SELECT TOP 2 Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status,Cust.CustomerType FROM Sales.Customer Cust INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID FOR XML AUTO Output: <Cust CustomerID="676" CustomerType="S"> <OrderHeader CustomerID="676" SalesOrderID="43659" Status="5" /> </Cust> <Cust CustomerID="117" CustomerType="S"> <OrderHeader CustomerID="117" SalesOrderID="43660" Status="5" /> </Cust>
SELECT TOP 2 Cust.CustomerID, OrderHeader.CustomerID OrderheaderCustomerID, OrderHeader.SalesOrderID, OrderHeader.Status,Cust.CustomerType FROM Sales.Customer Cust INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID FOR XML RAW
Insert into Customers
SELECT 1,'Customer A','Australia'
union
SELECT 2,'Customer A','Australia'
Insert into Orders
SELECT 1,1,'2000-01-01 19:13:43.540',3400
UNION
SELECT 2,1,'2001-01-01 19:13:43.540',4300
SELECT * FROM Customers
SELECT * FROM Orders
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers. CustomerId = 1
FOR XML RAW, ELEMENTS
SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers. CustomerId= 1
FOR XML AUTO, ELEMENTS
SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML AUTO
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers. CustomerId = 1
FOR XML AUTO
SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')
SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML RAW
SELECT Name AS 'Customers/Name', Country AS 'Custo/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH ('Customers')
SELECT Name AS 'Customers/Name', Country AS 'Custo/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML AUTO
SELECT Name AS 'Customers/Name', Country AS 'Custo/Country', OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML RAW
Stored Procedures with Recompile option:
When you create a stored procedure using the WITH RECOMPILE option, it
gets a brand new execution plan every time it runs. This can be good
for high performance queries because they get a plan perfect for the
variables that are passed in. However, this causes increased CPU time
due to compiling the execution plan over and over.
Option 1:
CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT) WITH RECOMPILE AS
SELECT *
FROM dbo.tblPerson
WHERE Age >= @MinAge AND Age <= @MaxAge GO
Option 2:
EXEC dbo.PersonAge 65,70 WITH RECOMPILE
This method is not recommended for large
stored procedures because the recompilation of so many statements may
outweigh the benefit of a better execution plan.
Note: Recompilation is not necessarily the best solution.
Physical Representation in tempdb
There’s a common misconception that only temporary tables have a physical representation
in tempdb and that table variables reside only in memory. This isn’t true. Both temporary tables and table variables have a physical representation in tempdb.
You can find entries in the sys.objects view for the internal tables that SQL Server creates
in tempdb to implement your temporary tables and table variables.
As an example, the
following code creates a temporary table called #T1 and then queries the sys.objects view in
tempdb looking for table names starting with #.
CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
DROP TABLE #T1;
GO
This code generated the following output in the test system:
name
----------
#T1
____________________________000000000018
As mentioned earlier, SQL Server internally adds a suffix to the user-assigned table names
to prevent name conflicts in case multiple sessions create a temporary table with the same
name.
The following code demonstrates how to declare a table variable and then query the
sys.objects view.
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
When this code ran on the test system, it produced the following output.
name
----------
#BD095663
As you can see, SQL Server created a table in tempdb to implement the table variable you
declared.
A common question is whether table expressions such as common table expressions (CTEs)
also get persisted like temporary tables and table variables. The answer is no. When SQL
Server optimizes a query against a table expression, it unnests the inner query’s logic and interacts
directly with the underlying tables. This means that unlike temporary tables and table
variables, table expressions have no physical side to them.
FORMAT Function:
FORMAT ( value, format [, culture ] )
For example, NULL is returned if the value specified in format is not valid.
FORMAT() function:
Well I’ve used this function
with only dates now and I’m quite happy with it. Now to convert a date
to a different format you don’t have to CAST/CONVERT your datetime with
lot of different options you don’t memorize or remember. With this new
function you can format your dates or datetime values in various formats
and in different langauges.
Let’s check this:
SELECT
GETDATE() ASDateToday,
FORMAT (GETDATE(), 'D') ASDateFormatted,
FORMAT (GETDATE(), 'dd/MM/yyyy (MMM)') ASDate_ddmmyyyyMON,
FORMAT (CAST('2012-02-01'ASDATE), 'MMM dd yyyy') ASDate_MONddyyyy,
FORMAT (CAST('2012-02-01'ASDATE), 'MMMM dd yyyy') ASDate_Monthddyyyy;
DECLARE@someDate DATETIME
SET@someDate = '2012-02-01 14:54:39.300'
SELECT
FORMAT (@someDate, 'D', 'de') ASDateNew_German,
FORMAT (@someDate, 'f', 'fr') ASDateNew_French,
FORMAT (@someDate, 'D', 'es-US') ASDateNew_Snanish,
FORMAT (@someDate, 'f', 'zh') ASDateNew_Chinese,
FORMAT (@someDate, 'D', 'ar') ASDateNew_Arabic,
FORMAT (@someDate, 'f', 'hi-IN') ASDateNew_Hindi;
Output:-
The 3rd parameter Culture is actaully used to specify the locale and
is optional. You can use different Cultures to modify your datetimes in
different languages and formats as shown above..
CONCAT() function:
as its name suggests helps
combine values of columns into a single string value. Well you can also
do this by using “+” operator and combine columns. But what about NULL
values and datatype conversion. You can also do it but what if somehow
you miss. This function takes care aboout all these things, NULL values
and datatype conversion.
Let’s check this:
USE [AdventureWorks2012]
GO
-- Handling NULL implicitly:
SELECTTOP10
-- Combining columns by using + operator (NULL values are not handled):
Note: the TRY_CAST(), TRY_CONVERT() & TRY_PARSE() functions
added exception handling to the existing CAST(), CONVERT() and new
PARSE() function respectively. Now developers can fully rely on these 2
functions while type converting values and defaulting them to something
else on any exception.
Analytic Functions:
USE AdventureWorks2008R2;
GO
-- FIRST_VALUE() and LAST_VALUE()
SELECT
Name, ListPrice, ProductSubcategoryID,
FIRST_VALUE (Name) OVER (PARTITION BYProductSubcategoryID ORDERBYListPrice ASC) ASLeastExpensive,
LAST_VALUE (Name) OVER (PARTITION BYProductSubcategoryID ORDERBYListPrice ASC) ASLeastExpensive
FROMProduction.Product;
-- LAG()
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression
The value to be returned based on the specified offset.
It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.
offset
The number of rows back from the current row from which to obtain a value.
If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.
LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression
The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.
offset
The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.
SELECT
TerritoryName, BusinessEntityID, SalesYTD,
LEAD (SalesYTD, 1, 0) OVER (PARTITION BYTerritoryName ORDERBYSalesYTD DESC) ASPrevRepSales
FROMSales.vSalesPerson
WHERETerritoryName IN(N'Northwest', N'Canada')
ORDERBYTerritoryName;
(OR)
SELECT
TerritoryName, BusinessEntityID, SalesYTD,
LEAD (SalesYTD) OVER (PARTITION BYTerritoryName ORDERBYSalesYTD DESC) ASPrevRepSales
FROMSales.vSalesPerson
WHERETerritoryName IN(N'Northwest', N'Canada')
ORDERBYTerritoryName;
Note: The LAG() and LEAD() functions are already in Oracle and by
introducing them in MS SQL Server’s Denali version is very much
welcomed by TSQL developers. Plus the addition of FIRST_VALUE(),
LAST_VALUE(), PERCENTILE_XXX(), etc will really add value and provide
zeal to programmers in TSQL development, just like Ranking functions and
Cube/Rollup did in 2005(08).
Some other new functions introduced are:
– CUME_DIST()
– PERCENTILE_CONT()
– PERCENTILE_DISC()
– PERCENT_RANK()
MCSA Exam 70-461 Querying Microsoft SQL Server 2012 Sample Test Practice Solutions:
Your database contains a table named
SalesOrders. The table includes a DATETIME column named OrderTime that
stores the date and time each order is placed. There is a non-clustered
index on the OrderTime column. The business team wants a report that
displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the
most efficient manner. Which Transact-SQL query should you use?
A
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime >= CONVERT(DATE, GETDATE())
AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
B
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = GETDATE()
C
SELECT COUNT(*) FROM SalesOrders
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
D
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = CONVERT(DATE, GETDATE())
SELECT COUNT(*) FROM OrderSales
WHERE CONVERT(VARCHAR, OrderDate, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
Msg 174, Level 15, State 1, Line 2
The getdate function requires 0 argument(s).
SELECT COUNT(*) FROM OrderSales
WHERE OrderDate = GETDATE()
0
SELECT COUNT(*) FROM OrderSales
WHERE OrderDate >= CONVERT(DATE, GETDATE())
AND OrderDate < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
2
So Answer is
A
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime >= CONVERT(DATE, GETDATE())
AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
You are developing a database that will
contain price information. You need to store the prices that include a
fixed precision and a scale of six digits. Which data type should you
use?
A
Money
B
Smallmoney
C
Float
D
Numeric
Ans: D
Numeric
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:
You create a view on the SalesDB using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
How should you alter this view to allow users to update data through the SalesV?
A.
You should add a CHECK constraint to the SalesV view.
B. You should add an INSTEAD OF trigger to the SalesV view.
C.
You should add a clustered index to the SalesV view.
D.
You should add an AFTER UPDATE trigger to the SalesV view.
E.
Create a columnstore index on all columns used in the SalesV view.
CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named HRDB with tables named Staff and SalaryHistory. The Staff and SalaryHistory
tables were created using the following Transact-SQL code:
CREATE TABLE HRDB.Staff
(
StaffID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
StaffAddress nvarchar (200) NULL,
StaffCity nvarchar(50) NOT NULL,
StaffState nvarchar(50) NOT NULL,
StaffPhone varchar (10) NOT NULL,
Salary smallmoney NOT NULL
)
GO
CREATE TABLE HRDB.SalaryHistory
(
StaffID int NOT NULL,
IncreaseDate date DEFAULT GETDATE(),
PrevSalary smallmoney NOT NULL,
NewSalary smallmoney NOT NULL
)
GO
You must write a Transact-SQL query to affect a salary increase of 6.5% for all staff members with
a JobTitle of Support Technician. Your query must also update the data in the SalaryHistory table.
What Transact SQL statement would accomplish this task?
To answer, type the correct code in the answer area.
Answer: See the explanation.
Explanation:
UPDATE Staff SET Salary = Salary * 1.065 OUTPUT inserted.StaffID, deleted.Salary, inserted.Salary
INTO SalaryHistory(StaffID, PrevSalary, NewSalary) WHERE JobTitle = 'Support Technician'
Sample Script for above output:
--kindly use SQL 2012 version
Use [AdventureWorks] CREATE TABLE dbo.Staff ( StaffID int NOT NULL PRIMARY KEY, FirstName nvarchar(50) NOT NULL, LastName nvarchar(50) NOT NULL, JobTitle nvarchar(50) NOT NULL, StaffAddress nvarchar (200) NULL, StaffCity nvarchar(50) NOT NULL, StaffState nvarchar(50) NOT NULL, StaffPhone varchar (10) NOT NULL, Salary smallmoney NOT NULL ) GO CREATE TABLE dbo.SalaryHistory ( StaffID int NOT NULL, IncreaseDate date DEFAULT GETDATE(), PrevSalary smallmoney NOT NULL, NewSalary smallmoney NOT NULL )
SELECT * FROM dbo.Staff
SELECT * FROM dbo.SalaryHistory
INSERT INTO dbo.Staff select 5555,'kirish','anand','Support Technician','MUMBAI','PUNE','MP','9209202022','75000' UNION ALL select 4678,'Kulip','prasad','EEE','MUMBAI','CHNI','TN','9209202022','89000' UNION ALL select 5687,'nagaru','chand','Support Technician','MUMBAI','PUNE','MP','9209202022','65000' UNION ALL select 6687,'dileep','kurkar','ECE','MUMBAI','PUNE','TN','9209202022','44000' UNION ALL select 9687,'lakesh','saran','Support Technician','KOCHI','KOC','KER','9209202022','45000' UNION ALL select 6666,'ashok','bansal','IT','COIMBATORE','CBE','TN','9209202022','95000'
INSERT INTO dbo.SalaryHistory(StaffID,IncreaseDate,PrevSalary,NewSalary)
select 4678,'2014-05-12 14:25:23.810','85000','89000' UNION ALL select 5687,'2015-03-27 10:23:11.910','62000','65000' UNION ALL select 6687,'2015-11-14 12:25:24.610','40000','44000' UNION ALL select 96877,'2015-05-20 21:23:12.411','40000','45000'
SELECT * FROM dbo.Staff
SELECT * FROM dbo.SalaryHistory
UPDATE Staff set Salary=Salary*1.605 OUTPUT Inserted.StaffID,Deleted.Salary,Inserted.Salary INTO dbo.SalaryHistory(StaffID,PrevSalary,NewSalary) WHERE StaffID=5555 and JobTitle='Support Technician' Other Example: CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100)) ----Creating temp table to store ovalues of OUTPUT clause DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100)) ----Insert values in real table INSERT TestTable (ID, TEXTVal) VALUES (1,'FirstVal') INSERT TestTable (ID, TEXTVal) VALUES (2,'SecondVal') ----Update the table and insert values in temp table using Output clause UPDATE TestTable SET TEXTVal = 'NewValue' OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable WHERE ID IN (1,2) ----Check the values in the temp table and real table ----The values in both the tables will be same SELECT * FROM @TmpTable SELECT * FROM TestTable
CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
database named SalesDB with tables named Customer and Orders. The Customer and Orders
tables were created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar (150) NOT NULL,
LastName varchar (150) NOT NULL,
Address1 varchar (200) NOT NULL,
Address2 varchar (200) NULL,
City varchar (100) NOT NULL,
StateCode varchar (2) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL
)
GO
You must now create an OrderDetails table as shown in the following database diagram:
The TotalPrice column must be a computed column based on the product of the UnitPrice and
Quantity columns and the data must be stored in the table.
How would you create this table?
To answer, type the correct code in the answer area.
Answer: See the explanation.
Explanation:
CREATE TABLE SalesDB.OrderDetails
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice money NOT NULL,
TotalPrice AS (Quantity * UnitPrice) PERSISTED
)
Sample code:
CREATE TABLE SalesDB.Customers ( CustomerID int NOT NULL PRIMARY KEY, FirstName varchar (150) NOT NULL, LastName varchar (150) NOT NULL, Address1 varchar (200) NOT NULL, Address2 varchar (200) NULL, City varchar (100) NOT NULL, StateCode varchar (2) NOT NULL, Zip varchar (5) NOT NULL, Phone varchar (10) NOT NULL ) GO CREATE TABLE SalesDB.Orders ( OrderID int NOT NULL PRIMARY KEY, CustomerID int NOT NULL, OrderDate datetime NOT NULL, ShipDate datetime NOT NULL, SalesRepID int NOT NULL )
Insert into SalesDB.Customers SELECT 7897,'ANKIN','SIMRI','USA','ST LANE NEAR','BOSTON','4','22','1-1-1141' UNION ALL SELECT 9658,'KIROU','WRGSS','UK','THOMAS RIVER','LONDON','6','61','8-9-6964'
select * FROM SalesDB.Customers
Insert into SalesDB.Orders SELECT 75,7897,GETDATE()-6,GETDATE()+2,6 UNION SELECT 76,9658,GETDATE()-4,GETDATE()+2,6
select * FROM SalesDB.Orders
CREATE TABLE SalesDB.OrderDetails ( OrderID int NOT NULL PRIMARY KEY, ProductID int NOT NULL, Quantity int NOT NULL, UnitPrice Money not null, TotalPrice as (Quantity*UnitPrice) PERSISTED )
INSERT INTO SalesDB.OrderDetails SELECT 75,001,6,25.36 union all SELECT 76,003,4,36.36
SELECT * from SalesDB.OrderDetails
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The relevant part of the ProductsDB is shown in the following database
diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
<row ProductID=”1001″ Product=”Product Name”, Price=”24.99″ InStock=”16″
Supplier=”Company Name” Contact=”Contact Name” Phone=”346 959 2215″ />
Which of the following SELECT statement would you write?
A. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML RAW
B.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML
C.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML AUTO
D.
SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS
[InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone
FROM Products
INNER JOIN Suppliers ON SupplierID = SupplierID
WHERE ProductID = 1001
FOR XML AUTO, RAW
Soution:
Ans A:
Let Discuss other option
Option A FOR XML RAW appears means it should be ROW comes it is the answer
if FOR XML RAW Elements means it should be separate closed < > brackets appear
Option B FOR XML syntax error come.
Option B if XML AUTO means select column table name should be appear in output
Option D XML AUTO,RAW not valid for this output syntax error come
if XML PATH means table comes
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB is shown in the following database diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
<Invoices InvoiceID=”1001″ Date=”2012-10-01T00:00:00″, Value=”1000.00″ Customer=”Customer
Name” ShippedTo=”Customer City” />
Which of the following SELECT statement would you write?
A. SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID WHERE cu.CustomerID = 1001 FOR XML RAW('Invoices')
B.
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS
[Name], CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML
C.
SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value],
Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML AUTO
D.
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS
[Name], CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML AUTO, RAW
Solution:
Ans: Both Option A and C have same result:
FOR XML WITH TAG see below
FOR XML AUTO
But actual original FOR XML Auto it is not valid it gives syntax
but actual original FOR XML RAW Without tag see below
Option A is Correct answer Author have missed table tag as FOR XML RAW(‘Invoices ‘)
SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName
AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in
INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID
WHERE cu.CustomerID = 1001
FOR XML RAW(‘Invoices ‘)
Option C is not correct answer because if FOR XML AUTO Means end of
output should be end with but in our case it was ShippedTo=”Customer
City” />
so it is proved that OPTION A is correct.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database diagram:
You need to write a Transact-SQL query that displays all the products received by a single
supplier in the following XML format:
<Suppliers SupplierID=”22″ Company=”Company Name” ContactNumber=”510 250 6400″>
<Products ProductID=”100″ UnitPrice=”249.00″ UnitsInStock=”7″ />
<Products ProductID=”118″ UnitPrice=”559.00″ UnitsInStock=”12″ />
</Suppliers>
Which of the following SELECT statement would you write?
A.
SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID,
p.UnitPrice, p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
WHERE s.SupplierID = 22
FOR XML RAW
B.
SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID,
p.UnitPrice, p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
WHERE s.SupplierID = 22
FOR XML
C.
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company],
Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE Suppliers.SupplierID = 22
FOR XML AUTO
D. SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company], Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO, RAW
Solution:
Ans Option C
There is no FOR XML AUTO,RAW and FOR XML clause only
It gives Syntax Error as see below
Incorrect syntax near ‘XML’.
CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes
over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to
have a rank of 2, and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.
Explanation:
SELECT TOP 30 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes
FROM WeeklyVotes
GROUP BY PlayerName
Insert into WeeklyVotes
SELECT 1,'Sachin',56
union all
SELECT 1,'WATSON',52
UNION ALL
SELECT 1,'GAYLE',67
UNION ALL
SELECT 1,'KOHLI',78
UNION ALL
SELECT 2,'Sachin',65
union all
SELECT 2,'WATSON',46
UNION ALL
SELECT 2,'GAYLE',78
UNION ALL
SELECT 2,'KOHLI',98
UNION ALL
SELECT 3,'Sachin',95
union all
SELECT 3,'WATSON',86
UNION ALL
SELECT 3,'GAYLE',98
UNION ALL
SELECT 3,'KOHLI',97
UNION ALL
SELECT 4,'Sachin',95
union all
SELECT 4,'WATSON',99
UNION ALL
SELECT 4,'GAYLE',99
UNION ALL
SELECT 4,'KOHLI',99
SELECT * FROM WeeklyVotes
To see only for 4 players see below
SELECT TOP 4 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes,SUM(Votes) [SumofVotes]
FROM WeeklyVotes
GROUP BY PlayerName
Also we can achive using Row_number() function as see below
SELECT TOP 4 PlayerName,ROW_NUMBER() OVER(ORDER BY AVG(Votes) DESC) AS AVGvotes,Sum(Votes) [SUMofVotes]
FROM WeeklyVotes
GROUP BY PlayerName
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales
amount for each of ABC.com’s 20 sales representitives.
You need to write a Transact-SQL query that ranks the sales representatives by the average sales
amount for the past year. You want the sales representatives with the same average sales amount
to have the same rank with the subsequent rank being skipped.
Which ranking function should you use?
A. The RANK( ) OVER function.
B.
The NTILE( ) OVER function
C.
The DENSE_RANK( ) OVER function
D.
The ROW_NUMBER( ) OVER function
E.
The FORMAT function.
so this case because of this "with the subsequent rank being skipped" it should be Rank()
Rank() too and not DENSE_RANK() the answer for that specific question.
RANK() Example = 1233557888
DENSE_RANK() Example = 12223344445677
So Answer is OPTION A The RANK( ) OVER function. Thats is it
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that returns the cricket player that received the most
votes for each week, as well as the number of votes they received.
Which of the following SELECT statement would accomplish this task?
A.
SELECT PlayerName, Votes
FROM ( SELECT PlayerName, Votes,
RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank
FROM WeeklyVotes ) AS tmp
WHERE Rank = 1
B. SELECT PlayerName, Votes FROM ( SELECT PlayerName, Votes, RANK() OVER (PARTITION BY Week ORDER BY Votes DESC) AS Rank FROM WeeklyVotes) AS tmp WHERE Rank = 1
C.
SELECT PlayerName, Votes
FROM ( SELECT TOP 1 PlayerName, Votes,
RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank
FROM WeeklyVotes
ORDER BY Rank) AS tmp
D.
SELECT PlayerName, Votes
FROM ( SELECT TOP 1 PlayerName, Votes,
RANXO OVER (PARTITION BY PlayerName ORDER BY Votes DESC) AS Rank
FROM WeeklyVotes
ORDER BY Rank) AS tmp
Ans: OPTION B
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that stores the total number of a product that ABC.com has.
What Transact-SQL statements would accomplish this task?
A. ALTER TABLE Inventory ADD TotalProducts AS (InStore + InWarehouse) PERSISTED
B.
ALTER TABLE Inventory
ADD TotalProducts int SPARSE NOT NULL
C.
ALTER TABLE Inventory
ADD TotalProducts AS SUM (ALL) OVER (GROUP BY InStore, InWarehouse) PERSISTED
D.
DROP TABLE Inventory
GO
CREATE TABLE Inventory
(
ProductID int NOT NULL PRIMARY KEY,
InStore int NOT NULL,
InWarehouse int NOT NULL,
TotalProducts AS SUM (InStore, InWarehouse) PERSISTED
)
ABC.com has a SQL Server 2012 database instance that hosts a database named ComDB. The
ComDB database has a table named Partners that was created using the following Transact-SQL
code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL,
[CompanyName] [nvarchar] (50) NOT NULL,
[Location] [nvarchar] (50) NOT NULL,
[ContactName] [nvarchar] (50) NOT NULL,
[Email] [nvarchar] (50) NOT NULL,
[Phone] [nvarchar] (10) NOT NULL,
CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)
ON PRIMARY
)
You want to create a FOR UPDATE trigger that will track changes to the ContactName and Phone
columns.
Which of the following statements should you use in the trigger definition?
A.
IF COLUMNS_UPDATED (ContactName, Phone)
B.
IF COLUMNS_UPDATED (ContactName) OR COLUMNS_UPDATED (Phone)
C.
IF UPDATED (ContactName, Phone).
D. IF UPDATED (ContactName) OR UPDATED (Phone)
Actually there is type Error it should be
IF UPDATE (ContactName) OR UPDATE (Phone)
but all other answers are incorrect, so have to choose “D” as correct.
like as Option CIF UPDATE (ContactName, Phone) also not correct
It gives Incorrect syntax near ','.
Sample code:
CREATE TABLE [dbo].[Partners] ( [CompanyID] [int] NOT NULL, [CompanyName] [nvarchar] (50) NOT NULL, [Location] [nvarchar] (50) NOT NULL, [ContactName] [nvarchar] (50) NOT NULL, [Email] [nvarchar] (50) NOT NULL, [Phone] [nvarchar] (10) NOT NULL, CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED ( [CompanyID] ASC ) )
CREATE TABLE dbo.PartnerAudit([ContactName] [nvarchar] (50) NOT NULL, [Phone] [nvarchar] (10) NOT NULL,ModifiedDate Datetime)
ALTER Trigger TRG_Update_ContactName_Phone on dbo.Partners FOR UPDATE as --IF COLUMNS_UPDATED (ContactName, Phone) --IF COLUMNS_UPDATED (ContactName) OR COLUMNS_UPDATED (Phone) --IF UPDATE (ContactName, Phone) IF UPDATE (ContactName) OR UPDATE (Phone) begin Print 'ContactName/Phone Column Updated on Partners' Insert into dbo.PartnerAudit(ContactName,Phone,ModifiedDate) Select ContactName,Phone,Getdate() From deleted End
Insert into dbo.Partners select 0154,'Company A','Golden Company','Coimbatore','CompanyA@Company.com','56464' UNION ALL select 0158,'Company B','Weldier Company','Salem','CompanyB@Company.com','57884'
SELECT * FROM dbo.Partners
SELECT * FROM dbo.PartnerAudit
Update dbo.Partners SET Phone='9658912345' where CompanyID=154
You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com.
You need to optimize a very large database table that contains several million rows of data by
designing a view based on the table. The view must allow users to perform aggregations on
several columns.
How should you configure the view to ensure optimal performance?
A. You should create the view as an indexed view.
B.
You should create a clustered index on the view.
C.
You should make use of a stored procedure to return that data.
D.
You should make use of a table-valued function.
You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with a table named Partners.
The Partners table was created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL,
[Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL,
[Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL
)
You develop a new table named Events using the following Transact-SQL code:
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [varchar] (2500),
[EventCordinator] [varchar] (150) NOT NULL
)
How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?
A. You should add a Foreign Key Constraint on the Events table.
B.
You should add a Check Constraint on the Events table.
C.
You should add a Unique Constraint on the Events table.
D.
You should specify Events.CompanyID as a spars column.
E.
You should change the Events table to a partitioned table.
ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB.
You have created a view using the following Transact-SQL code:
CREATE VIEW ABCCommunications
(Type, CompanyID, CompanyName, Location, ContactName, Email, Phone)
AS
SELECT ‘Clients’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Client’
SELECT ‘Partners’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Partner’
SELECT ‘Guests’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Guests’
GO
You want the view to be used to edit all columns except the CompanyID, CompanyName and
Location columns.
What should you implement on the view?
A.
You should consider implementing an AFTER UPDATE trigger.
B.
You should consider implementing an Index.
C. You should consider implementing an INSTEAD OF UPDATE trigger.
D.
You should consider implementing a CHECK constraint.
Solution:
OPTION C
Actually VIEW Has UNION ALL missing in code.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:
You create a view on the SalesDB using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
How should you alter this view to allow users to update data through the SalesV?
A.
You should add a CHECK constraint to the SalesV view.
B. You should add an INSTEAD OF trigger to the SalesV view.
C.
You should add a clustered index to the SalesV view.
D.
You should add an AFTER UPDATE trigger to the SalesV view.
E.
Create a columnstore index on all columns used in the SalesV view.
You use Microsoft SQL Server 2012 to
develop a database application. You create a stored procedure named
dbo.ModifyData that can modify rows. You need to ensure that when the
transaction fails, dbo.ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?
A
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
B
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
C
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH
D
BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH
Solution:
So Answer is Option D
sample code of modify data as see below
CREATE proc dbo.ModifyData(@Md int)
as
begin
INSERT INTO dbo.MyTableALL VALUES (24,38,36,96,21,@Md,32);
Update dbo.MyTableALL set Myintcolumn=Myintcolumn/@Md
SELECT * FROM dbo.MyTableALL
end
When you run Both those Option see what will result
OPTION A gives
(1 row(s) affected)
(0 row(s) affected)
Msg 8134, Level 16, State 1, Procedure ModifyData, Line 10
Divide by zero error encountered.
OPTION B gives
(1 row(s) affected)
(0 row(s) affected)
Msg 8134, Level 16, State 1, Procedure ModifyData, Line 10
Divide by zero error encountered.
OPTION C gives
(1 row(s) affected)
(0 row(s) affected)
OPTION D gives
Command(s) completed successfully.
So Answer is Option D
SELECT * FROM dbo.MyTableALL
You develop a Microsoft SQL Server 2012 database
that has two tables named SavingAccounts and LoanAccounts. Both tables
have a column named AccountNumber of the nvarchar data type. You use a
third table named Transactions that has columns named TransactionId
AccountNumber, Amount, and
TransactionDate. You need to ensure that
when multiple records are inserted in the Transactions table, only the
records that have a valid AccountNumber in the SavingAccounts or
LoanAccounts are inserted. Which Transact-SQL statement should you use?
A
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END
B
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END
C
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
END
D
CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN END
END
Solution:
Correct answer is OPTION C
see Sample Table and Data
Let see Option A is correct or not
After created Trigger on option A u can't able to insert other values other than that two tables
you can see 0 row affected message came.
When you have select rows you can see no rows inserted.
When we go to check Option B is incorrect Msg 257, Level 16, State 3, Procedure TrgValidateAccountNumberOptionB, Line 11 Implicit conversion from data type smalldatetime to int is not allowed. Use the CONVERT function to run this query.
because
INSERT INTO dbo.Transactions(TransactionId,AccountNumber,Amount,TransactionDate)
above code (TransactionId,AccountNumber,Amount,TransactionDate) missing in option B and option C
it should needed to create triggers
When we go to check Option D it was Instead of Insert Trigger. So even correct values also not able to insert it
Insert into SavingAccounts SELECT '0546798512','XYZ' union all SELECT '0546748512','UTR' Union all SELECT '0546798512','POK' union all SELECT '0546748512','WSE'
Insert into LoanAccounts SELECT '1234567890','XYZ' union all SELECT '1234567891','UTR' Union all SELECT '1234567892','POK' union all SELECT '1234567893','WSE'
Insert into Transactions SELECT 9,'9234567899',GETDATE(),36
Insert into Transactions SELECT 9,'1234567890',GETDATE(),36
CREATE TRIGGER TrgValidateAccountNumberOptionB
ON dbo.Transactions
FOR INSERT
AS
BEGIN
INSERT INTO dbo.Transactions--(TransactionId,AccountNumber,Amount,TransactionDate)
SELECT TransactionId,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)
END
CREATE TRIGGER TrgValidateAccountNumberOptionC
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions(TransactionId,AccountNumber,Amount,TransactionDate)
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted
WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)
END
Create TRIGGER TrgValidateAccountNumberOptionD
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN END
END
DROP TRIGGER TrgValidateAccountNumberOptionA
DROP TRIGGER TrgValidateAccountNumberOptionB
DROP TRIGGER TrgValidateAccountNumberOptionC
DROP TRIGGER TrgValidateAccountNumberOptionD
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB as illustrated in the following database diagram:
ABC.com has retail stores in a few major cities across the country. The company wants a list of
Customers who live in a city that does not have a ABC.com store, along with the customer’s City.
The result set must be sorted alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?
A.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
B.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
C. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
D.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
Sample table and solution see below
so Answer is OPTION B,OPTION D if you should need to choose one you can choose OPTION D.
transactions table see below
Loanaccounts table see below
let see options which is correct
OPTION A is incorrect bocs it gives incorrect syntax
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'EXISTS'.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
it should be like this
SELECT CustomerName, CustomerCity
FROM Customers
WHERE NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
Also it should be like this give correct result
Select * from dbo.Transactions T
where NOT EXISTS (select L.AccountNumber from LoanAccounts L WHERE T.AccountNumber=L.AccountNumber)
order by AccountNumber
so option A is not correct
option B is correct
Select * from dbo.Transactions
where AccountNumber NOT IN (select AccountNumber from LoanAccounts)
order by AccountNumber
Insert into SavingAccounts SELECT '0546798512','XYZ' union all SELECT '0546748512','UTR' Union all SELECT '0546798512','POK' union all SELECT '0546748512','WSE'
Insert into LoanAccounts SELECT '1234567890','XYZ' union all SELECT '1234567891','UTR' Union all SELECT '1234567892','POK' union all SELECT '1234567893','WSE'
Select * from Transactions Select * from dbo.Transactions T where NOT EXISTS (select L.AccountNumber from LoanAccounts L WHERE T.AccountNumber=L.AccountNumber) order by AccountNumber
Select * from dbo.Transactions where AccountNumber NOT IN (select AccountNumber from LoanAccounts) order by AccountNumber
Select * from dbo.Transactions where AccountNumber <> ALL (select AccountNumber from LoanAccounts) order by AccountNumber
Select * from dbo.Transactions where AccountNumber <> ANY (select AccountNumber from LoanAccounts) order by AccountNumber
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure with several databases. You have permissions on two of these databases, namely,
CommDB and SalesDB.
You need to develop a stored procedure named dbo.ABC_addEntry in a database named
AssetsDB. The dbo.ABC_addEntry stored procedure will run stored procedures in CommDB and
SalesDB.
How should you design the stored procedure so that callers that do not paver permissions on
CommDB and SalesDB can run the dbo.ABC_addEntry stored procedure successfully?
A.
You should configure the stored procedure to call the xp_cmdshell extended stored procedure.
B.
You should configure the stored procedure to call the sp_configure system stored procedure.
C.
You should assign users permission to the stored procedure.
D.
You should include the EXECUTE AS CALLER clause when creating the stored procedure.
E. You should include the EXECUTE AS OWNER clause when creating the stored procedure.
CORRECT TEXT
You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with tables named Partners and Events. These
tables were created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL,
[Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL,
[Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL
)
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [nvarchar] (MAX),
[EventDate] [nvarchar] (50) NOT NULL,
[EventCordinator] [nvarchar] (150) NOT NULL
)
You add a foreign key relationship between the two tables on the CompanyID column.
You need to develop a stored procedure named sp_coEvents that retrieves CompanyName for all
partners and the EventDate for all events that they have coordinated.
To answer, type the correct code in the answer area.
SELECT * FROM [dbo].[Partners]
SELECT * FROM [dbo].[Events]
ALTER TABLE dbo.Events ADD Constraint C_FK FOREIGN KEY(CompanyID) REFERENCES Partners(CompanyID)
Important FOREIGN KEY(Column Name)----------Important to remember
Explanation:
CREATE PROC sp_coEvents
AS
BEGIN
SELECT P.CompanyName,E.EventDate
froM dbo.Partners P
join dbo.Events E on P.CompanyID=E.CompanyID
end
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure with a very large database named SalesDB. You create a new table named
SalesHistory that will hold historical data from the SalesDB database.
You need to perform a batch update from the SalesDB database to the SalesHistory table. You
want the status information from the batch process to be logged to a SQL Server table that must
be created by the batch process.
How would you accomplish this task? A. You should make use of the FORMAT function. B. You should make use of the CONVERT function.
C. You should make use of a scalar user-defined function.
D. You should make use of an inline function. E. You should make use of a table-valued function. F. You should make use of a stored procedure.
F is correct.
You can make executing the batch process one of the steps in the stored procedure.
A requirement is that the status of the batch must be logged to “a
SQL Server table that must be created by the batch process” – functions
cannot create tables but stored procedures can.
You are developing a SQL Server 2012 database for ABC.com. You need to create a computed
column that returns the data by referencing another table using an INNER JOIN.
How would you accomplish this?
A.
You should make use of the FORMAT function.
B. You should make use of a scalar user-defined function.
C.
You should make use of an inline function.
D.
You should make use of a table-valued user-defined function.
E.
You should make use of a stored procedure.
Which of the following statements regarding SQL Server 2012 objects is TRUE? A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view.
B.
A scalar function can accept an input variable and return a table of results but cannot be used
within a view.
C.
A table-valued function can accept an input variable and return a table of results but cannot be
used within a view.
D.
A table-valued type can accept an input variable and return a table of results but cannot be
used within a view.
Answer is Option A
Kindly see below Inside View You cant able to Declare User Defined Data Type it is true
Also you can see View can able to use Inside UDF without any issue
Also Table Valued UDF can able to create it without any parameter defined as input see below
OPTION D is not correct because
You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported
for more ref see below link
https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx
create type cidType as Table
(
CompanyID int
)
declare @ctvp cidType
insert into @ctvp(CompanyID)
values(154)
SELECT E.CompanyID FROM
dbo.Partners P
join dbo.Events E on P.CompanyID=E.CompanyID
join @ctvp cvp on cvp.CompanyID=P.CompanyID
when you use in normal SQL Statement TVP can use it but TVP cant use in Views but we can use it in SP
There are several limitations to table-valued parameters:
You cannot pass table-valued parameters to CLR user-defined functions.
Table-valued
parameters can only be indexed to support UNIQUE or PRIMARY KEY
constraints. SQL Server does not maintain statistics on table-valued
parameters.
Table-valued parameters are read-only in
Transact-SQL code. You cannot update the column values in the rows of a
table-valued parameter and you cannot insert or delete rows. To modify
the data that is passed to a stored procedure or parameterized statement
in table-valued parameter, you must insert the data into a temporary
table or into a table variable.
You cannot use ALTER TABLE statements to modify the design of table-valued parameters.
Facts about SQL Concepts with Example *UDF Will allow only Select statement, it will not allow us to use DML statements. *Table Variable Can use it inside UDF But Temporary Tables can't use inside UDF *Transactions not allowed inside UDF *UDF Can use inside/within SP But SP cant use inside/within UDF *We can use UDF Inside Views Also We can use View inside UDF *View did n't Accept Parameter (If you achieved you can Table Valued function to achieve that) *We can't use SP inside View but We can use View inside SP
You work as a SQL Server 2012 database developer at ABC.com. TesABCig.com has a database named DataDB. You are developing a complex stored procedure named sp_updater that will use a single transaction to update several tables in the DataDB database. You are concerned about data integrity and incomplete updates should the sp_updater stored procedure cause a run-time error. To mitigate this potential problem you want the transaction to terminate and the transaction to be rolled back if the sp_updater stored procedure raises a run-time error. How would you accomplish this task?
A. You should make use of the SET XACT_ABORT ON statement in the stored procedure.
B. You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
C. You should make use of a LOOP hint in the stored procedure.
D. You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
E. You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.
You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database SalesDB with a large Orders table. You create a heap named OldData that will store historical data from the Orders table. You need to write a Transact-SQL query that will insert rows of data from the Orders table that are marked as closed and are more than six months old. Which of the following table hints should you use in your query if you want to optimize transaction logging and locking for the query?
A. You should make use of the READPAST hint.
B. You should make use of the HOLDLOCK hint.
C. You should make use of the READCOMMITTED hint.
D. You should make use of the NOLOCK hint.
E. You should make use of the TABLOCK hint.
F. You should make use of the UPDLOCK hint.
You are employed as a SQL Server 2012 database developer at ABC.com. You have a stored procedure that is executed quite often. The stored procedure joins data from two tables. ABC.com users report that the stored procedure takes a long time to execute. You analyze the
query plan and find that the stored procedure often makes use of table scans rather than indexes when the estimated rows do not match the actual rows on one of the tables. How would you optimize the performance of the stored procedure?
A. You should make use of the KEEPIDENTITY table hint in the stored procedure.
B. You should make use of the KEEPDEFAULTS table hint in the stored procedure.
C. You should make use of the IGNORE_CONSTRAINTS table hint in the stored procedure.
D. You should make use of the FORCESEEK table hint in the stored procedure.
E. You should update statistics on the tables queried by the stored procedure.
You work as a SQL Server 2012 database developer at ABC.com. You are developing a database driven Web application. The application executes a store procedure based on the location of the web user. The location of the Web user is determined by IP Geolocation. You want to develop a process that will execute the correct stored procedure for every Web user based on the user’s location. How would you accomplish this?
A. You should make use of a foreach SQLCLR statement.
B. You should make use of a scalar user-defined function.
C. You should make use of an inline function.
D. You should make use of a cursor.
You work as a SQL Server 2012 database administrator at ABC.com. You have developing a
database named WebAnDB that will be used by a web site analysis application. The WebAnDB
database has a table named Visitors that stores date and time data in a column named Accessed.
You must now develop a stored procedure that will insert data into the Accessed column. You
want the stored procedure to store time zone data as well.
How would you accomplish this?
A.
You should make use of a scalar user-defined function.
B.
You should make use of the SET CONTEXT_INFO statement in the stored procedure.
C.
You should make use of the DATETIMEOFFSET data type.
D. You should make use of the TODATETIMEOFFSET function.
E.
You should make use of the SET FORCEPLAN ON statement in the stored procedure.
F.
You should make use of a cursor.
You work as a SQL Server 2012 database developer at ABC.com.
You are developing a database that will be used by a Web application. The database will store
small multimedia files in several tables. The largest multimedia file is 975 kB. These multimedia
files will be retrieved quite often.
How would you store these files?
A.
You should make use of a cursor.
B.
You should make use of a row-level compression.
C.
You should make use of the FileStream data type.
D. You should make use of the VARBINARY data type.
You work as a database administrator at ABC.com. You are developing a database that will be
used by a web site analysis application name ABCWeb1.
The ABCWeb1 application must display the date and time each visitor visits a page on a website
as well as the date and time they leave that web page. This data needs to be displayed in different
date and time formats.
How would you accomplish this?
A.
You should make use of a scalar user-defined function.
B.
You should make use of the SET CONTEXT_INFO statement in the stored procedure.
C.
You should make use of the DATETIMEOFFSET data type.
D. You should make use of the FORMAT function.
E.
You should make use of the SET FORCEPLAN ON statement in the stored procedure.
F.
You should make use of a cursor.
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that is the sum of values in the InStore and InWarehoue
columns for each product.
What Transact SQL statement would accomplish this task?
A. ALTER TABLE Inventory ADD TotalProducts AS (InStore + InWarehouse)
B.
ALTER TABLE Inventory
ADD TotalProducts int SPARSE NOT NULL
C.
ALTER TABLE Inventory
ADD TotalProducts AS SUM (ALL) OVER (GROUP BY InStore, InWarehouse)
D.
DROP TABLE Inventory
GO
CREATE TABLE Inventory
(
ProductID int NOT NULL PRIMARY KEY,
InStore int NOT NULL,
InWarehouse int NOT NULL,
TotalProducts AS SUM (InStore, InWarehouse)
)
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:
You create a view on the SalesDB database using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
You want the SalesV view to persist data to disk in order to improve performance.
How would you accomplish this task? A. You should add a clustered index to the SalesV view.
B.
You should create a columnstore index on all columns used in the SalesV view.
C. You should drop and recreate the SalesV view as a system view.
D.
You should drop and recreate the SalesV view as a partitioned view.
You work as a database developer at ABC.com. You need to store Microsoft Word documents in a
SQL Server 2012 database table.
The Word documents must not be accessed by Windows applications that do not use TransactSQL queries.
How would accomplish this task?
A. You should store the documents using the VARBINARY(MAX) datatype.
B.
You should store the documents using the VARCHAR(MAX) datatype.
C.
You should store the documents using the FILESTREAM datatype.
D.
You should store the documents using the NVARCHAR(MAX) datatype.
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB with a table named Invoices.
Application developers are developing several in-house applications that will access the Invoices
table. You need to develop a solution that will allow the applications to access the table indirectly
while still allowing them to update the Invoice table.
How would you accomplish this task?
A.
You should create a view on the Invoices table.
B.
You should create a columnstore index on all columns used by the applications.
C. You should allow the applications access to the Invoices table via stored procedures.
D.
You should drop and recreate the Invoices table as a partitioned table.
Which of the following can be used to protect the code in a stored procedure?
A. The ENCRYPBYKEY statement.
B.
The ENCRYPBYASYMKEY statement.
C.
The SET TRUSTWORTHY ON option.
D.
The SET XACT_ABORT ON statement.
E.
The ENCRYPTBYPASSPHRASE statement.
F.
The ENCRYPTBYCERT statement.
G.
The SIGNBYASYMKEY statement.
H.
The CRYPT_GEN_RANDOM statement.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB database is shown in the following database diagram:
You need to write a Transact-SQL query that displays all Customers, whether they have invoices
or not. The query must also display the InvoiceDate for a Customer that has an Invoice.
How would you accomplish this task?
A.
You should make use of a UNION.
B.
You should make use of an INNER JOIN.
C.
You should make use of a CROSS JOIN.
D. You should make use of an OUTER JOIN.
E.
You should make use of a CROSS APPLY.
You work as a SQL Server 2012 database developer at ABC.com. You are developing a batch
process that accepts an input parameter and allows the result set to be joined with a table.
How would you accomplish this?
A.
You should make use of a stored procedure to perform the batch process.
B. You should make use of a table-valued user-defined function.
C.
You should make use of the SET XACT_ABORT ON statement.
D.
You should make use of a scalar user-defined function.
E.
You should make use of a schema-bound user-defined function.
You work as a database administrator at manufacturing company named ABC.com. ABC.com has
a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table
named Sites that was created using the following Transact-SQL code:
CREATE TABLE Sites (
SiteID int NOT NULL PRIMARY KEY,
Location int NOT NULL,
Manager nvarchar(200) NOT NULL,
Week smallint NOT NULL,
ItemsProduced int NOT NULL )
You want to write the Transact-SQL query that returns the number of items produced at each
location for each week. In addition, you want the result set to include a column named
PrevItemsProduced that holds the number of items produced at each location for the week before.
What Transact SQL statement would accomplish this task?
A.
SELECT Location, Week, ItemsProduced,
LEAD(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
B.
SELECT Location, Week, ItemsProduced,
FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
C. SELECT Location, Week, ItemsProduced, LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites
D.
SELECT Location, Week, ItemsProduced,
LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
E.
SELECT Location, Week, ItemsProduced,
CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced
FROM Sites
let see OPTION A and B
let see OPTION C and D
let see OPTION E
let see More detail on OPTION C
You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure for ABC.com’s e-Commerce application.
Your stored procedure must display unique values from one column in multiple columns in the
result set.
How would you accomplish this?
A.
You should make use of the OUTER APPLY operator.
B.
You should make use of a dynamic cursor.
C. You should make use of the PIVOT operator.
D.
You should make use of the CROSS APPLY operator.
E.
You should make use of the UNPIVOT operator.
F.
You should make use of a keyset cursor.
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB as illustrated in the following database diagram:
ABC.com has retail stores in a few major cities across the country. The company wants to
ascertain whether it would be advantageous to open a store in other cities based on feedback
from its customers.
You are required to provide the company’s CEO with a list of Customers who live in a city that
does not have a ABC.com store, along with the customer’s Phone Number and the customer’s
City, and arranged alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?
A.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
B.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
C. SELECT CustomerName, CustomerCity, CustomerPhone FROM Customers WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
D.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
Solution:
both option B and D correct answer but we can choose OPTION D most apporpriate.
You work as a database developer at ABC.com. You are developing a SQL server 2012 database
for ABC.com’s e-Commerce application. The application allows ABC.com employees from
different regions to execute a store procedure based on their location.
The location of the ABC.com employees are determined by an input parameter named @location.
You want to develop a process that will execute the correct stored procedure for every ABC.com
employee based on their location.
How would you accomplish this?
A.
You should make use of a client cursor.
B.
You should make use of a static cursor.
C.
You should make use of a forward-only cursor.
D.
You should make use of a dynamic cursor.
E. You should make use of a keyset cursor.
You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a large database
named SalesDB.
New rows are inserted into the tables in the SalesDB database and updates to existing rows occur
on a high frequency. The inserts and updates often blocked by queries retrieving and reading
data.
How would you prevent queries that retrieve and read data from blocking queries that insert and
update data?
A. You should make use of the SERIALIZABLE ISOLATION LEVEL.
B.
You should make use of the SNAPSHOT ISOLATION LEVEL.
C.
You should make use of the REPEATABLE READ ISOLATION LEVEL.
D.
You should make use of the READCOMMITTED ISOLATION LEVEL.
E.
You should make use of the READPAST ISOLATION LEVEL
You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure that updates rows in several tables.
You want the entire transaction to be rolled back should the stored procedure cause a run-time
error.
How would you accomplish this?
A. You should make use of the SET XACT_ABORT ON statement in the stored procedure.
B.
You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
C.
You should make use of a LOOP hint in the stored procedure.
D.
You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
E.
You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.
F.
You should make use of RAISERROR in the stored procedure.
You work as a database developer at ABC.com. ABC has an in-house application named
ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database named SalesDB.
ABC.com users report that ABCApp3 is functioning sluggishly. You discover that concurrent
updates are causing blockages on the SalesDB database.
How would you ensure that the query to use the original data when updates occur?
A.
You should have the query run in the REPEATABLE READ ISOLATION LEVEL.
B.
You should have the query run in the SERIALIZABLE ISOLATION LEVEL.
C.
You should have the query run in the READCOMMITTED ISOLATION LEVEL.
D. You should have the query run in the SNAPSHOT ISOLATION LEVEL.
E.
You should have the query run in the READPAST ISOLATION LEVEL.
You work as a database developer at ABC.com. ABC has an in-house application named
ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.
You want to run an execution plan against the query that will provide detailed information on
missing indexes.
How would you accomplish this task?
A.
You should make use of the READPAST hint in the queries.
B.
You should make use of the READCOMMITTED hint in the queries.
C. You should make use of the SET SHOWPLAN_XML ON statement in the query.
D.
You should make use of the SET STATISTICS XML ON statement in the query.
E.
You should make use of the SET XACT_ABORT OFF statement in the query.
F.
You should make use of the SET CONTEXT_INFO statement in the query.
ou work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named SalesDB.
The SalesDB database is used by an in-house application named ABCApp3. ABC.com users
report that ABCApp3 is functioning sluggishly.
You discover that application consumes considerable memory when it runs single-use dynamic
queries against the SalesDB database. You suspect that these queries are making excessive use
of procedure cache.
How would you reduce procedure cache if you cannot create new indexes on the SalesDB
database?
A.
You should replace the queries with recursive stored procedures.
B.
You should add make use of the INCLUDE clause in the index.
C.
You should make use of the READPAST hint in the queries.
D.
You should make use of the READCOMMITTED hint in the queries.
E. You should make use of the optimize for ad hoc workloads option.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named ABCDB.
The
ABCDB database is used by an in-house application named ABCApp3 that
queries a readonly table with a clustered index. ABC.com users report
that ABCApp3 is functioning sluggishly.
You suspect query the application uses is causing the problem. You analyze the query and
discover that column referenced in the WHERE clause is not part of the clustered index. You also
notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five
columns.
How would you improve the efficiency of this query?
A.
You should replace the query with recursive stored procedure.
B.
You should replace the COUNT (*) clause with a persisted computed column.
C.
You should create nonclustered indexes on all columns used in the query.
D.
You should create a filtered index on the column used in the WHERE clause.
E.
You should add an INCLUDE clause to the clustered index.
F. You should create a columnstore index on all columns used in the query.
G.
You should create a unique clustered index on the column used in the WHERE clause.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database
You need to write a Transact-SQL query that displays rows of data in the following XML format:
<Suppliers>
<CompanyName>Company Name</CompanyName>
<ContactName>Contact Name</ContactName>
<Phone>453 3545 5224</Phone>
<Products>
<ProductID>10</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>559.00</UnitPrice>
<UnitsInStock>12</UnitsInStock>
</Products>
<Products>
<ProductID>132</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>59.00</UnitPrice>
<UnitsInStock>102</UnitsInStock>
</Products>
<Products>
<ProductID>259</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>599.00</UnitPrice>
<UnitsInStock>6</UnitsInStock>
</Products>
</Suppliers>
Which of the following SELECT statement would you write? A. SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID FOR XML AUTO, ELEMENTS
B.
SELECT s.SupplierID, s.CompanyName, s.ContactName, s.Phone, p.ProductID, p.UnitPrice,
p.UnitsInStock
FROM Suppliers AS s
INNER JOIN Products AS p ON s.SupplierID = p.SupplierID
FOR XML
C.
SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName,
Suppliers.Phone, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
FOR XML AUTO
D.
SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName,
Suppliers.Phone, Products.ProductID, Products.UnitPrice, Products.UnitsInStock
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
FOR XML AUTO, RAW
Your database contains two tables named
DomesticSalesOrders and InternationalSalesOrders. Both tables contain
more than 100 million rows. Each table has a Primary Key column named
SalesOrderId. The data
in the two tables is distinct from one
another. Business users want a report that includes aggregate
information about the total number of global sales and total sales
amounts. You need to ensure that your query executes in the minimum
possible time. Which query should you use?
A
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
B
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
C
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
D
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
Solution:
Correct Answer is OPTION A
Sample Code:
CREATE TABLE InternationalSalesOrders(SalesOrderId int identity(20009,20) not null, SalesAmount int)
Declare @i int
Declare @j int
set @i=1
set @j=9564
while(@i<=300000)
begin
insert into InternationalSalesOrders(SalesAmount)
SELECT @i+@j+32/@i
Set @i=@i+1
end
CREATE TABLE DomesticSalesOrders(SalesOrderId int identity(20009,20) not null, SalesAmount int)
Declare @i int
Declare @j int
set @i=1
set @j=9564
while(@i<=300000)
begin
insert into DomesticSalesOrders(SalesAmount)
SELECT @i+@j+32/@i
Set @i=@i+1
end
SELECT COUNT(*) FROM DomesticSalesOrders
SELECT count(*) FROM InternationalSalesOrders
SELECT * FROM DomesticSalesOrders D
join InternationalSalesOrders I on D.SalesOrderId=I.SalesOrderId
let see Answer is OPTION A see below
kindly OPTION B how it take result in time
kindly OPTION C how it take result in time
kindly OPTION D how it take result in time
So Answer is OPTION A
You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:
Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports. What should you do? A. Convert the view into a table-valued function. B. Convert the view into a Common Table Expression (CTE). C. Convert the view into an indexed view. D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
Correct Answer: C
You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:
You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use? A. ALTER TABLE Orders
ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer
(CustomerId)
B. ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders
(CustomerId)
C. ALTER TABLE Orders
ADD CONSTRAINT CK_Crders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
D. ALTER TABLE Customer
ADD OrderId INT NOT NULL;
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES Orders
(CrderlD);
E. ALTER TABLE Orders
ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
Correct Answer: A
You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view
that is used to look up their email addresses and phone numbers. The view has the following
definition:
You need to ensure that users can update only the phone numbers and email addresses by using
this view. What should you do?
A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Create an INSTEAD OF UPDATE trigger on the view.
C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an
index on the view.
D. Create an AFTER UPDATE trigger on the view.
Correct Answer: B
You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and
OrderDetails tables by using the following definition
You need to ensure that users are able to modify data by using the view. What should you do?
A. Create an AFTER trigger on the view.
B. Modify the view to use the WITH VIEW_METADATA clause.
C. Create an INSTEAD OF trigger on the view.
D. Modify the view to an indexed view.
Correct Answer: C
You have a view that was created by using the following code:
You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must
meet the following requirements:
Accept the @T integer parameter.
Use one-part names to reference columns.
Filter the query results by SalesTerritoryID.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue
FROM Sales.OrdersByTerritory-----------VIEW name
WHERE SalesTerritoryID = @T
)
You have a database that contains the tables shown in the exhibit.
You deploy a new server that has SQL Server 2012 installed. You need to create a table named
Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.
Which code
segment should you use?
To answer, type the correct code in the answer area.
Solution:
On the 461 certification exam, several questions require:
The code must NOTuseany object delimiters
Any idea what this means?
so write code with out [] and ""
Correct Answer:
CREATE TABLE Sales.OrderDetails (
ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)
You have a database that contains the tables shown in the exhibit.
You need to create a view named uv_CustomerFullName to meet the following requirements:
The code must NOT include object delimiters.
The view must be created in the Sales schema.
Columns must only be referenced by using one-part names.
The view must return the first name and the last name of all customers. The view must prevent the underlying structure of the customer table from being changed.
The view must be able to resolve all referenced objects, regardless of the user's default
Schema.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer:
CREATE VIEW Sales.uv_CustomerFullName WITH SCHEMABINDING
AS
SELECT FirstName, LastName
FROM Sales.Customers
You have a database that contains the tables shown in the exhibit.
You need to create a query that calculates the total sales of each OrderId from the Sales.Details
table. The solution must meet the following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer:
SELECT OrderID, SUM(ExtendedAmount) AS TotalSales
FROM Sales.Details
GROUP BY OrderID
ORDER BY OrderID
You have a database that contains the tables as shown in the exhibit.
You have the following query:
You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer:
SELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty),
MAX(DiscountAmount)
FROM Sales.Details
GROUP BY SalesTerritoryID , ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC
You have a database that contains the tables shown in the exhibit.
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard. Which code segment should you use?
To answer, type the correct code in the answer area. Correct Answer:
SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.LastName
ORDER BY O.OrderDate DESC
You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of
the XML type named XML1. The solution must ensure that XML1 is validated by using
Sales.InvoiceSchema. Which code segment should you use?
To answer, type the correct code in the answer area.
You have a database that contains the tables as shown in the exhibit.
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution
must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer:
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.UnitPrice DESC)
AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
You have a database that contains the tables shown in the exhibit.
You have an application named Appl. You have a parameter named @Count that uses the int data
type. App1 is configured to pass @Count to a stored procedure. You need to create a stored
procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:
NOT use object delimiters.
Minimize sorting and counting.
Return only the last name of each customer in alphabetical order.
Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer:
CREATE PROCEDURE usp_Customers @Count int
AS
SELECT TOP(@Count) Customers.LastName
FROM Customers
ORDER BY Customers.LastName
Answer:
You are Member of SQL developer team. You Need to create a new view named vwEmployeePhoneList that is part of the HumanResources schema. The view should select the LastName,FirstName,PhoneExt columns from the HumanResources.Employees table.
Which code Segment should you use?
A.
CREATE VIEW vwEmployeePhoneList
AS
select LastName,FirstName,PhoneExt
FROM humanResources.Employees
B.
Create View HumanResources.vwEmployeePhoneList
AS
select LastName,FirstName,phoneExt
from HumanResources.Employees
C.
Alter view HumanResources.vwEmployeePhoneList
as
Select LastName,FirstName,PhoneExt
from humanResources.Employees
D.
NEW VIEW HumanResources.vwEmployeePhoneList
as
Select LastName,FirstName,PhoneExt
from humanResources.Employees
Correct Answer: A
Which of these view can be used to modify the data of an underlying base table?
Choose the Correct Answer
A.
CREATE VIEW PatientView
as
SELECT TOP 10 Patients.PatientName,Patients.PatientAddress
FROM Patients
B.
Create view PatientView
AS
select top 10 Patients.PatientName,
Patients.PatientAddress
From Patients WITH CHECK OPTION
C.
CREATE VVIEW PatientView
as
SELECT Count(PatientID) AS NumberOfPatients
from Patients
Correct Answer: Option A
Because if we use WITH CHECK OPTION or COUNT function in Statement that is non updatable in table so option A is correct answer.
You develop a Microsoft SQL Server 2012 database. The database contains tables named Patients,Prescriptions, Visits. The Transact-SQL Query that was used to create these tables is show below
CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
PatientName VARCHAR(255) NOT NULL,
PatientAddress VARCHAR(1024)
)
CREATE TABLE Prescriptions
(
PrescriptionID int not null Primary key,
PrescriptionText Varchar(255) not null,
PatientID int FOREIGN KEY REFERENCES Patients(PatientID)
)
CREATE TABLE Visits
(
VisitID int NOT NULL Primary key,
VisitReason Varchar(255) not null,
PatientID INT FOREIGN KEY REFERENCES Patients(PatientID)
)
Patients are considered active If they meet all of these requirements:
*Have at least one visit that is recorderd in the visits table
*Have at least one Prescription that is recorded in the Prescriptions table.
You need to create a view that shows only the name and address of all unique active patients.
which Transact-SQL Statement should you use?
choose the correct answer
A
CREATE VIEW PatientView
as
SELECT PatientName,PatientAddress FROM Patients
WHERE (EXISTS (select PatientID from Prescriptions
WHERE (Patients.PatientID=PatientID)) AND EXIST
(select PatientID from visits WHERE (Patients.PatientID=PatiendID))
B.
create view patientview
AS
select PatientName,PatientAddress
FROM (select Distinct PatientID,PatientName,PatiendAddress
FROM Patients
WHERE (EXISTS(SELECT PrescriptionID,PrescriptionText,PatientID
FROM Presecriptions
where (Patients.PatientID=PatientID))
OR EXISTS (select VisitID,VisitReason,PatientID FROM Visits
where (Patients.PatientID=PatientID))))
PatientData
C.
D.
Correct Answer is OPTION A
Solutions:
Here above Table PatientID 4578,2147 only exists in both Prescriptions,Visits Table.
So Option A would be Correct Answer. See below
OptionA Gives correct 2 values and OptionB gave not that value(Patient A extra)
Sample Code:
USE [AdventureWorks]
CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
PatientName VARCHAR(255) NOT NULL,
PatientAddress VARCHAR(1024)
)
CREATE TABLE Prescriptions
(
PrescriptionID int not null Primary key,
PrescriptionText Varchar(255) not null,
PatientID int FOREIGN KEY REFERENCES Patients(PatientID)
)
CREATE TABLE Visits
(
VisitID int NOT NULL Primary key,
VisitReason Varchar(255) not null,
PatientID INT FOREIGN KEY REFERENCES Patients(PatientID)
)
Insert into Patients
SELECT 6526,'Patient A','CBE'
UNION ALL
SELECT 4578,'Patient E','CBE'
UNION ALL
SELECT 8946,'Patient X','CBE'
UNION ALL
SELECT 2147,'Patient T','CBE'
Insert into Prescriptions
SELECT 025,'Kindly Take Xray',6526
UNION ALL
SELECT 085,'Need Operation',4578
UNION ALL
SELECT 045,'Take it tablets correctly',2147
UNION ALL
SELECT 015,'Need Surgeon consuldation',2147
insert into Visits
SELECT 801,'Meet Doctor',4578
union all
select 805,'Meet Surgeon',2147
SELECT * FROM Patients
SELECT * FROM Prescriptions
SELECT * FROM Visits
CREATE VIEW PatientViewOptionA
as
SELECT PatientName,PatientAddress FROM Patients
WHERE (EXISTS (select PatientID from Prescriptions
WHERE (Patients.PatientID=PatientID)) AND EXISTS
(select PatientID from Visits WHERE (Patients.PatientID=PatientID)))
SELECT * FROM PatientViewOptionA
SELECT * FROM patientviewOptionB
Create view patientviewOptionB
AS
select PatientName,PatientAddress
FROM (select Distinct PatientID,PatientName,PatientAddress
FROM Patients
WHERE (EXISTS(SELECT PrescriptionID,PrescriptionText,PatientID
FROM Prescriptions
where (Patients.PatientID=PatientID))
OR EXISTS (select VisitID,VisitReason,PatientID FROM Visits
where (Patients.PatientID=PatientID))))
PatientData
You administer a Microsoft SQL Server database that supports a
banking transaction management application. You need to retrieve a list
of account holders who live in cities that do not have a branch
location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster) D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster) Answer: AB
You administer a Microsoft SQL Server 2012 database
named ContosoDb. The database contains a table named Suppliers and a
column named IsActive in the Purchases schema. You create a new user
named ContosoUser in ContosoDb. ContosoUser has no permissions to the
Suppliers table. You need to ensure that ContosoUser can delete rows
that are not active from Suppliers. You also need to grant ContosoUser
only the minimum required permissions. Which Transact-SQL statement
should you use?
A
GRANT SELECT ON Purchases.Suppliers TO ContosoUser
B
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
C
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
D
GRANT DELETE ON Purchases.Suppliers TO ContosoUser
Solution:
see sample table
Initial properties of ContosoUser see below
Initial State we can see user cant able to select table see below of that user
let see option A
GRANT SELECT ON dbo.Suppliers TO ContosoUser
if issue option A ContosoUser can only select that table so it cant use to delete
so it is not correct answer
let see option B
it gives error
let see option C
After Execute SP of ContosoUser it is working fine see below
so OPTION C is the correct Answer
let see OPTION D
GRANT DELETE ON dbo.Suppliers TO ContosoUser
after that above command if run below commands
Delete from Suppliers where IsActive=0
If you execute it will throw message it needs additional select permission
you need select permission on that table as see in option A
so option d is not correct answer it will be correct but you need to execute both OPTION A and OPTION D concurrently.
additional info if you need to get back permission you can use below T-SQL
REVOKE SELECT ON dbo.Suppliers TO ContosoUser
REVOKE DELETE ON dbo.Suppliers TO ContosoUser REVOKE EXECUTE ON dbo.PurgeInactiveSuppliers TO ContosoUser
Also ContosoUser have only execute permission so ContosoUser user can't able to alter it
Also you can see TBK2012Reader user can able to execute storedprocedure as see below
GRANT EXEC ON dbo.PurgeInactiveSuppliersWITHCaller TO [TBK2012Reader] GO
Now TBK2012 Can able to remove that isactive field as see below
The EXECUTE AS
clause can be added to stored procedures, functions, DML triggers, DDL
triggers, queues as well as a stand alone clause to change the users
context. This clause is simply added to the code as follows:
CREATE PROCEDURE dbo.TestProcedure WITH EXECUTE AS OWNER
There are basically five types of impersonation that can be used:
SELF - the specified user is the person creating or altering the module
CALLER - this will take on the permissions of the current user
OWNER - this will take on the permissions of the owner of the mohttps://www.blogger.com/blogger.g?blogID=8453781330643442479#editor/target=post;postID=4119980947312551542;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=14;src=linkdule being called
'user_name' - a specific user
'login_name' - a specific login
CREATE PROCEDURE dbo.TestProcedure WITH EXECUTE AS OWNER AS BEGIN SQL Statement END
You Create a table that has the StudentCode,SubjectCode, and Marks Columns to record mid-year for students. The Table has marks obtained by 50 Students for various Subjects. You need to ensure that the following requirements are met:
Students must be ranked based on their average marks.
if one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned
Which Transact-SQL query should you use?
A SELECT StudentCode as Code,RANK() OVER(ORDER BY AVG(Marks) DESC) AS value FROM StudentMarks GROUP BY StudentCode
B SELEC Id,Name,Marks,DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
C SELECT StudentCode as Code,DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
D SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks Group by StudentCode
E SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode,Marks AS Marks,RANK() OVER(PARTITON BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
F SELECT StudentCode as Code,Marks as value FROM (select StudentCode,Marks as marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
G SELECT StudentCode AS code,Marks Value FROM (SELECT StudentCode,Marks as Marks,RANK(0 OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
H SELECT StudentCode as Code,Marks As Value FROM (SELECT StudentCode,Marks as Marks,RANK() OVER(PARTITON BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank=1
Solutions:
Sample Table
So Correct Answer is option A
You create a table that has the StudentCode, SubjectCode, and Marks
columns to record mid-year marks for students. The table has marks
obtained by 50 students for various subjects. You need to retrieve the
students who scored the highest marks for each subject along with the
marks. Which Transact-SQL query should you use?
A
SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
B
SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
C
SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks
D
SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
E
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
F
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
G
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp
WHERE Rank = 1
H
SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
NoteTo avoid an error, the NORECOVERY option is necessary.
| 
