Types & Difference Between SQL and Programming Languages
1 Types & Difference Between SQL and Programming Languages
The first thing you need to understand about SQL is that it not a procedural language. To solve a problem in procedural language, you have to write a procedure that performs one operation after another until the task is complete.SQL is non procedural. To solve a problem with SQL,instead of telling the system how to get you what you want, you simply tell it what you want. The DBMS decides the best way to get you what you asked for.
To illustrate what I mean by “tell the system what you want” suppose you have an EMPLOYEE table and you want to retrieve from it the rows corresponding to all your “senior” people. You want to define a senior person as anyone over age 50 or anyone earning more than Rs 2,00,000 per year. You could do so with the following query:
SELECT * FROM EMPLOYEE WHERE AGE>50 OR SALARY>2,00,000;
This statement will retrieve the desired rows with SQL, you are not responsible for specifying how to retrieve the information. The database engine examines the database and decides for itself how to fulfill your request. You only need to specify what data you want to retrieve.
To solve problems in a procedural programming language (such as Basic, C, COBOL, FORTRAN, and so on), you write lines of code that perform one operation after another until the program completes its tasks. The program may execute its lines of code in a linear sequence or loop to repeat some steps or branch to skip others. In any case, when writing a program in a procedural language, the programmer specifies what is to be done and how to do it.
SQL, on the other hand, is a nonprocedural language in that you tell SQL what you want to do without specifying exactly how to accomplish the task. The DBMS, not the programmer, decides the best way to perform the job. Suppose, for example, that you have a CUSTOMER table and you want a list of customers that owe you more than $1,000.00. You could tell the DBMS to generate the report with this SQL statement:
NAME, ADDRESS, CITY, STATE, ZIP, PHONE_NUMBER,
BALANCE_DUE > 1000.00
If writing a procedural program, you would have to write the control loop that reads each row (record) in the table, decides whether to print the values in the columns (fields), and moves on to the next row until it reaches the end of the table. In SQL, you specify only the data you want to see. The DBMS then examines the database and decides how best to fulfill your request.
Interactive and Embedded SQL
SQL is a simple and powerful language used to create, access and manipulate data and structure in the database.SQL is the only language through which you create or modify data and structures in Oracle. Although other tools will connect with the database, they all use SQL to read and modify data. SQL provides a small and concise set of commands. This helps us to save time and reduce the amount of programming required to perform complex queries.SQL also helps us to modify a database application in a much easier way. There are two types of SQL.
a)Interactive SQL: In this form of SQL we enter a command, it is executed and we can get the output. Interactive SQL allows the programmer or database administrator to quickly and easily define, update, delete, or look at data for testing, problem analysis, and database maintenance. A programmer, using interactive SQL, can insert rows into a table and test the SQL statements before running them in an application program. A database administrator can use interactive SQL to grant or revoke privileges, create or drop schemas, tables, or views, or select information from system catalog tables.
After an interactive SQL statement is run, a completion message or an error message is displayed. In addition, status messages are normally displayed during long-running statements.
Interactive SQL Command File
An Interactive SQL command file is a text file with semicolons placed at the end of commands as shown below.
CREATE TABLE t1 ( .. );
CREATE TABLE t2 ( .. );
CREATE LF INDEX i2 ON t2 ( .. );
An Interactive SQL command file usually carries the extension .sql. To execute a command file, either paste the contents of the file into the Interactive SQL command window (if the file has less than 500 lines) or enter a command that reads the file into the command window. For example, the READ statement:
reads the Interactive SQL commands in the file makedb.sql.
b)Embedded SQL: Embedded SQL consist of SQL commands put inside of programs that are written in other language such as Fortran,Pascal,COBOL,C.This can make these programs more powerful and efficient. The output of SQL command in embedded SQL is passed off to variables enabled by the program in which it is embedded.
Embedded SQL was once the predominant standard way to mix SQL statements with host languages. It lets you mix SQL statements directly into an application program written in some common computer programming language. It is especially associated with COBOL or PL/I programs and IBM's DB2 and big iron; however. Most of the big DBMS vendors support it on microcomputers too. Support is weak among small DBMS vendors, and especially weak for computer host languages that aren't currently in vogue.
SQL can be embedded into many host languages, but support varies depending on the vendor and depending on the language. The following are the standard host languages (that is, the ones mentioned in the SQL Standard). A DBMS that supports the embedded SQL binding style must support SQL embedded into at least one of these languages: