MCTS Database Management & SQL Server

Question 1

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

Accepted Answer

Managing SQL Server and executing queries

Answer

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?

Accepted Answer

T-SQL

Answer

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?

Accepted Answer

DROP

Answer

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?

Accepted Answer

Organizing data to minimize duplication

Answer

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?

Accepted Answer

master

Answer

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.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS Database Management & SQL Server