Aggregation
Aggregation
Single Row Operations:Single-Table Queries So far,our SQL statements have referred to the values in a single row at a time. table dogs name fur Write a query that outputs the name of dogs that either have long fur or are named Grover. abraham long barack short SELECT name FROM dogs clinton long WHERE fur ='long'OR name ='grover' delano long output: name eisenhower short abraham fillmore curly clinton grover short delano herbert curly grover
Single Row Operations: Single-Table Queries So far, our SQL statements have referred to the values in a single row at a time. name fur abraham long barack short clinton long delano long eisenhower short fillmore curly grover short herbert curly table dogs SELECT name FROM dogs WHERE fur = 'long' OR name = 'grover'; name abraham clinton delano grover Write a query that outputs the name of dogs that either have long fur or are named Grover. output:
Single Row Operations:Join table dogs table parents name fur parent child result of cross product: delano long delano herbert name fur parent child herbert curly fillmore delano delano long delano herbert grover short fillmore grover delano long fillmore delano Write a query that outputs the names and delano long fillmore grover fur types of all of Fillmore's children. herbert curly delano herbert SELECT name,fur FROM dogs,parents herbert curly fillmore delano WHERE parent ='fillmore'AND herbert name child; curly fillmore grover grover short delano herbert output: name fur → grover short fillmore delano delano long → grover short fillmore grover grover short
Single Row Operations: Join Write a query that outputs the names and fur types of all of Fillmore's children. parent child delano herbert fillmore delano fillmore grover name fur delano long herbert curly grover short table dogs table parents SELECT name, fur FROM dogs, parents name fur parent child delano long delano herbert delano long fillmore delano delano long fillmore grover herbert curly delano herbert herbert curly fillmore delano herbert curly fillmore grover grover short delano herbert grover short fillmore delano grover short fillmore grover name fur WHERE parent = 'fillmore' AND name = child; delano long grover short result of cross product: output:
Aggregation Aggregation is the process of doing operations on groups of rows instead of just a single row. SQL provides aggregate functions whose return values can be used as entries in a column. output the average age of all dogs: table dogs SELECT AVG(age)AS avg_age FROM dogs; name fur age output: avg_age delano long 10 6.2 eisenhowe short 7 output the total number of rows: fillmore curly 8 SELECT COUNT(*)AS count FROM dogs; grover short 2 output: count herbert curly 4 5
Aggregation Aggregation is the process of doing operations on groups of rows instead of just a single row. name fur age delano long 10 eisenhowe r short 7 fillmore curly 8 grover short 2 herbert curly 4 table dogs count 5 avg_age 6.2 output: output: SELECT AVG(age) AS avg_age FROM dogs; output the average age of all dogs: SELECT COUNT(*) AS count FROM dogs; output the total number of rows: SQL provides aggregate functions whose return values can be used as entries in a column