Deck 7: Information Retrieval From Relational Databases

Question 1

Query by Example (QBE) requires the end user to be able to construct a complex SQL Statement.

Answer 1

False

Question 2

A horizontal subset includes only some of the rows in the table and all of the table's columns.

Answer 2

True

Question 3

Relational Algebra allows for multiple operations in a single query.

Answer 3

False

Question 4

A Microsoft Access dynaset looks and behaves like a table but is not stored as a table.

Question 5

Ad hoc querying is done primarily by professional database developers who use SQL.

Question 6

You can perform a right outer join on tables that do not share a common field.

Question 7

A full outer join will show records that contain NULL values.

Question 8

The following tables cannot be joined:

Question 9

The criteria field in the query design for Microsoft Access accepts Relational Algebra language syntax.

Question 10

A query in Microsoft Access takes a subset of data contained in a table and creates another table on which you can perform multiple queries.

Question 11

Microsoft Access uses only a QBE method for query creation.

Question 12

Every SQL statement begins with the word PROJECT.

Question 13

The SELECT component of a SQL query statement specifies which attributes are to be included in the answer to the query.

Question 14

An SQL query statement that accomplishes only the relational algebra PROJECT operation would not include a WHERE clause.

Question 15

A single SQL query statement can accomplish relational algebra select, project, and join operations.

Question 16

Right outer join queries can only be created in relational algebra.

Question 17

The answer to a query connecting two criteria with an AND logical operator will include all instances that meet either of the two criteria.

Question 18

A calculation that performs a mathematical operation within a single column of data is called an aggregation function.

Question 19

SUM, MAX, and COUNT are examples of aggregation functions that may be included in both SQL and QBE queries.

Question 20

A parameter query requires a user to change the design of the query to reuse it with new parameter values.

Question 21

Which of the following are the relational algebra operators that accomplish the logic underlying most basic queries?

A) SELECT, JOIN, FROM
B) OPEN, CLOSE, SELECT
C) PROJECT, SELECT, JOIN
D) OPEN, CLOSE, DELETE
E) SELECT, CLOSE, JOIN

Question 22

If a table contained 20 columns and you wanted to use only ten of the table's columns in a query you would use:

A) A vertical subset
B) A horizontal subset
C) A JOIN
D) An OPEN
E) A diagonal subset

Question 23

In SQL what character allows the database to know that the end of the query has been reached?

A) /
B) *
C) .
D) ;
E) ^

Question 24

Mathematical Comparison operators are used in which part of the SQL statement?

A) SELECT
B) WHERE
C) JOIN
D) PROJECT
E) FROM

Question 25

Which of the following SQL statements will sum the total number of items from the Inventory table's Quantity column?

A) Project Quantity (Sum) From Inventory;
B) Select Sum (Quantity) From Inventory;
C) Select (Inventory) From Quantity Column And Sum Select (Inventory) Where (Quantity Column)= N And Itemid.
D) And (Sum)/
E) Select Quantity Column From Inventory Where;

Question 26

Which of the following join types will produce only the rows for which two tables' common attribute values match exactly:

A) Equi-join
B) Right outer join
C) Inner join
D) Both equi-join and inner join
E) Left outer join

Question 27

Which of the following are Boolean Search operators?

A) >, =, <
B) AND, OR, NOT
C) *, *.*
D) EXCEPT, INCLUDING
E) MAYBE, <>, BEGINNING

Question 28

Which of the following SQL statements would produce a list of all employees from the Employee table that live in the Zip Code of 80112?

A) Select * From Employee Where EmployeeId = 80112.
B) Select * From Employee Where ZipCode = 80112;
C) Select * From Employee Where ZipCode = 80112 And EmployeeId Is D) Select * From ZipCode Where EmployeeId = 80112;
E) Select ZipCode From Employee Where EmployeeId = 80112;

Question 29

The following Query will produce

A) All employees who have been hired
B) Employees who have been hired and fired
C) The query will not produce anything because the data requested is incomplete
D) All employees who were hired within the range of dates the end user enters in prompt boxes provided by Access.
E) The employees who were fired within 30 days of being hired.

Question 30

Which operation would you use to total sales for each individual sales person.

A) GROUP BY
B) WHERE
C) EQUALS
D) BETWEEN
E) NOT

Question 31

Examine the relational tables depicted below. A user needs a query that includes InventoryStockID, and Description from the inventory table and the corresponding SaleIDs and Quantities Sold from the Stockflow1 table. Inventory items that have not yet been sold need to be included in the query result, with blanks showing for the corresponding Sale IDs and Quantities Sold. What kind of join is required to generate the desired result? Assume the Inventory table is the first table listed in the query statement (and is thus considered the "left" table) and the Stockflow table is the second table listed in the query statement (and is thus considered the "right"
Table).

A) A left inner join is required
B) A right inner join is required
C) A right outer join is required
D) No join is required
E) A left outer join is required

Question 32

Examine the Sale and Cash Receipt relational tables and the corresponding query solution below. Which of the following SQL query statements applied to those tables will result in the depicted solution?

A) SELECT CustomerID, Sum(Amount)FROM Sale;
B) SELECT CustomerID, Sum(Amount)FROM Sale GROUP BY CustomerID;
C) SELECT CustomerID, AmountFROM Sale;
D) SELECT CustomerID, Sum(Amount)FROM SaleWHERE CustomerID unique;
E) SELECT CustomerID, AmountFROM Sale GROUP BY Amount;

Question 33

What is the general name for a query that prompts the user to enter criteria to apply in selecting the rows of interest in a table?

A) Outer join query
B) Filter query
C) Select query
D) Parameter query
E) Inner join query

Question 34

Given the Sale and Cash Receipt relational database tables below, which of the following answers would be produced by the query statement
SELECT Sum(Amount)
FROM Sale
WHERE Date >=6/30/02;

A) Answer 1
B) Answer 2
C) Answer 3
D) Answer 4
E) Answer 5

Question 35

Given the Sale and Cash Receipt relational database tables below, which of the relational algebra operators are needed to determine the unpaid balance due for each sale? Assume the only fields the user wants to appear in the query solution are SaleID and Balance Due, and also assume the user wants sales with zero balances to be included in the query solution.

A) SELECT, PROJECT, and JOIN
B) PROJECT and SELECT
C) JOIN only
D) PROJECT and JOIN
E) SELECT and JOIN

Question 36

Given the following tables and query, what is the most likely result?

A) Answer Choice 1
B) Answer Choice 2
C) Answer Choice 3
D) Answer Choice 4

Question 37

The approach to providing information for decision makers that allows users to create their own queries as needed is called

A) Database design
B) Predetermined query and interface design
C) Ad hoc querying
D) Scheduled querying
E) Three-pronged presupposed querying

Question 38

Relational algebra is most often used in practice to

A) Lay the foundation of the underlying logical procedures in querying
B) Create queries that conform to the SELECT….. FROM….. WHERE….. format
C) Create queries using a point-and-click interface that requires little user expertise
D) Create queries that perform only mathematical operations
E) Create queries that perform only logical comparison operations

Question 39

Under what conditions does a valid SQL statement not include a WHERE clause?

A) If the query is intended to accomplish only a relational algebra selection operation.
B) If the query is intended to accomplish only a relational algebra project operation.
C) If the query is intended to accomplish only a relational algebra join operation.
D) If the query is intended to combine a relational algebra join with a relational algebra selection operation.
E) No conditions exist under which an SQL statement is valid without including a WHERE clause; i.e., all SQL statements must include a WHERE clause.

Question 40

In Microsoft Access Query By Example, the inclusion of Criteria on two different lines as illustrated in the following query will

A) Act as the AND logical operator and will include only those records that meet both of the criteria.
B) Act as the AND logical operator and will include all records that meet either of the two criteria.
C) Act as the OR logical operator and will include only those records that meet both of the criteria.
D) Act as the OR logical operator and will include all records that meet either of the two criteria.
E) Act as the NOT logical operator and will include only those records that meet neither of the two criteria.

Question 41

What tool may be used in Microsoft Access querying to create horizontal calculations by pointing and clicking on field names and on mathematical operators?

Question 42

What type of query in Microsoft Access allows a user to run the same query with different specified criteria without changing the query design?

Question 43

Given the following table schema, create a query using Structured Query Language that displays the total amount of cash receipts for the month of June, 2010.

Question 44

Given the following tables, what are the first and last names of the customers who have bought the "large, basic" product?

Question 45

What will be the result of the following SQL statement applied to the CashDisbursement table?
CashDisbursement
SELECT Sum(Amount)
FROM CashDisbursement
WHERE Date <=2/05/2010 AND PayeeID > 40204;

Question 46

What will be the result of the following SQL statement applied to the CashDisbursement table?
CashDisbursement
SELECT Sum(Amount)
FROM CashDisbursement
WHERE Date <=2/05/2010 OR PayeeID > 40204;

Question 47

What will be the result of the following Microsoft Access QBE query of the CashDisbursement table?

Question 48

What will be the result of the following Microsoft Access QBE query of the CashDisbursement table?

Question 49

Three tables exist in a relational database as follows:
Which records from the Driver table would a Left Outer Join query of Driver (left table) to AssignmentDriverTruck (right table) include in its answer?

Question 50

Three tables exist in a relational database as follows:
What would a full outer join of these three tables include in its answer? Give a complete picture of what the answer would look like, using only the Student ID and CourseID attributes, and assuming Student is the left table, StudentTakesCourse is middle, and Course is the right table.

Question 51

Write the generic format of a SQL query statement and explain the purpose of each clause in the SQL statement. Include in your explanation a description of which relational algebra operations are accomplished by each component of a SQL query statement.

Question 52

A database includes the following tables. Design a query to identify the notebook computers whose condition worsened while they were checked out. The higher the number, the better the condition of the computer. Include the computer's ID, the ID of the employee who checked it out, the date it was checked out, the condition in which it was checked out, the date it was checked back in, and the condition in which it was checked back in. You may either write the query in SQL code or fill in the appropriate fields in the blank QBE design grid that follows the tables. The blank QBE design grid includes the "Total" line in case the query requires it; if the query does not use it, be sure to cross it off.

Question 53

A database includes the following tables. Design a query to display a count of the number of times each computer has been checked out, including zeros for those that have not yet been checked out. Include each computer's ID and the number of times it has been checked out. You may either write the query in SQL code or fill in the appropriate fields in the blank QBE design grid that follows the tables. The blank QBE design grid includes the "Total" line in case the query requires it; if the query does not use it, be sure to cross it off.

Question 54

A database includes the following tables and design for a query, Query3, illustrated in both Microsoft Access QBE and SQL formats. (a) What is the result of the Query3 design applied to the tables?
(b) If the goal is to identify the notebook computer makers of the computers that have not required any maintenance (including the NCID and the Maker name), does Query3 provide the needed information?
(c) If the answer to (b) is no, what errors need to be corrected in Query3?
(d) Once the query is corrected, what should the result look like?

Question 55

List the four types of JOINS that can be used in querying to combine data from multiple relational database tables. Explain how each type of JOIN works.