SQL Introduction:




To manipulate the data in relational model, Codd introduced a language called DSL/Alpha along with the definition of relational model. When he released his paper the IBM authorized a group to establish a framework that was based on the Codd’s ideas. This group later created a simplified version of DSL/Alpha.




This simplified version was called SQUARE. The SQUARE was then revised and a new language came into being that was called SEQUEL that is English Query Language and then finally named as SQL. SQL is recognized with the relational model. This is because the result obtained by the SQL query is a table. From this we can conclude that a permanent table can be made in a relational database by storing the result set of a query.

SQL stands for structured Query Language that is used to create and maintain the relational database and management of database within a database. SQL is a non procedural language. A non procedural language is concerned about the result of an operation.

 

Data Sublanguage

SQL language can also be called as the data sublanguage as SQL is used with many other programming languages which include C and JAVA languages that have nothing to do with the manipulation of data in a database.

Therefore, SQL is used with application languages to do efficient manipulation of data and is then considered as the sublanguage.

 

A Query language

A query language is used to perform different operations on the databases. The most popular query language is SQL. It stands for Structure Query Language. The statements of SQL are written in simple English like sentences. Come of the important commands of SQL are SLECT, DELETE, CREATE, and MODIFY, UPDATE and INSERT etc. SQL can be used for the following purposes:

  • Create table structures
  • Enter data in tables
  • Retrieve data from tables
  • Update data in tables
  • Delete data from tables etc.

 

SQL: A non procedural Language

In the programming languages such as C++, Visual Basic, C# etc. definition of variables and data structures was used. These type of languages also used the conditional logic that is if else structure and also used the looping constructs for example for loop, while loop etc.

These programming languages are known as the procedural languages because you have a full control on what the program is doing or what it is about.

From this we conclude that a procedural language include both the result or the mechanism or the process of the program by which the result is evaluated. Whereas, a non procedural language only defines the desired result but mechanism through which the desired result was generated is defined from an external source.

 

SQL Statement Classes

The SQL language is further divided into different parts. These parts are called SQL classes. The SQL classes are widely used to define the data structures that are stored in database. The SQL statements basically are the commands that are used to perform a specific task.

The database elements that are stored in a special set of tables is called data dictionary. The SQL statements are also used to define the inputs and outputs. The various statements in SQL language are as follows:

SQL Schema Statements

The SQL schema statements are used to define the data structures that are stored in the database. After designing a table, the next step is the generation of SQL statements to create the tables in the database.

Everything in the schema statement should be explained in detail. When a table is defined, you should tell the database server that which column or columns will serve as the primary key for the table.

This is done when a constraint is created in the table. This constraint will be the primary key constraint. For example, when a new table is created in a database you will use the SQL schema statement that is CREATE table.

SQL data statements

The SQL data statements are used to manipulate the data structures that were defined previously by the schema statement. For example when a new table is created in a database, the process of populating the new table with data will require the SQL data statement INSERT.

SQL transaction statements

The SQL transaction statements are used to begin, end and roll back transactions. For example the SQL START transaction command will cause the previous transaction to be committed.

The SQL language also allows you to maintain and to create the database objects although it is a non procedural language. Through the SQL language you can also secure and manipulate the data within the data objects.

The common method that is used to categorize the SQL statements is to divide the statements according to the functions that they define or perform. Therefore the SQL can be separated into various types of statements:




Data Definition Language (DDL)

The data definition statements are used to define, create, modify and delete the database objects. The database objects are: tables, views, schemas, domains, triggers, and the procedures that are stored. The changes made by these statements will be permanent and cannot be got back.

The SQL keywords that are associated with the DDL statements are: CREATE, TRUNCATE, RENAME, ALTER and DROP.

  • The CREATE command or keyword is used to create a table.
  • The ALTER TABLE statement is used to modify or to make alteration in the table or modification of table’s properties.
  • The DROP TABLE statement is used to delete the definition of the table from the database.
  • The TRUNCATE statement is used to delete the data stored in the table.
  • The RENAME statement is used to change the name of the table.

 

Data Manipulation Language (DML)

The data manipulation language statements are used to retrieve, add, delete, and modify the data that is stored in the objects of database. The keywords or statements that are associated with the data manipulation language are: SELECT INSERT, UPDATE and DELETE. These are the primary statements of data manipulation language (DML) and are used widely.

  • The INSRT statement is used to insert a new row in the database that is adding data to a table.
  • The SELECT statement is used to retrieve record from one or more tables.
  • The UPDATE statement is used to update the data or row in the table.
  • The MERGE statement is used to merge two rows or two tables in the database.
  • The DELETE statement is used to delete a row from the table in the database.

 

Transaction Control Language (TCL)

The Transaction Control Language commands are used to track the effects of other commands on the database and are also used to control the transactional processing in a database.

The changes made using the transaction control command are permanent and cannot be altered. The primary keywords or commands of transaction control language are SAVEPOINT, ROLLBACK, and COMMIT.

  • The SAVEPOINT command is used to save temporarily.
  • The COMMIT command or statement is used to save permanently.
  • The ROLLBACK command is used to revoke the changes.

 

Data Control Language (DCL)

The Data Control Language statements are used to allow the user to have a control to check that who can access the specified objects in the database. The database user can be a person or the application program.

With data control language you can restrict or grant the access for the database objects using GRANT and REVOKE statements of data control language (DCL). These two statements that are GRANT and REVOKE are the primary statements of the data control language.

The data control language also allows you to control the type of access that each of the users will have to the objects of database. For example, you can specify that which users can view the specific set of data and which users can manipulate that specific set of data.

  • The GRANT statement is used to grant the permission of right to the user.
  • The REVOKE statement is used to tack back the permission of right from the user.




Data Query Language

The Data Query Language is similar to the data manipulation language. If the user wants to retrieve, INSERT, DELETE and MODIFY data in a relational database management system then he should use the Data Query Language and the Data Manipulation language.

  • The SELECT statement is used to retrieve record from one or more tables.