Key words

A Database is a collection of persistent data that is used by the application systems of some given enterprise.

Data definition language (DDL) is a computer language for definition data structures.

Data manipulation language (DML) is a family of computer languages used by computer programs and/or database users to insert, delete and update data in a database.

The Database management system (DBMS) provides users with a perception of the database that is elevated somewhat above the hardware level, and it supports user operations that are expressed in terms of that higher-level perception.

The Structured Query Language (SQL) is a database computer language designed for managing data in relational database management systems (RDMBS), and originally based on Relation Algebra.

The SQL language is sub-divided into several language elements, including:

·         Clauses, which are in some cases optional, constituent components of statements and queries;

·         Expressions which can produce either scalar values or tables consisting of columns and rows of data;

·         Predicates which specify conditions that can be evaluated to SQL three-valued logic Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow;

·         Queries which retrieve data based on specific criteria;

·         Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics;

·         Insignificant whitespace which is generally ignored in SQL statements and queries, making it easier to format SQL code.

These elements are reviewed at fig.1.

Figure 1 – Language elements

The SQL consists of:

·         Data definition language (DDL). The DDL portion consists of those declarative constructs of SQL that are needed to declare database objects (for example, CREATE TABLE);

·         Data manipulation language (DML). The DML portion consists of those executable statements of SQL that insert, update and delete information into the database (for example, INSERT clause);

·         Data control language (DCL) is a computer language and a subset of SQL, used to control access to data in a database (for example, GRANT and REVOKE);

·         Transaction control language (TCL) is a family of computer languages used by database systems to control transactions (for example, COMMIT and ROLLBACK).

The SQL DML can operate at both the external level (on views) and the conceptual level (on base tables). Likewise, the SQL DDL can be used to define objects at the external level (views), the conceptual level (base tables), and even – in most commercial systems, though not in the standard per se – the internal level as well (indexes or other auxiliary structures).

Moreover, SQL also provides certain data control facilities that cannot really be classified as belonging to either the DDL or the DML. An example of such a facility is the GRANT statement, which allows users to grant access privileges to each other.

Standardization

SQL was adopted as a standard by the American National Standards Institute (ANSI) in 1986 as SQL-86 and International Organization for Standardization (ISO) in 1987. Until 1996, the National Institute of Standard and Technology (NIST) data management standards program certified SQL database management systems (DBMS) compliance with the SQL standard. Vendors now self-certify the compliance of their products. SQL standard realization most be different for different DBMS.

All examples in this aids tested in PostgreSQL 8.2

CREATE/DROP TABLE

CREATE TABLE will create a new, initially empty table in the database.

Synopsis: 

               CREATE TABLE table_name ( 

               [ { column_name data_type [DEFAULT expr] 

                               [column_constraint […] ] 

               | table_constraint  } ] );

The CREATE TABLE executed looks as follows:

CREATE TABLE tpost

(

  id integer NOT NULL DEFAULT nextval('tpost_id_seq'::regclass),

  name text NOT NULL,

  shortname character varying(50),

  CONSTRAINT tpost_pkey PRIMARY KEY (id)

);

CREATE TABLE statement specifies the name of the base table to be created, the names and types of the columns of that table, and the primary key and any foreign keys in that table.

DROP TABLE removes tables from the database. To empty a table of rows without destroying the table, use DELETE.

Synopsis:

               DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Next example assumes that we have table with name «tpos» and shows how to use drop clause.

DROP TABLE tpost; -- remove table tpost

“--" use for comments.

К содержанию >>