Certification Practice Test Sample Questions For Querying Microsoft SQL Server 2012/2014 (70-461)

QuickStart is now offering sample 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 test to better aid in certification. 100% of the questions are real test questions; from a recent version of the Querying Microsoft SQL Server 2012/2014 (70-461) exam.


Arrow
 

Querying Microsoft SQL Server 2012 Sample Exam Questions

1

You work for mid-sized company as a SQL Developer. The company is using Microsoft SQL Server 2012. Can you identify from the optiond below which function would be used to count the number of bytes being used within a data field?

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

You work for a mid-sized company as a SQL Developer. The company used Microsoft SQL Server 2012. You are required to change a text string into a date value. There should be a NULL value instead of an error if the text cannot be changed to data value. Which function should you use?

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

You work for an organization that has a team of in-house programmers. You work with the team to design and deploy a database on a SQL Server 2014 instance. The database is expected to support a new application. You must keep database table direct access to a minimum. Which of the following would you use if you need to design a database object that returns a multiline result set limited by input parameters values?

4

You are taskd by your organization to develop a Microsoft SQL Server 2012 database. There is a table in the database named Products. The Below you can see the Transact-SQL statement that was used to create the table: 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 There is a column in the table called ProductTested. The purpose of this column is to indicate if a product was tested for compatibility or not. You are required to make sure users are allowed to modify ProductDescription only for products that are not tested. What would be your first step of action?

5

You are working for a company as a Microsoft SQL Server 2012 database administrator. Below is shown a query that is tuned by you using a SQL query optimizer: 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 are required to force SQL Server to access tables in the same order as they are listed. Can you identify the correct Transact-SQL statement to be used here?

6

You work for a company as a database administrator. In order to support applications pertaining to sales and inverotry management, you assist in developing a database that is deployed on a default SQL Server 2014 instance. Following is the table you create: 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) It is required by the programmers to know and understand the extendeditem column and how to work with data in it. You have to give them the data type used to persist the data. Identiify the data type you would provide.

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

The company you work for as a Sales database hosted on a default SQL Server 2014 instance. There is a table in the database named Customers which helps in tracking customer records. There is a customer ID stored in the CustID colum. The ID helps identify the customers individually. Then there is a State column that is each customer’s associated state value stored in the column as a two-character code. 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. From the options below, choose the appropriate Transact-SQL script you would use.

8

There is a database in which you have a table with stored information about university campuses. You have stored information about every building in all the campuses using the geography data type. You have to come up with a query that returns an object representing the entire campus outline, without any interior borders delineating buildings. Identify the appropriate function you would use?

9

You work for a mid sized company as a SQL Developer. The company is using Microsoft SQL Server Standard Edition. You want to identify the sales employees who have sold more than 100,000 units in total sales for which you write a query. You use this line of code to begin the task: SELECT EmpID, SUM(Sales) AS Tot_Sales GROUP BY EmpID To return qualifying rows, which line of code would you use?

10

You work for a mid-sized company as an SQL Developer. The company is using Microsoft SQL Server 2012. You want to return the ProductName of the sold products for which you write a query. You only want to see the products that match the highest total of items sold. You start your query using the following line of code: SELECT ProductName FROM Production. Product WHERE qty Identify with which statement would you finish the code?

11

Following is a Transact-SQL statement used to create a table: CREATE TABLE Pets CustomerlD int PRIMARY KEY NONCLUSTERED, PetName varchar(30), Breed varchar(30) You want to avoid CustomerID and PetName to have same combination in any two rows. Data stored on the disk must be sorted by CustomerlD and then by PetName. Identify the Transact-SQL statement you would execute.

12

You work for a mid-sized company as an SQL Developer. The company is using Microsoft SQL Server 2012 Standard Edition. A table named HumanResources.Employees is created by you and the table has an Age field. Your company has a policy to hire professionals above the age of 21 years, and legal requirements might be the reason. You need to enforce the minimum age restriction by requiring an age value of at least 21. Identify the column defintion you would use when creating the table?

13

You work for a mid-sized company as an SQL Developer. The company is using Microsoft SQL Server 2012. You have a stored procedure named HumanResources.uspGetAllEmployees. You come to know from the users that the speed of stored procedure is getting slower than its average. You plan to create and store a new execution plan for the stored procedure via SQL Server once a month. For that you set a SQL Server job. You want to avoid creation of a new execution plan every time the stored procedure is run. Which Transact-SQL statement would you use to execute in the scheduled job?

14

There is an in-house sales promotion for which you create the following table: CREATE TABLE Sales.TopDay (LastName nvarchar(20) NOT NULL. FirstName nvarchar(20) NOT NULL. DayTotal money NOT NULL. Date datetime NOT NULL) At the end of day, you want to mention top five sales persons to the Sales.TopDay table. Can you identify the Transact-SQL script you would use?

15

You work for a company has a SQL Server 2014 instance. The instance has a single user database called SalesData. The following transaction is executed: UDPATE Customers SET Available = 0 WHERE Active = 0 There is no error reported in the transaction completion. Upon checking the Customers table, the changes made are discovered by you. Can you identify the type of transaction that these changes represent?

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

There is a SQL Server 2012 database developed by you for the purpose of storing Flights data that are departing from an airport. You need to identify the top five airlines with the longest departure delays. Identify the correct Transact-SQL query would you use?

17

You are working for a mid-sized company as a SQL Developer. The company uses Microsoft SQL Server 2012 Standard Edition. There are two tables named HumanResources.Employees and HumanResources.Departments between which you implement a Referential Integrity. A relationship is defined between the DeptID fields in both tables. No NULL values are allowed in the DeptID field. The want value of the Foreign Key to be updated as soon as a record is deleted in the parent table of the relationship. Identify the Referential Integrity option you must use for the task.

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

There is a database hosted on a SQL Server 2014 instance. You are updating the database so data integrity can be imrpved. Following is the table you create:: 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 have to make sure duplicate values are not witnessed in the column. Identify the Transact-SQL statement you must use?

19

You want to build a table via multiple queries performed by a SQL Server object. You create that SQL server object for that purpose. You must be able to use the object in the FROM clause of a SELECT statement. Can you identify which of the following would you create?

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

You work for a mid-sized company as a SQL Developer. The company uses Microsoft SQL Server 2012. There is an HR.Employees table and you write a query that returns the the following values: FirstName, LastName, Title, and Ext. You use these lines of code to begin the task: SELECT FirstName, LastName, Title, Ext FROM HR.Employees Which line of code would you use if you are to list only employees with their job title having the word Sales?

21

There is a table called Customers table with two columns named CustomerID and State column. The table has a clustered index on the CustomerlD column and a nonclustered index on the State column. Take a look at the following query. It is frequently executed but mostly takes a long time to execute: SELECT CustomerName, Address, City WHERE State = @state What step would you take to optimize the query?

22

You want to store information about train schedules, and you create a table for that. Using the same time zone as that of the station the train is at, the time for each departure is stored. Which data type would you choose keeping in mind it should require the least amount of storage?

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

You are working as a SQL Developer for a small company. There is a reporting application that selects various columns from different existing tables. You create a view that the app will use. You are required to pace up the query performance of your view. What will you do to modify the view and increase performance?

24

You work for mid-sized company as a SQL Developer. The company uses Microsoft SQL Server Standard Edition. There are two tables you are using named HR.Employees and Sales.Customers. You decide to create a mailing list from both tables. To retrieve address data from both Employees and Customers, you write SELECT statements. You need to return a list of all addresses from both queries, including duplicates. Identify the set operator you would use?

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

You work for a company as a member of its SQL developer team. You are tasked to create a new view named vwEmployeePhoneList. The view is a part of the HumanResources schema. Following columns are expected to be selected by the view from the HumanResources.Employees table: -LastName -FirstName -PhoneExt Identify which Transact-SQL statement would be appropriate to use?

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

$99.00

More Information:

  • Learning Style: On Demand
  • Learning Style: Practice Exam
  • Difficulty: Beginner
  • Course Duration: 1 Hour
  • Course Info: Download PDF
  • Certificate: See Sample

Need Training for 5 or More People?

Customized to your team's need:

  • Annual Subscriptions
  • Private Training
  • Flexible Pricing
  • Enterprise LMS
  • Dedicated Customer Success Manager

Outline

Reviews

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

Hit button to validate captcha