SQL Statements: A Comprehensive Guide

SQL Statements/Commands

• SQL commands are instructions. It is used to communicate with the database.

• It is also used to perform specific tasks, functions, and queries of data.

• SQL can perform various tasks like creating tables, adding data to tables, dropping the tables, modifying the tables, setting permission for users, etc.

Types of SQL Commands

There are five types of SQL statements or commands: DDLs DMLs DCLs TCLs and DQL.

1. Data Definition Language (DDL)

• DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.

• All the commands of DDL are auto-committed which means it permanently saves all the changes done in the database.

Here are some commands that come under DDL:

1. ALTER

2. CREATE

3. DROP

4. TRUNCATE

1. CREATE:- It is used to create a new table in the database.

a. Syntax:

        CREATE TABLE TABLE_NAME

      ( COLUMN_NAME_1 DATATYPES(size)s,

        COLUMN_NAME_2 DATATYPES(size)s,

        .

    .

    COLUMN_NAME_N DATATYPES(size)s );

            b. Example:

                              CREATE TABLE EMP

                              ( EMPNo VARCHAR2(20)s,

                                EName VARCHAR2(20)s,

                                Job VARCHAR2(20)s,

                                DOB DATE );

2. DROP:- This statement is used to drop an existing database. When you use this statement, complete information present in the database will be lost.

            a. Syntax:

                           DROP DATABASE DatabaseName;

            b. Example:

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

           a. Syntax:

                         DROP TABLE TableName;

            b. Example:

                         DROP Table Emp;

3. 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, and modify columns in an existing table.

            The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN

            You can use the ALTER TABLE statement with the ADD/DROP Column command
according to your needs. 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.

            a. Syntax:

                            ALTER TABLE TableName ADD ColumnName Datatype;

                            ALTER TABLE TableName DROP COLUMN ColumnName;

            b. Example:

• ADD Column MobNo:

                         ALTER TABLE Emp ADD MobNo Number(10);

• DROP Column MobNo:

                         ALTER TABLE Emp DROP COLUMN MobNo ;

            The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN

            This statement is used to change the datatype of an existing column in a table.

            

            a. Syntax:

                         ALTER TABLE TableName ADD COLUMN ColumnName Datatype;

            b. Example:

• Add a column DOB and change the data type to Date.

                         ALTER TABLE Emp ADD DOB date;

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

                a. Syntax:

                             TRUNCATE TABLE table_name;

                b. Example:

                             TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

• DML commands are used to modify the database. It is responsible for all forms of changes in the database.

• The command of DML is not auto-committed which means it can't permanently save all the changes in the database. They can be rolled back.

Here are some commands that come under DML:

1. INSERT

2. UPDATE

3. DELETE

1. INSERT: The INSERT statement is an SQL query. It is used to insert data into the
   row of a table.
   
               a. Syntax:
                   INSERT INTO TABLE_NAME
         (col1, col2, col3,.... coln)
         VALUES (value1, value2, value3, .... valueN);
                             Or
                 INSERT INTO TABLE_NAME
         VALUES (value1, value2, value3, .... valueN);
   
               b. Example:
       INSERT INTO EMP(ENames Job) VALUES ("SCOTT", "MANAGER");
   
   
   
   

2. UPDATE: This command is used to update or modify the value of a column in the table.
   
   
             a. Syntax:
                         UPDATE table_name SET column1= values column2= values
                         columnN = value WHERE CONDITION;
             b. Example:
                         UPDATE Emp SET Ename = 'SMITH' WHERE EmpNo = '1003';

3. DELETE: It is used to remove one or more rows from a table.
   
   
             Syntax1:
                           DELETE FROM table_name;
   
   
             Syntax2:
                           DELETE FROM table_name WHERE condition;
   
   
             Example1: Delete all rows from the emp table
                           DELETE FROM Emp;
   
   
             Example2: Delete all rows from the emp table whose Ename is SCOTT
                           DELETE FROM EName WHERE EName="SCOTT";
   
   

3. Data Control Language

• DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

1. GRANT

2. REVOKE

1. GRANT: It is used to give user access privileges to a database.

              Example:

              GRANT SELECTs UPDATE ON MY_TABLE TO SOME_USERs ANOTHER_USER;

2. REVOKE: It is used to test bacs permissions from the user.

              Example:

              REVOKE SELECTs UPDATE ON MY_TABLE FROM USER1s USER2;

4. Transaction Control Language

• TCL commands can only use with DML commands lise INSERTs DELETE and
  UPDATE only.

• These operations are automatically committed in the database that's why
  they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

1. COMMIT

2. ROLLBACK

3. SAVEPOINT

1. Commit: The commit command is used to save all the transactions to the
   Database.

1. Syntax:

 COMMIT;

2. Example:

 DELETE FROM CUSTOMERS WHERE AGE = 25;

 COMMIT;

     02. Rollback: The rollback command is used to undo transactions that have not
already been saved to the database.

                a. Syntax:

 ROLLBACK;

     b. Example:

 DELETE FROM CUSTOMERS WHERE AGE = 25;

 ROLLBACK;

      03. SAVEPOINT: It is used to roll the transaction back to a certain point
without rolling back the entire transaction.

1. Syntax:

 SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

• DQL is used to fetch the data from the database.

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;

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.

Syntax:

 SELECT DISTINCT Column1, Column2, ...ColumnN FROM TableName;

Example:

SELECT DISTINCT MobNo FROM Emp;

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

Syntax:

 SELECT Column1, Column2, ...ColumnN FROM TableName

 ORDER BY Column1, Column2, ... ASC|DESC;

Example:

1. Select all employees from the 'Emp’ table sorted by EmpNo:

 SELECT * FROM Emp ORDER BY EmpNo;

2. Select all employees from the 'Emp table sorted by EmpNo in Descending order:

 SELECT * FROM Employee_Info ORDER BY EmpNo DESC;

3. Select all employees from the 'Empl’ table sorted by EmpNo and EName:

 SELECT * FROM Emp ORDER BY EmpNo, EName;

4. Select all employees from the 'Emp' table sorted bsoEmpNo in Descending order and Ename in Ascending order: 

 SELECT * FROM Emp ORDER BY EmpNo ASC, Ename 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.

Syntax:

 SELECT 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(EmpNo), City FROM Emp GROUP BY City

• The ‘HAVING’ Clause

The ‘HAVING’ clause is used in SQL because the WHERE keyword cannot be
used everywhere.

Syntax:

 SELECT 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 from high to low and only those cities must be included who have more than 5 employees:

 SELECT COUNT(EmpNo), City FROM Emp GROUP BY City HAVING
COUNT(EmpNo) > 2 ORDER BY COUNT(EmpNo) DESC;

• The ‘SELECT INTO’ Statement

The ‘SELECT INTO’ statement is used to copy data from one table to
another.

Syntax:

 SELECT * INTO NewTable IN ExternalDB FROM OldTable WHERE
Condition;

Example:

•  To create a backup of the database 'Employee'

 SELECT * INTO EmpNo FROM Emp