Online SQL Compiler

What is SQL?

SQL stands for Structured Query Language. It is a domain-specific language which is used to manage your databases. It can help you with many tasks like storing, finding, updating, and deleting data very easily. You can work with many databases using SQL like MySQL, PostgreSQL, SQL Server, and Oracle.

SQL Syntax

Syntax plays an important role in SQL. You must know all the important syntaxes and commands to write queries without error. Here are some important syntaxes and commands everybody should know to learn SQL:

• Commands in SQL
  • DQL Command
  • DDL Command
  • DML Command
• Indexes in SQL
• Views in SQL
• Triggers in SQL
• Stored Procedures in SQL
• Joins in SQL

Commands in SQL

Commands are used to perform different actions on the database. They are mainly divided into 3 categories based on their functionalities:

1. DQL command

DQL stands for Data Query Language. This command is used to retrieve data from the database.

• SELECT: It is a DQL command which is used to fetch data from a table.

Syntax:

SELECT column1, column2, ..

FROM table_name

WHERE condition;

Example: In this example, we want the name of the student whose age is greater than 20, where ‘Students’ is the table and ‘Name’ is the column.

SELECT Name, Age FROM Students WHERE Age > 20;

2. DDL command

DDL stands for Data Definition Language. It is used to structure the database like creating a new table, modifying an existing table, or deleting a table.

• CREATE: This command is used to create a new table.

Syntax:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    ...);

• ALTER: This command is used to modify an existing table. You can add, modify, or delete a column from the table.

Syntax:

ALTER TABLE table_name ADD column_name datatype;

• DROP: This command is used to delete a table or database.

Syntax:

DROP TABLE table_name;

• TRUNCATE: This command will delete all the data from the table but will keep the structure of the table.

Syntax:

TRUNCATE table table_name;

Note:

Difference between DELETE and TRUNCATE:

• DELETE removes the rows one by one and it can have a WHERE condition.
• TRUNCATE is faster but it can not have a WHERE condition.

3. DML command

DML stands for Data Manipulation Language. It is used to modify the database like adding new data, changing the existing data, or removing the data from a table.

• INSERT: This command is used to insert data in the table.

Syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• UPDATE: This command is used to change the existing data in a table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2, ..

WHERE condition;

• DELETE: This command is used to delete the data of a table

Syntax:

DELETE FROM table_name WHERE condition;

Indexes in SQL

Index is a really important concept in SQL which is used to increase the speed of retrieval of data. Indexes can help you with many things like finding data fast without scanning the whole table, reducing the query time when you are working on large tables, and helping with sorting and filtering the data.

For example, if you want to search for an employee by ID in a 1 million rows table, it can take too much time without an index. Adding an index can greatly increase the performance:

CREATE INDEX idx_employee_id ON Employees(id);

SELECT * FROM Employees WHERE id = 1001;

Syntax:

• To create an index:

CREATE INDEX index_name ON table_name (column_name);

• To create a unique index:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

• To remove an index from a table:

DROP INDEX index_name ON table_name;

Views in SQL

A view in SQL is like a saved query which you can use multiple times whenever you need. It doesn’t actually store data but it will show the data from other tables when you run it. It is like a virtual table. You can use views in many different ways like when you have a complicated query, you can save it as a view and run it later, or if you want to show some specific data, you can hide the data you don’t want to show using views.

Syntax:

• To create a view:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

• To call a view:

SELECT * FROM View_name;

• To delete a view:

DROP VIEW view_name;

Triggers in SQL

A trigger is a set of commands that automatically run when you perform an action on the database like inserting, updating, or deleting data. There are 2 commonly used triggers in SQL:

1. BEFORE trigger: It runs before an action happens.
2. AFTER trigger: It runs after an action happens.

Syntax:

• To create a trigger:

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW [trigger_order] trigger_body

/* where

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } */

• To delete a trigger:

DROP TRIGGER trigger_name;

Stored Procedures in SQL

A stored procedure in SQL is a saved SQL script that you can run whenever you need. It is like a function that can store multiple SQL statements which makes it good for automating tasks.

Syntax:

• To create a stored procedure:

CREATE PROCEDURE procedure_name (parameters)

BEGIN

    /*SQL statements here*/

END;

• To call a stored procedure:

CALL procedure_name;

• To delete a stored procedure:

DROP PROCEDURE procedure_name;

Joins in SQL

A JOIN is used to combine two or more tables when there are related columns available in all tables. It helps you retrieve the data easily from multiple tables. There are 4 main joins in SQL:

1. INNER JOIN: It will give you the matching data available in both tables.

Syntax:

SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;

2. LEFT JOIN: It will give you all the data from the left table and the matching data from the right table.

Syntax:

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

3. RIGHT JOIN: It will give you all the data from the right table and the matching data from the left table.

Syntax:

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

4. CROSS JOIN: It combines every row of a table with every row of another table. It is also known as a Cartesian Product join.

Syntax:

SELECT select_list from TABLE1 CROSS JOIN TABLE2;

Some Advanced SQL Features

There are some advanced features of SQL that no one talks about. Let’s see them in detail:

1. EXPLAIN Query Optimization

EXPLAIN is used to check query performance by showing the execution plan of the SQL query. It will give you the details of how the query is executed like which indexes are used, the order to scan tables, and the approx. cost.

Syntax:

EXPLAIN SELECT * FROM table_name WHERE condition;

Example: We have a table named “Employees” and we want to know how the query will perform to find out the employees who are older than 30.

EXPLAIN SELECT * FROM Employees WHERE age > 30;

Expected Output:

CREATE PROCEDURE UpdateSalary(IN emp_id INT, IN new_salary DECIMAL)

BEGIN

    UPDATE Employees SET salary = new_salary WHERE id = emp_id;

END;

CALL UpdateSalary(1, 5000);

CREATE FUNCTION GetSalary(emp_id INT)

RETURNS DECIMAL

BEGIN

    DECLARE salary DECIMAL;

    SELECT salary INTO salary FROM Employees WHERE id = emp_id;

    RETURN salary;

END;

SELECT name, GetSalary(id) FROM Employees WHERE id = 1;