An introduction to SQL database queries and concepts.

Computers become cost-effective enough for private businesses to use them for data management

E.F. Codd, working for IBM, publishes a paper describing the relational database model.

SQL becomes a technological standard for query languages.

After a period of heavy market saturation, remaining database companies continue to sell their products at high prices.

Interactive applications are created to interact with databases.

- Structured Query Language
- Used to manipulate data in a relational database

- A tuple data structure
- Order of tuples in database does not matter
- Order of elements in tuple does not matter

A representation of how information is organized in a database.

Database design focus on the data application, instead of logical table structure.

A database schema, or logical organization, is disconnected from how the information is physicaly stored on the computer.

- Set theory
- Relational algebra
- Relational calculus (skipping)

A __set__ is a collection of things

where each thing in the collection is unique.

We write sets as a comma seperated list

inside a pair of curly braces.

The main application of relational algebra is providing a theoretical foundation for relational databases, particularly query languages for such databases, chief among which is SQL. (wikipedia.org/relational_algebra)

∪ | Union |

∩ | Intersection |

× | Cartesian product |

π | Projection |

σ | Selection |

The union of two sets A and B

is the set of elements which are

in A, in B, or in both A and B.

A = {2, 4, 6, 8}

B = {1, 3, 5, 7}

A ∪ B = {1, 2, 3, 4, 5, 6, 7, 8}

The intersection of two sets A and B

is the set that contains all elements of A that also belong to B.

A = {1, 2, 3, 4}

B = {2, 4, 6, 8}

A ∩ B = {2, 4}

The Cartesian product of sets A and B is the set of all ordered pairs (a, b) where a ∈ A and b ∈ B.

A = {1, 2}

B = {3, 4}

A × B = {(1,3), (1,4), (2,3), (2,4)}

B × A = {(3,1), (3,2), (4,1), (4,2)}

A projection is the subset of available columns based on a set of attribute names where attributes not listed in the set have been discarded.

Name | Age | |
---|---|---|

Gunther | gunther@salvius.org | 22 |

Mark | mark@aol.com | 65 |

Abby | abby42@gmail.com | 21 |

Name | |
---|---|

gunther@salvius.org | Gunther |

mark@aol.com | Mark |

abby42@gmail.com | Abby |

A selection is an operation that returns a subset of the rows in a database.

Name | Age | |
---|---|---|

Gunther | gunther@salvius.org | 22 |

Mark | mark@aol.com | 65 |

Abby | abby42@gmail.com | 21 |

Name | Age | |
---|---|---|

Gunther | gunther@salvius.org | 22 |

Name | Age | |
---|---|---|

Gunther | gunther@salvius.org | 22 |

Abby | abby42@gmail.com | 21 |

SELECT column_name, column_name

FROM table_name

WHERE column_name operator value;

SELECT *

FROM Account

WHERE Name="Gunther"

OR Email="gunther@salvius.org";

SELECT *

FROM Account

WHERE Age > 20

AND Age < 30;

SELECT Name, Address

FROM Account, Location;

Street | City | State | |
---|---|---|---|

gunther@salvius.org | 24 Main Street | Springfiled | MA |

mark@aol.com | 208 Innovation Drive | Silicon Valley | CA |

abby42@gmail.com | 4 Pine Grove Lane | Ducktrap | ME |

Since we have 3 Accounts and 3 Locations,
the __cardinality__ of the cartesian
product will be **108**.

This is because each tuple in the Account table contains 3 elements and we have 3 Accounts. There is 4 elements in row of the Location table and there is 3 rows. (3 * 3) * (3 * 4) = 108

SELECT Email, Name

FROM Account

Name | |
---|---|

gunther@salvius.org | Gunther |

mark@aol.com | Mark |

abby42@gmail.com | Abby |

SELECT *

FROM Account

WHERE Age < 50;

Name | Age | |
---|---|---|

Gunther | gunther@salvius.org | 22 |

Abby | abby42@gmail.com | 21 |

- Entity-Relationship diagrams
- Additional SQL syntax (ORDER BY, GROUP BY, etc.)
- Types of join operations (inner join, outer join, etc.)
- Schema requirements (Not NULL, etc.)
- Primary keys, candidate keys and foreign keys