70-433 Exam Questions

You have created an assembly that utilizes unmanaged code to access external resources. You need to deploy the assembly with the appropriate permissions. Which permission set shoul...

You have tables named Products and OrderDetails. The Products table has a foreign key relationship with the OrderDetails table on the ProductID column. You have the following Trans...

You are using TRY...CATCH error handling. You need to raise an error that will pass control to the CATCH block. Which severity level should you use?

You have a table named Orders. You have been tasked to modify your company's main database to remove all inactive order rows. You are developing a stored procedure that will enable...

You have a transaction that uses the repeatable read isolation level. This transaction causes frequent blocking problems. You need to reduce blocking. You also need to avoid dirty...

You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction. You need to set an option...

You have a table named JobCandidate. You are tasked to delete a row in the JobCandidate table. You need to write a transaction that allows the database to be restored to the exact...

You have the following table named Sales. You need to return sales data ordered by customer name and date of sale. For each customer, the most recent sale must be listed first. Whi...

You have a table named Sales.SalesOrderHeader and a table named Person.Person. You are tasked to write a query that returns SalesOrderID and SalesPersonName that have an OrderDate...

You have a table named Sales.PotentialClients. This table contains a column named EmailAddress. You are tasked to develop a report that returns valid ".com" email addresses from Sa...

You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1. You need to write a query to insert a new order into the Orders table for...

You have the following two tables. The foreign key relationship between these tables has CASCADE DELETE enabled. You need to remove all records from the Orders table. Which Transac...

You have a SQL Server database. The database contains two schemas named Marketing and Sales. The Marketing schema is owned by a user named MarketingManager. The Sales schema is own...

You have tables named Sales.SalesOrderDetails and Sales.SalesOrderHeader. You have been tasked to update the discount amounts for the sales of a particular salesperson. You need to...

You have a table named Product. You need to increase product prices for only the vendor named Coho Winery by 10 percent and then return a list of the products and updated prices. W...

You have two tables named dbo.Products and dbo.PriceChange. Table dbo.Products contains ten products. Five products are priced at $20 per unit and have PriceIncrease set to 1. The...

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?

You have been tasked with creating a table named dbo.Widgets. You need to insert five rows into the dbo.Widgets table and return WidgetID for each of the five rows that have been i...

You have the following two tables. Products ProductID ProductName VendorID 1 Product1 0 2 Product2 1 3 Product3 1 4 Product4 0 ProductChanges ProductID ProductName VendorID 1 Produ...

You have two tables. A table named Student.CurrentStudents contains the names of all students enrolled for the current year. Another table named Student.NewYearRoster contains the...

You create and populate two tables by using the following Transact-SQL statements: CREATE TABLE CurrentStudents (LastName VARCHAR(50), FirstName VARCHAR(50), Address VARCHAR(100),...

You are writing a query that returns a list of products that have grossed more than $10,000.00 during the year 2007. You need to insert the following filter expression into the que...

You have a table named Sales. You are tasked to list products that have been sold to less than ten customers. You need to write a query to achieve the task. Which Transact-SQL stat...

You have two tables named Customers and Orders. for customers that have placed at least one order, you need to produce a list of customer names and the number of orders for each cu...

You have a table named Products. The table contains a column named Color. You need to write a Transact-SQL statement that calculates the percentage of products of each product colo...

You have two tables named SalesPerson and SalesTerritory. You need to create sample data by using a Cartesian product that contains the data from the SalesPerson and SalesTerritory...

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.Emplo...

You have a table named Subcategories that contains subcategories for socks, vests and helmets. You have another table named Products that contains products only from the subcategor...

You have two tables named dbo.CurrentProducts and dbo.ArchiveProducts. You have the following query: SELECT ProductID, Name FROM dbo.CurrentProducts UNION ALL SELECT ProductID, Nam...

You have two tables named Products and NewProducts that have identical structures. You have the following query (Line numbers are included for reference only): 01 SELECT Product, D...

You are tasked to create a table that has a column that must store the current time accurate to ten microseconds. You need to use a system function in conjunction with the DEFAULT...

You need to round the value 1.75 to the nearest whole number. Which code segment should you use?

You have a column named TelephoneNumber that stores numbers as varchar(20). You need to write a query that returns the first three characters of a telephone number. Which expressio...

You are a database developer located in Seattle. You have a client in Melbourne, which is in a different time zone from Seattle. You have been using the datetimeoffset data type an...

You have a database that contains two tables named ProductCategory and ProductSubCategory. You need to write a query that returns a list of product categories that contain more tha...

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 lis...

Your company manufactures and distributes bicycle parts. You have a full-text catalog on the Inventory table which contains the PartName and Description columns. You also use a ful...

Your company manufactures and distributes bowling balls. You have a full-text catalog named ftCatalog which contains the ftInventory index on the Products table. Your marketing dep...

You have a server named Contoso with multiple databases. You have been tasked to write a PowerShell script to determine which databases on the server are larger than 100GB. You ope...

You are given a database design to evaluate. All of the tables in this database should have a clustered index. You need to determine the tables that are missing a clustered index b...

You need to identify which tables are referenced by name in a stored procedure that does not use dynamic SQL. Which catalog view should you use?

You need to create a stored procedure that accepts a table-valued parameter named @Customers. Which code segment should you use?

CREATE TRIGGER XY ON Inventory.XY FOR UPDATE,INSERT,DELETE EXECUTE AS SELF WebUser do INSERT statement on Inventory.XY table Which USER will the trigger use?

You have a list of Subscribers in a table. You were given an updated list of subscribers. You need to remove the records that are not in the new list of subscribers. Which statemen...

You need to manually raise an error. The error message that will be returned will display a message that contains parameters 1, 2, 3 sp_addmessage @msgnum = 6000, @severity = 16, @...

You are reviewing a trigger in the database which was deployed with the folowing script: EXECUTE AS USER = 'BuildUser' GO CREATE TRIGGER Inventory.TR_Stock ON Inventory.Stock FOR I...

Your database contains Products and Orders tables. You need to write a query which return ProductID of the products which have not been placed in any order. Which operator can you...

You administer a Microsoft SQL Server database named AdventureWorks2008R2. The database has a table that has the following definition: You plan to implement custom error handling f...

You are the administrator of a SQL Server database. Database table modifications and additions must occur only between 11:00P.M. and midnight. You need to ensure that if database t...

You are the database developer for an order-processing application. After a customer places an order, a confirmation message must be sent to the customer. The following Transact-SQ...