SQL EXAM

Question 1

What does the Acronym SSMS stand for?

Answer 1

SQL Server Management Studio

Question 2

SQL Server provides various tools that are used for different purposes. Choose the tools that
apply to SQL Server?

Answer 2

 system installation
 auditing
 configuration
 performance tuning

Question 3

Microsoft SQL Server Authentication Choose between the two authentication types

Answer 3

 Windows Authentication

 SQL Authentication

Question 4

SQL Server Management Studio comprises several different components that are used for the
authoring, administration, and management of the overall system. The following are the main
components used for these tasks:

Answer 4

 Registered Servers
 Object Explorer
 Query Editor
 Solution Explorer

Question 5

When you open SQL Server Management Studio, it displays the Connect to Server dialog box
which allows you to specify the necessary parameters to connect to a server.

T/F

Answer 5

True

Question 6

Registered Servers is represented as a pane that allows you to maintain connections of
servers that are not yet configured.

T/F

Answer 6

False

Question 7

The Object Explorer pane contains a tree view of the system database objects in a server.

T/F

Answer 7

False

Question 8

You can dock or hide each of the panes of SQL Server Management Studio. By right-clicking
the title bar at the top of the corresponding pane.

T/F

Answer 8

True

Question 9

The difference between the Hide and Auto Hide options is that the Hide option removes the
pane from view in SQL Server Management Studio, while Auto Hide collapses the pane to the
side panel.

T/F

Answer 9

True

Question 10

The Object Explorer pane appears on the ________, while the Object Explorer Details
tab appears on the right side of SQL Server Management Studio. (The Object Explorer Details
tab displays information about the currently selected node of Object Explorer.)

Answer 10

Left

Question 11

You can create a new database by using Object Explorer or the Transact-SQL language.

T/F

Answer 11

True

Question 12

In SQL Server Management Studio right-click your database and select Tasks > Generate Scripts

T/F

Answer 12

True

Question 13

In SQL Server Management Studio right-click your database and select Tasks > Generate Scripts

T/F

Answer 13

True

Question 14

What is Query Editor used for?

Answer 14

Create and run scripts containing Transact-SQL statements

Question 15

The master database contains all of the system level information for SQL Server – all of the logins,
linked servers, endpoints, and one other function you are required to choose below

Answer 15

system-wide configuration settings

Question 16

The model database is used as the ___________ for all databases created on an instance of SQL
Server.

Answer 16

Template

Question 17

The msdb database is used by SQL___________ for scheduling alerts and jobs and by other features
such as SQL Server Management Studio, Service Broker and Database Mail.

Answer 17

Server Agent

Question 18

TempDB is a system database in Microsoft SQL Server used as a store of internal objects, , , and
what else. Choose the answers below

Answer 18

 row versions
 temporary tables
 work tables
 indexes

Question 19

When you create a primary key, SQL Server automatically creates an index based on
the ___________columns

Answer 19

key

Question 20

The primary key consists of one or more columns whose data contained within is used to uniquely
identify each row in the__________________ .

Answer 20

Table

Question 21

In order to be a primary key, several conditions must hold.

T/F

Answer 21

True

Question 22

A foreign key is a set of one or __________________ columns in a table that refers to the
primary key in another table.

Answer 22

More

Question 23

This command CREATE TABLE Orders will create what in the New Query dialog box

Answer 23

Create orders table

Question 24

In the command below what does this mean?
CREATE TABLE Orders
(
 OrderId int NOT NULL PRIMARY KEY,
 OrderNo int NOT NULL,

Answer 24

Means in the OrderID, the Primary key cannot be empty and in the OrderNo cannot be at 0

Question 25

In the command below what does the comma (,) mean at the end of the line OrderNo INT Not Null,

Answer 25

End of the line in a script

Question 26

An SQL server profiler is a tool for _____________________________ in MS SQL Server.

Answer 26

tracing, recreating, and troubleshooting problems

Question 27

In SQL Database scripting what does VARCHAR(255) mean?

Answer 27

Can have upto 255 characters

Question 28

TempDB is a system database in Microsoft SQL Server used as a store of internal objects, , , and what else. Choose the best answers below

Answer 28

 row versions  temporary tables  work tables  indexes

Question 29

What does Integer mean when creating a script to build a database

Answer 29

Numbers

Question 30

Which command is correct to create a database in SQL server express?

Answer 30

Create Database SQL

Question 31

When you import and Excel Spreadsheet which option should you take below?

Answer 31

Import flat file

Question 32

What happens when you import an excel file the isn’t a .csv file?

Answer 32

It uploads unreadable characters

Question 33

Is it possible to grant and deny access to s database at the SQL level? T/F

Answer 33

True

Question 34

Microsoft SQL Server Experts recommend shrinking the database monthly and this will improve performance. T/F

Answer 34

True

Question 35

Choose the best practice recommendations when backing up SQL databases

Answer 35

 Backups should be run automatically daily |  Backups are a very critical activity in the administration of DBMS

Question 36

When adding users in SSMS which options are available in the configurtation of users?

Answer 36

 Enforce Password Policy |  Enforce Password Expiration

Question 37

Which database roles in the choices below?

Answer 37

 db_owner  db_datawriter  db_datawriter

Question 38

Choose the SQL Database reports SSMS provides below

Answer 38

 Disks  Disk Usage by Table  All Transactions

Question 39

Why are vulnerability assessment reports important?

Answer 39

to protect and secure the database

Question 40

You have a critical databse that you need to know who has access at all times. In addition, get reports on who is logging into the database. Which option is the best to make sure you are aware of any users logging into the database.

Answer 40

Both failed and successful logins

Question 41

When restoring a database, which extension is required to make the backup function?

Answer 41

.bak

Question 42

What does autogrow means in database technology?

Answer 42

Database will automatically grow provided there is enough disk space

Question 43

When dealing with SQL Server agent which options you can choose from? Choose the answers below?

Answer 43

 Stop  Start  Restart

Question 44

What does this ; symbol mean when you’re making a script in SQL

Answer 44

End of the script

Question 45

What is Objects explorer used for?

Answer 45

Object Explorer is a tree view of all the database objects in a server.

Question 46

Registered servers are ones of the SQL Server Management Studio's hidden gems

Answer 46

Using registered server, DBA gets the ability to execute SQL Query on multiple servers.

Question 47

The Solution Explorer pane in Microsoft SQL Server Management Studio provides containers called projects for managing items such as

Answer 47

 database scripts  data connections  queries  files

Question 48

Database engine tuning advisor is inbuilt tool which comes with Microsoft SQL server management studio which helps in analyzing required indexes, statistics, partitioning, strategy and

Answer 48

physical design structure for performance improvement

Question 49

Checking SQL Server with Policy-Based Management. Policy-Based Management, a feature of SQL Server, is a flexible tool that can help DBAs manage one or more SQL Server instances.

Answer 49

It's used for monitoring and enforcing a standard set of policies for SQL Server throughout an organization.

Question 50

The Solution Explorer pane in Microsoft SQL Server Management Studio provides containers called projects for managing items such as

Answer 50

 database scripts  data connections  queries  files

Question 51

Which of the following are the management tasks that you can perform by using SQL Server Management studio:

Answer 51

>Create databases without using Transact-SQL >Modify databases without using Transect-SQL >Manage tables without using Transect-SQL >Generate and execute SQL statements