AIS chapter 9
What are examples of aggregation functions?
-COUNT - summarizes the number of rows that contain a given value in the field -AVERAGE - computes the arithmetic mean value of all rows included in the answer -SUM - computes the arithmetic sum of all rows included in the answer -MIN - identifies the minimum (lowest) attribute value for the field -MAX - identifies the maximum (greatest) attribute value for the field
What is querying?
-It is asking questions about the data in the database and manipulating or combining the data in different ways -We can isolate certain rows in tables, we can isolate certain columns in tables, we can join tables together, we can create calculations based on various data items, etc.
Each query statement follows the same structure:
-SELECT attribute name(s) -FROM table name(s) -WHERE criteria is met
SQL's WHERE component
-isolates rows -Also can help accomplish join -may be omitted for single-table queries that retrieve all rows
Mathematical comparison operators
= equal to < less than <= less than or equal to > greater than >= greater than or equal to <> not equal to (or != in some software)
If a table contained 50 rows and you want to use only 20 of the rows that meet a specific criterion, you would create a query to get
A horizontal subset
What logical operators may queries include?
AND, OR, and NOT
If Table A is on the left and Table B is on the right, a right outer join will include in its answer
All the rows from Table B, with the corresponding detail of Table A for those rows for which the value of the two tables' common attribute match exactly.
What is the difference between an aggregation (vertical calculation) and a horizontal calculation?
An aggregation (vertical calculation) combines numbers within a single column into a calculation such as a sum, an average, a count, etc. A horizontal calculation combines numbers across columns within a single row.
Aggregation function
An aggregation function summarizes the data values within a field (column)
What special operators may queries include?
BETWEEN, NULL, and EXISTS
Logical operator
Boolean search terms used in queries to define which records are included in the query result; examples include AND, OR, and NOT
How do you query for Cash Receipts from Customer C-2 with row filtering?
Create query. Add Cash Receipts table. Add everything to Field columns. for CustomerNumber, ="C-2" for criteria. Save as CashReceiptsFromCustomerC-2.
How do you query for Cash Account# and Balances >=$50,000 using mathematical comparison operators?
Create query. Add Cash table. Add AccountNumber and Balance to Field column. For Balance, >=50000 for criteria. Save query as CashBalanceGreaterThanOrEqualTo50000.
How do you query for All detail of customers and their salespeople using inner join?
Create query. Add Customer and Salesperson tables. EmployeeNumber and SalespersonNumber should be joined. Add everything from both tables to Field column. Save query as CustomerSalespeopleDetails.
How do you query for Inventory-Sale Line Item Extension using Horizontal calculation?
Create query. Add InventorySaleStockflow table. Add InventoryItemID, SaleNumber, Quantity, ActualPrice to Field column. In next Field column, click builder. Multiply Quantity and ActualPrice. Rename "LineExtention" before : equation. Save query as SaleInventoryLineExtentions.
How do you query for Sales Not made by E-10 using mathematical comparison on character attribute?
Create query. Add Sale table. Add SaleNumber, Amount, and SalesRepNumber to Field column. For SalesRepNumber, <>"E-10" for criteria. Save query as SalesNotByE10.
How do you query for Customer#, name, and salesperson# using column filtering?
Create query. Add customer table. Add CustomerNumber, Name, and SalespersonNumber to Field column. Save query as CustomerProject.
How do you query for Details of all sales, related cash receipts using outer (left) join?
Create query. Add sale and Sales-CRDuality tables. SaleNumber and SaleNumber should be joined. Click on the join. Change to number 2 to make outer join. Add everything from both tables to Field column. Save query as SalesAndRelatedCR.
How do you query for Sales made before July 31 by Sales Rep E-10 using WITH operator?
Create query. Add sale table. Add Date and Amount to Field column. Where for Date. Sum for Amount. For Date, Between #7/15/2014# AND #7/31/2014# for criteria.
How do you query for Sales made before July 31 by Sales Rep E-10 using AND operator?
Create query. Add sale table. Add SaleNumber, Amount, Date, and SalesRepNumber to Field column. For Date, <#7/31/2014# for criteria. For SalesRepNumber, "E-10" for criteria. Save query as SalesByE10beforeJuly31.
How do you query for Sales made before July 31 by Sales Rep E-10 using OR operator?
Create query. Add sale table. Add SaleNumber, Amount, Date, and SalesRepNumber to Field column. For Date, <#7/31/2014# for criteria. For SalesRepNumber, "E-10" for or. Save query as SalesByE10beforeJuly31.
A query's answer in Microsoft Access is referred to as a
Dynaset
Which of the following is necessary for effective information retrieval? A) The database is well designed B) The query designer has a thorough knowledge of the database table structures and the nature of the data in the tables. C) The query designer adequately understands the desired output. D) The query designer knows the querying language used to retrieve information from the enterprise's database. E) All of the above are necessary for effective information retrieval.
E) All of the above are necessary for effective information retrieval.
What is the advantage to creating a query that includes a date constraint as a parameter query?
Each time the user needs to run the query for different dates, the user does not need to change the query design; but rather the user is prompted to enter the desired date upon running the query.
What is an SQL example of inner join?
Find all detail of all customers and all available details of each customer's salesperson
What is an SQL example of (left) outer join?
Find all details of all sales and the cash receipt number and amount applied of any cash receipts related to those sales
How would you column filter?
Find all the other amounts that correlate to one column. For example: Find the customer number, name, and salesperson number for all customers
Explain how incorrect information retrieval results may be obtained even from a perfectly designed database.
If a database is perfectly designed but the query designer doesn't understand the database design or the nature of the data in the tables, or if the query designer is not adept with the database's query language, the query designer is likely to make mistakes in creating the query. Sometimes querying mistakes are not readily evident and the incorrect results are used for decision-making, often resulting in bad decisions.
Explain why a poor database design will result in information retrieval problems.
If a database is poorly designed, connections between tables will be faulty or nonexistent and the tables will not be able to be joined to satisfy some information needs.
Give an example of a query for which you would need to use a left join instead of an inner join.
Left joins keep unmatched values from the left table and fill in the corresponding information from the right table for those left table values that do have matches. Thus any example of joining two tables for which some values of the left table will have matches in the right table and some won't is appropriate. One example is a join of Employees and Training Courses the employees have taken (not all employees will have taken a training course buy you would still want them on the list so that it is clear they haven't taken any). Another example is a join of Library Book Borrowing and Library Book Return events, as some book borrowings will not yet have resulted in returns. Students may come up with a variety of other examples.
What are the advantages (and/or disadvantages) of QBE compared to SQL?
QBE has more of a "point and click" feel, and allows the user to provide an example of what they want the answer to their query to look like. The QBE user doesn't need to learn SQL code in order to generate many useful queries. However, QBE can seem easier than it really is and may be more likely than SQL to cause a false sense of security for untrained users.
Which of the following query interfaces is intended to be more point-and-click in nature and to require less user expertise?
Query By Example
What does querying provide?
Querying provides the power of the relational database model
Create a query in SQL that will list the last name, first name, and telephone number for all the customers who live in Florida. States are entered in the database using their two digit postal abbreviation (Florida is FL).
SELECT LastName, FirstName, Telephone FROM Customer WHERE State = FL;
In SQL, every information retrieval query follows what structured, predefined syntax?
SELECT attribute name(s), FROM table name(s), WHERE condition criteria is met;
Which SQL statement will multiply Table A's Field P by Table A's Field Q?
Select (Field P * Field Q) From Table A;
SQL statement for Special Operators with EXISTS
Select * From Cash Where Balance EXISTS;
SQL statement for Special Operators with NULL
Select * From Cash Where Balance IS NULL;
SQL statement for Inner Join: Find all details of all customers and all available details of each customer's salesperson
Select * From Customer, Salesperson Where Customer.SP# = [Salesperson.Employee Number]; Select * From Customer INNER JOIN Salesperson ON Customer.SP# = [Salesperson.Employee Number];
SQL statement for (left) Outer Join: Find all details of all sales and the cash receipt number and amount applied of any cash receipts related to those sales
Select * From Sale LeftJoin [Sale-CashRecDuality] Where [Sale.Sale#]=[Sale-CashRecDuality.Sale#]; Select * From Sale LeftJoin [Sale-CashRecDuality] Where [Sale.Sale#]=[Sale-CashRecDuality.Sale#];
SQL statement for Row Filtering: Find the cash receipts from Customer #2 (keeping all the details of those cash receipts)
Select * From [Cash Receipt] Where [Customer Number] = C-2;
SQL statement for Mathematical Comparison Operators
Select Account#, Balance From Cash Where Balance>=50000;
SQL statement for Column Filtering: Find the customer number, name, and salesperson number for all customers
Select Customer#, Name, SP# From Customer;
SQL statement for Mathematical Comparison Operators on Character Attributes
Select Sale#, Amount From Sale Where SalesRep# <> E-10;
SQL statement for Special Operators
Select Sale#, Amount, Date From Sale Where Date BETWEEN 7/1 and 7/31;
What is the standard format of a SQL query statement?
Select ______ From______ Where_______;
How would you row filter?
Select and include all columns from table. For example: Find the cash receipts from customer #2 (keeping all the details of those cash receipts)
What is currently the most commonly used data manipulation language?
Structured Query Language (SQL)
What are two query languages?
Structured Query Language (SQL) and Query by Example (QBE)
What does the asterisk (*) in SQL mean?
The asterisk (*) is a wildcard symbol that requests inclusion of all attributes
Which of the following is true regarding the use of an outer join in SQL?
The outer join must be specified as a Left Join or a Right Join.
Structured Query Language (SQL)
The user enters commands according to a pre-defined syntax to retrieve desired data.
Query By Example (QBE) (Query Tool)
The user starts with a sample of the table(s) columns and marks the fields he or she wants to include in the answer. Defaults are available for summarizing and manipulating the data.
Outer join
a combination of tables based on a common attribute that includes unmatched records from both sides; accomplishes a set union of the tables
Left join
a combination of tables based on a common attribute that includes unmatched records from the first table in the join and does not include unmatched records from the second table in the join; is a partial outer join, is also called left outer join
Right join
a combination of tables based on a common attribute that includes unmatched records from the second table in the join and does not include unmatched records from the first table in the join; is a partial outer join, is also called right outer join
Vertical calculation
a computation that is a summarization of data values within a single column; also called an aggregation function
Equi-join
a join that combines the tables together based on a common attribute, keeping only those rows for which the data values of the common attribute match exactly; also called an inner join; accomplishes a set intersection of the tables.
Aggregation functions
a mathematical operation used in querying to summarize information within a single column; also called vertical calculation
SQL view
a mode for viewing the underlying SQL statement for a query; even if a query was created in QBE mode, Microsoft Access generates a corresponding SQL statement that the user may view to evaluate the query's logic
Datasheet view
a mode that presents a relational table or a query result in row/column format
Vertical subset of a table
a part of a table that includes only some of the table's columns (but includes all the rows)
Horizontal subset of a table
a part of a table that includes only some of the table's rows, but includes all the columns
Parameter query
a query in which variables are used in lieu of data values as part of the query's selection criteria; allows the user to specify the data value to be used each time the query is run, thereby allowing re-use of the same query many times for different decisions
Dynaset
a query's result; looks and behaves like a table but is not actually stored as a table; it is generated as a view each time the query is run
Group by
a querying function used to create subgroups to which aggregations may be applied; a means for creating subtotals
PROJECT
a relational algebra operator that specified a vertical subset to be included in the query result
SELECT (in relational algebra)
a relational algebra operator that specifies a horizontal subset to be included in the query result
Query
a request for information submitted to a database engine
Date constraint
a restriction placed on a date field in a query to limit the query results to include only records for which the date values meet the restriction
Horizontal calculation
a row computation in a query that combines data from two or more separate columns of one or more tables
Show Table window
a screen from which the user may choose which tables to include in the relationship layout or in a query
Join properties window
a screen that appears when a user double-clicks on a join line to reveal whether the join is an inner join, a left join, or a right join; a user can change the join type in this window; used in the relationship layout and in query designs
What is * ?
a wild card indicating all columns should be included)
AND
accomplishes a set intersection - answer includes all instances that meet BOTH conditions
OR
accomplishes a set union - answer includes all instances that meet one condition and all instances that meet the other condition
Expression builder
an application within Microsoft Access that assists the user in creating horizontal calculations within queries
Ad hoc querying
direct retrieval of information by end-users from a database whereby the retrieval was not planned (i.e. no pre-formulated queries or interfaces were developed in anticipation of needing the information)
Design view
for relational tables, a mode that displays details about the fields of a table and allows the user to specify various design parameters such as which fields comprise the primary key, whether a field is set to required data entry, and the data type for a field; for queries, a mode that depicts the logic of a query in QBE format
NOT
identifies instances that do not meet one or more conditions
Where are Mathematical comparison operators typically included?
in the WHERE clause of the SQL statement, and may be used on all types of fields
Outer join (right or left)
includes all records (rows) from one table, and matches those records from the other table for which values in the joined fields are equal
Inner join
includes only the records (rows) from both tables that have the exact same values in the fields that are joined
SQL's FROM component
is used for identifying the table(s) involved
SQL's SELECT component
isolates columns
"Horizontal" calculations
mathematically combine values from different fields for each row
Information retrieval
repossession or capture of data that was previously entered into a database or other data storage structure
SELECT-FROM-WHERE (in SQL)
the format of SQL queries; the Select clause specifies a vertical subset to be included in the query result; the From clause specifies which tables are to be queried and any sub-grouping to be done; the Where clause specifies a horizontal subset to be included in the query result, and if multiple tables are included, helps to define the join
Query grid
the lower half of the QBE view into which fields are dragged and in which aggregations or horizontal calculations may be created to establish the desired logic for a query
Relational Algebra
the original data manipulation (querying) language that was constructed based on set theory and predicate logic as part of the relational database model; primary operators include Select, Project, and Join; however, other operators are also part of the relational algebra
What should Horizontal calculations NOT be included in ?
the same query as an aggregation function
Query window
the screen in which queries are created; user may toggle back and forth between QBE design, SQL design, and Datasheet (result) views within the query window
Data manipulation
the specification of operations to be performed on one or more data fields to obtain additional information; may create aggregations, horizontal calculations, subset selections, and so forth
Join
to combine separate but related tables by linking them on their common attributes; one of the three primary relational algebra operators discussed in this text
BETWEEN
used to define the range limits. The endpoints of the range are included.
EXISTS
used to retrieve attributes for which the value is not null.
NULL
used to retrieve attributes for which the value is null.
