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 achieve this goal by using the minimum amount of changes to the existing applications. What should you create for each application? Which object should you use? Inline user-defined function. Stored procedure.
Table-valued user-defined function. Scalar user-defined function. Which Transact-SQL statement or statements should you use? Each correct answer presents part of the solution. Choose all that apply. GetCustomerRating Which table hint should you use? The indexes on the tables are as shown in the exhibit. 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. Update statistics on SalesOrderId on both tables. Your database contains a table named Purchases. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Page 3 of 7 Loading 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. 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.
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. Which Transact- SQL statement should you use? 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.
Alter the view. Drop the 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.
The Products table has the following definition: You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated. 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. 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? 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. You support a database structure shown in the exhibit. Page 4 of 7 Loading 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? 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? The database contains a table named Employee.
Part of the Employee table is shown in the exhibit. 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 unique constraint? Unless stated above, no columns in the Employee table reference other tables.
On which column in the Employee table should you use an identity specification to include a seed of 1, and an increment of 1? ProductName AND p. ProductName AND cte. You have permissions on both Database1 and Database2. Pricing does not include applicable taxes. Please confirm exact pricing with the exam provider before registering to take an exam. After the retirement date, please refer to the related certification for exam requirements.
Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode.
Save Table of contents. For currently available options, please refer to the Browse Certifications and Exams page. It was determined that no updates to exam objectives were required. If the virtual machines in your classroom are able to connect to the internet and you have a Windows Azure account you may be able to connect to your Azure server and database. Many of the labs in the rest of this course are enabled for you to perform the lab while connected to your own Azure database in the cloud.
After completing this module, students will be able to:. Course Directory [training on all levels]. This course maps to the skills and knowledge measured by Microsoft Exam , and in conjunction with on-the-job experience, can help prepare you for the exam. Basic knowledge of the Microsoft Windows operating system and its core functionality. Course Outline.
Provision a SQL Azure server and configure its security. Use column and table aliases. Write simple CASE expressions. Write queries that use inner joins. Write queries that use outer joins. Write queries that use self-joins and cross joins. Write queries using character data types. Write queries using date and time data types. Use conversion functions. Use logical functions. Use functions that work with NULL.
0コメント