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
