Standard SQL Grammars & Syntaxes
Tags: apacheage, postgressql, sql, ubuntu
Published: 2023-08-04 13:31:25

SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

**Why SQL?**
SQL is very popular because it offers the following:

- It allows users to access data in the relational database 
     management systems.
- Users can describe and define the data in a database and 
   manipulate it.
- It allows to embed within other languages using SQL modules, 
     libraries & pre-compilers.
- Users can create and drop databases and tables.
- Users can create view, stored procedure, functions in a 
     database.
- It allows users to set permissions on tables, procedures and 
    views.

## **SQL Commands & Syntax**

SQL is a programming language designed for accessing, modifying and extracting information from relational databases. As a programming language, SQL has commands and a syntax for issuing those commands. These commands can be classified into the following groups based on their nature:

## **DDL - Data Definition Language**
The DML commands are used for interacting with data within the database tables.
**CREATE:** Creates a new table, a view of a table, or other object in the database.
**ALTER:** Modifies an existing database object, such as a table.
**DROP:** Deletes an entire table, a view of a table or other objects in the database.

## **DML - Data Manipulation Language**
The DDL commands are responsible for defining and managing the database schema.
**SELECT:** Retrieves certain records from one or more tables.
**INSERT:** Creates a record.
**UPDATE:** Modifies records.
**DELETE:** Deletes records.

## **DCL - Data Control Language**
DCL commands manage database security, granting or revoking user access privileges. 
**GRANT:** Gives a privilege to user.
**REVOKE:** Takes back privileges granted from user.

## **Common SQL Commands With Examples:**

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

```
SELECT title, author, pub_date
FROM catalog
WHERE pub_date = 2021;
```
**SQL CREATE:** The CREATE command is used to create a new SQL database or SQL table.
The following CREATE DATABASE statement creates a new SQL database named my_table:

```
CREATE DATABASE my_table;
```
**SQL DELETE:** The DELETE command removes rows from a named table. 
```
DELETE FROM Employees WHERE last_name='Doe';
```
**SQL INSERT INTO:** The INSERT INTO command is used to add records into a database table.
```
INSERT INTO Employees (
    last_name,
    first_name
)
VALUES (
    'Jhon',
    'Doe'
);
```
**SQL UPDATE:** The UPDATE command is used to make changes to rows or records in a specific table.
```
UPDATE Employees
SET last_name = 'edison',
WHERE last_name = 'jhon';
```
## **Advanced SQL Commands:**
**JOIN:** The JOIN clause is used to combine rows from two or more tables based on a related column between them. Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

**GROUP BY:** The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used in conjunction with aggregate functions like SUM, COUNT, AVG, etc.

**ORDER BY:** The ORDER BY clause is used to sort the query result based on one or more columns, either in ascending or descending order.

**Subqueries:** A subquery is a query nested within another query. Subqueries can be used in various clauses like WHERE, FROM, and SELECT.

Suggested links: https://forcedotcom.github.io/phoenix/

Ending Note:
In this article, I tried to explained the SQL definition. If you are going to work with database its good to learn the standard SQL commands and grammar to enhance the skills.
However, SQL is a vast subject, and continuous learning and practice will further enhance your understanding and expertise.