Microsoft-TS Microsoft SQL Server 2008 - Database Development Set 3

You administer a Microsoft SQL Server 2008 R2 database that has a table named Customer. The table has the following definition: CREATE TABLE Customer( CustomerID int NOT NULL PRIMARY KEY, FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, CustomerAddress varchar(1024)) The database also has a table named PreferredCustomerList.Data will be added to the PreferredCustomerList table regularly. The PreferredCustomerList table has the following definition: CREATE TABLE PreferredCustomerList (FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL) You need to create a view that returns all records and columns of the Customer table that are also present in the PreferredCustomerList table. Which Transact-SQL statement should you use?

You have a database server that has four quad-core processors. This database server executes complex queries that are used to generate reports. You need to force a query to use only one processor core without affecting other queries. Which option should you use?

You need to implement a common table expression (CTE). Which code segment should you use?

You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources. You need to deploy this assembly by using the minimum required permissions. You must ensure that your database remains as secure as possible. Which options should you set?

You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the top 5% of products that have been sold most frequently. Which Transact-SQL code segments should you use?

You have two tables named MainTable and ArchiveTable. You need to move data older than 30 days from MainTable into ArchiveTable. Which code segment should you use?

Your database contains sales information for millions of orders. You need to identify the orders with the highest average unit price and an order total greater than 10,000. The list should contain no more than 20 orders. Which query should you use?

You are responsible for a SQL Server database.You require the tables to be added or altered only on the first day of the month. You need to ensure that if the tables are attempted to be modified or created on any other day, an error is received and the attempt is not successful. Which Transact-SQL statement should you use?

You have a table named Employees. You want to identify the supervisor to which each employee reports. You write the following query. SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperVisorName] FROM Employees e You need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?

You have a table named Stores that has an XML column named OpenHours. This column contains the opening and closing times. ... You need to write a query that returns a list of stores and their opening time for a specified day. Which code segment should you use?