Microsoft Querying Data with Transact SQL

You need to create a database object that meets the following requirements: accepts a product identified as input calculates the total quantity of a specific product, including quantity on hand and quantity on order caches and reuses execution plan returns a value can be called from within a SELECT statement can be used in a JOIN clause What should you create?

You need to create an indexed view that requires logic statements to manipulate the data that the view displays. Which two database objects should you use? Each correct answer presents a complete solution.

You have a database that contains the following tables: You need to write a query that returns a list of all customers who have not placed orders. Which Transact-SQL statement should you run?

You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source. You need to ensure that the SELECT statement in the stored procedure meets the following requirements: Data being processed must be usable in several statements in the stored procedure. Data being processed must contain statistics. What should you do?

You have a disk-based table that contains 15 columns. You query the table for the number of new rows created during the current day. You need to create an index for the query. The solution must generate the smallest possible index. Which type of index should you create?

You need to create a database object that meets the following requirements: accepts a product identifies as input calculates the total quantity of a specific product, including quantity on hand and quantity on order caches and reuses execution plan returns a value can be called from within a SELECT statement can be used in a JOIN clause What should you create?

You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records for approximately two million people. You create the table by running the following Transact-SQL statement: You create the following table valued function to generate lists of people: You need to build a report that shows meetings with at least two people only. What should you use?

You are building a stored procedure named SP1 that calls a stored procedure named SP2. SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a temporary table. You need to ensure that SP2 returns a text value to SP1. What should you do?

You have a table named Table1 that contains 200 million rows. Table1 contains a column named SaleDate that has a data type of DateTime2(3). Users report that the following query runs slowly. You need to reduce the amount of time it takes to run the query. What should you use to replace the WHERE statement?

You have a database that tracks customer complaints. The database contains a table named Complaints that includes the following columns: You need to create a query that lists complaints about defective products. The report must include complaints where the exact phrase "defective product" occurs, as well as complaints where similar phrases occur. Which Transact-SQL statement should you run?