User:Kirtichaud
Contents
SQL-STURCTURED QUERY LANGUAGE
Introduction
SQL is a standard language for accessing databases. Originally based upon relational algebra and tuple relational calculus, it is a declarative language with two parts: a data definition language and a data manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control.
SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". Despite not adhering to the relational model as described by Codd, it became the most widely used database language. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. Since then, the standard has been enhanced several times with added features. But code is not completely portable among different database systems, which can lead to vendor lock-in. The different makers do not perfectly follow the standard, they add extensions, and the standard is sometimes ambiguous.
Data Definition Language
The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements:
CREATE
CREATE creates an object (a table, for example) in the database, e.g.:
CREATE TABLE My_table(
my_field1 INT,
my_field2 VARCHAR(50),
my_field3 DATE NOT NULL,
PRIMARY KEY (my_field1, my_field2)
);
DROP
DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back, e.g.:
DROP TABLE My_table;
ALTER
ALTER modifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint, e.g.:
ALTER TABLE My_table ADD my_field4 NUMBER(3) NOT NULL;
Data Manupulation Language
Transactions, if available, wrap DML operations:
START TRANSACTION (or BEGIN WORK, or BEGIN TRANSACTION, depending on SQL dialect) marks the start of a database transaction, which either completes entirely or not at all.
SAVE TRANSACTION (or SAVEPOINT) saves the state of the database at the current point in transaction
SELECT
Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Books table, the above query could be rewritten in the following form:
SELECT title,
COUNT(*) AS Authors
FROM Book NATURAL JOIN Book_author
GROUP BY title;
However, many vendors either do not support this approach, or require certain column naming conventions in order for natural joins to work effectively.
SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.
SELECT isbn,
title,
price,
price * 0.06 AS sales_tax
FROM Book
WHERE price > 100.00
ORDER BY title;
[edit]Subqueries
Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as a subquery. While joins and other table operations provide computationally superior (i.e. faster) alternatives in many cases, the use of subqueries introduces a hierarchy in execution which can be useful or necessary. In the following example, the aggregation function AVG receives as input the result of a subquery:
SELECT isbn, title, price
FROM Book
WHERE price < AVG(SELECT price FROM Book)
ORDER BY title;
