Database Management Systems 2 - Final/Midterm Study Guide

To return all of the columns from the base table, you can code the ___________ operator in the SELECT clause.

*

To concatenate character strings, you use the _____________ operator in a string expression.

+

The order of precedence for the logical operators in a WHERE clause is

Not, And, Or

Which ORDER BY clause will cause 10 rows to be retrieved from the result set, starting with the 20th row?

ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

The users, groups, logins, and roles that have access to a sever are called.

Principal

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest BalanceDue values? Assume the full query contains this clause: ORDER BY BalanceDue

TOP 10 BalanceDue

To log on to SQL Server using your SQL Server login ID, you use _________________ authentication.

SQL Server

When you set a database compatibility level, you make the database compatible with a specific version of

SQL Server

The Query Editor of the Management Studio lets you enter and execute all types of

SQL statements

If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use a/an _______________ join.

left outer

The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and a/an ________________ file.

log transaction

If two tables have a many-to-many relationship, you need to define a/an __________________ table that relates their records.

lookup/linking

The ________________________________________ is a graphical user interface for working with the objects in a SQL Server database.

management studio

To allow users to log on using either type of authentication, you need to set the SQL Server authentication mode to __________ mode.

mixed

The three main hardware components of a client/server system are the client, the server, and the __________________________________.

network

Unless you assign a/an _____________________________, the column name in the result set is the same as the column name in the base table.

alias

You can create a database diagram for

any combination of the tables in a database

Unless you specify otherwise, the CREATE INDEX statement creates a/an ___________ index for the specified column or columns.

nonclustered

To normalize a data structure, you apply the ____________ in sequence.

normal form

In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.

foreign

To relate one table to another, a/an __________________ in one table is used to point to the primary key in another table.

foreign key

If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.

full outer

If you want to join all of the rows in two tables whether or not they are matched, you use a/an ____________________ join.

full outer

When you code a column list in the INTO clause of an INSERT statement, you can't include a/an _________ column.

identity

When you code a union with the INTERSECT keyword to combine two result sets, the union

includes only rows that occur in both result sets

Consider the following code example: SELECTVendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID; This type of join is called a/an __________________ join.

inner

If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use a/an __________ join.

inner

SELECTVendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorlD = I.VendorlD; This type of join is called a/an __________ join.

inner

The ___________ data types are used to store whole numbers.

integer

When you code a column list in an INSERT statement, you can omit columns with default values and columns that are _________ values.

null

You use DDL to create, modify, and delete the ________ of a database.

objects

The most common type of relationship between two tables in called a/an __________________ relationship.

one-to-many

The most common type of relationship between two tables is a/an ___________________ relationship.

one-to-many

To override the order of precedence in an arithmetic expression, you can use __________________.

parentheses

The ___________ of a decimal value indicates the total number of digits that can be stored in a decimal data type.

precision

A/An __________________ uniquely identifies each row in a table.

primary key

To retrieve or update the data in a database, the client sends a/an ________________ to the database.

query

A constraint that enforces referential integrity between tables is called a/an ___________ constraint.

reference

A union combines the rows from two or more ____________________.

result sets

A union combines the rows from two or more _______________________.

result sets

Which one of the following statements is true of a right outer join?

right outer joins can be converted to left outer joins

A full outer join includes rows that satisfy the join condition, plus

rows in both tables that don't satisfy the join condition

In a cross join, all of the rows in the first table are joined with all of the

rows in the second table

The entities that can be secured on a server are called _____________.

securables

The highest level at which you can grant permissions is the __________ level.

server

To insert several rows selected from another table into a table, you can code an INSERT statement with a/an _________ in place of the VALUES clause.

subquery

To model a database on a real-world system, you typically represent each real-world entity as a/an _________.

table

A relational database consists of one or more ___________.

tables

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added to the table

Typically, most database designers consider a database structure normalized if it is in the ____________ normal form.

third

When a column in a table is defined, it is given a data _________ that determines what kind of data it can store.

type

The MERGE statement is sometimes referred to as the _________ statement.

upsert

When you use Windows authentication to connect to a database, SQL Server

uses the login name and password that you use for your PC to authorize your connection

In a join, column names need to be qualified only

when the same names are used in both tables

If you assign a correlation name to one table in a join,

you have to use that name for the table

When used with the LIKE operation, the ____ symbol matches any string of zero or more characters.

%

To modify the structure of an existing table, you use the ___________ statement.

ALTER

When used in a WHERE clause, the AND logical operator must satisfy ____ conditions to be true.

All

Which of the following is not a valid column alias name?

Balance Due

Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function:

CAST(InvoiceAmount AS varchar)

To create a user-defined database role, you use the ____________ statement.

CREATE ROLE

The result set returned by a cross join is known as a __________________.

Cartesian Product

The intersection of a row and a column is commonly called a/an _______________.

Cell

___________ names can be used when you want to assign a temporary name to a table.

Correlation

___________________ names can be used when you want to assign a temporary name to a table.

Correlation

Which of the following types of SQL statements is not a DML statement?

CreateTable

To create a new table by using a SELECT statement, you code the _________ clause.

INTO

SQL statements that define the tables in a database are referred to as _______________ statements.

DDL

If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the _________ keyword in the VALUES clause of the INSERT statement.

DEFAULT

Write the code for a DELETE statement that deletes every row in the Vendors table:

DELETE Vendors

To sort the records that are retrieved by a SELECT statement in descending sequence by InvoiceTotal, you code _________ after ORDER BY InvoiceTotal.

DESC

To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.

DISTINCT

SELECTVendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorlD = I.VendorlD; The column name for the second column in the result set will be __________.

Date

In a SQL query, character comparisons are case-sensitive.

FALSE

The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.

FROM

LEFT() and GETDATE() are examples of SQL ____ which perform an operation, return a value, and may require one or more parameters.

Functions

Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?

InvoiceTotal - CreditTotal - PaymentTotal / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

InvoiceTotal IN (0,1000)

What can you use to combine data from two or more tables into a single result set?

Join

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.

LIKE

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

When you code a union that combines two result sets, which of the following is not true?

The result sets must be derived from different tables.

A ______________________is a type of nonclustered index that includes a WHERE clause.

filtered index

The SQL dialect that Microsoft SQL Server uses is called _______________.

Transact-SQL

___________ characters can be used to encode the characters that are used in languages throughout the world.

Unicode

If you want to filter the result set or limit the rows that are returned by a SELECT statement, you must include a/an ____ clause.

WHERE

If you want to filter the result set that's returned by a SELECT statement, you must include a/an ___________________ clause.

WHERE

When you code a DELETE statement for one or more rows, the _________ clause specifies which row or rows are to be deleted.

WHERE

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the ________ clause specifies which row or rows are to be updated.

WHERE

Which of the following comments is invalid?

WHERE Total > 100 # values greater than 100

To log on to SQL Server using your Windows login ID, you use ____________________ authentication.

Windows

Each of the following passwords is a strong password except for one. Which one is it? a) a247n41s874 b) 23Flt41Xt c) F23$G45$T86 d) cty%123%fast

a) a247n41s874

Insert, Update, and Delete statements can be referred to as ______________ queries.

action

Which of the following statements best describes what this INSERT statement does?INSERT INTO InvoiceArchiveSELECT *FROM InvoicesWHERE TermsID = 1; a) Adds one row from the Invoices table to the InvoiceArchive table. b) Adds all of the rows in the Invoices table that have 1 in the TermslD column to the InvoiceArchive table. c) Adds all of the rows in the Invoices table to the InVoiceArchive table and sets the TermslD column to 1 in each row. d) Updates all of the rows in the InvoiceArchive table that have 1 in the TermslD column to the rows in the Invoices table.

b) Adds all of the rows in the Invoices table that have 1 in the TermslD column to the InvoiceArchive table.

Which of the following is not a good guideline for deciding when to create an index for a column? a) The column is a foreign key. b) The column is frequently updated. c) The column is frequently used in search conditions or joins. d) The column contains a large number of unique values.

b) The column is frequently updated.

When you identify the data elements in a new database, you typically subdivide data elements into a) The largest practical components b) The smallest practical components c) Components that can be easily parsed you time you use them

b) The smallest practical components

To store a date value without storing a time value, you can use the a) time data type b) date data type c) datetime data type d) datetime2 data type

b) date data type

A full outer join includes rows that satisfy the join condition, plus a) rows in the left table that don't satisfy the join condition b) rows in both tables that don't satisfy the join condition c) rows in the right table that don't satisfy the join condition d) the Cartesian product of the two tables

b) rows in both tables that don't satisfy the join condition

If you assign a correlation name to one table in a join, a) you have to assign correlation names to all of the tables b) you have to refer to the table by the correlation name c) you have to qualify every column name in the query d) you have to qualify all of the column names for that table

b) you have to refer to the table by the correlation name

The processing that's done by the DBMS is typically referred to as

back-end processing

Which uses the least amount of storage? a) 'example' stored in a column of type varchar(20) b) 'exam' stored in a column of type varchar(20) c) 'ex' stored in a column of type varchar(20) d) they all use the same amount of storage

c) 'ex' stored in a column of type varchar(20)

To be in the first normal form, each cell in a table must contain a) a unique value b) a non-unique value c) a single, scalar value d) a non-redundant value

c) a single, scalar value

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This a) reduces redundancy but makes maintenance more difficult b) reduces redundancy and makes maintenance easier c) increases redundancy but makes maintenance easier d) increases redundancy but makes the data more consistent

c) increases redundancy but makes maintenance easier

A constraint that limits the values that can be stored in a column is called a/an ___________ constraint.

check

The rows in a table are kept in the sequence that is based on its ____________ index.

clustered

When you code a table-level check constraint, the constraint can refer to data in more than one ___________.

column

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by a/an _________.

comma

When you use the CREATE TABLE statement to create a table, you can also define the attributes and ________________ for the columns of the table.

constraints

The difference between the CAST function and the CONVERT function is that the ___________ function accepts an optional style argument that lets you specify a format for the result.

convert

SELECTVendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorlD = I.VendorlD; The name "V" is known as a/an ___________________.

correlation name

All of the following values can be stored in a column that's defined as decimal(6,2), except a) -246.29 b) 0 c) 2479.95 d) 32492.05

d) 32492.05

Which of the following does not violate the referential integrity of a database? a) Inserting a new row into a table with a foreign key that doesn't match a primary key in the related table b) Updating a foreign key with a value that doesn't match a primary key in the related table c) Updating a primary key in a primary key table without also updating the foreign keys for the related rows in all related tables d) Deleting a row in the foreign key table without deleting the related row in the related primary key table

d) Deleting a row in the foreign key table without deleting the related row in the related primary key table

Each of the following column attributes is a column constraint, except a) NOT NULL b) UNIQUE c) DEFAULT d) PRIMARY KEY

d) PRIMARY KEY

When you code a union that combines two result sets, which of the following is not true? a) Each result set must have the same number of columns. b) The result sets may or may not have any duplicate rows. c) The corresponding columns in the result sets must have compatible data types. d) The result sets must be derived from different tables.

d) The result sets must be derived from different tables.

To enforce referential integrity, the database can a) return an error instead of doing the requested action b) do the requested action and do the related changes to the related tables c) do the requested action and mark any orphans in related tables d) a or b e) a or c

d) a or b

To be in the third normal form, a) each non-key column must depend only on the primary key b) each non-key column must depend on the entire primary key c) the non-key columns must not contain repeating values d) all of the above

d) all of the above

In a cross join, all of the rows in the first table are joined with all of the a) distinct rows in the second table. b) matched rows in the second table c) unmatched rows in the second table d) rows in the second table

d) rows in the second table

Which uses the least amount of storage? a) 'example' stored in a column of type nchar(20) b) 'exam' stored in a column of type nchar(20) c) 'ex' stored in a column of type nchar(20) d) they all use the same amount of storage

d) they all use the same amount of storage

A string expression can consists of: a. One or more character columns b. One or more literal values c. A combination of character columns and literal values d. All of the above

d. All of the above

You can use the ______________________ data type to store a date that includes a time zone offset.

datetime offset

A database __________________ is a schematic drawing that shows you the relationships between the tables you're working with.

diagram

Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own _______________ of SQL.

dialect

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

equal