Which three Transact-SQL statements should you use?
DRAG DROP
You use Microsoft SQL Server 2012 to develop a database application.
You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index.
You also need to ensure that the column supports an index.
Which three 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.)
Which six Transact-SQL statements should you use?
DRAG DROP
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.)
Which four Transact-SQL statements should you insert at line 07?
DRAG DROP
You create the following stored procedure. (Line numbers are included for reference only.)
You need to ensure that the stored procedure performs the following tasks:
If a record exists, update the record.
If no record exists, insert a new record.
Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate
statements from the list of statements to the answer area and arrange them in the correct order.)
Which four Transact-SQL statements should you use?
DRAG DROP
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.)
You need to ensure that without modifying the SELECT statements, you enable users to query the historical data
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a table that has two partitions. The first partition contains the
current data, and the second partition contains historical data. There is a high frequency of
data manipulation on the current data. The testers report that they are frequently
unsuccessful in querying the historical data only. You discover that locks are being
escalated to the table-level lock. You need to ensure that without modifying the SELECT
statements, you enable users to query the historical data. You want to achieve this goal by
using the minimum amount of administrative effort and minimally affecting other queries.
What should you do?
Which data type should you choose?
You need to design a table that will contain a monetary value. The value must support five
digits to the right of the decimal point. Which data type should you choose?
You need to design the tables to meet the following requirements…?
You are designing a new database. The tables of the database will be replicated to multiple
offices. You need to design the tables to meet the following requirements: • A row identifier
must be set when a row is created • The row identifier must be unique across the entire
organization What should you recommend?
You need to create a table named OrderDetails on a new server
CORRECT TEXT
You need to create a table named OrderDetails on a new server. OrderDetails must meet the
following requirements:
Contain a new column named LineltemTotal that stores the product of ListPrice and Quantity for
each row.
The calculation for a line item total must not be run every time the table is queried.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.
You need to produce the required output by querying the database without using system stored procedures
You are a database developer. You plan to create a database by using SQL Server 2008.
The database has a table named Employees. The table contains records of employees and
their managers. The table includes the EmployeelD and ManagerlD fields. The EmployeelD
values are unique. The value in the M anagerlD field is the employee I D of t he e mployee’s
m anager. A Web s ite r equires X ML formatted output of all managers and employees to
be displayed as a tree diagram. You need to produce the required output by querying the
database without using system stored procedures. What should you do?
You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specif
CORRECT TEXT
You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit
button.)
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 App1 that returns only the
number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.
