Midterm Exam: Tuesday, September 28

1
Midterm Exam Tuesday, September 28

• Covers all course material through todays
  lecture
• Homeworks 1 - 5 Problem 1 of Homework 6
• All lectures
• All handouts
• Readings from Stair and Reynolds and Rob and
  Coronel
• Will be pencil-and-paper
• No computers or calculators are necessary
• Will include
• Relational database design (about 60)
• Short answer/True-False (about 30/10)
• Review sessions will be held
• Wednesday 9/29, 530 PM - 7 PM, Room 255 Baker
  Hall
• Saturday 10/2, 1 PM - 4 PM, Room 1003 HbH
• Homework 6 (Queries) due Tuesday, October 12

2
Query Logic and Design

• Queries are sequences of commands used to extract
  and combine data. Using the department store
  example, what are typical questions that could be
  answered via queries?
• While tables store data, and relationships encode
  business rules that link data in multiple tables,
  only queries assist in the transformation of data
  into information

3
Design and Use of Queries

• In Access, queries are designed and executed
  using three views
• Design view
• Datasheet view
• SQL view
• Queries are used
• to perform ad-hoc data analysis
• to populate recordsets used in forms
• to populate controls such as combo boxes and text
  boxes
• to organize data for reports
• as part of macros and code for custom processing
  tasks

4
Structured Query Language

• Queries are executed using Structured Query
  Language (SQL).
• SQL has a number of advantages compared to
  sequential programs implemented in
  third-generation languages such as C or Pascal
• SQL is simple there are only thirty or so
  commands
• SQL is nonprocedural data storage details are
  shielded from the user
• SQL is platform-independent queries can be
  represented identically on different platforms
• SQL is GUI-compatible queries can be written in
  Access and other languages with a GUI alone.

You may eventually find that some queries are
more easily expressed using SQL than via an
interface
5
SQL SELECT

• Many queries are based on the command which
  extracts a set of records from a table for
  further analysis SELECT

SQL View ?
? Datasheet View
? Design View
6
SQL SELECT (contd)

• The general SELECT command uses many clauses
• SELECT ltset of columns, string or arithmetic
  combination of column(s) or rows, joins on
  columns in different tablesgt
• INTO ltnew tablegt
• FROM lttable name(s)gt
• WHERE ltlogical conditions holdgt
• GROUP BY ltcolumn(s)gt
• The SELECT command can extract a set of columns
  from one table, or a set of columns from
  different tables for which pairs of tables have
  Foreign Key relationships.
• The INTO clause specifies a table which will
  store results of this query.
• The FROM clause lists the table(s) used in the
  query
• The WHERE clause lists logical conditions that
  must be satisfied.
• The GROUP BY clause lists the attributes by which
  rows will be aggregated.

7
SQL SELECT- Using String Expressions

• New columns in queries may be assigned values
  using a variety of string expressions
• ColName1 ColName2 (concatenation) appends
  one string field to another.
• Ex SName - Sex applied to Richards
  - M yields Richards - M
• Left(ColName, N) extracts first N characters
  of column ColName. Similarly Right(ColName, N)
• Ex Left(SName, 1) applied to Richards yields
  R
• Mid(ColName, N, M) extracts M characters of
  column ColName starting at character N
• Ex Mid(CrsNbr,4,1) applied to ACC610 yields
  6
• Len(ColName) returns the length of column
  ColName
• Ex Len(SID) applied to 218 yields 3.

8
SQL SELECT- Using Arithmetic Expressions

• New columns in queries may also be assigned
  values using a variety of arithmetic expressions
• ColName1 ColName2 (addition) add the
  contents of two columns. Similarly for -
  (subtraction)
• ColName1 ColName2 (multiplication)
  subtracts the contents of one column from
  another. Similarly for / (division)
• ColNameN (exponentiation) computes the value
  of a column to the power N (N can be any real
  number)
• Abs(ColName) (absolute value) computes the
  absolute value of a column
• ex. Abs(Temperature) applied to -32 gives
  32
• Int(ColName) returns the integer portion of a
  real number
• ex. Int(InterestRate) applied to 8.325 gives
  8

9
SQL WHERE Criteria for Selecting Rows

• Logical expressions can determine rows to appear
  in query output
• WHERE LogicalExpression(ColName)
• Ex WHERE Left(Sname,1) lt S chooses all rows
  where the first initial of the last name is lower
  than S
• Ex WHERE Major Is Null selects all rows where
  the value of the field Major is Null (empty)
• Other operators BETWEEN, LIKE, IN, EXISTS
• WHERE LogicalExpression1(ColName1) OR
  LogicalExpression2(ColName2) (Logical OR)
• Ex (Left(SName,1)lt"P") OR (GPA)gt2.5) chooses
  all rows where the first initial of the last name
  is lower than P OR the GPA exceeds 2.5
• WHERE LogicalExpression1(ColName1) AND
  LogicalExpression2(ColName2) (Logical AND)
• Ex (Left(SName,1)lt"P") AND (GPA)gt2.5)
  chooses all rows where the first initial of the
  last name is lower than P AND the GPA exceeds
  2.5

10
SQL GROUP BY Aggregate Records

• The GROUP BY clause divides records into groups
  based on the value of a criterion. New fields may
  be created using summarized values of fields in
  each category. Example
• SELECT Major, Avg(GPA) AS AvgOfGPA
• FROM STUDENT
• GROUP BY STUDENT.Major

Other summary calculations Sum, Min, Max, Count,
StDev, Var, First, Last
11
Access Variations on SELECT Queries

• UPDATE Calculates a new value for an existing
  column using arithmetic/string expressions for
  rows satisfying certain criteria. For example
• UPDATE FACULTY
• SET FACULTY.FName "Kennedy-Jenkins"
• WHERE ((FName"Kennedy"))

12
Access Variations on SELECT Queries (contd)

• APPEND Adds records from the current query to
  another table with identical format. For example
• INSERT INTO FACULTY4
• SELECT FACULTY3.
• FROM FACULTY3

13
Access Variations on SELECT Queries (contd)

• MAKE TABLE Creates a new table based on results
  from the current query. For example
• SELECT FACULTY3.FID, FACULTY3.FName
• INTO FACULTY_NEW
• FROM FACULTY3
• GROUP BY FACULTY3.FID, FACULTY3.FName
• ORDER BY FACULTY3.FID

14
Access Variations on SELECT Queries (contd)

• DELETE deletes all records satisfying certain
  criteria. Example
• DELETE STUDENT2., STUDENT2.GPA
• FROM STUDENT2
• WHERE (((STUDENT2.GPA)lt2.5))

15
Access Variations on SELECT Queries (contd)

• CROSSTAB creates a two-dimensional table in
  which a value field is summarized according to
  row and column field(s). Example
• TRANSFORM Avg(STUDENT.GPA) AS AvgOfGPA
• SELECT STUDENT.Major
• FROM STUDENT
• GROUP BY STUDENT.Major
• PIVOT STUDENT.Sex

16
Parameter Queries

• Parameter Queries Select records that match a
  user-defined criterion based on a particular
  field. Example
• SELECT COURSE.
• FROM COURSE
• WHERE (((COURSE.CrsNbr)Select a course
  number))

17
Other Query Applications

• Views Select a row driver, and add one or more
  tables, each pair of which is linked by a Foreign
  key relationship. Select columns of interest for
  output.

Find Duplicate Records Find the records in a
table that have identical values for all
columns Find Unmatched Records Find the records
in one table without records in another table
according to a common field
Archive Select records according to given
criteria (SELECT query) create a new table based
on query results (MAKE TABLE query), delete
selected records from original table (DELETE
query) Select Top X Records Select the top X
(X) records according to given criteria
18
Data Analysis Applications of Queries

• Suppose you have a number of fields that could
  serve as indexes or foreign keys, but with
  inconsistent information
• Degree attained Bachelors, B.A., A.B,
  MPM, M.S.
• How could you derive a consistent set of values?
• Now suppose you have a dataset describing
  customers with a number of candidate keys
• Last name, Phone number, Street address
• How could you determine a primary key (if one
  exists)?
• Finally, you want to determine the distribution
  of family income by marital status and gender
• Marital Status Married, Separated,
  Divorced,
• How would you calculate this distribution?