CS352 Lecture - SQLlast revised September 5, 2008Objectives:1. To provide background on the SQL language2. To review/expand upon basic SQL DML operations (select, insert, update, delete, commit, rollback), with added coverage of subqueries, joins, recursive queries3. To introduce selected SQL DDL operations (create table, view)Materials:1. Ability to connect to database / project operations; file with queries2. Projectable of architecture of db2 system at Gordon3. Projectable of example syntax diagram from SQL Reference (connect)4. Handout showing commands used to create library example database5. Projectable of SQL data types - book pp 77, 121I. IntroductionA. As you know, although there are quite a number of commercially-available relational query language, one language has come to be especially important: Structured Query Language. (SQL-pronounced Seequel or S Q L) This lecture will serve to provide a bit of background, and also to introduce some key features of SQL that you have not seen yet.B. SQL was originally developed for use with IBM's System R - the earliest research implementation of the relational model. In its original form, it was known as SEQUEL (Structured English Query Language). Since then, it has been adopted by many commercial vendors, and has become an ANSI standard - the only query language to be thus standardized - and has undergone a number of revisions - each of which is considerably more complex than its predecessor.1. The first ANSI standard was 1986. This was revised in 1989 to yield what is now known as SQL 89 - which many commercial products implemented. (The SQL 89 standard is 120 pp.,)2. A more recent standard is SQL 92 (also known as SQL 2). (This standard is 579 pp!) The standard defines three levels of conformance, plus a transitional level between entry and intermediate:a) Entry levelb) Intermediate levelc) Full level13. A newer standard is SQL:1999 (also known as SQL 3). a) This standard is in multiple parts, totaling well over 1000 pages.b) It incorporates many extensions to more easily support multimedia and object-orientation, so we will discuss it more fully later in the course.c) There are those who argue that the extensions have resulted in a model that is no longer truly relational - for example, there is a paper I found while researching this topic on the web entitled “Great News, The Relational Data Model is Dead!”, which is basically about SQL 3.d) Some of the features in SQL 99 were added to their systems by various database vendors in the years between SQL 92 and SQL:1999; however, no commercial product fully implements this standard (or the newer SQL:2003) yet.4. An even newer standard is SQL:2003. Much of it consists of minor changes to the previous standard (some have called it a “bug-fix”); the major addition is support for XML. I downloaded a draft which consists of 14 pdf files - one of which, alone, has over 1000 pages! The “offical” version is not freely available.5. The latest standard is SQL:2006, which extends XML support.6. As it turns out, database software from different vendors typically supports slightly different dialects of SQL.7. Actually, although SQL is based on the relational data model, vendors of database systems based on other models have included a facility for accessing their database using SQL.C. SQL is both a data definition language (DDL) and a data manipulation language (DML). We will classify statements this way, but the language itself does not draw a distinction between the two types of statement in terms of how they are used. DDL and DML statements can be freely intermixed with one another.D. SQL can be used in three ways:1. Interactively.2. Embedded in an application program, to allow the program to access and or modify the database. Actually, this latter form has two variants:a) SQL statements may be embedded in the application program, and processed by a suitably modified compiler or by a pre compiler. (This is called static SQL)2b) SQL statements may be generated as character strings and processed at run time (This is called dynamic SQL). (You have had some exposure to this using JDBC)3. Modules consisting of SQL statements can be stored with the tables in the database, to be invoked under various circumstances. (The OO idea of combining state and behavior!)4. We will focus on interactive SQL for now - static SQL will come later in the course (when you do your project) a) Static SQL can use any of the capabilities of interactive SQL, plus there are some statements that are only needed in embedded SQLb) We will not deal with dynamic SQL at all in this course - you had some exposure to it by way of JDBC in CS211.c) We will not deal with SQL modules in this course - that's an advanced topic beyond the scope of CS352.E. The version of SQL we will be studying is that implemented as part of IBM’s DB2 product. 1. This version of SQL implements most (but not all) of the SQL 92 standard, plus many parts of the SQL 99 standard.2. The following diagram shows the architecture of the way we have installed DB2 here at Gordon. The diagram uses few terms that are used in the IBM documentation in a way that is somewhat differently from the way we used them in our theoretical discussion - specifically, the terms “instance”, “database” and “schema”.PROJECTThere are three types of software installations we are using:(1) The server version of the db2 software is installed on our departmental server machine - moses. This is also where all the database data physically resides.(2) Client versions of the db2 software are installed on our 8 workstations. This allows them to access a database on the server interactively, or to run application programs that access a database on the server. They do not contain any of the actual database data. They do, however, contain a catalog that records information about databases they can connect to (in this case, databases on moses; but a client’s catalog could actually contain references to databases on many different servers.)34client (otherworkstation)db2 software(client installation)db2 software(server installation)instanceinstancedatabasedatabasedatabaseschemaschemaschemaserver (moses)client (otherworkstation)client (any system)JDBC driver for db2NETWORKclient (otherworkstation)db2 software(client installation)catalogdb2 software(client installation)catalog(3) It is also possible for any system that has the db2 JDBC