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;
Correct Answer
B. SELECT firstName +' '+ lastName AS [Name] FROM Student;Explanation
The correct answer is "SELECT firstName + ' ' + lastName AS [Name] FROM Student;" because it concatenates the firstName and lastName columns with a space in between, and aliases the result as "Name". This will display the full name of the student with the column heading "Name".Rate this question:
-
- 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
Correct Answer
C. The sixth highest salaryExplanation
The query first selects the top 6 distinct salaries from the employee table in descending order. Then, it selects the top 1 salary from the previous result set and orders it again in ascending order. Therefore, the output of the query will be the sixth highest salary from the employee table.Rate this question:
-
- 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)
Correct Answer
B. SELECT COUNT(*) FROM Sales WHERE salesTime >= CONVERT(DATE, GETDATE()) AND salesTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))Explanation
The correct answer is the second option. This query uses the CONVERT function to get the current date and time and compares it to the salesTime column. By using the >= operator, it includes all sales made on the current day, and by using the < operator with DATEADD, it ensures that it only includes sales up until the end of the current day. This query is efficient because it uses the non-clustered index on the salesTime column and avoids unnecessary conversions or comparisons.Rate this question:
-
- 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
Correct Answer
C. DECIMALExplanation
To store prices with a fixed precision and a scale of six digits, the most appropriate data type to use is DECIMAL. DECIMAL data type allows for precise storage of numeric values with a specified number of digits and scale. In this case, the scale of six digits ensures that the prices can be stored with up to six decimal places, providing the necessary precision for accurate representation of prices. MONEY and SMALL MONEY data types are not suitable for this scenario as they have predefined scales and may not offer the required precision. INT data type is also not suitable as it does not support decimal places.Rate this question:
-
- 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
Correct Answer
A. ViewsExplanation
Views should be created for each application. Views provide a layer of abstraction between the applications and the underlying table. They allow the applications to access the required data without directly accessing the table. Views can also accommodate data retrieval and data modification, making them suitable for meeting the given requirements. Additionally, future modifications to the table definition will not affect the applications' ability to access data through the views. Therefore, creating views for each application is the correct solution in this scenario.Rate this question:
- 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
Correct Answer
C. ALTER TABLE Audit ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTEDExplanation
The correct answer is "ALTER TABLE Audit ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED". This statement adds a computed column named TotalItems to the Audit table, which calculates the sum of the ItemsInStore and ItemsInWarehouse values for each row. The PERSISTED keyword is used to store the computed column physically on the disk, allowing it to be indexed efficiently. The other options are incorrect because they either use the SUM function incorrectly or do not include the PERSISTED keyword.Rate this question:
-
- 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
Correct Answer
D. CREATE TRIGGER TrgpHoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit RecordsExplanation
The correct answer is to use the query "CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records". This query creates a trigger named TrgPhoneNumberChange on the Customers table that is fired after an update operation. The IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) condition checks if either the HomeNumber or MobileNumber column is updated. If this condition is true, the trigger will create an audit record.Rate this question:
-
- 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.
Correct Answer
A. Convert the view into an indexed view.Explanation
Converting the view into an indexed view would improve the performance of the reports. Indexed views are materialized views that are stored physically on disk, allowing for faster data retrieval and aggregation. By creating an index on the indexed view, the query optimizer can use the index to quickly retrieve the aggregated data, resulting in improved performance for the reports.Rate this question:
-
- 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
Correct Answer
B. READ COMMITTED SNAPSHOTExplanation
READ COMMITTED SNAPSHOT is the correct isolation level to use in this scenario. This isolation level allows for non-blocking reads by creating a snapshot of the data when a read operation begins. This means that writers can continue to modify the data without being blocked by readers. Additionally, READ COMMITTED SNAPSHOT does not require the use of tempdb for versioning, which helps to minimize the use of tempdb space. Therefore, using READ COMMITTED SNAPSHOT will help to minimize contention between readers and writers and optimize the use of tempdb space.Rate this question:
-
- 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
Correct Answer
A. SQL user without loginExplanation
To ensure that you can port the database to different servers within the domain without additional user account configurations, you should create a SQL user without login. This type of user allows you to create a user account in the database without granting them access to the server itself. By doing so, you can easily move the database to different servers within the domain without the need to reconfigure the user account or grant login access on each server.Rate this question:
-
- 11.
Which of the following is exact numeric datatype?
- A.
MONEY
- B.
TINYINT
- C.
BIGINT
- D.
INT
- E.
All of the above
Correct Answer
E. All of the aboveExplanation
All of the options mentioned (MONEY, TINYINT, BIGINT, INT) are examples of exact numeric datatypes. These datatypes are used to store values that represent exact numeric values, such as integers or decimal numbers, with a specific precision and scale. The MONEY datatype is used to store monetary values, TINYINT is used to store small integers, BIGINT is used to store large integers, and INT is used to store regular integers. Therefore, all of the options mentioned are exact numeric datatypes.Rate this question:
-
- 12.
Which of the following returns current date and time?
- A.
DATEDIFF
- B.
DATETIME
- C.
GETDATE()
- D.
None of the above
Correct Answer
C. GETDATE()Explanation
The function GETDATE() returns the current date and time. This function is commonly used in SQL to retrieve the current timestamp. It is a built-in function that does not require any arguments and can be used to get the current date and time in various formats.Rate this question:
-
- 13.
Which of the following function checks whether the expression is a valid date or not ?
- A.
ISDAY()
- B.
ISDATE()
- C.
ISYEAR()
- D.
ISVALID()
Correct Answer
B. ISDATE()Explanation
The function ISDATE() is used to check whether an expression is a valid date or not. It returns TRUE if the expression is a valid date, and FALSE if it is not. This function is commonly used in programming and data analysis to validate dates before performing any operations or calculations involving dates.Rate this question:
-
- 14.
What does DML stand for?
- A.
Different Mode Level
- B.
Data Manipulation Language
- C.
Data Model Language
- D.
Data Mode Lane
Correct Answer
B. Data Manipulation LanguageExplanation
DML stands for Data Manipulation Language. This term refers to a type of computer programming language that is used to retrieve, insert, update, and delete data in a database. DML allows users to manipulate the data stored in a database by performing various operations on it. It is an essential component of database management systems and is commonly used in applications that require data manipulation capabilities.Rate this question:
-
- 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’
Correct Answer
D. SELECT * FROM Persons WHERE FirstName LIKE ‘%a’Explanation
The correct answer is "SELECT * FROM Persons WHERE FirstName LIKE '%a'". This query uses the LIKE operator with a wildcard (%) to select all records from the "Persons" table where the value of the "FirstName" column ends with "a".Rate this question:
-
- 16.
Which SQL statement is used to return only different values?
- A.
SELECT DIFFERENT
- B.
SELECT UNIQUE
- C.
SELECT DISTINCT
- D.
SELECT ALL
Correct Answer
C. SELECT DISTINCTExplanation
The SQL statement "SELECT DISTINCT" is used to return only different values. It eliminates duplicate rows from the result set, ensuring that each row returned is unique. This is helpful when you want to retrieve only distinct values from a column in a table. The other options, "SELECT DIFFERENT," "SELECT UNIQUE," and "SELECT ALL," are not valid SQL statements.Rate this question:
-
- 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
Correct Answer
A. The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraintsExplanation
The correct answer is that the ALTER TABLE statement modifies a table definition by altering, adding, or deleting table columns and/or constraints. This means that it allows changes to be made to the structure of a table, such as adding or removing columns, changing the data type of a column, or adding constraints to enforce data integrity rules. It does not update data in the table, delete the table itself, or add stored procedures or other objects to the database.Rate this question:
-
- 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
Correct Answer
C. SELECT * FROM Persons ORDER BY FirstName DESCExplanation
The correct answer is "SELECT * FROM Persons ORDER BY FirstName DESC". This query uses the ORDER BY clause in SQL to sort the records in the "Persons" table in descending order based on the "FirstName" column. The SELECT statement retrieves all the records from the table.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 18, 2023Quiz Edited by
ProProfs Editorial Team -
Feb 01, 2019Quiz Created by
Fantahun Zikie
Back to top