1. When are temporary tables automatically deleted?
- After ending the session in a SQL
database
- After running a
query in your SQL database
- After running a report from the table
- After completing all calculations in the table
Explanation: Due to
the fact that the particular rules might change from one database management
system (DBMS) to another, it is vital to evaluate the specific behavior of
temporary tables in the database system that you are using. Effective resource
management is facilitated by the automated deletion of temporary tables, which
guarantees that these tables do not remain in existence for longer than is
required.
2. The following SQL query contains information about bike
trips:
What data will appear in the temporary
table created through this query?
- A random subset of bike trips
- Bike trips that lasted exactly 60
minutes
- The total
number of bike trips
- Bike trips equal to or more than one hour
3. What benefit does a CREATE TABLE statement add to a temporary
table?
- Access for
anyone to use the table
- Automated calculations
- Metadata about the data in the table
- Specific naming conventions
4. A data analyst is working with a spreadsheet from a furniture
company. To use the template for this spreadsheet, click the link below and
select “Use Template.”
The analyst inputs a function to find the number of product
prices that are less than $150.00. Which formula will return that result?
- =COUNTIF(G2:G30, “>=150”)
- =COUNTIF(G2:G30, “<150”)
- =SUMIF(G2:G30, “>150”)
- =SUMIF(G2:G30, “<150”)
5. You are working in a spreadsheet and use the SUMIF function
in the formula below as part of your analysis.
=SUMIF(A1:A25, ”<10”, C1:C25)
Which part of this formula is the criteria
or condition?
- =SUMIF
- A1:A25
- C1:C25
- ”<10”
Explanation: Within
the equation =SUMIF(A1:A25, "<10", C1:C25), the component "
<10" serves as the criterion or condition that is being considered.
When it comes to the range A1:A25, this is the condition that makes it
applicable. The SUMIF function will only do the summation of the values in the
corresponding range C1:C25 if the corresponding cells in the range A1:A25
satisfy the stated criteria, which in this instance is values that are less
than 10.
6. A data analyst is working in a spreadsheet and uses the
SUMPRODUCT function in the formula below as part of their analysis.
=SUMPRODUCT(A2:A10,B2:B10)
How does the SUMPRODUCT function calculate
the cell ranges identified in the parentheses?
- It adds the ranges, then multiplies them by the
last value in the second array.
- It multiplies the ranges, then adds the
sum of the products of the two ranges.
- It adds the values in the first range, then adds
the values in the second range.
- It multiplies
the values in the first range, then multiplies the values in the second
range.
Explanation: Using the formula =SUMPRODUCT(A2:A10, B2:B10), the SUMPRODUCT
function first determines the product of the components that correspond to the
arrays that have been supplied (A2:A10 and B2:B10), and then it adds up the
products of these items individually.
7. You create a pivot table in a spreadsheet containing movie
data. To use the template for this spreadsheet, click the link below and select
“Use Template.”
In order to find out how much box office revenue each genre
earned, you would use the SUM function in the Values menu to summarize the
data.
- True
- False
8. A data analyst uses the following query to perform basic
calculations on their data. The variables in the query have the following
values: yes_responses = 10, no_responses = 12, and total surveys = 22. What is the
value of the Responses_Per_Survey variable?
- 44
- 11
- 22
- 1
Explanation: Unfortuitously,
I am unable to recognize the particular inquiry that you are referring to. I
would be pleased to assist you in determining the value of the
Responses_Per_Survey variable if you could give the query or the mathematical
expression that is being used to generate the results of the investigation.
9. You are working with a database table that contains data
about music. The table includes columns for track_id, track_name, composer, and
milliseconds (duration of the music track). You are only interested in data
about the classical musician Johann Sebastian Bach. You want to know the
duration of each Bach track in seconds. You decide to divide milliseconds by
1000 to get the duration in seconds, and use the AS command to store the result
in a new column called secs.
Add a statement to your SQL query that
calculates the duration in seconds for each track and stores it in a new column
as secs.
What is the duration in seconds of the
track with Id number 3408?
- 193
- 120
- 307
- 153
Explanation: Regarding
the track IDs that are related with the number 3408, I do not have access to a
particular database or information about them. If you have a particular system
or context in mind, you may wish to review the documentation or data source
that is relevant to the situation in order to determine the duration in seconds
for the track that has serial number 3408. In the event that you give further
information on the system or the context to which you are referring, I will
make an effort to provide more precise advise.
10. You are working with a database table that contains invoice
data. The table includes columns for invoice_id and quantity (the number of
purchases included in each line item of an invoice). Each invoice contains
multiple line items. You want to find out the total number of purchases for
each invoice, and store the result in a new column as total_purchases.
You write the SQL query below. Add a GROUP
BY clause that will group the data by invoice Id number.
What is the total number of purchases for
the invoice with Id number 4?
- 2
- 4
- 14
- 9
11. You are working with a database table that contains invoice
data. The table includes columns for billing_country and total. You want to
know the average total price for the invoices billed to the country of India.
You decide to use the AVG function to find the average total, and use the AS
command to store the result in a new column called average_total.
Add a statement to your SQL query that
calculates the average total and stores it in a new column as average_total.
NOTE: The three dots (…) indicate where to
add the statement.
What is the average total for India?
- 5.37
- 5.78
- 5.64
- 6.02
Explanation: Following the calculation of the average total for those rows, this query also saves the result in the average_total column. The query filters the data to include only rows in which the billing_country is 'India'. Be sure to substitute the real name of your table with the one that is written in your_table_name.
