Structured Query Language

Structured Query Language (SQL) is a generic term for the set of computer languages designed for querying and managing Relational Database Management Systems (RDBMS). RDBMSs organize data using tables, where each table has named column(s). Each column has one datatype, and data is stored as rows intersecting with these columns and therefore satisfy the datatype of the corresponding column. For example, a student table could have two columns, student_id and student_name. If I want to insert data into this table, I would insert a row of two values, a student_id e.g. 001 and a student_name e.g. Claire. It is this concept of tables that is the basis of SQL, which has become the de facto standard adopted by most database management systems vendors. However, most vendors provide an extension to SQL to serve various functions ranging from the integration of procedural constructs to exception handling.

SQL Data Manipulation Language

The Data Manipulation Language (DML) defines and manipulates the content of tables.It provides the ability to

• query databases to retrieve data
• insert records
• update records
• delete records

Syntax of the SELECT statement

The syntax of a simple SQL query:

SELECT select-list 
FROM table1, table2, ... 
WHERE predicates
GROUP BY grouping-columns
HAVING predicates

All rows in the table specified in the FROM clause are retrieved. If more than one table is specified, the Cartesian product of the rows is produced. Next, the rows not satisfying the predicates provided in the WHERE clause are eliminated. The predicates are a set of conditions separated by AND, OR and NOT. The conditions are tests using one or more of the following conditional operators - less than '<', less than or equal '<=', equal '=', not equal to '<>' or '!=', greater than '>', greater than or equal '>=' and LIKE. Then the GROUP BY clause groups the remaining rows according to the equal values in the specified grouping-columns. Next, groups not satisfying the predicates specified in the HAVING clause are eliminated. Finally, the columns specified in the select-list are returned. The select-list is a comma-separated list of column names, or could be an asterisk, '*' which means 'all columns'.

The WHERE,GROUP BY and HAVING clauses are optional.

Syntax of the INSERT statement

INSERT INTO table_name
VALUES (value1, value2, ...)

If you do not intend to supply a value for every column, your INSERT statement should have the syntax

INSERT INTO table_name (''column'', ..., ''column'')
VALUES (value1, value2, ...)

where columns are the names of columns that you want to populate with data value1, value2, etc.

Syntax of the UPDATE statement

UPDATE table_name
SET column = value
WHERE predicates

Syntax of the DELETE statement

DELETE FROM table_name
WHERE predicates

To delete all records from the table,

DELETE FROM table_name

JOIN statements

A join is useful for presenting information from multiple tables in a single table. There are several types of joins.

Syntax of the INNER JOIN statement

An inner join returns only those rows where there is a match across tables. It considers the columns on which the join is to be performed and returns only rows with matching values in that column.

SELECT select_list
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Syntax of the LEFT OUTER JOIN statement

A left outer join returns all rows from the first table, even if the table it is being joined with has no matching rows.

SELECT select_list
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Syntax of the EIGHT OUTER JOIN statement

A right outer join returns all rows from the second table, even if the table it is being joined with has no matching rows.

SELECT select_list
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Examples

Here is a simple example that returns details from a table called 'students', only if the row has a GPA higher than 3: SELECT name, rollnum, totalmarks FROM students WHERE gpa > 3;

The columns 'name', 'rollnum' and 'totalmarks' must be present in the 'students' table, although the table could have other columns. The result is a set of rows that satisfy the condition (gpa > 3).

Queries can be nested in order to give more specific results as desired.

SQL Data Definition Language

With the DDL, you can create and delete database tables. Furthermore, it allows the creation of other structures such as indexes and constraints.

Syntax of the CREATE statement

Creating a table

CREATE TABLE table_name
(columnName_1 dataType constraint,
columnName_2 dataType constraint,
.
.
.
)

'constraint' is optional. Its possible values include 'unique', 'primary key' and not 'null'. Unique ensures that values in that column are not repeated - no two rows can have the same value in that column. Not null ensures that a value is provided for all cells in that column. A primary key is unique and not null by definition.

Creating an index

CREATE INDEX index_name
ON table_name(columnName_1, columnName_2, ...)

Syntax of the DROP statement

Deleting a table

DROP TABLE table_name

Deleting an index

DROP INDEX index_name

Syntax of the TRUNCATE statement

To delete only the data within a table, without deleting the table itself, the TRUNCATE statement is used.

TRUNCATE TABLE table_name

Syntax of the ALTER statement

ALTER TABLE table_name 
ADD columnName datatype

ALTER TABLE table_name 
DROP COLUMN columnName

SQL server database implementations

• MySQL - A free, open-source implementation of SQL
• Microsoft SQL Server - Microsoft's version of a SQL Enterprise Database Server