AdSense

Wednesday, 10 July 2013

SQL – Structured Query Language for Database quering

Right now I am assuming that the readers are well aware of what Database means because I would be talking about SQL - a language used to interact with databases. The database in focus will be Oracle.

In Oracle, the SQL is divided into two basic groups: data definition language (DDL) or data manipulation language (DML). DDL is used to define objects within the database just as creating tables or indexes. DML is used to insert, update and delete data in the database. Finally, there is data retrieval, which is the SELECT statement.

Oracle makes use of SQL*Plus (SQL Star Plus) as the command line interface to the let user interact with Database. In a nutshell, SQL*Plus connects to the database, takes user SQL in and displays results to user.

The login format is:

"username/password@db_service_name"
select_statement

The db_service_name is looked up in the TNSNAMES.ora file located in ORACLE_HOME > network > admin > tnsnames.ora - which will be provided by the DBA (administrator).

Please note that SQL*Plus places each command into a buffer (opened as "ed"). You can edit the SQL*Plus buffer, but it is more efficient to create and run database scripts instead. Re-execute an immediately previous command by "/".

Before we look into each and every statement, here are some general points about SQL statements to conclude this post:

  • SQL is not case sensitive
  • Formatting and indentation only makes it readable to us humans. It makes no difference to servers in any aspect
  • Database metadata - data about data - like table names, index names, etc are stored in UPPER case. Use data is stored just how it is entered
  • SQL is not case sensitive