Microsoft Exam |19 questions with verified correct answers

You need to create a module that performs the following tasks: *Accepts an order number *Sets the value of the Status column in the Orders table for that order number to Shipped *Retrieves a tracking number to the Shipping table *Returns the number of items shipped and the customer's e-mail address What should you create? Stored procedure Using a stored procedure: improves performance because parsing is not required with each execution. An inline table-valued function: includes only a single SELECT statement. You can retrieve data sorted by the LastName column by using a: SELECT statement with a GROUP BY clause. Which statement shows a valid use of the CREATE VIEW statement? CREATE VIEW TopStudents AS SELECT TOP 10 FirstName, LastName, Grade FROM Grades ORDER BY Grades You execute the following query: SELECT LoanNumber, OriginalLoanAmt, InterestRate FROM Loan WHERE BorrowerID IN (SELECT BorrowerID WHERE BorrowerState='TX') What is this an example of? a subquery What data structure physically stores the data in a relational database table that has a single nonclustered index? Heap You have created a view using the statement shown in the exhibit. You need to modify the view so that only movies with a rating of G or PG are visible through the view. What should you use? An ALTER VIEW statement with a WHERE clause At a minimum, what is required within a column definition a CREATE TABLE statement? A column name and data type You are creating a relational database that will store information about physicians and their patients. Each patient may have more than one physician. Each physician may have multiple patients. What should you include? Three tables and two one-to-many relationships What statement is a data definition language (DDL) statement? ALTER You are creating a relational database that will be used to track car rentals. Each renter can rent multiple cars. A car can be rented by multiple drivers. You need to reduce the amount of redundant data. How should you store the car rental data? Create a Cars table, a Renters table, and a CarRentals table. You are creating a view restricting the visible rows using a WHERE clause. You need to ensure that no changes made through the view cause the data to not conform to the view's WHERE clause condition. What should you do? Include WITH CHECK OPTION in your CREATE VIEW statement. You have a relational database that stores information about projects within your organization. You have a Project table and a ProjectManager table as shown in the exhibit. Why would you issue a DML statement? To add a new project when a new project is launched You are creating a table that has a column named Description. The Description column must store a string between 1 and 3500 characters in length. The Description column must support text written in multiple languages. Which data type will require the least amount of storage space? a subquery What is one reason to denormalize a database? nvarchar(3500) You have database that has the Employees table shown in the exhibit. The MangerID column has a foreign key constraint that references the EmployeeID column. The ManagerID column allows Nulls. You need to write a query that retrieves a two-column result set that shows the full names of the employees who report to each manager and the employees who do not have an assigned manager. Which join type should you use? To improve data retrieval performance The TRUNCATE TABLE statement: deletes all rows from the table, but does not log each row deleted. Your database includes the tables shown in the exhibit. You need to users to query the ProductID, StoreID, Description, and Price for only products at stores in California. Which statement should you