SQL (officially pronounced /skjul/ like &quot;S-Q-L&quot; but is often pronounced /sikwl/ like &quot;Sequel&quot;), referred to as Structured Query Language,

[2] [3]

often

is a database computer language designed for managing data in

relational database management systems (RDBMS), and originally based upon relational algebra. Its scope includes data query and update, schema creation and modification, and data access control. SQL was one of the first languages for Edgar F. Codd's relational model in his influential 1970 paper, &quot;A Relational Model of Data for Large Shared Data Banks&quot;

[4]

and became the most widely used language for relational databases.

[2] [5]

ANSI SQL is a Turing complete programming language.

History

SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's original relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s.

[6]

The acronym SEQUEL was later changed to SQL because &quot;SEQUEL&quot; was a trademark of the UK-based

[7]

Hawker Siddeley aircraft company.

The first Relational Database Management System (RDBMS) was RDMS, developed at MIT in the early 1970s, soon followed by Ingres, developed in 1974 at U.C. Berkeley. Ingres implemented a query language known as QUEL, which was later supplanted in the marketplace by SQL.

[7]

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In the summer of 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. Oracle V2 beat IBM's release of the System/38 RDBMS to market by a few weeks. After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.

[8]

Common criticisms of SQL include a lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and occasionally ambiguous language grammar and semantics . It also lacks the rigour of more formal languages such as relational algebra .

SQL

2

Language elements

The SQL language is sub-divided into several language elements, including: · Clauses, which are in some cases optional, constituent components of statements and queries.

[9] This chart shows several of the SQL language elements that compose a single statement.

· Expressions which can produce either scalar values or tables consisting of columns and rows of data.

· Predicates which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow. · Queries which retrieve data based on specific criteria. · Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics. · SQL statements also include the semicolon (&quot;;&quot;) statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar. · Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

Queries

The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effects on the database. Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax that exists in some databases.

[10]

Queries allow the user to describe desired data, leaving the database management system (DBMS) responsible for planning, optimizing, and performing the physical operations necessary to produce that result as it chooses. A query includes a list of columns to be included in the final result immediately following the SELECT keyword. An asterisk (&quot;*&quot;) can also be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include: · The FROM clause which indicates the table(s) from which data is to be retrieved. The FROM clause can include optional JOIN subclauses to specify the rules for joining tables. · The WHERE clause includes a comparison predicate, which restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set for which the comparison predicate does not evaluate to True. · The GROUP BY clause is used to project rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause. · The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate. · The ORDER BY clause identifies which columns are used to sort the resulting data, and in which direction they should be sorted (options are ascending or descending). Without an ORDER BY clause, the order of rows returned by an SQL query is undefined. The following is an example of a SELECT query that returns a list of expensive books. The query retrieves all rows from the Book table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title. The asterisk (*) in the select list indicates that all columns of the Book table should be included in the

SQL result set. SELECT * FROM Book WHERE price &gt; 100.00 ORDER BY title; The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book. SELECT Book.title, count(*) AS Authors FROM Book JOIN Book_author ON Book.isbn = Book_author.isbn GROUP BY Book.title; Example output might resemble the following: Title ---------------------SQL Examples and Guide The Joy of SQL An Introduction to SQL Pitfalls of SQL Authors ------4 1 2 1

3

Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Books table, the above query could be rewritten in the following form: SELECT title, count(*) AS Authors FROM Book NATURAL JOIN Book_author GROUP BY title; However, many vendors either do not support this approach, or require certain column naming conventions in order for natural joins to work effectively. SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price. SELECT isbn, title, price, price * 0.06 AS sales_tax FROM Book WHERE price &gt; 100.00 ORDER BY title;

SQL Null and three-valued logic (3VL) The idea of Null was introduced into SQL to handle missing information in the relational model. The introduction of Null (or Unknown) along with True and False is the foundation of three-valued logic. Null does not have a value (and is not a member of any data domain) but is rather a placeholder or &quot;mark&quot; for missing information. Therefore comparisons with Null can never result in either True or False but always in the third logical result, Unknown. three-valued logic (3VL) are shown below (p and q represent logical states). keyword in SQL, used to identify the Null special marker. Additionally, since SQL operators return Unknown when comparing anything with Null, SQL provides two Null-specific comparison predicates: The IS NULL and IS NOT NULL test whether data is or is not Null. values of False, but also those whose value is Unknown.

[13] [12] [11]

4

SQL uses Null to handle missing information. It supports three-valued logic (3VL) and the rules governing SQL The word NULL is also a reserved

Note that SQL returns only results for which the WHERE clause returns a value of True. I.e., it excludes results with

p AND q True q True False True False

p False Unknown False Unknown False False q

p OR q True True False

p False Unknown True Unknown

p True False

NOT p False True q

p= q True True False True False

p False False True Unknown Unknown Unknown

True True True False

Unknown Unknown

Unknown Unknown False Unknown

Unknown True Unknown Unknown

Unknown Unknown Unknown Unknown

Universal quantification is not explicitly supported by SQL, and must be worked out as a negated existential [14] [15] [16] quantification. There is also the &quot;&lt;row value expression&gt; IS DISTINCT FROM &lt;row value expression&gt;&quot; infixed comparison operator which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as &quot;NOT (&lt;row value expression&gt; IS DISTINCT FROM &lt;row value expression&gt;&quot;)

SQL · MERGE is used to combine the data of multiple tables. It combines the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called &quot;upsert&quot;.

5

Transaction controls

Transactions, if available, wrap DML operations: · START TRANSACTION (or BEGIN WORK, or BEGIN TRANSACTION, depending on SQL dialect) mark the start of a database transaction, which either completes entirely or not at all. · SAVE TRANSACTION (or SAVEPOINT ) save the state of the database at the current point in transaction CREATE TABLE tbl_1(id int); INSERT into tbl_1(id) value(1); INSERT into tbl_1(id) value(2); COMMIT; UPDATE tbl_1 SET id=200 WHERE id=1; SAVEPOINT id-1upd; UPDATE tbl_1 SET id=1000 WHERE id=2; ROLLBACK to id-1upd; SELECT id from tbl_1; · COMMIT causes all data changes in a transaction to be made permanent. · ROLLBACK causes all data changes since the last COMMIT or ROLLBACK to be discarded, leaving the state of the data as it was prior to those changes. Once the COMMIT statement completes, the transaction's changes cannot be rolled back. COMMIT and ROLLBACK terminate the current transaction and release data locks. In the absence of a START TRANSACTION or similar statement, the semantics of SQL are implementation-dependent. Example: A classic bank transfer of funds transaction. START TRANSACTION; UPDATE Account SET amount=amount-200 WHERE account_number=1234; UPDATE Account SET amount=amount+200 WHERE account_number=2345; IF ERRORS=0 COMMIT; IF ERRORS&lt;&gt;0 ROLLBACK;

Data definition

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements: · CREATE creates an object (a table, for example) in the database. · DROP deletes an object in the database, usually irretrievably. · ALTER modifies the structure of an existing object in various ways--for example, adding a column to an existing table. Example: CREATE TABLE My_table ( my_field1 my_field2 my_field3 INT, VARCHAR(50), DATE NOT NULL,

SQL PRIMARY KEY (my_field1, my_field2) );

6

Data types

Each column in an SQL table declares the type(s) that column may contain. ANSI SQL includes the following datatypes.

[17]

Character strings · CHARACTER(n) or CHAR(n) -- fixed-width n-character string, padded with spaces as needed · CHARACTER VARYING(n) or VARCHAR(n) -- variable-width string with a maximum size of n characters · NATIONAL CHARACTER(n) or NCHAR(n) -- fixed width string supporting an international character set · NATIONAL CHARACTER VARYING(n) or NVARCHAR(n) -- variable-width NCHAR string Bit strings · BIT(n) -- an array of n bits · BIT VARYING(n) -- an array of up to n bits Numbers · INTEGER and SMALLINT · FLOAT, REAL and DOUBLE PRECISION · NUMERIC(precision, scale) or DECIMAL(precision, scale) SQL provides a function to round numerics or dates, called TRUNC (in Informix, DB2, PostgreSQL, Oracle and [18] MySQL) or ROUND (in Informix, Sybase, Oracle, PostgreSQL and Microsoft SQL Server) Date and time · DATE -- for date values (e.g., 2010-05-30) · TIME -- for time values (e.g., 14:55:37). The granularity of the time value is usually a tick (100 nanoseconds). · TIME WITH TIME ZONE or TIMESTAMP -- the same as TIME, but including details about the time zone in question. · TIMESTAMP -- This is a DATE and a TIME put together in one variable (e.g., 2010-05-30 14:55:37). · TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ -- the same as TIMESTAMP, but including details about the time zone in question. SQL provides several functions for generating a date / time variable out of a date / time string (TO_DATE, TO_TIME, TO_TIMESTAMP), as well as for extracting the respective members (seconds, for instance) of such variables. The current system date / time of the database server can be called by using functions like NOW.

Data control

The Data Control Language (DCL) authorizes users and groups of users to access and manipulate data. Its two main statements are: · GRANT authorizes one or more users to perform an operation or a set of operations on an object. · REVOKE eliminates a grant, which may be the default grant. Example: GRANT SELECT, UPDATE ON My_table TO some_user, another_user;

SQL REVOKE SELECT, UPDATE ON My_table FROM some_user, another_user;

7

Procedural extensions

SQL is designed for a specific purpose: to query data contained in a relational database. SQL is a set-based, declarative query language, not an imperative language such as C or BASIC. However, there are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. These are:

In addition to the standard SQL/PSM extensions and proprietary SQL extensions, procedural and object-oriented programmability is available on many SQL platforms via DBMS integration with other languages. The SQL standard defines SQL/JRT extensions (SQL Routines and Types for the Java Programming Language) to support Java code in SQL databases. SQL Server 2005 uses the SQLCLR (SQL Server Common Language Runtime) to host managed .NET assemblies in the database, while prior versions of SQL Server were restricted to using unmanaged extended stored procedures which were primarily written in C. Other database platforms, like MySQL and Postgres, allow functions to be written in a wide variety of languages including Perl, Python, Tcl, and C.

Criticisms of SQL

SQL is a declarative computer language for use with relational databases. Interestingly, many of the original SQL features were inspired by, but violated, the semantics of the relational model and its tuple calculus realization. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto. Practical criticisms of SQL include: · Implementations are inconsistent and, usually, incompatible between vendors. In particular date and time syntax, string concatenation, nulls, and comparison case sensitivity vary from vendor to vendor. · The language makes it too easy to do a Cartesian join (joining all possible combinations), which results in &quot;runaway&quot; result sets when WHERE clauses are mistyped. Cartesian joins are so rarely used in practice that requiring an explicit CARTESIAN keyword may be warranted. (SQL 1992 introduced the CROSS JOIN keyword that allows the user to make clear that a Cartesian join is intended, but the shorthand &quot;comma-join&quot; with no predicate is still acceptable syntax, which still invites the same mistake.)

SQL · It is also possible to misconstruct a WHERE on an update or delete, thereby affecting more rows in a table than desired. (A work-around is to use transactions or habitually type in the WHERE clause first, then fill in the rest later.) · The grammar of SQL is perhaps unnecessarily complex, borrowing a COBOL-like keyword approach, when a function-influenced syntax could result in more re-use of fewer grammar and syntax rules.

8

Cross-vendor portability

Popular implementations of SQL commonly omit support for basic features of Standard SQL, such as the DATE or TIME data types. As a result, SQL code can rarely be ported between database systems without modifications. There are several reasons for this lack of portability between database systems: · The complexity and size of the SQL standard means that most implementors do not support the entire standard. · The standard does not specify database behavior in several important areas (e.g., indexes, file storage...), leaving implementations to decide how to behave. · The SQL standard precisely specifies the syntax that a conforming database system must implement. However, the standard's specification of the semantics of language constructs is less well-defined, leading to ambiguity. · Many database vendors have large existing customer bases; where the SQL standard conflicts with the prior behavior of the vendor's database, the vendor may be unwilling to break backward compatibility. · Software vendors often desire to create incompatibilities with other products, as it provides a strong incentive for their existing users to remain loyal (see vendor lock-in).

Standardization

SQL was adopted as a standard by the American National Standards Institute (ANSI) in 1986 as SQL-86 pronunciation for SQL is &quot;es queue el&quot;.

[2] [19]

and

[20]

International Organization for Standardization (ISO) in 1987. The original SQL standard declared that the official Many English-speaking database professionals still use the nonstandard pronunciation /sikwl/ (like the word &quot;sequel&quot;). Until 1996, the National Institute of Standards and Technology (NIST) data management standards program certified SQL DBMS compliance with the SQL standard. Vendors now self-certify the compliance of their products. The SQL standard has gone through a number of revisions, as shown below:

Year Name Alias SQL-87 FIPS 127-1 SQL2, FIPS 127-2 1999 SQL:1999 SQL3 Added regular expression matching, recursive queries, triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features. Introduced XML-related features, window functions, standardized sequences, and columns with auto-generated values (including identity-columns). ISO/IEC 9075-14:2006 defines ways in which SQL can be used in conjunction with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the database and publishing both XML and conventional SQL-data in XML form. In addition, it enables applications to integrate into their SQL code the use of XQuery, the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents. First formalized by ANSI. Minor revision, adopted as FIPS 127-1. Comments [21]

Interested parties may purchase SQL standards documents from ISO or ANSI. A draft of SQL:2008 is freely [23] available as a zip archive.

Standard structure

The SQL standard is divided into several parts, including: SQL Framework, provides logical concept SQL/Foundation, defined in ISO/IEC 9075, Part 2. This part of the standard contains the most central elements of the language. It consists of both mandatory and optional features. The SQL/Bindings, specifies how SQL is to be bound to variable host languages,excluding Java. The SQL/CLI, or Call-Level Interface, part is defined in ISO/IEC 9075, Part 3. SQL/CLI defines common interfacing components (structures and procedures) that can be used to execute SQL statements from applications written in other programming languages. SQL/CLI is defined in such a way that SQL statements and SQL/CLI procedure calls are treated as separate from the calling application's source code. Open Database Connectivity is a well-known superset of SQL/CLI. This part of the standard consists solely of mandatory features. The SQL/PSM, or Persistent Stored Modules, part is defined by ISO/IEC 9075, Part 4. SQL/PSM standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., &quot;stored procedures&quot;). This part of the standard consists solely of optional features. The SQL/MED, or Management of External Data, part is defined by ISO/IEC 9075, Part 9. SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This part of the standard consists solely of optional features. The SQL/OLB, or Object Language Bindings, part is defined by ISO/IEC 9075, Part 10. SQL/OLB defines the syntax and symantics of SQLJ, which is SQL embedded in Java. The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes. This part of the standard consists solely of optional features. The SQL/MM (Multimedia), This extends SQL to deal intelligently with large,complex and sometimes streaming items of data, such as video,audio and spatial data. The SQL/Schemata, or Information and Definition Schemas, part is defined by ISO/IEC 9075, Part 11. SQL/Schemata defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier, structure and integrity constraints, security and authorization specifications, features and packages of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items, and the values supported by the DBMS implementations. optional features. The SQL/JRT, or SQL Routines and Types for the Java Programming Language, part is defined by ISO/IEC 9075, Part 13. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL structured user-defined types. This part of the standard consists solely of optional features. The SQL/XML, or XML-Related Specifications, part is defined by ISO/IEC 9075, Part 14. SQL/XML specifies SQL-based extensions for using XML in conjunction with SQL. The XML data type is introduced, as well as several

[24]

This part of the standard contains both mandatory and

SQL routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in an SQL database. This part of the standard consists solely of optional features.

· 1995 SQL Reunion: People, Projects, and Politics, by Paul McJones (ed.) (http://www.mcjones.org/System_R/ SQL_Reunion_95/sqlr95.html): transcript of a reunion meeting devoted to the personal history of relational databases and SQL. · American National Standards Institute. X3H2 Records, 1978-1995 (http://special.lib.umn.edu/findaid/xml/ cbi00168.xml) Charles Babbage Institute Collection documents the H2 committee's development of the NDL and SQL standards. · Oral history interview with Donald D. Chamberlin (http://www.cbi.umn.edu/oh/display.phtml?id=317) Charles Babbage Institute In this oral history Chamberlin recounts his early life, his education at Harvey Mudd College and Stanford University, and his work on relational database technology. Chamberlin was a member of the System R research team and, with Raymond F. Boyce, developed the SQL database language. Chamberlin also briefly discusses his more recent research on XML query languages. · Comparison of Different SQL Implementations (http://troels.arvin.dk/db/rdbms/) This comparison of various SQL implementations is intended to serve as a guide to those interested in porting SQL code between various RDBMS products, and includes comparisons between SQL:2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle, and Informix.

SQL:2003

12

SQL:2003

SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.

Summary

The SQL:2003 standard makes minor modifications to all parts of SQL:1999 (also known as SQL3), and officially introduces a few new features such as: · XML-related features (SQL/XML) · Window functions · the sequence generator, which allows standardized sequences · two new column types: auto-generated values and identity-columns · the new MERGE statement · extensions to the CREATE TABLE statement, to allow &quot;CREATE TABLE AS&quot; and &quot;CREATE TABLE LIKE&quot; · removal of the poorly-implemented &quot;BIT&quot; and &quot;BIT VARYING&quot; data types

[1]

Documentation availability

The SQL standard is not freely available. SQL:2003 may be purchased from ISO available as a zip archive

Developer(s) Initial release Written in Operating system Available in Type Website C, C++ Cross-platform Sybase

English RDBMS advantagedatabase.com

[1]

Advantage Database Server is a relational database management system (RDBMS) for small to medium sized businesses by Sybase iAnywhere. Database author Cary Jensen describes Advantage as follows: &quot;Advantage has been around since 1993, when it was introduced to provide a stable solution for Clipper developers who were tired of slow performance and corrupt indexes inherent to file server-based databases. Over the years, ADS has grown in both popularity and features. Advantage is now a mature product with an impressive collection of features that rival [2] many of the more expensive and complicated database servers&quot; . &quot;In short, the Advantage Database Server is a high-performance, low-maintenance, remote database server that permits you to easily build and deploy client/server applications and web-based applications&quot;

[3]

.

Uses

Advantage Database Server is most popular among application developers as a client/server backend solution for shared, networked, standalone, mobile and Internet database applications. ADS is unique among other database offerings because it provides both ISAM table-based and SQL based data access.

The Call Level Interface (CLI) is a software standard defined in ISO/IEC 9075-3:2003. The Call Level Interface defines how a program should send SQL queries to the database management system (DBMS) and how the returned recordsets should be handled by the application in a consistent way. Developed in the early 1990s, the API was defined only for the C and COBOL programming languages. The interface is part of what The Open Group refers to as the Common Application Environment, which is intended to be a wide standard for programming open applications, i.e. applications from different programming teams and different vendors that can interoperate in an efficient manner. SQL/CLI provides an international standard implementation-independent CLI to access SQL databases. Client-server tools can easily access databases through Dynamic-link libraries (DLL). It supports and encourages a rich set of client-server tools. The most widespread use of the CLI standard is the basis of the ODBC (Open Database Connectivity) specification, which is widely used to allow applications to transparently access database systems from different vendors. The current version of the API, ODBC 3.52, incorporates features from both the ISO and X/Open standards (see below).

History

The work with the Call Level Interface began in a subcommittee of the US-based SQL Access Group. In 1992, it was initially published and marketed as Microsoft's ODBC API. The CLI specification was submitted as to the ISO and ANSI standards committees in 1993; the standard has the book number ISBN 1-85912-081-4 and the internal document number is C451. ISO SQL/CLI is an addendum to 1992 SQL standard (SQL-92). It was completed as ISO standard ISO/IEC 9075-3:1995 Information technology -- Database languages -- SQL -- Part 3: Call-Level Interface (SQL/CLI). The current SQL/CLI effort is adding support for SQL3. In the fourth quarter of 1994, control over the standard was transferred to the X/Open Company, which significantly expanded and updated it. The X/Open CLI interface is a superset of the ISO SQL CLI. This article was originally based on material from the Free On-line Dictionary of Computing, which is licensed under the GFDL.

External links

· Online definition of CLI

[1]

at The Open Group webpage

References

[1] http://www.opengroup.org/products/publications/catalog/c451.htm

Cardinality (SQL statements)

18

Cardinality (SQL statements)

In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column. Thus, a column with the lowest possible cardinality would have the same value for every row. SQL databases use cardinality to help determine the optimal query plan for a given query.

Values of Cardinality

When dealing with columnar value sets, there are 3 types of cardinality: high-cardinality, normal-cardinality, and low-cardinality. High-cardinality refers to columns with values that are very uncommon or unique. High-cardinality column values are typically identification numbers, email addresses, or user names. An example of a data table column with high-cardinality would be a USERS table with a column named USER_ID. This column would contain unique values of 1-n. Each time a new user is created in the USERS table, a new number would be created in the USER_ID column to identify them uniquely. Since the values held in the USER_ID column are unique, this column's cardinality type would be referred to as high-cardinality. Normal-cardinality refers to columns with values that are somewhat uncommon. Normal-cardinality column values are typically names, street addresses, or vehicle types. An example of a data table column with normal-cardinality would be a CUSTOMER table with a column named LAST_NAME, containing the last names of customers. While some people have common last names, such as Smith, others have uncommon last names. Therefore, an examination of all of the values held in the LAST_NAME column would show &quot;clumps&quot; of names in some places (e.g.: a lot of Smith's ) surrounded on both sides by a long series of unique values. Since there is a variety of possible values held in this column, its cardinality type would be referred to as normal-cardinality. Low-cardinality refers to columns with few unique values. Low-cardinality column values are typically status flags, boolean values, or major classifications such as gender. An example of a data table column with low-cardinality would be a CUSTOMER table with a column named NEW_CUSTOMER. This column would contain only 2 distinct values: Y or N, denoting whether the customer was new or not. Since there are only 2 possible values held in this column, its cardinality type would be referred to as low-cardinality.

See also

· Cardinality (mathematics)

Check Constraint

19

Check Constraint

A check constraint (also known as table check constraint) is a condition that defines valid data when adding or updating an entry in a table of a relational database. A check constraint is applied to each row in the table. The constraint must be a predicate. It can refer to a single or multiple columns of the table. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of NULLs. If the predicate evaluates to UNKNOWN, then the constraint is not violated and the row can be inserted or updated in the table. This is contrary to predicates in WHERE clauses in SELECT or UPDATE statements. For example, in a table containing products, one could add a check constraint such that the price of a product and quantity of a product is a non-negative value: PRICE &gt;= 0 QUANTITY &gt;= 0 If these constraints were not in place, it would be possible to have a negative price (-$30) or quantity (-3 items). Check constraints are used to ensure the validity of data in a database and to provide data integrity. If they are used at the database level, applications that use the database will not be able to add invalid data or modify valid data so the data becomes invalid, even if the application itself accepts invalid data.

A NOT NULL constraint is functionally equivalent to the following check constraint with an IS NOT NULL predicate: CHECK (column IS NOT NULL) Some relational database management systems are able to optimize performance when the NOT NULL constraint [1] syntax is used as opposed to the CHECK constraint syntax given above.

Common Restrictions

Most database management systems restrict check constraints to a single row, with access to constants and deterministic functions, but not to data in other tables, or to data invisible to the current transaction because of transaction isolation. Such constraints are not truly table check constraints but rather row check constraints. Because these constraints are generally only verified when a row is directly updated (for performance reasons,) and often implemented as implied INSERT or UPDATE triggers, integrity constraints could be violated by indirect action were it not for these limitations. Future, otherwise-valid modifications to these records would then be prevented by the CHECK constraint. Some examples of dangerous constraints include: · CHECK ((select count(*) from invoices where invoices.customerId = customerId) &lt; 1000) · CHECK (dateInserted = CURRENT_DATE) · CHECK (countItems = RAND()) User-defined triggers can be used to work around these restrictions. Although similar in implementation, it is semantically clear that triggers will only be fired when the table is directly modified, and that it is the designer's responsibility to handle indirect, important changes in other tables; constraints on the other hand are intended to be &quot;true at all times&quot; regardless of the user's actions or the designer's lack of foresight.

In the context of computer science and data management, commit refers to the idea of making a set of tentative changes permanent. A popular usage is at the end of a transaction. A commit is the act of committing.

Data management

A COMMIT statement in SQL ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users. The general format is to issue a BEGIN WORK statement, one or more SQL statements, and then the COMMIT statement. Alternatively, a ROLLBACK statement can be issued, which undoes all the work performed since BEGIN WORK was issued. A COMMIT statement will also release any existing savepoints that may be in use. In terms of transactions, the opposite of commit is to discard the tentative changes of a transaction, a rollback.

Revision control

Commits are also done for revision control systems for source code such as Subversion or Concurrent Versions System. A commit in the context of these version control systems refers to submitting the latest changes of the source code to the repository, and making these changes part of the head revision of the repository. Thus, when other users do an UPDATE or a checkout from the repository, they will receive the latest committed version, unless they specify they wish to retrieve a previous version of the source code in the repository. Version control systems also have similar functionality to SQL databases in that they allow rolling back to previous versions easily. In this context, a commit with version control systems is not as dangerous as it allows easy rollback, even after the commit has been done.

A Common Table Expression (in SQL) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. CTE can be thought of as an alternatives to derived tables (subquery), views, and inline user-defined functions.

A relational database management system uses SQL conditions or Expression (programming) in WHERE clauses and in HAVING clauses to SELECT subsets of data.

Examples

To SELECT one row of data from a table tab with primary key column pk set to 100 -- use the condition pk = 100: SELECT * FROM tab WHERE pk = 100 To SELECT the duplicate rows of data from a table tab with duplicate key column dk set to 100 -- use the condition dk = 100 and the condition having count(*) &gt; 1: SELECT * FROM tab WHERE dk = 100 having count(*) &gt; 1

Correlated subquery

23

Correlated subquery

A correlated sub-query is a term used for specific types of queries in SQL in computer databases. It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query. Here is an example for a typical correlated sub-query. In this example we are finding the list of employees (employee number and names) having more salary than the average salary of all employees in that employee's department. SELECT empnum, name FROM employee as e1 WHERE salary &gt; (SELECT avg(salary) FROM employee WHERE department = e1.department); In the above query the outer query is, SELECT empnum, name FROM employee as e1 WHERE salary &gt; And the inner query is, (SELECT avg(salary) FROM employee WHERE department = e1.department); In the above nested query the inner query has to be executed for every employee as the department will change for every row. Hence the average salary will also change.

CUBRID is a comprehensive open source and complete free relational database management system (RDBMS) highly optimized for Web applications, especially when complex business services process large amount of data and generate huge concurrent requests. By providing unique optimized features, CUBRID enables to process much more parallel requests at much less response time. With CUBRID, companies will benefit from superior performance and stability, scalability, and high availability required by organization to provide non-stop service for their valuable customers.

Who else uses CUBRID?

CUBRID is being actively used by IT industry leader in Korea ­ NHN Corporation 10,000 servers. In addition, large Hosting companies Cafe24 ESTsoft Security

[10] [5] [3] [2]

, which breeds a farm of over , Software Security company

and Mireene

[4]

, and many Korean local colleges manage their data with CUBRID. Two third of all CUBRID references

[6]

come from the Government sector. Korea National Tax Service

[7]

, Korea Ministry of Public Administration and

[9]

, Korea Ministry of National Defense

[8]

, Busan Transportation Corporation

, and Korea White House

are major customers who deployed CUBRID as their major database management software. Just imagine how

much sensitive data do they all have, how much security do they all require, and they all chose CUBRID as their database management solution. CUBRID helped many organizations gain greater results and achieve faster performance at a lower cost of ownership.

License Policy

CUBRID, unlike other database systems, does not have an Enterprise version of its DBMS. CUBRID does not distinguish in its license policy

[11]

between Community and Enterprise. There is only one version of CUBRID

DBMS, which is under GPL v2.0 or GPL v3.0. However, the GUI tools and API interfaces are distributed under Berkeley Software Distribution license. There is a reason for such a license policy. CUBRID wants all commercial companies to benefit from deploying its database as much as possible. BSD license allows companies to keep the source code of their proprietary software closed, thus they can make real profit.

CUBRID

25

Linux Foundation Silver Sponsor

SAN FRANCISCO, April 7, 2010 ­ CUBRID became the newest member of The Linux Foundation

[12]

.

The company is joining the Linux Foundation to gain access to exclusive networking opportunities and face-to-face collaboration with members of the Linux community. Linux' strengths in the enterprise translate into major advantages for supporting web-based businesses. Its ability to enable seamless high-volume transactions and high performance server/client infrastructure are among the reasons CUBRID has become an active member of the Linux development community and the Linux Foundation. &quot;We are looking forward to collaboration with other Linux Foundation members at events where major Linux stakeholders meet face-to-face. It is this benefit that will help us advance our customers' web services strategies,&quot; said Byungjoo Chung

[13]

, CEO of CUBRID.

[14]

CUBRID also participated at the Linux Foundation Collaboration Summit on April 14­16, 2010 in San Francisco .

Product Name Origin

The name &quot;CUBRID&quot; is a combination of two words &quot;Cube&quot; and &quot;Bridge&quot;. In case of CUBRID, &quot;Bridge&quot; stands of the &quot;data bridge&quot;, while &quot;Cube&quot; is a sealed box which provides security for its contents. Thus, CUBRID means a secure software which holds sensitive information.

Platforms and Interfaces

The CUBRID server and official libraries are implemented in C and C++, while CUBRID Manager, a Client Manager for CUBRID DBMS, is implemented in Java. CUBRID runs on Linux and Microsoft Windows platforms and provides language-specific APIs, including JDBC, PHP, ODBC, and C-API. Both CUBRID and its CUBRID Manager provide a command-line interface called CSQL and an front-end administration tool called SQL buddy for CUBRID

In database packages, a cursor comprises a control structure for the successive traversal (and potential processing) of records in a result set. Cursors provide a mechanism by which a database client iterates over the records in a database. Using cursors, the client can get, put, and delete database records. Database programmers use cursors for processing individual rows returned by the database system for a query. Cursors address the problem of impedance mismatch, an issue that occurs in many programming languages . Most procedural programming languages do not offer any mechanism for manipulating whole result-sets at once. In this scenario, the application must process rows in a result-set sequentially. Thus one can think of a database cursor as an iterator over the collection of rows in the result set. Several SQL statements do not require the use of cursors. That includes the INSERT statement, for example, as well as most forms of the DELETE and UPDATE statements. Even a SELECT statement may not involve a cursor if it is used in the variation of SELECT INTO. A SELECT INTO retrieves at most a single row directly into the application.

Working with cursors

This section introduces the ways the SQL:2003 standard defines how to use cursors in applications in embedded SQL. Not all application bindings for relational database systems adhere to that standard, and some (such as CLI or JDBC) use a different interface. A programmer makes a cursor known to the DBMS by using a DECLARE ... CURSOR statement and assigning the cursor a (compulsory) name: DECLARE cursor_name CURSOR FOR SELECT ... FROM ... Before code can access the data, it must open the cursor with the OPEN statement. Directly following a successful opening, the cursor is positioned before the first row in the result set. OPEN cursor_name Programs position cursors on a specific row in the result set with the FETCH statement. A fetch operation transfers the data of the row into the application. FETCH cursor_name INTO ... Once an application has processed all available rows or the fetch operation is to be positioned on a non-existing row (compare scrollable cursors below), the DBMS returns a SQLSTATE '02000' (usually accompanied by an SQLCODE +100) to indicate the end of the result set. The final step involves closing the cursor using the CLOSE statement: CLOSE cursor_name After closing a cursor, a program can open it again, which implies that the DBMS re-evaluates the same query or a different query and builds a new result-set.

Cursor (databases)

29

Scrollable cursors

Programmers may declare cursors as scrollable or not scrollable. The scrollability indicates the direction in which a cursor can move. With a non-scrollable cursor, also known as forward-only, one can FETCH each row at most once, and the cursor automatically moves to the immediately following row. A fetch operation after the last row has been retrieved positions the cursor after the last row and returns SQLSTATE 02000 (SQLCODE +100). A program may position a scrollable cursor anywhere in the result set using the FETCH SQL statement. The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL, although different language bindings like JDBC may apply different default. DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT ... FROM ... The target position for a scrollable cursor can be specified relative to the current cursor position or absolute from the beginning of the result set. FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM cursor_name FETCH ABSOLUTE n FROM cursor_name FETCH RELATIVE n FROM cursor_name Scrollable cursors can potentially access the same row in the result set multiple times. Thus, data modifications (insert, update, delete operations) from other transactions could have an impact on the result set. A cursor can be SENSITIVE or INSENSITIVE to such data modifications. A sensitive cursor picks up data modifications impacting the result set of the cursor, and an insensitive cursor does not. Additionally, a cursor may be ASENSITIVE, in which case the DBMS tries to apply sensitivity as much as possible.

&quot;WITH HOLD&quot;

Cursors are usually closed automatically at the end of a transaction, i.e when a COMMIT or ROLLBACK (or an implicit termination of the transaction) occurs. That behavior can be changed if the cursor is declared using the WITH HOLD clause. (The default is WITHOUT HOLD.) A holdable cursor is kept open over COMMIT and closed upon ROLLBACK. (Some DBMS deviate from this standard behavior and also keep holdable cursors open over ROLLBACK.) DECLARE cursor_name CURSOR WITH HOLD FOR SELECT ... FROM ... When a COMMIT occurs, a holdable cursor is positioned before the next row. Thus, a positioned UPDATE or positioned DELETE statement will only succeed after a FETCH operation occurred first in the transaction. Note that JDBC defines cursors as holdable per default. This is done because JDBC also activates auto-commit per default. Due to the usual overhead associated with auto-commit and holdable cursors, both features should be explicitly deactivated at the connection level.

Cursor (databases)

30

Positioned update/delete statements

Cursors can not only be used to fetch data from the DBMS into an application but also to identify a row in a table to be updated or deleted. The SQL:2003 standard defines positioned update and positioned delete SQL statements for that purpose. Such statements do not use a regular WHERE clause with predicates. Instead, a cursor identifies the row. The cursor must be opened and positioned on a row already using the FETCH statement. UPDATE table_name SET WHERE ... CURRENT OF cursor_name

DELETE FROM table_name WHERE CURRENT OF cursor_name

The cursor must operate on an updatable result set in order to successfully execute a positioned update or delete statement. Otherwise, the DBMS would not know how to apply the data changes to the underlying tables referred to in the cursor.

Cursors in distributed transactions

Using cursors in distributed transactions (X/Open XA Environments), which are controlled using a transaction monitor, is no different than cursors in non-distributed transactions. One has to pay attention when using holdable cursors, however. Connections can be used by different applications. Thus, once a transaction has been ended and committed, a subsequent transaction (running in a different application) could inherit existing holdable cursors. Therefore, an application developer has to be aware of that situation.

Cursors in XQuery

The XQuery language allows cursors to be created using the subsequence() function. The format is: let $displayed-sequence := subsequence($result, $start, $item-count)

Where $result is the result of the initial XQuery, $start is the item number to start and $item-count is the number of items to return. Equivalently this can also be done using a predicate: let $displayed-sequence := $result[$start to $end]

Where $end is the end sequence. For complete examples see the XQuery Wikibook

[1]

.

Cursor (databases)

31

Disadvantages of cursors

The following information may vary from database system to database system. Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted. Cursors allocate resources on the server, for instance locks, packages, processes, temporary storage, etc. For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query's result-set. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures.

A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database. Examples of DCL commands include: · GRANT to allow specified users to perform specified tasks. · REVOKE to cancel previously granted or denied permissions. The following privileges can be GRANTED TO or REVOKED FROM a user or role: · CONNECT · SELECT · INSERT · UPDATE · DELETE · EXECUTE · USAGE In Oracle, executing a DCL command issues an implicit commit. In PostgreSQL, executing DCL is transactional, and can be rolled back.

See also

· Data Definition Language · Data Manipulation Language

Data Definition Language

33

Data Definition Language

A Data Definition Language (DDL) is a computer language for defining data structures. The term was first introduced in relation to the Codasyl database model, where the schema of the database was written in a Data Definition Language describing the records, fields, and &quot;sets&quot; making up the user Data Model. Initially it referred to a subset of SQL, but is now used in a generic sense to refer to any formal language for describing data or information structures, like XML schemas.

SQL

Initially, DDL was a subset of SQL statements. perhaps the most common code is CREATE TABLE code in this kind of statements.

CREATE statements

Create - To make a new database, table, index, or stored query. A CREATE statement in SQL creates an object inside of a relational database management system (RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation of tables, indexes, users, synonyms and databases. Some systems (such as PostgreSQL) allow CREATE, and other DDL commands, inside of a transaction and thus they may be rolled back. CREATE TABLE statement Perhaps the most common CREATE command is the CREATE TABLE command. The typical usage is: CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters]. Column Definitions: A comma-separated list consisting of any of the following · Column definition: [column name] [data type] {NULL | NOT NULL} {column options} · Primary key definition: PRIMARY KEY ( [comma separated column list] ) · CONSTRAINTS: {CONSTRAINT} [constraint definition] · RDBMS specific functionality For example, the command to create a table named employees with a few sample columns would be: CREATE TABLE employees ( id INTEGER first_name CHAR(50) last_name dateofbirth ); CHAR(75) DATE

PRIMARY KEY, null, not null, null

DROP statements

Drop - To destroy an existing database, table, index, or view. A DROP statement in SQL removes an object from a relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables, users, and databases. Some systems (such as PostgreSQL) allow DROP and other DDL commands to occur inside of a transaction and thus be rolled back. The typical usage is simply DROP objecttype objectname. For example, the command to drop a table named employees would be: DROP TABLE employees;

Data Definition Language The DROP statement is distinct from the DELETE and (non-standard) TRUNCATE statements, in that they do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement would remove the entire table from the database.

34

ALTER statements

Alter - To modify an existing database object. An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is ALTER objecttype objectname parameters. For example, the command to add (then remove) a column named bubbles for an existing table named sink would be: ALTER TABLE sink ADD bubbles INTEGER; ALTER TABLE sink DROP COLUMN bubbles;

Referential integrity statements

Finally, other kind of DDL sentence in SQL are the statements to define referential integrity relationships, usually implemented as primary key and foreign key tags in some columns of the tables. These two statements can be included inside a CREATE TABLE or an ALTER TABLE sentence.

XML Schema

XML Schema is an example of a pure DDL (although only relevant in the context of XML).

DDL Tools and Related Applications

Apache DdlUtils

Apache DdlUtils is a small, easy-to-use component for working with Database Definition (DDL) files. These are XML files that contain the definition of a database schema, e.g. tables and columns. These files can be fed into DdlUtils via its Ant task or programmatically in order to create the corresponding database or alter it so that it corresponds to the DDL. Likewise, DdlUtils can generate a DDL file for an existing database.

[1]

See also

· Data Manipulation Language · Data Control Language

References

[1] http://db.apache.org/ddlutils/

Data Manipulation Language

35

Data Manipulation Language

Data Manipulation Language (DML) is a family of computer languages used by computer programs and/or database users to insert, delete and update data in a database. Read-only querying, i.e. SELECT, of this data may be considered to be either part of DML or outside it, depending on the context. Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. and others. Data Manipulation Language comprises the 'SQL-data change' statements , which modify stored data but not the schema or database objects. Manipulation of persistent database objects (e.g. tables or stored procedures) via the 'SQL-schema' statements , rather than the data stored within them, is considered to be part of a separate Data Definition Language. In SQL these two categories are similar in their detailed syntax, data types, expressions etc., [2] but distinct in their overall function. Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are: · SELECT ... FROM ... WHERE ... · INSERT INTO ... VALUES ... · UPDATE ... SET ... WHERE ... · DELETE FROM ... WHERE ... The purely read-only SELECT query statement is classed with the 'SQL-data' statements

[2] [2] [2] [1]

Other forms of DML are those used by IMS/DLI, CODASYL databases (such as IDMS),

and so is considered by

the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it manipulates (i.e. modifies) data. In common practice though, this distinction is not made and SELECT is widely considered to be part of DML.

[3]

Most SQL database implementations extend their SQL capabilities by providing imperative, i.e., procedural, languages. Examples of these are Oracle's PL/SQL and DB2's SQL PL. Data manipulation languages tend to have many different flavors and capabilities between database vendors. There have been a number of standards established for SQL by ANSI, the standard while not implementing the entire standard. There are two types of data manipulation languages: · Procedural · Declarative Each SQL DML statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative, in that they describe what the program should accomplish, rather than describing how to go about accomplishing it. Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people as well.

The Database Console Commands (DBCC) are a series of statements in Transact-SQL programming language to check the physical and logical consistency of a Microsoft SQL Server database. . These commands are also used to [1] [2] fix existing issues . They are also used for administration and file management. DBCC was previously expanded as Database Consistency Checker.

[3] [1]

Categories of DBCC Commands

Based on their uses, DBCC commands are made of three categories of statements. They are:

This statement is used to recreate the indexes for a particular table. This statement rebuilds indexes in a single [5] [5] step. It also assigns fresh pages to reduce internal and external fragmentation.

[4]

DBCC DBREPAIR

This statement is used to drop or delete a damaged database . However, this command is no longer available with [7] Microsoft SQL Server 2005 and later versions of Microsoft SQL Server . Instead, it has been replaced by the [7] DROP DATABASE Transact-SQL statement

[6]

DBCC INDEXDEFRAG

This statement is used to defragment the clustered and secondary indexes associated with the particular table. index defragmentation is carried out using the fill factor specified at the time of creation of indexes. factor to be specified.

[9] [9] [8]

The

While its

operation is strikingly similar to that of DBCC DBREINDEX, unlike DBCC INDEXFRAG it does not allow new fill

DBCC SHRINKDATABASE

This statement is used to reduce the size of a database log file

[11] [12] [10]

. This statement reduces the physical size of the database

[13]

. An alternate way to shrink a database is to use the commander ALTER DATABASE.

DBCC SHRINKFILE

This statement is used to reduce the size of a data file or log file of a particular database. [13] be shrunk by using the SHRINKFILE attribute of the ALTER DATABASE command.

[14] [15]

The file could also

DBCC UPDATEUSAGE

This statement is used to correct inaccuracies in the page and row statistics in the views.

[16]

DBCC CLEANTABLE

This statement is used to remove spaces occupied by columns when they are removed. . This feature is not [17] available with Micrcosoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005

[17]

DBCC DROPCLEANBUFFERS

This statement is used to drop clean buffers from the buffer pool.

[18]

This feature is not available with Micrcosoft

[18]

SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005

DBCC FREEPROCCACHE

This statement is used to remove all elements from the procedure cache. This feature is not available with [19] Micrcosoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005

[19]

DBCC INPUTBUFFER

This statement is used to display the last statement stored in the buffer.

[20]

Database Console Commands (Transact-SQL)

38

DBCC OPENTRAN

This statement is used to display information about the oldest open transaction.

[21]

DBCC OUTPUTBUFFER

This statement is used to return the current value of the output buffer.

[22]

DBCC PROCCACHE

This statement is used to display information about procedure cache.

[23]

DBCC SHOWCONTIG

This statement is used to display fragmentation information

[23]

DBCC SHOW_STATISTICS

This statement is used to show current distribution statistics

[24]

DBCC SQLPERF

This statement is used to show transaction log statistics

[25]

DBCC TRACESTATUS

This statement is used to display status of trace flags

[26]

DBCC USEROPTIONS

This statement is used to return set as ACTIVE

[27]

DBCC CHECKALLOC

This statement is used to checks whether every extent allocated by the system has been allocted and whether there are extents that have not been allocated.

[28]

DBCC CHECKCATALOG

This statement is used to check for consistency between system tables [28] cross-referencing checks.

[29]

in the system catalog. It does so through

DBCC CHECKCONSTRAINTS

This statement is used to check integrity of specific constraints.

[30]

DBCC CHECKDB

This statement is used to check integrity and allocation of specific objects in database. It also perforns DBCC [28] CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG in the particular order.

[31]

Database Console Commands (Transact-SQL)

39

DBCC CHECKFILEGROUP

This statement is used to check allocation and structural integrity of tables.

[32]

DBCC CHECKIDENT

This statement is used to check identity value of specified table.

[33]

DBCC CHECKTABLE

This statement is used to check the integrity of a table

[28] [34]

and all the pages and structures which comprise the

[28]

table. Both physical and logical checks are performed in this case. [28] ONLY option to check for physical consistency alone.

However, we can also use a PHYSICAL

DBCC NEWALLOC

DBCC NEWALLOC is almost similar to DBCC CHECKALLOC. This statement is not supported by recent [35] versions.

DBCC dllname (FREE)

This statement is used to unload a particular stored procedure DLL from memory.

[35]

DBCC HELP

This statement is used to return syntax information.

[36]

DBCC PINTABLE

This statement is used to mark a particular table to be pinned.

[37]

DBCC ROWLOCK

This statement is used to enable Insert Row Locking (IRL) operations.

[38]

DBCC TRACEOFF

This statement is used to disable a trace flag.

[39]

DBCC TRACEON

This statement is used to turn on a specific trace flag.

[40]

DBCC UNPINTABLE

This statement is used to mark a table as unpinned. In an unpinned table, the table pages in the cache could be easily [41] removed.

Database Console Commands (Transact-SQL)

40

Running a Database Console Command

A database console command could be run from (i) the command window or (ii) query analyzer window.

[42]

Advantages of Database Console Commands

Database Console Commands have a number of advantages. Their use is extremely essential in some instances · Occasionally, there have been bad allocations of database pages.

[42] [42]

· Indexes could be destroyed or corrupted easily. [42] · There could misunderstandings o part of the SQL server engine. · There could be problems when a large number of updates need to be carried out. · Individual pages may lose their optimal storage footprint.

dbForge Studio for MySQL is a database development and administration software for the popular Relational Database Management System (RDBMS) MySQL created by Devart.

Editions (licenses)

dbForge Studio for MySQL has three editions: Express, Standard, and Professional. Express is a free software, which provides basic functionality for working with schema objects, user accounts, and SQL scripts. Standard includes all must-have tools for database developers, such as a debugger, a Query Builder, code templates, object search, various export and maintenance wizards. Professional is a fully-featured software for professional work with database projects, comparing schema and project data, debugging stored procedures and scripts, creating complex queries, and etc.

History

The first version was released on 12 April 2005 under the name &quot;MyDeveloper Studio&quot;. Through 3 years of development the product has raised up to the version 3.0 (released on 01-August-2008), and got a [2] new name - &quot;dbForge Studio for MySQL&quot; as well as a lot of new features and improvements .

Functionality

dbForge Studio for MySQL has got both standard features of most database tools and unique functionality. Main features of dbForge Studio for MySQL, v4.50 include: · Query Profiler - a new tool for database developers that shows results of internal MySQL tools like SHOW PROFILE and EXPLAIN as well as STATUS variables changes due to query execution in a convenient and clear GUI.

Declarative Referential Integrity (DRI) is one of the techniques in the SQL database programming language to ensure data integrity.

Meaning in SQL

A table (called the child table) can refer to a column (or a group of columns) in another table (the parent table) by using a foreign key. The referenced column(s) in the parent table must be under a unique constraint, such as a primary key. Also, self-references are possible (not fully implemented in MS SQL Server though

[1]

). On inserting a

new row into the child table, the relational database management system (RDBMS) checks if the entered key value exists in the parent table. If not, no insert is possible. It is also possible to specify DRI actions on UPDATE and DELETE, such as CASCADE (forwards a change/delete in the parent table to the child tables), NO ACTION (if the specific row is referenced, changing the key is not allowed) or SET NULL / SET DEFAULT (a changed/deleted key in the parent table results in setting the child values to NULL or to the DEFAULT value if one is specified).

Declarative Referential Integrity

45

Product specific meaning

In Microsoft SQL Server the term DRI also applies to the assigning of permissions to users on a database object. [2] Giving DRI permission to a database user allows them to add foreign key constraints on a table.

Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code of the host language. The embedded SQL statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library. The output from the preprocessor is then compiled by the host compiler. This allows programmers to embed SQL statements in programs written in any number of languages such as: C/C++, COBOL and Fortran. The ANSI SQL standards committee defined the embedded SQL standard in two steps: a formalism called Module Language was defined, then the embedded SQL standard was derived from Module Language.

[1]

The SQL standard

defines embedding of SQL as embedded SQL and the language in which SQL queries are embedded is referred to as the host language. A popular host language is C. The mixed C and embedded SQL is called Pro*C in Oracle and Sybase database management systems. In the PostgreSQL database management system this precompiler is called ECPG. Other embedded SQL precompilers are Pro*Ada, Pro*COBOL, Pro*FORTRAN, Pro*Pascal, and Pro*PL/I.

Systems that support Embedded SQL

IBM DB2

IBM DB2 version 9 for Linux, UNIX and Windows supports embedded SQL for C, C++, Java, COBOL, FORTRAN and REXX although support for FORTRAN and REXX has been deprecated.

[2]

Oracle Corporation

Ada Pro*Ada was officially desupported by Oracle in version 7.3. Starting with Oracle8, Pro*Ada has been replaced by SQL*Module but appears to have not been updated since. standard for Ada. C/C++ Pro*C became Pro*C/C++ with Oracle8. Pro*C/C++ is currently supported as of Oracle Database 11g. COBOL Pro*COBOL is currently supported as of Oracle Database 11g. Fortran Pro*FORTRAN is no longer updated as of Oracle8 but Oracle will continue to issue patch releases as bugs are reported and corrected.

[4] [3]

SQL*Module is a module language

that offers a different programming method from embedded SQL. SQL*Module supports the Ada83 language

Embedded SQL Pascal Pro*Pascal was not released with Oracle8. PL/I Pro*PL/I was not released with Oracle8. The Pro*PL/I Supplement to the Oracle Precompilers Guide, however, continues to make appearances in the Oracle Documentation Library (current as of release 11g).

[4] [4]

48

PostgreSQL

C/C++ ECPG is part of PostgreSQL since version 6.3. COBOL Cobol-IT

[5]

is now distributing a COBOL precompiler for PostgreSQL

Altibase

C/C++ SESC is an embedded SQL precompiler provided by Altibase Corp. for its DBMS server.

Data Access Corporation

With DataFlex 3.2 and Visual DataFlex you can pass SQL statements via one of the Data Access CLI connectivity kits to Microsoft SQL Server, IBM DB2 or any ODBC supporting database. The results can be retrieved and processed.

Microsoft SQL Server

COBOL Cobol-IT

[5]

is distributing a Embedded SQL precompiler for COBOL.

MySQL

COBOL Cobol-IT

[5]

is distributing a Embedded SQL precompiler for COBOL.

Systems that do not support Embedded SQL

Microsoft SQL Server

Embedded SQL for C has been deprecated as of Microsoft SQL Server 2008 although earlier versions of the product support it.

EnterpriseDB is a privately-held company that provides enterprise class support for PostgreSQL through its product Postgres Plus Standard Server, a relational database management system (RDBMS) built as a superset of PostgreSQL with additional open source packages and targeted for the enterprise market. The company also offers Postgres Plus Advanced Server, which adds compatibility software to simplify application migration from other databases.

History

Founded in March 2004, EnterpriseDB is headquartered in Westford, Massachusetts and has other offices in the United States, Europe, and Asia.

Products

In addition to providing a mirror site to download PostgreSQL, Enterprise offers two distributions based on PostgreSQL that have additional features and enterprise-class support. These distributions are available for free download and are supported on several different platforms, including Linux, Windows, and Solaris. They include connectors for the most common enterprise programming languages and environments, including: JDBC, ODBC, .NET, ESQL / C++, Perl, Python, PHP. All three versions of the database server are available for free download. · PostgreSQL - the bits developed by the Postgres community · Postgres Plus Standard Server - all the features of PostgreSQL plus additional QA testing, integrated components, tuning and one-click install · Postgres Plus Advanced Server - all the features of Postgres Standard Server plus Oracle compatibility, scalability features, and DBA and developer tools [it can run Oracle applications written for Oracle databases by revising the core of PostgreSQL to recognize Oracle's PL/SQL Oracle

[5] [3] [4]

as well as handle data replication to and from

]

EnterpriseDB EnterpriseDB also offers the following services and support options to aid in the development and deployment of Postgres-based applications: · Remote DBA - administration of a Postgres-based applicaition · Developer support subscription - 8x5 support, product updates and case management · Deployment support subscription - 8x5 or 24x7 support, product updates and case management · Getting Started with Postgres training - half day of training designed for beginners · Postgres administration training - four days of training covering all aspects of the database · Packages service: Health Check - detailed architectural review · Packages service: Partitioning - define a partitioning strategy for an existing installation · Packages service: Replication - define a replication strategy for an existing installation · Packages service: High Availability - define a high availability strategy for an existing installation

In the context of relational databases, a foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship. The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in SQL:2003 as a self-referencing or recursive foreign key. A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys. Improper foreign key/primary key relationships or not enforcing those relationships are often the source of many database and data modeling problems.

Foreign key CONSTRAINT col3_fk FOREIGN KEY(col3) REFERENCES other_table(key_col) ON DELETE CASCADE, ... ) If the foreign key is a single column only, the column can be marked as such using the following syntax: CREATE TABLE table_name ( id INTEGER PRIMARY KEY, col2 col3 ... ) CHARACTER VARYING(20), INTEGER REFERENCES other_table(column_name),

54

Referential Actions

Because the Database Management System enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. SQL:2003 specifies 5 different referential actions that shall take place in such occurrences: · CASCADE · RESTRICT · NO ACTION · SET NULL · SET DEFAULT

CASCADE

Whenever rows in the master (referenced) table are deleted, the respective rows of the child (referencing) table with a matching foreign key column will get deleted as well. This is called a cascade delete. Example Tables: Customer(customer_id, cname, caddress) and Order(customer_id, products, payment) Customer is the master table and Order is the child table, where 'customer_id' is the foreign key in Order and represents the customer who placed the order. When a row of Customer is deleted, any Order row matching the deleted Customer's customer_id will also be deleted.

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error. In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. This is different from RESTRICT, which assumes at the outset that the operation will violate the constraint. Using NO ACTION, the triggers or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.

Foreign key

55

SET NULL

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

SET DEFAULT

Similarly to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.

Triggers

Referential actions are generally implemented as implied triggers (i.e. triggers with system-generated names, often hidden.) As such, they are subject to the same limitations as user-defined triggers, and their order of execution relative to other triggers may need to be considered; in some cases it may become necessary to replace the referential action with its equivalent user-defined trigger to ensure proper execution order, or to work around mutating-table limitations. Another important limitation appears with transaction isolation: your changes to a row may not be able to fully cascade because the row is referenced by data your transaction cannot &quot;see&quot;, and therefore cannot cascade onto. An example: while your transaction is attempting to renumber a customer account, a simultaneous transaction is attempting to create a new invoice for that same customer; while a CASCADE rule may fix all the invoice rows your transaction can see to keep them consistent with the renumbered customer row, it won't reach into another transaction to fix the data there; because the database cannot guarantee consistent data when the two transactions commit, one of them will be forced to rollback (often on a first-come-first-served basis.)

Example

As a first example to illustrate foreign keys, suppose an accounts database has a table with invoices and each invoice is associated with a particular supplier. Supplier details (such as address or phone number) are kept in a separate table; each supplier is given a 'supplier number' to identify it. Each invoice record has an attribute containing the supplier number for that invoice. Then, the 'supplier number' is the primary key in the Supplier table. The foreign key in the Invoices table points to that primary key. The relational schema is the following. Primary keys are marked in bold, and foreign keys are marked in italics. Supplier ( SupplierNumber, Name, Address, Type ) Invoices ( InvoiceNumber, SupplierNumber, Text ) The corresponding Data Definition Language statement is as follows. CREATE TABLE Supplier ( SupplierNumber Name Address Type INTEGER NOT NULL, VARCHAR(20) NOT NULL, VARCHAR(50) NOT NULL, VARCHAR(10),

FSQL, Fuzzy Structured Query Language, or Fuzzy SQL, means, fuzzy SQL language. It is an extension of the SQL language that allows users to write flexible conditions in their queries. using the fuzzy logic defined by Lofti A. Zadeh. Some of the main extensions to SQL are: · Linguistic Labels: If an attribute is capable of fuzzy treatment then linguistic labels can be defined on it. These labels will be preceded with the symbol $ to distinguish them easily. There are two types of labels and they will be used in different fuzzy attribute types: 1. Labels for attributes with an ordered underlined fuzzy domain: Every label of this type has associated a trapezoidal possibility distribution. 2. Labels for attributes with an non ordered fuzzy domain. Here, there is a similarity relation defined between each two labels in the domain. The similarity degree is in the interval [0,1]. · Possibility and Necessity Fuzzy Comparators: Besides the typical comparators (=, &gt;...), FSQL includes the fuzzy comparators in the following table. Like in SQL, fuzzy comparators may compare one column with one constant or two columns of the same type.

FSQL

57

Comparator for Possibility Comparator for Necessity FEQ or F= NFEQ or NF=

Significance Fuzzy EQual (Possibly/Necessarily Equal)

FDIF, F!= or F&lt;&gt; FGT or F&gt; FGEQ or F&gt;= FLT or F&lt; FLEQ or F&lt;= MGT or F&gt;&gt; MLT or F&lt;&lt;

NFDIF, NF!= or NF&lt;&gt; NFGT of NF&gt; NFGEQ or NF&gt;= NFLT or NF&lt; NFLEQ or NF&lt;= NMGT or NF&gt;&gt; NMLT or NF&lt;&lt;

Fuzzy DIFferent Fuzzy Greater Than Fuzzy Greater or Equal Fuzzy Less Than Fuzzy Less or Equal Much Greater Than Much Less Than

Possibility comparators are more general than necessity comparators are. Then, necessity comparators retrieve less tuples and these comply with the conditions necessarily. · Inclusion Fuzzy Comparators: FSQL includes two comparators for the inclusion operation: FINCL (Fuzzy Included in) and INCL (Included in). · Fulfilment thresholds (T): For each simple condition a fulfilment threshold may be established (default is 1). with the format: condition THOLD T indicating that the condition must be satisfied with minimum degree T in [0,1] to be considered. The reserved word THOLD is optional and may be substitute for a traditional crisp comparator (=, &lt;...), modifying the query meaning. The word THOLD is equivalent to using the crisp comparator greater or equal. The constant T maybe substituted by a qualifier (a predefined label) like $High or $Low. Example: Give me all persons with fair hair (in minimum degree 0.5) that they are possibly taller than label $Tall (in minimum degree 0.8):

SELECT * FROM Person

WHERE Hair FEQ $Fair THOLD 0.5 AND Height FGT $Tall THOLD 0.8

· Function CDEG(attribute): It shows a column with the fulfilment degree of the condition of the query, for a specific attribute, which is expressed between brackets as the argument. If logic operators appear, the calculation of this compatibility degree is carried out in the following way: We use the minimum T-norm and the maximum T-conorm, but the user may change these values by default modifying only a view (FSQL_NOTANDOR). In this view the user can set the function to use for every logic operator (NOT, AND, OR). Obviously, that function must be implemented in the FSQL Server or may be implemented by the user himself. · Basic Fuzzy Constants: Firstly, FSQL defined the syntax for some fuzzy constants. These fuzzy constants that we can use in FSQL are detailed in the following table:

FSQL

58

Fuzzy Constant UNKNOWN UNDEFINED NULL $[a,b,c,d] $label

Significance Unknown value but the attribute is applicable The attribute is not applicable or it is meaningless Total ignorance: We know nothing about it Fuzzy trapezium (a&lt;=b&lt;=c&lt;=d) Linguistic Label: It may be a trapezium or a scalar (defined in FMB). Interval &quot;Between n and m&quot; (a=b=n and c=d=m). Fuzzy value &quot;Approximately n&quot; (b=c=n and n-a=d-n=margin).

[n,m] #n

· Extended Fuzzy Constants: In FSQL we can also use the following fuzzy constants:

Fuzzy Constant $[a,b,c,d,P1/N1,...,Pn/Nn] Significance Extended fuzzy trapezoid (with some points Pi/Ni where all the Ni are between a and b or between c and d). Values b and c are both optional. If they do not exist, then this constant is a fuzzy value without kernel. Fuzzy value &quot;Approximately n&quot;: triangle n ± m. Non-continuous possibility distribution on labels, where P1, P2, ..., Pn are the possibility values and L1, L2, ...,Ln are the labels. Non-continuous possibility distribution on labels, where L1, L2, ..., Ln are the labels, with possibility degrees 1 for all of them: {1/L1, ..., 1/Ln}. Non-continuous possibility distribution on numbers, where P1, P2, ..., Pn are the possibility values and N1, N2, ..., Nn are the numbers. Non-continuous possibility distribution on numbers, where N1, N2, ..., Nn are the numbers, with possibility 1 for all of them: {1/N1, ..., 1/Nn}.

In various SQL implementations, a hint is an addition to the SQL standard that instructs the database engine on how to execute the query. For example, a hint may tell the engine to use as little memory as possible (even if the query will run slowly), or to use or not use an index even if the query optimizer would decide otherwise. Different database engines use different approaches in implementing hints. MySQL uses its own extension to the SQL standard, where a table name may be followed by USE INDEX, FORCE INDEX or IGNORE INDEX keywords

[1]

. Oracle implements hints by using specially crafted comments in the query that begin with a + symbol,

HSQLDB (Hyper Structured Query Language Database) is a relational database management system written in Java. HSQLDB is based on the discontinued Hypersonic SQL Project. HSQLDB is available under a BSD license. It has a JDBC driver and supports a large subset of SQL-92, SQL-99, and SQL:2003 standards. It offers a fast, small (around 600 kilobytes in the standard version) database engine which offers both in-memory and disk-based tables. Embedded and server modes are available. Additionally, it includes tools such as a minimal web server, in-memory query and management tools (can be run as applets), and a number of demonstration examples. It can run on Java runtimes from version 1.1 upwards, including free Java runtimes such as Kaffe. HSQLDB is currently being used as a database and persistence engine in many open source software projects, such [4] as OpenOffice.org Base and the Standalone Roller Demo, as well as in commercial products, such as Mathematica or InstallAnywhere (starting with version 8.0).

[5] [2] [3] [1]

Over 350 book titles document the use of HSQLDB for application development with frameworks such as Spring [6] Framework or Hibernate.

HSQLDB

61

Transaction support

HSQLDB version 2.0 has three transaction control modes. It supports read committed and serializable isolation levels with table level locks or with multiversion concurrency control (MVCC), or a combination of locks and MVCC. version 1.8.1 supports transaction isolation level 0 (read uncommitted) only.

[7]

Data storage

HSQLDB has two main table types used for durable read-write data storage (i.e. if transaction has been successfully committed, it is guaranteed that the data will survive system failure and will keep its integrity). The default MEMORY type stores all data changes to the disk in the form of a SQL script. During engine start up, these commands are executed and data is reconstructed into the memory. While this behavior is not suitable for very large tables, it provides highly regarded performance benefits and is easy to debug. Another table type is CACHED, which allows one to store gigabytes of data, at the cost of the slower performance. HSQLDB engine loads them only partially and synchronizes the data to the disk on transaction commits. However, the engine always loads all rows affected during an update into the memory. This renders very large updates impossible without splitting the work into smaller parts.

In Microsoft SQL Server, log shipping is the process of automating the backup of a database and transaction log files on a primary (production) server, and then restoring them onto a standby server . Similar to replication, the primary purpose of log shipping is to increase database availability by maintaining a backup server that can replace production server quickly. Although the actual failover mechanism in log shipping is manual, this implementation is often chosen due to its low cost in human and server resources, and ease of implementation. As comparison, SQL server clusters enable automatic failover, but at the expense of much higher storage and license costs. Compared to database replication, log shipping does not provide as much reporting capabilities, but backs up also system tables along with data tables, and locks standby server from users' modifications. is not suitable for failover purposes.

MaxDB is an ANSI SQL-92 (entry level) compliant relational database management system (RDBMS) from SAP AG, which was delivered also by MySQL AB from 2003 to 2007. MaxDB is targeted for large SAP environments e.g. mySAP Business Suite and other applications that require enterprise-level database functionality. It is able to run terabyte-range data in continuous operation.

History

The database development started in 1977 as a research project at the Technical University of Berlin. In the early 1980s it became a database product that subsequently was owned by Nixdorf Computer, Siemens-Nixdorf, Software AG and today by SAP AG. Along this line it has been named VDN, Reflex, Supra 2, DDB/4, Entire SQL-DB-Server and Adabas D. In 1997 SAP took over the software from Software AG and renamed it to SAP DB. Since October 2000 SAP DB sources additionally were released as open source under the GNU General Public License. In 2003 SAP AG and MySQL AB joined a partnership and re-branded the database system to MaxDB. In October 2007 this reselling was terminated and sales and support of the database reverted back to SAP

[3]

. SAP

AG is now managing MaxDB development, distribution, and support. Source code of MaxDB is no longer available under the GNU General Public License. SAP also stated that &quot;Further commercial support concepts to cover mission critical use requirements outside of SAP scenarios are currently subject to discussion.&quot;

[4]

Version 7.5 of MaxDB is a direct advancement of the SAP DB 7.4 code base. Therefore, the MaxDB software version 7.5 can be used as a direct upgrade of previous SAP DB versions starting 7.2.04 and higher. MaxDB is subjected to SAP AG's complete quality assurance process before it is shipped with SAP solutions or [5] provided as a download on the 'SAP Network' .

Features

MaxDB is delivered with a set of administration and development tools. Most tools are GUI based and have CLI (Command Line Interface) based counterparts. It offers bindings for JDBC; ODBC; SQLDBC (native C/C++ interface); Precompiler; PHP; Perl; Python; WebDAV; OLE DB, ADO, DAO, RDO and .NET via ODBC; Delphi and Tcl via Third Party Programming Interfaces. MaxDB is Cross-platform, offering releases for HP-UX, IBM AIX, Linux, Solaris, Microsoft Windows 2000, Microsoft Windows Server 2003, and Microsoft Windows XP. SAP users should check the details of the platform availability on the SAP product pages for the product that will be used together with MaxDB.

MaxDB

64

Distinguishing features

MaxDB offers built-in hot backup, does not need any online reorganizations and claims to be SQL 92 Entry-Level compatible. One current development goal is &quot;zero administration&quot; and by this &quot;low TCO&quot;. It is also fair to expect good online transaction processing (OLTP) performance combined with relatively low hardware requirements. High availability mode and small fault record is another historically observed feature of MaxDB, which holds some appraisal from the open source software and db communities.

Future releases

The next release will have the name MaxDB 7.7.00. One possible future feature for 7.7.00 is the use of Multiversion Concurrency Control (MVCC) instead of the current lock based implementation.

Licensing

MaxDB was licensed under the GNU GPL from versions 7.2 through 7.6. Programming interfaces were licensed under the GPL with exceptions for projects released under other Open Source licenses. SAP DB 7.3 and 7.4 were licensed as GPL with LGPL drivers. MaxDB 7.5 was offered under dual licensing.i.e. licensed as GPL with GPL drivers or a commercial license. From version 7.5 through version 7.6 onwards distribution of MaxDB (previously SAP DB) to the open source community was provided by MySQL AB, the same company that develops the open-source software database, MySQL. Development was done by SAP AG, MySQL AB and the open-source software community. In October 2007, SAP assumed full sales and commercial support for MaxDB. MaxDB 7.6 is now closed source, available free-of-charge (without support, and with usage restrictions) for use with non-SAP applications. Commercial support models for using MaxDB outside of SAP environments are under consideration.

Types There are three types of meta-SQL elements: · Construct Constructs are a direct substitution of a value, and help to build or modify a SQL statement. Examples include %Bind, %InsertSelect, and %List. · Function Functions perform actions or cause another function to be called. Examples include %ClearCursor, %Execute, and %ExecuteEdits. · Meta-variable. Meta-variables enable substitution of text within SQL statements. Examples include %AsOfDate, %Comma, and %JobInstance. Meta-SQL Placement Considerations: Not all meta-SQL can be used by all programs. Some meta-SQL can be used only in Application Engine programs. Other meta-SQL can only be used as part of a SQL statement in a SQL or dynamic view. The following table lists available meta-SQL elements and where each element can be used. If a meta-SQL construct, function, or meta-variable is supported in PeopleCode, it is supported in all types of PeopleCode programs; that is, in Application Engine PeopleCode programs (actions), component interface PeopleCode programs, and so on. Note. Even if a meta-SQL element is used in PeopleCode, you cannot use meta-SQL like a built-in function. You can use meta-SQL in the SQLExec function, the Select method, the Fill method, and so on. Note. Meta-SQL is not available in SQR

MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single, seamless environment for the MySQL database system. It is the successor to DBDesigner 4 from fabFORCE.net, and replaces the previous package of software, MySQL GUI Tools Bundle.

History

fabFORCE.net DBDesigner4

DBDesigner4 is an open source visual database design and querying tool for the MySQL database released under the GPL. It was written in 2002/2003 by the Austrian programmer Michael G. Zinner for his [2] [3] fabFORCE.net platform using Delphi 7 / Kylix 3. While being a physical-modeling only tool DBDesigner4 offers a

[1]

comprehensive feature set including reverse engineering of MySQL databases, model-to-database synchronization, model poster printing, basic version control of schema models and a SQL query builder. is available for MS Windows and Linux.

[5] fabFORCE.net DBDesigner4 [4]

It

Zinner was approached by representatives from MySQL AB and joined

the company in late 2003 to take over the development of graphical [6] user interface (GUI) tools for MySQL which lead to the creation of the MySQL GUI Tools Bundle.

MySQL GUI Tools Bundle

The MySQL GUI Tools Bundle is a cross-platform open source suite of desktop applications for the administration of MySQL database servers, and for building and manipulating the data within MySQL databases. It was developed by MySQL AB and later by Sun Microsystems and released under the GPL. Development on the GUI Tools bundle has stopped, and is now only preserved under the [7] Download Archives of the MySQL site. The GUI Tools bundle has been superseded by MySQL Workbench, and reached its End-of-Life with the beta releases of MySQL Workbench 5.2. However, the MySQL Support team will continue to provide assistance for the bundle until June 30, 2010.

[8] The MySQL Administrator part of GUI Tools

Future releases of MySQL Workbench will add a migration plugin, providing features comparable to the

The first preview version of MySQL Workbench was released in September 2005, MySQL GUI flagship product. DBDesigner4.

[11] [10] [9]

and was not included in the

MySQL GUI Tools Bundle. Development was started again in 2007 and MySQL Workbench was set to become the Version numbering was started 5.0 to emphasis the fact that MySQL Workbench was developed as the successor to

MySQL Workbench 5.0 and 5.1

MySQL Workbench 5.0 and 5.1 are specialized visual database design tools for the MySQL database. While MySQL Workbench 5.0 was a MS Windows only product cross-platform support was added to MySQL Workbench 5.1 and later.

[12] [13]

MySQL Workbench 5.2

Starting with MySQL Workbench 5.2 the application has evolved to a general database GUI application. Apart from physical database modeling it features a SQL Editor and a database server administration interface, replacing the old MySQL GUI Tools Bundle.

MySQL Workbench is the first MySQL family of products that offer two different editions - an open source and a commercial edition.

[14]

The &quot;Community Edition&quot; is a full featured product that is not crippled in any way. Being the

[15]

foundation for all other editions it will benefit from all future development efforts. The commercial &quot;Standard Edition&quot; extends the Community Edition with a series of modules and plugins. As this business decision was announced soon after the takeover of MySQL by Sun Microsystems this caused [16] [17] speculation about the future licensing of the MySQL database in the press.

Community reception and reviews

Since its introduction MySQL Workbench has become popular within the MySQL community. Since 2010 it is now [18] the 2nd most downloaded product from the MySQL website with more than 250.000 downloads a month. Before that it was voted Database Tool of the Year 2009 on Developer.com.

[19] [20] [21] [22] [23]

MySQL Workbench has been reviewed by the open source community and print magazines. While some limitation of the Beta releases have been noted MySQL Workbench usage is generally recommended.

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered. The syntax for the NVL function is: NVL( string1, replace_with_if_null )

string1 is the string to test for a null value. replace_with is the value returned if string1 is null. Both parameters of the function have to be of the same data type. You cannot use this function to replace a null integer by a string unless you call the TO_CHAR function on that value: NVL(TO_CHAR(numeric_column), 'some string')

See also

· COALESCE, the standard equivalent

Navicat

70

Navicat

Developer(s) Operating system Available in Type License Website PremiumSoft Cross-platform

Multilingual (8) SQL Database Management and Development Shareware

http://www.navicat.com

PremiumSoft Navicat is a series of graphical database management and development software for MySQL, Oracle, SQLite and PostgreSQL. It has an Explorer-like interface and supports multiple database connections for local and remote databases. Navicat allows database administrators and developers to manage, develop and monitor MySQL, Oracle, PostgreSQL and SQLite databases. Navicat works in Microsoft Windows, Macintosh and Linux platforms. Some of its features include: · Visual query builder · SSH and HTTP tunneling · Data and structure synchronization · Import and export and backup of data · Report builder · Task scheduling and wizards tool There are differences in the features available across operating systems.

[1]

History

Navicat for MySQL is the first product of PremiumSoft which is released in 2002. Navicat for PostgreSQL was released in 2005, whereas Navicat for Oracle is released in August 2008 and Navicat for SQLite is released in April 2010. In June 2009, Navicat Premium version 8 is released. Navicat Premium combines all Navicat versions into one version and can connect to different database types including MySQL, Oracle, and PostgreSQL simultaneously, allowing users to do data migration between cross databases. In April 2010, PremiumSoft has released version 9 of Navicat Premium, in this new version, it added the connectivitity of SQLite database to Navicat Premium, allowing Navicat Premium to connect to MySQL, Oracle, Postgresql and SQLite in a single application.

Navicat

71

Free version

Navicat Lite is a free version of the software made available in October 2007 for non-commercial use only. Previously, Navicat was an exclusively commercial software package since its release in 2002. The Lite editions of Navicat lack several features found in the Standard editions, including form view, record [1] filtering, visual query building, and options for import, export and backup of data.

A nested table is a table that is embedded within another table. Individual elements can be inserted, updated, and deleted in a nested table. Since individual elements can be modified in a nested table, they are more flexible than an array because elements in an array can only be modified as a whole, not individually. A nested table doesn't have a maximum size, and an arbitrary number of elements can be stored in it.

A functional language, a superset of SQL, used in Hewlett-Packard's OpenODB database system. SQL1999, formerly known as SQL3, is an Object-Oriented SQL.

PL/pgSQL

73

PL/pgSQL

PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is a procedural language supported by the PostgreSQL RDBMS. It closely resembles Oracle's PL/SQL language. PL/pgSQL, as a fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures. Functions created in the PL/pgSQL language can be called from an SQL statement, or as the action that a trigger performs. PL/pgSQL was created to be able to perform more complex operations and computations than SQL, while being [1] easy to use, and is able to be defined as trusted by the server. Lot of examples of PLpgSQL using are in PL/pgSQL tutorial PL/Java

[3] [2]

.

[8]

PL/pgSQL is the only &quot;PL&quot; language installed by default for PostgreSQL, but many others are available, including , PL/Perl

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. PL/SQL's general syntax resembles that of Ada. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), IBM DB2 (since version 9.7)

[2] [1]

.

Introduction

PL/SQL supports variables, conditions, loops and exceptions. Arrays are also supported, though in a somewhat unusual way, involving the use of PL/SQL collections. PL/SQL collections are a slightly advanced topic. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. PL/SQL program units (essentially code containers) can be compiled into the Oracle database. Programmers can thus embed PL/SQL units of functionality into the database directly. They also can write scripts containing PL/SQL program units that can be read into the database using the Oracle SQL*Plus tool. Once the program units have been stored into the database, they become available for execution at a later time. While programmers can readily embed Data Manipulation Language (DML) statements directly into their PL/SQL code using straight forward SQL statements, Data Definition Language (DDL) requires more complex &quot;Dynamic SQL&quot; statements to be written in the PL/SQL code. However, DML statements underpin the majority of PL/SQL code in typical software applications. In the case of PL/SQL dynamic SQL, early versions of the Oracle Database required the use of a complicated Oracle DBMS_SQL package library. More recent versions have however introduced a simpler &quot;Native Dynamic SQL&quot;, along with an associated EXECUTE IMMEDIATE syntax. Oracle Corporation customarily extends package functionality with each successive release of the Oracle Database.

PL/SQL program units

Anonymous Blocks

Anonymous blocks &lt;&lt;label&gt;&gt; DECLARE Type / item / function / procedure declarations BEGIN Statements EXCEPTION Exception handlers END label; The &lt;&lt;label&gt;&gt; and the DECLARE and EXCEPTION sections are optional. Exceptions, errors which arise during the execution of the code, have one of two types: 1. Predefined exceptions 2. User-defined exceptions.

[3]

form the basis of the simplest PL/SQL code, and have the following structure:

PL/SQL User-defined exceptions are always raised explicitly normal execution to continue. RAISE command has the syntax: RAISE &lt;exception name&gt;; Oracle Corporation has pre-defined several exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, etc. Each exception has a SQL Error Number and SQL Error Message associated with it. Programmers can access these by using the SQLCODE and SQLERRM functions. The DECLARE section defines and (optionally) initialises variables. If not initialised specifically, they default to NULL. For example: declare number1 number(2); number2 number(2) := 17; text1 varchar2(12) := 'Hello world'; text2 begin date := SYSDATE; -- value default -- current date and time by the programmers, using the RAISE or

75

RAISE_APPLICATION_ERROR commands, in any situation where they have determined that it is impossible for

SELECT street_number INTO number1 FROM address WHERE name = 'INU'; end; The symbol := functions as an assignment operator to store a value in a variable. The major datatypes in PL/SQL include NUMBER, INTEGER, CHAR, VARCHAR2, DATE, TIMESTAMP, TEXT etc.

Functions

Functions in PL/SQL are a collection of SQL and PL/SQL statements that perform a task and should return a value to the calling environment. User defined functions are used to supplement the many hundreds of functions built in by Oracle. There are two different types of functions in PL/SQL. The traditional function is written in the form:

There are three types of parameter: IN, OUT and IN OUT. An IN parameter is used as input only. An IN parameter is passed by reference and thus cannot be changed by the called program. An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program. An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program by default by copying but, with the NOCOPY hint may be passed by reference.

Procedures

Procedures are similar to Functions, in that they can be executed to perform work. The primary difference is that procedures cannot be used in a SQL statement and although they can have multiple out parameters they do not &quot;RETURN&quot; a value. Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code. PL/SQL maintains many of the distinctions between functions and procedures found in many general-purpose programming languages, but in addition, functions can be called from SQL, while procedures cannot.

Packages

Packages are groups of conceptually linked Functions, Procedures,Variable,PL/SQL table and record TYPE statements,Constants &amp; Cursors etc. The use of packages promotes re-use of code. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec. They are stored permanently in USER_SOURCE system table. Advantages 1.Modular approach, Encapsulation/hiding of business logic, security, performance improvement, re-usability.They support OOPS features like function over loading,encapsulation. 2.Using we can declare session level (scoped) variables. Since variable declared in pack spec has session scope.

Variables

Numeric variables

variable_name number(P[,S]) := value; To define a numeric variable, the programmer appends the variable type NUMBER to the name definition. To specify the (optional) precision(P) and the (optional) scale (S), one can further append these in round brackets, separated by a comma. (&quot;Precision&quot; in this context refers to the number of digits which the variable can hold, &quot;scale&quot; refers to the number of digits which can follow the decimal point.)

variable_name varchar2(L) := 'Text'; To define a character variable, the programmer normally appends the variable type VARCHAR2 to the name definition. There follows in brackets the maximum number of characters which the variable can store. Other datatypes for character variables include: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, bfile

Date variables

variable_name date := '01-Jan-2005'; Oracle provides a number of data types that can store dates (DATE, DATETIME, TIMESTAMP etc.), however DATE is most commonly used. Programmers define date variables by appending the datatype code &quot;DATE&quot; to a variable name. The TO_DATE function can be used to convert strings to date values. The function converts the first quoted string into a date, using as a definition the second quoted string, for example: to_date('31-12-2004','dd-mm-yyyy') or to_date ('31-Dec-2004','dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American') To convert the dates to strings one uses the function TO_CHAR (date_string, format_string). PL/SQL also supports the use of ANSI date and interval literals.

[4]

The following clause gives an 18-month range:

WHERE dateField BETWEEN DATE '2004-12-31' - INTERVAL '1-6' YEAR TO MONTH AND DATE '2004-12-31'

Datatypes for specific columns

Variable_name Table_name.Column_name%type; This syntax defines a variable of the type of the referenced column on the referenced tables. Programmers specify user-defined datatypes with the syntax:

PL/SQL This sample program defines its own datatype, called t_address, which contains the fields name, street, street_number and postcode. So according to the example, we are able to copy the data from the database to the fields in the program. Using this datatype the programmer has defined a variable called v_address and loaded it with data from the ADDRESS table. Programmers can address individual attributes in such a structure by means of the dot-notation, thus: &quot;v_address.street := 'High Street';&quot;

78

Conditional Statements

The following code segment shows the IF-THEN-ELSIF construct. The ELSIF and ELSE parts are optional so it is possible to create simpler IF-THEN or, IF-THEN-ELSE constructs. IF x = 1 THEN sequence_of_statements_1; ELSIF x = 2 THEN sequence_of_statements_2; ELSIF x = 3 THEN sequence_of_statements_3; ELSIF x = 4 THEN sequence_of_statements_4; ELSIF x = 5 THEN sequence_of_statements_5; ELSE sequence_of_statements_N; END IF; The CASE statement simplifies some large IF-THEN-ELSE structures. CASE WHEN x = 1 THEN sequence_of_statements_1; WHEN x = 2 THEN sequence_of_statements_2; WHEN x = 3 THEN sequence_of_statements_3; WHEN x = 4 THEN sequence_of_statements_4; WHEN x = 5 THEN sequence_of_statements_5; ELSE sequence_of_statements_N; END CASE; CASE statement can be used with predefined selector: CASE x WHEN 1 THEN sequence_of_statements_1; WHEN 2 THEN sequence_of_statements_2; WHEN 3 THEN sequence_of_statements_3; WHEN 4 THEN sequence_of_statements_4; WHEN 5 THEN sequence_of_statements_5; ELSE sequence_of_statements_N; END CASE;

PL/SQL

79

Array handling

PL/SQL refers to arrays as &quot;collections&quot;. The language offers three types of collections: 1. Index-by tables (associative arrays) 2. Nested tables 3. Varrays (variable-size arrays) Programmers must specify an upper limit for varrays, but need not for index-by tables or for nested tables. The language includes several collection methods used to manipulate collection elements: for example FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. Index-by tables can be used to simulate associative arrays, as in this example of a memo function for Ackermann's function in PL/SQL.

Looping

As a procedural language by definition, PL/SQL provides several iteration constructs, including basic LOOP statements, WHILE loops, FOR loops, and Cursor FOR loops.

LOOP statements

Syntax

[5]

:

&lt;&lt;parent_loop&gt;&gt; LOOP statements &lt;&lt;child_loop&gt;&gt; loop statements exit parent_loop when &lt;condition&gt;; -- Terminates both loops exit when &lt;condition&gt;; -- Returns control to parent_loop end loop; exit when &lt;condition&gt;; END LOOP parent_loop; Loops can be terminated by using the EXIT keyword, or by raising an exception.

Cursor FOR loops

FOR RecordIndex IN (SELECT person_code FROM people_table) LOOP DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code); END LOOP; Cursor-for loops automatically open a cursor, read in their data and close the cursor again As an alternative, the PL/SQL programmer can pre-define the cursor's SELECT-statement in advance in order (for example) to allow re-use or to make the code more understandable (especially useful in the case of long or complex queries). DECLARE CURSOR cursor_person IS SELECT person_code FROM people_table;

PL/SQL BEGIN FOR RecordIndex IN cursor_person LOOP DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code); END LOOP; END; The concept of the person_code within the FOR-loop gets expressed with dot-notation (&quot;.&quot;): RecordIndex.person_code

80

Example

declare var number; begin /*N.B. for loop variables in pl/sql are new declarations, with scope only inside the loop */ for var in 0 .. 10 loop dbms_output.put_line(var); end loop;

if (var is null) then dbms_output.put_line('var is null'); else dbms_output.put_line('var is not null'); end if; end;

Output: 0 1 2 3 4 5 6 7 8 9 10 var is null

PL/SQL

81

Similar languages

PL/SQL functions analogously to the embedded procedural languages associated with other relational databases. Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which tries to emulate PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language, SQL/PSM standard. The designers of PL/SQL modelled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in numerous aspects. The structure of a PL/SQL package closely resembles the basic Pascal program structure or a Borland Delphi unit. Programmers can define global data-types, constants and static variables, public and private, in a PL/SQL package. PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usages in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an &quot;Abstract Data Type&quot; (ADT) or &quot;User Defined Type&quot; (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL does not resemble Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages, but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. The Fyracle project aims to enable the execution of PL/SQL code in the open-source Firebird database. The StepSqlite product is a PL/SQL compiler for the popular small database SQLite.

Pro*C (also known as Pro*C/C++) is an embedded SQL programming language used by Oracle Database and Sybase SQL Server database management systems. Pro*C uses either C or C++ as its host language. During compilation, the embedded SQL statements are interpreted by a precompiler and replaced by C or C++ function calls to their respective SQL library. The output from the Pro*C precompiler is standard C or C++ code that is then compiled by any one of several C or C++ compilers into an executable.

The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query. The optimizer considers the possible query plans for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated &quot;cost&quot; to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the data dictionary. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and join algorithms (e.g. sort-merge join, hash join, nested loops). The search space can become quite large depending on the complexity of the SQL query. Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with hints.

Implementation

Most query optimizers represent query plans as a tree of &quot;plan nodes&quot;. A plan node encapsulates a single operation that is required to execute the query. The nodes are arranged as a tree, in which intermediate results flow from the bottom of the tree to the top. Each node has zero or more child nodes--those are nodes whose output is fed as input to the parent node. For example, a join node will have two child nodes, which represent the two join operands, whereas a sort node would have a single child node (the input to be sorted). The leaves of the tree are nodes which produce results by scanning the disk, for example by performing an index scan or a sequential scan.

Query optimizer

83

Join ordering

The performance of a query plan is determined largely by the order in which the tables are joined. For example, when joining 3 tables A, B, C of size 10 rows, 10,000 rows, and 1,000,000 rows, respectively, a query plan that joins B and C first can take several orders-of-magnitude more time to execute than one that joins A and C first. Most query optimizers determine join order via a dynamic programming algorithm pioneered by IBM's System R database project. This algorithm works in two stages: 1. First, all ways to access each relation in the query are computed. Every relation in the query can be accessed via a sequential scan. If there is an index on a relation that can be used to answer a predicate in the query, an index scan can also be used. For each relation, the optimizer records the cheapest way to scan the relation, as well as the cheapest way to scan the relation that produces records in a particular sorted order. 2. The optimizer then considers combining each pair of relations for which a join condition exists. For each pair, the optimizer will consider the available join algorithms implemented by the DBMS. It will preserve the cheapest way to join each pair of relations, in addition to the cheapest way to join each pair of relations that produces its output according to a particular sort order. 3. Then all three-relation query plans are computed, by joining each two-relation plan produced by the previous phase with the remaining relations in the query. In this manner, a query plan is eventually produced that joins all the queries in the relation. Note that the algorithm keeps track of the sort order of the result set produced by a query plan, also called an interesting order. During dynamic programming, one query plan is considered to beat another query plan that produces the same result, only if they produce the same sort order. This is done for two reasons. First, a particular sort order can avoid a redundant sort operation later on in processing the query. Second, a particular sort order can speed up a subsequent join because it clusters the data in a particular way. Historically, System-R derived query optimizers would often only consider left-deep query plans, which first join two base tables together, then join the intermediate result with another base table, and so on. This heuristic reduces the number of plans that need to be considered (n! instead of 4^n), but may result in not considering the optimal query plan. This heuristic is drawn from the observation that join algorithms such as nested loops only require a single tuple (aka row) of the outer relation at a time. Therefore, a left-deep query plan means that fewer tuples need to be held in memory at any time: the outer relation's join plan need only be executed until a single tuple is produced, and then the inner base relation can be scanned (this technique is called &quot;pipelining&quot;). Subsequent query optimizers have expanded this plan space to consider &quot;bushy&quot; query plans, where both operands to a join operator could be intermediate results from other joins. Such bushy plans are especially important in parallel computers because they allow different portions of the plan to be evaluated independently.

Query planning for nested SQL queries

A SQL query to a modern relational DBMS does more than just selections and joins. In particular, SQL queries often nest several layers of SPJ blocks (Select-Project-Join) , by means of group by, exists, and not exists operators. In some cases such nested SQL queries can be flattened into a select-project-join query, but not always. Query plans for nested SQL queries can also be chosen using the same dynamic programming algorithm as used for join ordering, but this can lead to an enormous escalation in query optimization time. So some database management systems use an alternative rule-based approach that uses a query graph model.

Query optimizer

84

Cost estimation

One of the hardest problems in query optimization is to accurately estimate the costs of alternative query plans. Optimizers cost query plans using a mathematical model of query execution costs that relies heavily on estimates of the cardinality, or number of tuples, flowing through each edge in a query plan. Cardinality estimation in turn depends on estimates of the selection factor of predicates in the query. Traditionally, database systems estimate selectivities through fairly detailed statistics on the distribution of values in each column, such as histograms. This technique works well for estimation of selectivities of individual predicates. However many queries have conjunctions of predicates such as select count(*) from R where R.make='Honda' and R.model='Accord'. Query predicates are often highly correlated (for example, model='Accord' implies make='Honda'), and it is very hard to estimate the selectivity of the conjunct in general. Poor cardinality estimates and uncaught correlation are one of the main reasons why query optimizers pick poor query plans. This is one reason why a DBA should regularly update the database statistics, especially after major data loads/unloads.

A query plan (or query execution plan) is an ordered set of steps used to access or modify information in a SQL relational database management system. This is a specific case of the relational model concept of access plans. Since SQL is declarative, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a query is submitted to the database, the query optimizer evaluates some of the different, correct possible plans for executing the query and returns what it considers the best alternative. Because query optimizers are imperfect, database users and administrators sometimes need to manually examine and tune the plans produced by the optimizer to get better performance.

Generating query plans

A given database management system may offer one or more mechanisms for returning the plan for a given query. Some packages feature tools which will generate a graphical representation of a query plan. Other tools allow a special mode to be set on the connection to cause the DBMS to return a textual description of the query plan. Another mechanism for retrieving the query plan involves querying a virtual database table after executing the query to be examined.

Graphical plans

The SQL Server Management Studio tool which ships with Microsoft SQL Server, for example, shows this graphical plan when executing this two-table join against a sample database:

SELECT * FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID ORDER BY c.LastName

The UI allows exploration of various attributes of the operators involved in the query plan, including the operator type, the number of rows each operator consumes or produces, and the expected cost of each operator's work.

It indicates that the query engine will do a scan over the primary key index on the Employee table and a matching seek through the primary key index (the ContactID column) on the Contact table to find matching rows. The resulting rows from each side will be shown to a nested loops join operator, sorted, then returned as the result set to the connection.

Query plan In order to tune the query, the user must understand the different operators that the database may use, and which ones might be more efficient than others while still providing semantically correct query results.

86

Database tuning

Reviewing the query plan can present opportunities for new indexes or changes to existing indexes. It can also show that the database is not properly taking advantage of existing indexes (see query optimizer).

Query tuning

The query optimizer will not always choose the best query plan for a given query. In some databases the query plan can be reviewed, problems found, and then the query optimizer given hints on how to improve it. In other databases alternatives to express the same query (other queries that return the same results) can be tried. Some query tools can generate embedded hints in the query, for use by the optimizer. Some databases like Oracle provide a Plan table for query tuning. This plan table will return the cost and time for executing a Query. In Oracle there are 2 optimization techniques: 1. CBO or Cost Based Optimization 2. RBO or Rule Based Optimization The RBO is slowly being deprecated. For CBO to be used, all the tables referenced by the query must be analyzed. To analyze a table, a package DBMS_STATS can be made use of. The others methods for query optimization include: 1. SQL Trace 2. Oracle Trace 3. TKPROF · Video tutorial on how to perform SQL performance tuning with reference to Oracle

[1]

References

[1] http://seeingwithc.org/sqltuning.html

Rollback (data management)

87

Rollback (data management)

In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed. They are crucial for recovering from database server crashes; by rolling back any transaction which was active at the time of the crash, the database is restored to a consistent state. In SQL, ROLLBACK is a command that causes all data changes since the last BEGIN WORK, or START TRANSACTION to be discarded by the relational database management system (RDBMS), so that the state of the data is &quot;rolled back&quot; to the way it was before those changes were made. A ROLLBACK statement will also release any existing savepoints that may be in use. In most SQL dialects, ROLLBACKs are connection specific. This means that if two connections are made to the same database, a ROLLBACK made in one connection will not affect any other connections. This is vital for proper concurrency. The rollback feature is usually implemented with a transaction log, but can also be implemented via multiversion concurrency control. A cascading rollback occurs in database systems when a transaction (T1) causes a failure and a rollback must be performed. Other transactions dependent on T1's actions must also be rolled back due to T1's failure, thus causing a cascading effect.

The SQL Access Group (SAG) was a group of software companies that was formed in 1989 to define and promote standards for database portability and interoperability. Initial members were Oracle Corporation, Informix, Ingres, DEC, Tandem, Sun and HP. The SAG started the development of the SQL Call Level Interface which later was published as an X/Open specification. In 1992, Microsoft released version 1.0 of ODBC which was based on the X/Open SQL CLI specification. The SQL Access Group transferred its activities and assets to X/Open in the fourth quarter of 1994.

SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment. This technology, introduced in Microsoft SQL Server 2005, allow users for example to create the following types of managed code objects in SQL Server in .NET languages such as C# or VB.NET. · Stored procedures (SPs) which are analogous to procedures or void functions in procedural languages like VB or C, · triggers which are stored procedures that fire in response to Data Manipulation Language (DML) or Data Definition Language (DDL) events, · User-defined functions (UDFs) which are analogous to functions in procedural languages, · User-defined aggregates (UDAs) which allow developers to create custom aggregates that act on sets of data instead of one row at a time, · User-defined types (UDTs) that allow users to create simple or complex data types which can be serialized and deserialized within the database. The SQL CLR relies on the creation, deployment, and registration of .NET assemblies, which are physically stored in managed code dynamic load libraries (DLLs). These assemblies may contain .NET namespaces, classes, functions and properties.

A cursor is a construct available in most implementations of SQL that allows the programmer to handle data in a row-by-row manner. The trouble with cursor processing is that the row-by-row nature cannot easily be made to occur in parallel by database optimizers. This results in a failure of the cursor processing to take full advantage of the processing power available on most database platforms. To overcome this problem, cursor logic can often be converted into set-based SQL queries. Set-based SQL uses the syntax &quot;SELECT...FROM...&quot; with various clauses to perform the data manipulation work. Database optimizers have little trouble dividing such queries into parallel threads, thus fully utilizing the database hardware. The resulting performance gain can be literal orders of magnitude: hours become minutes, and minutes become seconds. Despite the tremendous performce gains from set-based SQL, it can be challenging to find the SQL that replaces fairly straigh-forward cursors. This article discusses examples of such conversions.

Constraints

In this article, the following constraints apply: · The term &quot;cursor&quot; includes all cursor-like behavior. For example, using a loop in a shell script that loops across SQL queries or the output of SQL queries is cursor-like behavior and does not achieve the goal of true set-based processing within the database. · All set-based SQL must be ANSI SQL. A number of vendors provide some extremely powerful proprietary extensions. The goal is to avoid such extensions in favor of ANSI SQL. · The solution must be generalizable. In one or more examples below, specific values may be used for demonstration purposes, but any solution must scale to any number feasible within the power of the database software and machine resources.

Generating the target table with a cursor is fairly simple. pre&gt; declare cursor c is select * from marbles_seed; begin for r in c loop for i in 1..r.quantity loop 000000000000r.co lor_predicted, r.accuracy, r.coverage ); end loop; end loop; end; &lt;/pre&gt;

Solution in ANSI set-based SQL

Solving the problem with ANSI SQL is a bit more code, but requires a bit more creative thought than the nested loop approach of cursors. Number Table To reach the solution requires an intermediate table. The table has one column of type NUMBER that has the values 0 to whatever number of rows is needed. For this discussion, we'll limit it to one million rows. The code is as follows: &lt; Solution Core Assume the source table above is named marbles_seed and the target table is named marbles. The code that generates the needed 270 rows is: insert into marbles select m.texture, m.appearance, m.shape, m.color_actual, m.hits, m.color_predicted, m.accuracy, m.coverage from marbles_seed m, numbers n where m.quantity &gt; n.n

SQL injection

92

SQL injection

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.

[1]

Forms of vulnerability

Incorrectly filtered escape characters

This form of SQL injection occurs when user input is not filtered for escape characters and is then passed into an SQL statement. This results in the potential manipulation of the statements performed on the database by the end user of the application. The following line of code illustrates this vulnerability: statement = &quot;SELECT * FROM users WHERE name = '&quot; + userName + &quot;';&quot; This SQL code is designed to pull up the records of the specified username from its table of users. However, if the &quot;userName&quot; variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the &quot;userName&quot; variable as ' or '1'='1 renders this SQL statement by the parent language: SELECT * FROM users WHERE name = '' or '1'='1'; If this code were to be used in an authentication procedure then this example could be used to force the selection of a valid username because the evaluation of '1'='1' is always true. The following value of &quot;userName&quot; in the statement below would cause the deletion of the &quot;users&quot; table as well as the selection of all data from the &quot;userinfo&quot; table (in essence revealing the information of every user), using an API that allows multiple statements: a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't This input renders the final SQL statement as follows: SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't'; While most SQL server implementations allow multiple statements to be executed with one call in this way, some SQL APIs such as PHP's mysql_query() do not allow this for security reasons. This prevents attackers from injecting entirely separate queries, but doesn't stop them from modifying queries.

SQL injection

93

Incorrect type handling

This form of SQL injection occurs when a user supplied field is not strongly typed or is not checked for type constraints. This could take place when a numeric field is to be used in a SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric. For example: statement := &quot;SELECT * FROM userinfo WHERE id = &quot; + a_variable + &quot;;&quot; It is clear from this statement that the author intended a_variable to be a number correlating to the &quot;id&quot; field. However, if it is in fact a string then the end user may manipulate the statement as they choose, thereby bypassing the need for escape characters. For example, setting a_variable to 1;DROP TABLE users will drop (delete) the &quot;users&quot; table from the database, since the SQL would be rendered as follows: SELECT * FROM userinfo WHERE id=1;DROP TABLE users;

Vulnerabilities inside the database server

Sometimes vulnerabilities can exist within the database server software itself, as was the case with the MySQL server's mysql_real_escape_string() function

[2]

. This would allow an attacker to perform a successful SQL injection

attack based on bad Unicode characters even if the user's input is being escaped. This bug was patched with the release of version 5.0.22 (released on 24th May 06).

Blind SQL injection

Blind SQL Injection is used when a web application is vulnerable to an SQL injection but the results of the injection are not visible to the attacker. The page with the vulnerability may not be one that displays data but will display differently depending on the results of a logical statement injected into the legitimate SQL statement called for that page. This type of attack can become time-intensive because a new statement must be crafted for each bit recovered. There are several tools that can automate these attacks once the location of the vulnerability and the target information has been established. Conditional responses One type of blind SQL injection forces the database to evaluate a logical statement on an ordinary application screen. SELECT booktitle from booklist where bookId = 'OOk14cd' AND 1=1; will result in a normal page while SELECT booktitle from booklist where bookId = 'OOk14cd' AND 1=2; will likely give a different result if the page is vulnerable to a SQL injection. An injection like this may suggest to the attacker that a blind SQL injection is possible, leaving the attacker to devise statements that evaluate to true or false depending on the contents of another column or table outside of the SELECT statement's column list.

[4] [3]

SQL injection Conditional errors This type of blind SQL injection causes an SQL error by forcing the database to evaluate a statement that causes an error if the WHERE statement is true. For example, SELECT 1/0 from users where username='Ralph'; the division by zero will only be evaluated and result in an error if user Ralph exists. Time delays Time Delays are a type of blind SQL injection that cause the SQL engine to execute a long running query or a time delay statement depending on the logic injected. The attacker can then measure the time the page takes to load to determine if the injected statement is true.

94

Preventing SQL injection

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered.

Parameterized statements

With most development platforms, parameterized statements can be used that work with parameters (sometimes called placeholders or bind variables) instead of embedding user input in the statement. In many cases, the SQL statement is fixed, and each parameter is a scalar, not a table. The user input is then assigned (bound) to a parameter. This is an example using Java and the JDBC API: PreparedStatement prep = conn.prepareStatement(&quot;SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?&quot;); prep.setString(1, prep.setString(2, username); password);

prep.executeQuery(); Enforcement at the database level Currently only the H2 Database Engine supports the ability to enforce query parameterization. However, one drawback is that query by example may not be possible or practical because it's difficult to implement query by example using parametrized queries. Enforcement at the coding level Using object-relational mapping libraries avoids the need to write SQL code. The ORM library in effect will generate parameterized SQL statements from object-oriented code.

[5]

Escaping

A straight-forward, though error-prone, way to prevent injections is to escape characters that have a special meaning in SQL. The manual for an SQL DBMS explains which characters have a special meaning, which allows creating a comprehensive blacklist of characters that need translation. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query: $query = sprintf(&quot;SELECT * FROM Users where UserName='%s' and Password='%s'&quot;, mysql_real_escape_string($Username),

This is error prone because it is easy to forget to escape a given string.

Real-world examples

· On November 1, 2005, a high school student used SQL injection to break into the site of a Taiwanese information security magazine from the Tech Target group and steal customers' information.

[6]

· On January 13, 2006, Russian computer criminals broke into a Rhode Island government web site and allegedly [7] stole credit card data from individuals who have done business online with state agencies. · On March 29, 2006, Susam Pal discovered an SQL injection flaw in an official Indian government tourism site. · On March 2, 2007, Sebastian Bauer discovered an SQL injection flaw in the knorr.de login page. · On June 29, 2007, a computer criminal defaced the Microsoft U.K. website using SQL injection. website The Register quoted a Microsoft spokesperson acknowledging the problem.

[9] [10] [11] [8]

. U.K.

· In January 2008, tens of thousands of PCs were infected by an automated SQL injection attack that exploited a [12] vulnerability in application code that uses Microsoft SQL Server as the database store. · On April 13, 2008, the Sexual and Violent Offender Registry of Oklahoma shut down its website for 'routine maintenance' after being informed that 10,597 Social Security numbers from sex offenders had been downloaded via an SQL injection attack

[13]

· In May 2008, a server farm inside China used automated queries to Google's search engine to identify SQL server [12] [14] websites which were vulnerable to the attack of an automated SQL injection tool. · In 2008,at least April through August, a sweep of attacks began exploiting the SQL injection vulnerabilities of Microsoft's IIS web server and SQL Server database server. The attack doesn't require guessing the name of a table or column, and corrupts all text columns in all tables in a single request.

[15]

A HTML string that references

a malware JavaScript file is appended to each value. When that database value is later displayed to a website visitor, the script attempts several approaches at gaining control over a visitor's system. The number of exploited web pages is estimated at 500,000

[16]

· On August 17, 2009, the United States Justice Department charged an American citizen Albert Gonzalez and two unnamed Russians with the theft of 130 million credit card numbers using an SQL injection attack. In reportedly &quot;the biggest case of identity theft in American history&quot;, the man stole cards from a number of corporate victims after researching their payment processing systems. Among the companies hit were credit card processor Heartland Payment Systems, convenience store chain 7-Eleven, and supermarket chain Hannaford Brothers. and passwords of about 32 million users using an SQL injection attack.

[18] [17]

· In December 2009, an attacker breached a RockYou! plaintext database containing the unencrypted usernames

The SQL/CLI, or Call-Level Interface, extension to the SQL standard is defined in ISO/IEC 9075-3:2003. This extension defines common interfacing components (structures and procedures) that can be used to execute SQL statements from applications written in other programming languages. The SQL/CLI extension is defined in such a way that SQL statements and SQL/CLI procedure calls are treated as separate from the calling application's source code.

See also

· SQL · SQL:2003

SQL/MED

The SQL/MED, or Management of External Data, extension to the SQL standard is defined by ISO/IEC 9075-9:2003. SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This standard can be used in the development of Federated database systems

See also

· SQL · SQL:2003

External links

SQL/MED - A Status Report

[1]

References

[1] http://www.sigmod.org/record/issues/0209/jimmelton.pdf

SQL/OLB

98

SQL/OLB

The SQL/OLB, or Object Language Bindings, extension to the SQL standard is defined by ISO/IEC 9075-10:2003. SQL/OLB defines the syntax and symantics of SQLJ, which is SQL embedded in Java. The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes.

See also

· SQL · SQL:2003

External links

· Short description

[1]

References

[1] http://www.jcc.com/sql.htm

SQL/PSM

SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute (ANSI) as an extension to SQL. It was first adopted in 1996, procedural programmability in addition to the querying commands of SQL.

[1] [2]

and it provides

The SQL/PSM extension is defined by ISO/IEC 9075-4:2003. SQL/PSM standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., &quot;stored procedures&quot;).

The SQL/Schemata, or Information and Definition Schemas, part to the SQL standard is defined by ISO/IEC 9075-11:2008. SQL/Schemata defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier, structure and integrity constraints, security and authorization specifications, features and packages of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items, and the values supported by the DBMS implementations. some of which are mandatory.

The SQL/XML, or XML-Related Specifications, extension to the SQL standard is defined by ISO/IEC 9075-14:2003 (see SQL:2003). SQL/XML specifies SQL-based extensions for using XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database. The SQL/XML specification includes functions to construct XML data. These functions allow the user to construct new XML elements or attributes with values e.g. from relational tables. Other functions such as XMLCONCAT or XMLAGG can be used to combine small XML fragments into larger ones. The list of available construction functions includes: · XMLELEMENT · XMLATTRIBUTES · XMLFOREST · XMLCONCAT · XMLNAMESPACES · XMLCOMMENT · XMLPI · XMLDOCUMENT · XMLAGG · etc. SQL/XML also defines functions which allow the user to embed XQuery expressions in SQL statements. These functions include: · XMLQUERY · XMLTABLE While XMLQUERY returns values of type XML, the function XMLTABLE can take XML data as input and produce a relational table as output. Predicates on XML data, such as search conditions, can be expressed with the

SQL/XML XMLEXISTS predicate, typically in the WHERE clause of a SQL statement. Further information and examples of the SQL/XML functions are provided in the external links below.

SQLPro is a proprietary, visual database management and development tool for multiple databases. SQLPro comes with a three-pane interface and has integrated SQL editor with many useful SQL editing features. SQLPro connects directly (using native drivers) to most popular database servers using one single interface. It supports native data source connection for Oracle, PostgreSQL, MySQL, Microsoft SQL Server, Microsoft Access and SQLite. Provides quick view of the database schema, table schema and results of the query execution. Amenities include color-coding of the SQL, drag-and-drop of objects into the SQL pane to save you from typing their name, retrieval of SQL for things like stored procedures and triggers from the underlying database, and one-click creation of SELECT and INSERT statements. You can save SQL files and print result sets.

Feature Summary

· SQLPro runs on all 32-bit Windows platforms, including Windows 95, 98, NT, 2000, XP, and Vista. · No specific hardware requirements. · Color coding of SQL keywords · Retrieve stored procedures, triggers and functions · No other software or third party drivers to install. SQLPro comes with full set of drivers. · Multiple host/database connections.

Scriptella is an open source ETL (Extract-Transform-Load) and script execution tool written in Java. Its primary focus is simplicity. It doesn't require the user to learn another complex XML-based language to use it, but allows the use of SQL or another scripting language suitable for the data source to perform required transformations. Potential users should be aware that Scriptella does not offer any graphical user interface.

· Support for multiple datasources (or multiple connections to a single database) in a ETL file. · Support for many useful JDBC features , e.g. parameters in SQL including file blobs and JDBC escaping. · Performance. Performance and low memory usage are one of our primary goals. · Support for evaluated expressions and properties (JEXL syntax) · Support for cross-database ETL scripts by using &lt;dialect&gt; elements · Transactional execution · Error handling via &lt;onerror&gt; elements · Conditional scripts/queries execution (similar to Ant if/unless attributes but more powerful) · Easy-to-Use as a standalone tool or Ant task. No deployment/installation required. · Easy-To-Run ETL files directly from Java code.

Scriptella · Built-in adapters for popular databases for a tight integration. Support for any database with JDBC/ODBC compliant driver. · Service Provider Interface (SPI) for interoperability with non-JDBC DataSources and integration with scripting languages. Out of the box support for JSR 223 (Scripting for the Java Platform) compatible languages. · Built-In CSV, TEXT, XML, LDAP, Lucene, Velocity, JEXL and Janino providers. Integration with Java EE, Spring Framework, JMX and JNDI for enterprise ready scripts.

SQL PL stands for Structured Query Language Procedural Language and was developed by IBM as a set of commands that extend the use of SQL in the IBM DB2 (DB2 UDB Version 7) database system. procedural programmability in addition to the querying commands of SQL.

The SQL/JRT, or SQL Routines and Types for the Java Programming Language, extension to the SQL standard is defined by ISO/IEC 9075-13:2003. SQL/JRT specifies the ability to invoke static Java methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL structured user-defined types.

The SQuirreL SQL Client is a database administration tool. It uses JDBC to allow users to explore and interact with databases via a JDBC driver. It provides an editor that offers code completion and syntax highlighting for standard SQL. It also provides a plugin architecture that allows plugin writers to modify much of the application's behavior to provide database-specific functionality or features that are database-independent. As this desktop application is written entirely in Java with Swing UI components, it should run on any platform that has a JVM.

[2]

SQuirreL SQL Client is free as open source software that is distributed under the GNU Lesser General Public License.

Feature Summary

· Object Tree allows for browsing database objects such as catalogs, schemas, tables, triggers, views, sequences, procedures, UDTs, etc. · The SQL Editor is based on the NetBeans core editing components to provide syntax highlighting. It can be used to open, create, save and execute files containing SQL statements. · Supports simultaneous sessions with multiple databases. · It runs on any platform that has a JVM. · Plugin architecture to facilitate database vendor-specific extensions (Information or actions not available using standard JDBC - see SQuirreL SQL Client Plugin API for more details) · Translations for the user interface are available in 8 languages (Bulgarian, Brazilian Portuguese, Chinese, Czech, French, German, Italian, Spanish). [3] · Graph capabilities to create charts showing table relationships. · Bookmarks, which are user-defined code templates. SQuirreL comes with predefined example bookmarks for the most common SQL and DDL statements.

[3]

SQuirreL SQL Client

107

History

The SQuirreL SQL project was developed by a team of Java developers around the world and led by Colin Bell. It has been hosted as a SourceForge project since 2001, and is still being developed today.

Distribution Retails Systems Group (FDRSG) for the interchange of information between software programs. It is a subset of SQL (Structured Query Language) and acts as an interface standard for transferring data between proprietary store systems like DSD (Direct Store Delivery) and POS (Point Of Sale). It was introduced in 1989 in the United States.

In relational databases and flat file databases, a table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A table has a specified number of columns, but can have any number of rows. Each row is identified by the values appearing in a particular column subset which has been identified as a candidate key. Table is another term for relations; although there is the difference in that a table is usually a multi-set (bag) of rows whereas a relation is a set and does not allow duplicates. Besides the actual data rows, tables generally have associated with them some meta-information, such as constraints on the table or on the values within particular columns. The data in a table does not have to be physically stored in the database. Views are also relational tables, but their data are calculated at query time. Another example are nicknames, which represent a pointer to a table in another database.

Comparisons with other data structures

In non-relational systems, hierarchical databases, the distant counterpart of a table is a structured file, representing the rows of a table in each record of the file and each column in a record. Unlike a spreadsheet, the datatype of field is ordinarily defined by the schema describing the table. Some relational systems are less strict about field datatype definitions.

Tables versus relations

In terms of the relational model of databases, a table can be considered a convenient representation of a relation, but the two are not strictly equivalent. For instance, an SQL table can potentially contain duplicate rows, whereas a true relation cannot contain duplicate tuples. Similarly, representation as a table implies a particular ordering to the rows and columns, whereas a relation is explicitly unordered. However, the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified in the SELECT statement that queries the table.

Table (database) An equally valid representations of a relation is as an n-dimensional chart, where n is the number of attributes (a table's columns). For example, a relation with two attributes and three values can be represented as a table with two columns and three rows, or as a two-dimensional graph with three points. The table and graph representations are only equivalent if the ordering of rows is not significant, and the table has no duplicate rows.

109

See also

· Relation (database) · Table (information)

Transact-SQL

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application. Transact-SQL augments SQL with certain additional features: · Control-of-flow language · Local variables · Various support functions for string processing, date processing, mathematics, etc. · Changes to DELETE and UPDATE statements These additional features make Transact-SQL Turing complete.

Flow control

Keywords for flow control in Transact-SQL include BEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE. IF and ELSE allow conditional execution. This batch statement will print &quot;It is the weekend&quot; if the current date is a weekend day, or &quot;It is a weekday&quot; if the current date is a weekday. IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT 'It is the weekend.' ELSE PRINT 'It is a weekday.' BEGIN and END mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use BEGIN and END like this: IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 BEGIN PRINT 'It is the weekend.' PRINT 'Get some rest!' END ELSE BEGIN PRINT 'It is a weekday.' PRINT 'Get to work!' END

Transact-SQL WAITFOR will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time. RETURN is used to immediately return from a stored procedure or function. BREAK ends the enclosing WHILE loop, while CONTINUE causes the next iteration of the loop to execute. An example of a WHILE loop is given below.

110

Changes to DELETE and UPDATE statements

In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included. This example deletes all users who have been flagged with the 'Idle' flag. DELETE users FROM users as u JOIN user_flags as f ON u.id=f.id WHERE f.name = 'Idle'

BULK INSERT

BULK INSERT is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added. Additional details are available on Microsoft's MSDN page

streaming data, including queries that combine those streams with other streaming data or with historical/staged One public example of Truviso's customers using continuous analytics is the dynamic tag cloud

[8]

visualization of blog indexer Technorati.

Truviso is one of the pioneers in the continuous analytics space which seeks to alter how business intelligence is done -- rather than accumulating data first and then running queries on the data set stored in a relational database or a data warehouse, Truviso has always-on queries which process streaming data as it arrives, continuously. For many queries this approach yields results hundreds or thousands of times faster and more efficiently. Truviso has received funding from ONSET Ventures,[9] Diamondhead Ventures,[10] and the UPS Strategic [11] Enterprise Fund.

Technology

Truviso's analytics approach is to have always-on queries analyzing streaming data. This strategy for handling continuously flowing data is different from traditional business intelligence approaches of first accumulating data and then running batch queries for reporting and analysis. Truviso has developed a continuous analytics solution to solving the challenge of high-volume, always-on data analysis. Truviso's solution is based on a scalable PostgreSQL platform capable of concurrent query execution, utilizing standard SQL against live streams of data. Truviso's approach enables analysis of heterogeneous data regardless of whether the data is flowing, staged, or some combination of the two. · Queries are continuous and always running so new results are delivered when the downstream application or use require them · Data does not need to be stored or modified, so the system can keep up with enormous data volumes · Thousands of concurrent queries can be run continuously and simultaneously on a single server · Queries can be run over both real-time and historical data

Truviso · Incoming data can be optionally persisted for replay, backtesting, drill-down, or benchmarking On May 4, 2010, Truviso announced that the company developed a specific application for web analytics called Visitor Insight &amp; Analytics

A User-Defined Function, or UDF, is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment.

BASIC language

In some old implementations of the BASIC programming language, user defined functions are defined using the &quot;DEF FN&quot; syntax. More modern dialects of BASIC are influenced by the structured programming paradigm, where most or all code is written as user defined functions or procedures, and the concept becomes practically redundant.

Databases

In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements. The SQL standard distinguishes between scalar and table functions. A scalar function returns only a single value (or NULL), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns. User-defined functions in SQL are declared using the CREATE FUNCTION statement. For example, a function that converts Celsius to Fahrenheit might be declared like this: CREATE FUNCTION dbo.CtoF(Celsius FLOAT) RETURNS FLOAT RETURN (Celsius * 1.8) + 32 Once created, a user-defined function may be used in expressions in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes SELECT statements, where the function can be used against data stored in tables in the database. Conceptually, the function is evaluated once per row in such usage. For example, assume a table named ELEMENTS, with a row for each known chemical element. The table has a column named BoilingPoint for the boiling point of that element, in Celsius. This query: SELECT Name, CtoF(BoilingPoint) FROM Elements would retrieve the name and the boiling point from each row. It invokes the CtoF user-defined function as declared above in order to convert the value in the column to a value in Fahrenheit. Each user-defined function carries certain properties or characteristics. The SQL standard defines the following properties: · language - defines the programming language in which the user-defined function is implemented; examples are SQL, C, or Java. · parameter style - defines the conventions that are used to pass the function parameters and results between the implementation of the function and the database system (only applicable if language is not SQL). · specific name - a name for the function that is unique within the database. Note that the function name does not have to be unique, considering overloaded functions. Some SQL implementations, such as Microsoft SQL Server, require that function names are unique within a database, and overloaded functions are not allowed. · determinism - specifies whether the function is deterministic or not. The determinism characteristic has an influence on the query optimizer when compiling a SQL statement. · SQL-data access - tells the database management system whether the function contains no SQL statements (NO SQL), contains SQL statements but does not access any tables or views (CONTAINS SQL), reads data from tables or views (READS SQL DATA), or actually modifies data in the database (MODIFIES SQL DATA).

User-defined function User-defined functions should not be confused with stored procedures. Stored procedures allow the user to group a set of SQL commands. A procedure can accept parameters and execute its SQL statements depending on those parameters. A procedure is not an expression and, thus, cannot be used like user-defined functions. Some database management systems allow the creation of user defined functions in languages other than SQL. Microsoft SQL Server, for example, allows the user to use .NET languages including C# for this purpose. DB2 and Oracle support user-defined functions written in C or Java programming languages.

114

SQL Server 2000

There are three types of UDF in Microsoft SQL Server 2000: #Scalar functions. #Inline table-valued functions. #Multistatement table-valued functions. Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types. Inline table-valued functions return the result set of a single SELECT statement. Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements. User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures. Performance Notes: 1. On Microsoft SQL Server 2000 a table-valued function which 'wraps' a View may be much faster than the View itself. The following MyFunction is an example of a 'function-wrapper' which runs faster than the underlying view MyView:

SAS_StudentInstancesID , Label , Value , CMN_PersonsID FROM MyView -- where MyView selects (with joins) the same columns from large table(s) RETURN END

115

2. On Microsoft SQL Server 2005 the result of the same code execution is the opposite: view is executed faster than the 'function-wrapper'. User-defined functions are subroutines made of one or more Transact-SQL statements that can be used to encapsulate code for reuse. It takes zero or more arguments and evaluates a return value. Has both control-flow and DML statements in its body similar to stored procedures. Does not allow changes to any Global Session State, like modifications to database or external resource, such as a file or a network. Does not support output parameter. DEFAULT keyword must be specified to pass the default value of parameter. Errors in UDF cause UDF to abort which, in turn, aborts the statement that invoked the UDF. CREATE FUNCTION CubicVolume -- Input dimensions in centimeters (@CubeLength decimal(4,1), @CubeWidth decimal(4,1) , @CubeHeight decimal(4,1) ) RETURNS decimal(12,3) AS BEGIN RETURN ( @CubeLength * @CubeWidth * END Data type supported in Microsoft SQL Server 2000 Like a temporary table used to store results Mostly used to define temporary variable of type (table) and the return value of a UDF The scope is limited to function, stored procedure, or batch in which it is defined Assignment operation is not allowed between (Table) variables May be used in SELECT, INSERT, UPDATE, and DELETE CREATE FUNCTION to create UDF ALTER FUNCTION to change the characteristics of UDF DROP FUNCTION to remove UDF @CubeHeight )

A varchar or Variable Character Field is a set of character data of indeterminate length. The term varchar specifically refers to a data type of a field (or column) in a database management system. Varchar fields can be of any size up to the limit. The limit differs from types of databases, an Oracle 9i Database has a limit of 4000 bytes, a MySQL Database has a limit of 65,535 bytes (for the entire row) and Microsoft SQL Server 2005 8000 bytes (unless varchar(max) is used, which has a maximum storage capacity of 2,147,483,648 bytes). varchar data types are popular amongst all database management systems (DBMS). This is mostly because, unlike the fixed-size char data-type, varchar does not store any blank characters, reducing the size of a database when the full length of the field is not used, although the length of the used size is stored, adding a small overhead.

In database theory, a view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables (base tables) in a relational database, a view does not form part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a table alters the data shown in subsequent invocations of the view. Views can provide advantages over tables: · Views can represent a subset of the data contained in a table · Views can join and simplify multiple tables into a single virtual table · Views can act as aggregated tables, where the database engine aggregates data (sum, average etc) and presents the calculated results as part of the data · Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table · Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents · Depending on the SQL engine used, views can provide extra security · Views can limit the degree of exposure of a table or tables to the outer world Just as functions (in programming) can provide abstraction, so database users can create abstraction by using views. In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views. Without the use of views the normalization of databases above second normal form would become much more difficult. Views can make it easier to create lossless join decomposition. Just as rows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered - by definition - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. The SQL standard (SQL:2003) does not allow an ORDER BY clause in a subselect in a CREATE VIEW statement, just as it is not allowed in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table - as part of a query statement. Nevertheless, some DBMS (such as Oracle and SQL Server) allow a view to be created with an ORDER BY clause in a subquery, affecting how data is displayed.

Read-only vs. updatable views

Database practitioners can define views as read-only or updatable. If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. INSERT, UPDATE, and DELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation. Some systems support the definition of INSTEAD OF triggers on views. This technique allows the definition of other logic for execution in place of an insert, update, or delete operation on the views. Thus database systems can implement data modifications based on read-only views. However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.

View (database)

118

Advanced view features

Various database management systems have extended the views from read-only subsets of data. The Oracle database introduced the concept of materialized views: pre-executed, non-virtual views commonly used in data warehousing. They give a static snapshot of the data and may include data from remote sources. The accuracy of a materialized view depends on the frequency or trigger mechanisms behind its updates. DB2 provides so-called &quot;materialized query tables&quot; (MQTs) for the same purpose. Microsoft SQL Server introduced in its 2000 version indexed views which only store a separate index from the table, but not the entire data.

Equivalence

A view is equivalent to its source query. When queries are run against views, the query is modified. For example, if there exists a view named Accounts_view with the content as follows: accounts view: ------------SELECT name, money_received, money_sent, (money_received - money_sent) AS balance, address, ... FROM table_customers c JOIN accounts_table a ON a.customerid = c.customer_id then the application could run a simple query such as: Sample query -----------SELECT name, balance FROM accounts_view The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to the optimiser: Preprocessed query: -----------------SELECT name, balance FROM (SELECT name, money_received, money_sent, (money_received - money_sent) AS balance, address, ... FROM table_customers c JOIN accounts_table a ON a.customerid = c.customer_id )

From this point on the optimizer takes the query, removes unnecessary complexity (for example: it is not necessary to read the address, since the parent invocation does not make use of it) and then sends the query to the SQL engine

Windows Management Instrumentation Query Language (WQL) is Microsoft's implementation of the CIM Query Language (CQL), a query language for the Common Information Model standard from the Distributed [1] Management Task Force (DMTF). It is a subset of the standard ANSI SQL with minor semantic changes. A basic WQL query remains fairly understandable for people with basic SQL knowledge. WQL is dedicated to WMI and is designed to perform queries against the CIM repository to retrieve information or get event notifications.

Example

As an example, the following WQL query selects all the drives on a computer that have less than 2 MB of free [2] space: SELECT * FROM Win32_LogicalDisk WHERE FreeSpace &lt; 2097152

Windows Internal Database (codenamed WYukon, sometimes referred to as SQL Server Embedded Edition) is a variant of SQL Server Express 2005 that is included with Windows Server 2008, and is included with other free Microsoft products released after 2007 that require an SQL Server database backend. Windows SharePoint Services 3.0 and Windows Server Update Services 3.0 both include Windows Internal Database, which can be used as an alternative to using a retail edition of SQL Server. WID is a 32-bit application, even as component of Windows Server 2008 64-bit, which installs in the path C:\Program Files (x86)\Microsoft SQL Server. Windows Internal Database is not available as a standalone product for use by end-user applications; Microsoft provides SQL Server Express and Microsoft SQL Server for this purpose. Additionally, it is designed to only be accessible to Windows Services running on the same machine. Several components of Windows Server 2008 use Windows Internal Database for their data storage: Active Directory Rights Management Services, Windows System Resource Manager, UDDI Services, and Windows SharePoint Services. On Windows Server 2003, SharePoint and Windows Server Update Services will install Windows Internal Database and use it as a default data store if a retail SQL Server database instance is not provided. A Knowledge Base article published by Microsoft states that Windows Internal Database does not identify itself as a removable component, and provides instructions how it may be uninstalled by calling Windows Installer directly.

[1]

SQL Server Management Studio Express can be used to connect to an instance of Windows Internal Database using \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query as instance name. But this will only work locally, as Remote Connections cannot be enabled for this edition of SQL Server.

[1] &quot;Windows Internal Database is not listed in the Add or Remove Programs tool and is not removed when you remove Windows SharePoint Services 3.0 from the computer (MSKB920277)&quot; (http://support.microsoft.com/kb/920277). Knowledge Base. Microsoft. . [2] http://go.microsoft.com/fwlink/?LinkId=79600 [3] http://go.microsoft.com/fwlink/?LinkId=71220 [4] http://www.mssqltips.com/tip.asp?tip=1577

XLeratorDB

XLeratorDB is a suite of database function libraries that enable Microsoft SQL Server to perform a wide range of additional (non-native) business intelligence and ad hoc analytics. The libraries, which are embedded and run centrally on the database, include more than 300 individual functions similar to those found in Microsoft Excel spreadsheets. The individual functions are grouped and sold as six separate libraries based on usage: finance, statistics, math, engineering, unit conversions and strings. WestClinTech, the company that developed XLeratorDB, claims it is &quot;the first commercial function package add-in for Microsoft SQL Server.&quot;

[1]

Company history

WestClinTech (LLC), founded by software industry veterans Charles Flock and Joe Stampf in 2008, is located in Irvington, New York, USA. Flock was a co-founder of The Frustum Group, developer of the OPICS enterprise banking and trading platform, which was acquired by London-based Misys, PLC in 1996. generations of OPICS now employed by over 150 leading financial institutions worldwide.

[2]

Stampf joined Frustum

in 1994 and with Flock remained active with the company after acquisition, helping to develop successive

[3]

Following a full year of research, development and testing, WestClinTech introduced and recorded its first [4] [5] commercial sale of XLeratorDB in April 2009. In September 2009, XLeratorDB became available to all Federal agencies through NASA's Strategic Enterprise-Wide Procurement (SEWP-IV) program, a government-wide acquisition contract.

code to be hosted by, and run in, the Microsoft SQL Server environment. SQL CLR relies on the creation, deployment and registration of .NET Framework assemblies that are physically stored in managed code dynamic-link libraries (DLL). The assemblies may contain .NET namespaces, classes, functions, and properties. Because managed code compiles to native code prior to execution, functions using SQL CLR can achieve significant performance increases versus the equivalent functions written in T-SQL in some scenarios.

[8]

XLeratorDB XLeratorDB requires Microsoft SQL Server 2005 or SQL Server 2005 Express editions, or later (compatibility mode 90 or higher). The product installs with PERMISSION_SET=SAFE. SAFE mode, the most restrictive permission set, is accessible by all users. Code executed by an assembly with SAFE permissions cannot access [10] external system resources such as files, the network, the internet, environment variables, or the registry.

[9]

122

Functions

In computer science, a function is a portion of code within a larger program which performs a specific task and is relatively independent of the remaining code. As used in database and spreadsheet applications these functions generally represent mathematical formulas widely used across a variety of fields. While this code may be user-generated, it is also embedded as a pre-written sub-routine in applications. These functions are typically identified by common nomenclature which corresponds to their underlying operations: e.g. IRR identifies the function which calculates Internal Rate of Return on a series of periodic cash flows.

Function uses

As sub-routines functions can be integrated and used in a variety of ways, and in a wide variety of larger, more complicated applications. Within large enterprise applications they may, for example, play an important role in defining business rules or risk management parameters, while remaining virtually undetected by end users. Within database management systems and spreadsheets, however, these kinds of functions also represent discrete sets of tools; they can be accessed directly and utilized on a stand-alone basis, or in more complex, user-defined configurations. In this context, functions can be used for business intelligence and ad hoc analysis of data in fields such as finance, statistics, engineering, math, etc.

Function types

XLeratorDB uses three kinds of functions to perform analytic operations: scalar, aggregate, and a hybrid form which WestClinTech calls Range Queries. Scalar functions take a single value, perform an operation and return a [11] single value. An example of this type of function is LOG, which returns the logarithm of a number to a specified base. Aggregate functions operate on a series of values but return a single, summarizing value. An example of [13] this type of function is AVG, which returns the average of values in a specified group. In XLeratorDB there are some functions which have characteristics of aggregate functions (operating on multiple series of values) but cannot be processed in SQL CLR using single column inputs, such as AVG does. For example, irregular internal rate of return (XIRR), a financial function, operates on a collection of cash flow values from one column, but must also apply variable period lengths from another column and an initial iterative assumption from a third, in order to return a single, summarizing value. WestClinTech documentation notes that Range Queries specify the data to be included in the result set of the function independently of the WHERE clause associated with the T-SQL statement, by incorporating a SELECT statement into the function as a string argument; the function then traps that SELECT statement, executes it internally and processes the result.

MULTINOMIAL, and SERIESSUM. Within the application these functions are identified by a &quot;_q&quot; naming

XLeratorDB

123

Analytic functions

SQL Server functions

Microsoft SQL Server is the #3 selling database management system (DBMS), behind Oracle and IBM. versions of SQL Server have been on the market since 1987

[17] [16]

(While

, XLeratorDB is compatible with only the 2005

[18]

edition and later.) Like all major DBMS, SQL Server performs a variety of data mining operations by returning or arraying data in different views (also known as drill-down). In addition, SQL Server uses Transact-SQL (T-SQL) to execute four major classes of pre-defined functions in native mode.

[19]

Functions operating on the DBMS offer

[20]

several advantages over client layer applications like Excel: they utilize the most up-to-date data available; they can process far larger quantities of data; and, the data is not subject to exporting and transcription errors . SQL Server 2008 includes a total of 58 functions that perform relatively basic aggregation (12), math (23) and string manipulation (23) operations useful for analytics; it includes no native functions that perform more complex operations directly related to finance, statistics or engineering.

[21]

Excel functions

Microsoft Excel, a component of Microsoft Office suite, is one of the most widely used spreadsheet applications on the market today.

[22]

In addition to its inherent utility as a stand-alone desktop application, Excel overlaps and

[23]

complements the functionality of DBMS in several ways: storing and arraying data in rows and columns; performing certain basic tasks such as pivot table and aggregating values; and facilitating sharing, importing and exporting of

[24]

database data. Excel's chief limitation relative to a true database is capacity; Excel 2003 is limited to some 65k rows and 256 columns; Excel 2007 extends this capacity to roughly 1million rows and 16k columns. SQL Server is able to manage over 500k terabytes of memory.

[25]

By comparison,

Excel offers, however, an extensive library of specialized pre-written functions which are useful for performing ad hoc analysis on database data. Excel 2007 includes over 300 of these pre-defined functions, although customized functions can also be created by users, or imported from third party developers as add-ons. Excel functions are grouped by type:

Operating on the client computing layer Excel plays an important role as a business intelligence tool · performs a wide array of complex analytic functions not native to most DBMS software · offers far greater ad hoc reporting and analytic flexibility than most enterprise software · provides a medium for sharing and collaborating because of its ubiquity throughout the enterprise Microsoft reinforces this positioning with Business Intelligence documentation that positions Excel in a clearly [28] pivotal role.

[27]

because it:

XLeratorDB

124

XLeratorDB vs. Excel functions

While operating within the database environment, XLeratorDB functions utilize the same naming conventions and input formats, and in most cases, return the same calculation results as Excel functions. with SQL Server's native capabilities, compares to Excel's function sets as follows:

XSQL combines the power of XML and SQL to provide a language and database independent means to store and retrieve SQL queries and their results.

Description

XSQL is the combination of XML (Extensible Markup Language) and SQL (Structured Query Language) to provide a language and database independent means for storing SQL queries, clauses and query results. XSQL development is still in its infancy and welcomes suggestions for improvement (especially in the form of patches). Currently, the XSQL project has a DTD (Document Type Definition) to define the structure of an XSQL document and researchers are currently working on modifying the XML Generator, DBI Perl module to be able to parse XSQL documents and provide a tree- and event-based API (Application Programming Interface) to their elements. These modifications are being submitted as patches to the modules maintainer, Matt Sergeant. Thus, the source code does not live at this site. It is hoped that XSQL will provide an end-to-end solution for handling SQL in Perl (other languages can be supported if there is interest). Creating XSQL implementations in other languages will allow all databases to support XML without having to alter the database source code in any way. The XSQL implementations can take care of turning XSQL in SQL and turning results into XSQL.