Structured Query Language (SQL)
Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases. SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex databases. In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns result sets to the client program.
Following are some interesting facts about SQL:
- SQL is case insensitive. But it is a recommended practice to use keywords (like SELECT, UPDATE, CREATE, etc) in capital letters and use user defined things (liked table name, column name, etc) in small letters.
- can write comments in SQL using “–” (double hyphen) at the beginning of any line.
- SQL is the programming language for relational databases (explained below) like MySQL, Oracle, Sybase, SQL Server, Postgre, etc. Other non-relational databases (also called NoSQL) databases like MongoDB, DynamoDB, etc do not use SQL
- Although there is an ISO standard for SQL, most of the implementations slightly vary in syntax. So we may encounter queries that work in SQL Server but do not work in MySQL.
First developed in the early 1970s at IBM by Raymond Boyce and Donald Chamberlin, SQL was commercially released by Relational Software Inc. (now known as Oracle Corporation) in 1979. The current standard SQL version is voluntary, vendor-compliant and monitored by the American National Standards Institute (ANSI). Most major vendors also have proprietary versions that are incorporated and built on ANSI SQL, e.g., SQL*Plus (Oracle), and Transact-SQL (T-SQL) (Microsoft). One of the most fundamental DBA rites of passage is learning SQL, which begins with writing the first SELECT statement or SQL script without a graphical user interfaces (GUI). Increasingly, relational databases use GUIs for easier database management, and queries can now be simplified with graphical tools, e.g., drag-and-drop wizards. However, learning SQL is imperative because such tools are never as powerful as SQL. SQL code is divided into four main categories:
- Queries are performed using the ubiquitous yet familiar SELECT statement, which is further divided into clauses, including SELECT, FROM, WHERE and ORDER BY.
- Data Manipulation Language (DML) is used to add, update or delete data and is actually a SELECT statement subset and is comprised of the INSERT, DELETE and UPDATE statements, as well as control statements, e.g., BEGIN TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK.
- Data Definition Language (DDL) is used for managing tables and index structures. Examples of DDL statements include CREATE, ALTER, TRUNCATE and DROP.
- Data Control Language (DCL) is used to assign and revoke database rights and permissions. Its main statements are GRANT and REVOKE.