5.7 Chapter Summary
Oracle, IBM’s DB2, Informix, Sybase, MySQL, Microsoft SQL Server, PostgreSQL, Microsoft Access, Teradata, MariaDB, Apache OpenOffice Base, LibreOffice Base, and other relational database management systems use SQL, a standard relational data definition language (DDL) and data manipulation language (DML). On the logical level, each relation is represented by a base table. The external level consists of views, which are created from subsets, combinations, or other operations on the base tables. A base table can have many indexes defined on it. Dynamic database definition allows the structure to be changed at any time.
SQL DDL commands CREATE TABLE and CREATE INDEX are used to create the base tables and their indexes. Several built-in data types are available for strictly relational databases. Constraints can be specified on the column or table level. The ALTER TABLE command allows changes to the structure of an existing table, such as adding a new column, dropping a column, changing data types, or changing constraints. The RENAME TABLE command allows the user to change a table’s name. DROP TABLE and DROP INDEX remove tables and indexes, along with all the data in them, from the database.
The DML commands are SELECT, UPDATE, INSERT, and DELETE. The SELECT command has several forms, and it performs the equivalent of the relational algebra SELECT, PROJECT, and JOIN operations. Options include GROUP BY, ORDER BY, GROUP BY ... HAVING, OFFSET, and FETCH. Aggregate functions include COUNT, SUM, AVG, MAX, MIN, VARIANCE, and STDDEV. The SELECT statement can operate on various joins of tables and can handle subqueries, including correlated subqueries. Expressions and set operations are also possible. The UPDATE command may be used to update one or more columns in one or more records. The INSERT command can insert one or more records, possibly with null values for some columns. The DELETE operator erases records, while leaving the table structure intact.
The CREATE VIEW command is used to define a view by selecting columns from existing base tables or previously defined views. The SELECT operation can be used on views, but other DML commands are restricted to certain types of views. A view definition can be destroyed by a DROP VIEW command.
The system catalog or system data dictionary is a database containing information about users’ databases. It keeps track of the tables, columns, indexes, and views that exist, as well as authorization information and other data. The system automatically updates the catalog when structural changes and other modifications are made.