Onyx.boisestate.edu

SQL Language Report
Abstract

SQL is an abbreviation of Structured Query Language. It is used for requesting and editing
information that stored in a certain database management system. The original version of SQL is
called SEQUEL (Structured English Query Language). It was first designed by an IBM research
center in 1974 and 1975. SQL was first introduced as part of commercial database system in
1979 by Oracle Corporation.
Historically, SQL has been the favorite query language for database management system running
on minicomputers and mainframes. Now into its third decade of existence, SQL offers great
flexibility to users by supporting distributed databases. It serves both industry-level and
academic needs, and is used on both individual computers and corporate servers.
Introduction
SQL is designed for a specific purpose. It is used for accessing and manipulating databases. The traditional SQL using declarative programming which makes itself not a typical language in the same sense as Java or C++. The traditional SQL is not compiled and offers limited functionality. Although SQL is an ANSI (American National Standards Institute) standard, there are many versions of the SQL language. The most important ones are: Transact-SQL that used by Microsoft SQL server, PL/SQL that used by Oracle databases, and SQL PL which is a subset of SQL that provides procedural constructs. These various SQL extensions expanded on the traditional SQL and added more functionality to it. They support local variables, loops, and subroutines that are similar to Java and C. They are considered as procedural programming languages rather than traditional SQL is a declarative language. Standard SQL is based on several elements: clauses (the components of statements and queries), expressions which consists of columns and row of data, and statements (used for sending queries from a client program to a server where the databases are stored) [1]. These elements are introduced in detail next. Traditional SQL clauses and statements
Like a sentence, a SQL statement has clauses. Each clause performs a function for the SQL statement. Some clauses are required in a SELECT statement and some are not. The basic SQL clauses are SELECT clause which lists the fields that contains data of interest. The FROM clause lists the tables that contain the fields listed in the SELECT clause. The WHERE clause specifies field criteria that must be met by each record to be included in the results. Other common SQL clauses include ORDER BY, GROUP BY and HAVING clauses. The names of these clauses are self explanatory. Each SQL clause is composed of terms. Each term can be an identifier, an operator, a constant or an expression. A simple SQL select statement contains the basic clauses may look like this: SELECT last_name FROM Contacts WHERE First_Name = "Bob"; In this example, the SELECT clause consists of an operator (SELECT) followed by an identifier (last_name). The while clause consists an operator (WHERE) followed by an expression (First_Name = "Bob"). We can see that SQL is very close to human language and not difficult to read and understand. Also, we can see SQL is very different from other procedural languages such as Java and C. By meaning of procedural language, the programmer writes step-by-step instructions telling the computer exactly what to do to achieve a specified goal. If the above example was written in C, the goal might be find someone whose first name is Bob based on the last name from the contacts table. The procedure might be roughly like this: 1. Load the contacts table into memory 2. Extract the individual last name from the data table 3. Check to see if the last name is Bob 4. If it is, make of a note of the data 5. Else, go to next last name of the table and keep going until all last names have been checked. SQL, however, is a declarative language, which means that instead of telling the computer what to do to get the results we want, we simply tell it what we want, and it figures out what to do and comes back with the results. II. SQL Data Types and Variables

SQL has 3 main data types: char (string), number, and Date/Time. For the standard SQL, a char
type is a character string of fixed length, it can hold 1 to 215 - 1 characters. A character varying type is a string of variable length. The integer, float, double types are the same as they are in C. In addition to the standard Date and Date/Time type, SQL has several interval data types: Interval day, Interval hour, Interval Day To Hour, etc [2]. These types are useful for recording when a database entry was created or modified, for logging when an event occurred, or for determining how much time has elapsed since another datetime value was created. All the SQL extensions such as SQL PL and PL/SQL support variables and procedures. A SQL variable is an object that can hold a data value. After the variable has been declared or defined, a SQL statement can set the variable to a value and a later statement can get the value from the variable. In SQL, variables are declared using the 'declare' keyword and initialized using the 'set' keyword. As with any other programming language, the use of variable helps keeping the code short, clean and concise. Consider the following [3] : FROM Transactions WHERE TranDate >= {some long, complicated expression to determine the start date} AND TranDate < {some long, complicated expression to determine the end date} We can potentially use variables to make these formulas much simpler. For example, if the end date is always one day later than the start date, we can write: SET start = {some long, complicated expression to determine the start date} SET end = dateadd(day, 1, start) SELECT * from Transactions WHERE TranDate >= start and TranDate < end That is much easier to read, write and debug then if we repeated the long complicated expression twice, and it makes it very clear what the relationship is between the start date and the ending date. If we need to tweak our starting date formula, we can do it in one place and again it is easier to debug. We also have the ability to name our variables intelligently to help with the readability of our code. III. Transact-SQL Expression
A SQL expression is a combination of symbols and operators that the SQL Server database engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression. The syntax for a Transact-SQL expression like this [4]: { constant | scalar_function | [ table_name. ] column | variable | ( expression ) | ( scalar_subquery ) | { unary_operator } expression | expression { binary_operator } expression | ranking_windowed_function | aggregate_windowed_function When we combine two expressions by using comparison or logical operators, the resulting data type is Boolean and the value is true, false, or unknown. When two expressions are combined by using arithmetic, bit-wise, or string operators, the operator determines the resulting data type. Complex expressions made up of many symbols and operators evaluate to a single-valued result. The data type, collation, precision, and value of the resulting expression is determined by combining the component expressions, two at a time, until a final result is reached. The sequence in which the expressions are combined is defined by the precedence of the operators in the expression. Two expressions can be combined by an operator if they both have data types supported by the operator and at least one of these conditions is true: the expressions have the same data type or the data type which has lower precedence can be implicitly converted to the data type with the higher data type precedence. If the expressions do not meet these conditions, the cast or convert functions can be used to explicitly convert the data type with the lower precedence to either the data type with the higher precedence or to an intermediate data type that can be implicitly converted to the data type with the higher precedence. If there is no supported implicit or explicit conversion, the two expressions cannot be combined [4]. The collation of any expression that evaluates to a character string is set by following the rules of collation precedence. In a programming language such as C or Microsoft Visual Basic, an expression always evaluates to a single result. Expressions in a Transact-SQL select list follow a variation on this rule: the expression is evaluated individually for each row in the result set. A single expression may have a different value in each row of the result set, but each row has only SQL procedures and flow-control

SQL procedures can contain parameters, variables, assignment-statements, control statements, and compound statements. Compound statements introduce a block of SQL statements that are compiled and executed as single statement in database server. Most SQL procedures also support a powerful condition and error handling mechanism, nested and recursive calls, the returning of multiple result sets to the caller or the client application. PL/SQL procedures, functions, and methods each have a respective CREATE statement. In each CREATE statement, you must specify the routine name, and parameters if there are to be any as well as the return type. Here is an examples of PL/SQL function using CREATE statement [5]: CREATE FUNCTION GetPrice (Vendor CHAR(20), PId INT) RETURNS DECIMAL(10,3) LANGUAGE SQL BEGIN DECLARE price DECIMAL(10,3); IF Vendor = 'Vendor 1' THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); ELSE IF Vendor = 'Vendor 2' THEN SET price = (SELECT Price FROM V2Table WHERE Pid = GetPrice.Pid); The most common flow-control programming constructs in SQL are: if-else, while, begin-end. An if-else statement from PL/SQL looks like this: IF condition THEN sequence of statement 1 ELSE sequence of statement 2 END IF; A while statement from Transact-SQL has the basic syntax: WHILE <Boolean expression > begin <code block > end One can see, the flow-control statements used in SQL are very similar to the flow-control statements in other modern programming languages. Conclusion
SQL is a special purposed programming language that used to manipulate data in a certain database. Its syntax is very close to human language and it is not difficult to read or understand. The traditional SQL is a declarative programming language which is very different from other procedural language such as Java and C. There are many version of SQL extensions today, the most popular ones are PL/SQL and Transact-SQL. To complaint with the ANSI standard, these extensions all support the major SQL commands such as SELECT, UPDATE, DELETE in a similar manner. However, these SQL extensions differ from traditional SQL by supporting loops, functions, and procedures. This added functionality is very similar to other high level languages such as Java and C. Bibliography
[1] "SQL Database Reference Material." November 14, 2011. Retrieved from [2] "SQL: Data Types." November 17, 2011. Retrieved from http://www.techonthenet.com/sql/datatypes.php [3] "Using Variables and Parameter." November 17, 2011. Retrieved from [4] Itzik Ben-Gen. Microsoft SQL Server 2008. October 22, 2008. [5] Steven Feuerstain, Bill Priby. Oracle PL/SQL Programming, 5th Edition. September 2009.

Source: http://onyx.boisestate.edu/~tcole/cs354/fall11/languages/jzeng/SQL.pdf