Advertisement Upgrade to remove ads

IST 272 Final Review

InvoiceTotal IN (0,1000)

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

Alias

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

correlation name (or table alias)

In the join shown here: SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID
The name V is known as a/an ____________________________________.

primary key

A/An __________________ uniquely identifies each row in a table

when the same names are used in both tables.

In a join, column names need to be qualified only _____________

Correlation

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

FROM

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

DISTINCT

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

DESC

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

I_Date

In the join shown here: SELECT VendorName AS Vendor, InvoiceDate AS I_Date
FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID
The column name for the second column in the result set will be __________________________.

inner

The join shown here: SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID
is called a/an __________________ join.

DDL

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

one-to-many

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

the number of rows in the Invoices table

In the join shown here: SELECT VendorName, InvoiceNumber
FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
The total number of rows returned by this query must equal_____________.

DML

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

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

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?

type

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

one or more tables

A relational database can contain ___________.

foreign

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

explicit (SQL-92)

The join shown here: SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID
is coded using the _____________________________ syntax.

T-SQL

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

action

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

WHERE

When you use the implicit syntax for coding joins, the join conditions are coded in the ____________ clause.

Uses the login names and password that you use for your PC to authorize the connection

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

equal

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

False

True or False
Because the American National Standards Institute publishes the specifications for a standard SQL language,
each DBMS vendor's version of SQL follows exactly the same syntax rules.

right outer joins can be converted to left outer joins

You don't ever need to code a right outer join because _____________.

Management Studio

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

LIKE

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

*

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

SELECT, FROM, WHERE, ORDER BY

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

full outer

If you want to retrieve all the rows from both tables involved in the join including all unmatched rows, you use a/an _______________ join.

WHERE

When you use the implicit syntax for coding joins, the join conditions are coded in the ____________ clause.

True

True or False
To override the order of precedence in an arithmetic expression, you can use parenthesis.

foreign key

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

True

True or False
By default, all duplicate values are included in the
aggregate calculation, unless you specify the DISTINCT keyword.

Subquery

A(n) ______________ is a query (select statement) that has another select statement coded within it.

WHERE

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

True

True or False
The aggregate functions are AVG, SUM, MIN, MAX, and COUNT

HAVING

_______________ is at the grouping level it works after the SELECT/WHERE/GROUPING and
limits the groups to include in the final answer delivered by the query.

True

True or False
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow null values.

True

True or False
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by a comma

WHERE

The ___________ is concerned with the original data. The HAVING is concerned with the data
appearing in the groups

False

True or False
When you use a SELECT INTO statement to create a table the definitions of primary keys,
foreign keys, indexes, and default values specifications are included in the new table.

INTO

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

scalar

A(n) ___________ function is a function that operates on a single value and returns a single value

True

True or False
When coding a query with two columns in the GROUP BY clause, you can insert a summary
row for each major group by coding the WITH ROLLUP operator

JOIN

In most cases, a subquery can be restated as a/an ______________.

SELECT, FROM WHERE, GROUP BY, HAVING, ORDER BY

The six clauses of the SELECT statement must be coded in the following order

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.

MIN(InvoiceDate)

_________________ is an aggregate expression to find the oldest date in the InvoiceDate column:

can use non-aggregate search conditions but can't use aggregate search conditions

Expressions coded in the WHERE clause

TRUE

True or False
Executing a DELETE without a WHERE clause will delete all the rows on the table.

MAX(VendorName)

___________________ is an aggregate expression to find the VendorName column value
that's last in alphabetical order:

INTO

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

Count(Distinct)

_______________ is an aggregate expression to count the number of unique values in the VendorID column:

True

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

True

True or False
To test whether one or more rows are returned by a subquery, you can use the EXISTS operator.

CTE

A _____________________ creates one or more temporary tables that can be used by the query that follows it

aggregate

A(n) ___________ function performs a calculation on the values in a set of selected rows.

DEFAULT

f 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.

Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table.

Which of the following statements best describes what this INSERT statement does?
INSERT INTO InvoiceArchive
SELECT *
FROM Invoices
WHERE TermsID = 1

Clustered

The rows in a table are kept in the sequence that's based on its __________________________ index.

Third

Typically, most database designers consider a database structure
normalized if it's in the ________________________ normal form

Denormalized

A(n) ____________________________ data structure is one that was deliberately put in a form that
is less than third normal form.

You need to create the referred to tables before you an create the tables that refer to them

When you create a script for creating a database,

True

True or False
If you perform a search using a date string that does not include the time, the date string
is converted implicitly to a date/time value with a zero time component

Column is frequently updated

Which of the following is not a good reason to consider creating an index for a column?

CONVERT

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

you move columns that don't depend on the entire primary key to
another table and establish a relationship between the two tables.

To apply the second normal form ________________________.

references or foreign key

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

GO

When you create a script for creating a database, you can use the _____________ keyword
to signal the end of a batch and cause all the statements in the batch to be executed

linking

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

filtered index

A(n) __________________ is a type of nonclustered index that includes a WHERE clause that
filters the number of rows that are included in the index

deleting a row in a foreign key table without deleting the related row in the related primary key table

Which of the following does not violate the referential integrity of a database?

True

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

check

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

COALESCE

The ___________ function provides one way to substitute constant values for null values.

DATEDIFF

The _____________ function returns the number of date/time boundaries crossed.

a single, scalar value

To be in the first normal form, each cell in a table must contain ______________________.

table

Involves searching through the entire table. SQL server has to perform a(n) _______ scan
to locate records if no index exists on the table.

Attaching a database

_____________________ is the process of making a database that was created on another server available to your server.

foreign key

A(n) _________________________ is used to relate to a primary key on a different table.

identity

A surrogate key is often defined to be a(n) _________________ column.

Unicode

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

True

True or False
To maintain referential integrity, if you delete a row in a primary key table,
you must also delete any related rows in foreign key tables.

nonclustered index

A __________________ index is not in the same order as which the data is stored

False

True or False
When you code a table-level check constraint, the constraint can refer to data in only one column.

date data type

To store a date value without storing a time value, you can use the _______________.

integer

The ____________________ data types are used to store whole numbers.

a or b

To enforce referential integrity, the database can __________________________________

one-to-many

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

the smallest useful components

During the database design process when you identify a data element
such as an address data element, you typically
subdivide the data element into ______________________________

False

True or False
To maintain referential integrity, if you delete a row in a foreign key table,
you must also always delete the related row in the primary key table.

DDL

_______________ are SQL statements that create, modify, delete database objects such as the database itself,
the tables contained in a database, and the indexes for those tables.

True

True or False
You can calculate the number of days between two dates by subtracting the date/time values
and converting the results to an integer.

True

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

Precision

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

32492.05

All of the following values can be stored in a column that's defined as decimal(6,2), except ______________.

January 1, 1900

When you search a datetime column for a time value without specifying a date component,
SQL Server automatically uses a default date of ___________________.

True

True or False
To normalize a data structure, you apply the normal forms in sequence

datetimeoffset

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

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set