Sql Programming Language

SQL is an abbreviation which stands for Structured Query Language. Some read and pronounced it as “Sequel” while others pronounce it by reading the letters separately. SQL is a standardized query language used to retrieve information from databases. It was originally designed by International Business Machine (IBM) way back in the 70s and called its original version as SEQUEL (Structured English Query Language). Since then it has become the favorite query language for Database Management Systems running on minicomputers, mainframes, and Personal Computers up to the present.

Query languages are computer languages used to make queries to database and information system – taken from wikipedia. com. It supports databases that are spread out over several computers in a computer networks allowing transaction to take place between Server and Client computers. It is capable of handling simultaneous request from several users to access to database on a computer networks. One example of SQL’s application is on websites that allows users to register information and then do updates and search later on. SQL is the working on the background to handle everything that the user does though we cannot see it.

In the year 1979 Oracle Corporation introduced SQL as the first commercial database management system. Taken from Webopedia. com http://www. webopedia. com/TERM/S/SQL. htm There are many versions of SQL nowadays for the fact that the language itself is still expanding and evolving. In 1986, SQL version complied with American National Standard Institute (ANSI), and in the following year 1987 with the International Standard Organization (ISO). Further development was made in the following years containing expansions and revisions of the relevant parts.

Present versions of SQL compared to its old versions can already allow access to external data sources or Non-SQL data sources.

Is SQL a Programming Language?

Some people underrated SQL to be considered as a programming language. These people have forgotten to accept the definition itself which states that it is a language. When they talk of programming languages the first things that come to their minds are Assembly Language, Cobol, Fortran, Java, and C++. They insist that it is not compiled and contains lesser functions compared to the above mentioned languages.

Yes, SQL is a programming language. The languages mentioned above are 3rd Generation and High-Level Languages which were invented to ease the problem of using complex commands which are hard to memorize and incomprehensible in forms. They are all designed for their own purpose. For instance Cobol is designed for business oriented applications, C is for system programming applications. SQL on the other hand is designed for data access. Data is the most valuable element in business systems and the need to be kept, retrieve, and manipulate. And SQL is there to help.

Aside from being different in purpose, one feature of a programming language is the replacement of words to Numeric commands. These are both features of the 3rd Generation and the 4th Generation languages. For instance, the machine code represented by a combination of bits 0’s and 1’s to look for file directories is replaced by using the English word “Dir”. But if we will look at the syntax and forms that it takes it will still sound awkward English. If you are not familiar with “Dir” you could still get confused with how you are going to use it. 3rd Generation and High-Level Languages are still far closer to the real human language.

SQL on the other hand is a 4th Generation Language that is very close to the human language. For instance the SQL command “UPDATE Employees SET lastname = ‘Sequel’ WHERE fldidnumber = ‘2000-c-0001’”. The command resembles closely to an English sentence structure. It is much closer to human language to manipulate data so it eliminates the threat of using the wrong command since the command itself is much easier to understand. What would happen if you see command that takes that form in future versions of C++, in Java and in other high level languages? Will you no longer consider them as a programming language?

Another thing to remember is that these languages are now being used in combination in order to support the lacking capability of the other in completing a certain task. A programming language designed mainly for numeric calculations may find it hard to perform data access. What if a completion of task needs both data access and numeric calculations? The only solution is to consider using any possible combination between the two separately designed languages. That is why SQL commands are being embedded in some Non SQL Products. Do not underestimate the application of SQL for fast data access.

We should remember that the main point of developing computer programming languages is to make data manipulation possible through the use of computer. In order for data manipulation to be successful, computers must be instructed with particular sets of commands. These commands are coded or programmed by computer programmers that when once completed will enable data manipulation. These programmers works are often disrupted when they fail to recognize properly some of the machine based commands or partially human like words even though when given in full listings.

If there is a programming language that offers much more readability then that would be best suited in situations like this, and the best example is the SQL. Maybe its less functionality comes from the fact that conversion of all machine based codes do not only took days, but perhaps decades. And that SQL is designed primarily for data access not for creation of another complex application. But it is the easiest one to use when dealing with data access and that is undeniable. That is why up to the present SQL is still being revised and expanded with other functionalities.

In the end it is still a language and is considered 4th Generation languages.

SQL Commands

Types of SQL Commands Like other programming languages, SQL commands are categorized according to its functions. These functions include building database objects like tables and queries, manipulating objects, inserting data to existing tables, updating existing data in tables, deleting existing data from tables, performing database queries, controlling database access, and overall database administration. The main categories are:

DDL (Data Definition Language)

Data Definition Language, DDL, is consists of SQL commands that allows a user to create and restructure database objects, such as the creation or the deletion of a table. Examples of DDL Commands are:

Create Table Command

• For Adding new column: ADD “NewColumn” “data type for NewColumn 1”.
• For Deleting or Dropping an existing column: DROP “ColumnName”.
• For Changing a column name: CHANGE “OldColumnName” “NewColumnName” “data type for NewColumnName”.
• For Changing the data type for a column: MODIFY “ColumnName” “newdatatype”.

Examples: If we want to add a column for Employee Status with data type Char: ALTER table Employee add Employee_Status char(1) To rename “Employee_Status” to “EmpStat”: ALTER table Employee change Employee_Status EmpStat char(50)

Drop Table Command

Used to delete an existing table. Syntax; Drop “tablename”.

Example: Drop Employee.

Create Index Command

Indexes are created to make searches much faster. Most index are defined on fields which is mostly used for searching like the id number, or lastname fields.

Syntax: CREATE INDEX “index_name” ON “table_name” (column_name).

Example: CREATE INDEX “idxFirstname” ON “Employee” (Firstname)

Create View Command

Views are like tables, but they do not physically stores data which table does. Views only stores data temporarily.

Syntax: CREATE VIEW “VIEW_NAME” AS “SQL Statement”.

Example: CREATE VIEW VwEmployee AS SELECT FirstName, LastName, Country FROM Employee.

Other commands included in DDL are Drop View and Drop Index.

DML (Data Manipulation Language)

Data Manipulation Language, DML, is consists of SQL commands used to manipulate data within objects of a relational database. There are three basic DML commands listed below:

Insert Command

Insert command is used to add record to a database table.

Syntax: INSERT INTO “tablename” (“column1”, “column2”, … ) VALUES (“value1”, “value2”, … ).

Example: INSERT INTO Employee (Firstname, Lastname, … ) VALUES (‘John’,’Mayer’).

Update Command

This command is used to modify a certain record.

Syntax: UPDATE “tablename” SET “ColumnName” = [new value] WHERE {condition}.

Example: UPDATE Employee SET Lastname = “Eckert” WHERE IdNumber = ‘2000-c-0001’.

Delete Command

This command is used to delete records from a table.

Syntax: DELETE FROM “tablename”WHERE {condition}.

Example: DELETE FROM Employee WHERE IdNumber = ‘2000-c-0001”.

DQL (Data Query Language)

This command is used to retrieve from one or more tables in a database or from other databases.

Select Command

Syntax: SELECT “columnname” FROM “tablename”.

Example: To select only the firstname and the lastname fields of all records from table employee: SELECT Firstname, Lastname FROM Employee.

DCL (Data Control Language)

These commands allows user to configure how user can access the database. These DCL commands are normally used to create objects related to implement limitations to user access and also control the distribution of privileges among users.

Example of these commands are listed below: You will find that these commands are often grouped with other commands and may appear in a number of different lessons throughout this book.

Data administration commands

Data administration commands allow the user to perform audits and perform analyses on operations within the database. They can also be used to help analyze system performance. Two general data administration commands are as follows: START AUDIT STOP AUDIT

Transactional Control Commands

These commands allows user to manage all database transactions that are taking place within a certain period of time.

• COMMIT – this command confirms saving of all database transactions that were made by the user
• ROLLBACK – this command is used to scratch or undo all database transactions that were made by the user
• SAVEPOINT Used to create points within groups of transactions in which to be undone or ROLLBACK
• SET TRANSACTION Places a name on a transaction

Today’s business organizations practice is far more different from the past. It is mostly characterized by the computerization of manual data processing, allowing online inquiries, buying, selling, payment, money transfers, social networking, and information sharing.

Because data is the most important value for any organization, the demand in the use of SQL which is widely used in the past up to the present is expected to grow. With the expansion of SQL’s use from minicomputers, mainframes, PCs, Local Area Network, it is now working behind sophisticated internet based applications. There is a huge demand in the development of these types of applications nowadays and the demand is predicted to grow in the years to come. There are recent effort to expand SQL for multimedia purposes. We will start checking the importance of SQL versions in today’s business applications.

Perhaps one of the most powerful features of SQL is its ability to support Server-Client transactions. This is made possible by using SQL Servers which allows database creation. This is what is being implemented in internet based application, in database systems in local area networks which allow several users to access data simultaneously. A Database is created on an SQL Server placed on Large Server Computers and the server is the one that will process the request from several clients. Several Versions of SQL Servers nowadays already have Graphical user Interfaces which allows point and click operation.

Another feature of these servers are the capability to generate SQL commands which corresponds to a certain operation. This is very advantageous for users who are using only point and click option and then later on check what commands are actually done by that whole process. The point and click view is often called as Design View, while the SQL generated is called the SQL View. For example the creation by clicking a Create View Button in Design View will generate the Create View command that will be shown on SQL View.

So it offers much more advantage for beginning SQL users to master SQL Commands, and for expert users to check their manually created SQL commands by comparing to the ones generated when using point and click option in Design View.

References

1. Patrick O’Neil, “Database Principles, Programming, Performance”, Morgan Kaufmann 1994
2. Elmasri & Navathe, “Fundamentals of Database Systems”, Benjamin/Cummings, 1994.
3. Ramakrishnan, “Database Management Systems”, McGraw Hill, 1996.
4. Date & Darwin, “A Guide to the SQL Standard”, Fourth Edition, Addison-Wesley, 1993.
5. Ullman & Widom, “A First Course in Database Systems”, Prentice Hall, 1997.