In this article, we will learn about the important SQL commands with explanation which are commonly used in every project. Let's start with these commands,
DDL Commands
 
CREATE TABLE
Using CREATE TABLE you can create a new table in the database. You can set the table name and column name in the table.
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

SQL
ALTER TABLE

The ALTER TABLE is used to add, delete, or modify columns in a table.

ALTER TABLE table_name
ADD column_name datatype;

SQL
DROP Table

The DROP TABLE is used to drop an existing table in a database.
DROP TABLE table_name;

SQL
TRUNCATE Table

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

TRUNCATE TABLE table_name;

SQL
COMMENT

When we do not want our code to be executed we comment them.

There are three types of comment in SQL,
    Single line comments.
    Multi-line comments
    Inline comments

Single line comments
-- single line comment example
SELECT * FROM employees;

Lua
Multi-line comments
/* multi line comment line 1
line 2 */
SELECT * FROM employees;

SQL
Inline comments
SELECT * FROM /* employees; */

SQL
DML Commands
 
SELECT
SELECT statements are used to fetch data from a database. Every query will begin with SELECT.

SELECT column_name
FROM table_name;

SQL
INSERT
INSERT statements are used to add a new row to a table.

INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);

SQL
UPDATE

UPDATE statements allow you to edit rows in a table.

UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;

SQL
DELETE
DELETE statements are used to remove rows from a table.
DELETE FROM table_name
WHERE column_name = column_value;

SQL
DCL Commands
GRANT
GRANT used to provide access or privileges on the database objects to the users.
GRANT privileges ON object TO user;

SQL
REVOKE
REVOKE command removes user access rights or privileges to the database objects.
REVOKE privileges ON object FROM user;

SQL
TCL Commands
COMMIT

COMMIT is used to save changes by a transaction to the database.

COMMIT;

SQL
ROLLBACK
The ROLLBACK command is used to undo transactions that have not saved to the database. The command is only be used to undo changes since the last COMMIT.
ROLLBACK;

SQL
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction to a point so that you can rollback to that point whenever required.

SAVEPOINT SAVEPOINT_NAME;

SQL
Other Useful Commands
 
AND

And is used to combines two conditions. Both conditions must be true to display the record.

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

SQL
AS
With the help of AS you can rename a column or table using an alias.

SELECT column_name AS 'Alias_Example'
FROM table_name;

SQL
AVG()
AVG() function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name;

SQL
BETWEEN
BETWEEN operator selects values (values can be numbers, text, or dates) within a given range.

SELECT column1
FROM table_name
WHERE column1 BETWEEN value1 AND value2;

SQL
CASE
In SQL we are using CASE like an if-then-else statement.

SELECT column_name,
  CASE
    WHEN condition THEN 'Result1'
    WHEN condition THEN 'Result2'
    ELSE 'Result3'
  END
FROM table_name;

SQL
COUNT()
The COUNT() function returns the number of rows in a column.
SELECT COUNT(column_name)
FROM table_name;

SQL
GROUP BY
The GROUP BY statement groups rows that shows the identical data into groups.

SELECT column_name1, column_name2
FROM table_name
GROUP BY column_name1;

SQL
HAVING
WHERE keyword cannot be used with aggregate functions that's why the HAVING clause was added to SQL.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;

SQL
INNER JOIN
An inner join will combine rows that have matching values in both tables.
SELECT column_name
FROM table1
INNER JOIN table2
On table1.column_name = table2.column_name;

SQL
IS NULL / IS NOT NULL
We used IS NULL and IS NOT NULL with the WHERE clause to test the value empty or not.

IS NULL Syntax,

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

SQL
IS NOT NULL Syntax,

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

SQL
LIKE
LIKE is a special operator used to search for a specific pattern in a column with the WHERE clause.

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

SQL
MAX()

MAX() is a function that returns the largest value in that column.

SELECT MAX(column_name)
FROM table_name;

SQL
MIN()

MIN() is a function that returns the smallest value in that column.

SELECT MIN(column_name)
FROM table_name;

SQL
OR
The OR operator displays the result where one of two conditions is true.

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

SQL
ORDER BY
ORDER BY is used to sort the result in ascending or descending order. By default, it sorts the records in ascending order for descending order, we will use the DESC keyword.
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;

SQL
ROUND()

The ROUND function returns a number rounded to a certain number of decimal places.
SELECT ROUND(column_name, integer)
FROM table_name;

SQL
SELECT DISTINCT

The SELECT DISTINCT statement is used to returns unique values in the specified column(s).
SELECT DISTINCT column_name
FROM table_name;

SQL
SUM

The SUM() function returns the total sum of a column.

SELECT SUM(column_name)
FROM table_name;

SQL
WHERE

WHERE clause is used to filter records.

SELECT column1, column2
FROM table_name
WHERE condition;

HostForLIFEASP.NET SQL Server 2019 Hosting