CS105 Lab 3

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.

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

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!

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
• from players
• where firstname "Mike" or
• firstname "Jim" and
• team "Sox"
• Is this correct?

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 isnt what we meant to ask! We need to
  force our own priorities using parentheses.
• What needs to be change?

6
Wildcards

• We will be using two wildcards in this class
• The (percent) wildcard matches zero to any
  number of characters
• c5 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

7
Using Wildcards in Queries

• Your boss remembers that his favorite players
  first name starts with a D. He wants you to
  retrieve a list of all players whose names start
  with D.
• select
• from players where firstname like D
• Your boss still cant find the player. However,
  he definitely remembers that players last name
  contains an o in the middle or at the end.
• select
• from players where lastname like _o

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 players last name was u.
• select
• from players where lastname like _uo
• OR
• select
• from players where lastname like _u and
  lastname like _o

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 lt 5

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 lt 10)
• OR
• select
• from Pets
• where weight gt 10

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.

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.

13
COUNT( ) Function

• count( field_name ) displays the number of
  records returned
• Lets count the number of pets we have found in
  our previous query
• select count(name) from Pets
• where weight between 5 and 10

14
COUNT( ) Function (continued)

• count( distinct field_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?
• select count(distinct type)
• from Pets
• where weight between 5 and 10

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
• select max(weight), min(weight)
• from Pets
• where weight between 5 and 10

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

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) gt 2005

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

19
Aliasing

• Sometimes the default field name in the result
  set isnt 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

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.