CSE 103

1
CSE 103

• 103 Students Please do not log in yet.
• Check-in with Brian/Erica in the back.Review
  Days 3 and 4 in the book.
• Others Please save your work and logout.Class
  begins at 1020.

2
Review Questions

• What does the LIKE operator do?
• What are wildcards and how do they relate to
  LIKE?
• If you want to find an empty field in a record,
  what keyword would you need?
• What is meant by filtering?

3
Sample Problem from Homework

• TABLE myTableOfMovies
• FIELDS Title Text Length Number, in
  minutes MPAA_Rating Text one of G, PG, PG13,
  R, NC17 Director Text Producer
  Text Writer Text Seen Yes/No
• What SQL could be used to find all the movies
  directed by Steven Spielberg that are at least
  ninety minutes long but no longer than two hours?

4
Queries in musicdemo.mdb

• Question Which artists are musical groups?
• Under OBJECTS, select Queries, then the NEW
  button, and then Design View. 
• Then ADD the tbl_Artists table, and CLOSE the
  Show Table window.
• Enter the criterion in the Criteria row of the
  Design Grid. (In Access, Yes and True both work
  for Yes/No fields 1 only sometimes works)
• Save your query as qry_day03_groups

5
Queries Exercise Design View

• Construct queries that
• List only the track title, album ID and duration
  of the tracks in the collection gt376
  (qry_day03_tracklength)
• List titles of all albums produced in 1996 gt4
  (qry_day03_1996_albums)
• List the track titles and associated album IDs in
  the collection that run over 4 minutes gt64
  (qry_day03_long_tracks)
• Compare the SQL generated by Access to that we
  created on day 2

6
Sorting a Query

• See the link Sorting Records in a Query on the
  Classwork page to see the Access Help entry
• Make queries that
• List the tracks from shortest to longest
• List the albums from newest to oldest
• List the albums from newest to oldest and further
  ordered by number of tracks
• You may optionally save them with names of your
  choice (following the qry_day03_ scheme).

7
Complex Criteria Queries

• Boolean Operators
• AND / OR in Design View
• Same line AND different lines OR
• Can mix and match all ANDs grouped together,
  sets of ANDs are ORed
• NOT
• Type in criteria field, use NOT(condition)
• Other Criteria Keywords
• LIKE (and wildcard in Access)
• IS NULL / IS NOT NULL
• BETWEEN AND
• Type these directly into the criteria field in
  Design View

8
Complex Criteria Queries

• Construct queries which
• List albums released after 1990 but prior to 2000
  18 (qry_day03_90s_albums) 
• List the digital tracks in the collection,
  ordered by filesize, which have Christmas in the
  title and which are over 3 MB 10
  (qry_day03_large_xmas_digtracks) 
• List the albums (and years) which have no label
  data 7 (qry_day03_albums_no_labels)

9
Homework

• Read Days 13 and 15 in your textbook
• We will be covering Mail Merge with Access and
  calculated fields with aliases
• Be sure you understand queries in SQL and design
  views, take extra time to review if needed
• Fill out the Helproom Survey
• See link on the Classwork page for today