List of SQL Commands

SQL stands for Structured Query Language. It is a standardized language for accessing and manipulating the data in the relational databases. It involves querying the databases using SQL statements which involve elements of SQL like commands, clauses, constraints, functions, operators, procedures, etc. Among these elements, commands play a significant role in the language because they are the instructions that enable us to communicate with the database in order to perform certain operations. Here’s an appendix of commonly used commands in SQL.

SQL COMMANDS

AND

SELECT column_1, column_2…column_N from table_name where Condition_1 AND Condition_2 AND….Condition_N ;

AND is an operator which is commonly used in SQL commands to filter the records on the basis of ANDing conditions (where all the conditions have to be true).

ALTER TABLE

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE command allows us to modify the structure of the table. It enables us to add columns to the table after the table is already created.

AS

SELECT column_name AS ‘alias_name’ from table_name;

AS keyword is used to give alias names to a column or a table. Using the above syntax, we can give alias names to a column. Also, we can rename a table using the below syntax.

SELECT column_name(s) from table_name AS ‘alias_name’ ;

AVG()

SELECT AVG(column_name) FROM table_name WHERE condition;

AVG() is an aggregate function which returns the average value of a numeric column given as an argument to the function

BACKUP DATABASE

BACKUP DATABASE database_name TO DISK = ‘filepath’ ;

BACKUP DATABASE command is used to back up the entire database to the path that has been mentioned in the query.

BETWEEN

SELECT column_name(s) from table_name WHERE column_name BETWEEN value_1 and value_2;

BETWEEN is an operator that is used to select a range of values from the table.

COUNT()

SELECT COUNT(column_name) FROM table_name WHERE condition;

COUNT() is an aggregate function that takes a column name as an argument and returns the number of rows that satisfy the specified criteria.

CREATE TABLE

CREATE TABLE table_name (column_1 datatype, column_2 datatype….column_N datatype);

CREATE TABLE command is used to create tables in databases. It allows us to specify the column names and their types inside the brackets.

DELETE

DELETE from table_name ;

DELETE command is used to remove records from the table. It will remove all the records unless the where clause is added and after that, the deletion is restricted to a particular row.

DELETE from table_name where column_name = value;

DISTINCT

SELECT DISTINCT column_1, coumn_2…. from table_name;

DISTINCT command is used to return different values from the table. It is possible that a table may contain duplicate values, so to return only distinct values we use the DISTINCT command.

DROP TABLE

DROP TABLE table_name;

DROP TABLE command is used to delete the entire table. When we use this command, the entire information is lost. It can even be used to drop a database.

DROP DATABASE database_name;

GROUP BY

SELECT column_1, column_2….column_N from table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s) ;

GROUP BY command is used with aggregate functions to group the records on the basis of one or more columns.

HAVING

SELECT column_1, column_2….column_N from table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s) ;

HAVING clause is used to return rows where aggregate fulfills the specified conditions. The HAVING clause was designed because WHERE clause could not be used with aggregate functions.

INSERT

INSERT INTO Table_name ( column_1, column_2….column_N) VALUES ( value_1, value_2….value_N);

INSERT command is used to insert records into the table after the table is already created. We cannot use a WHERE clause in the statement which has INSERT command. The values inside the second bracket have to be in the same order as previously specified in the first bracket which contains the column names.

IN

SELECT column_name(s) from table_name WHERE column_name IN (value_1, value_2…..value_N);

IN is an operator that returns the rows when either of the values specified in the WHERE clause are met. This operator is used for multiple OR conditions. This allows us to specify multiple values in the WHERE clause.

JOIN

SELECT column_name(s) from table_1 JOIN table_2 condition;

JOIN command is used to join the related data from two or more tables. There are many types of JOIN that work differently.

• (INNER) JOIN – It returns the records with a match from both the tables.
• LEFT (OUTER) JOIN – It returns the records with all the records from the table specified in the left and matching records from the right table.
• RIGHT (OUTER) JOIN – It returns the records with all the records from the right table and matching records from the left table.
• FULL (OUTER) JOIN – It returns the records with a match in either table.

LIKE

SELECT column_name(s) from table_name WHERE column_name LIKE pattern ;

LIKE command is used to return records with a specified pattern in a column of a table. There are two wildcards used in conjunction with the LIKE operator

• % - It matches 0 or more characters.
• _ - It matches exactly one character.

ORDER BY

SELECT column_name from table_name ORDER BY column_name ASC/DESC;

ORDER BY command is used to sort the records on the basis of a particular column specified in the query. The records can be sorted in ascending or descending order depending upon the keyword ASC or DESC respectively.

OR

SELECT column_1, column_2…column_N from table_name where Condition_1 OR Condition_2 OR….Condition_N ;

OR is an operator that is used to filter the records on the basis of using the conditions such that either of the conditions is true.

MAX()

SELECT MAX (column_name) from table_name WHERE condition;

MAX() is an aggregate function that is used to return the largest value among the selected column of the table.

MIN()

SELECT MIN (column_name) from table_name WHERE condition;

MIN() is an aggregate function that is used to return the smallest value among the selected column of the table.

NOT

SELECT column_1, column_2…column_N from table_name where NOT Condition;

NOT is an operator that is used to filter the records on the basis of the conditions such that the condition is not true.

SELECT

SELECT * from table_name;

SELECT command is used to retrieve tables from the databases. Asterisk implies selecting all the columns from the table. We can even specify the columns to be displayed in the table. This can be done by providing the column names instead of the asterisk(*).

SELECT column_1, column_2 from table_name ;

SUM

SELECT SUM(column_name) FROM table_name WHERE condition;

SUM() is an aggregate function that is used to return the sum of a numeric column specified as an argument to the function.

TOP

SELECT TOP number/percent column_name(s) FROM table_name WHERE condition;

TOP is a clause that requires us to pass a number with it. This clause returns the top records from a specified column which satisfy the given condition. The number of top records returned is decided according to the number specified in the query.

TRUNCATE

TRUNCATE TABLE table_name;

TRUNCATE command is used to delete all the rows in a table. It is quite similar to the DROP command but the only difference is that the DROP command deletes the entire table whereas the TRUNCATE command deletes all the records without deleting the table and a schema of the table is still maintained.

UNION

SELECT column_name(s) from table_1 UNION SELECT column_name(s) from table_2;

UNION is an operator that is used to combine rows from two or more statements. Unlike the JOIN command which only returns the matching records, the UNION operator is used to append unrelated records.

UPDATE

UPDATE table_name SET column_name= updated_value WHERE column_name = value_before_updation;

UPDATE is a DML command which is used to modify a particular value after the records are already inserted into the table. This command requires us to firstly mention the column name that has to be updated with the value with which it should be updated. Secondly, we have to mention the column_name and the previous value (before updation) in the WHERE clause. We can even use AND/OR operators inside the WHERE clause in this command.

USE

USE database_name;

USE command is used to select the database specified in the query so that operations can be performed on the corresponding database.

WHERE

SELECT * from table_name WHERE column_name = value;

WHERE is a clause which is used to apply conditions in a SELECT statement. We simply append the WHERE clause at the end of the SELECT statement. This way, we are able to filter the records of the table.