SQL and Such

scalar

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

aggregate

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

Subquery

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

JOIN

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

Precision

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

Management Studio

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

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

TRUE

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

type

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

linking

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

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.

when the same names are used in both tables.

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

foreign

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

equal

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

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

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

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

action

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

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.

T-SQL

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

COALESCE

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

WHERE

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

DATEDIFF

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

FROM

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

integer

The ____________________ data types are used to store whole numbers.

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.

True

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

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

True

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

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

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.

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.

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.

True

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

True

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

True

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

True

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

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

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.

False

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

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.

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.

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.

Third

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

Alias

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

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

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?

Attaching a database

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

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.

DML

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

nonclustered index

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

CTE

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

references or foreign key

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

check

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

one or more tables

A relational database can contain ___________.

identity

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

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

foreign key

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

Denormalized

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

primary key

A/An __________________ uniquely identifies each row in a table

32492.05

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

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 ______________________________

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

Expressions coded in the WHERE clause

DDL

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

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.

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.

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.

one-to-many

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

one-to-many

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

Clustered

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

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

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

a single, scalar value

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

INTO

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

a or b

To enforce referential integrity, the database can __________________________________

DISTINCT

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

foreign key

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

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.

DESC

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

date data type

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

WHERE

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

SELECT, FROM, WHERE, ORDER BY

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

INTO

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

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.

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,

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

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

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

WHERE

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

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?

Column is frequently updated

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

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

datetimeoffset

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

right outer joins can be converted to left outer joins

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

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.

Count(Distinct)

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

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.

MIN(InvoiceDate)

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

MAX(VendorName)

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

Correlation

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

Unicode

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