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.

CommandsDescription
DDLData Definition Language
DMLData Manipulation Language
TCLTransaction Control Language
DCLData Control Language

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:

StatementDescription
CREATEIt will make a new object like procedure, table, function, view etc. in the database.
ALTERIt will modify the structure of database table in SQL.
DROPIt will delete the database Objects.
RENAMEIt will rename the database Objects such as sequence, view, table, private synonym)
TRUNCATEIt removes all records of a table.

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:

StatementDescription
SELECTIt will Retrieve rows/columns from a table.
INSERTIt will insert new data to a table.
UPDATEIt will update existing records of table.
DELETEIt will delete existing records from table.
MERGEIt will INSERT new rows or UPDATE existing rows in a table based on the specified conditions.
LOCK TABLEIt will lock one or more tables in the specified mode. Based on lock applied table access denied or only real only access given to another user.

3. TCL:-( Transaction Control Language)

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

StatementDescription
COMMITIn this the transaction changes are permanently saved to the database by it.
ROLLBACKIt restores the original state of the database since the last comment.
SAVEPOINTIt Creates a SAVEPOINT to be later used by ROLLBACK Command to undo changes up to that point.
SET TRANSACTIONSet the transaction properties such as READ WRITE or READ ONLY access.

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.

StatementDescription
GRANTIt will give privileges to the user for accessing data.
REVOKEIt will take back given privileges from the user.
COMMENTIt will specify comments on Database tables and columns.
ANALYZEIt will collect statistics of table, index, partition, cluster, etc.
AUDITIt will track occurrence of specific or all SQL statements or operations on some specific Schema object.

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.

StatementDescription
ALTER SESSIONModify database parameters for current session.
SET ROLETo enable or disable roles for current session.

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.


Next TopicSQL Syntax




Latest Courses