MCTS Database Management & SQL Server 1 — Questions and Answers

Question 1: What is the purpose of SQL Server Management Studio (SSMS)?

Writing HTML code
Designing graphics
Managing SQL Server and executing queries (Correct answer)
Creating Excel spreadsheets

Correct answer: Managing SQL Server and executing queries

SQL Server Management Studio (SSMS) is the primary graphical interface tool for administering and developing SQL Server databases. It allows users to connect to, configure, manage, and develop all components of SQL Server, including writing and executing T-SQL queries. Therefore, its core purpose is managing the database server and interacting with its data.

Question 2: Which language is used to interact with SQL Server databases?

Java
Python
T-SQL (Correct answer)
C#

Correct answer: T-SQL

T-SQL (Transact-SQL) is Microsoft's proprietary extension to SQL (Structured Query Language), specifically designed for SQL Server. It is the standard language used to interact with and manage SQL Server databases, enabling users to perform data manipulation, definition, and control operations. While other languages can connect to SQL Server, T-SQL is the native language for direct database interaction.

Question 3: Which SQL command is used to remove a table from the database?

DELETE
TRUNCATE
DROP (Correct answer)
REMOVE

Correct answer: DROP

The `DROP` command is a Data Definition Language (DDL) statement used to completely remove an object from the database. When applied to a table, `DROP TABLE` permanently deletes the table's structure, all its data, indexes, and constraints. This differs from `DELETE` (which removes rows) or `TRUNCATE` (which removes all rows but keeps the table structure).

Question 4: What is normalization in SQL databases?

Adding redundant data
Backing up data
Organizing data to minimize duplication (Correct answer)
Encrypting sensitive information

Correct answer: Organizing data to minimize duplication

Normalization is a database design technique aimed at organizing tables in a relational database to minimize data redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables and defining relationships between them. This process helps to avoid data anomalies and ensures that data is stored efficiently and consistently.

Question 5: Which system database in SQL Server stores login accounts and system configuration?

tempdb
msdb
model
master (Correct answer)

Correct answer: master

The `master` database is a critical system database in SQL Server that stores all system-wide information. This includes metadata about all other databases on the SQL Server instance, server configuration settings, login accounts, and endpoints. It is essential for the operation of the SQL Server instance, as the server cannot start if the `master` database is unavailable.

Question 6: What is the function of the PRIMARY KEY constraint?

Creates a relationship between tables
Prevents duplicate and NULL values in a column (Correct answer)
Allows foreign keys
Inserts data into the database

Correct answer: Prevents duplicate and NULL values in a column

A `PRIMARY KEY` constraint uniquely identifies each record in a table. It enforces two crucial rules: entity integrity, meaning no two rows can have the same primary key value (prevents duplicates), and non-nullability, meaning the primary key column cannot contain NULL values. This ensures that each row can be uniquely identified and referenced.

Question 7: Which command is used to retrieve data from a SQL Server table?

FETCH
PULL
SELECT (Correct answer)
READ

Correct answer: SELECT

The `SELECT` statement is the most fundamental Data Manipulation Language (DML) command in SQL. Its primary function is to retrieve data from one or more tables in a database. Users can specify which columns and rows they want to view using various clauses like `FROM`, `WHERE`, and `ORDER BY`.

Question 8: Which feature in SQL Server provides data recovery after a system failure?

Indexes
Triggers
Transaction logs (Correct answer)
Constraints

Correct answer: Transaction logs

Transaction logs in SQL Server record all modifications made to the database, including inserts, updates, and deletes. This detailed record is crucial for data recovery, allowing the database to be restored to a consistent state after a system failure, power outage, or data corruption. It ensures data integrity and provides point-in-time recovery capabilities.

Question 9: What is the purpose of the SQL Server Agent?

Hosts websites
Manages system logins
Automates administrative jobs (Correct answer)
Creates new databases

Correct answer: Automates administrative jobs

SQL Server Agent is a component of SQL Server that is used to automate administrative tasks, known as jobs. These jobs can include routine maintenance operations such as database backups, replication tasks, data transformation services, and custom scripts. By automating these tasks, the SQL Server Agent helps ensure the smooth and efficient operation of the database server.

MCTS Database Management & SQL Server 1

What is the purpose of SQL Server Management Studio (SSMS)?