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

    3. 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.

  3. Query:

    TRUNCATE TABLE IBM;

  4. 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

    2. 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;
      
    3. 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’;
      
  5. DQL (Data Query Language)

    Some of the DQL statements are as follows:

    1. Select in SQL

      Used to view the database.

    2. Syntax:

      SELECT * FROM tableName;

      Example: Let us view our database – DataFlair.

      Query:

      SELECT * FROM IBM;

  6. 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.

    3. Syntax:

      ROLLBACK TO savedTransaction;

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

      Query:

      ROLLBACK TO initialState;

    4. Savepoint in SQL

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

    5. 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.

  3. 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;
    


Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.