MTA 98-364 Test 1
17. Primary data files used on a Microsoft SQL server has an _____ extension.
Answer: .mdf Section Reference: Understanding Databases Concepts Explanation: Microsoft SQL Server uses three types of files to store the database. Primary data files, with an .mdf extension, are the first files created in a database and can contain user-defined objects, such as tables and views, as well as system tables that SQL Server requires for keeping track of the database.
18. What statement is used to add or remove columns from a table?
Answer: ALTER Explanation: The ALTER statement changes an existing object and enables you to add or remove columns from a table. You can also use ALTER to change the definition of a view, a stored procedure, a trigger, or a function.
19. What is the difference between the TRUNCATE and DELETE commands?
Answer: The DELETE statement is used to delete rows from a table, but it does not free the space containing the table. Use the SQL TRUNCATE command to delete all rows from the table and free the space containing the table. Explanation: To delete all rows from the user table, use the following command: DELETE FROM user; To delete all rows from the user table while freeing the space, use the following command: TRUNCATE FROM user;
20. Which statement would you use to change the database context?
Answer: USE Explanation: One T-SQL command worth mentioning is the USE command, which changes the database context to the specified database or database snapshot. In other words, when performing commands on a particular database, you most likely have to utilize the USE command just to select the database first.
6. A ____________ is an organized collection of data, typically stored in electronic format.
Answer: database (db) Explanation: A database (db) is an organized collection of data, typically stored in electronic format. It allows you to input, organize, and retrieve data quickly. Traditional databases are organized by fields, records, and files.
9. Which of the following is a language element that allows you to use core statements such as INSERT, UPDATE, and DELETE to manipulate data in any SQL Server table? a) DML b) DDE c) DDL d) DAT
a) DML Section Reference: Understanding Data Manipulation Language (DML) Explanation: Data Manipulation Language (DML) allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server table.
6. What allows a SQL database to be accessed by multiple users at the same time? a) database server b) workstation c) collector d) computer located in the center of the building
a) database server Section Reference: Understanding Relational Database Concepts Explanation: Databases are often found on database servers so that they can be accessed by multiple users and so they can provide a high-level of performance. A popular database server runs Microsoft SQL Server. Database servers are entirely optimized to serve only the purposes of the database itself, usually using advanced hardware to allow for the high-processing necessities of the database server.
4. What is a simple database that uses two-dimensional tables consisting of rows and columns? a) flat type database b) hierarchical database c) relational database d) tabled database
a) flat type database Section Reference: Understanding Flat Type Databases Explanation: A flat type database is very simplistic in design. They are most commonly used in plain text formats, as their purpose is to hold one record per line, making the access performance and queries very quick. An example of this type of database would be what you would find in a .txt or .ini file. Flat type databases are considered flat because they are two-dimensional tables consisting of rows and columns.
8. What is the primary console used to configure databases for Microsoft SQL Server? a) DBS b) SSMS c) SQL Configuration Tool d) SQL ODBC Emulator
b) SSMS Section Reference: Using the SQL Server Management Studio (SSMS) Interface Explanation: When you install Microsoft SQL Server, you also install the SQL Server Management Studio (SSMS), which is the primary tool to manage the server and its databases via a graphical interface.
15. What allows you to execute SQL commands in a command-line application? a) DOS commands b) sqlcmd scripts c) XQuery d) Transact-SQL
b) sqlcmd scripts Section Reference: Using the SQL Server Management Studio (SSMS) Interface Explanation: The sqlcmd command-line application comes with Microsoft SQL Server and exposes SQL Server's management features. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as a .sql file and are used either for management of databases or to create the database schema during the deployment of a database.
10. Which of the following is a subset of T-SQL that deals with creating database objects such as tables and stored procedures? a) DML b) DDE c) DDL d) DAT
c) DDL Section Reference: Understanding Data Definition Language (DDL) Explanation: Data definition language (DDL) statements form part of the T-SQL portion of SQL Server and can be used to create database objects such as tables and views. The user interface employed to create these underlying DDL statements will be through the SQL Server Management Studio user interface.
11. What kind of statements are USE, CREATE, ALTER, and DROP? a) DML b) DDE c) DDL d) DAT
c) DDL Section Reference: Using DDL Statements Explanation: A DDL script statement task can always be completed through the SQL Server Management Studio user interface, but not all the options you may want to use with the DDL script can be accomplished through the user interface. You must, therefore, be familiar with the DDL statements USE, CREATE, ALTER, and DROP for objects to create and manage tables, user-defined data types, views, triggers, functions, and stored procedures.
13. Which statement can you use to remove rows from a table? a) SELECT b) UPDATE c) DELETE d) STOP
c) DELETE Section Reference: Understanding Data Manipulation Language (DML) Explanation: Data Manipulation Language (DML) is the language element that allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server tables. The DELETE command removes rows from a table or view.
12. What statement would you use to delete a table from a SQL database? a) DELETE b) DEL c) DROP d) REMOVE
c) DROP Section Reference: DROP Explanation: The DROP statement removes an object from the database, but if other objects are dependent on the object you are attempting to remove, this statement will fail and an error will be raised.
2. What kind of data structure improves the speed of data retrieval? a) sort b) filter c) index d) transaction log
c) index Section Reference: Understanding Database Concepts Explanation: In a database containing thousands of records with many fields per record, a fast computer would take some time to search through a table to retrieve data. A database index is a data structure that improves the speed of data retrieval operations on a table. The disadvantage of using an index is that indexes need to be created and updated, which takes processing and takes up disk space.
5. Which type of database has multiple tables with parent/child relationships and a child that can have more than one parent? a) flat-file database b) hierarchical database c) relational database d) tabled database
c) relational database Section Reference: Understanding Relational Databases Explanation: A relational database is similar to a hierarchical database in that data is stored in tables, and any new information is automatically added into the table without the need to reorganize the table itself. Different from hierarchical database, a table in a relational database can have multiple parents.
1. What kind of filename extension do Microsoft SQL Server transaction log files have? a) .mdf b) .ldf c) .ndf d) .log
d) .log Section Reference: Understanding Database Concepts Explanation: Microsoft SQL Server transaction log files use an .ldf extension and don't contain any objects such as tables or views. Transaction log files can be used to perform a point-in-time restore or rollback.
3. What is a collection of programs that enables you to enter, organize, and select data from a database? a) flat program b) spreadsheet group c) DAT application d) DBMS
d) DBMS Section Reference: Understanding Database Concepts Explanation: Most users do not access databases directly. Instead, they use a database management system (DBMS) to access the databases indirectly. DBMS is a collection of programs that enables you to enter, organize, and select data in a database.
14. What is the primary means of programing and managing SQL Server? a) DOS commands b) sqlcmd scripts c) XQuery d) T-SQL
d) T-SQL Section Reference: Using the SQL Server Management Studio (SSMS) Interface Explanation: Transact-SQL (T-SQL) is the primary means of programming and managing SQL Server. It exposes keywords so that you can create and manage databases and its components and monitor and manage the server itself. When you use SSMS to perform an action or task, you are executing T-SQL commands.
7. What are the limitations or rules placed on a file or column to ensure that data is considered valid for a database? a) checker b) validator c) scanner d) constraint
d) constraint Section Reference: Introducing Language Elements Explanation: Constraints are limitations or rules placed on a field or column to ensure that invalid data is not entered. For example, if you want someone to input a person's age, the data typed in can be only a positive number. A person cannot have a negative age.
