Basic SQL  «Prev 

SQL-Server PUBS database

What is the PUBS database?

The PUBS database is one of the more standardized databases that is referenced frequently when working with SQL for the first time. The database includes information about a theoretical operation that works with books, publishers, and the sales of those books. It lends itself well to experimenting with the different statements you will be learning.
Below is an excerpt from the movie the "Time Machine" which discusses the importance of books.

Time Machine: Rod Taylor

This browser does not support the video element.

Terminology

Having said that SQL tries to simplify one set of terms, I must say too that it does its best to complicate another. I refer to its use of the terms
  1. operator,
  2. function,
  3. procedure,
  4. routine, and
  5. method,
all of which denote essentially the same thing (with, perhaps, very minor differences). In this course I willl use the term operator throughout; thus, for example, I will refer to
  1. = (equality comparison),
  2. := (assignment),
  3. + (addition),
  4. DISTINCT, JOIN, SUM, GROUP BY
all as operators specifically. Talking of SQL, incidentally, let me remind you that (as stated in the preface) I use that term to mean the standard version of the language exclusively, except in a few places where the context demands otherwise.
  1. The standard use of terminology is sometimes not appropriate. In such situations, I generally prefer to use terminology of my own. For example, I use the term table expression in place of the standard term query expression, for the following reasons among others: First, the value such expressions denote is indeed a table and not a query.
    Second, queries are not the only context in which such expressions are used anyway.
    (As a matter of fact the standard does use the term table expression, but again it does so quite inappropriately; to be specific, it uses it to refer to what comes after the SELECT clause in a SELECT expression.)
  2. Following on from the previous point, I should add that not all table expressions win either my sense or the standard legal in SQL in all contexts where they might be expected to be. In particular, an explicit JOIN invocation, although it certainly does denote a table, cannot appear as a a stand alone table expression (i.e., at the outermost level of nesting), nor can it appear as the table expression in parentheses that constitutes a subquery.3 Please note that these remarks apply to many of the individual discussions in the body of the book; it would be very tedious to keep on repeating them, however, and I will not. (They are reflected in the BNF grammar)
  3. I ignore aspects of the standard that might be regarded as a trifle esoteric we specially if they are not part of what the standard calls Core SQL or do not have much to do with relational processing as such.
    Examples here include the so called
    1. analytic or window (OLAP) functions;
    2. dynamic SQL;
    3. temporary tables; and
    4. details of user defined types.
  4. For reasons that ar not important here, I use a style for comments that differs from that of the standard.
    To be specific, I show comments as text strings in italics, bracketed by/* and */ delimiters.