SQL Practice Questions

When using mathematical functions in SQL, which of the following comes first in the order of operations? + / - ( )

()

When using NVL(creditcard_balance2 , ____________ ) in an SQL query, which of the following values could be used in the _______________ as the value to assign to any null values of creditcard_balance2? 'FALSE' 0 '01-Dec-2020' 'Paid Off'

0

How many tables are in the KeyBank Financial Wellness Database? 8 5 11 10

10

Which of the following is NOT a relational operator used in SQL? < > >= ==

==

Which of the following statements is true about the WHERE and HAVING clauses? A SQL statement may have a WHERE clause or a HAVING clause, but not both A SQL statement may have a single WHERE clause and a single HAVING clause WHERE and HAVING are equivalent and interchangeable A SQL statement may have any number of WHERE clauses and any number of HAVING clauses

A SQL statement may have a single WHERE clause and a single HAVING clause

What does it mean if a primary key field within a record is null? The column is ambiguously defined There is a syntax error in your query A primary key can never be null There is no matching record in the foreign key table

A primary key can never be null

In BUS 104, we will be using which popular SQL database? SAP HANA IBM DB2 Oracle Microsoft SQL Server

Oracle

With whom are you allowed to discuss information about the data in the client's database? People who have signed the Non-Disclosure Agreement Only representatives of KeyBank All of my classmates Anyone with whom you conduct an empathy interview

People who have signed the Non-Disclosure Agreement

What does this SQL statement do? SELECT * FROM kbfw.recommendations WHERE description LIKE 'Upgrade%' Returns all records where the field description begins with 'Upgrade' Returns all the records where the field description = 'Upgrade' Returns all the records where the field description contains 'Upgrade' Returns all the records where the field description ends with 'Upgrade'

Returns all records where the field description begins with 'Upgrade'

What does this SQL statement do? SELECT * FROM kbfw.emergency_savings WHERE emergencysavings LIKE '%SAVINGS' Returns any records where the field emergencysavings ends with 'SAVINGS' Returns any records where the field emergencysavings ends with 'savings' Returns any records where the field emergencysavings contains 'SAVINGS' Returns an error - invalid identifier

Returns any records where the field emergencysavings ends with 'SAVINGS'

The DISTINCT keyword immediately follows which keyword? WHERE ORDER BY FROM SELECT

SELECT

The ______ operator displays a record if any of the conditions separated by ______ is TRUE. EITHER, OR ELSE, AND ALSO, ALSO OR, OR

OR, OR

Which SQL aggregate function returns the average of all non NULL values in a field? COUNT SUM AVG AVERAGE

AVG

Which table contains demographic information, like the age of the individual completing the Financial Wellness Review? Emergency_Savings Conversations About_You Recommendations

About_you

The GROUP BY clause: should follow SELECT, FROM and WHERE (if present). indicates that we want our aggregate function to be calculated for each unique combination of values of the GROUP BY fields. All of these choices must contain any non-aggregate field(s) listed in the SELECT statement.

All of these choices

The ORDER BY clause: if present, should be the final clause in a SQL query. sorts in ascending order by default, but can be made to sort in descending order if desired. All of these choices can be used with numeric, character, and date data types

All of these choices

Which aggregate functions in SQL can be used with numeric fields? All of these choices AVG COUNT SUM

All of these choices

What would this SQL query produce? SELECT * FROM northwinds.products ORDER BY unitprice DESC All the information in the products table sorted by actual sales price from low to high All the information in the products table sorted by retail list price from high to low All the information in the products table sorted by retail list price from low to high All the information in the products table sorted by actual sales price from high to low

All the information in the products table sorted by retail list price from high to low

What would this query return? SELECT autoloan_fi FROM kbfw.spending_accounts WHERE autoloan_fi = '%Ford%' Any records in the spending_accounts table where the field autoloan_fi was exactly equal to the six character string '%Ford%' Any records in the spending_accounts table where the field autoloan_fi contains 'Ford' Any records in the spending_accounts table where the field autoloan_fi contains 'ford', 'Ford', or 'FORD' Any records in the spending_accounts table where the field autoloan_fi ends with 'Ford'.

Any records in the spending_accounts table where the field autoloan_fi was exactly equal to the six character string '%Ford%'

Which statement is true of the COUNT () aggregate function in SQL? COUNT can be used only with numeric fields. COUNT will include null values. Correct! COUNT can be used with text and numeric fields. COUNT will return the total sum of a numeric field.

COUNT can be used with text and numeric fields.

Which of the following is true regarding multiple conditions connected by AND and OR in a WHERE clause? Conditions are combined with AND prior to OR Multiple conditions must be applied by using multiple WHERE clauses Conditions are combined with OR prior to AND Conditions are applied left to right regardless of whether they are AND or OR.

Conditions are combined with AND prior to OR

The aggregate function included in the HAVING clause must also be in the SELECT clause. True False

False

You can have only one aggregate function in a SELECT. True False

False

Select the clause below that is missing from the following query: SELECT categoryname, categoryid, COUNT(productid) FROM northwinds.products JOIN northwinds.categories ON categories.categoryid = products.categoryid ... ORDER BY categoryid ASC None of these GROUP BY categoryid, categoryname GROUP BY categoryid HAVING categoryid = 8

GROUP BY categoryid, categoryname

Which of the following clauses could be the missing clause from this query? SELECT COUNT(orderid), companyname FROM northwinds.orders JOIN northwinds.customers ON customers.customerid = orders.customerid GROUP BY companyname ...... WHERE companyname = 'Island Trading' WHERE freight > 300 HAVING SUM(freight) > 300 HAVING shipperid = 3

HAVING SUM(freight) > 300

The ______ clause is used to combine rows from two or more tables, based on a related column between them. DISTINCT SELECT JOIN WHERE

JOIN

Choose the correct order of SQL clauses below: SELECT, WHERE, JOIN, FROM SELECT, FROM, WHERE, JOIN SELECT, JOIN, FROM, WHERE SELECT, FROM, JOIN, WHERE

SELECT, FROM, JOIN, WHERE

Choose the correct order of SQL clauses below: SELECT, FROM, JOIN, WHERE, HAVING, GROUP BY, ORDER BY SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY SELECT, FROM, JOIN, WHERE, ORDER BY, GROUP BY, HAVING SELECT, JOIN, FROM, WHERE, GROUP BY, HAVING, ORDER BY

SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY

Which of the following are true regarding case-sensitivity (i.e., whether or not capitalization matters) in SQL? SQL keywords are case-sensitive, but string comparisons are not case-sensitive Both SQL keywords and string comparisons are case-sensitive Neither SQL keywords nor string comparisons are case-sensitive. SQL keywords are not case-sensitive, but string comparisons are case-sensitive

SQL keywords are not case-sensitive, but string comparisons are case-sensitive

Which of the following aggregate functions could you use to get a field total? SUM AVG COUNT All of these choices

SUM

Which table in the KeyBank Financial Wellness Database contains information about the respondent's retirement planning and employer-provided retirement accounts? Emergency_Savings About_You Savings_Goals Conversations

Savings_Goals

Which table contains detailed information about the respondent's financial accounts, even if they are not at KeyBank? Conversations Spending_Accounts Recommendations Money_Management

Spending_Accounts

What is the maximum number of JOIN clauses that can be present in a SQL query? 5 2 1 There is no practical limit

There is no practical limit

In SQL, you can do mathematical operations in the SELECT, WHERE, HAVING, and ORDER BY clauses. True False

True

When using GROUP BY, aggregate function results will be shown for all unique combinations of values of the group by fields, including nulls. True False

True

Which keyword is used to extract only those records that fulfill a specified condition? DISTINCT SELECT WHERE FROM

WHERE

Which WHERE statement could be used in a query of kbfw.spending_accounts to locate all records where the response to credit card 1 financial institution contained 'Chase' regardless of the user's capitalization of the word. WHERE UPPER(creditcard_fi1) LIKE '%CHASE%' WHERE UPPER(creditcard_fi1) LIKE UPPER('Chase') WHERE creditcard_fi1 LIKE UPPER('%chase%') WHERE CREDITCARD_FI1 LIKE UPPER('%Chase%')

WHERE UPPER(creditcard_fi1) LIKE '%CHASE%'

Which WHERE statement could be used to find all the friendly descriptions of the Financial Wellness Review detailed recommendation type that contained 'overdraft', regardless of the capitalization of the word? WHERE description LIKE '%OVERDRAFT%' WHERE description LIKE UPPER('%Overdraft%') WHERE UPPER(description) LIKE UPPER('%overdraft%' ) WHERE UPPER(description) LIKE '%Overdraft%'

WHERE UPPER(description) LIKE UPPER('%overdraft%' )

The WHERE clause is used to limit the number of _______________ returned by a query. fields rows tables columns

rows

What would the following query display? SELECT COUNT(*) FROM northwinds.orders WHERE shipcountry = 'USA' the number of orders shipped to USA nothing, you cannot use COUNT(*) an error message the total value of orders shipped to USA

the number of orders shipped to USA