Most Important SQL Commands

In this article, we will learn about the important SQL commands which are commonly used in every project, with explanations. 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,
   ....
);

ALTER TABLE

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

ALTER TABLE table_name 
ADD column_name datatype;

DROP Table

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

DROP TABLE table_name;

TRUNCATE Table

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

TRUNCATE TABLE table_name;

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; 

Multi-line comments

/* multi line comment line 1
line 2 */
SELECT * FROM employees; 

Inline comments

SELECT * FROM /* employees; */ 

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;

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);

UPDATE

UPDATE statements allow you to edit rows in a table.

UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;

DELETE

DELETE statements are used to remove rows from a table.

DELETE FROM table_name
WHERE column_name = column_value;

DCL Commands
 

GRANT

GRANT is used to provide access or privileges on the database objects to the users.

GRANT privileges ON object TO user;

REVOKE

REVOKE command removes user access rights or privileges to the database objects.

REVOKE privileges ON object FROM user;

TCL Commands
 

COMMIT

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

COMMIT;

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;

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;

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;

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;

AVG()

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

SELECT AVG(column_name)
FROM table_name;

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;

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;

COUNT()

The COUNT() function returns the number of rows in a column.

SELECT COUNT(column_name)
FROM table_name;

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;

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;

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;

IS NULL / IS NOT NULL

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

IS NULL Syntax,

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax,

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

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;

MAX()

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

SELECT MAX(column_name)
FROM table_name;

MIN()

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

SELECT MIN(column_name)
FROM table_name;

OR

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

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

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;

ROUND()

The ROUND function returns a number rounded to a certain number of decimal places.

SELECT ROUND(column_name, integer)
FROM table_name;

SELECT DISTINCT

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

SELECT DISTINCT column_name
FROM table_name;

SUM

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

SELECT SUM(column_name)
FROM table_name;

WHERE

WHERE clause is used to filter records.

SELECT column1, column2
FROM table_name
WHERE condition;

Conclusion

In this article, we learned about the Most Important SQL Commands. If you want to download the cheatsheet of all these commands please download from the source code.