Certification Exam Prep Questions For Querying Microsoft SQL Server 2012/2014 (70-461)


QuickStart is now offering assessment questions for Querying Microsoft SQL Server 2012/2014 (70-461). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions; from a recent version of the test you will take for Querying Microsoft SQL Server 2012/2014 (70-461)


Arrow
 

1

You develop a Microsoft SQL Server 2012 database. The database contains a table named Products. The Transact-SQL statement used to create this table is shown below: CREATE TABLE Products ( ProductlD INT NOT NULL, ProductTested INT NOT NULL CONSTRAINT ProductTested_value CHECK ProductTested IN (0, 1)), ProductName CHAR(50) NOT NULL, ProductDescription CHAR(50) NULL The ProductTested column in this table is used to indicate whether a product was tested for compatibility. You need to ensure that users can modify ProductDescription only for products that are not tested. What should you do first?

2

You administer a Microsoft SQL Server 2012 database. You use SQL query optimizer to tune the query shown below: SELECT p.LastName, p.FirstName, v.Name FROM Person AS p INNER JOIN Employee AS e ON e.BusinessEntitylD = p.BusinessEntitylD INNER JOIN PurchaseOrderlD AS poid ON e.BusinessEntitylD = poid.EmployeelD INNER JOIN Vendor AS v ON poh.VendorlD = v.BusinessEntitylD; GO You need to force SQL Server to access tables in the order they are listed. Which Transact-SQL statement should you use?

3

The Patients table has a primary key of PatientlD. The Prescriptions table has a primary key of Prescription ID. You need to create an indexed view. You write the Transact-SQL statements shown in the exhibit. Line numbers are for reference only. When you attempt to execute the statements, an error occurs. Which line do you need to change to successfully create the indexed view?

A. 5
Incorrect.
B. 6
Correct!
C. 2
Incorrect.
D. 4
Incorrect.
4

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server 2012. You need to count how many bytes are being used within a data field. Which function should you use?

A. COUNT()
Incorrect.
B. LEN()
Correct!
C. CHARD
Incorrect.
D. DATALENGTH()
Incorrect.
5

5. You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server 2012. You need to convert a text string to a date value. If the text cannot be converted, the function should return a NULL value instead of an error. Which function should you use?

A. TRY_PARSE()
Correct!
B. PARSE()
Incorrect.
C. CONVERT()
Incorrect.
D. CAST()
Incorrect.
6

You work with in-house programmers to design and deploy a database to support a new application. The database will be deployed on a SQL Server 2014 instance. Direct access to database tables must be kept to a minimum. You need to design a database object that returns a multiline result set limited by input parameters values. What should you use?

7

You help to develop a database to support sales and inventory management applications. The database is deployed on a default SQL Server 2014 instance. You create the following table: CREATE TABLE OrderDetail (ordernumber nchar(8) PRIMARY KEY NOT NULL, lineitemnumber int NOT NULL. sku nchar(20) NOT NULL, numordered int NOT NULL, price money NOT NULL. itemcount int NOT NULL, specialinstructions nvarchar(200) NULL), extendeditem AS itemcount * price PERSISTED) Programmers must understand how to work with data in the extendeditem column. You need to provide them with the data type used to persist the data. Which data type you should provide?

A. decimal
Incorrect.
B. Int
Incorrect.
C. money
Correct!
D. real
Incorrect.
8

You work with in-house programmers to develop stored procedures for data manipulation. Several of the procedures use the following general syntax: SET TRANSACTION ISOLATION LEVEL < isolation level>: GO BEGIN TRANSACTION: < statemenrk; < staternent2>: < statement3>: You must set a transaction isolation level to ensure that: • Other transactions cannot modify any data read by this transaction. • This transaction cannot read data modified, but not committed, by other transactions. ▪ Other transactions cannot insert rows with key values falling in the range of keys read by this transaction. You need to make sure that these restrictions apply until the transaction is committed or rolled back. Which transaction isolation level should you use?

A. REPEATABLE READ
Incorrect.
B. SERIALIZABLE
Correct!
C. READ COMMITTED
Incorrect.
D. SNAPSHOT
Incorrect.
9

The Sales database is hosted on a default SQL Server 2014 instance. It includes a table named Customers that tracks customer records. Each user is uniquely identified by a customer ID stored in the CustID column. Each customer has an associated state value stored as a two-character code in the State column. You need to create a user-deiced function that accepts the state code as an input parameter and returns a count of customers In that state. Which Transact-SQL script should you use?

10

You have a table that stores information about university campuses. You use the geography data type to store data about each building within a campus. You need to write a query that returns an object that represents the complete outline of an entire campus, without any interior borders delineating buildings. Which function should you use?

11

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server Standard Edition. You are writing a query that finds all the Employees who have sold more than 100,000 in total sales. You use this line of code to begin the task: SELECT EmpID, SUM(Sales) AS Tot_Sales GROUP BY EmpID You need to add a line of code to return qualifying rows. Which line of code should you use?

12

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server 2012. You write a query that returns the ProductName of the products that have been sold. You only want to see the products that match the highest total of items sold. You use these lines of code to begin your query: SELECT ProductName FROM Production. Product WHERE qty Which statement should you use to finish the code?

13

You create a table using the following Transact-SQL statement: CREATE TABLE Pets CustomerlD int PRIMARY KEY NONCLUSTERED, PetName varchar(30), Breed varchar(30) You need to ensure that no two rows have the same combination of CustomerlD and PetName. Data stored on the disk must be sorted by CustomerlD and then by PetName. Which Transact-SQL statement should you execute?

14

You prepare the maintenance on a database named Retail. The database is hosted on a named instance of SQL Server 2012. You are doing extensive maintenance on inventory records. This impacts the following: The dbo.InvHead table with an AFTER UPDATE trigger named dbo.tr_InvTop * The dbo.InvDetail table with an AFTER UPDATE trigger named dbo.tr_Inv * The dbo.vinventory view with an INSERT OF UPDATE trigger named dbo.trv_Inv You need to ensure that the triggers do not fire while you are performing maintenance on the tables. The triggers should be configured to fire as normal when you are finished. You want to keep the administrative effort necessary to accomplish this to a minimum. Which Transact-SQL script should you use to prevent the triggers from firing?

15

You are a SQL Developer for a mid-sized company that uses Microsoft SQL Server 2012 Standard Edition. You are creating a table named HumanResources.Employees that will have an Age field. Based on legal requirements, your company may only hire employees who are at least 21 years of age. You need to enforce the minimum age restriction by requiring an age value of at least 21. Which column defintion should you use when creating the table?

16

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server 2012. You have a stored procedure named HumanResources.uspGetAllEmployees. Users report that the stored procedure is running slower than it used to run You schedule a SQL Server job that will create and store a new execution plan for the stored procedure once a month. You do not want a new execution plan created each time the stored procedure is run. You need to determine the Transact-SQL statement to execute in the scheduled job. Which statement should you use?

17

You create the following table to collect data for an in-house sales promotion: CREATE TABLE Sales.TopDay (LastName nvarchar(20) NOT NULL. FirstName nvarchar(20) NOT NULL. DayTotal money NOT NULL. Date datetime NOT NULL) At end•of-day, you need to write the top five salespersons. by daily sales. to the Sales.TopDay table. Which Transact-SQL script should you use?

18

You company has an instance of SQL Server 2014 with a single user database named SalesData. You execute the following transaction: UDPATE Customers SET Available = 0 WHERE Active = 0 The transaction completes without error. When you check the Customers table, you find the changes that have been made. You need to determine the type of transaction that was used. Which type of transaction do the changes represent?

A. Explicit
Incorrect.
B. Autocommit
Correct!
C. Distributed
Incorrect.
D. Batch-scoped
Incorrect.
19

You develop a Microsoft SQL Server 2012 database that stores data about Rights that depart from an airport. You need to select the top five airlines with the longest departure delays. Which Transact-SQL query should you use?

20

You are a SQL Developer for a mid-sized company that uses Microsoft SQL Server 2012 Standard Edition. You implement Referential Integrity between the HumanResources.Employees table and the HumanResources.Departments table. A relationship is defined between the DeptID fields in both tables. The DeptID field does not allow NULL values. You need to ensure that, when a record is deleted in the parent table of the relationship, the value of the Foreign Key is updated. Which Referential Integrity option should you use for this task?

A. ON DELETE SET DEFAULT
Correct!
B. ON DELETE SET NULL
Incorrect.
C. ON DELETE CASCADE
Incorrect.
D. ON DELETE NO ACTION
Incorrect.
21

You are updating a database hosted on an instance of SQL Server 2014 to improve data integrity. You create the following table: CREATE TABLE dbo.OrderHead (orderid INT IDENTITY (1,1) PRIMARY KEY CLUSTERED NOT NULL. ordernumber nchar(8) NOT NULL. orderdate datetime DEFAULT GETDATE(), customer nvarchar(20) NOT NULL DEFAULT 'Cash') You need to modify the dbo.OrderHead table to allow the ordernumber column to be referenced by another table through a foreign key constraint. You must ensure that the column does not contain any duplicate values. Which Transact-SQL statement should you use?

22

You need create a SQL Server object that performs multiple queries to build a table. You must be able to use the object in the FROM clause of a SELECT statement. What should you create?

A. Function
Correct!
B. Stored procedure
Incorrect.
C. View
Incorrect.
D. Cursor
Incorrect.
23

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server 2012. You write a query that returns the FirstName, LastName, Title, and Ext from the HR.Employees table. You use these lines of code to begin the task: SELECT FirstName, LastName, Title, Ext FROM HR.Employees You need to list only employees who have the word Sales anywhere in their title. Which line of code should you use to select the users?

24

You are the SQL Developer for a small company. You create a view to be used by a reporting application that selects several columns from multiple existing tables. You need to speed up the query performance of your view. How should you modify the view to increase performance?

25

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server Standard Edition. You have two tables named HR.Employees and Sales.Customers. You want to create a mailing list from both tables. You write SELECT statements that retrieve address data from both Employees and Customers. You need to return a list of all addresses from both queries, including duplicates. Which set operator should you use?

A. UNION ALL
Correct!
B. Except
Incorrect.
C. Union
Incorrect.
D. Intersect
Incorrect.
26

You are a SQL Developer for a mid-sized company that is using Microsoft SQL Server Standard Edition. You have a table named HR.Payroll that includes the fields EmpID, HourlyRate, Salary, Commission, and TotSales. You need to calculate what the annual pay is for each employee from this table's contents. Your calculations must use a function and follow these guidelines: * If the employee is paid at an hourly rate, you will multiply the hourly rate by 40 hours and then by 52 weeks. * If the employee does not have an hourly rate, you want the function to return the value from the Salary field. * If both the HourlyRate and Salary fields are NULL, then the function will return the result of the expression Commission*TotSales. What should you use?

A. Case
Incorrect.
B. ISNULL
Incorrect.
C. COALESCE
Correct!
D. NULLIF
Incorrect.
27

You are a member of the SQL developer team. You need to create a new view named vwEmployeePhoneList that is part of the HumanResources schema. The view should select the LastName. FirstName, and PhoneExt columns from the HumanResources.Employees table. Which Transact-SQL statement should you use?

28

The SalesData database includes the following table: CREATE TABLE Sales.lnventory (VendorPN nvarchar(20) PRIMARY KEY NOT NULL, SKU nvarchar(20) NULL, CurCost money NOT NULL. CurPriceA money NOT NULL. CurPriceC money NOT NULL, LastChange datetime NULL. SKUDescription nvarchar(40) NULL, Vendor char(6) NOT NULL) You bulk load new rows into the table from a CSV file you received from a new vendor. The VendorlD for the vendor is ACME00. You included the vendor's recommended SKU for product lines in which the value was provided, but some items did not include an SKU. The SKU is set to NULL for those items. You need to set the SKU equal to the VendorPN for items that do not have an SKU. Which Transact-SQL statement should you use?

29

The Customers table has a clustered index on the CustomerlD column and a nonclustered index on the State column. The following query is executed frequently and takes a long time to execute: SELECT CustomerName, Address, City WHERE State = @state You need to optimize the query. What should you do?

30

You are creating a table that will store information about train schedules. You need to store the time of each departure using the time zone in which the station is located. The data type you choose should require the least amount of storage. Which data type should you use?

A. Small datatime
Incorrect.
B. Time
Incorrect.
C. Datatime
Incorrect.
D. Datetimeoffset
Correct!
PDP Url

Sample Question - Querying Microsoft SQL Server 2012/2014 (70-461)


Self-Paced

Learning Style

Beginner

Difficulty

1 Hour

Course Duration

Certificate

See Sample

Buy Individually
About Individual Course:
  • Individual course plan gives you access to this course
$109.00
$109.00
/ Each

Outline

More Information

More Information
Lab Access No
Learning Style Self-Paced Learning
Difficulty Beginner
Course Duration 1 Hour
Language English

Reviews

Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Contact A Learning Consultant


click here