Database programming exam 1
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values a. AVG(InvoiceTotal) b. none of the above c. either a or b d. SUM(InvoiceTotal)
a
If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? a. 21 b. 19 c. 23 d. 16
b
In many cases, a subquery can be restated as a/an ______________. a. average b. join c. object d. aggregate function
b
The IIF function determines the value it returns based on what type of expression? a. conditional b. numeric c. none of the above d. string
a
What SQL dialect does Microsoft SQL Server use? a. Transact-SQL b. SQL-dialect c. ANSI d. SQL-92
a
You can use a ________________ in the ________________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause. a. table, FROM b. join, FROM c. join, WHERE d. column, WHERE
b
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
If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution a. 25 b. 200.00 c. 225.00 d. 0
c
SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal >(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)FROM Invoices) GROUP BY VendorName ORDER BY BalanceDue DESC; (Please refer to code example 6-1.) When this query is executed, the NumberOfInvoices column for each row will show the number a. of invoices in the Invoices table b. 1 c. of invoices for each vendor that have a larger balance due than the average balance due for all invoices d. of invoices for each vendor
c
All of the aggregate functions ignore null values, except for which function? a. AVG b. COUNT(*) c. MIN d. MAX
b
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 a. CONVERT b. Neither accepts the optional style argument c. They are both the same d. CAST
a
The integer and decimal data types are considered ________________ because their precision is exact. a. exact numeric data types b. real data types c. Unicode characters d. approximate numeric data types
a
The six clauses of the SELECT statement must be coded in the following order: a. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY b. SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING c. SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY d. SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING
a
The varchar and nvarchar data types to store variable-length strings. Which of the statements below is true? a. Data stored using these data types occupies only the number of bytes needed to store the string b. all of the above c. They are typically used to define columns with a fixed number of characters. d. In general, they are less efficient than fixed-length strings.
a
This is typically modeled after a real-world entity, such as an invoice or a vendor. a. table b. column c. cell d. row
a
To code a/an ________________ value as a literal, precede the value with the character N a. Unicode b. ASCII c. fixed-length string d. variable-length string
a
To concatenate character strings in a string expression, which operator do you use? a. + b. * c. - d. =
a
To locate the index of the first character of the first occurence of a substring within another string, you would use which function? a. CHARINDEX b. PATINDEX c. SUBSTRING d. REVERSE
a
To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause? a. DISTINCT b. ALL c. PERCENT d. ORDER BY
a
Which choice below will increase the storage capacity of a varchar column so it can store up to 2 gigabytes of data? a. varchar(max) b. varchar XTREME c. varchar(large) d. varchar(XL)
a
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaluate to? ISNUMERIC(ZipCode) AS Solution a. none b. true c. false d. null
b
If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value? a. DEFAULT b. none of the above c. NULL d. VALUE
a
Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows? a. OFFSET b. NEXT c. FETCH d. FIRST
c
Which of the following statements is true about the CONVERT and TRY_CONVERT functions? a. CONVERT can format the result of a conversion, but TRY_CONVERT can't. b. CONVERT is an ANSI-standard function, but TRY_CONVERT is not. c. CONVERT can be used with any data type, but TRY_CONVERT can't. d. CONVERT returns an error if the expression can't be converted, but TRY_CONVERT returns a NULL value.
d
A SELECT statement that includes aggregate functions is often called a/an ________________ query. a. compound b. none of the above c. summary d. action
c
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows. a. aggregate b. expression c. table d. none of the above
c
A relational database consists of one or more what? a. columns b. rows c. tables d. cells
c
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause. a. GROUP BY b. FROM c. HAVING d. ORDER BY
c
Expressions coded in the WHERE clause a. must refer to columns in the SELECT clause b. can use aggregate search conditions but can't use non-aggregate search conditions c. can use non-aggregate search conditions but can't use aggregate search conditions d. can use either aggregate search conditions or non-aggregate search conditions
c
The _____________________ of a decimal value indicates the total number of digits that can be stored in a decimal data type. a. ASCII b. Unicode specification c. precision d. scale
c
The char and nchar data types are used to store fixed-length strings. Which of the statements below is true? a. They are typically used to define columns that have a fixed number of characters b. The char(2) data type will always contain two characters c. all of the above d. Data stored using these data types occupies the same number of bytes regardless of the actual length of the string.
c
The processing that's done by the DBMS is typically referred to as a. a database management system b. the file server c. back-end processing d. front-end processing
c
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what? a. a backslash b. a semi-colon c. a comma d. a colon
c
To override the order of precedence in an arithmetic expression, you can use a. braces b. single quotes c. parentheses d. double quotes
c
To test whether one or more rows are returned by a subquery, you can use which operator? a. MAX b. NOT EXISTS c. EXISTS d. MIN
c
WITH Top10 AS ( SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for a. each invoice b. each vendor c. each invoice in the Top10 table d. each vendor in the Top10 table
d
A SQL Server database consists of two files: a database file and a ___________. a. creation file b. error file c. foreign key file d. log file
d
A correlated subquery is one that a. uses correlation names for one or more of the tables in a join b. uses correlation names for the tables in the subquery c. uses correlation names for the tables in the outer query d. is executed once for each row in the outer query
d
A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn't applied until after the grouping. a. WHERE, FROM b. FROM, WHERE c. HAVING, WHERE d. WHERE, HAVING
d
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword? a. AVG b. GROUP BY c. ORDER BY d. DISTINCT
d
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) a. a subquery can't be introduced in this way b. a single value c. a column of one or more rows d. a table
d
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) a. a column of one or more rows b. a subquery can't be introduced in this way c. a table d. a single value
d
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery) a. a table b. a subquery can't be introduced in this way c. a single value d. a column of one or more rows
d
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery) a. a subquery can't be introduced in this way b. a single value c. a table d. a column of one or more rows
d
What can you use to combine data from two or more tables into a single result set? a. result set b. view c. virtual table d. join
d
What uniquely identifies each row in a table? a. field b. cell c. foreign key d. primary key
d
When using the Query Designer, where is the generated SQL statement displayed? a. Diagram pane b. Update pane c. Criteria pane d. SQL pane
d
If you use ________________ in the select list, you must name the column since that name is used in the definition of the new table. a. indexes b. aggregate functions c. foreign keys d. calculated values
d
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN VendorsON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) Although this query runs as coded, it contains this logical error: a. The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence b. The condition in the WHERE clause should be coded in the HAVING clause c. The condition in the HAVING clause should be coded in the WHERE clause d. The column name for the fifth column in the result set doesn't match the data
d
When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table. a. updated in b. moved into c. deleted from d. copied into
d
A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with. a. diagram b. system c. object d. role
a
A subquery is a/an ______________ statement that's coded within another SQL statement. a. SELECT b. ORDER BY c. FROM d. WHERE
a
A subquery that's used in a WHERE or HAVING clause is called what? a. a subquery search condition b. an aggregate value c. an outer query d. an introduction
a
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) a. a subquery can't be introduced in this way b. a table c. a column of one or more rows d. a single value
a
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. left outer b. right outer c. full outer d. cross
a
In an UPDATE statement, the WHERE clause will a. specifiy the condition a row must meet to be updated b. name the table to be updated c. specify the values to be assigned to columns d. name the columns to be updated
a
Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statement is sometimes referred to as what? a. the upsert statement b. the insate statement c. the dessert statement d. the magic statement
a
When coding search conditions, you can use which keyword to create compound search conditions? a. AND b. JOIN c. CREATE d. MERGE
a
When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted? a. WHERE b. HAVING c. USING d. MERGE
a
When you code a union with the INTERSECT keyword to combine two result sets, the union a. includes only rows that occur in both result sets b. excludes rows from the first result set if they also occur in the second result set c. includes all rows that occur in both result sets if the primary keys are the same d. excludes rows from the second result set if they also occur in the first result set
a
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. a. WHERE b. HAVING c. MERGE d. ORDER BY
a
Which data types are used to store whole numbers? a. integer b. real data c. string d. decimal
a
Which function returns the specified number of characters from the string starting at the specified position? a. SUBSTRING b. REPLACE c. RIGHT d. SPACE
a
Which function returns the string with any leading spaces removed? a. LTRIM b. REPLACE c. LEFT d. RTRIM
a
Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchive SELECT * FROM Invoices WHERE TermsID = 1; a. Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table. b. Adds all of the rows in the Invoices table to the InvoiceArchive table and sets the TermsID column to 1 in each row. c. Adds one row from the Invoices table to the InvoiceArchive table. d. Updates all of the rows in the InvoiceArchive table that have 1 in the TermsID column to the rows in the Invoices table.
a
Which uses the least amount of storage? a. they all use the same amount of storage b. 'example' stored in a column of type nchar(20) c. 'exam' stored in a column of type nchar(20) d. 'ex' stored in a column of type nchar(20)
a
Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function a. CAST (InvoiceAmount AS varchar) b. CAST (varcharDate, InvoiceAmount) AS varchar c. CAST (InvoiceAmount = varchar) d. CAST (varcharDate, InvoiceAmount, 107.25) as varchar
a
You can't update a. an identity column b. a row c. one or more rows d. a column value
a
You specify the conditions that must be met for a row to be deleted in the which clause? a. WHERE b. FROM c. ORDER BY d. HAVING
a
You use the UPDATE statement to modify one or more rows in the table named in the ________________ clause. a. UPDATE b. FROM c. INSERT d. WHERE
a
All of the following values can be stored in a column that's defined as decimal(6,2), except a. 2479.95 b. 32492.05 c. -246.29 d. 0
b
If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution a. EWilliams b. ewilliam c. ewilliams d. EWilliam
b
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery) a. a table b. a single value c. a subquery can't be introduced in this way d. a column of one or more rows
b
If you omit the WHERE clause from a DELETE statement a. the table definition will be deleted b. all rows in the table will be deleted c. all columns in the table will be deleted d. none of the above
b
In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement? INSERT INTO InvoiceCopy(VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal,TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2016-08-01'); a. The column names in the column list are in the wrong sequence. b. The number of items in the column list doesn't match the number in the VALUES list. c. There are zeroes in the VALUES list. d. The values are in the wrong sequence.
b
SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal >(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)FROM Invoices) GROUP BY VendorName ORDER BY BalanceDue DESC; (Please refer to code example 6-1.) When this query is executed, the result set will contain a. one row for each invoice that has a larger balance due than the average balance due for all invoices b. one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices c. one row for the invoice with the largest balance due for each vendor d. one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices
b
Subqueries can be ________________ within other subqueries. a. grandfathered b. nested c. joined d. restated
b
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. a. summary query b. subquery c. WHERE clause d. HAVING clause
b
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
WITH Top10 AS ( SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top10 is coded as a a. none of the above b. common table expression (CTE) c. noncorrelated subquery d. correlated subquery
b
WITH Top10 AS ( SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top10 is used as part of a a. correlated table expression b. join c. noncorrelated subquery d. correlated subquery
b
WITH Top10 AS ( SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, there will be how many rows in the result table? a. 1 b. 10 c. Unknown d. 6
b
When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where a. 0=Sunday, 1=Monday, etc. b. 1=Sunday, 2=Monday, etc. c. none of the above d. 7=Sunday, 1=Monday, etc.
b
Which uses the least amount of storage? a. 'example' stored in a column of type varchar(20) b. 'ex' stored in a column of type varchar(20) c. 'exam' stored in a column of type varchar(20) d. they all use the same amount of storage
b
Write the code for an ORDER BY clause that sorts a table into numeric sequence by the data in the PartCode column if that column contains whole numbers that are stored with the varchar data type. a. ORDER BY CAST (PartCode AS decimal) b. ORDER BY CAST (PartCode AS int) c. ORDERBY CAST (PartCode) d. none of the above
b
WITH Top10 AS ( SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, each row in the result table will show a. the average invoice amount related to that row b. the largest invoice amount related to that row, but only if it's larger than the average for all invoices c. the largest invoice amount related to that row d. the largest invoice amount related to that row, but only if it's larger then the average for all invoices related to that row
c
When you code a column list in an INSERT statement, you can omit identify columns, columns that have default values, and columns that allow __________ values. a. time b. date c. null d. string
c
Which statements are true about the ROUND function? a. To truncate the function rather than round it, code a zero value for the function b. If the length is positive, the digits to the left of the decimal point are rounded c. It returns the number rounded to the specified precision d. all of the above
c
Write an aggregate expression for the number of entries in the VendorName column, including null values a. SUM(VendorName) b. COUNT(Invoice + Null) c. COUNT(*) d. COUNT(VendorName)
c
____________________ characters can be used to encode most of the characters in most of the languages of the world. a. fixed-length string b. ASCII c. Unicode d. variable-length string
c
Which of the following statements is true? a. Values are implicitly converted from the data type with higher precedence to the data type with lower precedence. b. You can convert a data type implicitly by using either the CAST or the CONVERT function. c. Every data type can be implicitly converted to every other data type. d. Implicit data type conversion is performed when you mix values of different data types in an expression.
d
Write an aggregate expression to find the latest date in the InvoiceDate column a. SUM(InvoiceDate) b. MIN(InvoiceDate) c. COUNT(InvoiceDate) d. MAX(InvoiceDate)
d
Write the code for a DELETE statement that deletes every row in the Vendors table a. none of the above b. DELETE Vendors WHERE ALL; c. DELETE * Vendors; d. DELETE Vendors;
d
You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause. a. WHERE b. HAVING c. FROM d. DELETE
d
Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL. a. dialect/variant b. parlance c. flavor d. style
a
Correlation names are temporary table names assigned in which clause? a. FROM b. WHERE c. ORDER BY d. ON
a
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. a number is generated for that column whenever a row is added to the table c. you can't use the column as a primary key column d. you must also define the column with a default value
a
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
A join that joins a table with itself is called a. a U-join b. a self-join c. an outer join d. an inner join
b
If you assign a correlation name to one table in a join, a. you have to assign them to all of the tables in the query b. you have to use that name for the table in the query 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
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. disconnect b. detach c. delete d. remove
b
If you want to filter the result set that's returned by a SELECT statement, you must include which clause? a. FROM b. WHERE c. SELECT d. ORDER BY
b
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table. a. primary b. foreign c. unique d. SELECT
b
Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it. a. reconstitute b. restore c. reconfigure d. redesign
b
The Query Editor of the Management Studio lets you enter and execute all types of a. DML statements b. SQL statements c. SELECT statements d. DDL statements
b
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. DISTINCT b. DESC c. ASC d. ALL
b
A graphical tool that you can use to start and stop the database server is called what? a. Object Explorer b. SQL Server Management Studio c. SQL Server Configuration Manager d. Table Designer
c
Expressions coded in the HAVING clause a. can use aggregate search conditions but can't use non-aggregate search conditions b. can refer to any column in the base tables c. can use either aggregate search conditions or non-aggregate search conditions d. can use non-aggregate search conditions but can't use aggregate search conditions
c
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. connect b. copy c. attach d. email
c
A combination of column names and operators that evaluate to a single value is called a. a query b. a predicate c. a view d. an expression
d
A string expression can consist of a. one or more literal values b. a combination of character columns and literal values c. one or more character columns d. all of the above
d
A view is a SELECT statement that is stored with the ________________. a. calculated values b. join c. query d. database
d
In Management Studio, if a statement returns data, that data is displayed in the __________ tab. a. Object Explorer b. Query Editor tab c. Messages tab d. Results tab
d
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. data b. execution c. colored d. completion
d
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
In a join, column names only need to be qualified where? a. in inner joins b. in outer joins c. when the code is confusing d. when the same names are used in both tables
d
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 InvoiceNumber d. TOP 10 PERCENT WITH TIES InvoiceNumber e. TOP 10 InvoiceNumber
e
If you define a column with a default value, that value is used whenever a row a. that doesn't include a value for that column is added to the table b. with a zero value for that column is added to the table c. is added to the table d. in the table is updated
a
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. outer join c. left join d. right join
a
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.) The column name for the second column in the result set will be? a. Date b. Vendor c. VendorName d. VendorID
a
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
SQL statements that define the tables in a database are referred to as ________________ statements. a. Data Definition Language (DDL) b. SELECT c. ASCII d. Data Manipulation Language (DML)
a
The order of precedence for the logical operators in a WHERE clause is a. Not, And, Or b. Not, Or, And c. And, Or, Not d. Or, And, Not
a
To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table. a. foreign key b. primary key c. index d. composite primary key
a
What is the most common type of relationship between two tables? a. one-to-many b. one-to-one c. foreign d. many-to-many
a
When using the Query Designer, you can sort the sequence for the query in the a. Criteria pane b. SQL Pane c. Diagram pane d. Update pane
a
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
Which clause of the SELECT statement names the table that contains the data to be retrieved? a. FROM b. ORDER BY c. SELECT d. WHERE
a
Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask? a. LIKE b. WHERE c. ORDER BY d. OR
a
Which keyword lets you control the number of rows that are returned by a query? a. all of the above b. DISTINCT c. TOP d. ALL
a
Which of the following is not a valid column alias name? a. Total Sales b. "Total Sales" c. Total d. TotalSales
a
Which of the following types of statements isn't an action query? a. Select b. Update c. Delete d. Insert
a
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 GROUP BY VendorID; a. The total unpaid balance due for each VendorID b. The unpaid balance for each invoice c. The total of paid invoices for each VendorID d. The total amount invoiced by each VendorID
a
You can create a database diagram for a. any combination of the tables in a database b. just the related tables in a database c. just the tables in a one-to-many relationship d. up to 3 tables in a database
a
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
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. InvoiceTotal BETWEEN 0 AND 1000 b. InvoiceTotal IN (0,1000) c. NOT (InvoiceTotal > 1000) d. InvoiceTotal <= 1000
b
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
When you need to code multiple conditions in a join, it's best to a. code all conditions in the ON clause b. code only join conditions in the ON clause c. code all conditions in the WHERE clause d. code only join conditions in the WHERE clause
b
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
Which functions perform a calculation on the values of a column from selected rows? a. Arithmetic b. Aggregate c. Summary d. String
b
Which of the following isn't a common error when entering and executing SQL statements? a. Misspelling a keyword b. Forgetting to attach the required database c. Misspelling the name of a table d. Forgetting to select the required database
b
Write an aggregate expression for the number of unique values in the VendorID column a. AVG(VendorID) b. COUNT(DISTINCT VendorID) c. COUNT(VendorID) d. SUM(VendorID)
b
You can use the OVER clause with an aggregate function to a. omit summary rows with values over a specified amount b. include the rows used to calculate the summary in the result set c. perform the summary over a range of values d. include values in the summary only if they're over a specified amount
b
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. FROM b. WHERE c. ORDER BY d. any of the above
b
___________________ names can be used when you want to assign a temporary name to a table. a. Table b. Correlation c. Qualified d. Object
b
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. Design command c. Edit Top 200 Rows command d. Select Top 1000 Rows command
c
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. Object Explorer c. Table Designer d. Database Attacher
c
Insert, Update, and Delete statements can be referred to as ________________ queries. a. SELECT b. database c. action d. result
c
One limitation of the Query Designer is that you can't use it for a. one-to-many relationships b. action queries c. certain types of complex queries d. multiple-table queries
c
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN VendorsON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for a. each vendor with invoice average over $500 b. each city with invoice totals over $500 c. each vendor with invoice totals over $500 d. each city with invoice average over $500
c
The search condition of a WHERE clause consists of one or more a. string expressions b. none of the above c. Boolean expressions d. numeric expressions
c
The three main hardware components of a client/server system are the clients, the server, and the ________________. a. application b. data access API c. network d. hard drive
c
To return all of the columns from the base table, which wildcard character do you include in the SELECT clause? a. - b. = c. * d. +
c
When a column in a table is defined, what determines the kind of data it can store? a. a primary key b. a relationship c. a data type d. an index
c
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. NOT (PaymentDate IS NULL AND InvoiceTotal <= 500) b. PaymentDate IS NULL AND InvoiceTotal > 500 c. PaymentDate IS NOT NULL AND InvoiceTotal >= 500 d. PaymentDate IS NOT NULL OR InvoiceTotal >= 500
c
When you set the compatibility level of SQL Server 2016, you make sure it is compatible with a specific version of a. DDL statements b. standard SQL c. SQL Server d. DML statements
c
Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 21st row? a. ORDER BY InvoiceTotal DESCOFFSET 0 ROWSFETCH NEXT 10 ROWS b. ORDER BY InvoiceTotal DESCOFFSET 20 ROWSFETCH FIRST 10 ROWS c. ORDER BY InvoiceTotal DESCOFFSET 20 ROWSFETCH NEXT 10 ROWS d. ORDER BY InvoiceTotal DESCOFFSET 10 ROWSFETCH FIRST 20 ROWS
c
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)) * 0.10 b. (InvoiceTotal - PaymentTotal - CreditTotal) / 10 c. InvoiceTotal - CreditTotal - PaymentTotal / 10 d. ((InvoiceTotal - PaymentTotal) - CreditTotal) / 10
c
Which of the following is not a SQL DML statement? a. Insert b. Select c. CreateTable d. Update
c
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. is easier to read and understand b. lets you separate the join and search conditions c. lets you combine the join and seearch conditions d. lets you combine inner and outer joins
c
Which of the following recommendations won't improve the readability of your SQL statements? a. Indent continued lines. b. Break long clauses into multiple lines. c. Use comments to describe what each statement does. d. Start each clause on a new line.
c
Write an aggregate expression to find the VendorName column that's last in alphabetical order a. COUNT(VendorName) b. SUM(VendorName) c. MAX(VendorName) d. either b or c
c
A union combines the rows from two or more what? a. SELECT statements b. result tables c. queries d. all of the above
d
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.) The name "v" is known as a? a. placeholder b. correlation name c. table alias d. both b and c
d
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 join is coded using what syntax? a. implicit b. explicit c. SQL-92 d. both b and c
d
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
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
The CUBE operator is similar to the ROLLUP operator except that a. a single summary row is added at the end of the result set that summarizes all the groups b. you can add additional sets of parentheses to create composite groups that consist of multiple columns c. none of the above d. it adds summary rows for every combination of groups
d
The interface between an application program and the DBMS is usually provided by the a. front end b. back end c. programmer d. data access API
d
The intersection of a row and a column is commonly called what? a. intersection b. foreign key c. primary key d. cell
d
To retrieve or update the data in a database, the client sends a ________________ to the database. a. web browser b. database c. server d. query
d
When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding which operator? a. HAVING b. none of the above c. OVER d. ROLLUP
d
When you code a SELECT statement, you must code the four main clauses in the following order a. SELECT, WHERE, ORDER BY, FROM b. SELECT, ORDER BY, FROM, WHERE c. SELECT, FROM, ORDER BY, WHERE d. SELECT, FROM, WHERE, ORDER BY
d
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
When you code an ORDER BY clause, you can specify a a. column name or alias b. column name, alias, or expression c. column name or expression d. column name, alias, expression, or column number
d
When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause? a. SELECT b. FROM c. ORDER BY d. WHERE
d
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorState, COUNT(*) AS Column2 FROM Vendors GROUP BY VendorState HAVING COUNT(*) > 1; a. The number of vendors in each state b. The duplicate vendors from each state c. The names of the vendors in each state d. The number of vendors in each state having more than one vendor
d
