Sunday, September 12, 2010

SQL Basics

I thought to write few posts about SQL and Database testing. This post is the result of that interest.

SQL - Structured Query Language. SQL is used to interact with databases. SQL is a non-procedural language and has few standards. SQL is standardized by the American National Standards Institute (ANSI).

Rules for writing SQL statements

  • It is not case sensitive.
  • It cannot be abbreviated or split across lines.
  • It can be on one or more lines.
  • Indents are used to enhance readability.
  • Clauses are usually placed on separate lines.

SQL's Components / Sub Languages
SQL consists of few components and given below:
  • DDL - Data Definition Language - Create, Alter, Drop, Truncate - For creating, altering and dropping the database objects like tables, viewes, indexes, synonyms,sequences and contraints.
  • DML - Data Manipulation Language - Insert, Update, Delete - For reading and updating data.
  • DRL - Data Retrieval Language - SELECT - For querying the data. SELECT has many clauses.
  • TCL - Transaction Control Language - Commit, Rollback, SavePoint - These allow to group one or more DML statements into units of work or transactions. All statements in a transaction either succeed or fail as a single group.
  • DCL - Data Control Language - Grant, Revoke - For managing database security, by creating user ids and assigning or removing privileges from them.