SQL and Relational Databases

Introduction

Structured Query Language or SQL is probably the most common database language in existance. Many database engines are based on it, MySQL, PosgreSQL and Microsoft SQL Server to name a few. SQL is made specifically for relational databases, those are databases in which there are multiple tables and there are relationships that are enforced between them. Some modern databases forgo this relational aspect in their database engines but often the syntax to access the database is still based on SQL.

Key Questions

• What is a relational database?
• How can I select specific columns from a table?
• How can I select specific rows from a table?
• How can I join multiple tables into a single set of results?
• How can I make a new table?
• How can I modify the rows of a table?

Assignment Overview

This week you will be practicing a number of basic SQL operations. Mainly this will focusing on selecting particular sets of data but it will also include creating and modifying some data as well.

Explore the Topics

SQL Overview

An introduction to the basics of SQL and relational databases.

SQL SELECT

This exploration covers the topic of selecting data from a single table using SQL.

Data Manipulation

This exploration goes over manipulating data within a table in SQL. It will cover INSERT, UPDATE and DELETE, which do more or less exactly whey they sound like they do.

SQL Relationships

This exploration looks at relationships in SQL. They are a critical part of what makes a relational database relational. They also can often stand in the way of having fast access to very large datasets.

Creating SQL Tables

This exploration covers creating SQL tables. We are not going to discuss database design, but we will go over the basic syntax to create your own SQL tables.

Additional Resources

Class Database

This file contains the class database that you can use to follow along with examples (but note many examples show a smaller result set to fit on the page) and it will also be used for the homework queries. Instructions for setting it up can be found in the first exploration.

MySQL Documentation

This ranks pretty low in the tier list when it comes to documentation quality. It is hard to follow and poorly organized. But its official and really there isn’t a lot of other good stuff out there for MySQL. The only sections on the left menu you will likely care about are Data Types, Functions and Operators and SQL Statement Syntax you can pretty much ignore the rest.

Review

Obviously there is a lot more to SQL databases than what we went over here. But it mainly has to do with IT stuff like maintaining backups or dealing with user permissions. That isn’t in the scope of what we want to do with databases. What this does cover is most of the use cases you will run into working with relational databases on a day-to-day basis as a consumer of the data and it should give you a solid grounding in the sort of syntax you are likely to see when working with databases in general. A lot of databases use SQL like syntax for interactions.

As a closing thought, consider what we have learned and try to figure out what sorts of data sets would be hard to make using the subset of SQL we talked about. What features are missing that would make it easier? A lot of what we do later on will be looking at those sorts of questions and dealing with them.