Query Quiz

Why do businesses use queries?

answer business questions and generate reports for decision-making

what is an example of multiple-table query?

- What sandwiches are larger than 8 ounces? 1. Merge category data into product data 2. Filter to show only sandwiches AND only products larger than 8 ounces

Assume you have a table that contains the population figures for all cities in the United States. What is the quickest way to find the city with the lowest population?

sort ascending on population column

Examples of data queries in a report

- For example, a quarterly sales report would show the total sales for each division, salesperson, geographical region, or product category - A quarterly sales report will very rarely show each individual sale made during the quarter - Thus, even though the report may only be a few pages long, the amount of data that was queried to produce the report may cover thousands or millions of observations

Examples of parameters

- How many orders occurred each month in 2018? - What was the average order quantity among customers from Ohio?

Multiple-table queries?

- If multiple tables have the exact same columns and store similar information, the tables can be appended to form a single table. - For queries that require data from multiple different tables, the tables must first be merged together using a join • Merge selects all rows from both participating tables or queries as long as there is a match between the specified columns

what is the goal of multiple-table queries?

- Moving information into a single table so that single table queries can be applied

Meeting Multiple Filtering Criteria

- Multiple filters can be specified using the Advanced Menu under Filtering... - Each criteria referred to as a "clause" - When combining multiple clauses, must specify whether they are connected via AND (more conservative) or OR (less conservative) - AND: both test must be true - OR: only one test have to be true

Single table theories

- Single-table queries use only one table as their data source - Select the existing table that contains the data needed to answer the business question - Keep in mind that PKs and FKs, while they may have the same name, have different meanings

Shape Data

- Transform a data source into the form and format that meets your needs - Remove any number of rows from the top - Remove from the bottom - add columns - split columns - replace values

Aggregating within groups

- Using the Group By menu, specify a column that contains the grouping column and what column should be aggregated and how - Any type of aggregation that can be performed on an entire column can be performed within groups - The query will automatically determine how many groups exist by examining the grouping column's distinct values

Sorting

- arranges the rows in the query by examining the values in a specified column - Ascending: A to Z, Lowest Number to Highest Number - Descending: Z to A, Highest Number to Lowest Number

Group rows

- group the values in multiple rows into a single value - can be useful when summarizing the number of products offered, the total sales, or the count of students.

Pivot columns

- if you need to know how many different products you have in each product category, you can quickly create a table the does precisely that.

Filtering

- removing values that meet specified criteria - Can specify the entire cell contents ("is"), partial cell contents ("contains"), or starting with a specific character ("begins with") - Can specify operators: equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to

What is a query?

- the identification and transformation of data to answer a question - usually have parameters (constraints) that need to be met - Queries use tables or other queries as their data source

Bob is responsible for maintaining the inventory for a local coffee shop. One critical inventory item is to-go coffee cups. Bob's goal is to maintain enough inventory so that the supply of cups does not run out before he receives his next weekly order. At the same time, he cannot order too many cups as he will run out of the storage space. To get an accurate estimate of the number of cups to order each week. Bob relies on the coffee shops historical sales order data. Specifically, Bob prepares a query to determine the average number of cups used per week over the past year. Based on the scenario, how many rows of data should Bob expect to see in his analysis?

52

For every sales transaction, a grocery store captures the quantity sold and Price attributes for each item sold. Which of the following options is the proper way to calculate Revenue per transaction?

Add a custom column that multiplies Quantity Sold column by Price column

Janet has been asked by the CEO to provide a single data source that aggregates data from four different business units. Each business unit collects data in the same format, Specifically, each data source contains the same number of columns, in the same order, with each column having data that means the same thing. Which strategy does Janet need to take in order to create a single data source from the four data sources?

Append

Parameters

important elements of the question that need to be part of the query

Average

returns average of the column's values

Maximum

returns highest value in the column

Minimum

returns lowest value in the column

Median

returns median of the column's values (middle value)

standard deviation

returns standard deviation of the column's values

Count distinct values

returns the number of different or unique values in a column

Count Values

returns the number of values in the column

Sum

returns total of the column