it-ebooks - SQL for Web Nerds

SQL for Web Nerds by Philip Greenspun

---

1. : one table, one table with subquery, JOIN, JOIN with subquery, OUTER JOIN
2. : GROUP BY, aggregates, HAVING
3. (inserts and updates)
4. : PL/SQL and Java executing inside the Oracle server
5. in Oracle; how they will bite you and how to work around them
6. , what to do when your query runs too slowly
7. , what to do when your query doesn't answer your questions
8. , making foreign Web sites look like local SQL tables
9. 
   Appendix A:
   Appendix B:

More:

• the complete Oracle doc set is available online, as a link from www.oracle.com; some particularly interesting books are the Application Developer's Guide and Concepts
• Oracle 10g: The Complete Reference

---

philg@mit.eduvar _gaq = _gaq || [];_gaq.push(['_setAccount', 'UA-315149-1']);_gaq.push(['_trackPageview']);(function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);})();

Preface to

---

The last thing the world needs is another SQL tutorial. So here'sours ...

We could wallpaper all 500 rooms of the computer science building at MIT with different SQL tutorials from various publishers. Yet when it came time to sit down with our students and teach them this material, we couldn't bear to use any of the commercial texts.

The CS department at MIT doesn't offer a course in SQL (or in any other computer language per se for that matter). But lots of universities do and therefore you can choose from many textbooks designed to teach SQL to 20-year-old CS majors. Perfect, eh? Not for us. Part of the problem is that universities try to give students what they deem to be eternal knowledge. So rather than focus on the relational model and SQL, the overwhelmingly dominant system for the past 20 years, introductory database textbooks spend several chapters talking about database management systems that were used in the 1960s.

The second part of the problem with college-level texts is that these books are too dreary and long. In our class, the students face the tangible problem of building a sophisticated db-backed Web service in 12 weeks. This motivates them to learn whatever intricacies of SQL that they need. But what keeps a student going through 500 pages of SQL and RDBMS? We can't figure this out.

The third problem is that most of the college-level textbooks bring to mind the old question "What is the difference between a tenured professor of computer science and an ape?" (The ape doesn't think he can program.) Sure, the academic egghead author can learn the syntax of SQL but he or she won't have any personal experience with real-life interesting systems. That's because no real user of the RDBMS is stupid enough to hire a professor to write any SQL code.

In our computer-obsessed society, we need not be stuck with the dry theoretical offerings of computer scientists. Walk into any bookstore and you'll find SQL tutorials. Sadly, due to structural problems in the trade computer book industry (see (Bowman, Emerson, Darnovsky; Addison-Wesley). We truly do like this book so we can feel free to pick on it:

• though it is 450 pages long, by straining to offer complete coverage of simple boring stuff the authors run out of space and energy to cover the interesting and sophisticated stuff
• students have to learn SQL in the context of a data model; like its competitors The Practical SQL Handbook forces readers to live in the dessicated world of business data processing for several hundred pages. This is where the RDBMS started in the 1970s and if you want to get a job in a corporate IS department, this is not a bad place to live while learning SQL. However, we're trying to demonstrate how the concurrency control and transaction management capabilities of the RDBMS enable the construction of powerful reliable Web services. We can dazzle the students with much more interesting and relevant data models than Practical SQL's "bookbiz database about a fictitious publishing company".
• the authors write with the assumption that the reader is unaccustomed to thinking formally and using formal languages
• the authors avoid the ugly fact that SQL is not a standard. Most SQL queries involve dates and times. Yet there are only two pages out of 450 involving the date data type. Why? The authors don't explain but perhaps it is because they didn't want to say "here's what ANSI SQL date-time arithmetic looks like and, by the way, Oracle has completely different syntax and semantics."
• the authors assume that the Web doesn't exist, i.e., that the physical book must be self-contained and comprehensive. Anyone actually using an RDBMS is going to have full documentation on the Web or at least (shudder) on CD-ROM. This book started on the Web and therefore we assume that we can cover the interesting and pedagogically valuable stuff then link students to the full documentation.

Bashing other authors and publishers is fun but isn't pedagogically effective. Thus it is probably worth stating what this SQL tutorial tries to do.

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 . Sometimes we drag readers into the dreary world of commerce but at least it is the flashier-than-average corner of ecommerce.

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.
Next:

---

philg@mit.eduvar _gaq = _gaq || [];_gaq.push(['_setAccount', 'UA-315149-1']);_gaq.push(['_trackPageview']);(function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);})();

Introduction by

---

After writing a preface lampooning academic eggheads who waste a lot ofink placing the relational database management system (RDBMS) in thecontext of 50 years of database management software, how does this bookstart? With a chapter placing the RDBMS in the context of otherdatabase management software.Next page