SQL Commands with Examples

Written By

Deepak Jose

Introduction

SQL is the standard language for relational database management systems. We use it to carry out operations on the database, including creating, updating, and retrieving data. It’s basically a way to communicate with the database. Learning SQL is important to work in any IT field and is extremely important if you want to become a Web Developer or a Data Scientist. In this article, you would learn about all the important SQL commands that you should know.

Before we begin if you want to learn more about Data Science read the below articles.

1. Introduction to Data Science
2. Languages and Tools you should know to become a Data Scientist
3. Tools for Data Science
4. Statistics for Data Science – Descriptive Statistics
5. Complete Data Science Roadmap – With resources
6. Introduction To Natural Language Processing

SQL Commands

As we have mentioned above, to communicate with the database we use SQL commands. There are mainly 5 types of SQL Commands: DDL, DML, DCL, TCL, and DQL. Let’s see each one of them below.

Types of SQL Commands

1. Data Definition Language (DDL) SQL Commands

• DDL as its name suggests contains statements that are used for Defining Database objects like tables, users, and indices. These statements include: Create, alter, drop, etc.
• All the DDL commands are auto-committed if they are successfully executed without errors. This means that the changes are saved permanently in the database.

Some of the important commands under DDL are:

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE

The CREATE command creates a new database, table, or view.

The CREATE DATABASE command lets you create a new database.

Syntax

CREATE DATABASE databasename

Example

CREATE DATABASE knowindustrialengineering;

The CREATE TABLE command lets you create a new table in the database.

Syntax

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

Example

CREATE TABLE Coders( id int, FirstName varchar2(50), LastName varchar2(50), Address varchar2(100) );

The CREATE VIEW command lets you create a virtual table to show the results of an SQL statement in the form of a real table.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

CREATE VIEW [Students Above Average Marks] AS
SELECT StudentName, Marks
FROM Students
WHERE Marks> (SELECT AVG(Marks) FROM Students);

DROP

DROP Command lets you Delete a whole Database, Table, or View.

The DROP DATABASE Command allows you to delete a whole database.

Syntax

DROP DATABASE databasename;

Example

DROP DATABASE knowindustrialengineering;

The DROP TABLE Command lets you delete an entire table from the current database.

Syntax

DROP TABLE table_name;

Example

DROP TABLE Coders;

The DROP VIEW lets you delete a view.

Syntax

DROP VIEW [view_name];

Example

DROP VIEW [Students Above Average Marks];

ALTER

The ALTER TABLE lets you add, delete, or modify the columns of a particular table. Adding or dropping the constraints of a table is also done using this command.

Examples

Let’s see some examples of using ALTER TABLE Command.

ALTER TABLE- ADD

In the following example, we would add a new column “CoderID” to our table “Coders “.

ALTER TABLE Coders
ADD CoderID varchar(10);

ALTER TABLE – MODIFY – Change Column Data Type

In this example, we would change a column’s datatype from varchar to varchar2.

ALTER TABLE Coders
ALTER COLUMN CoderID varchar2(10);

ALTER TABLE – MODIFY – Change Column Name

In this example, we would use the command to change the name of the column “CodersID” to “CoderID”

ALTER TABLE Coders
RENAME COLUMN CodersID TO CoderID;

ALTER TABLE – DROP COLUMN

Here we would drop the column we have created in the above example.

ALTER TABLE Coders
DROP COLUMN CoderID;

TRUNCATE

The TRUNCATE Command lets you delete all the data inside the table without deleting the table structure.

Example

The following example would remove all the rows from the table. But this won’t delete the table structure.

TRUNCATE TABEL Coders;

2. Data Manipulation Language (DML) SQL Commands

These statements allow you to access and manipulate data in the database. For example, we can perform operations to create, update and delete data using the DML Commands in SQL.

The important DML Commands that let us do these operations are as follows:

• INSERT
• UPDATE
• DELETE
• CALL
• EXPLAIN
• LOCK

Lets now see each one of them in detail.

INSERT

The INSERT INTO Command allows you to insert a new record into the table. Let’s see the syntax and an example below.

Syntax

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Examples

Example for inserting one-row data into the table:

INSERT INTO Coders
VALUES ('Deepak',22,'DJ000');

We can also enter multiple rows all at once. Let’s see the syntax for the same below.

INSERT INTO table_name (column_list) 
VALUES 
(value_list_1), 
(value_list_2), 
... 
(value_list_n);

We can also enter values to only specific columns by specifying them. Let’s see the example below.

INSERT INTO Coders (CoderName,CoderID)
VALUES ('Tojo','TJ007');

UPDATE

The UPDATE Command in SQL allows you to modify the current value/values of a column in the table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE Coders
SET CoderID = 'DJ00', CoderAge = 23
WHERE CoderName = 'Deepak';

DELETE

The DELETE Command lets you delete the existing records in a table w.r.t some conditions.

Syntax

DELETE FROM table_name WHERE condition;

Example

DELETE FROM Coders WHERE CoderID = 'TJ007';

The above command deletes the record that has the ID “TJ007”

CALL

The CALL Statement in SQL lets you invoke/call a specified registered stored procedure.

Syntax

CALL procedure_name(parameter[param1, param2, ...])

EXPLAIN

This command allows us to find out details about how SQL queries were executed. We can get details like the optimizer log, the manner in which the tables are connected, etc. It is a helpful tool for query optimization as a result. It functions with statements such as SELECT, deletes, INSERT, REPLACE, and UPDATE.

Syntax

EXPLAIN (QUERY Statement) ; /* ONLY TAKES COMMAND AS PARAMETER */

LOCKS

Since SQL Server is a relational database, SQL Locks can be used to ensure data consistency, which is a key method. In SQL Server, a lock is created when a transaction begins and released when it is finished. There are numerous varieties of locks. Let’s see each one of them.

• Shared (S) Locks: This form of lock will occur when the object has to be read, but it is not destructive.
• Exclusive (X) Locks: These locks restrict additional operations from being performed on locked objects, such as inserting, updating, or deleting data.
• Update (U) Locks: Similar to Exclusive lock in certain respects, except here the procedure can be divided into a “read phase” and “write phase.” Other transactions are restricted, particularly during the read phase.
• Intent Locks: A table has an intent lock if SQL Server has a shared (S) lock or an exclusive (X) lock on a particular row.
• Regular intent locks: Intent exclusive (IX), intent shared (IS), and intent update are the standard types of intent locks (IU).
• Conversion locks: Shared with intent exclusive (SIX), Shared with intent update (SIU), and Update with intent exclusive (UIX).

Syntax

LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE

3. Transaction Control Language (TCL) SQL Commands

A Transaction is the execution of a set of statements in SQL that would affect the data in the database. Whenever any transaction is made it happens temporarily in the database. So in order to make these changes permanent we use this type of SQL command.

The most used TCL commands are as follows:

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION
• SET CONSTRAINT

COMMIT

This command allows us to save the changes permanently after performing a DML command like – INSERT, DELETE, UPDATE, etc. If we don’t do this we can rollback these changes using another TCL command called ROLLBACK

Syntax

commit; 

ROLLBACK

As we have mentioned above the ROLLBACK command lets us roll back the temporary changes caused by the DML commands in the database. This command would roll back the data to the last savepoint or to the last committed state. SAVEPOINT is also another TCL command we would see later.

Syntax

rollback;

SAVEPOINT

This command allows us to save the data in the database at a particular point temporarily. This would allow us to roll back the changes made after this savepoint if we make any mistakes. One thing we should remember is that savepoint would not save the data permanently like commit.

Syntax

Savepoint A; 

Now if we want to rollback to this particular savepoint we should type:

Rollback to A; 

SET TRANSACTION

This SQL statement lets us set the current transaction as a read-only or read/write, establish its isolation level, or assign it to a specified rollback segment.

Syntax

SET TRANSACTION [ READ WRITE | READ ONLY ];

SET CONSTRAINT

This statement allows us to change the mode for ALL constraints or a list of constraint names. We can use it to define, for a specific transaction, whether a deferrable constraint is verified after each DML statement (IMMEDIATE) or after the transaction is committed using the SET CONSTRAINTS command (DEFERRED).

The SET CONSTRAINTS mode is active for the whole transaction or until it is reset by another SET CONSTRAINTS statement.

Example 1

SET CONSTRAINTS ALL IMMEDIATE;

Example 2

SET CONSTRAINTS emp_job_nn, emp_salary_min ,
   hr.jhist_dept_fk@remote DEFERRED;

Both examples are taken from the official Oracle Documentation

4. Data Query Language (DQL) IN SQL

The DQL commands allow us to fetch data from the database. It contains only one important command:

SELECT

The SELECT statement lets us get the desired data from the database. We can fetch and display the data we need using the select statement.

Syntax

SELECT column1, column2, ...
FROM table_name;

Example

SELECT * FROM Coders;

This code would display all the records in the table “Coders”.

5. Data Control Language (DCL)

The DCL commands are used to GRANT and REVOKE certain rights, permissions, and other controls on the database for a user. The 2 important DCL commands are as follows:

• GRANT
• REVOKE

GRANT

The GRANT statement is used to grant certain privileges to perform SELECT, ALTER, INDEX, GRANT, DROP, etc on certain objects to a user.

Syntax

GRANT privileges_names ON object TO user;

The object can be a database on which the user needs permission to perform particular operations.

Example

GRANT SELECT ON Users TO'Deepak'@'localhost;

REVOKE

The REVOKE statement is used to revoke all or some of the privileges we have given using the above command.

Syntax

Its syntax is similar to the GRANT command.

REVOKE privileges ON object FROM user;

That brings an end to our article. I hope you have enjoyed it and were able to learn something from it. Please comment with your thought below and support us by sharing the article with your friends and colleagues.

Deepak Jose @deepakjose

View Profile