CS105 Lab Discussion 14 Review

1
CS105 Lab Discussion 14Review

• Announcements
• NO LECTURES OR LABS NEXT WEEK
• Extra Credit Quiz is due Thursday, Dec 8.
• Final Exam and review details are posted on the
  website. If you need to take the conflict exam,
  request it as soon as possible.

2
Objectives

• Review
• SQL
• Excel
• Macros

3
An Example Table
Players
4
Now lets query the database

• The SELECT statement is used to query the
  database. All Select statements in this course
  have this format

Select From Where Order by
5
SQL Example

• What if we want to find all of the pets that
  weigh between 5 and 10 pounds. How would we do
  this?
• SELECT name
• FROM Pets
• WHERE weight BETWEEN 5 AND 10

6
Wildcards

• There are two wildcards for searching data that
  we will use in class
• The wildcard matches zero to any number of
  characters
• c5 gt cs105, c5, C!5, cat5
• The _ (underscore) wildcard matches a single
  (exactly one) character
• be_r gt bear, beer
• Note a character can be letter or number or
  punctuation

7
SQL Wildcards

• SELECT FROM pets WHERE Name LIKE _e
• We know that
• the first letter can be anything (but has to be
  there)
• e should be the second letter, and
• the rest of the name can be anything (even
  nothing)
• We want to use one _ wildcard and one .
• Note it is hard to tell how many underscores are
  there

Exercise Midterm 1, SQL section
8
Joins

• Display the first name and last name of all the
  players who play CF.
• When you use multiple tables, you should specify
  the table name and field in the format of
  TableName.Field
• select players.LastName, players.FirstName
• from players , fielding
• where fielding.position "CF"

9
Joins - Corrections
Exercise Midterm 1 Extra Credit

• By default, each row in the players table is
  joined with each row in the fielding table.
• To fix this, we need to add to our where
  condition
• where fielding.position CF and
  fielding.playerID players.playerID
• Now only the rows that have the same playerID in
  both tables will be joined.

10
Excel Cell References

• By default, Excel adjusts cell references in a
  formula when the formula is copied and pasted.
• If you want the row or column to stay the same,
  put a dollar () sign in front of it.

11
Excel IF

• Recall from lecture how the formula
  IF(condition, result1, result2) works.
• Excel looks at the condition. If it is true,
  result1 is returned. If it is false, result2 is
  returned.
• The condition must be something that is either
  true or false that is, the condition must be
  BOOLEAN.

12
ExcelNested If
13
ExcelNested If

• IF(C3East,IF(A3H2,D3,0), 0)

14
Excel And, Or, Not

• There is an alternative way to write the IF
  statement in cell H3. We want the revenue to be
  displayed in H3 if the product is Lively Lemon
  Tea AND the region is East
• IF(AND(C3"East",A3H2),D3,0)

Exercise Midterm 2, Excel Section
15
Absolute Macros

• To see what Excel recorded, go to Tools, Macro,
  Macros.
• In the dialog box, choose NameMacro and click on
  Edit.
• This will bring up the Project Explorer with the
  NameMacro in the VBA code window.
• Notice the cell references
• Range("A1").Select
• ActiveCell.FormulaR1C1 Samarth Swarup"
• Range("A2").Select
• ActiveCell.FormulaR1C1 "123 Main Street
• What two cells are always referenced? What type
  of Macro is this?

16
Relative Macros

• Now, select any cell and hit ctrlr
• Does the macro do what you expected?How is it
  different from NameMacro?
• Take a look at the code for the NameRelative
  macro
• ActiveCell.Select
• ActiveCell.FormulaR1C1 "Nazim Elmazi"
• ActiveCell.Offset(1, 0).Range("A1").Select
• ActiveCell.FormulaR1C1 "1234 Main Street"
• How does Offset(1, 0) select a new cell?

Exercise Midterm 2, VBA section