Additional Features of SQL

Additional Features of SQL

SQL has a number of additional features that we have not described in this chapter but that we discuss elsewhere in the book. These are as follows:

                                                    In Chapter 5, which is a continuation of this chapter, we will present the fol-lowing SQL features: various techniques for specifying complex retrieval queries, including nested queries, aggregate functions, grouping, joined tables, outer joins, and recursive queries; SQL views, triggers, and assertions; and commands for schema modification.

                                                    SQL has various techniques for writing programs in various programming languages that include SQL statements to access one or more databases. These include embedded (and dynamic) SQL, SQL/CLI (Call Level Interface) and its predecessor ODBC (Open Data Base Connectivity), and SQL/PSM (Persistent Stored Modules). We discuss these techniques in Chapter 13. We also discuss how to access SQL databases through the Java programming language using JDBC and SQLJ.

Each commercial RDBMS will have, in addition to the SQL commands, a set of commands for specifying physical database design parameters, file structures for relations, and access paths such as indexes. We called these commands a storage definition language (SDL) in Chapter 2. Earlier versions of SQL had commands for creating indexes, but these were removed from the language because they were not at the conceptual schema level. Many systems still have the CREATE INDEX commands.

              SQL has transaction control commands. These are used to specify units of database processing for concurrency control and recovery purposes. We dis-cuss these commands in Chapter 21 after we discuss the concept of transactions in more detail.

              SQL has language constructs for specifying the granting and revoking of privileges to users. Privileges typically correspond to the right to use certain SQL commands to access certain relations. Each relation is assigned an owner, and either the owner or the DBA staff can grant to selected users the privi-lege to use an SQL statement—such as SELECT, INSERT, DELETE, or UPDATE—to access the relation. In addition, the DBA staff can grant the

privileges to create schemas, tables, or views to certain users. These SQL commands—called GRANT and REVOKE—are discussed in Chapter 24, where we discuss database security and authorization.

              SQL has language constructs for creating triggers. These are generally referred to as active database techniques, since they specify actions that are automatically triggered by events such as database updates. We discuss these features in Section 26.1, where we discuss active database concepts.

              SQL has incorporated many features from object-oriented models to have more powerful capabilities, leading to enhanced relational systems known as object-relational. Capabilities such as creating complex-structured attributes (also called nested relations), specifying abstract data types (called UDTs or user-defined types) for attributes and tables, creating object identifiers for referencing tuples, and specifying operations on types are dis-cussed in Chapter 11.

              SQL and relational databases can interact with new technologies such as XML (see Chapter 12) and OLAP (Chapter 29).