Question 1

To add new data to a table,use the ____ command.
A)ADDTO
B)DELETE
C)INSERT
D)SELECT

Accepted Answer

To add new data to a table, we use the INSERT command. This command adds new rows to the table. ADDTO is not a valid SQL command, DELETE removes data from a table, and SELECT retrieves data from a table but does not add new data.

Question 2

In MySQL,the ____ is used as a wildcard to represent any collection of characters.
A)asterisk (*)
B)underscore (_)
C)percent sign (%)
D)question mark (?)

Accepted Answer

The percent sign (%) is used as a wildcard in MySQL to match any collection of characters, including none, while the underscore (_) is used to match a single character. The asterisk (*) and question mark (?) are not used as wildcards in MySQL.

Question 3

To use a wildcard,include the ____ operator in the WHERE clause.
A)AS
B)UNION
C)BETWEEN
D)LIKE

Accepted Answer

The LIKE operator is used in the WHERE clause to search for patterns in a column. It is commonly used with the wildcard characters % (percent sign), which matches any string of zero or more characters, and _ (underscore), which matches any single character.

Question 4

Based on the code above,list the number,name,credit limit,and balance for all customers with credit limits that exceed their balances.
A)SELECT CustomerNum,CustomerName,CreditLimit,Balance FROM Customer ;
B)SELECT CustomerNum,CustomerName,CreditLimit,Balance FROM Customer WHERE CreditLimit>Balance ;
C)SELECT CustomerNum,CustomerName,Balance FROM Customer WHERE CreditLimit>Balance ;
D)SELECT CustomerNum,CustomerName FROM Customer WHERE CreditLimit>Balance ;

Accepted Answer

This choice uses a WHERE clause to filter for only customers whose CreditLimit is greater than their Balance, which is the requirement given in the question. It also includes all the necessary columns (CustomerNum, CustomerName, CreditLimit, and Balance), allowing for easy identification of the required information. Option A includes unnecessary column (we don't need the NAME to list the customers) and will require further processing of the data. Option C includes unnecessary column (CREDIT LIMIT) and will require further processing of the data. Option D will not include the credit limit and balance of the customers which are required in the question.

Question 5

When used after the word SELECT,the ____ symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS when you created the table.
A)&
B)*
C)$
D)#

Accepted Answer

The asterisk (*) symbol is used to represent "all fields" in a table. So, when used after the word SELECT, it indicates that all fields should be included in the query results.

Question 6

When you use a name containing a space in Access SQL,you must ____.
A)enclose it in square brackets
B)enclose it in quotation marks
C)precede it by an asterisk
D)precede it by a question mark

Accepted Answer

When using a name containing a space in Access SQL, you must enclose it in square brackets. This is because the space character in a name can cause confusion in the SQL syntax, so the square brackets indicate to Access that the name should be treated as a single entity.

Question 7

In Access SQL,the ____ is used as a wildcard to represent any individual character.
A)asterisk (*)
B)underscore (_)
C)percent sign (%)
D)question mark (?)

Accepted Answer

The answer of In Access SQL,the ____ is used as...

Question 8

Based on the code above,list the number,name,street,and credit limit of all customers. Order the customers by name within descending credit limit.
A)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer SORT BY CreditLimit DESC,CustomerName ;
B)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer SORT BY CreditLimit ASC,CustomerName ;
C)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer ORDER BY CreditLimit DESC,CustomerName ;
D)SELECT CustomerNum,CustomerName,Street,CreditLimit FROM Customer ORDER BY CreditLimit ASC,CustomerName ;

Accepted Answer

This query selects the necessary columns, lists the customers in descending order of credit limit, and then within the same limit, orders them alphabetically by name. The keyword is "ORDER BY," not "SORT BY," and the correct order of the two columns is first by credit limit, then by name.

Question 9

When rows are grouped,____.
A)the totals appear with the rows
B)the rows appear in order
C)no output is produced
D)one line of output is produced for each group

Accepted Answer

When rows are grouped, one line of output is produced for each group. This line typically contains the group's summary information, such as total or average values. Therefore, choice D is the best option. Choices A and B may or may not apply, depending on the specific grouping and display settings. Choice C is incorrect because data grouping is a fundamental feature in data analysis and reporting, and it always produces some output.

Question 10

You can save the results of a query as a table by including the ____ clause in the query.
A)INTO
B)INSERT
C)UPDATE
D)DELETE

Accepted Answer

The INTO clause is used to save the results of a query as a new table. It is followed by the name of the new table that will be created and the SELECT statement that defines the data to be included in the new table. For example, "SELECT * INTO new_table FROM old_table" will create a new table named "new_table" with the same structure and data as "old_table".

Question 11

Based on the code above,list the number,name,and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.
A)SELECT CustomerNum,CustomerName,Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
B)SELECT CustomerNum,CustomerName,Balance FROM Customer WHERE Balance > 1000 ;
C)SELECT CustomerName,Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;
D)SELECT CustomerNum,CustomerName FROM Customer WHERE Balance BETWEEN 1000 AND 5000 ;

Accepted Answer

Choice A correctly selects the customer number, name, and balance for customers with balances between $1,000 and $5,000, inclusive.

Question 12

Based on the code above,list the number,name,and complete address of every customer located on a street that contains the letters "Oxford".
A)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "?Oxford" ;
B)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "%Oxford%" ;
C)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "@Oxford@" ;
D)SELECT CustomerNum,CustomerName,Street,City,State,Zip FROM Customer WHERE Street LIKE "Oxford" ;

Accepted Answer

The SQL wildcard "%" is used to match any sequence of characters. So in this case, "%Oxford%" will find any customer whose street contains the letters "Oxford" anywhere in the street name.

Question 13

The ____ function determines the largest value in a given column.
A)LGE
B)LARGE
C)MAX
D)MAXIMUM

Accepted Answer

The MAX function determines the largest value in a given column. The LGE and MAXIMUM options are not valid Excel functions, and LARGE returns the nth largest value in a range or array, not the overall largest value.

Question 14

When a subquery is used,____ is(are) evaluated first.
A)the subquery query
B)the outer query
C)both a and b simultaneously
D)whichever query is selected to execute first by the user

Accepted Answer

When a subquery is used, the subquery is evaluated first and the results are used in the outer query. Therefore, option A is the correct choice.

Question 15

Based on the code above,list the number,name,and available credit for all customers with credit limits that exceed their balances.
A)SELECT CustomerNum,CustomerName AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
B)SELECT CustomerNum,CustomerName,CreditLimit AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
C)SELECT CustomerNum,CustomerName,Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;
D)SELECT CustomerNum,CustomerName,CreditLimit-Balance AS AvailableCredit FROM Customer WHERE CreditLimit>Balance ;

Accepted Answer

Option D uses the formula (CreditLimit - Balance) to calculate the available credit for each customer. This will only return the customers whose credit limit exceeds their balance, meeting the requirement of the question. Options A, B, and C do not calculate the available credit correctly and/or do not filter the customers based on the required condition.

Question 16

Based on the code above,for each sales rep,list the rep number,the number of customers assigned to the rep,and the average balance of the rep's customers. Group the records by rep number and order the records by rep number.
A)SELECT RepNum,AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
B)SELECT RepNum,COUNT(*),AVG(Balance) FROM Part GROUP BY RepNum ORDER BY RepNum ;
C)SELECT RepNum,COUNT(*),AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;
D)SELECT RepNum,COUNT(*) FROM Customer GROUP BY RepNum ORDER BY RepNum ;

Accepted Answer

Option C selects the RepNum, COUNT(*) (number of customers assigned to the rep), and the AVG(Balance) (average balance of the rep's customers) columns from the Customer table, groups the results by RepNum, and orders them by RepNum. This is exactly what is required by the question prompt. Option A only selects the RepNum and AVG(Balance) columns, but does not count the number of customers assigned to the rep. Option B includes the Part table instead of the Customer table, which is not relevant to the question prompt. Option D counts the number of customers assigned to the rep, but does not include the average balance of the rep's customers.

Question 17

Many versions of SQL require you to end a command with a ____.
A)comma (,)
B)period (.)
C)colon (:)
D)semicolon (;)

Accepted Answer

Many versions of SQL require you to end a command with a semicolon (;) in order to indicate the end of the command and allow the database to execute it.

Question 18

In order to make changes to existing data in a table,you would use the ____ command.
A)DELETE
B)CHANGE
C)SELECT
D)UPDATE

Accepted Answer

The UPDATE command is used to modify existing data in a table. It allows you to specify which rows to update and what values to set for each field in those rows. The DELETE command removes entire rows from a table, the CHANGE command does not exist in SQL syntax, and the SELECT command is used to retrieve data from a table rather than modify it.

Question 19

The basic form of an SQL retrieval command is ____.
A)SELECT-WHERE-FROM
B)CREATE-SELECT-FROM
C)SELECT-WHERE
D)SELECT-FROM-WHERE

Accepted Answer

The basic form of an SQL retrieval command is SELECT-FROM-WHERE, where SELECT specifies the column(s) to be retrieved, FROM specifies the table(s) from which to retrieve the data and WHERE specifies the condition(s) that must be satisfied for the records to be retrieved. Option A is missing the FROM clause, Option B is missing the WHERE clause, and Option C is missing the FROM clause.

Question 20

The ____ clause can be used to create groups of records.
A)SELECT
B)ORDER BY
C)SORT BY
D)GROUP BY

Accepted Answer

GROUP BY clause can be used to create groups of records based on one or more columns in the table. It is often used with aggregate functions like SUM, COUNT, AVG, etc. to summarize data within each group.

Quiz 3: The Relational Model 2: SQL

To add new data to a table,use the ____ command.

In MySQL,the ____ is used as a wildcard to represent any collection of characters.

To use a wildcard,include the ____ operator in the WHERE clause.

Based on the code above,list the number,name,credit limit,and balance for all customers with credit limits that exceed their balances.

When used after the word SELECT,the ____ symbol indicates that you want to include all fields in the query results in the order in which you described them to the DBMS when you created the table.

When you use a name containing a space in Access SQL,you must ____.

In Access SQL,the ____ is used as a wildcard to represent any individual character.

Based on the code above,list the number,name,street,and credit limit of all customers. Order the customers by name within descending credit limit.

When rows are grouped,____.

You can save the results of a query as a table by including the ____ clause in the query.

Based on the code above,list the number,name,and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.

Based on the code above,list the number,name,and complete address of every customer located on a street that contains the letters "Oxford".

The ____ function determines the largest value in a given column.

When a subquery is used,____ is(are) evaluated first.

Based on the code above,list the number,name,and available credit for all customers with credit limits that exceed their balances.

Based on the code above,for each sales rep,list the rep number,the number of customers assigned to the rep,and the average balance of the rep's customers. Group the records by rep number and order the records by rep number.

Many versions of SQL require you to end a command with a ____.

In order to make changes to existing data in a table,you would use the ____ command.

The basic form of an SQL retrieval command is ____.

The ____ clause can be used to create groups of records.