This is a free online sql book by Philip Greenspun. First and foremost, we keep our readers in the world of Web services. Most often they are working within the data model for online communities chronicled in Philip and Alex’s Guide to Web Publishing. Sometimes we drag readers into the dreary world of commerce but at least it is the flashier-than-average corner of ecommerce.
Book Description
Second, our examples are all drawn from real production Web sites that get close to 1 million requests per day. This should make the examples more interesting, particularly as the sites are mostly still up and running so the students will be able to visit pages and see the queries in action on up-to-the-minute data sets.
Third, we assume that our readers are bright and accustomed to formal languages. We don’t assume any experience with declarative languages, database query languages, or any specific programming language. But once we can assume that the reader has written code, it is possible to use more sophisticated examples and get to the interesting stuff more quickly. So if this book ends up being a bad choice for the office manager who wants to start building marketing reports, we hope that we make up for it by making it a great choice for the MIT student or the working programmer.
Fourth, we assume that our readers will be using Oracle. This is a safe assumption for our class because we set up the computing facility and, in fact, Oracle8 is the only RDBMS running on it! It is also a safe assumption for much of the world: Oracle is the most popular RDBMS system available. We find it burdensome to maintain. We wish it were open-source. We wish it were free. Yet if we accept Oracle as part of the landscape, we don’t have to waste a lot of ink pretending that SQL is a standard.
Table of Contents
- Introduction
- Data modeling
- Simple queries: one table, one table with subquery, JOIN, JOIN with subquery, OUTER JOIN
- More complex queries: GROUP BY, aggregates, HAVING
- Transactions (inserts and updates)
- Triggers
- Views
- Style
- Escaping to the procedural world: PL/SQL and Java executing inside the Oracle server
- Trees
- Handling dates in Oracle
- Limits in Oracle; how they will bite you and how to work around them
- Tuning, what to do when your query runs too slowly
- Data warehousing, what to do when your query doesn’t answer your questions
- Foreign and legacy data, making foreign Web sites look like local SQL tables
- Normalization