Additional Features of SQL – Pedagogy Zone

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:

• we will present the following additional 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. 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). 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 discuss these commands 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 privilege 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 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.
• 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