98-364 Database Fundamentals
Which SQL statement is used to update data in a database? a. SAVE b. MODIFY c. SAVE AS d. UPDATE
D. UPDATE
Which of the following is a data manipulation language (DML) statement? A. ALTER B. CREATE C. DROP D. UPDATE
D. UPDATE
Which of the following is not a constraint? a. CHECK b. DEFAULT c. UNIQUE d. INDEX
D. index
If a _____ command has been issued anywhere, then, just as in Windows, the user is blocked.
DENY
What would be one reason to routinely back up your database?
One reason to routinely back up your database is to be able to restore the database to a functioning state after a hardware or server failure. It is important to have a backup strategy so that you can recover in the event of data loss.
This server role can create, alter, and drop disk files.
Diskadmin
_____ are the most common SQL Server data types used to store numeric information
Exact numeric data types
This object permission grants the right to run stored procedures or user-defined functions.
Execute
The only required component of the SQL SELECT query is the _____ clause
FROM
True or False: A user must have permissions to access the files that make up a database in order to use the database.
False
True or False: SQL Server will automatically promote other integer data types (i.e., tinyint, smallint, and int) to bigint.
False
Two common naming conventions are _____ and _____.
PascalCase and camelCase
Restores a database in stages, beginning with the primary filegroup and one or more secondary filegroups.
Piecemeal restore
This server role can kill a running SQL Server process.
Processadmin
Use the __________ command to recover data that was accidentally deleted by a user.
RESTORE
Recovers data that is already consistent with the database and needs only to be made available.
Recovery only
_____ joins allow you to match related records taken from different source tables.
INNER
What are the three types of JOIN statements?
INNER, OUTER, CROSS
The char data type is
Non-Unicode character data type with fixed length.
SQL Server includes fixed, predefined _____. Primarily, they grant permission to perform certain _____.
Server roles; server-related administrative tasks
The sql_variant data type
Stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant
This server role can perform any activity in the SQL Server installation, regardless of any other permission setting. Ths role even overrides denied permissions on an object.
Sysadmin
The datetimeoffset data type takes up _ byte(s) of storage
10
The uniqueidentifier data type takes up _ byte(s) of storage
16
The uniqueidentifier (UUID) data type is
16-byte GUID
When converting string values to bit values, TRUE is converted to _ and FALSE is converted to _.
1; 0
The time data type takes up _ byte(s) of storage
5
Which of the following will combine the results of two or more queries into a resulting single set that includes all the rows belonging to the query? a. UNION b. EXCEPT c. INTERSECT d. AND
A
What is a database management system (DBMS)?
A DBMS is a collection of programs that enables you to enter, organize, and select data in a database.
What is a unique constraint?
A unique constraint allows the database administrator to specifically identify which column should not contain duplicate values.
Which of the following is not a constraint? a. Null b. Unique c. Check d. Primary
A. Null
What role gives full access to an individual database? a. db_owner b. db_accessadmin c. db_securityadmin d. db_ddladmin
A. db_owner
The binary data type is
Binary data with fixed length.
The varbinary data type is
Binary data with variable length.
The _______ data type is also known as the bit data type. This data type converts true and false string values to bit values, with true converted to 1 and false converter to 0.
Boolean
Which of the following things can help speed data retrieval? a. A DEFAULT constraint b. A primary key constraint c. A clustered index d. A foreign key constraint
C. A clustered index
The Orders table has a column named OrderDate. It currently has a default value of today's date. You need to modify the configuration so that there is no default value. Which statement should you use? A. DROP RULE B. DROP DEFAULT C. ALTER TABLE D. DELETE
C. ALTER TABLE
Which of the following is a server-scoped securable? A. Stored procedure B. Schema C. Login D. Index
C. Login
_____ is compliance with ANSI standards, which allow you to import or export to other database management systems. _____ is specific to T-SQL, but is a little bit more powerful.
Cast; convert
What is the primary means of programming and managing SQL Server?
Transact-SQL (T-SQL)
What are transaction log files?
Transaction log files use an .ldf extension and don't contain objects such as tables or views.
Collation refers to
a set of rules that determine how data is sorted and compared
Candidate keys that are not selected as the primary key are then known as _____ keys.
alternate
All floating data is _____.
approximate
The _____ is a special role that has all permissions in the database.
db_owner
A _____ contains only the data that has changed since the base.
differential backup
Each object's permission is assigned by _____, _____, or _____ user login permissions.
granting, revoking, denying
Within a database, a _____ is used to grant an entity (such as a user) access to an object (such as another user or a database).
permission
A regular character literal is always expressed with _____.
single quotes
A _____ is a previously written SQL statement that has been saved into a database.
stored procedure
The _____ server role is the ultimate security role, which has full access to all databases.
sysadmin
The value of a primary key must be ____________.
unique
True or False: Use the CREATE LOGIN statement to allow a Windows account to access SQL-Server.
True
True or False: Use the CREATE ROLE statement to create new roles within a database.
True
True or false: A table can contain only one clustered index.
True
What is a database?
A database is an organized collection of data, typically stored in electronic format. It allows you to input, organize, and retrieve data quickly
There are several different ways to remove rows from a table or view. You can identify and delete individual rows from the database using the _____ syntax, delete all the rows using a _____ statement, or remove the entire table using the _____ statement. Which method you choose depends entirely on your needs or the amount of data you need to remove.
DELETE; TRUNCATE TABLE; DROP TABLE
SQL Server gives the maximum of whatever permission has been granted. _____ is an exception to this rule, however
DENY
When a view definition contains a join:
DML can be performed using the view, but only on a single base table. A single UPDATE, INSERT, or DELETE statement can only modify, add, or remove data from one base table at a time.
What is Data Manipulation Language (DML)?
DML is the language element that allows you to manipulate data in any SQL Server tables.
You would like to delete all rows from the Customer table while preserving the table structure for future use. What command would you use?
TRUNCATE TABLE Customer
You wish to remove the rows from the Shirt.Size table while retaining the table structure for future use. What command would you use?
TRUNCATE TABLE Shirt.Size
True or False: Any DENY permission always overrides a granted permission.
True
Storage consumption is reduced when using _____ character data types.
Unicode
A _____ uses two bytes of storage per character so that you can represent one of 65,536 (16 bits are in 2 bytes, and 2^16 = 65,536) characters.
Unicode character
The nchar data type is
Unicode character data type with fixed length.
Exact numerics include (but are not limited to) _____, _____, _____, _____, _____, _____, and _____.
int, bigint, bit, decimal, numeric, money, and smallmoney
You would use the _______ data type to store numeric data that can be used for mathematical computations and does not require a decimal point.
integer
A _____ index contains the _____ index key values, and each of those keys has a pointer to a data row that contains the key value.
non-clustered
All users are automatically members of the _____ role.
public standard database
Unlike a table, a view is stored in a database as a _____.
query object
Enforcing the foreign key attribute is referred to as _____
referential integrity
A _____ uses one byte of storage for each character, which allows you to define one of 256 (8 bits are in a byte, and 2^8 = 256) possible characters.
regular character
The DROP statement
removes an object from a 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.
Add an index to one or more columns to speed up data ____________.
retrieval
The pointers within non-clustered indexes are referred to _____ and their structures depend on whether the data pages are stored in a _____ or a _____.
row locators; heap; clustered table
The _____ normal form ensures that each attribute does in fact describe the entity.
second
The SQL Server security model is based on what are referred to as _____; in this model, different objects (defined as databases, tables, logins, users, and roles) can be granted permissions to access different _____.
securables
When creating a view, be sure to consider ____________ in your design.
security
You need to add an two entries to the Customer table. The values that you would like to enter are: CompanyID - Microsoft FirstName - Bill LastName - Gates and CompanyID - Apple FirstName - Steve LastName - Jobs What commands would you use?
INSERT INTO Customer (CompanyID, FirstName, LastName) VALUES ('Microsoft', 'Bill', 'Gates'), ('Apple', 'Steve', 'Jobs')
A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups is a _____.
Partial backup
What is the function of the MERGE statement?
Performs insert, update, or delete operations on a target table based on the results of a join with a source table
What are primary data files?
Primary data files, which have 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.
_____ does not allow deletion of tables unless all of the related tables are deleted using a cascading delete.
Referential integrity
This server role can configure linked servers, extended stored procedures, and the startup stored procedure.
Setupadmin
_____ requires the least administration since the transaction log backups are truncated on a regular basis.
Simple Recovery
The tinyint data type is
an integer between 0 and 255
An _____ role is a database-specific role intended to allow an _____ to gain access regardless of its user
application
An _____ is a database-specific role intended to allow an application to gain access regardless of the user.
application role
The USE command
changes the database context to the specified database or database snapshot
Each table can have only one _____ index that defines how SQL Server will sort the data stored inside the table.
clustered
Because the data in a table can only be sorted in one way, it simply is not possible to have two _____ on the same table.
clustered indexes
The image data type is
deprecated. Therefore, use varbinary(max) instead.
The text data type is
deprecated. Therefore, use varchar(max) instead.
A _____ is based on the latest full backup of the data.
differential backup
Using _____ can speed up the process of making frequent backups to decrease the risk of data loss.
differential backups
A _____ contains all the data in a specific database or set of filegroups or files to allow recovering that data.
full backup
When you restore from differential backup, you must first restore the preceding _____ and then restore the last _____.
full backup; differential backup
It is possible for users who have not been granted direct access to a database to gain access using the _____ user account—and with this account, they can make limited changes within the database server.
guest
Update of data using variable-length data types may be _____ efficient than updates using fixed-length data types.
less
When you are defining the cost of a product, it is best to use the ___________ data type.
money
The three different methods by which a user can be initially identified include:
• Windows user login • Membership in a Windows user group • SQL Server-specific login (if the server uses mixed-mode security)
_____ allows you to restore to a point in time since the logs files record all SQL transactions and the time they were performed.
Full Recovery
All users are automatically a member of the __________ database role.
public role
When using the bit data type, columns that are 8 or fewer bits wide, these columns will be stored as _____, and if it has 9- to 16-bit columns, they will be stored as _____.
1 byte; 2 bytes
A unique constraint acts similarly to a primary key but with two important differences:
1. Columns containing a unique key constraint may contain only one row with a NULL value. You cannot have two rows containing a NULL value in the same option, as that would violate the unique constraint's duplicate value error. 2. A table may have multiple unique constraints.
What steps would an administrator need to take to design the following hierarchical structure for database roles: • The worker role may have limited access. • The manager role may have all worker rights plus additional rights to look up tables. • The administrator role may have all manager rights plus the right to perform other database administration tasks.
1. Create the worker role and set its permissions. 2. Create the manager role and set its permissions. Add the manager role as a user to the worker role. 3. Create the admin role. Add the admin role as a user to the manager role.
After you and the network administrator review some records in the AdventureWorks database, the two of you decide to delete some old records. This scenario brings up the following questions: 1. What command would you use to remove all records from the Customer table where the age is less than 18? 2. What command would you use to remove all records from the Schools table that have enrollment less than 500? 3. What command would you use to remove all records from the Contact table that do not have a country of USA and at the same time free the space used by those records? 4. What command would you use to delete the Temp table?
1. DELETE FROM Customer WHERE Age < 18 2. DELETE FROM Schools WHERE Enrollment < 500 3. This is not possible. TRUNCATE is all or nothing. 4. DROP TABLE Temp
You have just have been hired as a database administrator for the AdventureWorks Corporation. A network administrator wants to know how to extract information from the AdventureWorks database. Therefore, you need to answer the following questions: 1. What command would you use to display records from a table? 2. What command would you use to display a FirstName and LastName from the Users table? 3. What command would you use to display all records from the Member database and have it sorted by the Name column? 4. What command would you use to display all records from the Suppliers table that have the City of Sacramento? 5. What command would you use to display the CompanyName, ContactName, and PhoneNumber from the Suppliers table with Supplier ID greater than 1000? 6. What command would you use to display CompanyName, ContactName, and Phone Number from the Customers table for companies that have more than 100 employees and reside in the state of California?
1. SELECT * FROM <table> 2. SELECT FirstName, LastName FROM Users 3. SELECT * FROM Member ORDER BY Name 4. SELECT * FROM Suppliers WHERE City = 'Sacramento' 5. SELECT CompanyName, ContactName, PhoneNumber FROM Suppliers WHERE SupplierID > 1000 6. SELECT CompanyName, ContactName, PhoneNumber FROM Customers WHERE State = 'California' AND Employees > 100
What are two basic requirements for a database to be in third normal form?
1. The database must already meet the requirements of both 1NF and 2NF. 2. The database must not contain any columns that aren't fully dependent upon the primary key.
First normalized form requires these 3 conditions be met:
1. The table must have no duplicate records. Once you have defined a primary key for the table, you have met the first normalized form criterion. 2. The table also must not have multivalued attributes, meaning that you can't combine in a single column multiple values that are considered valid for a column. 3. The entries in the column or attribute must be of the same data type.
As the AdventureWorks DBA, you need to insert data into the Credit table in the Sales database using a variety of data statements, along with updating and deleting data from the same Credit table. 1. After opening SSMS, what commands would you type and execute in the AdventureWorks database to review the columns in the Sales table? 2. In the existing query window, what commands would you use that will add the following row to the Credit table? Patrick, Roberts, 6000, [email protected] 3. In the existing query window, what commands would you type, highlight, and execute to add the following rows to the Credit table for firstname, lastname, and credit limit? Alex, Hall, 5000 Annie, Smith, 10000 4. In the existing query window, what commands would you use to update the email address (Email) for Annie Smith in the Credit table using the Update statement?
1. USE AdventureWorks SELECT TOP (1) FROM Sales 2. INSERT INTO Credit (FirstName, LastName, CreditScore, Email) VALUES ('Patrick', 'Roberts', 6000, '[email protected]') 3. INSERT INTO Credit (FirstName, LastName, CreditLimit) VALUES ('Alex', 'Hall', 5000); ('Annie', 'Smith', 10000) 4. UPDATE Credit SET Email = '[email protected]' WHERE FirstName = 'Annie' AND LastName = 'Smith'
A regular character uses ____________ byte(s) of storage for each character, whereas a Unicode character requires ____________ byte(s) of storage.
1; 2
When using the decimal or numeric data type, the precision value must be a minimum of _ and a maximum of _. The default precision number is _.
1; 38; 18
Describe the five normalization forms. 1NF 2NF 3NF 4NF 5NF
1NF - eliminate repeating groups 2NF - Eliminate redundant data 3NF - Eliminate columns not dependent on key 4NF - Isolate independent multiple relationships 5NF - Isolate semantically related multiple relationships
A column with data type char(23) would have a storage of how many bytes?
23
The date data type takes up _ byte(s) of storage
3
The datetime2 data type takes up __ byte(s) of storage.
4
The int data type takes up _ byte(s) of storage
4
The smalldatetime data type takes up _ byte(s) of storage
4
The smallmoney data type takes up _ byte(s) of storage
4
Using a 4-byte float supports precision up to _ digits and an 8-byte float supports precision up to _ digits.
7; 15
It is important to remember that _ bits equal 1 byte.
8
The bigint data type takes up _ byte(s) of storage
8
The datetime data type takes up __ byte(s) of storage.
8
The money data type takes up _ byte(s) of storage
8
You need to create a module that performs the following tasks: * Accepts an order number * Sets the value of the Status column in the Orders table for that order number to Shipped * Retrieves a tracking number from a table * Logs shipping information to the Shipping table * Returns the number of items shipped and the customer's e-mail address What should you create?
A stored procedure. A stored procedure can modify the data in permanent tables and return multiple values as output parameters.
You execute the following query: SELECT LoanNumber, OriginalLoanAmt, InterestRate FROM Loan WHERE BorrowerID IN (SELECT BorrowerID WHERE BorrowerState = 'TX') What is this an example of?
A subquery. A subquery, sometimes called an inner query, is a query that is included within another query. First, the subquery executes and returns a result to the containing, or outer, query. The outer query can use a subquery wherever an expression is allowed, such as in a WHERE or HAVING clause condition. Subqueries can return a single value or multiple values. However, the outer query must use the appropriate type of operator. For this query, first the inner query executes returning multiple BorrowerID values for all the borrowers with a BorrowerState = 'TX'. Because this result could contain multiple values, the outer query must use an operator that is capable of accepting multiple values, such as the IN operator. Attempting to use an operator such as =, which requires a single value, would result in an error.
Which of the following are supported database restore scenarios? a. Restore an entire database from a full backup. b. Restore an entire database from a series of partial backups. c. Restore part of a database using partial backups. d. Restore specific files used by the database.
A, C, D
You have an empty Product table that you created with the following statement: CREATE TABLE Product ( ProdID int PRIMARY KEY, CategoryID int NOT NULL, Description varchar(35), QtyOnHand int CHECK (QtyOnHand > 0 AND QtyOnHand <= 50)) Which statement will successfully insert data into the Product table? A. INSERT INTO Product VALUES (101, 1, 'Outdoor Extension Cord', 25) B. INSERT INTO Product (ProdID, CategoryID, Description, QtyOnHand) VALUES (101. 2, 'Oval Wall Mirror', 65) C. INSERT INTO Product VALUES (101, NULL, 'Outdoor Extension Cord', 25) D. INSERT INTO Product (Description, QtyOnHand) VALUES ('Oval Wall Mirror', 15)
A. INSERT INTO Product VALUES (101, 1, 'Outdoor Extension Cord', 25)
What is an index?
An index is a data structure that improves the speed of data retrieval operations on a database table.
_____ is the act of establishing or confirming a user or system identity.
Authenticating
Which normal form ensures that each attribute describes the entity? a. 1NF b. 2NF c. 3NF d. 4NF
B. 2NF
By using the _______________and a _______________ or ____________while performing maintenance tasks, you will catch most accidents before they happen.
BEGIN and a COMMIT or ROLLBACK
The one user that SQL Server automatically creates during installation of the software is _____, which includes all Windows users in the Windows Administration group and allows a choice of what groups or users are added during setup.
BUILTINS/Administrator
You wish to create a stored procedure named GetEmployees. The stored procedure will: - Return data from the Employees table - Return data from the, FirstName, LastName, and Department columns - Allow you to supply values for FirstName and LastName when executing the procedure as Unicode-character strings of up to 50 characters - Ignore results where the EndDate is null. How would you create this stored procedure using Transact-SQL? How would you execute this stored procedure to search for an employee named Bill Gates?
CREATE PROCEDURE GetEmployees @FirstName nvarchar(50), @LastName nvarchar(50) AS SELECT FirstName, LastName, Department FROM Employees WHERE FirstName = @FirstName AND LastName = @LastName And EndDate IS NULL exec GetEmployees N'Bill', N'Gates' ---or exec GetEmployees @FirstName = N'Bill', @LastName = N'Gates' ---or exec GetEmployees @LastName = N'Gates', @FirstName = N'Bill'
You wish to create a view named vwCustomer. The view criteria should include: - Results gathered from the Customers table - CustomerID column - CompanyName column - Phone column What sequence of commands would you use?
CREATE VIEW vwCustomer AS SELECT CustomerID, CompanyName, Phone FROM Customers
_____ joins return all rows from one table along with all rows from the other table. WHERE conditions should always be included.
CROSS
What is the function of the ALTER statement?
Changes an existing object.
What is the function of the UPDATE statement?
Changes existing data in one or more columns in a table or view
What is the function of the USE statement?
Changes the database context.
By default, SQL Server has predefined _____ precedence. If you wish to override how data is being sorted, you must use a _____ clause.
Collation
_____ refers to a set of rules that determine how data is sorted and compared
Collation
In SQL Server, each _____, _____, _____, and _____ always has a related data type that defines the storage characteristics of the data being stored.
Column, local variable, expression, and parameter
Restores an entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups).
Complete database restore
What are constraints?
Constraints are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered.
What are DML statements used for?
DML statements are used to retrieve, add, change, or delete data in a database.
This object permission grants the to create foreign keys with _____
DRI (References)
To remove a SQL server login for a Windows account named "Bill" on the "Microsoft" domain, what command would you use?
DROP LOGIN 'Microsoft\Bill'
You would like to delete all rows from the Customer table without preserving the table structure for future use. What command would you use?
DROP TABLE Customer
You wish to completely remove the Shirt.Size table from your database. What command would you use?
DROP TABLE Shirt.Size
The removal of an entire table can be accomplished using the _______________ and _______________ syntax.
DROP TABLE and <table name>
A backup of one or more files that contain data extents that were changed since the most recent full backup of each file is a _____.
Differential file backup
Which of the following statements best describes the importance of creating views? a. Views give users the ability to access underlying tables. b. Views allow you to limit the type of data users can access. c. Views reduce the complexity for end users so they don't need to learn complex SQL queries. d. Both a and b e. Both b and c f. All of the above
F. All of the above
True or False: Multiple differential backups must be restored in the same order as originally created.
False
Restores a file or filegroup in a multi-filegroup database. After a full file restore, a differential file backup can be restored.
File restore
You would include a HAVING clause in a query to:
Filter the results after grouping. You would include a HAVING clause in a query to filter the results grouped by a GROUP BY clause. First, the WHERE clause is applied to filter the result set. Then, the GROUP BY clause is applied to group the results. After grouping is done, the HAVING clause is evaluated to specify a condition that is checked after grouping has occurred to further restrict the results. A HAVING clause can only reference aggregate functions or columns that are also specified in the GROUP BY clause. For example, suppose you have the following query: SELECT LocationName FROM Instructor INNER JOIN Location ON Instructor.LocationID = Location.LocationID WHERE Status = 'Part-time' GROUP BY LocationName HAVING COUNT(*) < 10 This query uses an INNER JOIN in the FROM clause to join the two tables together. An INNER JOIN clause is used to display only rows that meet the criteria specified in the ON clause. The WHERE clause is used to filter the rows before they are grouped by location so that only Part-time instructors are retrieved. After the WHERE clause is applied, the rows are grouped by location using a GROUP BY clause so that the number of part-time instructors for each location can be determined. Finally, the HAVING clause is applied to filter the results to include only the locations that have less than ten part-time instructors.
What is a flat-type database?
Flat-type databases are simplistic in design. They are most commonly used in plain-text formats. Because their purpose is to hold one record per line, they make 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. Each column can be referred to as a field (such as a person's last name or a product's ID number), and each row can be referred to as a record (such as a person's or product's information).
The ____ numeric type is commonly used in the scientific community and considered an approximate-number data type. This means that not all values within the data-type range will be represented exactly.
Float
_____ and _____ are used in conjunction with floating-point numeric data.
Float and real
Use the __________ command to allow users to access objects within the database.
GRANT
You wish to give Select and Update permissions to the users "Bill" and "Steve" on the "Planets" table. Bill and Steve are managers that will need to be able to give these same permissions to their team members. What commands would you use?
GRANT SELECT, UPDATE ON Planets TO Bill, Steve WITH GRANT OPTION
Object permissions are assigned with the SQL DCL commands _____, _____, and _____.
GRANT, REVOKE, DENY
_____ permission means that a user can access the object.
Granting
_____ backups contains only the data that has changed since the last full or _____ backup.
Incremental
In order to create an _____, a series of keys is built from one or more columns in each row within a table or a view.
Index
This object permission grants the right to insert data.
Insert
The int data type is
Integer data from -2^31(-2,147,483,648) to 2^31-1 (2,147,483,647).
The bigint data type is
Integer data from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
The smallint data type is
Integer data from -32,768 to 32,767
The _____ clause allows you to combine related data from multiple table sources.
JOIN
_____ statements can be specified in either the FROM or the WHERE clause, but it is recommended that you specify them in the FROM clause.
JOIN
The range for the datetime data type is _________ to _________.
January 1, 1753, and December 31, 9999
The range for the datetime2 data type is _________ to _________.
January 1, 1900, and June 6, 2079
The money data type is
Monetary or currency values from -922,337,203,685,477.508 to 922,337, 203,685,477.5807
Which data type is used in places where you want money or currency involved in your database?
Money
_____ and _____ are Transact-SQL data types that you would use to represent monetary or currency values.
Money and smallmoney
_____ is the elimination of redundant data to save space.
Normalization
_____ is the process of organizing data in order to reduce redundancy by dividing a database into two or more tables and then defining table relationships.
Normalization
_____ reduces locking contention and improves multiple-user performance.
Normalization
You are creating a relational database. Your current data model meets the following requirements: * Each table represents a single entity identified by a primary key. * Repeating columns have been eliminated from each table. * All columns in each table depend only on the table's primary key. * Foreign keys have been implemented to relate the tables. Your database is considered to be:
Normalized to third normal form. A database normalized to first normal form has separate tables with no repeating attributes with each table row identified by a primary key. A database normalized to second normal form has these characteristics and also uses foreign keys to relate the tables. A database normalized to third normal form has all of the characteristics of a database normalized to first or second normal form, but also has had any columns that are not totally dependent on the table's primary key removed.
_____ joins can include records from one or both tables you are querying that do not have any corresponding record(s) in the other table. There are three types of outer joins: _____, _____, and _____.
OUTER; LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
_____ are great for developing tight security where users execute stored procedures but aren't granted direct permission to any tables.
Ownership chains
Restores individual pages.
Page restore
When using the decimal or numeric data type, _____ is the maximum total number of decimal digits that can be stored, both to the left and the right of the decimal point.
Precision (p)
What are the three types of files that Microsoft SQL Server uses to store databases?
Primary data files (.mdf), secondary data files (.ndf), and transaction log files (.ldf)
What is the function of the DROP statement?
Removes an object from the database.
What is the function of the TRUNCATE statement?
Removes rows from a table and frees the space used by those rows.
What is the function of the DELETE statement?
Removes rows from a table or view
A DML statement can be used:
Retrieve, add, change, or remove data from a table. The SELECT statement can be used to retrieve data. The INSERT statement can be used to add data. The UPDATE statement can be used to change data. The DELETE statement can be used to remove data.
What commands would return all the rows and columns in the ProductCategory table?
SELECT * FROM ProductCategory
To select a single column for your query, identify the name of the column by typing it between the _______________and _______________words in the query statement.
SELECT and FROM
What is Microsoft's primary tool for managing the server and its databases using a graphical interface?
SQL Server Management Studio (SSMS)
When using the decimal or numeric data type, _____ reflects the maximum number of decimal digits that can be stored to the right of the decimal point.
Scale (s)
Using the ______________ statement is the most efficient way to delete all rows from a table.
TRUNCATE TABLE
You have two tables in your database named CurrentCourses and PreviousCourses. Both have a CourseName column. You need to display the names of all courses from both tables, but exclude duplicate course names. Which operator should you use in your query?
The UNION operator. The UNION operator is used to return a single result set that includes the rows returned from two separate queries, with duplicate rows omitted. The result set returned by each of the two queries must have the same number of columns in the same order, and the returned columns must have compatible data types. For example, in this case, you would specify a query to select the desired data from the CurrentCourses table and another query to select the desired data from the PreviousCourses table. Then, you would use the UNION operator to combine the two result sets into a single result set. In this case, you would use the following SELECT statement: SELECT CourseName FROM CurrentCourses UNION SELECT CourseName FROM PreviousCourses
You have two database tables, Loan and Borrower. The Borrower table contains 20 rows and the Loan table contains 60 rows. You execute the following select statement: SELECT BorrowerID, BorrowerName, LoanNumber FROM Borrower, Loan How many rows will the query return?
The query will return 1200 rows. When no join is provided in a query, the query performs a cross join and returns a Cartesian product. A Cartesian product includes a row in the result for each possible combination of results from the other tables. The number of rows returned equals the number of rows in the first table multiplied by the number of rows in the second table. Sometimes Cartesian products are used for unique purposes, such as generating test data. However, they should be used with caution because they may return extremely large result sets.
You have a database that has an OrderDetails table containing one or more rows for each order. Each order is sequentially assigned an OrderID to identify the order. You execute the following query against the OrderDetails table: SELECT TOP 20 OrderID, SUM(LineTotal) AS OrderTotal FROM OrderDetails GROUP BY OrderID ORDER BY OrderTotal DESC What is the result?
The result is the 20 orders with the highest total. The GROUP BY clause allows you to perform an aggregate calculation over rows that have a matching value in a column. In this case, the statement groups all rows associated with a specific order and sums the values in the LineTotal column for each order. The ORDER BY clause is specified after the GROUP BY clause and orders the results in descending order by the total summed value by including the alias for the summed value and the DESC keyword. The ORDER BY clause can only reference aggregate values, identified with or without an alias, or a column referred to in the GROUP BY clause. Then, the TOP clause returns the first 20 rows in the ordered result, which in this case would be the 20 orders with the highest total.
Where does a record of each data modification get stored before being written to the database?
The transaction log. All data modifications are recorded to the transaction log before they are written to the database. This allows a transaction to be rolled back if an error occurs. It also allows for recovery to a point in time by replaying transaction logs during the recovery process.
What are three fundamental characteristics of databases?
They are designed to store billions of rows of data. They are limited to the computer's available hard disk space. They are optimized to use all a computer's available memory to improve performance.
You are creating a relational database that will store information about physicians and their patients. Each patient may have more than one physician. Each physician may have multiple patients. What should you include?
Three tables and two one-to-many relationships. You would create a Physician table and a Patient table. In this case, the relationship between physicians and patients is a many-to-many relationship because each physician can have multiple patients and each patient can have multiple physicians. To implement a many-to-many relationship in a relational database, you create an additional table known as a junction or intersection table. This table contains a column that references the primary key of the table on the one side of the relationship and another column that references the primary key of the table on the other side of the relationship. For example, in this case, you might have a Physician table with a primary key on the PhysicianID column containing data about each physician and a Patient table with a primary key on the PatientID column containing information about each patient. To implement the many-to-many relationship between physicians and patients in this case, you would create a new table containing two columns. One column would reference the primary key of the Physician table, PhysicianID, and the other would reference the primary key of the Patient table, PatientID. Each row in the table would represent a patient-physician combination. You can include additional columns in the junction table if needed to represent attributes for each patient-physician combination.
True or False: TRUE and FALSE string values can be converted to bit values.
True
True or False: Users must be explicitly granted access to any user database.
True
Use the _______________ statement to modify the data in one or more rows in a table.
UPDATE
You need to change an entry in the Customer table. You would like to change the LastName to "Gates" for the customer with CustomerID 50. What commands would you use?
UPDATE Customer SET LastName = 'Gates' WHERE CustomerID = 50
To select a database named TESTDB, you would execute what command?
USE TESTDB
To remove a user whose has a server account named Microsoft\Bill with a database username of 'Gates' for the Windows database. what command would you use?
USE Windows DROP LOGIN 'Gates'
The nvarchar data type is
Unicode character data type with variable length.
You would use the _______ data type to store character-string data in databases where you are supporting English attributer. If you are supporting multiple languages, use the ________ data type instead, as this will help minimize issues of character conversion.
Varchar; nvarchar
What statement should you use to insert values from one table into another existing table that already contains data?
You should use an INSERT statement. You can include a SELECT statement instead of a VALUES clause to use a SELECT statement as the source of the data to be inserted rather than specifying a VALUES clause explicitly. The SELECT statement can query one or more tables. For example, you might include a SELECT statement that retrieves data from several tables to insert into another table. You cannot use a SELECT INTO. The SELECT INTO statement cannot be used to insert rows into an existing table that already contains data.
The ProductInventory table and the ReorderList table are shown in the exhibit. The ReorderID column is an identity column. The ReorderList table stores a record for each product that has been ordered. You need to add rows to the ReorderList table that includes the ProductID, the current date, and a Quantity of 100 for all products that have a Quantity of 0 in the ProductInventory table. Which statement should you use?
You should use the following Transact-SQL: INSERT INTO ReorderList (ProductID, ReorderDate, Quantity) SELECT ProductID, getdate(), 100 FROM ProductInventory WHERE Quantity = 0 The INSERT statement allows you to use a query as the source for the rows. To do so, you specify the query instead of a VALUES list. In this case, you need to select the ProductID from the source table, use the getdate() function to provide the current date, and use a literal value for the Quantity column in the ReorderList table.
If you have a reason to force a conversion, you can use the _____ and _____ functions.
cast and convert
The syntax of the cast function is:
cast(source-value AS destination-value) ex. cast(count AS float)
A differential backup contains only the data that has __________ since the differential base.
changed
One important feature of SQL Server is its automatic creation of a _____ when the _____ is defined for a table
clustered index; primary key
A _____ occurs when you define more than one column as your primary key.
composite primary key
Even though a user may belong to a fixed database role and have certain administrative-level permissions, a user still cannot access data without first being granted permission to the _____ themselves.
database objects
Regardless of an organization's size, the end result of its _____ should be to ensure that users' assigned rights and responsibilities are enforced through a security plan.
database security
The end result of _____ is to ensure that the rights and responsibilities given to users are enforced.
database security
The _______ data type defines a date. Range: 0001-01-01 through 9999-12-31. (January 1, 1 AD, through December 31, 9999). Accuracy: one day.
date
Date and time data types include _____, _____, _____, _____, _____, and _____.
date, datetime, datetime2, datetimeoffset, smalldatetime, and time
You would use the ________ data type for storing values between the dates of January 1, 1753, and December 31, 9999, that are accurate to 3.33 milliseconds
datetime
You would use the ________ data type for storing values between January 1, 1900, and June 6, 2079, that are accurate to only 1 minute.
datetime2
The _________ data type defines a date that is combined with a time of day that has time-zone awareness and is based on a 24-hour clock. Range: 0001-01-01 through 9999-12-31 (January 1, 1 AD, through December 31, 9999). Range: 00:00:00 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
datetimeoffset
This database role authorizes a user to read all data in the database. This role is the equivalent of a grant on all objects, and it can be overridden by a deny permission.
db_datareader
This database role allows a user to write to all data in the database. This role is the equivalent of a grant on all objects, and it can be overridden by a deny permission.
db_datawriter
This database role authorizes a user to issue DDL commands (create, alter, drop).
db_ddladmin
This database role permits a user to read from any table in the database. This overrides any object-level grant.
db_denydatareader
This database role blocks a user from modifying data in any table in the database. This overrides any object-level grant
db_denydatawriter
This database role is a special role that has all permissions in the database. This role includes all the capabilities of the other roles and differs from the dbo user role. This is not the database-level equivalent of the server sysadmin role because an object-level deny will override membership in this role.
db_owner
This database role permits a user to manage database-level security—including roles and permissions.
db_securityadmin
Transactions that are running during the backup are never _____.
delayed
The ntext data type is
deprecated. Therefore, use nvarchar(max) instead.
Typically, _____ that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup.
differential backups
This is the recommended login account because the SQL Server can then use the Windows account specifically created for it. You can then grant administrator rights to the SQL Server account.
domain user account
When two tables relate to each other, one of them will act as the primary table and the other will act as the secondary table. In order to connect the two tables, the primary key is replicated from the primary to the secondary table, and all the key attributes duplicated from the primary table become known as the _____.
foreign key
The _____ normal form involves two independent attributes brought together to form a primary key along with a third attribute. But, if the two attributes don't really uniquely identify the entity without the third attribute, then the design violates the _____ normal form.
fourth
The _____ user account is not actually created when a database is created; it must be specifically added either through SSMS or through a Transact-SQL statement.
guest
An important part of a non-clustered index's function: if it points to a _____, the row locator is a pointer to the row, but in a _____, the row locator is then the clustered index key.
heap; clustered table
When working with SQL data, you may wish to convert values from one data type to another. In most situations, these conversions are done automatically. When a conversion is done automatically, it is called an _____.
implicit conversion
A _____ user account cannot be used outside the server environment.
local
If you are using a single-server installation, you may wish to choose this account, because the SQL Server can use the local system account of the operating system for permission to the machine. The only drawback of using this account login is that it fails to provide the necessary network security credentials for databases because it has privileges inside the operating system that the administrator's account does not. This creates a potential security hole.
local system account
A _____ is the process by which individual access to a computer system is controlled by identification of the user through the credentials he or she provides.
login or logon
Performing a backup operation has _____ effect on transactions that are running.
minimal
When you have a _____, you have duplicate databases on multiple servers. When information is written to one server, it is automatically replicated to the second server.
mirror database
Because SQL Authentication is less secure than Windows logins, avoiding _____ mode is recommended; however, it is available for backward compatibility.
mixed
A primary key makes it simple for you, as database administrator, to look at creating _____ based on the columns in a table.
non-clustered indexes
The third normal form is violated when the attribute depends on the key but also on another _____.
nonkey attribute
The second normal form is violated when an attribute depends on only _____
part of a key
The _____, _____, and _____ of the result depend on the precision of the same in the input expression.
precision, scale, and length
Perhaps the most important concept of designing any database table is ensuring that it has a _____.
primary key
When looking at whether there is a violation in either the second or third normal form, remember that each attribute is directly or indirectly tied to the _____.
primary key
What are the three different types of constraints available within SQL server to ensure that you are able to maintain database integrity?
primary keys, foreign keys, composite (unique) keys
The Query Analyzer in SSMS
provides a GUI-based interface to write and execute queries
Every user is automatically a member of the _____ role and cannot be removed, so the _____ role serves as a baseline or minimum permission level.
public
The _____ is a fixed role, but it can have object permissions like a standard role. Every user is automatically a member of the _____ and cannot be removed, so the _____ serves as a baseline or minimum permission level.
public role
A foreign key works in conjunction with primary key or unique constraints to enforce ____________ between tables.
referential integrity
The _____ account is the built-in SQL administrator account associated with SQL authentication.
sa
The _____ is the built-in SQL administrator account associated with SQL authentication.
sa account
When you have a _____, you are using an active-passive cluster that consists of two or more servers. When the active server fails, the passive server will become the active server, allowing for minimum downtime.
standby server
By creating _____ and _____, you make it possible to select, insert, update, or delete data using these statements.
stored procedures and functions
Remember, if you add a user to the _____ role, that user now has full access to every server function, database, and object for that server.
sysadmin
The _____ role can perform any activity in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides denied permissions on an object.
sysadmin
Users who lack the _____ level of access can't alter database server configurations or grant access where they shouldn't be able to.
sysadmin
The purpose of a _____ is to provide a structure for storing data within a relational database.
table
The _____ normal form checks for transitive dependencies.
third
If you take several different values and perform a mathematical operation on those values, the precision, scale, and length will be based on
those values on which you are performing the mathematical operations.
The _______ data type defines a time of day. This time is without time-zone awareness and is based on a 24-hour clock. Range: 00:00:00.0000000 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
time
A _____ will allow you to enforce the uniqueness property of columns, in addition to a primary key within a table.
unique key constraint
A _____ is a logical representation of a person within an electronic system.
user account
A _____ is simply a virtual table consisting of different columns from one or more tables.
view
What is a check constraint?
A check constraint allows the administrator to limit the types of data a user can insert into the database.
At a minimum, what is required within a column definition in a CREATE TABLE statement?
A column name and a data type. At a minimum, a column definition in a CREATE TABLE statement must contain a column name and a data type. The column name must also be unique within the table.
What is a data type?
A data type is an attribute that specifies the type of data an object can hold, as well as how many bytes each data type takes up.
What is a foreign key constraint?
A foreign key constraint in one table points to a primary key in another table.
What is a hierarchical database?
A hierarchical database is similar to a tree structure (such as a family tree). In this database, each "parent" table can have multiple "children," but each child can have only one parent.
What is a relational database?
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. Unlike in hierarchical databases, however, a table in a relational database can have multiple parents.
How many clustered indexes can you have for a database? a. 1 b. 2 c. 4 d. 8
A. 1
Which clause returns only the matching rows from the two joined tables? A. INNER JOIN B. FULL OUTER JOIN C. CROSS JOIN D. RIGHT OUTER JOIN
A. INNER JOIN
What do you call when a data type is converted automatically to another data type? a. implicit conversion b. explicit conversion c. dynamic conversion d. static conversion
A. Implicit conversion
Which of the following is not a DML statement? a. REMOVE b. INSERT c. DELETE d. SELECT
A. REMOVE
Which of the following SQL statements is used to extract data from a database? a. SELECT b. OPEN c. EXTRACT d. GET
A. SELECT
You wish to add a column to the Shirt table that has the following characteristics: - Column name is Price - Data type is a monetary value that takes up 8 bytes or storage - Column cannot be null What sequence of commands would you use?
ALTER TABLE Shirts ADD Price Money
What is the function of the INSERT statement?
Adds one or more new rows to a table or a view in SQL Server.
_____ numeric data types are not used as often as other SQL Server data types.
Approximate
The correct syntax for specifying the decimal or numeric data type is: A. decimal[(s[,p])] numeric[(s[,p])] B. decimal[(p[,s])] numeric[(p[,s])]
B
Which of the following range conditions would generate a syntax error? a. salary <= 50000 and salary >= 10000 b. salary between (10000 and 50000) and (60000 and 90000) c. salary >= 10000 and salary <= 50000 d. salary between 10000 and 50000
B
Which type of index defines the physical sort order for database data? A. Nonclustered index B. Clustered index C. Full-text index D. Spatial index
B. Clustered index
Which of the following is not a DDL statement? a. CREATE b. MERGE c. ALTER d. DROP
B. MERGE
Do implicit conversions require an actual callout feature (i.e., cast or convert)? a. Yes b. No
B. No Explicit conversions require a callout using CAST or CONVERT
Which statement is used to suppress the '(1 row(s) affected)' after executing query statements? a. SET NO COUNT b. SET NOCOUNT ON c. SET NO COUNTING d. SET NO COUNTING ON
B. SET NOCOUNT ON
Which of the following conditions is invalid? a. salary <> 50000 b. salary != 50000 c. salary NOT EQUAL 50000 d. NOT salary = 50000
C
Which of the following is not a column constraint? a. Default b. Check c. Range d. Unique
C. Range
Which is not a regular data type? a. CHAR b. NCHAR c. TEXT d. VARCHAR
C. TEXT The text data type is deprecated.
You just hired a new database administrator and you want to give her full access to your SQL server. What role should you assign? a. diskadmin b. securityAdmin c. sysadmin d. db_owner
C. sysadmin
Which of the following is not true about the int data type? a. int is an integer. b. bigint is used when the value will exceed int data type's range. c. An Integer uses 8 bytes to store data. d. Functions will return bigint only if the original expression has the same data type.
C., the int data type uses 4 bytes to store data. Other integer types use various storage.
What is the function of the CREATE statement?
Creates a SQL Server database object (table, view, or stored procedure).
Which of the following operators is not supported when combining results between SELECT statements? a. UNION b. EXCEPT c. INTERSECT d. AND
D
Which of the following statements is not true with regard to foreign keys? a. A foreign key is a combination of one or more columns used to establish and enforce a link between the data in two tables. b. You can create a foreign key by defining a foreign key constraint when you create or alter a table. c. A foreign key enforces referential integrity by ensuring only valid data is stored. d. A table can contain only one foreign key.
D. A table can contain only one foreign key
What are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered? a. Primary key b. index c. Foreign key d. constraint
D. Constraint
Which of the following is not a type of backup supported by SQL Server? a. Full b. Differential c. File d. Device
D. Device
What mode allows both Windows and SQL account logins? a. Any b. Full c. Shared d. Mixed
D. Mixed
What data type can store between one and five characters of alphanumeric data and supports multiple languages with minimal storage space requirements? A. VARCHAR(5) B. VARCHAR(1,5) C. CHAR(5) D. NVARCHAR (5)
D. NVARCHAR (5)
Consider using a clustered index when: a. Columns contain a large number of distinct values b. Columns are accessed sequentially c. Columns undergo frequent changes d. Queries return large result sets
D. Queries return large result sets
Which of the following is not a level of security supported by SQL Server? a. Server b. Database c. Table d. Task
D. Task
Which statement should you use to modify a column value in a table? A. ALTER B. MODIFY C. INSERT D. UPDATE E. CREATE
D. UPDATE
You need to remove all rows in the Employee table where the employee is a member of the Accounting Department. What command would you use?
DELETE FROM Employee WHERE Department = 'Accounting'
What is Data Definition Language (DDL)?
Data Definition Language (DDL) is a subset of the Transact-SQL language; it deals with creating database objects like tables, constraints, and stored procedures.
A database that includes a table that contains repeating columns is said to be normalized. Choose whether the statement is correct as stated or choose the text that should replace the underlined text to make the statement correct.
Denormalized A database that includes a table that contains repeating columns is said to be denormalized. When you normalize a database, you must first normalize to the first normal form (1NF) by eliminating all repeating columns.
What are three advantages of normalization?
Development costs, usability, extensibility
_____ only back up data since the last full backup.
Differential backups
The _____ clause gives you the final result set where data exists in the first query and not in the second dataset.
EXCEPT
Both the _____ and the _____ statements are designed to return distinct values by comparing the results of two queries.
EXCEPT and INTERSECT
True or false: A clustered index usually improves performance when inserting data
False
True or false: Tables in a database must satisfy all five normal forms in order to maximize performance.
False
True or false: A primary key can contain NULL values.
False. By definition, a primary key must be unique and must have a value that is not null.
True or false: Creating a primary key satisfies the first normal form.
False. In the first normalized form (1NF), the data is in an entity format, which basically means that the following three conditions must be met: the table must have no duplicate records, the table must not have multi-valued attributes, and the entries in the column or attribute must be of the same data type.
What are the three main types of databases?
Flat-type or flat-file databases, hierarchical databases, and relational databases.
The Planets table has columns for Name and Diameter. You need to add data into the table for Earth, using 1000 as the diameter. What command would you use?
INSERT INTO Planets (Name, Diameter) VALUES ('Earth', 10000)
What are the core DML statements?
INSERT, SELECT, UPDATE, DELETE, MERGE
_____ are essential mechanisms that are used to prevent simultaneous changes to the database, such as two different users making changes to the same record.
Locks
The smallmoney data type is
Monetary or currency values from -214,748.3648 to 214,748.3647.
In order to represent the phrase "Hello world" as a Unicode character literal you would use
N'Hello World'
The varchar data type is
Non-Unicode character data type with variable length.
If the _____ is broken, meaning the owners of one object and the next lower object are different, then SQL Server checks the user's permission for every object accessed.
Ownership chain
In SQL Server databases, users often access data by going through one or several objects. _____ apply to views, stored procedures, and user-defined functions.
Ownership chains
What is the function of the SELECT statement?
Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server
The SQL command for retrieving any data from a database is _____.
SELECT
What commands would you type and execute to return rows where the word bike appears somewhere in the Name column from the ProductCategory table?
SELECT * FROM ProductCategory WHERE Name = 'bike'
What commands would you type and execute to return only the ProductSubcategoryID, ProductCategoryID, Name, and ModifiedDate columns from the ProductCategory table?
SELECT ProductSubcategoryID, ProductCategoryID, Name, ModifiedDate FROM ProductCategory
A _____ is an attack in which malicious code is inserted into strings that are later passed on to instances of SQL Server waiting for parsing and execution.
SQL injection
This server role can manage the logins for the server.
Securityadmin
This object permission grants the right to select data. This permission can be applied to specific columns
Select
This server role can configure the server-wide settings, including setting up full-text searches and shutting down the server.
Serveradmin
What are two basic rules when combining the results of two queries via the UNION, EXCEPT, and INTERSECT clauses?
The number and order of the columns must be the same in each of the queries in the clause. The data types you use must be compatible
Under the full or bulk-logged recovery model, since the logs record each transaction, restoring from log backups is required to reach a desired recovery point.
Transaction log restore
The _____ clause allows you to combine the results of two or more queries into a resulting single set that includes all the rows belonging to the query.
UNION
The _______________clause allows you to combine the results of any two or more queries into a resulting single set that will include all the rows belonging to the query.
UNION
To combine the results of two queries, use the ____________ operator.
UNION
_____ mode is superior to mixed mode because users don't need to learn yet another password and because it leverages the security design of the network.
Windows authentication
_____ mode is superior to _____ mode because users need not learn yet another password and because this mode leverages the security design of the network.
Windows authentication; mixed
Which of the following columns do you need to set a value for when issuing an INSERT statement?
You need to set a value for a column that stores values of the uniqueidentifier data type. When you do, you need to use the NEWID() function in the VALUES list to generate the value.
SQL Server uses Windows __________ to verify that a user is valid before access is allowed.
authentication mode
_____ is intended for use when integer values will exceed the int data type's range of support.
bigint
The purpose of a database _____ is to have something to restore if data is lost during a business's daily routine.
backup
At restore time, full backups are restored _____ the most recent differential backup.
before
The ALTER statement
changes an existing object; you can use it to add or remove columns from a table
In some tables, there might be multiple possible primary keys to choose from, such as employee number, driver's license number, or another government-issued number such as a Social Security number (SSN). In this case, all the potential primary keys are known as _____.
candidate keys
The purpose of a _____ is to have something to restore if data is lost during a business's daily routine.
database backup
The scope of a backup of _____ can be a whole database, a partial database, or a set of files or filegroups.
data backup
This database role authorizes a user to access the database, but not to manage database-level security.
db_accessadmin
This database role allows a user to perform backups, checkpoints, and DBCC commands, but not restores. (Only server sysadmins can perform restores.)
db_backupoperator
You wish to call a stored procedure named usp_displayallusers. What command would you use?
exec usp_displayallusers
Users must be _____ granted access to any user database.
explicitly
A permission is used to grant an _____ access to an _____.
entity; object
When two _____ have different data types, rules for data-type precedence specify that the data type with lower precedence is converted to the data type with higher precedence.
expressions (mathematical functions or comparison functions)
The _____ normalized form means the data is in an entity format.
first
_____ only back up data since the last full or incremental backup.
incremental backups
An _____ is an on-disk (or stored) structure associated entirely with a table or a view that increases the speed of data retrieval.
index
The SELECT statement is used to query and combine data from one or more ____________.
tables
A _____ is a single unit of work.
transaction
After the first data backup, under the full recovery model or bulk-logged recovery model, regular _____ are required.
transaction log backups
True or False: The INSERT statement can be used to insert multiple rows at a time.
true
Because you cannot modify the permissions assigned to a fixed server role, you may need to grant individual server permissions to a user that are not defined by a fixed server role. Such _____ roles are typically employed for users who need to perform specific database functions but to whom you don't want to grant a role that would permit them do more than what they need to.
user-defined
A bit is a Transact-SQL integer data type that can take a ____________ of 1, 0, or NULL.
value
SQL Server offers three recovery models. They are:
• Simple Recovery • Full Recovery • Bulk-Logged
The central phrase to remember in describing the third normal form is that every attribute must
"provide a fact about the key, the whole key, and nothing but the key."
When using the decimal or numeric data type, the scale value must be a value from _ to _, but it can be specified only if precision is also specified. The default scale is _.
0; p; 0
The bit data type takes up _ byte(s) of storage.
1
The tinyint data type takes up _ byte(s) of storage
1
The smallint data type takes up _ byte(s) of storage
2
You need to log data about each record deleted from the Members table in the FormerMembers table. What should you create?
A DELETE trigger A DELETE trigger occurs after a record is deleted. In this case, the trigger would use the columns in the deleted table to add a row to the FormerMembers table. The deleted table is created automatically and is only available within a trigger that fires in response to a DELETE statement or an UPDATE statement.
Which of the following is used to prevent accidental deletion of data in a table? a. Transactions b. Null values c. Inner joins d. Referential integrity
A and D
What is one difference between an application role and a database role?
A database role is assigned to users and groups. Permissions can be defined and granted to the role and then all role members are allowed those permissions. Application roles allow access to database objects, but only through an application. Application roles are not assigned to users. Instead, a single login is associated with an application. A system stored procedure is called when the application is launched that authenticates the application. Application users can then access the database through the application, but not directly.
What is a default constraint?
A default constraint is used to insert a default value into a column. If no other value is specified, the default value will be added to all new records.
What is a not null constraint?
A not null constraint ensures that data is entered into a cell. In other words, the cell cannot be blank. It also means that you cannot insert a new record or update a record without adding a value to this field.
What is a query?
A query is an inquiry into the database that returns information back from the database
What is represented by a column in a well-designed relational database table?
A single entity attribute. In a well-designed relational database table, a column represents a single entity attribute. The entity is represented with a table, and its attributes are represented by columns within the table. For example, a customer entity would be represented by a table, and the customer's attributes, such as the customer's name or address, would be represented by columns within the table.
When you define a primary key, you have met the first criteria for: a. 1NF b. 2NF c. 3NF d. 4NF
A. 1NF
Which statement is a data definition language (DDL) statement? A. ALTER B. UPDATE C. DELETE D. INSERT
A. ALTER
What data type can store characters of alphanumeric data and supports multiple languages with minimal storage space requirements?
An NVARCHAR data type can store alphanumeric data and supports multiple languages, but requires minimal storage. An NVARCHAR data type stores Unicode strings of different lengths. Unicode strings should be used to provide multilingual support. The storage required for an NVARCHAR data type is the maximum number of bytes to be stored multiplied by two.
The best method of data recovery is to: a. backup, backup, backup b. use RAID c. use UPS d. use redundant NICs
A. Backup, backup, backup
What is the name for the situation in which more than one columns act as a primary key? a. Composite primary key b. Escalating key c. Foreign key d. Constraint key
A. Composite primary key
Which of the following is not a database permission that can be applied to objects? a. DROP b. SELECT c. INSERT d. UPDATE
A. DROP
Which database object should you create to optimize how the underlying data is physically accessed by queries?
An index. An index is a database object that is used to optimize how the underlying data is physically accessed by queries. Each table can include one clustered index that represents the order of the data and multiple nonclustered indexes that use pointers to locate the desired data. Because the entire table does not have to be scanned, query performance can be improved.
At a minimum, which clause or clauses are required in a SELECT statement that returns column values from a table?
At a minimum, only the FROM clause is required in a SELECT statement. If no other clauses are specified, all rows in the table specified in the FROM clause are returned. You can omit the FROM clause, but only if the select list does not contain any column names.
A table with a clustered index is considered a _____; when a table has no clustered index, the data rows are then stored in an unordered structure called a _____.
clustered table; heap
You have a relational database that stores information about projects within your organization. You have a Project table and a ProjectManager table as shown in the exhibit. Why would you issue a DML statement? A. To add a new project when a new project is launched B. To add a new column to the Project table to track each project's sponsor C. To create a view that can be user to query the project data D. To add a new table to store the tasks associated with each project
A. To add a new project when a new project is launched
What commands would you type to change the data type to DATETIME2 on the ModifiedDate column in the ProductCategory table?
ALTER TABLE ProductCategory ALTER COLUMN Modified Date DATETIME2
You wish to redefine the existing Size view to include the Price column. The view criteria should include: - Results gathered from the Shirt table - ProductID column - ProductName column - Price column - Return only results where the ProductType is 'Size' What sequence of commands would you use?
ALTER VIEW Size AS SELECT ProductID, ProductName, Price, FROM Shirts WHERE ProductType = 'Size'
Which DDL statement should you use to permanently remove a column and all of the column's data from a table? A. DELETE B. ALTER TABLE C. UPDATE D. DROP TABLE
B. ALTER TABLE
Which of the following could not be used as a primary key? a. A Social Security number b. An address c. An employee number d. The serial number of an electronic component
B. An address
Which statement is used to back up the transaction log? A. BACKUP LOG WITH DIFFERENTIAL B. BACKUP LOG C. BACKUP DATABASE D. BACKUP DATABASE WITH DIFFERENTIAL
B. BACKUP LOG
The least used recovery model used is _____. It is a compromise between the other two models. It allows good performance while using the least log space. However, you cannot do a point-in-time recovery.
Bulk-logged
This server role can perform bulk insert operations.
Bulkadmin
A _____ is an attribute or set of attributes that can be used to uniquely identify each row.
primary key
A database that includes a table that contains repeating columns is said to be *normalized.* Choose whether the statement is correct as stated or choose the text that should replace the underlined text to make the statement correct. A. fragmented B. denormalized C. The statement is correct and does not need any changes D. defragmented
B. denormalized
What is a built-in SQL account used in mixed mode that has full access to the SQL server? a. fulladmin b. sa c. admin d. administrator
B. sa
To create a SQL server login for a Windows account named "Bill" on the "Microsoft" domain, what command would you use?
CREATE LOGIN 'Microsoft\Bill'
By definition, a _____ must be unique and must have a value that is not null
primary key
Which of the following statements are true? a. A greater number of narrow tables (with fewer columns) is a characteristic of a normalized database. b. A few wide tables (with more columns) are characteristic of a normalized database. c. Indexes allow faster data retrieval. d. Optimal database performance can be achieved by indexing every column in a table.
C. Indexes allow faster data retrieval
Which of the following is not a Transact-SQL command to manage permissions? a. GRANT b. REVOKE c. PERMIT d. DENY
C. PERMIT
In order to maintain data integrity, the _____ must form part of the design requirements for each table
primary key
The syntax of the convert function is:
CONVERT ( data_type [ ( length ) ], expression [,style ] ) ex. CONVERT(nvarchar(10), OrderDate, 101) This will convert the OrderDate, which is a DateTime data type to nvarchar value. The 101 style represents USA date with century. mm/dd/yyyy
You have a table called PlanetsID in the AdventureWorks2008 database. Assuming that the data in your table meets the necessary criteria, how would you create a clustered index named IX_PlanetsID_ID based on the ID column?
CREATE CLUSTERED INDEX IX_PlanetsID_ID ON dbo.PlanetsID (ID)
You would like to create a database role named Auditors that is owned by the db_securityadmin role. What command would you use?
CREATE ROLE 'Auditors' AUTHORIZATION db_securityadmin
You need to create a new table called Planets that contains the following columns: Name, consisting of up to 50 non-Unicode characters and Diameter, consisting of up to 50 non-Unicode characters What command would you use?
CREATE TABLE Planets (name varchar(50), diameter varchar(50))
_____ permission overrides a granted permission.
Denying
A backup that contains only the data that were modified since the most recent partial backup of the same set of filegroups is a _____.
Differential partial backup
You have created a Employee table that contains an EmpID column as its primary key defined with the following column definition: EmpID int IDENTITY(1,1) PRIMARY KEY You need to create a foreign key with the same column name in the SalaryHistory table that references the EmpID in the Employee table. What must you ensure? A. The SalaryHistory table does not contain any rows. B. The EmpID column in the SalaryHistory table has a check constraint. C. The EmpID column in the SalaryHistory table has the IDENTITY property D. The two EmpID columns have a compatible data type
D. The two EmpID columns have a compatible data type
What does DML stand for?
Data Manipulation Language
You have a database that uses three read/write filegroups. The database is configured to use the Full recovery model. You are planning the backup strategy for the database. You need to meet the following requirements: * Disk space required to store the backups should be minimized * The number of transaction log backups that need to be replayed during restoration should be minimized What should your backup plan include?
Database backups, differential backups, and transaction log backups. A database backup backs up the entire database. This backup can serve as a base for one or more differential backups. Differential backups back up only the data that has changed since the base backup. Differential backups can reduce the number of transaction logs that must be replayed during restoration because only the transaction log backups taken since the last differential backup need to be replayed. Your plan also needs to include transaction log backups because the recovery model is set to Full. If transaction log backups are not taken, the transaction log will never be truncated and will eventually reach its maximum size. If the transaction log is full, no data modification operations can occur.
This server role can create, alter, drop, and restore databases.
Dbcreator
The datetimeoffset data type
Defines a date that is combined with a time of day that has time-zone awareness and is based on a 24-hour clock. Range: 0001-01-01 through 9999-12-31 (January 1, 1 AD, through December 31, 9999). Range: 00:00:00 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
The smalldatetime data type
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.
The date data type
Defines a date. Range: 0001-01-01 through 9999-12-31. (January 1, 1 AD, through December 31, 9999). Accuracy: one day.
The time data type
Defines a time of day. This time is without time-zone awareness and is based on a 24-hour clock. Range: 00:00:00.0000000 through 23:59:59.9999999. Accuracy: 100 nanoseconds.
This object permission grants the right to delete existing data.
Delete
You wish to add the user "Bill" to the Manager role using a stored procedure. What command would you use?
EXEC sp_addRoleMember 'Manager', 'Bill'
You wish to add the user Microsoft\Bill to the diskadmin role. What command would you use?
EXEC sp_addsrvrolemember 'Microsoft\Bill', 'diskadmin'
What command would you use to create the guest user account after creating a database?
EXEC sp_adduser 'Guest'
You wish to remove the user "Bill" from the Manager role using a stored procedure. What command would you use?
EXEC sp_dropRoleMember 'Manager', 'Bill'
A full backup of all the data in one or more files or filegroups is a _____.
File backup
The _____ gives you the final result set where values in both of the queries match by the query on both the left and right sides of the operand.
INTERSECT
What are secondary data files?
If the database becomes too large and you run out of room on your first hard disk, you can create secondary data files, which have an .ndf extension, on separate physical hard disks.
You wish to restore the Planets database. The file is located on the E:\ and is called "Planets.bak". What commands would you use?
RESTORE DATABASE Planets FROM DISK = 'E:\Planets.bak'
If your environment prohibits mixed-mode security, then the easiest way to check security is to right-click SQL Server Management Studio or Query Analyzer and use the _____ command to run as a different user
RUN AS
_____ a permission removes the permission that has been assigned, regardless of whether it was a denied permission or a granted permission.
Revoking
What commands would you type and execute to return rows where the word bike appears somewhere in the Name column from the ProductCategory table and sort the results in ascending order according to the Name column value?
SELECT * FROM ProductCategory WHERE Name = 'bike' ORDER BY Name ASC
What does SQL stand for?
Structured Query Language
The Orders table has a column named OrderDate. It currently has a default value of today's date. You need to modify the configuration so that there is no default value. Which statement should you use?
The ALTER TABLE statement. The ALTER TABLE statement can be used to change the structure of the table, including adding or removing constraints from a column. A default constraint is used to define a default value for a column.
Which statement should you use to add a column named SubCategory to the Products table?
The ALTER TABLE statement. The ALTER TABLE statement can be used to modify the structure of a table, including adding a column or constraint.
You wish to add a price of $13.50 into the Price column of the Shirt table for the row that contains a ProductID of 7. What command would you use?
UPDATE Shirt SET Price = 13.50 WHERE ProductID = 7
Which of the following are required clauses when issuing an UPDATE statement?
The SET clause is a required clause. You specify the columns or variables that should be updated and, when a FROM clause is not also used, their values.
What is the disadvantage of indexes?
The disadvantage of indexes is that they need to be created and updated, which requires processing resources and takes up disk space.
What is a primary key constraint?
The 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
You need to create a new table named Planets within a database named Adventureworks with the following columns: IndividualID - cannot be null and must contain a numerical value that takes up 4 bytes of space PlanetName - can be null and can contain variable-length, non-Unicode string data of up to 50 characters PlanetType - can be null and can contain variable-length, non-Unicode string data of up to 50 characters Radius - can be null and can contain variable-length, non-Unicode string data of up to 50 characters TimeCreated - can be null and can contain a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. What sequence of commands would you use?
USE AdventureWorks CREATE TABLE dbo.Planets ( IndividualID INT NOT NULL, PlanetName VARCHAR(50) NULL, PlanetType VARCHAR(50) NULL, Radius VARCHAR(50) NULL, TimeCreater DATETIME NULL )
You have been hired as a new database administrator and told to create a customer database. After opening SSMS and accessing the AdventureWorks database, what query command would you use to create a table called customers.customer with the following columns or fields? Unique Customer ID CompanyID—Up to 50 characters FirstName—Up to 50 characters LastName—Up to 50 characters ModifiedDate
USE AdventureWorks CREATE TABLE customers.customer ( CustomerID INT IDENTITY(1,1) NOT NULL UNIQUE, CompanyID VARCHAR(50), FirstName VARCHAR(50), LastName VARCHAR(50), ModifiedDate DATETIME NOT NULL DEFAULT GETDATE() )
To create a SQL server login for a Windows account named "Bill" on the "Microsoft" domain with a database username of 'Gates' and grant the account access to the Windows database what command would you use?
USE Windows CREATE LOGIN 'Microsoft\Bill', 'Gates'
What are the core DDL statements?
USE, CREATE, ALTER, DROP, TRUNCATE
This object permission grants the right to modify existing data. _____ rights for which a WHERE clause is used require select rights as well. _____ permission can be set on specific columns.
Update
When you use a _____ element, SQL Server will preserve space in the row in which that element resides based on the column's defined size (and not on the actual number of characters in the character string itself), plus an extra two bytes of data for offset data.
VAR
In SSMS, the Query Analyzer supports
XQuery, SQLCMD, and T-SQL
You are creating a relational database that will be used to track car rentals. Each renter can rent multiple cars. A car can be rented by multiple drivers. You need to reduce the amount of redundant data. How should you store the car rental data?
You should create a Cars table, a Renters table, and a CarRentals table. The scenario involves a many-to-many relationship between two entities: Renters and Cars. When you need to model a many-to-many relationship in a relational database, you should create a junction table that contains foreign key relationships with the primary key in the tables that contain the detailed information for the two entities. The detailed information about each car is stored in the Cars table. The detailed information about each renter is stored in the Renters table. The CarRentals table references the entities involved in a specific rental.
You have a database table named TransactionHistory that contains millions of rows. The table has a primary key named TrxID. Users frequently use a query that includes the region and status in the WHERE clause condition. You need to optimize performance of this query. What should you do?
You should create a composite nonclustered index on region and status. A nonclustered index on columns frequently used in a WHERE clause can improve query performance. A nonclustered index can be created on an individual column or multiple columns. An index created on multiple columns is known as a composite index.
When would you use a .WRITE clause within an UPDATE statement?
You use a .WRITE clause in an UPDATE statement when you need to update the values of columns that have a large object data type, such as varchar(max) or varbinary(max) columns.
It should also be mentioned that a _____ is a physical construct, unlike most indexes, which are logical or software-based.
clustered index
The bit data type is
an integer with either a 1 or 0 value. (Columns of 9 to 16 bits are stored as 1 byte 2 bytes, and storage size continues to increase as the number of bits in a column increases.)
True or False: An INNER JOIN condition is the same as a CROSS JOIN and a WHERE condition.
false
True or False: The SELECT statement is limited to querying data from a single table.
false
True or False: The TRUNCATE statement is used to delete a table.
false
True or False: The order of the columns in a SELECT statement must match the order in which the columns were created when the table was made.
false
The _____ normal form provides a method for designing complex relationships involving multiple (usually three or more) entities.
fifth
There are _____ normalization forms.
five
The data set char is of ____________ length and has a length of ____________ bytes.
fixed; 1
When you restore from an incremental backup, you must first restore the preceding _____ and then restore each _____ since the full backup in order.
full backup; incremental backup
When a foreign key constraint references columns in the same table, this is known as
self-reference As an example of a self-reference table, say you want to create a Generations table that contains names of people using columns named PersonID, PersonName, and MotherID. The mother is also a person stored in the Generations table, so you can create a foreign key relationship from the MotherID (the foreign key column) referencing PersonID (the primary key column).
The ____________ data type 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.
smalldatetime
Which data type can store a whole number between -200 and 200 with the least amount of storage?
smallint The smallint data type can store a whole number between -200 and 200 with the least amount of storage. A column of the smallint data type can be used to store values between -32,768 and 32,767. It requires 2 bytes of storage capacity.
Values in a clustered index are ____________.
sorted
Normalization is the elimination of redundant data to save ____________.
space