SQL Coding

Databases are at the heart of many enterprise applications, and it is very important to pay special attention to database programming. Everywhere you'll, from small websites or accounting applications, to the largest analytical applications such as a Web Search Engine, you will find that databases play essential role.

What is SQL?

SQL is a database computer language designed for retrieval and management of data in relational database management systems ( RDBMS ), database schema creation and modification, and database object access control management.

SQL is a standard interactive and programming language for querying and modifying data and managing databases. It is both ANSI and ISO standard. However, many proprietary database systems use their own variations to it.

Largest Datbase Vendors: Oracle, Microsoft SQL Server, MySQL, IBM. There are other large proprietory databases that currently surpass these in size.

SQL is a set-based, declarative query language. However extensions of Standard SQL has long been developed that allow to expand it with procedural programming language functionality. Examples of extensions:

Source	Common Name	Full Name
ANSI/ISO Standard	SQL/PSM	SQL/Persistent Stored Module
IBM	SQL PL	SQL Procedural Language
Microsoft/ Sybase	T-SQL	Transact-SQL
MySQL	MySQL	MySQL
Oracle	PL/SQL	Procedural Language/SQL
PostgreSQL	PL/pgSQL	Procedural Language/PostgreSQL Structured Query Language

Additional SQL Extensions

SQL:2003 standard defines additional extensions to increase functionality. These are:

SQL/CLI, or Call-Level Interface - extension is defined in ISO/IEC 9075-3:2003, defines common interfacing components (structures and procedures) that can be used to execute SQL statements from applications written in other programming languages
SQL/MED, or Management of External Data - extension is defined by ISO/IEC 9075-9:2003
SQL/OLB, or Object Language Bindings - extension is defined by ISO/IEC 9075-10:2003. SQL/OLB defines the syntax and symantics of SQLJ, which is SQL embedded in Java
SQL/Schemata, or Information and Definition Schemas - extension is defined by ISO/IEC 9075-11:2003. SQL/Schemata defines the Information Schema and Definition Schema
SQL/JRT, or SQL Routines and Types for the Java Programming Language - extension is defined by ISO/IEC 9075-13:2003. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications
SQL/XML, or XML-Related Specifications - extension is defined by ISO/IEC 9075-14:2003
SQL/PSM, or Persistent Stored Modules - extension is defined by ISO/IEC 9075-4:2003. SQL/PSM standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors, and more

Common SQL Statements

Common SQL Statements are:

Queries - represented by SELECT statement. Example:

SELECT * FROM ORDERS WHERE TOTALAMOUNT > 100.00 ORDER BY TOTALAMOUNT DESC

Data Manipulation - represented by INSERT, UPDATE, DELETE and MERGE statements. Example:

INSERT INTO ORDERS (ORDERNUMBER,CUSTOMERNAME,TOTALAMOUNT, ORDERDATE) VALUES (123,'John Doe',150.00,GETDATE())

Data Definition - represented by CREATE, DROP, TRUNCATE and ALTER statements. Example:

CREATE TABLE ORDERS (
    ORDERNUMBER INT NOT NULL,
    CUSTOMERNAME VARCHAR(100),
    TOTALAMOUNT MONEY, 
    ORDERDATE DATE NOT NUL,
    PRIMARY KEY (ORDERNUMBER)
);