CSE 103 Students Your BTs have been graded

CSE 103 Students: Your BTs have been graded. See Erica or Jo with questions or stay after class. Makeup sign-up is now available. Others: Please save your work and log out ASAP. http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 1

Review: Calculated Fields and Aliases What are calculated fields? Calculated Fields in Access default to Expr 1, Expr 2, 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? http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 2

Intro to Functions What are functions (in general)? What are some examples? We will deal mainly with functions in My. SQL, rather than in Access. My. SQL 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 won’t even work in Access!) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 3

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 (we’ll 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 http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 4

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 My. SQL) 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 My. SQL documentation for details (use the link on the navigation bar on CSE 103 page) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 5

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? ) http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 6

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 also functions to calculate the difference between two dates and to get the current date/time. See My. SQL documentation for details. http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 7

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 haven’t had my birthday yet this year? http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 8

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 everyone’s 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] http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 9

Homework Check the HOMEWORK link from today’s Web page Finish queries from class There are problems that will involve using other classes of functions, so use the My. SQL documentation link Read Day 16 in the textbook on Aggregate Functions http: //www. cse. msu. edu/~cse 103 U: /msu/course/cse/103 10