subject: Basics Of The Structured Query Language [print this page] Basics Of SQL Basics Of SQL
There is no commercial application developed today that does not use a DataBase Management System (DBMS) to store the applications business data. The DBMS, which is normally an application support layer, always exists below the commercial application codespec used to manipulate business data.
All DBMS must therefore have a natural language that will permit the application codespec to communicate with the DBMS effectively.
The Structured Query Language (i.e. SQL) is a language that provides such an interface to DBMS. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is pronounced SEQUEL.
SQL encompasses
DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs
DDL (Data Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by The American National Standards Institute (ANSI). This is an organization that approves standards in many different industries.
SQL has been deemed the standard language in DBMS, originally approved in 1986 based on IBM's implementation. In 1987, the International Standards Organization (ISO) accepted the ANSI SQL standard as the international standard. The standard was revised again in 1992 and was called SQL-92. The newest standard is now called SQL-99, it is also referred to as SQL3. SQL3 supports object extensions that are partially implemented in Oracle8 and 9. ANSI SQL also works flawlessly with MySQL, PostGre SQL and a multiple other open source, industrial strength DBMS.
This really means that ANSI SQL code that runs in one ANSI SQL compliant DBMS will run seamlessly in any other ANSI SQL compliant DBMS. This is really good news for programmers as they only have to learn a single language to be able to communicate effectively with multiple DBMS.
ANSI SQL has been the natural command language for Oracle 9i Server from any tool or application as with MySQL and PostGre SQL and other RDBMS. When an SQL statement is entered, it is stored in a part of memory called the SQL buffer and remains there until a new SQL statement is encountered.
Features Of SQL
1.SQL can be used by a range of users, including those with little or no programming experience
2.It is a non procedural language
3.It reduces the amount of time required for creating and maintaining systems
4.It is an English-like language
Components Of SQL
DDL (Data Definition Language)
This is a set of SQL commands used to create, modify and delete database structures. These command are normally used by the Data Base Administrator (DBS) and to a limited extent, a database designer or application developer who plays the role of a DBA when required.
These commands are not used by general users, who should access the DBMS tables and their data via the application only.
These statements execute immediately. They are not susceptible to ROLLBACK commands. It should be noted that DDL command is executed immediately after several DML statements had executed all the changes made via the DML statements would be made permanent first (i.e. the DDL statement would COMMIT all the changes made by every DDL command implicitly
Anybody using DDL statements must have the CREATE object privilege and a DBMS area in which to create such objects.
DML (Data Manipulation Language)
This is a set of SQL commands used to view, insert, modify and delete business data within database tables.
DCL (Data Control Language)
This is a set of SQL commands used that control access to any database and its table data. Occasionally DCL statements are grouped with DML statements.
