http:www.cse.msu.educse103

1
CSE 103

• StudentsYour BTs have been graded. See Erica or
  Jo with questions or stay after class.Makeup
  sign-up is now available.
• OthersPlease save your work and log out ASAP.

2
Review Calculated Fields and Aliases

• What are calculated fields?
• Calculated Fields in Access default to Expr1,
  Expr2, etc. when displayed
• What is an SQL alias and how can we use it with
  calculated fields?
• What SQL keyword is necessary in order to
  designate an alias?
• What do we have to remember when using table
  aliases?

3
Intro to Functions

• What are functions (in general)? What are some
  examples?
• We will deal mainly with functions in MySQL,
  rather than in Access.
• MySQL functions are similar to the SQL
  standard, and good HELP files are available.
• Access functions differ from those in most other
  systems, and have very little actual HELP for
  users. (Some of the HELP is actually Excel help
  and wont even work in Access!)

4
Classes of Functions

• What are the main classes of functions?
• String (for manipulating text)
• Numeric (for mathematical operations)
• Date/Time (for working with date and time fields)
• Cast (for converting between data types)
• Full-text search (useful if you have enormous
  text fields, like entire news or research
  articles)
• Aggregate (well cover these on Day 11)
• Control (e.g., IF, CASE, IFNULL)
• Other (e.g., encryption, information about
  database)
• We will cover String, Numeric, and Date/Time
• Others are not used as often, much more advanced

5
String Functions

• STRING refers to a group of characters, like a
  word or a phrase (including spaces, punctuation)
• Text fields (char(X) and varchar(X) in MySQL)
  contain strings
• Examples of string functions
• obtain substrings (portions of a longer string)
• calculate the length of a string
• convert the case of a string
• concatenate strings into one longer string
• See MySQL documentation for details (use the link
  on the navigation bar on CSE 103 page)

6
String Function Exercise

• Sort directors based on the length of their last
  name, longest first. Break ties by length of
  first name. Display full names and lengths.
  290107, Pedro, Sanchez
• Notice the duplicate records? Why are those
  there? Use SELECT DISTINCT to fix this. 69236
  (69787?)
• Create a listing of each movie from 2004 with a
  single field that contains the name of the movie
  with the rating behind it following a slash
  (e.g., The Lord of the Rings The Fellowship of
  the Ring/PG-13) . Sort the list alphabetically by
  movie title. 61, 30 Days of Night/Unrated
• A new language code scheme has been proposed
  the first two letters and the last letter of
  each language will produce a 3-letter code.
• Create a list of languages, old codes, and new
  codes 211
• See if this scheme will work do conflicts
  exist?
• (Think about how to sort best to find conflicts.
  Could you write a query to look for conflicts?)

7
Date/Time Functions

• DMBSs store dates in a highly specialized format
  that allows for detailed manipulation.
• A common use for Date/Time functions is to
  extract various parts, such as month, day, hour,
  etc., from a single date/time field.
• There are also functions to calculate the
  difference between two dates and to get the
  current date/time.
• See MySQL documentation for details.

8
Date/Time Functions Exercise

• Which people in the database were born in July?
  3402
• Which people died on a Monday or Tuesday? 2670
• Who was born on Valentine's day in any year? How
  old are they now? 129, oldest is 157!
• Think about this If I was born in 1975 on
  February 14, how old am I now?
• What if I just told you that I was born in 75
  and havent had my birthday yet this year?

9
Date/Time Functions Exercise, continued

• Find the current age, in order, of everyone who
  starred in The Ten Commandments (1956 version),
  as if they were still alive today. 22 This
  query should work correctly no matter what year
  it is when you run it. oldest in 05 is 143
• For simplicity, you can assume that everyones
  birthday has already occurred this year.
• Remember that A, An, and The have been
  moved to the end of movie titles.
• Now modify your query to create a list of only
  the actors that are still living. 8

10
Homework

• Check the HOMEWORK link from todays Web page
• Finish queries from class
• There are problems that will involve using other
  classes of functions, so use the MySQL
  documentation link
• Read Day 16 in the textbook on Aggregate Functions