This is a short introduction to relational database design and the Structured Query Language (SQL). Many people organize their electronic data into spreadsheets. This works great if the data is simple, small, and not likely to be tracked for very long periods of time. However, when you have more sophisticated, larger, and long-lived data sets the spreadsheet approach breaks down. In these examples I discuss why this is the case and how to build and query relational databases.
I use a relational database management system called SQLite. SQLite is one of my favorite tools. It is an open-source, full-featured relational database management system where an entire database is stored in a single file. I also use an accompanying tool called 'DB Browser for SQLite' because it provides a nice user interface to interact with SQLite databases. If you are curious about how to use SQLite databases in different programming languages then check out my other 'book', "Programming with SQLite" on this site. This shows how to use SQLite in a C/C++, Python, and Java programs.
I start this book by discussing what makes a good database design. I explain entity-relationship diagrams and schemas. These modeling tools aide in the design of a database. I talk about one-to-many and many-to-many relationships and how to use them in a relational database. Perhaps most importantly, though, I cover the basics of SQL so that you can retrieve meaningful information from your databases. The second chapter is a reference for beginners. I cover each of the most commonly used sql keywords in a separate playback. The third chapter shows a few worked examples. I build each of these queries slowly adding more and more detail to get the results I am looking for. If you like the worked examples I have another group of 30 worked examples on this site: "Worked SQL Examples"
Each of the first three links below will take you to a 'playback' that shows how I wrote some SQL to build and query a database for a fictitious pet adoption center. I move pretty fast in these playbacks so it is important that you spend some time after each of my comments to try and fully understand the SQL in the editor window. You may want to look at the playbacks in the reference section or worked examples to find out more information about individual sql keywords.
| 1.1 Database Design and Simple SQL |
| 1.2 One-to-Many Relationships and More SQL |
| 1.3 Many-to-Many Relationships and Even More SQL |
| 2.1 CREATE TABLE and ALTER TABLE |
| 2.2 INSERT |
| 2.3 SELECT |
| 2.4 FROM |
| 2.5 WHERE |
| 2.6 UPDATE and DELETE |
| 2.7 ORDER BY |
| 2.8 Aggregate Operators, GROUP BY, and HAVING |
| 2.9 Nested Queries with IN and Common Table Expressions |
| 2.10 UNION, INTERSECT, EXCEPT |
| 2.11 Transactions |
| 2.12 CREATE INDEX |
| 3.1 Which Dogs Have Had the Most Visits? |
| 3.2 Number of Adoptions and Average Age |
| 3.3 Locations with Least/Most Aggressive Dogs |
| 3.4 Average Time to Adoption By Location |
| 3.5 Finding Available Capacity at Each Location |
| 3.6 Who Visited then Adopted an Aggressive Dog |