Most Important SQL Commands

Introduction

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

The various DDL commands are CREATE, ALTER, and DROP. Let us learn about each DDL Command one by one with examples. Please go through it if you are unaware of the various types of SQL 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 on them.

There are three types of comments 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

DML is an abbreviation of Data Manipulation Language. It clearly shows its functionality by its name, which means what is going to perform in the database. Manipulation simply indicates that it is going to perform some task i.e: Alter, Delete, Edit the data in the database. 

Let us learn about each DDL Command one by one with examples. Please go through it if you are unaware of the various types of 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;

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

SQL provides us with 2 DCL Commands - GRANT and REVOKE. Let us learn about each DDL Command one by one with examples. Please go through it if you are unaware of the various types of SQL 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

SQL provides us with 3 TCL Commands - COMMIT, ROLLBACK, and SAVEPOINT. 

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.


Similar Articles