Deck 7: Workshop 7: Aggregated Calculations, Subquerying, and Sql

Question 1

If you wanted to perform an aggregated calculation on a ProductPrice field,you would use the ________ function.

A)Sum
B)Count
C)Total
D)You cannot perform aggregated calculations on text or numeric fields.

Answer 1

A

Question 2

Many managers require ________ data in order to make decisions.

A)numeric
B)table
C)summarized
D)textual

Answer 2

C

Question 3

The SQL ________ clause includes the Name of the table(s)from which the information is retrieved.

A)SELECT
B)FROM
C)WHERE
D)HAVING

Answer 3

B

Question 4

A field in the Group By field list can refer to any field in a table except the ________ data types.

A)OLE Object and Memo
B)Attachment and Hyperlink
C)OLE Object and Attachment
D)Memo and Hyperlink

Question 5

The ________ aggregate function limits the results without grouping the data by the field.

A)Last
B)Where
C)First
D)Expression

Question 6

A(n)________ returns a single value calculated from multiple values in a column.

A)aggregated calculation
B)summarized calculation
C)subquery
D)SQL HAVING clause

Question 7

The SQL ________ clause specifies which table rows are used.

A)SELECT
B)WHERE
C)AS
D)HAVING

Question 8

The SQL ________ clause includes one or more columns from which data are retrieved.

A)SELECT
B)FROM
C)WHERE
D)AS

Question 9

By using the ________ clause,you can combine records with identical values in a specified field list into a single record.

A)As
B)Having
C)Where
D)Group By

Question 10

Because more sophisticated queries require more advanced manipulation of data,you may need to create a(n)________.

A)SQL query
B)crosstab query
C)union query
D)subquery

Question 11

________ is the number of items sold or services rendered during normal business hours.

A)Sales revenue
B)Net revenue
C)Physical volume
D)Sales volume

Question 12

The ________ function truncates the decimal and returns only the numbers to the left of the decimal.

A)Round
B)Int
C)Number
D)Precision

Question 13

When writing SQL,the ________ selects and returns all fields in the table when you run the query.

A)semicolon
B)ampersand
C)asterisk
D)dollar sign

Question 14

Which of the following is NOT true regarding the Group By clause?

A)The Group By clause needs to be used with Sum,Count,and so on to display aggregated data.
B)The Group By clause can be used to summarize duplicate data.
C)By using the Group By clause,you can combine records with identical values in a specified field list into a single record.
D)The Group By clause allows you to limit the results of your query by specifying criteria that field values must meet without using that field to group the data mathematically.

Question 15

If you wanted to perform an aggregated calculation on a LastName field,you would use the ________ function.

A)Sum
B)Count
C)Total
D)You cannot perform aggregated calculations on text fields.

Question 16

The ________ can compare the portion of the gross revenue to the total gross revenue.

A)revenue
B)percentage of sales revenue
C)net income
D)percentage of physical volume

Question 17

Which of the following is NOT true in regard to calculating physical volume?

A)The basic formula for calculating volume is length*width*depth.
B)If the item is not a perfect rectangle,take the average width and length.For example,if the object is 6 inches at its widest point and 2 inches at its narrowest point,use 4 inches for the width.
C)If you are wrapping the item in bubble wrap,take your measurements before it is wrapped.
D)If you take your measurements in inches,your final calculation will be in cubic inches.If you take them in centimeters,your final calculation will be in cubic centimeters,and so on.

Question 18

The ________ clause allows you to limit the results of your query by specifying criteria that field values must meet without using that field to group the data mathematically.

A)As
B)Having
C)Where
D)Group By

Question 19

Which of the following will NOT calculate the same answer for (Sum([Fee])*0.38)?

A)Sum([Fee]*(1-.38))
B)Sum([Fee]*.62)
C)Sum([Fee]*1.00-0.38)
D)Sum([Fee]*1.62)

Question 20

Which of the following is NOT an example of a common aggregate function?

A)Sum
B)Avg
C)Product
D)Count

Question 21

The ________ is the easiest way to create a crosstab query.

A)Crosstab Query Wizard
B)Union Query Wizard
C)SQL Query Wizard
D)Subquery Wizard

Question 22

Even though you can select all the fields from a table,you cannot use a(n)________ in a query in which you are using the GROUP BY clause.

A)semicolon
B)asterisk
C)dollar sign
D)ampersand

Question 23

Many programs use a modified version of SQL.If standard SQL is written in SQL view,the query will ________.

A)run,but return inaccurate data
B)cause an error and not run
C)cause an error,but still run
D)still run properly and return the correct dataset

Question 24

A(n)________ is a special type of query that can be created when you want to describe one number in terms of two other numbers.

A)subquery
B)union query
C)crosstab query
D)SQL query

Question 25

The following SELECT statement: SELECT EmpFirstName,EmpLastName
FROM tblEmployee
WHERE EmpLastName Like "[D-G]*";
Would list the EmpFirstName and EmpLastName that ________.

A)begins with the letters D,E,F,and G sorted in ascending order
B)begins with the letters D,E,F,and G sorted in descending order
C)begins with the letters D,E,F,and G not sorted in any particular order
D)begins with the letters E and F

Question 26

A crosstab query is different than the aggregate functions that you have been completing thus far because it groups the aggregates by the ________ headings.

A)column and row
B)field and row
C)record and row
D)column and field

Question 27

When working with SQL,if a field includes an aggregate function,you need to use a(n)________ clause,which specifies the aggregated field criteria and restricts the results based on aggregated values.

A)WHERE
B)HAVING
C)FROM
D)AS

Question 28

When writing SQL,you cannot use a renamed field in the ________ clause.You must use the original field name.

A)AS
B)FROM
C)SELECT
D)HAVING

Question 29

The following SELECT statement: SELECT *
FROM tblCustomers
WHERE LastName Like "smith";
Would list ________.

A)all customers first and last names from tblCustomers with a last name that begins with "smith"
B)all fields from tblCustomers with a last name that begins with "smith"
C)all fields from tblCustomers where the customers' last name is "smith"
D)all fields from tblCustomers where the customers' last name contains "smith"

Question 30

The following SELECT statement: SELECT ProductName,ProductPrice
FROM tblInventory
WHERE ProductName Like "pr*";
Would list the ProductName and ProductPrice that ________.

A)begins with the letters "pr",such as pretzel,prime rib,and prosciutto
B)begins with the letters "pr",such as pretzel,prime rib,and prosciutto sorted in descending order
C)begins with the letters "pr",such as pretzel,prime rib,and prosciutto sorted in ascending order
D)contains the letters "pr",such as pretzel,prime rib,and prosciutto anywhere in the field

Question 31

Which of the following is NOT true in regard to crosstab queries?

A)Crosstab queries can be a useful way to present data in a compact and summarized format.
B)Crosstab queries are the same type of query as those which perform aggregate functions.
C)Crosstab queries are useful for summarizing data,calculating statistics,identifying bad data,and looking for trends.
D)A crosstab query is a special type of query that can be created when you want to describe one number in terms of two other numbers.

Question 32

A subquery is usually added in the ________ clause of the SQL SELECT statement.

A)SELECT
B)FROM
C)AS
D)WHERE

Question 33

Which of the following is NOT true regarding inner joins?

A)The INNER JOIN clause is used to return only the rows that actually match based on the join predicate.
B)An inner join is a join in which the values in the columns being joined are equal.
C)Inner joins are subdivided into left inner joins,right inner joins,and full inner joins,depending on which table rows are shown in the dataset.
D)The output of an inner join will include where the two tables intersect or overlap.

Question 34

Which of the following is NOT true in regard to writing a SQL subquery?

A)Most of the time,a subquery is used when you know how to search for a value using a SELECT statement but do not know the exact value.
B)A subquery is usually added in the WHERE clause of the SQL SELECT statement.
C)A SQL subquery is a separate SELECT statement that is nested outside the main SELECT statement.
D)This type of query is an alternate way of returning data from multiple tables and will be performed once for each row of the resulting dataset.

Question 35

When you create a query in Design view,Access automatically generates the ________ in the background.

A)Form
B)Report
C)SQL statement
D)Subquery

Question 36

Which of the following is NOT true in regard to the Crosstab Query Wizard?

A)If you want to use fields from multiple tables,you need to add the tables in Design view.
B)You cannot create expressions in the wizard.
C)You cannot add a parameter prompt.
D)You can specify a list of fixed values to be used as column headings.

Question 37

Which of the following is NOT true regarding outer joins?

A)The OUTER JOIN clause returns all rows from two or more of the tables within the FROM clause,as long as those rows meet any WHERE or HAVING search conditions.
B)The OUTER JOIN clause is used whenever multiple tables that are accessed through a SQL SELECT statement returns all of the records from one table and only those records from the other table where the joined fields match.
C)The LEFT JOIN clause is used when you want to return all rows in the left table,even if no matching rows exist in the right table.
D)The FULL OUTER JOIN clause is not supported in Microsoft Access.

Question 38

The WHERE clause,which is optional in a SELECT statement,narrows the query results by specifying which ________ in the table will be returned in the dataset.

A)fields
B)rows
C)aggregated data
D)aggregated calculation

Question 39

When working in SQL,other ________ can be used,such as AND,OR,or IN.

A)Group by methods
B)aggregated calculations
C)operators
D)subqueries

Question 40

A(n)________ is used to query unrelated tables or queries and combine the results into a single dataset.This type of query is different from querying related tables as it combines two SQL SELECT statements.

A)subquery
B)crosstab query
C)SQL query
D)union query

Question 41

Another way that the Group By clause can be used is to take a count,such as a count of products or a count of customers.

Question 42

Sum can only be performed on number and currency fields.Count can be done on any field data type,except number and currency fields.

Question 43

When you edit a crosstab query in Design view,you use the ________ rows in the design grid to specify which field value becomes the column heading,which field values become row headings,and which field's value to calculate.

A)Column and Row
B)Criteria and Or
C)Total and Crosstab
D)Total and Or

Question 44

The Show check box has to be manually cleared once you select "Where" because Access cannot display a field when it is included in an aggregate function.

Question 45

The benefit of learning SQL is that once you know it,you can easily adapt to other relational database management systems.

Question 46

When writing SQL,the number sign selects and returns all fields in the table when you run the query.

Question 47

Revenue is income that a company receives from its normal business activities such as the sale of goods and services to customers.

Question 48

Each query that you create has an underlying SQL statement,which means that even when you create a query in Design view,Access automatically generates the SQL statement in the background.

Question 49

When writing SQL,be certain to write each clause of the SELECT statement on separate lines or the query will not run.

Question 50

A subset query is a select query that is nested inside of another select query.

Question 51

Net income is one of the most important pieces of information that a company uses to learn more about its financial progress.

Question 52

The Group By clause has to be used with Sum,Count,and so on in order to display aggregated data.

Question 53

Sales volume is the number of items sold or services rendered during normal business hours.All these figures would be taken over a specific period-week,month,year,and so on-and can be expressed in either dollars or percentages.

Question 54

The SQL SELECT clause includes one or more columns from which data are retrieved.

Question 55

The SQL AS clause includes the Name of the table(s)from which the information is retrieved.

Question 56

The WHERE clause,which is required in a SELECT statement,narrows the query results by specifying which rows in the table will be returned in the dataset.

Question 57

An aggregate function differs from a calculated field in that when you create a calculated field,you are calculating or summarizing data across a single row at a time.

Question 58

The Where function allows you to limit the results of your query by specifying criteria that field values must meet without using that field to group the data mathematically.

Question 59

The SQL WHERE clause specifies which table rows are used.

Question 60

Which of the following is NOT a reason to work in Design view when creating or editing a crosstab query?

A)When you want to use fields from multiple tables or queries
B)When you want to specify column headings
C)When you want to add a parameter to your query
D)When you want to add an expression to your query

Question 61

A union query is used to query unrelated tables or queries and combine the results into a single dataset.

Question 62

The salon and spa's management team wants to create a phone directory from both the Client table and Employee table of data.Management should use a crosstab query.

Question 63

The ________ is automatically cleared once you select "Where" because Access cannot display a field when it is included in an aggregate function.

Question 64

The OUTER JOIN clause is used to return only the rows that actually match based on the join predicate.

Question 65

A(n)________ simply is a select query that is nested inside of another select query.

Question 66

Joins are performed based on a predicate,which specifies the condition to use in order to perform a join.

Question 67

The Crosstab Query Wizard is the easiest way to create a crosstab query because all settings can be established within the wizard.

Question 68

________ is one of the most important pieces of information that a company uses to learn more about its financial progress.

Question 69

________ Language is an internationally recognized standard database language used by many relational databases.

Question 70

The INNER JOIN clause returns all rows from at least one of the tables within the FROM clause,as long as those rows meet any WHERE or HAVING search conditions.

Question 71

When you work in Design view of a crosstab query,you have more control over your query design,which enables features to be added that are not available in the wizard.

Question 72

A(n)________ returns a single value calculated from multiple values in a column.

Question 73

The ________ function truncates the decimal and returns only the numbers to the left of the decimal.

Question 74

Another way a(n)________ clause can be used is to summarize duplicate data.

Question 75

A union query is a special type of query that can be created when you want to describe one number in terms of two other numbers.

Question 76

When you edit a crosstab query in Design view,you use the Total and Crosstab rows in the design grid to specify which field value becomes the column heading,which field values become row headings,and which field's value to calculate.

Question 77

When working with SQL,if a field includes an aggregate function,you need to use a HAVING clause,which specifies the aggregated field criteria and restricts the results based on aggregated values.

Question 78

Each query that you create has an underlying SQL statement,which means that even when you create a query in Design view,Access automatically generates the SQL ________ in the background.

Question 79

The word ________ simply means a summative calculation,such as a total or average,or summarizing data.

Question 80

Everything you have learned how to do in Design view can also be done in SQL view.