Access Patterns Karl Lieberherr 10202021 Access Patterns 1

Access Patterns Karl Lieberherr 10/20/2021 Access Patterns 1

Patterns • It is useful to think in terms of problem solving patterns when writing queries. • We follow the Principle of Least Information which divides queries into four pattern kinds: • • calculated field elimination of duplicates (distinct rows for selected fields) aggregation-Totals (non-trivial aggregation with calculated field) selection • We cover two patterns of the aggregation-Totals: Aggregate. For. Other and Count. For. Self. Also two composite patterns: Arg. Max and Above. Average Patterns. Disambiguation Pattern. 10/20/2021 Access Patterns 2

Pattern: Aggregate. For. Other • Instantiations and Examples • Sum. For. Other • Examples: • Order. Totals: For each Order. ID, sum all extended prices. • Course. Load: For each Student, sum the credit hours. • Menu. Item. Units. Sold: For each menu item, sum the units sold. • Average. For. Other • Example: • Average. GPA: For each Student. ID, compute the average grade across all courses taken. • Count. For. Other • Example: • Count. Courses. Student: For each Student. ID, count the number of courses taken. • Count. Courses. Instructor: For each Instructor. ID, count the number of courses taught. 10/20/2021 Access Patterns 3

Pattern: Count. For. Self • Examples • Count. Wins: Count the wins a team made. • Count. Losses: Count the number of losses a team had. 10/20/2021 Access Patterns 4

We review some of the common patterns we have used. • IMPORTANT NOTE: SQL is given for informational purposes only to summarize the effects of clicking in the Query Builder. We have not covered SQL but used the Query Builder instead. 10/20/2021 Access Patterns 5

Table • ID • 1 • 2 • 3 • 4 • 5 • 6 • 7 • 8 10/20/2021 A a 1 a 1 a 1 a 2 a 3 B b 1 b 2 b 2 b 1 b 2 C 5 8 7 2 1 7 8 0 Access Patterns 6

Pattern Sum. For. Other: What we want: summing for distinguished column of another column • A • a 1 • a 2 • a 3 10/20/2021 Sum. Of. C 23 15 0 Access Patterns 7

Pattern Sum. For. Other: SQL query SELECT Table 1. A, Sum(Table 1. C) AS Sum. Of. C FROM Table 1 GROUP BY Table 1. A; 10/20/2021 Access Patterns 8

Pattern Sum. For. Other: Query Builder Manipulation • Create Query • Choose Table 1 • Select column A • Select column C • Totals (Group. By default) • Sum for column C 10/20/2021 Access Patterns 9

Table • ID • 1 • 2 • 3 • 4 • 5 • 6 • 7 • 8 10/20/2021 A a 1 a 1 a 1 a 2 a 3 B b 1 b 2 b 2 b 1 b 2 C 5 8 7 2 1 7 8 0 Access Patterns 10

Pattern Count. For. Self: What we want: Counting for distinguished column • B Count. Of. B • b 1 4 • b 2 4 10/20/2021 Access Patterns 11

Pattern Count. For. Self: SQL SELECT Table 1. B, Count(Table 1. B) AS Count. Of. B FROM Table 1 GROUP BY Table 1. B; 10/20/2021 Access Patterns 12

Pattern Count. For. Self: Query Builder Manipulation • Create Query • Select Table 1 • Select column B • Totals (Group. By) • For second column B choose count 10/20/2021 Access Patterns 13

Table 2 (Game. Results) • ID • 1 • 2 • 3 • 4 • 5 • 6 • 7 10/20/2021 winner 1 2 1 3 4 1 1 loser 2 1 3 2 1 4 5 forced 1 2 3 2 4 4 0 1 Baltimore Orioles 2 New York Yankees 3 Toronto Blue Jays 4 Tampa Bay Rays 5 Boston Red Sox Forced: Team was handicapped (played without their strongest player) Access Patterns 14

Pattern Count. For. Self: What we want: count wins for each team • winner • 1 • 2 • 3 • 4 10/20/2021 Count. Ofwinner 4 1 1 1 Access Patterns 15

Pattern Count. For. Self: SQL • SELECT Table 2. winner, Count(Table 2. winner) AS Count. Ofwinner • FROM Table 2 • GROUP BY Table 2. winner; 10/20/2021 Access Patterns 16

Count. For. Self: Query Builder Manipulation • Create Query • Choose Table 2 • Select winner column twice • Totals (Group. By) • For second winner column: Count 10/20/2021 Access Patterns 17

Arg. Max and Above. Average Pattern • Composite pattern involving several other patterns. 10/20/2021 Access Patterns 18

Arg. Max Pattern • Example Queries • • Which are the most expensive menu items? Which are the most expensive orders? Which course has been taken by the most number of students? List the students who have taken the smallest number of credits. 10/20/2021 Access Patterns 19

Implementation of Arg. Max Pattern • Use an Aggegration. For. Other query to create the list of numbers lon of which the maximum is computed (subquery 1). • Compute the maximum M (subquery 2). • Select the numbers in lon which are =M (use subquery 1 and 2). Use selection query or relationship with join. 10/20/2021 Access Patterns 20

Above. Average. Pattern • Examples • Which orders are above average? 10/20/2021 Access Patterns 21

Implementation of Above. Average Pattern • Use an Aggegration. For. Other query to create the list of numbers lon of which the average is computed (subquery 1). • Compute the average A (subquery 2). • Select the numbers in lon which are >A (use subquery 1 and 2). Use selection query. 10/20/2021 Access Patterns 22

Disambiguation Pattern • Example Query: • How many courses is each instructor available to teach? List the last name of the instructor who can teach and the number of courses. • Note: We list the last name and not the primary key (Instructor. ID). Therefore there is a need for the disambiguation pattern because there may be multiple instructors with the same last name. • Name not unique. Use ID to disambiguate but don’t show it. Let’s call this the Disambiguation Pattern. 10/20/2021 Access Patterns 23

Disambiguation Pattern 10/20/2021 Access Patterns 24

Required Elimination of Duplicates Pattern • Example Query: • How many courses use textbooks published by “Wiley”? • Note: when we list Course. IDs and Textbook. IDs there may be courses that use two textbooks by Wiley. We cannot count the Course. IDs directly and need first an isolated Elimination of Duplicates. 10/20/2021 Access Patterns 25

First Subquery 10/20/2021 Access Patterns 26

output 10/20/2021 Access Patterns 27

Summary • We covered • two single table aggregation patterns: Aggregate. For. Other and Count. For. Self. • two composite patterns: Arg. Max and Above. Average. • There are many more patterns we covered in class. 10/20/2021 Access Patterns 28

Table 2 (Debate. Results) • ID • 1 • 2 • 3 • 4 • 5 • 6 • 7 10/20/2021 winner 1 2 1 3 4 1 1 loser 2 1 3 2 1 4 5 forced 1 2 3 2 4 4 0 Access Patterns 29

What we want: count faults • Faulter • 1 • 2 • 5 10/20/2021 Count. Of. Faulter 2 1 1 Access Patterns 30

Add calculated field for Faulters: subquery • What do we want for the subquery? 10/20/2021 Access Patterns 31

Create Faulter column from Loser column • Faulter • 2 • 1 • 5 10/20/2021 forced 1 2 4 0 Access Patterns 32

SQL • SELECT Table 2. loser AS Faulter, Table 2. forced • FROM Table 2 • WHERE (((Table 2. loser)<>[forced])); 10/20/2021 Access Patterns 33

Query Builder Manipulation • Create Query • select loser column; rename to Faulter; condition <>[forced] • select forced column (for checking result) • name subquery: Faults 10/20/2021 Access Patterns 34

Reminder: What we want • Faulter • 1 • 2 • 5 10/20/2021 Count. Of. Faulter 2 1 1 Access Patterns 35

SQL • SELECT Faults. Faulter, Count(Faults. Faulter) AS Count. Of. Faulter • FROM Faults • GROUP BY Faults. Faulter; 10/20/2021 Access Patterns 36

Query Builder Manipulation • Create Query • Choose subquery Faults • Select Faulter column twice • Totals (Group. By) • select Count for second 10/20/2021 Access Patterns 37