CS105 Lab 3 – Wildcards & Functions

1. CS105 Lab 3 – Wildcards & Functions • Announcements: • MP1 is available and is due on Saturday, September 8th at noon. You are NOT allowed to work in groups for MP1. Each person should should complete and submit their own MP1. • Instructions on downloading and installing SQLyog on your personal computer can be found at: http://www.cs.uiuc.edu/class/cs105/sqlyog.htm • Mallard Quiz 2 is posted and due on Tuesday September 12th at 8 am. CS 105 – Fall 2007

2. Objectives • Learn logical operator precedence order • Learn how to use wildcard for template based filtering • Study relational operators • Learn functions in SQL • Use aliases for user friendly output CS 105 – Fall 2007

3. Connecting with SQLyog • Start SQLyog. (Start / Class Software / CS105 / SQLyog) • You should see login screen. • If a connection is not already defined, click “New” and name the new connection CS105. • Enter the following data into the login screen: • Hostname: • cs105-mysql1.cs.uiuc.edu • or cs105-mysql2.cs.uiuc.edu • User: cs105 • Password: cs105 • Leave the ‘Database(s)’ box blank. • Click “Connect!” CS 105 – Fall 2007

4. Using AND & OR Together • Suppose we want to find all Sox players whose first names are either "Mike" or "Jim"? • Try this example: select* fromplayers wherefirstname = "Mike"or firstname = "Jim"and team = "Sox" • Is this correct? CS 105 – Fall 2007

5. Operator Precedence • In SQL, And has a higher priority than Or. • Therefore, SQL interpreted the previous query as follows: 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 needs to be change? CS 105 – Fall 2007

6. Wildcards • We will be using two wildcards in this class: • The % (percent) wildcard matches zero to any number of characters c%5 matches cs105, c5, C!5, cat5 • The _(underscore)wildcard matches a single (exactly one) character be_r matches bear, beer • The combination_% of wildcards matches one or more numbers of characters Note: a character can be letters, numbers or even punctuation CS 105 – Fall 2007

7. Using Wildcards in Queries • Your boss remembers that his favorite player’s first name starts with a “D”. He wants you to retrieve a list of all players whose names start with D. select* fromplayerswherefirstnamelike“D%” • Your boss still can’t find the player. However, he definitely remembers that player’s last name contains an “o” in the middle or at the end. select* fromplayerswherelastnamelike“_%o%” CS 105 – Fall 2007

8. Using Wildcards in Queries • After a while, your boss comes back to you and informs you that he also remembers the second character of the player’s last name was “u”. select* fromplayerswherelastnamelike“_u%o%” OR select* fromplayerswherelastnamelike“_u%” and lastname like “_%o%” CS 105 – Fall 2007

9. Relational Operators • Suppose we want to look for pets of a certain weight… • First, select the Pets database. • What if we want to find all of the pets that weigh less than 5 lbs? select * from Pets where weight < 5 CS 105 – Fall 2007

10. Relational Operators (Continued) • What if we want to find all of the pets that weigh not less than 10 lbs? select * from Pets where Not (weight < 10) OR select * from Pets where weight >= 10 CS 105 – Fall 2007

11. Between • What if we want to find all of the pets that weigh between 5 and 10 pounds? select * from Pets where weight between 5 and 10 • Observe that between is inclusive. CS 105 – Fall 2007

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 a 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. CS 105 – Fall 2007

13. COUNT( ) Function • count(field_name)displays the number of records returned • Let’s count the number of pets we have found in our previous query selectcount(name)from Pets whereweight between 5 and 10 CS 105 – Fall 2007

14. COUNT( ) Function (continued) • count( distinctfield_name)displays the number of unique values in the specified field, taken only from the records returned. • Now, how many different types of pets did we retrieve? selectcount(distinct type) from Pets whereweight between 5 and 10 CS 105 – Fall 2007

15. MIN( ) & MAX ( ) Functions • What is the maximum and the minimum weight of the pets we retrieved? • Hint #1: You can use max(field) and min(field) functions • Hint #2: You can retrieve multiple functions as if they are fields selectmax(weight), min(weight) from Pets whereweight between 5 and 10 CS 105 – Fall 2007

16. AVG( ) and SUM( ) Functions • avg(field_name)returns the average value in a field (NULLs are ignored) • sum(field_name)returns the sum of all entries in a field • We can calculate the average weight of all pets and the total price for the entire table data • Try this query: select avg(weight), sum(price) from Pets CS 105 – Fall 2007

17. Date Functions • SQL provides several date manipulation functions. • For example: year( date )returns only the year part of a date field. • Also in SQL you can use functions for filtering: • For example, how we can get information about pets born after 2005? select * from Pets where year(birthdate) > 2005 CS 105 – Fall 2007

18. Concatenating Fields • The concat(field1, field2, …) function can be used to combine multiple fields and/or strings into a single value. • We want to display the fields name and type in the Pets table as a single field in the result set. What can we do? select concat(name, type) from Pets • How can we improve on this result? • Note: Do not use || or + to concatenate fields in MySQL CS 105 – Fall 2007

19. 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 CS 105 – Fall 2007

20. Additional Exercises • Find all Pets whose name start with “N” and ends with “n”. • Find all Pets whose price no cheaper than 50$ and no more expensive than $70. (Give two solutions) • Find the "best value" Pet, "Best value" means the value of dollar per pound. • Find the average price of all cats born between 2000 and 2005, and give a user friendly name for the calculated field. CS 105 – Fall 2007