From EduTech Wiki
Jump to: navigation, search

1 Definition

According to Wikipedia, “SQL, commonly expanded as Structured Query Language, is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management. SQL has been standardized by both ANSI and ISO.”, retrieved 11:47, 4 September 2007 (MEST).

See also: MySQL and the SQL and MySQL tutorial (this article just provides a short summary of SQL and points to further reading on Wikipedia)

2 Overview of language elements

This overview chapter is copied from Wikipedia SQL article. (retrieved 11:47, 4 September 2007 (MEST)) with minor markup modificiations. Its contents are available under the GNU Free Documentation License. Links lead to specialized Wikipedia articles.



  • Queries which retrieve data based on specific criteria.
  • Expressions which can produce either (computing) scalar values or tables consisting of columns and (database) rows of data.
  • Clauses which are (in some cases optional) constituent components of statements and queries.[1]
  • SQL statements also include the ";" statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.

2.1 Queries


SQL queries allow the user to specify a description of the desired result set, but it is left to the devices of the management system database management system (DBMS) to plan, optimize, and perform the physical operations necessary to produce that result set in as efficient a manner as possible. A SQL query includes a list of columns to be included in the final result immediately following the SELECT keyword. An asterisk ("*") can also be used as a "wildcard" indicator to specify that all available columns of a table (or multiple tables) are to be returned. SELECT is the most complex statement in SQL, with several optional keywords and clauses, including:

  • The ORDER BY clause is used to identify which columns are used to sort the resulting data, and in which order they should be sorted (options are ascending or descending). The order of rows returned by a SQL query is never guaranteed unless an ORDER BY clause is specified.

The following is an example of a SELECT query that returns a list of expensive books. The query retrieves all rows from the books table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title. The asterisk (*) in the select list indicates that all columns of the books table should be included in the result set.

FROM books
WHERE price > 100.00
ORDER BY title;

The example below demonstrates the use of multiple tables in a join, grouping, and aggregation in a SQL query, by returning a list of books and the number of authors associated with each book.

SELECT books.title, count(*) AS Authors
FROM books
JOIN book_authors 
ON books.isbn = book_authors.isbn
GROUP BY books.title;

Example output might resemble the following:

Title                   Authors
SQL Examples and Guide     3
The Joy of SQL             1
How to use Wikipedia       2
Pitfalls of SQL            1
How SQL Saved my Dog       1

(The underscore character "_" is often used as part of table and column names to separate descriptive words because other punctuation tends to conflict with SQL syntax. For example, a dash "-" would be interpreted as a minus sign.)

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 books 
NATURAL JOIN book_authors 
GROUP BY title;

However, many vendors either don't support this approach, or it requires certain column naming conventions. Thus, it is less common in practice.


SELECT isbn, title, price, price * 0.06 AS sales_tax
FROM books
WHERE price > 100.00
ORDER BY title;

2.2 Data manipulation


  • INSERT is used to add rows (formally tuples) to an existing table, eg:
INSERT INTO my_table (field1, field2, field3) VALUES ('test', 'N', NULL);
UPDATE my_table SET field1 = 'updated value' WHERE field2 = 'N';
DELETE FROM my_table WHERE field2 = 'N';

2.3 Transaction controls




UPDATE inventory SET quantity = quantity - 3 WHERE item = 'pants';

2.4 Data definition




CREATE TABLE my_table (
 my_field1   INT,
 my_field2   VARCHAR (50),
 my_field3   DATE         NOT NULL,
 PRIMARY KEY (my_field1, my_field2) 

2.5 Data control




GRANT SELECT, UPDATE ON my_table TO some_user, another_user.

2.6 Other



SELECT * FROM inventory -- Retrieve everything from inventory table

3 References

  1. ANSI/ISO/IEC International Standard (IS). Database Language SQL, Part 2: Foundation (SQL/Foundation). 1999

4 Links

  • SQL (Wikipedia)
Introductory Tutorials
  • What is SQL ? (SQL Course.com). Includes an online SQL interpreter where you can test expressions.
  • Greespun. Philip (2003). SQL for Web Nerds, HTML
  • SchemaSpy analyzes schema metadata, letting you click through the hierarchy of tables' parent/child relationships. Works with just about any RDBMS that supports JDBC (Oracle/MySQL/DB2/SQL Server/PostgreSQL/Sybase/etc).