What is SQL

SQL stands for Structured Query Language. Pronunciation for the term SQL is ess-Kew-all or sequel.

SQL is used to manipulate the underlying relational databases that are queried by SQL like Oracle, MySQL, SQL Server, SQLite, PostgreSQL, etc. Initially, SQL was developed in the 1970s, but database administrators do not regularly use SQL. Now a days, it is also used by software developers and data analysts.

SQL is an ISO, i.e., International Organization for Standardization, and ANSI, i.e., American National Standards Institute standard language. However, SQL is not supported by all databases.

Following are the various parts included in SQL syntax:

1. Keywords:

Keywords are reserved or non-reserved words. SQL-reserved keywords are INTO, UPDATE, SELECT, DELETE, DROP, DESC, and ASC.

2. Identifiers:

The database objects, like function name, schema name, table name, etc., are named Identifiers.

Clauses:

The components of queries and SQL statements such as WHERE, GROUP BY, HAVING, and ORDER BY are formed by clauses.

3. Expression:

Either columns or scalar values and rows of data in SQL produced by EXPRESSION.

4. Boolean Conditions:

The boolean value TRUE or FALSE is the result of the Conditions, also called expressions. the effect of statements or queries limited by this condition.

5. Queries:

The data based on specific criteria is retrieved by the SQL statements. Queries are Statements that start with the SELECT clause because they retrieve data from the underlying database.

6. Statements:

SQL statements may persistently affect schema and data or control transactions, program flow, connections, sessions, or diagnostics. SQL statements are such as INSERT, UPDATE, DROP, and DELETE statements since they modify the database structure or data.

SQL standard and proprietary extensions

In 1986, the American National Standards Institute adopted an official SQL standard, and in 1987, the international organization for standardization adopted the standard. After few years, new versions of the SQL are published, and the most recent version of SQL is version 2016.

International electrotechnical commission and ISO combined developed the SQL standard of ISO/IEC 9075. The ISO standard version is referred to the standard way by using the standard organizations, ISO/IEC, followed by the ISO standard number and a colon with the publication year. The most recent ISO standard for SQL is ISO/IEC 9075:2016

Open-source and proprietary RDBMS built around SQL is ready for use by the organization. The products given are included in an SQL-compliant database server. These are such as oracle database, Microsoft SQL Server, IBM Db2, SAP HANA, Oracle MySQL, SAP adaptive server, open source PostgreSQL, and Oracle MySQL.

For procedural programming and for some other functions, there are some SQL versions that include the proprietary extension to the standard language. For instance, transact-SQL is a set of extensions which is offered by Microsoft, and on the other hand, the procedural language is the extended version of the standard of Oracle.

Proprietary extensions are offered by commercial vendors to differentiate their product offerings by giving additional functions and features to the consumers. Therefore the vendors offer some different variants of extended SQL, which are only partially compatible with one another.

Basic use of SQL:

1. It modifies the database table and index structures.
2. It adds, updates, and deletes the rows of data.
3. Subsets of information from within the relational database management system are retrieved by it. The information from that can be used for the analytical application, transaction processing, and other applications which require communication with a relational database.

Relational vs. nonrelational databases

Relational or nonrelational databases are used in the SQL query language, but that gives us advantages for relational databases.

Classification of SQL:

The structured query language is divided into the following categories. The important point is that the statement in the following table may vary in different databases.

The structured query language is divided into the following categories. The important point is that the statement in the following table may vary in different databases.

1. DDL: (Data Definition Language)

The data structure in the database takes the help of Data Definition Language (DDL) statements to define, such as tables, procedures, functions, views, etc.

The following table lists DDL statements:

2. DML: (Data Manipulation Language)

Data management within a database object is done by the Data manipulation language( DML ) statement. Querying and manipulation are allowed by it for the existing database schema objects.

The following table lists DML statements:

3. TCL:-( Transaction Control Language)

The data changes done by executing the DML statements are finalized by the transaction control language (TCL) statements.

4. DCL- Data Control Language

Data Control Language (DCL) statements are used to enforce database security by giving privileges to different users to access the database.

5. SCL- Session Control Language

Session Control Language (SCL) statements are used to manage changes made to the database by executing DML statements. The SCL commands vary based on the database. The following table lists the SCL commands for the Oracle database.

Rules for writing SQL statement:

SQL syntax is the set of rules for how SQL statements are written and formatted. It is similar to other programming languages.

Some components of SQL syntax include the following:

SQL statements start with a SQL command and end with a semicolon (;), for example:

This SELECT statement extracts all of the contents of a table called consumers.

SQL statements are case-insensitive, meaning that they can be written using lowercase, uppercase, or a combination. However, it is customary to write out SQL keywords, commands or control operators in all-caps and table/column names in lowercase.

Words in the statement can be treated as case-sensitive using quotes, so the following two statements produce identical results.

These two statements are different:

SQL statements are terminated only by the semicolon, meaning that more complex statements can be rendered across multiple lines, like this one:

This command selects the contents of the name, telephone, and age columns from the table consumers.

SQL statements can incorporate program flow controls, meaning that a statement can incorporate table and row selection.

For example, the following command selects the name, telephone number, and birthdate for all consumers whose age is over 27:

Most SQL implementations include support for issuing statements at the command line, through a graphical user interface, by using SQL programs, or through application programming interfaces to access SQL databases using other programming languages.

Some Commonly used SQL Commands with Examples

Most SQL commands are used with operators to modify or reduce the scope of data operated on by the statement. Some most commonly used SQL commands with examples are mentioned below:

1. SQL SELECT

The SELECT command is used to get some or all data in a table.

SELECT can be used with operators to narrow down the amount of data selected:

A publisher could use this example to select the title, writer, and publication date columns from a table named catalog.

2. SQL CREATE

The CREATE command is used to create a new SQL database or SQL table. Most versions of SQL create a new database by creating a new directory, in which tables and other database objects are stored as files.

The following CREATE DATABASE statement creates a new SQL database named Human_Res:

The CREATE TABLE command is used to create a table in SQL. The following statement creates a table named Workers that has three columns: employee_ID, last_name, and first_name, with the first column storing integer (int) data and the other columns storing variable character data of type varchar and a maximum of 255 characters.

3. SQL DELETE

The DELETE command removes rows from a named table. In this example, all records of workers with the last name Swain are deleted:

This statement returns the number of rows deleted when it finishes running.

4. SQL INSERT INTO:

The INSERT INTO command is used to add records to a database table. The following statement adds a new record to the Employee's table:

5. SQL UPDATE

The UPDATE command is used to make changes to rows or records in a specific table. For example, the following statement updates all records that include a last_name value of Swain by changing the name to Sahoo:

SQL statements can use loops, variables, and other programming language components to update records based on different criteria.