Let's Understand the Basics of SQL

SQL stands for Structured Query Language. It is a standard language used to store, manipulate and retrieve data from databases. A database is a logically related collection of data that is easily accessible, manageable, and updatable. A Database Management System (or DBMS) is software for creating and managing databases. It helps users create, update, read, and delete data in the database. Databases are of two types:

Relational Databases are based on the tabular relational model. These are also called SQL databases.

Non - Relational Databases are based on a model other than the tabular one. These are also called NoSQL databases.

The data in relational databases is stored in the form of tables which is a collection of related data entries and consist of columns and rows. SQL is used to manipulate or retrieve such data. Many Relational Database Management Systems use SQL, such as MySQL, PostgreSQL, SQLite3, Microsoft SQL Server, T-SQL, etc. 

The main features of SQL are as follows:

1. SQL is an elementary, easy-to-learn, and easy-to-understand language.
2. SQL retrieves large amounts of data efficiently and quickly.
3. SQL provides flexibility and scalability for Relational Database Management Systems.
4. SQL is an ANSI and ISO standard language. 
5. SQL lets you manage databases without the knowledge of a lot of coding.

Some standard SQL commands are CREATE, SELECT, INSERT, UPDATE, DELETE, DROP, etc. These commands can be classified into five categories based on their nature. These are as follows:

1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5.  TCL – Transaction Control Language

Data Definition Language has a set of SQL commands to define a database's schema. These include CREATE, ALTER, DROP, COMMENT, TRUNCATE, etc. 

Data Query Language consists of the SQL command - SELECT, which helps to retrieve data from the database to perform operations with it.

Data Manipulation Language consists of SQL commands that help manipulate the data in the database. These include INSERT, UPDATE and DELETE commands.

Data Control Language contains SQL commands such as GRANT and REVOKE. These are concerned with the rights and permissions of the database system.  

Transaction Control Language has a set of SQL commands which manage transactions within the database. A database transaction is a sequential group of statements used to perform operations in a database as a single logical unit of work. These include ROLLBACK, COMMIT, SAVEPOINT, AND SET TRANSACTION commands. 

There are various data types in SQL, which are as follows:

1. Character String Data Types such as CHAR, VARCHAR, TEXT, etc.
2. Binary Data Types, which include BINARY, VARBINARY, etc.
3. Exact Numeric Data Types such as INT, BIT, DECIMAL, etc.
4. Approximate Numeric Data Types, which include FLOAT, REAL, etc.
5. Date and Time Data Types consist of DATE, TIME, DATETIME, etc.

SQL Operators

An SQL operator is a reserved word or character used in the WHERE clause of an SQL statement for comparisons and arithmetic operations and to specify conditions.

The different types of SQL Operators are as follows:

1. Arithmetic operators: These operators perform simple arithmetic operations such as addition, multiplication, division, and subtraction. The operators are +, -, *, / etc.
2. Comparison operators: These operators compare two values. The operators are =, >, <, != etc.
3. Logical operators: These operators compare multiple SQL commands. The operators are AND, OR, ANY, etc.

While manipulating a database, many SQL commands are used per the requirement. Every SQL command is typically ended with a semicolon (;). Let's understand this with an example. The below-mentioned are different SQL statements separated by a semicolon (;).

SELECT First_Name, Last_Name

FROM Customers;

DELETE FROM Customers

WHERE Customer_Id > 20;

These are the basic concepts in SQL that should be thoroughly understood.

Image Source: https://www.gallerysystems.com/sql-for-tms-training-hands-on-skills-to-enhance-and-advance-tms-knowledge/