SQL Statements

SQL Statements

SQL statements update, manipulate and maintain the data in a database, or retrieve data from a database. Some of the common statements that we use are DDL and DML statements.

The basic SELECT, UPDATE and DELETE statements are used extensively by developers in integration and deployment of even industry-level applications.

The CRUD operations which we perform extensively are also done using the SQL statements.

Demo Database

Let us know have a look at our demo database – IBM.

Query:

SELECT * FROM IBM;

Uses of SQL Statements

Some of the uses of SQL statements are as follows:

1. Allow us to perform CRUD operations.
2. Provides us with the flexibility to maintain and create the database.
3. DML statements help us to manipulate the data.
4. DDL statements help us to declare the data.

Types of SQL Statements

SQL works by running the commands provided by the user. We can divide the SQL commands into five distinctions based on the functionality they provide:

1. DDL (Data Definition Language)

   DDL is used to define the structure or schema of the database and to specify additional properties of the data.

   These statements define the implementation of the schema and are mostly hidden from the user, also the data stored in the database is made to follow certain constraints.

   Some of the DDL commands are as follows :

   1. Create

      To create objects in the database

      CREATE TABLE tableName(
      col1datatype,
      col2datatype,
      col3datatype…..);
      

      Example:

      Let us now see the query to create our IBM database.

      CREATE TABLE IBM(
      emp_idvarchar(5),
      namevarchar(50),
      locationvarchar(50),
      experienceint);
      

   2. Alter in SQL

      Alters the structure of the database

   Syntax

   ALTER TABLE tableName

   ADD columnNamedatatype;

   Example:

   ALTER TABLE IBM

   ADD emp_idvarchar(5);

   Drop in SQL

   Delete objects from the database

   Syntax:

   DROP TABLE tableName;

   Example: Let us now see how to drop the table IBM.

   Query:

   DROP TABLE IBM;

2. Truncate in SQL

   Deletes all the data stored in a table

   Syntax:

   Example: Let us now see how to truncate a table.

Query:

TRUNCATE TABLE IBM;

3. DML is used for managing data contained in the schema objects. DML’s can be divided into two subtypes:

• Procedural DML:

  In these types of DML’s we need to specify the type of data we need and how to get that data.

• Declarative or Non-Procedural DML:

  Here, we only need to know what data is required, but the specifications on the process to get the data is not required. The database, on its own, figures out how to get the data efficiently.

Some of DML statements are as follows:

1. Insert in SQL

   Used to insert data in the database

Syntax:

	INSERT INTO TABLE_NAME(col1, col2, col3,...)
VALUES(val1, val2, val3,...);
Example: Let us now try to insert a record in our database.

Query:

INSERT INTO IBM
VALUE('I33','Ram','Pune',4);
SELECT * FROM IBM;

2. Update in SQL

   Used to modify the existing data in the database

   Syntax:

   	UPDATE tableName
   SET col1 = val1, col2 = val2...., 
   WHERE condition;
   Example: Let us run an update query on our table IBM.
   

   Query:

   UPDATE IBM
   SET emp_id = 'A33' 
   WHERE name='Rajan';
   

   Delete data in SQL

   Used to delete all the records contained

   Syntax:

   DELETE FROM tableName
   WHERE condition;
   Example: Let us now try to delete the row corresponding to emp_id ‘A33’.
   

   Query:

   DELETE FROM IBM
   WHERE emp_id = ‘A33’;
   

4. DQL (Data Query Language)

   Some of the DQL statements are as follows:

1. Select in SQL

   Used to view the database.

Syntax:

SELECT * FROM tableName;

Example: Let us view our database – DataFlair.

Query:

SELECT * FROM IBM;

5. TCL (Transaction Control Language)

   TCL commands are used to handle transactions in the database. These statements come in handy to manage and track the changes being made by the DML queries on our database.

   Some of TCL statements are as follows :

1. Commit in SQL

   Used to permanently save any transaction into the database.

   Syntax:

   Commit;

   Example: Let us now save the transaction using commit.

   Query:

   Commit;

2. Rollback in SQL

   Used to restore the database to the last committed state and is also used with the Savepoint command to move over to any saved state in a transaction.

Syntax:

ROLLBACK TO savedTransaction;

Example: Let us now move back to the saved state.

Query:

ROLLBACK TO initialState;

3. Savepoint in SQL

   It is used to temporarily store a transaction so that we can move to the point we need whenever desired.

Syntax:

SAVEPOINT pointName;

Example: Let us now save the present state of database transaction.

Query:

SAVEPOINT initialState;

E. DCL (Data Control Language)

DCL commands are similar to the computer programming language in syntax.

They are a component of the Structured Query Language while using DCL commands we need to be cautious as some databases don’t allow rollback command on the DCL command.

One such example is the Oracle database – when we execute DCL on an oracle database it results in an implicit commit and thus rollback is not allowed.

Some of the DCL statements are as follows:

1. Grant in SQL

   Used to allow a specific set of users to perform some specified tasks.

   Syntax:

   GRANT privilege 
   ON privilege_level
   TO account_name;
   

   Example: Let us now provide the update access to the user.

   Query:

   GRANT UPDATE
   ON IBM
   TO user;
   

2. Revoke in SQL

   Used to cancel all the previously granted or denied permissions.

Syntax:

REVOKE privilegeName
ON tableName
FROM userName;

Example: Let us now revoke the update privilege from the user.

Query:

REVOKE Update
ON IBM
FROM user;