CS105 Lab 4 – SQL Filters, Wildcards, Functions

1. CS105 Lab 4 – SQLFilters, Wildcards, Functions • Announcements: • MP2 is due on Saturday, September 23rd at noon. • Midterm 1 is on Monday, September 25th. (7pm, 1hr long, rooms posted on website). If you need to take the conflict exam, request a conflict from the conflict request page asap. • The review sessions for the midterm are on Sunday, September 24th (3pm and 7pm in 141 Wohlers) CS105 – Fall 2006

2. More Announcements • If you work on your MP in the Wohlers lab, do not save your work to the desktop. It will not be there the next time you log on. • If you work on MP2 from off campus, you will need VPN software to connect to the database. See the announcements on the course website for more info. CS105 – Fall 2006

3. Start up SQLyog • Start SQLyog: Start / Class Software / CS105/ MySQL-Front • Fields to fill in: • Hostname/IP: cs105-mysql1.cs.uiuc.edu • User: cs105 • Password: cs105 • Don’t enter a database name • If you receive a “Too many connections” error, try connecting to the other server: cs105-mysql2.cs.uiuc.edu CS105 – Fall 2006

4. More on Advanced Filters • Filters can be made more powerful by the use of And, Or & Not. These logical operators allow us to combine several conditions when filtering queries. All players on the Sox whose first names are Mike • How do you tell the computer that you don’t want just players on the Sox, you don’t want just players named Mike, rather that you want the players on the Sox who are named Mike? wherefirstname = “Mike”AND team = “Sox” CS105 – Fall 2006

5. More on Advanced Filters • What if we wanted to find all players who either have the first name “Mike” or “Jim” and who play for the Sox? Try this: Select * from players where firstname = "Mike" OR firstname = "Jim" AND team = “Sox" • Is this correct? CS105 – Fall 2006

6. More on Advanced Filters • What happened? SQL groups ands together with higher priority than ors. • Our query is the same as running Select * from players where firstname = "Mike" OR (firstname = "Jim" AND team = “Sox“) This isn’t what we meant to ask! We need to force our own priorities using parentheses. • What do we need to change to fix our query? CS105 – Fall 2006

7. Wildcards • There are two wildcards for searching data that we will use in class • The % wildcard matches zero to any number of characters c%5 => cs105, c5, C!5, cat5 • The_(underscore)wildcard matches a single (exactly one) character be_r => bear, beer Note: a character can be letter or number or punctuation CS105 – Fall 2006

8. Using wildcards in queries Find all players whose first name begins with ‘D’ • Select the Baseball database. • We want to use % when there are zero or more letters after D • We can’t use = in the whereclause because we aren’t matching exact strings; We need to use the keyword like to tell MySQL that we’re using wildcards in our strings Select * from players where firstname like ‘D%’ CS105 – Fall 2006

9. Another query This time, we want to see all the players whose first name has o as the second letter • What wildcard(s) should we use here? • How can we make sure that o is the second letter? • What keyword should we use in the filter? CS105 – Fall 2006

10. The solution: • We know that • the first letter can be anything (but has to be there) • o should be the second letter, and • the rest of the name can be anything (even nothing) • We want to use one _ wildcard and one %. Select * from players where firstname like ‘_o%’ Note: it is hard to tell how many underscores are there CS105 – Fall 2006

11. SQL: between Looking for pets of a certain weight • First, select the Pets database. • What if we want to find all of the pets that weigh between 5 and 10 pounds. How would we do this? Select * from Pets Where weight BETWEEN 5 and 10 • Observe that BETWEEN is inclusive. CS105 – Fall 2006

12. Functions in SQL • A function is a predefined formula that accepts one or more arguments as input, processes the arguments, and returns an output. The sqrt() function on your calculator is an example. • In SQL, the arguments will generally be fields from tables. What the function returns is defined by the function and the arguments given to it. • There are several built-in functions that we’ll use, like Count, Sum, Avg, Min, Max, and Length. CS105 – Fall 2006

13. COUNT( ) function COUNT(field)displays the number of records returned CS105 – Fall 2006

14. COUNT( ) function cont. COUNT(DISTINCTfield)displays the number of unique values in the specified field, taken only from the records returned CS105 – Fall 2006

15. MIN( ) & MAX ( ) function MAX( field )Returns the maximum value in a field MIN( field )Returns the minimum value in a field (it ignores NULL values) CS105 – Fall 2006

16. AVG( ) and SUM( ) functions AVG( field )Returns the average value in a field (NULLs are ignored) SUM( field )Returns the sum of all entries in a field Try this query: Select avg(weight), sum(price) From Pets CS105 – Fall 2006

17. Functions in the where clause You can also use functions to filter your result set. Year( date )Returns only the year part of a date field. To get all pets born before 1995, try: Select * from Pets where year(birthdate) <1995 CS105 – Fall 2006

18. Aliasing • Sometimes the default field name in the result set isn’t as descriptive as we would like. • In these cases we can rename the field header using as. Try this query: Select avg(weight) as ‘Average Weight’ from Pets CS105 – Fall 2006