SQL Server Assessment Offered By Targeted Technology Institute
Test your SQL Server Database Design, Development, Querying and Administration Knowledge
- 1.Which of the following query would concatenate and display the full name of a student, with a column heading "Name"
- A.
SELECT firstName, lastName AS [Name] FROM Student;
- B.
SELECT firstName +' '+ lastName AS [Name] FROM Student;
- C.
SELECT Name FROM Student;
- D.
SELECT firstName, lastName FROM Student;
- 2.Given Employee table with salary information, what will be the output of the following query? SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
- A.
The highest salary
- B.
The sixth lowest salary
- C.
The sixth highest salary
- D.
Query has syntax error
- 3.Your database contains a table named Sales. The table includes a DATETIME column named salesTime that stores the date and time each sales is made. There is a non-clustered index on the salesTime column. The business team wants a report that displays the total number of sales made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?
- A.
SELECT COUNT(*) FROM Sales WHERE salesTime = CONVERT(DATE, GETDATE())
- B.
SELECT COUNT(*) FROM Sales WHERE salesTime >= CONVERT(DATE, GETDATE()) AND salesTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
- C.
SELECT COUNT(*) FROM Sales WHERE salesTime = GETDATE()
- D.
SELECT COUNT(*) FROM Sales WHERE CONVERT(VARCHAR, salesTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
- 4.You are creating a database table that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?
- A.
MONEY
- B.
SMALL MONEY
- C.
DECIMAL
- D.
INT
- 5.You develop a Microsoft SQL Server 2014 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
- Future modifications to the table definition will not affect the applications' ability to access data.
- The new object can accommodate data retrieval and data modification.
- A.
Views
- B.
Index
- C.
Temporary tables
- D.
Triggers
- E.
User Defined Datatypes
- 6.You develop a Microsoft SQL Server 2014 server database that supports an application for TargetedRetail. The application contains a table called Audit that has the following definition: CREATE TABLE Audit ( ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL ) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?
- A.
ALTER TABLE Audit ADD TotalItems AS ItemslnStore + ItemsInWarehouse
- B.
ALTER TABLE Audit ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
- C.
ALTER TABLE Audit ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
- D.
ALTER TABLE Audit ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
- E.
All of the Above
- F.
None of the Above
- 7.You are developing Microsoft SQL Server 2014 Database called TargetedRetail that contains a table named Customer. The Customer table has the following definition: CREATE TABLE Customer ( customerId BIGINT NOT NULL, mobileNumber VARCHAR(25) NOT NULL, homeNumber VARCHAR(25) NULL, name VARCHAR(50) NOT NULL, country VARCHAR(25) NOT NULL, CONSTRAINT pk_Customer_Id PRIMARY KEY (customerId) ); You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?
- A.
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records
- B.
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber from inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records
- C.
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records
- D.
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records
- 8.You develop a Microsoft SQL Server 2014 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each. Performs aggregations on 5 fields. The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?
- A.
Convert the view into an indexed view.
- B.
Convert the view into a table-valued function.
- C.
Convert the view into a Common Table Expression (CTE).
- D.
Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
- 9.You use Microsoft SQL Server 2014 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?
- A.
SERIALIZABLE
- B.
READ COMMITTED SNAPSHOT
- C.
SNAPSHOT
- D.
REPEATABLE READ
- E.
All of the Above
- 10.You use a contained database named TargetedTest within a domain. You need to create a user who can log on to the TargetedTest database and also want to ensure that you can port the database to different servers within the domain without additional user account configurations. Which type of user should you create?
- A.
SQL user without login
- B.
Windows login from domain
- C.
SQL user with login
- D.
Domain user
- 11.Which of the following is exact numeric datatype?
- A.
MONEY
- B.
TINYINT
- C.
BIGINT
- D.
INT
- E.
All of the above
- 12.Which of the following returns current date and time?
- A.
DATEDIFF
- B.
DATETIME
- C.
GETDATE()
- D.
None of the above
- 13.Which of the following function checks whether the expression is a valid date or not ?
- A.
ISDAY()
- B.
ISDATE()
- C.
ISYEAR()
- D.
ISVALID()
- 14.What does DML stand for?
- A.
Different Mode Level
- B.
Data Manipulation Language
- C.
Data Model Language
- D.
Data Mode Lane
- 15.With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” ends with an “a” ?
- A.
SELECT * FROM Persons WHERE FirstName=’a’
- B.
SELECT * FROM Persons WHERE FirstName LIKE ‘a%’
- C.
SELECT * FROM Persons WHERE FirstName=’%a%’
- D.
SELECT * FROM Persons WHERE FirstName LIKE ‘%a’
- 16.Which SQL statement is used to return only different values?
- A.
SELECT DIFFERENT
- B.
SELECT UNIQUE
- C.
SELECT DISTINCT
- D.
SELECT ALL
- 17.What does ALTER TABLE statement do?
- A.
The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints
- B.
Updates data in a user database table
- C.
The SQL ALTER TABLE clause is used to delete a database table
- D.
Is used to add Stored procedures or other objects to a database
- 18.With SQL, how can you return all the records from a table named “Persons” sorted descending by “FirstName” ?
- A.
SELECT * FROM Persons ORDER FirstName DESC
- B.
SELECT * FROM Persons SORT ‘FirstName’ DESC
- C.
SELECT * FROM Persons ORDER BY FirstName DESC
- D.
SELECT * FROM Persons SORT BY ‘FirstName’ DESC
Back to top