SQL
 Appeared in:
 1974
 Influenced:
 Paradigm:
 Typing discipline:
 File extensions:
 .sql
 Versions and implementations (Collapse all  Expand all):
SQL (Structured Query Language) is a database management language for relational databases. SQL itself is not a programming language, but its standard allows creating procedural extensions for it, which extend it to functionality of a mature programming language.
The language was developed in 1970th under the name “SEQUEL” for database management system (DBMS) called System R. It was renamed to “SQL” later to avoid trademarks conflict.
SQL was first released for commercial purposes in 1979, implemented in Oracle V2.
The first official standard of the language was adopted by ANSI in 1986 and by ISO in 1987. Since then several more versions were created, some of them being only revisions of previous ones, while the others introduced new major features.
Despite of existing standards, most popular implementations of SQL differ so much that SQL code can be hardly ported from one DBMS to another one without significant modifications. This can be explained by oversize and complexity of the standard, as well as by its lack of specifications in several important areas of implementation.
SQL was created as a simple standartized way to query and manage data contained in a relational database. Nowadays it has grown more complicated than it was intended to, becoming a tool of developer, not of end user.
Currently SQL (mostly in Oracle implementation) remains the most popular of database management languages, though there exists a number of alternatives. Naturally, it is used only for databaserelated development,
SQL itself consists of four principal sublanguages:
 DDL (Data Definition Language) is used to define data structures stored in the database. DDL statements allow to create, modify or destroy individual database objects. Object types allowed depend on the DBMS used, and usually include databases, users, tables and some smaller housekeeping objects like roles and indexes.
 DML (Data Manipulation Language) is used to query and change data stored in the database. DML statements allow to select, insert, update and delete data in the tables. Sometimes pure select statements are not classified as part of DML, since they don’t change the state of the data. All SQL DML statements are declarative.
 DCL (Data Control Language) is used to control access to data stored in the database. DCL statements operate with privileges and allow to grant and revoke privileges on applying certain DDL and DML commands to certain database objects.

TCL (Transaction Control Language) is used to control processing of transactions
in the database. Usual TCL statements are
commit
to apply the changes introduced by the transaction,rollback
to undo them andsavepoint
to divide the transaction into several smaller parts.
Note that SQL implements declarative programming paradigm: each statement describes only the required action, and it’s up to DBMS to decide how to implement it, i.e., to plan the elementary operations necessary to perform the action and to execute them. However, it is important for the developer to understand the way DBMS analyzes each statement and creates its execution plan to be able to use SQL efficiently.
Elements of syntax:
Inline comments   

Nonnestable comments  /* ... */ 
Casesensitivity  no 
Variable assignment  varname := value 
Variable declaration  varname type 
Variable declaration with assignment  varname type := value 
Grouping expressions  ( ... ) 
Block  begin ... end 
Physical (shallow) equality  = 
Physical (shallow) inequality  != 
Comparison  < > <= >= 
Sequence  ; 
If  then  IF condition THEN trueBlock END IF 
If  then  else  IF condition THEN trueBlock ELSE falseBlock END IF 
Examples:
Hello, World!:
Example for versions Oracle 10g SQL, Oracle 11g SQL‘Hello, World!’ string is selected from builtin table dual
which is used for queries which don’t need data from real tables.
select 'Hello, World!'
from dual;
Factorial:
Example for versions Oracle 10g SQL, Oracle 11g SQLPure SQL doesn’t support loops, recursions or userdefined functions. This example shows one possible workaround which uses

pseudocolumn
level
to construct a pseudotable containing numbers 1 through 16, 
aggregate function
sum
which allows to sum the elements of a set 
and math functions
exp
andln
to replace multiplication (required to calculate the factorial) with addition (provided by SQL).
Note that 0! will not be present in the return, since trying to calculate ln(0) produces an exception.
select t2.n  '! = '  round(exp(sum(ln(t1.n))))
from
( select level n
from dual
connect by level <= 16) t1,
( select level n
from dual
connect by level <= 16) t2
where t1.n<=t2.n
group by t2.n
order by t2.n
Fibonacci numbers:
Example for versions Oracle 10g SQL, Oracle 11g SQLPure SQL doesn’t support loops, recursions or userdefined functions. Besides, concatenating fields from multiple rows of a table or a subquery is not a standard aggregate function. This example uses:

Binet’s formula and math functions
round
,power
andsqrt
to calculate nth Fibonacci number;  pseudocolumn level to construct a pseudotable t1 containing numbers 1 through 16;

builtin function
SYS_CONNECT_BY_PATH
to concatenate the resulting numbers in ascending order.
SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(fib', ', '/')),'/','')'...' fiblist
FROM (
SELECT n, fib, ROW_NUMBER()
OVER (ORDER BY n) r
FROM (select n, round((power((1+sqrt(5))*0.5, n)power((1sqrt(5))*0.5, n))/sqrt(5)) fib
from (select level n
from dual
connect by level <= 16) t1) t2
)
START WITH r=1
CONNECT BY PRIOR r = r1;
Factorial:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example shows the use of model
clause, available since Oracle 10g. It allows arraylike processing of query rows. Each row has two columns — iteration number (stored in n
) and its factorial (stored in f
).
select n  '! = '  f factorial
from dual
model
return all rows
dimension by ( 0 d )
measures ( 0 f, 1 n )
rules iterate (17)
( f[iteration_number] = decode(iteration_number, 0, 1, f[iteration_number1]*iteration_number),
n[iteration_number] = iteration_number
);
Fibonacci numbers:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example shows the use of model
clause, available since Oracle 10g. It allows arraylike processing of query rows. Each row has two columns — Fibonacci number itself (stored in f) and concatenation of all Fibonacci numbers less than or equal to the current Fibonacci number (stored in s). Iterative aggregation of Fibonacci numbers in the same query that they were generated is easier than aggregating them separately.
select max(s)  ', ...'
from
(select s
from dual
model
return all rows
dimension by ( 0 d )
measures ( cast(' ' as varchar2(200)) s, 0 f)
rules iterate (16)
( f[iteration_number] = decode(iteration_number, 0, 1, 1, 1, f[iteration_number1] + f[iteration_number2]),
s[iteration_number] = decode(iteration_number, 0, to_char(f[iteration_number]), s[iteration_number1]  ', '  to_char(f[iteration_number]))
)
);
Hello, World!:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example accomplishes the task by means of anonymous PL/SQL block, which uses standard package dbms_output
to print the message to standard output.
begin
dbms_output.put_line('Hello, World!');
end;
Factorial:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example calculates factorials iteratively by means of PL/SQL.
declare
n number := 0;
f number := 1;
begin
while (n<=16)
loop
dbms_output.put_line(n  '! = '  f);
n := n+1;
f := f*n;
end loop;
end;
Fibonacci numbers:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example implements iterative definition of Fibonacci numbers by means of PL/SQL. Already calculated numbers are stored in varray
, PL/SQL analogue of array in other languages.
declare
type vector is varray(16) of number;
fib vector := vector();
i number;
s varchar2(100);
begin
fib.extend(16);
fib(1) := 1;
fib(2) := 1;
s := fib(1)  ', '  fib(2)  ', ';
for i in 3..16 loop
fib(i) := fib(i1) + fib(i2);
s := s  fib(i)  ', ';
end loop;
dbms_output.put_line(s  '...');
end;
Quadratic equation:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example was tested in SQL*Plus and TOAD.
Pure SQL allows to input values at runtime in the form of substitution variables. To define a substitution variable, use its name (in this case A, B and C) with an ampersand &
before it each time you need to reference it. When the query needs to be executed, you will receive a prompt to enter the values of the variables. Each reference to such variable will be replaced with its value, and the resulting query will be executed.
There are several ways to input the values for substitution variables. In this example first reference to each variable has double ampersand &&
before its name. This way the value of the variable has to be entered only once, and all following references will be replaced with the same value (using single ampersand in SQL*Plus asks to enter the value for each reference to the same variable). PL/SQL Developer requires that all variables have single &
before their names, otherwise ORA01008 “Not all variables bound” exception will be raised.
Note that the references are substituted with the values “as is”, so negative values of coefficients have to be entered in brackets.
First line of the example sets the character for decimal separator which is used when the numbers are converted into strings.
The query itself is composed of four different queries. Each query returns a string containing the result of calculations for one case of quadratic equation, and returns nothing for three other cases. The results of each query are united to produce the final result.
alter session set NLS_NUMERIC_CHARACTERS='. ';
select 'Not a quadratic equation.' ans
from dual
where &&A = 0
union
select 'x = '  to_char(&&B/2/&A)
from dual
where &A != 0 and &B*&B4*&A*&&C = 0
union
select 'x1 = '  to_char((&B+sqrt(&B*&B4*&A*&C))/2/&A)  ', x2 = '  to_char(&Bsqrt(&B*&B4*&A*&C))/2/&A
from dual
where &A != 0 and &B*&B4*&A*&C > 0
union
select 'x1 = ('  to_char(&B/2/&A)  ','  to_char(sqrt(&B*&B+4*&A*&C)/2/&A)  '), ' 
'x2 = ('  to_char(&B/2/&A)  ','  to_char(sqrt(&B*&B+4*&A*&C)/2/&A)  ')'
from dual
where &A != 0 and &B*&B4*&A*&C < 0;
CamelCase:
Example for versions Oracle 10g SQL, Oracle 11g SQLThis example uses Oracle SQL regular expressions. First use of regexp_replace
replaces all digits with spaces — this is necessary for further initcap
usage (this function treats digits as parts of the words and doesn’t capitalize following letters). Next, initcap
converts all words to lowercase with first letter capitalized. Finally, second use of regexp_replace
removes all punctuation and spaces from the string.
&TEXT is substitution variable, which allows to input different strings without rewriting the select statement.
select regexp_replace(initcap(regexp_replace('&TEXT', '[[:digit:]]', ' ')), '([[:punct:]  [:blank:]])', '')
from dual
CamelCase:
Example for versions Oracle 11g SQLThis example uses Oracle regular expressions combined with PL/SQL. regex_substr
returns a substring of text
that is occurrence
‘s match to the given regular expression.
declare
text varchar2(100) := '&user_input';
word varchar2(100);
camelcase varchar2(100);
occurrence number := 1;
begin
loop
word := regexp_substr(text, '[[:alpha:]]+', 1, occurrence);
exit when word is null;
camelcase := camelcase  initcap(word);
occurrence := occurrence + 1;
end loop;
dbms_output.put_line(camelcase);
end;
Quadratic equation:
Example for versions Oracle 11g SQLdeclare
A number := '&A';
B number := '&B';
C number := '&C';
D number := B * B  4 * A * C;
begin
if A = 0 then
dbms_output.put_line('Not a quadratic equation.');
return;
end if;
if D = 0 then
dbms_output.put_line('x = '  to_char(B/2/A));
elsif D > 0 then
dbms_output.put_line('x1 = '  to_char((Bsqrt(D))/2/A));
dbms_output.put_line('x2 = '  to_char((B+sqrt(D))/2/A));
else
dbms_output.put_line('x1 = ('  to_char(B/2/A)  ', '  to_char(sqrt(D)/2/A)  ')');
dbms_output.put_line('x2 = ('  to_char(B/2/A)  ', '  to_char(sqrt(D)/2/A)  ')');
end if;
end;
Hello, World!:
Example for versions Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012, MySQL 3.23.57, PostgreSQL 9.1select 'Hello, World!';
Fibonacci numbers:
Example for versions Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012This example uses a kind of iterative definition of Fibonacci numbers, implemented with a recursive query. Each row of recursive query contains two consecutive numbers of the sequence, and next row is calculated as (last number, sum of numbers) of previous row. This way most numbers are stored twice, so only first number of each row is included in the result.
with fibonacci(a, b) as
(
select 1, 1
union all
select b, a+b from fibonacci where b < 1000
)
SELECT cast(a as varchar)+', ' AS [text()]
FROM fibonacci
FOR XML PATH ('')
Factorial:
Example for versions Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012This example uses recursive factorial definition, implemented as a recursive query — a feature included in SQL Server 2005 and higher. Each row of recursive query contains two numeric fields — n and f = n!, and each row is calculated using the data of the previous row.
Note that starting with 2008 version you can calculate factorials only up to 12!. Trying to calculate 13! results in “Data truncation” error, i.e. the resulting value is too large for its datatype.
with factorial(n, f) as
(
select 0, 1
union all
select n+1, f*(n+1) from factorial where n<16
)
select cast(n as varchar)+'! = '+cast(f as varchar)
from factorial
Quadratic equation:
Example for versions Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012SELECT dbo.quadratic_equation(1,2,1)
SELECT dbo.quadratic_equation(1,2,3)
CREATE FUNCTION quadratic_equation (@a FLOAT
,@b FLOAT
,@c float )
RETURNS varchar (100)
AS
BEGIN
DECLARE @ret varchar(100);
declare @delta float ;
declare @denom float ;
select @delta=(POWER(@b,2)4*@a*@c);
select @denom=(2*@a);
if (@delta>=0)
BEGIN
DECLARE @x12 FLOAT;
SELECT @x12=((@b+SQRT(@delta))/@denom);
SELECT @ret='x1='+ CONVERT(varchar(100),@x12 )
+' x2='+ CONVERT(varchar(100),@x12 )
END
ELSE
BEGIN
DECLARE @nat FLOAT;
DECLARE @imm FLOAT;
SELECT @nat=((@b)/@denom);
SELECT @imm=((SQRT(@delta))/@denom);
SELECT @ret='x1='+ CONVERT(varchar(100),@nat)+'+i'+CONVERT(varchar(100),@imm )
+' x2='+ CONVERT(varchar(100),@nat)+'i'+CONVERT(varchar(100),@imm )
end
RETURN @ret ;
END;
Factorial:
Example for versions MySQL 3.23.57Replace TABLE
with name of any table you have access to, like mysql.help_topic
.
select concat(cast(t2.n as char), "! = ", cast(exp(sum(log(t1.n))) as char))
from
( select @i := @i+1 AS n
from TABLE, (select @i := 0) as sel1
limit 16 ) t1,
( select @j := @j+1 AS n
from TABLE, (select @j := 0) as sel1
limit 16 ) t2
where t1.n <= t2.n
group by t2.n
Fibonacci numbers:
Example for versions MySQL 3.23.57Replace TABLE
with name of any table you have access to, like mysql.help_topic
.
select concat(group_concat(f separator ', '), ', ...')
from (select @f := @i + @j as f, @i := @j, @j := @f
from TABLE, (select @i := 1, @j := 0) sel1
limit 16) t
Factorial:
Example for versions PostgreSQL 9.1The task of factorials calculation is solved using standard methods only: a builtin factorial function !
(postfix form; there is also a prefix form !!
) and a set function generate_series
which generates a set of rows containing values between the first and the second parameters.
select n  '! = '  (n!)
from generate_series(0,16) as seq(n);
Fibonacci numbers:
Example for versions PostgreSQL 9.1WITH RECURSIVE t(a,b) AS (
VALUES(1,1)
UNION ALL
SELECT b, a + b FROM t
WHERE b < 1000
)
SELECT array_to_string(array(SELECT a FROM t), ', ')  ', ...';
Comments
]]>blog comments powered by Disqus
]]>