CS105 Lab Discussion 14 Review

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. CS105 Lab Discussion 14Review CS105 – Fall 2005

Objectives • Review: • SQL • Excel • Macros CS105 – Fall 2005

One table Three Records Two Fields First Name Last Name Alex Rodriguez Sammy Sosa Manny Ramirez An Example Table Players: CS105 – Fall 2005

Now let’s 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 …] CS105 – Fall 2005

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 CS105 – Fall 2005

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 2005

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 CS105 – Fall 2005

Joins • Display the first name and last name of all the players who play CF. • When you use multiple tables, you should specify the tablename and field in the format of TableName.Field select players.LastName, players.FirstName fromplayers , fielding wherefielding.position = "CF" CS105 – Fall 2005

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 wherecondition: wherefielding.position = “CF”andfielding.playerID = players.playerID • Now only the rows that have the same playerID in both tables will be joined. CS105 – Fall 2005

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. CS105 – Fall 2005

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. CS105 – Fall 2005

False Result for False Condition 1 Condition 1 True Result for True Condition 1 and False Condition 2 False Condition 2 True Result for True Condition 1 and True Condition 2 Excel:Nested If CS105 – Fall 2005

False C3="East" 0 True 0 False A3=H2 True D3 Excel:Nested If =IF($C3=“East”,IF($A3=H$2,$D3,0), 0) CS105 – Fall 2005

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",$A3=H$2),$D3,0) Exercise: Midterm 2, Excel Section CS105 – Fall 2005

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? CS105 – Fall 2005

Relative Macros • Now, select any cell and hit ctrl+r • 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 doesOffset(1, 0)select a new cell? Exercise: Midterm 2, VBA section CS105 – Fall 2005

CS105 Lab Discussion 14 Review