Most Important SQL Commands

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

ALTER TABLE table_name 
ADD column_name datatype;

DROP TABLE table_name;

TRUNCATE TABLE table_name;

-- single line comment example
SELECT * FROM employees; 

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

SELECT * FROM /* employees; */ 

SELECT column_name 
FROM table_name;

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

UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;

DELETE FROM table_name
WHERE column_name = column_value;

GRANT privileges ON object TO user;

REVOKE privileges ON object FROM user;

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 been 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 roll back to that point whenever required.

SAVEPOINT SAVEPOINT_NAME;

Other Useful Commands

Here are some other commands of SQL with examples.

AND

And is used to combine 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 the 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 show 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 or 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 return 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 it from the source code.