MTA 98-364 Test 1 – Flashcards

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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;

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.