MTA 98-364 Practice Exam Questions (1/2/3)

How many levels of normal forms are there?

5

Row

A group of database items listed across - left to right (horizontally)

bulkadmin

Can perform bulk insert operations

Full Recovery Model

Can recover to an arbitrary point in time because in addition to backing up the database, this model also backs up log files. This way, no work is lost due to a lost or damaged data file.

Second Normal Form (2NF)

Creates relationships between related tables using foreign keys.

What are some fixed server roles?

Diskadmin Setupadmin Securityadmin

Fifth normal form (5nf)

Every non-trivial join dependency in the table is implied by the super keys of the table.

Char(size)

Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.

DATETIME range

January 1, 1753 to December 31, 9999

SMALLDATETIME range

January 1, 1900 to June 6, 2079

A "unique constraint" is the same as a primary key. y/n

No As with a primary key, a "unique constraint" defines that values within a column must be unique. However, unlike a primary key, you can have more than one unique constraint on a table.

When using the multiple-record syntax, the INSERT INTO statement precedes a __________ statement and adds a single or multiple records to a table.

Select

First Normal Form (1NF)

Sets rules for eliminating duplicate columns, creates separate tables if needed, and identifies each row with a primary key.

DateTime2(0-7)

Stores the date and time time can be recorded up the 7 digit after the whole second 00:00:00.0000000

DateTime

Stores the date and time.

Which T-SQL command removes all rows from a table without logging the individual row deletions?

TRUNCATE TABLE removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses few system and transaction log resources.

CREATE VIEW

The clause ORDER BY is not allowed in a create view statement.

The result of the query: SELECT COUNT(*) from employees

The number of rows in the table employees. The COUNT(*) function returns the number of records in a table.

What will be the result of the following statement? SELECT CAST (-1 AS SMALLDATETIME)

The system will generate an error. Only positive integer values can be converted to a SMALLDATETIME data type. A SMALLDATETIME data type is stored internally in SQL Server as two 2-bytes integers. The first 2 bytes store the number of days after the base date of January 1, 1900. Given this, a value of 0 corresponds to January 1, 1900. Any negative values will generate an error.

nvarchar(n)

Unicode characters variable 1 to 4000 2 bytes per character used

__________ can be used in Select, Where, or case statements. They can also be used to create joins.

User defined functions

What happens if a column is not specified using the basic INSERT command?

Values are placed in columns in the order listed in the statement. If a column is not specified using the basic INSERT command, values are placed into columns in the order listed in the statement from left to right.

Linked Servers

When data in the db is organizes so that all the data elements relating to the same real-world even or object are linked together.

Which of the following conditions will you use in a WHERE clause to select all last names that start with J?

Where LastName LIKE 'J%' to search for strings in a column, the LIKE operator is used together with the % wildcard.

The DELETE statement removes records in a table?

Yes

Example of a primary key would be a user ID. y/n

Yes A primary key defines one or more columns that uniquely identify each row in a table.

A Primary Key constraint enforces data uniqueness by creating a unique index for the Primary Key columns? y/n

Yes.

A Primary Key constraint is a column that participates in the PRIMARY KEY constraint cannot accept NULL values? y/n

Yes.

The ALTER TABLE command can add a new column? y/n

Yes.

The ALTER TABLE command can drop multiple columns from a table? y/n

Yes.

db_ddladmin

can issue DDL commands(statements)(create, alter,drop)

db_backupoperator

can perform backups, checkpoints, and DBCC commands, but not restores(only sysadmins can perform restores)

Float data type

considered a single precision data type; occupies 4 bytes of memory

SET NOCOUNT ON

turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.

(max) on any character data type

up to 2 GB

Exact Numeric Data Type: bigint

uses 8 bytes of storage approx. -9.2x10^19 to approx 9.2x10^19

What is the major difference between a stored procedure and a function?

A function must be compiled every time it is called. A stored procedure does not. Stored procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved which executes whenever it is called.

Function

A function must return a value but in Stored Procedure it is optional (A stored procedure can return zero or n values). Functions can have only input parameter; whereas, a stored procedure can have input/output parameters. Functions can be called from a Stored Procedure; whereas, Stored Procedures cannot be called from a Function.

__________ is a reserved keyword in SQL Server 2008 and therefore cannot be used as an object name or as a column name in a table, unless delimited.

ADD One of the uses of ADD is in the ALTER TABLE when adding a new column to a table.

DBCC

Database Consistency Check

__________ removes a SQL Server login account.

Drop Login

A user needs the __________ permissions to run a stored procedure.

EXECUTE. Permissions can be granted to an existing user, database role, or application role in the database.

TINYINT

Integer data from 0 to 255.

ALTER TABLE command

Modifies a table by altering, adding, or dropping columns and constraints, reassigning partitions, or disabling or enabling constraints and triggers.

Data Warehousing

The concept in SQL Server by which we can add other SQL Servers to a group and query both servers using T-SQL statements.

A table provides structure to store data? y/n

Yes. Databases use Tables and Views to provide structure and storage for data.

How many files does Microsoft SQL Server use to store the database?

3 Files: Primary (.mdf) Seconday (.ndf) Transaction log files (.ldf)

What will be the result of the following statement? SELECT STR(938.56, 6, 1)

938.6 The STR function returns character data converted from numeric data. The second parameter of the STR function is for the total length of the output, including the decimal point, sign, digits and spaces. The output is rounded to the number of decimal places specified in the third parameter.

What are three true statements about Foreign Keys?

A foreign key is a combination of one or more columns used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a foreign key constaint when you create or alter a table. A foreign key enforces referential integrity by ensuring only valid data is stored.

function

A group of command that return value. Sometimes called a routine, subroutine, or procedure.

What is a subquery?

A select statement that is nested within another T-SQL statement? Sub-queries are often referred to as sub-selects, as they allow a select statement to be executed arbitrarily within the body of another SQL Statement. The rest of the options refer to Inner Joins, Self Joins, and Outer Joins.

view

A subset of a table, which can be used for displaying data as well as updating or deleting rows.

Which of the following could not be used as a primary key?

A table containing only one FOREIGN key. The primary key of a table is the column whose values are different in every row. Because they are different, they make each row unique. If no one such column exists, the primary key is a composite of two or more columns whose values, taken together, are different in every row. A table containing only a FOREIGN key cannot be used as a primary key.

char(n)

ANSI character fixed 1 to 8000 characters 1 byte per character

varchar(n)

ANSI characters variable 1 to 8000 characters 1 byte per character used

Which is one of the primary benefits of nesting SQL statements between BEGIN TRAN and COMMIT TRAN?

BEGIN TRAN and COMMIT TRAN identify a Transaction. All SQL statements inside these statements are considered one logical unit of work. If any statement in the transaction fails, nothing in the database will be changed.

db_denydatawriter

Blocks modifying data in any table in the database. Will override any object-level grant

DBCC CHECKFILEGROUP

Checks all tables file group for any damage

DBCC CHECKALLOC

Checks that all pages in a database are correctly allocated.

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitive, accent marks and character width.

Consider using a Clustered Index for:

Columns that contain a large number of distinct values. Queries that return a range of values using operators such as BETWEEN, >, >=, <, <=. Columns that are accessed sequentially. Queries that return large result sets. Columns that are frequently accessed by queries involving join of GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clauses eliminates the need for SQL Server to sort the data because the rows are already sorted. OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.

Which are the two main language features of SQL?

DDL and DML

__________ erases data from a table.

DELETE.

What is the sub-language that allows user to insert, delete, and update data in a database?

DML (Data Manipulation Language) is a language element which allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server tables.

Which T-SQL command removes a table from the database?

DROP TABLE (name of table)

smalldatetime

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00), meaning there are no fractional seconds. Range: 1900-01-01 through 2079-06-06 (January 1, 1900, through June 6, 2079). Accuracy: one minute.

Fourth Normal Form (4NF)

Ensures no multivalued dependencies.

__________ is the best data type to calculate a percentage value.

Float.

Which three are T-SQL commands to handle privileges?

Grant, Deny, and Revoke.

Varchar(size)

Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.

CRUD

In computer programming, create, read, update and delete are the four basic functions of persistent storage.

Which of the following is NOT stored in the sysobjects system table?

Indexes The sysobjects contains one row for each function, constraint, or trigger created within a database. On the other hand, indexes are contained in the sysindexes system table, which contains one row for each index and table in the database.

What are the entities that can be viewed in the SQL Server Utility.?

Instances of SQL Server, Data-Tier apps, Database File, Volumes. SQL Server Utility forms a collection of managed instances with a repository for performance data and management policies. After data is collected from managed instances, Utility Explorer and SQL Server Utility dashboard and viewpoints in SQL Server Management Studio (SSMS) provide administrators with a view of SQL Server resource health through policy evaluation and analysis of trending instances and applications throughout the enterprise.

Date

Just stores the date YYYYMMDD January 1, 0001 to December 31, 9999

Second normal form (2nf)

No non-prime attribute in the table is functionally dependent on a proper subset of candidate key.

A Primary Key constraint is always created as CLUSTERED? y/n

No.

The ALTER TABLE command can change the IDENTITY specification of an existing column? y/n

No.

A database cannon parse out redundant data? y/n

No. Databases are capable or recognizing and parsing out duplicates using various techniques, including the DISTINCT keyword.

Remove marks the contents of a table for reallocation?

No. Remove is not a command and truncate serves this function.

Primary Keys

Not always created as CLUSTERED indexes. If a table already has an existing clustered index before a PRIMARY KEY constraint is created for the table, adding a PRIMARY KEY constraint on the table will be created as a non-clustered index.

Which of the following can be used to improve query performance?

Primary Keys Clustered index Unique Index

A clustered index improves the performance of which types of queries?

Queries that return large result sets.

Third Normal Form (3NF)

Removes columns that are not dependent upon the primary key.

The first normal form requires that the database exclude which of the following?

Repeating groups.

A view can be used to:

Restrict a user to specific rows in a table. Restrict a user to specific columns. Restrict information rather than supplying details.

Full join

Return all rows when there is a match in ONE of the tables

What is the right way to get an accurate count of the number of records in table1?

SELECT rows FROM sysindexes where name like 'IX_table1' SELECT COUNT(*) FROM table1 SELECT * FROM TABLE1

What statement is used to retrieve data from a database?

Select The DML command SELECT returns a result set of records from one or more tables.

What does SQL stand for?

Structured Query Language

Your organization is using a full recovery model. You perform full backups once a week on Saturday night and differential backups every day. If the database fails on Saturday morning, what should you do first? How many differential backups must you restore?

The first step is to backup the transaction log. This log holds all the transactions since the last differential log and then you should restore the full backup, then the most recent differential backup and then the transaction log again. 1 differential backup must be restored because it copies all the changes that have occurred since the last full backup.

When should you use an UPDATE statement?

To modify a set of existing table rows.

nchar(n)

Unicode characters fixed 1 to 4000 characters 2 bytes per character

TRUNCATE TABLE removes all data from a table?

Yes, and marks the space it removed content for reallocation.

A Primary Key constraint can be composes of more than 1 column? y/n

Yes.

The ALTER TABLE command can modify the data type of an existing column? y/n

Yes.

A database retrieves data from different tables and views? y/n

Yes. Data can be retrieved from multiple tables and views simultaneously by writing queries using the JOIN or APPLY key words.

A table can be created in a graphical interface or by using syntax? y/n

Yes. Many database engines provide a graphical user interface for table creation in addition to the CREATE TABLE statement.

Table

a group of rows and columns. In an RDMS, a table is the data structure characterized by rows and columns, with data occupying or potentially occupying each cell formed by a row-column intersection. The table is the underlying structure of a relation.

Check constraint

allows the administrator to limit the types of data a user can insert into the database. In other words, the column cannot be blank. It also means that you cannot insert a new record, or update a record without adding a value to this field.

__________ is the best method to prevent the loss of data.

backups Data loss can happen in many ways. One of the most common causes is physically failure of the media the data is stored on. Normally hard drives drives will live for years without incident. But eventually they will die, therefore in this scenario it's best to have a backup of all our information.

Timestamp

date/time that the current record was created in the database.

Approximate Data Type: Float(53)

double precision Float in T-SQL -1.79 x 10^308 to positive

DBCC CHECKDB

ensures the tables in the DB and the indexes are correctly linked.

Time(0-7)

just time up to the number of digits specified.

Which database index is most similar to the index in a book?

non-clustered index Both the clustered index and Non-clustered index can use a B-tree structure to increase search speed.

The where clause allows you to __________.

obtain a subset of rows. The WHERE clause is used to obtain only the records that fulfill the criterion in the WHERE clause.

Which of the following fixed server roles can shut down the SQL server?

serveradmin has access to the SHUTDOWN command

Approximate Data Type: Float(24)

single precision similar to T-SQL Real -1.79 x 10^308 to positive

DateTimeOffset(0-7)

stores date and time as well as a timezone and daylight savings notation to the end.

SELECT GETDATE () +3

the result will be three days after the current date.

Primary key constraint

uniquely identifies each record in a database table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.

Exact Numeric Data Type: bit

uses 1 byte of storage 0 to 1 (boolean)

Exact Numeric Data Type: tinyint

uses 1 byte of storage 0 to 255

Numeric data type: smallint

uses 2 bytes of storage -32,768 to 32,767

SMALLINT

uses 2 bytes to store values ranging from -32,768 to 32,767

Exact Numeric Data Type: smallmoney

uses 4 bytes of storage -$214,748.3648 to $214,748.3647

Exact Numeric Data Type: int

uses 4 bytes of storage -2,147,483,648 to 2,147,483,647

numeric and decimal data type

uses 5-17 bytes. (variable depending on scale) -10^38+1 to -10^38-1

Exact Numeric Data Type: money

uses 8 bytes of storage -922 a lot of decimals to 922 a lot of decimals

Transact-SQL (T-SQL)

Microsoft's proprietary extension to SQL. Expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes the DELETE and UPDATE statements.

text

holds a variable-length character string. Maximum size is 2 Gb of text data.

Record

A group of fields across one row. In Relational Database Management System, it is a data structure that is a collection of fields, each with its own name, value, and type .

column

A group of items listed up and down (vertically)

Variable

A label given to a column.

Field

A location in a record in which a particular type of data is stored.

Stored procedure

A named group of SQL statements that have been previously created and stored in the database.

What happens if you omit the WHERE clause in an UPDATE statement?

All records will be updated. The WHERE class specifies which record or records should be updated. If you omit the WHERE clause, all records will be updated.

Which DDL statment will change a table?

Alter Alter changes the structure of the table by adding fields or changing the attributes of fields.

Which organization governs that syntax of the Structured Query Language Language (SQL)?

American National Standards Institute (ANSI) Originally called the American Engineering Standards Committee (AESC)

__________ data type contains On of Off, 1 or 0, True or False.

Boolean

__________ can create, alter, drop, and restore databases.

dbcreator

A table is often implemented as an array of records, a linked list, or several arrays of different data types, all using a common __________ .

indexing scheme

The SQL __________ operator takes the results of two queries and returns only rows that appear in both result sets.

intersect It removes duplicate rows from the final result set unless INTERSECT ALL is used.

Data Manipulation Language (DML)

is a language used to insert data, update, and query and a database. Most DMLs are also capable of performing mathematical and statistical calculations that facilitate generating reports.

Name varchar(50) Lastname varchar (50) Email varchar (50) Phone varchar(15) With 100,000 records, the estimate table size will be?

1 Mb = 1020 Kb (binary) 1 record is a total of 165 bytes 165 bytes x 100,000 records = 16,500,000 bytes 16,500,000 bytes/1,024 Kb = 16,113.28125 Kb 16,113.28125 Kb / 1024 Kb = 15.73 Mb

Users can be initially identifies by three different methods:

1.) windows user login 2.) Membership in a Windows user group 3.) SQL server-specific login (if the server uses mixed-mode security).

What is the major functional difference between a clustered and non-clustered index?

A non-clustered index has a pointer value at the leaf layer rather than the value itself. Non-clustered indexes are less efficient in searches than clustered indexes. Both the clustered index and non-clustered index can use a B-tree structure to increase search speed.

index

A physical structure containing pointers to data. Indices are created in an existing table to locate rows more quickly and efficiently.

Stored procedure

A pre compiled group of SQL statements written by users and saved to database is called a stored procedure.

First normal form (1nf)

A table that faithfully represents a relation and has no repeating groups.

SQL Code injections is __________ ?

A technique that exploits security vulnerabilities in the db layer of an application.

Differential backup

A type of partial backup that involves copying all changes made since the last full backup. Thus, each new differential backup file contains the cumulative effects of all activity since the last full backup.

Incremental backup

A type of partial backup that involves copying only the data items that have changed since the last partial backup. This produces a set of incremental backup files, each containing the results of one day's transactions

What is a view?

A virtual table created by a query joining one or more tables.

DELETE FROM table_name What will be the result of executing the statement above without a WHERE clause?

All records will be deleted.

Bulk Logged Recovery Model

Ad adjunct of the Full Recovery model that permits high-performance bulk copy operations. Reduced log space usage by using minimal logging for most bulk operations. If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Can recover to the end of any backup. Point-in-time recovery is not supported.

INSERT statement

Adds one or more rows to a table. 1.) used to insert specified values into specified fields in a table. 2.) used to insert the contents of elements from an array, memory variable, or property of an object that match the field names in the table. 3.) To insert rows from and SQL SELECT command into the specified fields in the table.

Full backup

Contains all the data in a specific database or set of filegroups or files to allow recovering that data.

You need to store whole numbers ranging from 1 to 100. This number will never change. Which data type should you choose?

Byte - Holds whole numbers from 0 to 255 in 1 byte of storage. Integer - holds whole numbers between -33,768 and 32,7672 in 2 bytes of storage. Long -holds whole numbers between -2 millionish and 2 million in 4 bytes of storage.

setupadmin

Can configure linked servers, extend stored procedures and the start-up stored procedure. This is a Fixed server role

diskadmin

Can create, alter, and drop disk files. This is a Fixed server role

db_denydatareader

Can read from any table in the database, this deny will override an object-level grant.

Which statement provides if-then-else logic?

Case CASE creates a functionality of when-then-else (WHEN this criteria exists, THEN do this)

Which two are DDL statments? Insert Update Create Select Drop

Create and Drop

What are the three characteristics are foundations of a modern database?

Data - entries in the database. Attributes - the characteristics of the database. Relationships - how database objects tie together.

Fifth Normal Form (5NF)

Ensures no nontrivial join dependencies exist.

What determines a database in the third normal form?

Every non-prime attribute is non-transitively dependent on every candidate key in the table.

Fourth normal form (4nf)

Every non-trivial multi-valued dependency in the table is a dependency on a super key.

Consider a query of two tables. The result contains only the rows that have a match in both tables. What kind of Join was used?

Inner Join - returns rows when there is at least one match in both tables. The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B) A right outer join returns all the values form the right table and matched values from the left table (Null in case of no matching join predicate). A full outer join combines the effect of applying both left and right outer joins.

Data Definition Language (DDL)

Is a language that defines all attributes and properties of a database, especially record layouts, field definitions, key fields, file locations, and storage strategy.

What is the maximum date value that can be store in a SMALLDATETIME data type?

June 6th, 2079 The range is from January 1st, 1900 through June 6th, 2079.

left join

Returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2)

right join

Returns all rows from the right table (table_name2), even if there are no matches in the left table (table_name1)

Join

Returns rows when there is a least one match in both tables.

Benefits of using Stored procedures are:

Speed - Stored procedures are pre-compiled Code reuse - stored procedures often involve complex code which only has to be written once. Security - permissions can be granted for stored procedures while being restricted for the underlying tables. Reduced traffic between client and server - the query is stored on the server and only the procedure call gets send, so traffic to the server is decreased.

DDL (Data Definition Language)

Subset of the Transact-SQL language; it deals with creating database objects like tables, constraints, and store procedures.

The UPDATE statement operates on which database structure?

Tables

How do you use a stored procedure?

The SQL statement to call an existing stored procedure is: EXEC procedurename (input variables)

What is the result of the following query: Select MAX(salary) from employee.

The maximum salary of all employees. The MAX function returns the maximum value of an expression.

DROP command

This statement removes an object from any SQL relational database management system (RDBMS). The type of objects that can be dropped depend o which RDBMS is being used, but most support the dropping of tables, users, and databases.

What does the following statement do? Select Dateadd(DD,0,Datediff(DD,0,Getdate()))

This statement will return just the date part of the current system date removing the time.

Simple recovery model

Transaction logs are truncated on a regular basis which limits recovery options but lowers maintenance overhead. Ensure you get a regular full backup. Can recover only to the end of the backup because no log backups are done. This model automatically reclaims log space to keep space requirements small. The work-loss exposure is greater than the Full Recovery Model because changes since the most recent backup are unprotected. In the even of a disaster, those changes must be redone.

A __________ initiates an action when an event (INSERT, DELETE OR UPDATE) occurs.

Trigger

Relationships between tables are stored as foreign key constraints. y/n

Yes Relationships between tables are stored as a foreign key constraints.

unique constraint

allows the database administrator to specifically identify which column should not contain duplicate values.

Normalization

is organizing information in relational databases to ensure the database is suitable for querying and free of insertion, update, and deletion anomalies that could lead to loss of data integrity. Normalization also promotes efficient maintenance, storage, and updating.