SQL Server Admin Final Review

Which is not a database property?

Rows in a table must have a particular order.

SQL Servers flavor of SQL is:

Transact SQL

What is DML?

Data Manipulation Language

SQL is:

Structured Query Language

What feature of virtualization software allows you to revert to a previous point in time in the event you make an unrecoverable error?

snapshots

What type of Windows Server should you NOT install SQL Server for security reasons?

domain controller

Mixed Mode allows for:

Windows and SQL users to log in

Which tool in Windows Server provides a one-stop management interface for querying server status and changing roles and features?

Server Manager

Which of the following ultimately determines which SQL Server edition is most suited for a particular application?

business requirements

SQL Server 2012 is only available in 64-bit versions.

False

What feature of SQL Server provides high end datacenter capabilities with fast performance, unlimited virtualization, and end to end business intelligence for a production environment?

Enterprise

Which of the following is NOT a recommended minimum requirement for running SQL Server 2019 Standard?

500 MHz processor

SQL Server 2019 can be installed on a 32 bit system.

False

Which of the following is an edition of SQL Server ?

Enterprise

Which of the following is the core component within a database that control data storage and access requests from client applications?

database engine

You can install SQL Server 2019 on

All the above (Windows, Linux, Mac)

A common reason that SQL Server installation might fail is because the Windows OS is not up to date with service packs or security updates.

True

What are the SQL Server Authentication modes?

Windows, Mixed mode

To which section of Server Manager would you go to view the event log or run device manager?

Diagnostics

Log files

should not be stored on the same disk as the data files

The primary purpose of a database is to organize and store data in a secure manner.

True

Which of the following is true about the Autogrowth setting?

you should configure it so that file growth occurs infrequently

The master system database stores a database template that is used as a blueprint when creating a new user database.

False

Which of the following is a way of organizing data, with defined rules for storing, manipulating, and retrieving data?

logical structure

Which system database is recreated each time SQL Server starts?

tempdb

Which of the following is true about the model database template?

new databases will inherit settings from it

The two main physical file types in an SQL Server database are data files and log files.

True

Data is stored in a _________.

Page

Since it is easy to make configuration changes after a database is created, it's best to get the database created and make changes as necessary afterward.

False

Which system database stores information related to jobs, schedules, and database backups?

msdb

Which of the following is true about log files?

they aid in database roll back

Which of the following is a file extension used in the physical structure of a SQL database?

.ldf

Which system database stores information on the structure of other databases and their components?

master

Which of the following is NOT a system database on a SQL Server system?

directory

Which tool allows you to create SQL queries to be executed against a database?

Query Editor

Data Discovery and Classification was introduced in:

SSMS 17.5

Which of the following is true about the physical file structure of a SQL Server database?

it must have at least one log and one data file

Always encrypted encrypts data at rest only.

False

Which of the following is NOT true about roles?

the scope of a role is confined to each table in a database

When is the Service Master Key created?

When the database engine is installed

Which term is best described as a way to group together users for the purposes of managing database permissions?

role

Which of the following is NOT a type of security principal that may request access to a SQL server instance?

Windows contact

Which of the following is an authentication mode supported by SQL Server ?

Mixed authentication mode

Which action does a user perform that requires the user to provide credentials?

system logon

Asymmetric keys__________?

Use both a public key and a private key to encrypt and decrypt data

Which default account is enabled when SQL Server authentication is enabled using mixed mode?

sa

Which predefined server-level role allows a user to create or alter any database?

dbcreator

Security principals can be either users or groups.

True

Encryption does not prevent data loss, but it can lessen the impact of data loss.

True

Which environments must use SQL Server authentication because Windows authentication cannot be used?

Non windows users

What should you reduce in order to limit the number of security vulnerabilities in a system?

surface area

Which of the following is Not involved in Encryption?

Instance Master Key

Which of the following is true about asymmetric encryption algorithms?

the performance is slower than with symmetric algorithms

Which type of encryption uses public and private keys?

asymmetric

Which of the following is another term for the layered security model?

defense-in-depth

Which of the following is true about database access control?

to access a database the SQL Server login must be mapped to a database user

All server-level principals have access to the SQL databases.

False

Which of the following is true about database permissions?

permissions can be granted on a schema

Which of the following is NOT an action against a database object that requires a permission?

logging in to SQL Server

Two methods of data encryption are Transparent Data Encryption and Always Encrypted.

True

What SQL function encrypts data using a key that has been registered with the database?

EncryptByKey

Which of the following is a reason that Windows authentication is considered more secure than combining Windows and SQL Server authentication.

multiple sets of credentials are not required

The AwlaysOn capabilities of SQL Server effectively replace the need to perform traditional backups.

False

How are full and differential database backups used?

differential backups are usually performed more often than full backups

Which category of risk has the highest probability of occurrence?

user error

Which of the following is NOT a commonly used type of backup plan?

Single transaction log backup plus simple recovery model

Transaction logs are required in the event that a database needs to be restored back to a specific point in time.

True

Which of the following is true about the recovery point objective?

the recovery point objective is expressed as a time frame

Which of the following is true about backup sets?

a backup device references a physical location where backup sets are stored

Which backup scheme reduces the amount of downtime to perform a restore but creates additional IT management and physical resource overhead?

Full database backup plus differential database backup plus multiple transaction log backups

The four Recover Models are: Full, Bulk-logged, BCP logged, and Simple.

False

Which recovery model causes all operations to be written to the transaction log?

full recovery model

Which of the following is true about log shipping?

Allows you to have a warm standby Server

Which two ways can you back up and restore a database?

Using Transact SQL, SSMS

Which of the following is true about the factors that should be considered when evaluating database business requirements and the associated risk assessments?

databases that have a high change rate require more frequent backups

Which of the following is a reason that transaction logs must be backed up frequently?

when the log is backed up, it is truncated

What SQL command do you use when restoring transaction logs if you want to do a point-in-time restore?

STOPAT

If you experience database corruption, what should you do before performing a restore operation?

backup the transaction log tail

Which of the following is NOT among the three primary factors that should be considered for each risk scenario identified?

personnel involved in the scenario

A Backup device is a logical storage device that references a physical location.

True

Data loss caused by environmental events has the greatest possibility of occurrence.

False

Which of the following SQL commands should you perform in a database recovery operation when you have recovered the last transaction log?

RESTORE DATABASE WITH RECOVERY;

Which command changes the database context?

USE [Database Name];

Which command selects all columns in a table?

SELECT *

Which syntax should you use to sort the result set in reverse alphabetic order.

ORDER BY [Column Name] DESC

Which type of merge statement only combines rows that exist in two result sets?

INTERSECT

The database schema is defined by the DDL components in the SQL programming language.

True

Which clause do you use with a join operation to specify which columns from each table should be used as the matching key?

ON

Transact-SQL is the variant of SQL developed by Microsoft and Sybase.

True

Which of the following represents correct syntax for a SELECT statement?

SELECT [Column Name] FROM [Table Name]

What keyword can you include in a SELECT statement to create a column alias for the column returned in the result set?

AS

Which type of join returns all columns from both tables and only returns rows where the join column values are equal?

inner

Which of the following is true about the SQL language?

the semicolon is a statement terminator

If Col1 contains the string "Hello" and Col2 is Null, what is the result of SELECT Col1 + Col2?

Null

If you want to compare a value in a column against a list of values, which syntax would you use?

WHERE [Column Name] IN ([Value1], [Value2], [Value3])

What are the keywords CHECK, UNIQUE, and NOT NULL referred to as?

constraints

What type of functions are used to summarize data in a grouped column or for all records in a table?

Aggregate functions

SQL is an imperative programming language.

False

Which of the following best describes the IntelliSense feature in Query Editor?

it is a text AutoComplete feature

If you want to remove duplicates from a result set, which command should you use?

SELECT DISTINCT