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.
I start 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.
Each of the 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. There are some videos that go along with the changes in the editor window. 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.
Chapter 1 Database Design and SQL
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
Chapter 2 A Beginner's Reference to 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
Chapter 3 Worked SQL Query Examples
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
If you liked these the playbacks I have 30 more here: Worked SQL Examples.
If you are curious about how to use SQLite databases in different programming languages then check out my other 'book', Programming with SQLite. This shows how to use SQLite in a C/C++, Python, and Java programs. If you'd like to stay connected and get updates when I add new playbacks you can follow me on twitter: @markm208.
Top comments (2)
Amazing sharing...
when i still beginner untill now i am alway design my database design using online database design tool from dynobird
Amazing!