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.