Structured Query Language (SQL)
Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting database information. SQL is an ANSI and ISO standard and is the de facto standard database query language. Various established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in industry and academia, often for enormous, complex databases. In a distributed database system, a program often called the database's "back end" constantly runs 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 (like 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 implementations vary slightly 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 interface (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, further divided into clauses, including SELECT, FROM, WHERE and ORDER BY.
- Data Manipulation Language (DML) is used to add, update or delete data. It 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) manages 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.