[DBA 221] Chapter 1-4 Quiz

[1] Which of the following types of statements isn't an action query? a. Insert b. Update c. Select d. Delete

c. Select

[4] Correlation names are temporary table names assigned in which clause? a. ON b. WHERE c. FROM d. ORDER BY

c. FROM

[3] The search condition of a WHERE clause consists of one or more: a. Boolean expressions b. string expressions c. numeric expressions d. none of the above

a. Boolean expressions

[1] Which of the following is not a SQL DML statement? a. CreateTable b. Update c. Select d. Insert

a. CreateTable

[4] (Refer to code example 4-1.) The column name for the second column in the result set will be? a. Date b. VendorName c. Vendor d. VendorID

a. Date

[3] Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask? a. LIKE b. OR c. WHERE d. ORDER BY

a. LIKE

[4] Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause? a. WHERE b. FROM c. ORDER BY d. any of the above

a. WHERE

[4] A union combines the rows from two or more what? a. all of the above b. result tables c. SELECT statements d. queries

a. all of the above

[2] When using the Query Designer, you select the tables and columns you want to use in the: a. Criteria pane b. Diagram pane c. Object Explorer d. SQL pane

b. Diagram pane

[1] Which of the following recommendations won't improve the readability of your SQL statements? a. Break long clauses into multiple lines. b. Use comments to describe what each statement does. c. Indent continued lines. d. Start each clause on a new line.

b. Use comments to describe what each statement does.

[3] Unless you assign a ________________, the column name in the result set is the same as the column name in the base table. a. qualification b. column alias c. unique syntax d. all of the above

b. column alias

[3] When you code an ORDER BY clause, you can specify a: a. column name or alias b. column name, alias, expression, or column number c. column name, alias, or expression d. column name or expression

b. column name, alias, expression, or column number

[2] If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server. a. remove b. detach c. disconnect d. delete

b. detach

[1] To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table. a. primary key b. foreign key c. index d. composite primary key

b. foreign key

[3] When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers? a. TOP InvoiceNumber, InvoiceTotal b. TOP 10 InvoiceTotal c. TOP 10 PERCENT WITH TIES InvoiceNumber d. TOP 10 PERCENT InvoiceNumber e. TOP 10 InvoiceNumber

e. TOP 10 InvoiceNumber

[3] To return all of the columns from the base table, which wildcard character do you include in the SELECT clause? a. - b. + c. = d. *

d. *

[4] Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax: a. lets you separate the join and search conditions b. lets you combine inner and outer joins c. lets you combine the join and seearch conditions d. is easier to read and understand

c. lets you combine the join and seearch conditions

[2] A SQL Server database consists of two files: a database file and a ___________. a. creation file b. error file c. log file d. foreign key file

c. log file

[1] What uniquely identifies each row in a table? a. foreign key b. field c. primary key d. cell

c. primary key

[3] To concatenate character strings in a string expression, which operator do you use? a. + b. - c. * d. =

a. +

[3] 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? a. PaymentDate IS NOT NULL AND InvoiceTotal >= 500 b. PaymentDate IS NULL AND InvoiceTotal > 500 c. NOT (PaymentDate IS NULL AND InvoiceTotal <= 500) d. PaymentDate IS NOT NULL OR InvoiceTotal >= 500

a. PaymentDate IS NOT NULL AND InvoiceTotal >= 500

[2] In Management Studio, if a statement returns data, that data is displayed in the __________ tab. a. Results tab b. Query Editor tab c. Messages tab d. Object Explorer

a. Results tab

[3] When you code a SELECT statement, you must code the four main clauses in the following order: a. SELECT, FROM, WHERE, ORDER BY b. SELECT, ORDER BY, FROM, WHERE c. SELECT, WHERE, ORDER BY, FROM d. SELECT, FROM, ORDER BY, WHERE

a. SELECT, FROM, WHERE, ORDER BY

[2] When you set the compatibility level of SQL Server 2016, you make sure it is compatible with a specific version of: a. SQL Server b. standard SQL c. DDL statements d. DML statements

a. SQL Server

[2] When using the Query Designer, where is the generated SQL statement displayed? a. SQL pane b. Diagram pane c. Update paned d. Criteria pane

a. SQL pane

[2] If you have the files for an existing SQL Server database, the easiest way to create the database is to ________________ those files to the database server. a. attach b. email c. connect d. copy

a. attach

[1] To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like: a. commands, connections, and data readers b. queries, connections, and databases c. queries, connections, and data readers d. commands, connections, and databases

a. commands, connections, and data readers

[4] In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table. a. foreign b. unique c. SELECT d. primary

a. foreign

[4] Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an: a. inner join b. left join c. right join d. outer join

a. inner join

[1] What can you use to combine data from two or more tables into a single result set? a. join b. result set c. virtual table d. view

a. join

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

a. rows in the second table

[1] This is typically modeled after a real-world entity, such as an invoice or a vendor. a. table b. column c. row d. cell

a. table

[4] Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal: a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above

a. the number of rows in the Invoices table

[2] When you use Windows authentication to connect to a database, SQL Server a. uses the login name and password that you use for your PC to authorize your connection b. requires that you use a special login name and password for your authorization c. lets you access the database without authorization as long as you're logged on to Windows d. uses both Windows and SQL Server login names and passwords for authorization

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

[4] In a join, column names only need to be qualified where? a. when the same names are used in both tables b. in outer joins c. in inner joins d. when the code is confusing

a. when the same names are used in both tables

[1] SQL statements that define the tables in a database are referred to as ________________ statements. a. ASCII b. Data Definition Language (DDL) c. SELECT d. Data Manipulation Language (DML)

b. Data Definition Language (DDL)

[2] If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select what? a. View Top 100 Rows command b. Edit Top 200 Rows command c. Select Top 1000 Rows command d. Design command

b. Edit Top 200 Rows command

[3] Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows? a. OFFSET b. FETCH c. FIRST d. NEXT

b. FETCH

[2] Which of the following isn't a common error when entering and executing SQL statements? a. Misspelling the name of a table b. Forgetting to attach the required database c. Misspelling a keyword d. Forgetting to select the required database

b. Forgetting to attach the required database

[3] 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? a. ((InvoiceTotal - PaymentTotal) - CreditTotal) / 10 b. InvoiceTotal - CreditTotal - PaymentTotal / 10 c. (InvoiceTotal - PaymentTotal - CreditTotal) / 10 d. (InvoiceTotal - (PaymentTotal + CreditTotal)) * 0.10

b. InvoiceTotal - CreditTotal - PaymentTotal / 10

[3] The order of precedence for the logical operators in a WHERE clause is: a. Not, Or, And b. Not, And, Or c. Or, And, Not d. And, Or, Not

b. Not, And, Or

[3] Which clause specifies the number of rows that should be skipped before rows are returned from the result set? a. NEXT b. OFFSET c. FIRST d. FETCH

b. OFFSET

[3] If you want to filter the result set that's returned by a SELECT statement, you must include which clause? a. SELECT b. WHERE c. ORDER BY d. FROM

b. WHERE

[1] Insert, Update, and Delete statements can be referred to as ________________ queries. a. SELECT b. action c. database d. result

b. action

[2] You can create a database diagram for: a. just the tables in a one-to-many relationship b. any combination of the tables in a database c. up to 3 tables in a database d. just the related tables in a database

b. any combination of the tables in a database

[1] The processing that's done by the DBMS is typically referred to as: a. front-end processing b. back-end processing c. the file server d. a database management system

b. back-end processing

[1] To run a SELECT statement from an application program, you store the statement in the ________________ object for the database connection.: a. database b. command c. CLR d. table

b. command

[1] The interface between an application program and the DBMS is usually provided by the: a. front end b. data access API c. back end d. programmer

b. data access API

[4] 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 what kind of join? a. right outer b. left outer c. full outer d. cross

b. left outer

[1] The three main hardware components of a client/server system are the clients, the server, and the ________________.: a. application b. network c. data access API d. hard drive

b. network

[3] o override the order of precedence in an arithmetic expression, you can use: a. single quotes b. parentheses c. braces d. double quotes

b. parentheses

[2] Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it. a. redesign b. restore c. reconfigure d. reconstitute

b. restore

[4] ___________________ names can be used when you want to assign a temporary name to a table. a. Qualified b. Table c. Correlation d. Object

c. Correlation

[2] When using the Query Designer, you can sort the sequence for the query in the a. Diagram pane b. SQL Pane c. Criteria pane d. Update pane

c. Criteria pane

[3] To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause? a. ALL b. PERCENT c. DISTINCT d. ORDER BY

c. DISTINCT

[3] Which clause of the SELECT statement names the table that contains the data to be retrieved? a. WHERE b. SELECT c. FROM d. ORDER BY

c. FROM

[3] 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? a. NOT (InvoiceTotal > 1000) b. InvoiceTotal <= 1000 c. InvoiceTotal IN (0,1000) d. InvoiceTotal BETWEEN 0 AND 1000

c. InvoiceTotal IN (0,1000)

[2] Within the Management Studio, you can build a SQL statement without having to write your own code by using the ________________. a. View Designer b. XML editor c. Query Designer d. Object Explorer

c. Query Designer

[2] A graphical tool that you can use to start and stop the database server is called what? a. SQL Server Management Studio b. Table Designer c. SQL Server Configuration Manager d. Object Explorer

c. SQL Server Configuration Manager

[2] The Query Editor of the Management Studio lets you enter and execute all types of: a. SELECT statements b. DDL statements c. SQL statements d. DML statements

c. SQL statements

[4] 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 result sets must be derived from different tables. d. The corresponding columns in the result sets must have compatible data types.

c. The result sets must be derived from different tables.

[1] What SQL dialect does Microsoft SQL Server use?: a. SQL-92 b. SQL-dialect c. Transact-SQL d. ANSI

c. Transact-SQL

[1] When a column in a table is defined, what determines the kind of data it can store? a. an index b. a primary key c. a data type d. a relationship

c. a data type

[1] If you define a column as an identity column,: a. you must provide a unique numeric value for that column whenever a row is added to the table b. you can't use the column as a primary key column c. a number is generated for that column whenever a row is added to the table d. you must also define the column with a default value

c. a number is generated for that column whenever a row is added to the table

[4] (Refer to code example 4-1.) The name "v" is known as a? a. correlation name b. placeholder c. both b and c d. table alias

c. both b and c

[4] (Refer to code example 4-1.) This join is coded using what syntax? a. implicit b. SQL-92 c. both b and c d. explicit

c. both b and c

[1] The intersection of a row and a column is commonly called what? a. intersection b. foreign key c. cell d. primary key

c. cell

[4] When you need to code multiple conditions in a join, it's best to: a. code only join conditions in the WHERE clause b. code all conditions in the WHERE clause c. code only join conditions in the ON clause d. code all conditions in the ON clause

c. code only join conditions in the ON clause

[1] A view is a SELECT statement that is stored with the ________________. a. join b. calculated values c. database d. query

c. database

[1] To store and manage the databases of the client/server system, each server requires what? a. tables b. data access API (application programming interface) c. database management system (DBMS) d. Structured Query Language (SQL)

c. database management system (DBMS)

[2] A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with. a. system b. object c. diagram d. role

c. diagram

[4] 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 the right table that don't satisfy the join condition c. rows in both tables that don't satisfy the join condition d. the Cartesian product of the two tables

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

[1] A relational database consists of one or more what? a. cells b. columns c. tables d. rows

c. tables

[4] If you assign a correlation name to one table in a join,: a. you have to qualify every column name in the query b. you have to assign them to all of the tables in the query c. you have to use that name for the table in the query d. you have to qualify all of the column names for that table

c. you have to use that name for the table in the query

[3] To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause? a. DESC b. ALL c. ASC d. DISTINCT

d. DISTINCT

[3] Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 20th row? a. ORDER BY InvoiceTotal DESCOFFSET 0 ROWSFETCH NEXT 10 ROWS b. ORDER BY InvoiceTotal DESCOFFSET 10 ROWSFETCH FIRST 20 ROWS c. ORDER BY InvoiceTotal DESCOFFSET 20 ROWSFETCH FIRST 10 ROWS d. ORDER BY InvoiceTotal DESCOFFSET 20 ROWSFETCH NEXT 10 ROWS

d. ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS

[2] In Management Studio, if you want to modify a column, or view more detailed information about the columns in a table, what tool would you use? a. Query Editor b. Database Attacher c. Object Explorer d. Table Designer

d. Table Designer

[3] Which of the following is not a valid column alias name? a. Total b. "Total Sales" c. TotalSales d. Total Sales

d. Total Sales

[4] When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause? a. FROM b. ORDER BY c. SELECT d. WHERE

d. WHERE

[4] A join that joins a table with itself is called: a. an outer join b. a U-join c. an inner join d. a self-join

d. a self-join

[3] A string expression can consist 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

[3] Which keyword lets you control the number of rows that are returned by a query? a. DISTINCT b. ALL c. TOP d. all of the above

d. all of the above

[3] A combination of column names and operators that evaluate to a single value is called: a. a predicate b. a query c. a view d. an expression

d. an expression

[2] One limitation of the Query Designer is that you can't use it for: a. multiple-table queries b. action queries c. one-to-many relationships d. certain types of complex queries

d. certain types of complex queries

[2] In Management Studio, the Query Editor uses the IntelliSense feature to automatically display ________________ lists that you can use to enter parts of the SQL statement. a. colored b. data c. execution d. completion

d. completion

[1] Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL. a. flavor b. parlance c. style d. dialect/variant

d. dialect/variant

[4] When you code a union with the INTERSECT keyword to combine two result sets, the union: a. includes all rows that occur in both result sets if the primary keys are the same b. excludes rows from the first result set if they also occur in the second result set c. excludes rows from the second result set if they also occur in the first result set d. includes only rows that occur in both result sets

d. includes only rows that occur in both result sets

[4] Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal: a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above

d. none of the above

[4] Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal: a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above

d. none of the above

[1] What is the most common type of relationship between two tables? a. one-to-one b. many-to-many c. foreign d. one-to-many

d. one-to-many

[1] To retrieve or update the data in a database, the client sends a ________________ to the database.: a. database b. server c. web browser d. query

d. query

[1] If you define a column with a default value, that value is used whenever a row: a. in the table is updated b. with a zero value for that column is added to the table c. is added to the table d. that doesn't include a value for that column is added to the table

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