Sunday, 3 July 2011

Structured Query Language (SQL)

Almost all relational database management systems use SQL (Structured Query Language) for data manipulation and retrieval. SQL is the standard language for relational database systems. SQL is a non-procedural language, where you need to concentrate on what you want, not on how you get it. Put it in other way, you need not be concerned with procedural details.

SQL Commands are divided into four categories, depending upon what they do.

 DDL (Data Definition Language)
 DML (Data Manipulation Language)
 DCL (Data Control Language)
 Query (Retrieving data)

DDL commands are used to define the data. For example, CREATE TABLE.

DML commands such as, INSERT and DELETE are used to manipulate data.

DCL commands are used to control access to data. For example, GRANT.

Query is used to retrieve data using SELECT.

DML and Query are also collectively called as DML. And DDL and DCL are called as DDL.

Data processing Methods
Data that is stored is processed in three different ways. Processing data means retrieving data and deriving information from data. Depending upon where it is done and how it is done, there are three methods.

 Centralized data processing
 De-centralized data processing
 Distributed data processing

Centralized data processing
In this method the entire data is stored in one place and processed there itself. Mainframe is best example for this kind of processing. The entire data is stored and processed on mainframe. All programs, invoked from clients (dumb terminals), are executed on the mainframe and data is also stored in mainframe.

Decentralized data processing
In this data is processed at various places. A typical example is each department containing its own system for its own data processing needs. See figure 7, for an example of decentralized data processing. Each department stores data related to itself and runs all programs that process its data. But the biggest drawback of this type of data processing is that data is to be duplicated. As common data is to be stored in each machine, it is called as redundancy. This redundancy will cause data inconsistency. That means the data stored by two departments will not agree with each other.

Data in this mode is duplicated, as there is no means to store common data in one place and access from all machines.

Distributed Data Processing (Client/Server)
In this data processing method, data process is distributed between client and server. Server takes care of managing data. Client interacts with user. For example, if you assume a process where we need to draw a graph to show the number of students in a given month for each subject, the following steps will take place:

1. First, client interacts with user and takes input (month name) from user and then passes it to server.
2. Server then will query the database to get data related to the month, which is sent to server, and will send data back to client.
3. The client will then use the data retrieved from database to draw a graph.

If you look at the above process, the client and server are equally participating in the process. That is the reason this type of data processing is called as distributed. The process is evenly distributed between client and server. Client is a program written in one of the font-end tools such as Visual basic or Delphi. Server is a database management system such as Oracle, SQL Server etc. The language used to send commands from client to server is SQL (see figure 8).

This is also called as two-tier client/server architecture. In this we have only two tiers (layers) one is server and another is client.

The following is an example of 3-tier client server, where client interacts with user on one side and interacts with application server on another side. Application, which processes and validates data, takes the request from client and sends the request in the language understood by database server. Application servers are generally object oriented. They expose a set of object, whose methods are to be invoked by client to perform the required operation.

Application server takes some burden from database server and some burden from client.

In 3-tier client/server architecture, database server and application server may reside on different machines or on the same machine. Since the advent of web application we are also seeing more than 3-tiers, which is called as n-tier architecture. For example, the following is the sequence in a typical web application.

1. Client- web browser, sends request to web server.
2. Web server executes the request page, which may be an ASP or JSP.
3. ASP or JSP will access application server.
4. Application server then will access database server.

Top 50 SQL Interview Questions & Answers

1. What is DBMS? A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be...