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.