Structured Query Language (SQL)

DatabaseRDBMSComputer Science

SQL (Structured Query Language) is a specialized programming language which is standardized to be used for managing relational databases and performing various operations on the data. There are various uses of SQL which includes modifying database table and index structures; adding, updating and deleting rows of data; and retrieving various subsets of information from a database for transaction processing and analytics applications. There are specialized queries and operations which operates in the form of commands and commonly known as SQL statements like select, add, insert, update, delete, create, alter and truncate.

SQL became standard programming language for relational databases after they emerged in the late 1970s and early 1980s. Commonly known as SQL databases, relational systems comprise a set of tables which contains data in form of rows and columns, where each column in a table corresponds to a category of data for example, customer name or address & each row contains a data value for the intersecting column.

SQL is also a domain-specific language used for programming and designing in data held in a relational database management system (RDBMS). It is particularly useful in handling structured data where there are relations between different entities/variables of the data.

SQL is originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements, commonly known as a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. Also, SQL is described as a declarative language (4GL), because it also includes procedural elements.

Applications Of SQL

There are various applications of SQL −

  • 1. Data Integration Scripts - The main application of SQL is to write data integration scripts by the database administrators and developers.
  • 2. Analytical Queries - The data analysts use structured query language for setting and running analytical queries on a regular basis.
  • 3. Retrieve Information - Another popular application of this language is to retrieve the subsets of information within a database for analytics applications and transaction processing. The most commonly used SQL elements are select, insert, update, add, delete, create, truncate and alter.
  • 4. Other Important Applications - The SQL is used for modification of the index structures and database table. Additionally, the users can add, update and delete the rows of the data by using this language.

SQL Standard and proprietary extensions

An official SQL standard was adopted by the American National Standards Institute (ANSI) in 1986 and then by the International Organization for Standardization (ISO) in 1987. SQL is regularly used by database administrators, as well as by developers writing data integration scripts and data analysts looking to set up and run analytical queries.

Both proprietary and open source relational database management systems built around SQL are available for use by organizations. They include Microsoft SQL Server, Oracle Database, IBM DB2, SAP HANA, SAP Adaptive Server, MySQL (now owned by Oracle) and PostgreSQL.

However, many of these database products support SQL with proprietary extensions to the standard language for procedural programming and other functions. For example, Microsoft offers a set of extensions called Transact-SQL (T-SQL), while Oracle's extended version of the standard is PL/SQL. As a result, the different variants of SQL offered by vendors aren't fully compatible with one another.

SQL Commands and Syntax

SQL commands are divided into several different types, like data manipulation language (DML) and data definition language (DDL) statements, transaction controls, and security measures.

  • The DML is used to retrieve and manipulate data.
  • The DDL statements are for defining and modifying database structures.
  • The transaction controls help manage transaction processing, ensuring that transactions are either completed or rolled back if errors or problems occur.
  • The security statements are used to control database access as well as to create user roles and permissions

The SQL language is subdivided into several language elements, including −

  • Clauses, which are constituent components of statements and queries.
  • Expressions, which can have either scalar values, or tables consisting of columns and rows of data
  • Predicates used to specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values (true/false) and are used to limit the effects of statements and queries, or to change program flow.
  • Queries used to retrieve the data based on specific criteria.
  • Statements, which may have a persistent effect on schema and data, or may control transactions, program flow, connections, sessions, or diagnostics.
  • SQL statements also include the semicolon (";") statement terminator.
  • Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

Interoperability and Standardization

SQL implementations are incompatible between vendors and do not follow all the standards. In particular date and time syntax, string concatenation, NULLs, and comparison case sensitivity vary from vendor to vendor. As a result, SQL code can rarely be ported between database systems without modifications.

There are several reasons for portability issues between database systems −

  • The complexity and size of the SQL standard mean that most implementers 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 newer version of the SQL standard conflicts with the prior behavior of the vendor's database, the vendor may be unwilling to break backward compatibility.
  • There is a little commercial incentive for vendors to make it easier for users to change database suppliers (see vendor lock-in).
  • Users evaluating database software tend to place other factors such as performance higher in their priorities than standards conformance.

Advantages of SQL

  • SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.
  • SQL is used to view the data without storing the data in the object.
  • SQL joins two or more tables and shows it as one object to the user.
  • SQL databases use long-established standard, which is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear standard.
  • Using standard SQL it is easier to manage database systems without having to write a substantial amount of code.
  • SQL restricts the access of a table so that nobody can insert the rows into the table.
  • Previously SQL databases were synonymous with a relational database. With the emergence of ObjectOriented DBMS, object storage capabilities are extended to relational databases.

Disadvantages of SQL

  • Interfacing an SQL database is more complex than adding a few lines of code.
  • When the table is dropped view becomes inactive. It depends on the table objects.
  • Although SQL databases conform to ANSI & ISO standards, some databases go for proprietary extensions to standard SQL to ensure vendor lock-in.
  • It is an object so it occupies space.
Published on 23-Jul-2018 14:10:14