In the era where data is being generated in humongous amounts, there is a constant need to handle data in databases. Relational databases are one of the most popular databases, and SQL is the basis of relational databases. Therefore SQL skills are indispensable in most of the job roles. In this article on SQL Commands, I will discuss the top commands and statements that you need to understand in SQL.
The topics covered in this blog are mainly divided into 4 categories:
- Data Definition Language(DDL) – Consists of commands which are used to define the database.
- Data Manipulation Language(DML) – Consists of commands which are used to manipulate the data present in the database.
- Data Control Language(DCL) – Consists of commands which deal with the user permissions and controls of the database system.
- Transaction Control Language(TCL) – Consist of commands which deal with the transaction of the database.
Apart from the above commands, the following topics will also be covered in this article:
- Comments in SQL
- Different Types Of Keys In Database
- Constraints Used In Database
- Nested Queries
- Joins
- Set Operations
- Dates & Auto Increment
- Views
- Stored Procedures
- Triggers
In this article on SQL Commands, I am going to consider the below database as an example, to show you how to write commands.
| EmployeeID | EmployeeName | Emergency ContactName | PhoneNumber | Address | City | Country |
| 01 | Shanaya | Abhinay | 9898765612 | Oberoi Street 23 | Mumbai | India |
| 02 | Anay | Soumya | 9432156783 | Marathalli House No 23 | Delhi | India |
| 03 | Preeti | Rohan | 9764234519 | Queens Road 45 | Bangalore | India |
| 04 | Vihaan | Akriti | 9966442211 | Brigade Road Block 4 | Hyderabad | India |
| 05 | Manasa | Shourya | 9543176246 | Mayo Road 23 | Kolkata | India |
So, let’s get started now!
Comments in SQL
There are two ways in which you can comment in SQL, i.e. either the Single-Line Comments or the Multi-Line Comments.
Single-Line Comments
The single line comment starts with two hyphens (–). So, any text mentioned after (–), till the end of a single line will be ignored by the compiler.
Example:
--Select all: SELECT * FROM Employee_Info;Multi-Line Comments
The Multi-line comments start with /* and end with */. So, any text mentioned between /* and */ will be ignored by the compiler.
Example:
/*Select all the columns of all the records from the Employee_Info table:*/ SELECT * FROM Students;SQL Commands: Data Definition Language Commands (DDL)
This section of the article will give you an insight into the commands through which you can define your database. The commands are as follows:
- CREATE
- DROP
- TRUNCATE
- ALTER
- BACKUP DATABASE
CREATE
This statement is used to create a table or a database.
The ‘CREATE DATABASE’ Statement
As the name suggests, this statement is used to create a database.
SyntaxCREATE DATABASE DatabaseName;ExampleCREATE DATABASE Employee;The ‘CREATE TABLE’ Statement
This statement is used to create a table.
SyntaxCREATE TABLE TableName ( Column1 datatype, Column2 datatype, Column3 datatype, .... ColumnN datatype );ExampleCREATE TABLE Employee_Info ( EmployeeID int, EmployeeName varchar(255), Emergency ContactName varchar(255), PhoneNumber int, Address varchar(255), City varchar(255), Country varchar(255) );You can also create a table using another table. Refer the below sytax and example:
The ‘CREATE TABLE AS’ Statement
SyntaxCREATE TABLE NewTableName AS SELECT Column1, column2,..., ColumnN FROM ExistingTableName WHERE ....;ExampleCREATE TABLE ExampleTable AS SELECT EmployeeName, PhoneNumber FROM Employee_Info;DROP
This statement is used to drop an existing table or a database.
The ‘DROP DATABASE’ Statement
This statement is used to drop an existing database. When you use this statement, complete information present in the database will be lost.
SyntaxDROP DATABASE DatabaseName;ExampleDROP DATABASE Employee;The ‘DROP TABLE’ Statement
This statement is used to drop an existing table. When you use this statement, complete information present in the table will be lost.
SyntaxDROP TABLE TableName;ExampleDROP Table Employee_Info;TRUNCATE
This command is used to delete the information present in the table but does not delete the table. So, once you use this command, your information will be lost, but not the table.
Syntax TRUNCATE TABLE TableName;ExampleTRUNCATE Table Employee_Info;ALTER
This command is used to delete, modify or add constraints or columns in an existing table.
The ‘ALTER TABLE’ Statement
This statement is used to add, delete, modify columns in an existing table.
The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN
You can use the ALTER TABLE statement with ADD/DROP Column command according to your need. If you wish to add a column, then you will use the ADD command, and if you wish to delete a column, then you will use the DROP COLUMN command.
SyntaxALTER TABLE TableName ADD ColumnName Datatype; ALTER TABLE TableName DROP COLUMN ColumnName;Example--ADD Column BloodGroup: ALTER TABLE Employee_Info ADD BloodGroup varchar(255); --DROP Column BloodGroup: ALTER TABLE Employee_Info DROP COLUMN BloodGroup ;The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN
This statement is used to change the datatype of an existing column in a table.
Syntax ALTER TABLE TableName ALTER COLUMN ColumnName Datatype;Example--Add a column DOB and change the data type to Date. ALTER TABLE Employee_Info ADD DOB year; ALTER TABLE Employee_Info ALTER DOB date;BACKUP DATABASE
This statement is used to create a full backup of an existing database.
SyntaxBACKUP DATABASE DatabaseName TO DISK = 'filepath';ExampleBACKUP DATABASE Employee TO DISK = 'C:UsersSahitiDesktop';You can also use a differential back up. This type of back up only backs up the parts of the database, which have changed since the last complete backup of the database.
SyntaxBACKUP DATABASE DatabaseName TO DISK = 'filepath' WITH DIFFERENTIAL;ExampleBACKUP DATABASE Employee TO DISK = 'C:UsersSahitiDesktop' WITH DIFFERENTIAL;Now that you know the data definition commands, let me take you through the various types of Keys and Constraints that you need to understand before learning how to manipulate the databases.
SQL Commands: Different Types Of Keys In Database
There are mainly 7 types of Keys, that can be considered in a database. I am going to consider the below tables to explain to you the various keys.
- Candidate Key – A set of attributes which can uniquely identify a table can be termed as a Candidate Key. A table can have more than one candidate key, and out of the chosen candidate keys, one key can be chosen as a Primary Key. In the above example, since EmployeeID, InsuranceNumber and PanNumber can uniquely identify every tuple, they would be considered as a Candidate Key.
- Super Key – The set of attributes which can uniquely identify a tuple is known as Super Key. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn’t true.
- Primary Key – A set of attributes which are used to uniquely identify every tuple is also a primary key. In the above example, since EmployeeID, InsuranceNumber and PanNumber are candidate keys, any one of them can be chosen as a Primary Key. Here EmployeeID is chosen as the primary key.
- Alternate Key – Alternate Keys are the candidate keys, which are not chosen as a Primary key. From the above example, the alternate keys are PanNumber and Insurance Number.
- Unique Key – The unique key is similar to the primary key, but allows one NULL value in the column. Here the Insurance Number and the Pan Number can be considered as unique keys.
- Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. in the above example, the Employee_ID from the Employee_Information Table is referred to the Employee_ID from the Employee_Salary Table.
- Composite Key – A composite key is a combination of two or more columns that identify each tuple uniquely. Here, the Employee_ID and Month-Year_Of_Salary can be grouped together to uniquely identify every tuple in the table.
SQL Commands: Constraints Used In Database
Constraints are used in a database to specify the rules for data in a table. The following are the different types of constraints:
- NOT NULL
- UNIQUE
- CHECK
- DEFAULT
- INDEX
NOT NULL
This constraint ensures that a column cannot have a NULL value.
Example
--NOT NULL on Create Table CREATE TABLE Employee_Info ( EmployeeID int NOT NULL, EmployeeName varchar(255) NOT NULL, Emergency ContactName varchar(255), PhoneNumber int NOT NULL, Address varchar(255), City varchar(255), Country varchar(255) ); --NOT NULL on ALTER TABLE ALTER TABLE Employee_Info MODIFY PhoneNumber int NOT NULL;UNIQUE
This constraint ensures that all the values in a column are unique.
Example
--UNIQUE on Create Table CREATE TABLE Employee_Info ( EmployeeID int NOT NULL UNIQUE, EmployeeName varchar(255) NOT NULL, Emergency ContactName varchar(255), PhoneNumber int NOT NULL, Address varchar(255), City varchar(255), Country varchar(255) ); --UNIQUE on Multiple Columns CREATE TABLE Employee_Info ( EmployeeID int NOT NULL, EmployeeName varchar(255) NOT NULL, Emergency ContactName varchar(255), PhoneNumber int NOT NULL, Address varchar(255), City varchar(255), Country varchar(255), CONSTRAINT UC_Employee_Info UNIQUE(Employee_ID, PhoneNumber) ); --UNIQUE on ALTER TABLE ALTER TABLE Employee_Info ADD UNIQUE (Employee_ID); --To drop a UNIQUE constraint ALTER TABLE Employee_Info DROP CONSTRAINT UC_Employee_Info;CHECK
This constraint ensures that all the values in a column satisfy a specific condition.
Example
--CHECK Constraint on CREATE TABLE CREATE TABLE Employee_Info ( EmployeeID int NOT NULL, EmployeeName varchar(255), Emergency ContactName varchar(255), PhoneNumber int, Address varchar(255), City varchar(255), Country varchar(255) CHECK (Country=='India') ); --CHECK Constraint on multiple columns CREATE TABLE Employee_Info ( EmployeeID int NOT NULL, EmployeeName varchar(255), Emergency ContactName varchar(255), PhoneNumber int, Address varchar(255), City varchar(255), Country varchar(255) CHECK (Country = 'India' AND Cite = 'Hyderabad') ); --CHECK Constraint on ALTER TABLE ALTER TABLE Employee_Info ADD CHECK (Country=='India'); --To give a name to the CHECK Constraint ALTER TABLE Employee_Info ADD CONSTRAINT CheckConstraintName CHECK (Country=='India'); --To drop a CHECK Constraint ALTER TABLE Employee_Info DROP CONSTRAINT CheckConstraintName;DEFAULT
This constraint consists of a set of default values for a column when no value is specified.
Example
--DEFAULT Constraint on CREATE TABLE CREATE TABLE Employee_Info ( EmployeeID int NOT NULL, EmployeeName varchar(255), Emergency ContactName varchar(255), PhoneNumber int, Address varchar(255), City varchar(255), Country varchar(255) DEFAULT 'India' ); --DEFAULT Constraint on ALTER TABLE ALTER TABLE Employee_Info ADD CONSTRAINT defau_Country DEFAULT 'India' FOR Country; --To drop the Default Constraint ALTER TABLE Employee_Info ALTER COLUMN Country DROP DEFAULT;INDEX
This constraint is used to create indexes in the table, through which you can create and retrieve data from the database very quickly.
Syntax
--Create an Index where duplicate values are allowed CREATE INDEX IndexName ON TableName (Column1, Column2, ...ColumnN); --Create an Index where duplicate values are not allowed CREATE UNIQUE INDEX IndexName ON TableName (Column1, Column2, ...ColumnN);Example
CREATE INDEX idex_EmployeeName ON Persons (EmployeeName); --To delete an index in a table DROP INDEX Employee_Info.idex_EmployeeName;Now, let us look into the next part of this article i.e. DML Commands.
SQL Commands: Data Manipulation Language Commands (DML)
This section of the article will give you an insight into the commands through which you can manipulate the database. The commands are as follows:
- USE
- INSERT INTO
- UPDATE
- DELETE
- SELECT
Apart from these commands, there are also other manipulative operators/functions such as:
- Operators
- Aggregate Functions
- NULL Functions
- Aliases & Case Statement
USE
The USE statement is used to select the database on which you want to perform operations.
Syntax
USE DatabaseName;Example
USE Employee;INSERT INTO
This statement is used to insert new records into the table.
Syntax
INSERT INTO TableName (Column1, Column2, Column3, ...,ColumnN) VALUES (value1, value2, value3, ...); --If you don't want to mention the column names then use the below syntax INSERT INTO TableName VALUES (Value1, Value2, Value3, ...);Example
INSERT INTO Employee_Info(EmployeeID, EmployeeName, Emergency ContactName, PhoneNumber, Address, City, Country) VALUES ('06', 'Sanjana','Jagannath', '9921321141', 'Camel Street House No 12', 'Chennai', 'India'); INSERT INTO Employee_Info VALUES ('07', 'Sayantini','Praveen', '9934567654', 'Nice Road 21', 'Pune', 'India');UPDATE
This statement is used to modify the records already present in the table.
Syntax
UPDATE TableName SET Column1 = Value1, Column2 = Value2, ... WHERE Condition;Example
UPDATE Employee_Info SET EmployeeName = 'Aahana', City= 'Ahmedabad' WHERE EmployeeID = 1;DELETE
This statement is used to delete the existing records in a table.
Syntax
DELETE FROM TableName WHERE Condition;Example
DELETE FROM Employee_Info WHERE EmployeeName='Preeti';SELECT
This statement is used to select data from a database and the data returned is stored in a result table, called the result-set.
Syntax
SELECT Column1, Column2, ...ColumN FROM TableName; --(*) is used to select all from the table SELECT * FROM table_name; -- To select the number of records to return use: SELECT TOP 3 * FROM TableName;Example
SELECT EmployeeID, EmployeeName FROM Employee_Info; --(*) is used to select all from the table SELECT * FROM Employee_Info; -- To select the number of records to return use: SELECT TOP 3 * FROM Employee_Info;Apart from just using the SELECT keyword individually, you can use the following keywords with the SELECT statement:
- DISTINCT
- ORDER BY
- GROUP BY
- HAVING Clause
- INTO
The ‘SELECT DISTINCT’ Statement
This statement is used to return only different values.
SyntaxSELECT DISTINCT Column1, Column2, ...ColumnN FROM TableName;ExampleSELECT DISTINCT PhoneNumber FROM Employee_Info;The ‘ORDER BY’ Statement
The ‘ORDER BY’ statement is used to sort the required results in ascending or descending order. The results are sorted in ascending order by default. Yet, if you wish to get the required results in descending order, you have to use the DESC keyword.
SyntaxSELECT Column1, Column2, ...ColumnN FROM TableName ORDER BY Column1, Column2, ... ASC|DESC;Example-- Select all employees from the 'Employee_Info' table sorted by EmergencyContactName: SELECT * FROM Employee_Info ORDER BY EmergencyContactName; -- Select all employees from the 'Employee_Info' table sorted by EmergencyContactName in Descending order: SELECT * FROM Employee_Info ORDER BY EmergencyContactName DESC; -- Select all employees from the 'Employee_Info' table sorted by EmergencyContactName and EmployeeName: SELECT * FROM Employee_Info ORDER BY EmergencyContactName, EmployeeName; /* Select all employees from the 'Employee_Info' table sorted by EmergencyContactName in Descending order and EmployeeName in Ascending order: */ SELECT * FROM Employee_Info ORDER BY EmergencyContactName ASC, EmployeeName DESC;The ‘GROUP BY’ Statement
This ‘GROUP BY’ statement is used with the aggregate functions to group the result-set by one or more columns.
SyntaxSELECT Column1, Column2,..., ColumnN FROM TableName WHERE Condition GROUP BY ColumnName(s) ORDER BY ColumnName(s);Example-- To list the number of employees from each city. SELECT COUNT(EmployeeID), City FROM Employee_Info GROUP BY City;The ‘HAVING’ Clause
The ‘HAVING’ clause is used in SQL because the WHERE keyword cannot be used everywhere.
SyntaxSELECT ColumnName(s) FROM TableName WHERE Condition GROUP BY ColumnName(s) HAVING Condition ORDER BY ColumnName(s);Example/* To list the number of employees in each city. The employees should be sorted high to low and only those cities must be included who have more than 5 employees:*/ SELECT COUNT(EmployeeID), City FROM Employee_Info GROUP BY City HAVING COUNT(EmployeeID) > 2 ORDER BY COUNT(EmployeeID) DESC;The ‘SELECT INTO’ Statement
The ‘SELECT INTO’ statement is used to copy data from one table to another.
SyntaxSELECT * INTO NewTable [IN ExternalDB] FROM OldTable WHERE Condition;Example-- To create a backup of database 'Employee' SELECT * INTO EmployeeBackup FROM Employee; --To select only few columns from Employee SELECT EmployeeName, PhoneNumber INTO EmployeeContactDetails FROM Employee; SELECT * INTO BlrEmployee FROM Employee WHERE City = 'Bangalore';Now, as I mentioned before, let us move onto our next section in this article on SQL Commands, i.e. the Operators.
Operators in SQL
The different set of operators available in SQL are as follows:
Let us look into each one of them, one by one.
Arithmetic Operators
| Operator | Description |
| % | Modulous [A % B] |
| / | Division [A / B] |
| * | Multiplication [A * B] |
| – | Subtraction [A – B] |
| + | Addition [A + B] |
Bitwise Operators
| Operator | Description |
| ^ | Bitwise Exclusive OR (XOR) [A ^ B] |
| | | Bitwise OR [A | B] |
| & | Bitwise AND [A & B] |
Comparison Operators
| Operator | Description |
| < > | Not Equal to [A < > B] |
| <= | Less than or equal to [A <= B] |
| >= | Greater than or equal to [A >= B] |
| < | Less than [A < B] |
| > | Greater than [A > B] |
| = | Equal to [A = B] |
Compound Operators
| Operator | Description |
| |*= | Bitwise OR equals [A |*= B] |
| ^-= | Bitwise Exclusive equals [A ^-= B] |
| &= | Bitwise AND equals [A &= B] |
| %= | Modulo equals [A %= B] |
| /= | Divide equals [A /= B] |
| *= | Multiply equals [A*= B] |
| -= | Subtract equals [A-= B] |
| += | Add equals [A+= B] |
Logical Operators
The Logical operators present in SQL are as follows:
- AND
- OR
- NOT
- BETWEEN
- LIKE
- IN
- EXISTS
- ALL
- ANY
This operator is used to filter records that rely on more than one condition. This operator displays the records, which satisfy all the conditions separated by AND, and give the output TRUE.
Syntax SELECT Column1, Column2, ..., ColumnN FROM TableName WHERE Condition1 AND Condition2 AND Condition3 ...;ExampleSELECT * FROM Employee_Info WHERE City='Mumbai' AND City='Hyderabad';</pre> OR OperatorThis operator displays all those records which satisfy any of the conditions separated by OR and give the output TRUE.
SyntaxSELECT Column1, Column2, ..., ColumnN FROM TableName WHERE Condition1 OR Condition2 OR Condition3 ...;ExampleSELECT * FROM Employee_Info WHERE City='Mumbai' OR City='Hyderabad'; NOT OperatorThe NOT operator is used, when you want to display the records which do not satisfy a condition.
SyntaxSELECT Column1, Column2, ..., ColumnN FROM TableName WHERE NOT Condition;ExampleSELECT * FROM Employee_Info WHERE NOT City='Mumbai';NOTE: You can also combine the above three operators and write a query as follows:
SELECT * FROM Employee_Info WHERE NOT Country='India' AND (City='Bangalore' OR City='Hyderabad');NOTE: You can also combine the above three operators and write a query as follows:
SELECT * FROM Employee_Info WHERE NOT Country='India' AND (City='Bangalore' OR City='Hyderabad'); BETWEEN OperatorThe BETWEEN operator is used, when you want to select values within a given range. Since this is an inclusive operator, both the starting and ending values are considered.
SyntaxSELECT ColumnName(s) FROM TableName WHERE ColumnName BETWEEN Value1 AND Value2;ExampleSELECT * FROM Employee_Salary WHERE Salary BETWEEN 40000 AND 50000; LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column of a table. There are mainly two wildcards that are used in conjunction with the LIKE operator:
- % – It matches 0 or more character.
- _ – It matches exactly one character.
Refer to the following table for the various patterns that you can mention with the LIKE operator.
| Like Operator Condition | Description |
| WHERE CustomerName LIKE ‘v% | Finds any values that start with “v” |
| WHERE CustomerName LIKE ‘%v’ | Finds any values that end with “v” |
| WHERE CustomerName LIKE ‘%and%’ | Finds any values that have “and” in any position |
| WHERE CustomerName LIKE ‘_q%’ | Finds any values that have “q” in the second position. |
| WHERE CustomerName LIKE ‘u_%_%’ | Finds any values that start with “u” and are at least 3 characters in length |
| WHERE ContactName LIKE ‘m%a’ | Finds any values that start with “m” and end with “a” |
This operator is used for multiple OR conditions. This allows you to specify multiple values in a WHERE clause.
SyntaxSELECT ColumnName(s) FROM TableName WHERE ColumnName IN (Value1,Value2...);ExampleSELECT * FROM Employee_Info WHERE City IN ('Mumbai', 'Bangalore', 'Hyderabad');NOTE: You can also use IN while writing Nested Queries.
EXISTS OperatorThe EXISTS operator is used to test if a record exists or not.
SyntaxSELECT ColumnName(s) FROM TableName WHERE EXISTS (SELECT ColumnName FROM TableName WHERE condition);ExampleSELECT EmergencyContactName FROM Employee_Info WHERE EXISTS (SELECT EmergencyContactName FROM Employee_Info WHERE EmployeeId = 05 AND City = 'Kolkata'); ALL OperatorThe ALL operator is used with a WHERE or HAVING clause and returns TRUE if all of the subquery values meet the condition.
SyntaxSELECT ColumnName(s) FROM TableName WHERE ColumnName operator ALL (SELECT ColumnName FROM TableName WHERE condition);ExampleSELECT EmployeeName FROM Employee_Info WHERE EmployeeID = ALL (SELECT EmployeeID FROM Employee_Info WHERE City = 'Hyderabad'); ANY OperatorSimilar to the ALL operator, the ANY operator is also used with a WHERE or HAVING clause and returns true if any of the subquery values meet the condition.
Syntax SELECT ColumnName(s) FROM TableName WHERE ColumnName operator ANY (SELECT ColumnName FROM TableName WHERE condition);ExampleSELECT EmployeeName FROM Employee_Info WHERE EmployeeID = ANY (SELECT EmployeeID FROM Employee_Info WHERE City = 'Hyderabad' OR City = 'Kolkata');Next, in this article on SQL Commands, let us look into the various Aggregate Functions provided in SQL.
Aggregate Functions
This section of the article will include the following functions:
- MIN()
- MAX()
- COUNT()
- SUM()
- AVG()
MIN() Function
The MIN function returns the smallest value of the selected column in a table.
SyntaxSELECT MIN(ColumnName) FROM TableName WHERE Condition;ExampleSELECT MIN(EmployeeID) AS SmallestID FROM Employee_Info;MAX() Function
The MAX function returns the largest value of the selected column in a table.
SyntaxSELECT MAX(ColumnName) FROM TableName WHERE Condition;ExampleSELECT MAX(Salary) AS LargestFees FROM Employee_Salary;COUNT() Function
The COUNT function returns the number of rows which match the specified criteria.
SyntaxSELECT COUNT(ColumnName) FROM TableName WHERE Condition;ExampleSELECT COUNT(EmployeeID) FROM Employee_Info;SUM() Function
The SUM function returns the total sum of a numeric column that you choose.
SyntaxSELECT SUM(ColumnName) FROM TableName WHERE Condition;ExampleSELECT SUM(Salary) FROM Employee_Salary;AVG() Function
The AVG function returns the average value of a numeric column that you choose.
SyntaxSELECT AVG(ColumnName) FROM TableName WHERE Condition;ExampleSELECT AVG(Salary) FROM Employee_Salary;NULL Functions
The NULL functions are those functions which let you return an alternative value if an expression is NULL. In the SQL Server, the function is ISNULL().
Example
SELECT EmployeeID * (Month_Year_of_Salary + ISNULL(Salary, 0)) FROM Employee_Salary;Aliases & Case Statement
In this section of this article on SQL Commands, you will go through the Aliases and Case statement one after the other.
Aliases
Aliases are used to give a column/table a temporary name and only exists for a duration of the query.
Syntax--Alias Column Syntax SELECT ColumnName AS AliasName FROM TableName; --Alias Table Syntax SELECT ColumnName(s) FROM TableName AS AliasName;ExampleSELECT EmployeeID AS ID, EmployeeName AS EmpName FROM Employee_Info; SELECT EmployeeName AS EmpName, EmergencyContactName AS [Contact Name] FROM Employee_Info;Case Statement
This statement goes through all the conditions and returns a value when the first condition is met. So, if no conditions are TRUE, it returns the value in the ELSE clause. Also, if no conditions are true and there is no ELSE part, then it returns NULL.
SyntaxCASE WHEN Condition1 THEN Result1 WHEN Condition2 THEN Result2 WHEN ConditionN THEN ResultN ELSE Result END;ExampleSELECT EmployeeName, City FROM Employee_Info ORDER BY (CASE WHEN City IS NULL THEN 'Country is India by default' ELSE City END);Now, that I have told you a lot about DML commands in this article on SQL Commands, let me just tell you in short about Nested Queries, Joins, Set Operations, and Dates & Auto Increment.
SQL Commands: Nested Queries
Nested queries are those queries which have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE. Refer to the image below:
JOINS are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins:
- INNER JOIN: This join returns those records which have matching values in both the tables.
- FULL JOIN: This join returns all those records which either have a match in the left or the right table.
- LEFT JOIN: This join returns records from the left table, and also those records which satisfy the condition from the right table.
- RIGHT JOIN: This join returns records from the right table, and also those records which satisfy the condition from the left table.
Refer to the image below.
Let’s consider the below table apart from the Employee_Info table, to understand the syntax of joins.
| TechID | EmpID | TechName | ProjectStartDate |
| 1 | 10 | DevOps | 04-01-2019 |
| 2 | 11 | Blockchain | 06-07-2019 |
| 3 | 12 | Python | 01-03-2019 |
INNER JOIN
Syntax
SELECT ColumnName(s) FROM Table1 INNER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;Example
SELECT Technologies.TechID, Employee_Info.EmployeeName FROM Technologies INNER JOIN Employee_Info ON Technologies.EmpID = Employee_Info.EmpID;FULL JOIN
Syntax
SELECT ColumnName(s) FROM Table1 FULL OUTER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;Example
SELECT Employee_Info.EmployeeName, Technologies.TechID FROM Employee_Info FULL OUTER JOIN Orders ON Employee_Info.EmpID=Employee_Salary.EmpID ORDER BY Employee_Info.EmployeeName;LEFT JOIN
Syntax
SELECT ColumnName(s) FROM Table1 LEFT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;Example
SELECT Employee_Info.EmployeeName, Technologies.TechID FROM Employee_Info LEFT JOIN Technologies ON Employee_Info.EmployeeID = Technologies.EmpIDID ORDER BY Employee_Info.EmployeeName;RIGHT JOIN
Syntax
SELECT ColumnName(s) FROM Table1 RIGHT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;Example
SELECT Technologies.TechID FROM Technologies RIGHT JOIN Employee_Info ON Technologies.EmpID = Employee_Info.EmployeeID ORDER BY Technologies.TechID;SQL Commands: Set Operations
There are mainly three set operations:UNION, INTERSECT, EXCEPT. You can refer to the image below to understand the set operations in SQL.
UNION
This operator is used to combine the result-set of two or more SELECT statements.
Syntax
SELECT ColumnName(s) FROM Table1 UNION SELECT ColumnName(s) FROM Table2;INTERSECT
This clause used to combine two SELECT statements and return the intersection of the data-sets of both the SELECT statements.
Syntax
SELECT Column1 , Column2 .... FROM TableName WHERE Condition INTERSECT SELECT Column1 , Column2 .... FROM TableName WHERE ConditionEXCEPT
This operator returns those tuples that are returned by the first SELECT operation, and are not returned by the second SELECT operation.
Syntax
SELECT ColumnName FROM TableName EXCEPT SELECT ColumnName FROM TableName;Next, in this article, let us look into the date functions and auto-increment fields.
SQL Commands: Dates & Auto Increment
In this section of this article, I will explain to you how to use the Date functions and also the Auto-Increment fields.
Dates
The following data types are present in a SQL Server to store a date or a date/time value in a database.
| Data Type | Format |
| DATE | YYYY-MM-DD |
| DATETIME | YYYY-MM-DD HH:MI:SS |
| SMALLDATETIME | YYYY-MM-DD HH:MI:SS |
| TIMESTAMP | A Unique Number |
Example
SELECT * FROM Technologies WHERE ProjectStartDate='2019-04-01'Auto Increment
This field generates a unique number automatically when a new record is inserted into a table. The MS SQL Server uses the IDENTITY keyword for this feature.
Example
<span>/* To define the "EmployeeID" column to be an auto-increment primary key field in the "Employee_Info" table */</span> <span>CREATE TABLE Employee_Info (</span> <span>EmployeeID INT IDENTITY(1,1) PRIMARY KEY,</span> <span>EmployeeName VARCHAR(255) NOT NULL</span> <span>EmergencyContactName VARCHAR(255) NOT NULL,</span> <span>);</span>Now, that you guys know the DML commands, let’s move onto our next section in this article on SQL Commands i.e. the DCL commands.
SQL Commands: Data Control Language Commands (DCL)
This section of the article will give you an insight into the commands which are used to enforce database security in multiple user database environments. The commands are as follows:
- GRANT
- REVOKE
GRANT
This command is used to provide access or privileges on the database and its objects to the users.
Syntax
GRANT PrivilegeName ON ObjectName TO {UserName |PUBLIC |RoleName} [WITH GRANT OPTION];where,
- PrivilegeName – Is the privilege/right/access granted to the user.
- ObjectName – Name of a database object like TABLE/VIEW/STORED PROC.
- UserName – Name of the user who is given the access/rights/privileges.
- PUBLIC – To grant access rights to all users.
- RoleName – The name of a set of privileges grouped together.
- WITH GRANT OPTION – To give the user access to grant other users with rights.
Example
-- To grant SELECT permission to Employee_Info table to user1 GRANT SELECT ON Employee_Info TO user1;REVOKE
This command is used to withdraw the user’s access privileges given by using the GRANT command.
Syntax
REVOKE PrivilegeName ON ObjectName FROM {UserName |PUBLIC |RoleName}Example
-- To revoke the granted permission from user1 REVOKE SELECT ON Employee_Info TO user1;Now, next in this article on SQL Commands, I will discuss Views, Stored Procedures, and Triggers.
SQL Commands: Views
A view in SQL is a single table, which is derived from other tables. So, a view contains rows and columns similar to a real table and has fields from one or more table.
The ‘CREATE VIEW’ statement
This statement is used to create a view, from a table.
Syntax
CREATE VIEW ViewName AS SELECT Column1, Column2, ..., ColumnN FROM TableName WHERE Condition;Example
CREATE VIEW [Kolkata Employees] AS SELECT EmployeeName, PhoneNumber FROM Employee_Info WHERE City = "Kolkata";The ‘CREATE OR REPLACE VIEW’ statement
This statement is used to update a view.
Syntax
CREATE VIEW OR REPLACE ViewName AS SELECT Column1, Column2, ..., ColumnN FROM TableName WHERE Condition;Example
CREATE VIEW OR REPLACE [Kolkata Employees] AS SELECT EmployeeName, PhoneNumber FROM Employee_Info WHERE City = "Kolkata";The ‘DROP VIEW’ statement
This statement is used to delete a view.
Syntax
DROP VIEW ViewName;Example
DROP VIEW [Kolkata Employees];SQL Commands: Stored Procedures
A code which you can save and reuse it again is known as StoredProcedures.
Syntax
CREATE PROCEDURE ProcedureName AS SQLStatement GO;Example
EXEC ProcedureName;SQL Commands: Triggers
Triggers are a set of SQL statements which are stored in the database catalog. These statements are executed whenever an event associated with a table occurs. So, a trigger can be invoked either BEFORE or AFTER the data is changed by INSERT, UPDATE or DELETE statement. Refer to the image below.
Syntax
CREATE TRIGGER [TriggerName] [BEFORE | AFTER] {INSERT | UPDATE | DELETE} on [TableName] [FOR EACH ROW] [TriggerBody]Now, let’s move on to the last section of this article on SQL Commands i.e. the Transaction Control Language Commands.
SQL Commands: Transaction Control Language Commands (TCL)
This section of the article will give you an insight into the commands which are used to manage transactions in the database. The commands are as follows:
- COMMIT
- ROLLBACK
- SAVEPOINT
COMMIT
This command is used to save the transaction into the database.
Syntax
COMMIT;ROLLBACK
This command is used to restore the database to the last committed state.
Syntax
ROLLBACK;NOTE: When you use ROLLBACK with SAVEPOINT, then you can directly jump to a savepoint in an ongoing transaction. Syntax: ROLLBACK TO SavepointName;
SAVEPOINT
This command is used to temporarily save a transaction. So if you wish to rollback to any point, then you can save that point as a ‘SAVEPOINT’.
Syntax
SAVEPOINT SAVEPOINTNAME;Consider the below example to understand the working of transactions in the database.
| EmployeeID | EmployeeName |
| 01 | Ruhaan |
| 02 | Suhana |
| 03 | Aayush |
| 04 | Rashi |
Now, use the below SQL queries to understand the transactions in the database.
INSERT INTO Employee_Table VALUES(05, 'Avinash'); COMMIT; UPDATE Employee_Table SET name = 'Akash' WHERE id = '05'; SAVEPOINT S1; INSERT INTO Employee_Table VALUES(06, 'Sanjana'); SAVEPOINT S2; INSERT INTO Employee_Table VALUES(07, 'Sanjay'); SAVEPOINT S3; INSERT INTO Employee_Table VALUES(08, 'Veena'); SAVEPOINT S4; SELECT * FROM Employee_Table;The output to the above set of queries would be as follows:
| EmployeeID | EmployeeName |
| 01 | Ruhaan |
| 02 | Suhana |
| 03 | Aayush |
| 04 | Rashi |
| 05 | Akash |
| 06 | Sanjana |
| 07 | Sanjay |
| 08 | Veena |
Now, if you rollback to S2 using the below queries, the output is mentioned in the below table.
ROLLBACK TO S2; SELECT * FROM Employee_Table;| EmployeeID | EmployeeName |
| 01 | Ruhaan |
| 02 | Suhana |
| 03 | Aayush |
| 04 | Rashi |
| 05 | Akash |
| 06 | Sanjana |
By this, I come to the end of this article on SQL Commands. I hope you enjoyed reading this article on SQL Commands. We have seen the different commands that will help you write queries and play around with your databases. If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of ”SQL Commands” and I will get back to you.