- (Exam Topic 1)
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 runtime error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
Correct Answer:
E
Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx
- (Exam Topic 2)
You are a database developer of a Microsoft SQL Server database.
The database contains a table named Instructor that has the following definition:
< ><>>< ><>>Which Transact-SQL statement should you use?
Correct Answer:
C
References: http://msdn.microsoft.com/en-us/library/ms189049.aspx
- (Exam Topic 2)
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)<>>< > >< > >< > >< > >< > >< > >>
Solution:
Complete the SQL statement.<>>>
Does this meet the goal?
Correct Answer:
A
- (Exam Topic 1)
You use a Microsoft SQL Server database. You want to create a table to store files.
You need to ensure that the following requirements are met:
< > >< > >< > >>< ><>>< ><>>< ><>>
Correct Answer:
D
References:
https://docs.microsoft.com/en-us/sql/relational-databases/blob/create-alter-and-drop-filetables?view=sql-server-
- (Exam Topic 2)
You develop an SQL Server database. The database contains a table that is defined by the following T-SQL statements:
< ><>>>>< >
Solution:
Example:
let us write a query which will delete all duplicate data in one shot. We will use a CTE (Common Table Expression) for this purpose. We will read in future posts what a CTE is and why it is used. On a lighter note, CTE's can be imagined as equivalent to temporary result sets that can be used only in an underlying SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
;WITH CTE AS (
SELECT Name
, City
, [State]
, ROW_NUMBER() OVER(PARTITION BY Name, City, [State] ORDER BY [Name]) AS Rnum
FROM Persons
)
DELETE FROM CTE WHERE Rnum <> 1
In the code by saying WHERE Rnum <> 1, we are asking SQL Server to keep all the records with Rank 1, which are not duplicates, and delete any other record. After executing this query in SQL Server Management Studio, you will end up with no duplicates in your table. To confirm that just run a simple query against your table.
Reference: How to Remove Duplicates from a Table in SQL Server
http://social.technet.microsoft.com/wiki/contents/articles/22706.how-to-remove-duplicates-from-a-table-in-sql-s
Does this meet the goal?
Correct Answer:
A
