SQL : The Sequel

Table

A table stores data. It consists of a fixed number of columns, data entires stored in rows.

- To make a table in SQL, use a CREATE TABLE statement

- To create rows of data, UNION together SELECT statements

- To create rows of data from existing table, use a SELECT statement with a FROM clause

Join

- Given multiple tables, we can join them together by specifying their names, separated by commas, in the FROM clause of a SELECT statement

- When we join two tables, we get a new table with one row for each combination of rows from the original tables

Single Row Operations : Single - Table Queries

: so far, our SQL statements have referred to the values in a single row at a time

- Write a query that outputs the name of dogs that either have long fur or are named Grover

- Write a query that outputs the names and fur types of all of Fillmore's children

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

Aggregate Function

MAX ( [ columns ] ) : The maximum value in the given column(s)

MIN ( [ columns ] ) : The minimum value in the given column(s)

AVG ( [ columns ] ) : The average value in the given column

COUNT ( [ columns ] ) : The number of values in the given column

SUM ( [ columns ] ) : The sum of the values in the given column

Groups

- By default, aggregation is performed over all the rows of the table

- We can specify that we want to group rows based on values in a particular column using the GROUP BY clause in a SELECT statement

- Write a query that finds the average age of dogs for each fur type

More on Group BY

- You can GROUP BY any valid SQl expression, which includes using multiple column names and operators

- A single group consists of all rows for which [ expression ] evaluates to the same value

- The output table will have one row per group

Filtering Groups

- We know how to filter individual rows using the WHERE clause

- To filter groups, use the HAVING [ condition ] clause

- Write a query that finds the average age of dogs for each fur type if there are more than one dogs with that fur type

Mutating Tables

Databases

: In real databases, it's common practice to initialize empty tables and add rows as new

create / remove rows

Summary

- we can use aggregate functions to perform operations on a set of rows rather than on individual rows