A Relational Database Management System (RDBMS) is a software program that provides users with the tools to perform various operations on the database. Some of the key operations include:

• Creating- Establishing the database structure and defining tables.
• Inserting- Adding records into the tables.
• Selecting- Retrieving and displaying records from the tables.
• Updating- Modifying existing records.
• Deleting- Removing records from the database.

RDBMS plays a crucial role in managing the structured storage of data and ensuring that it is efficiently manipulated and retrieved.

SQL (Structured Query Language) is the standard language used by every RDBMS (Relational Database Management System) to interact with databases. It allows users to perform operations such as:

• Inserting data into a database
• Selecting data from a database
• Modifying data within the database

Categories of SQL Commands

SQL commands are broadly categorized into five groups:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DQL (Data Query Language)
• DCL (Data Control Language)
• TCL (Transaction Control Language)

Data Definition Language (DDL)

DDL commands are used for defining and modifying the structure of a database, such as creating, deleting, or altering tables. These commands automatically commit the changes made to the database.

Key DDL Commands:

• CREATE TABLE: Used to create a new table in the database.

      
      CREATE TABLE 
      employees ( 
      id INT, 
      name VARCHAR(100), 
      department VARCHAR(50), 
      hire_date DATE 
      )
      

• ALTER TABLE: Used to modify the structure of an existing table, such as adding or removing columns.

              
      ALTER TABLE employees ADD salary DECIMAL(10, 2);
            

• TRUNCATE TABLE: Used to delete all rows from a table and free the space allocated to the table.

              
      TRUNCATE TABLE employees;
            

• DROP TABLE: Used to delete an entire table and all the data stored in it from the database.

              
      DROP TABLE employees;
            

Data Manipulation Language (DML)

DML commands are used for modifying the data within the tables. These commands are not auto-committed, meaning that changes made with DML commands are not permanently saved until explicitly committed.

Key DML Commands:

• INSERT: Used to insert a new row into a table.

      
      INSERT INTO 
      employees 
      (id, name, department, hire_date) 
      VALUES (1, 
      'John Doe', 
      'HR', 
      '2023-09-01'); 
    

• UPDATE: Used to update the values of specific columns in existing rows.

      
      UPDATE 
      employees 
      SET department = 'Finance' 
      WHERE id = 1; 
    

• DELETE: Used to remove one or more rows from a table.

      
      DELETE FROM 
      employees 
      WHERE id = 1;
    

Data Query Language (DQL)

DQL commands are used to query and retrieve data from the database. The primary command in this category is SELECT.

Key DQL Command:

• SELECT: Fetches data from one or more tables based on the specified conditions.

      
      SELECT name, department FROM employees WHERE hire_date  
      '2022-01-01';
    

Data Control Language (DCL)

DCL commands are used to control access to the data within the database. These commands are responsible for granting and revoking access privileges to users.

Key DCL Commands:

• GRANT: Grants specific user permissions to access tables or perform certain operations.

       
      GRANT SELECT, INSERT 
      ON 
      employees 
      TO 
      'user_name';
     

• REVOKE: Removes previously granted permissions from a user.

       
      REVOKE INSERT 
      ON 
      employees 
      FROM 
      'user_name';
     

Transaction Control Language (TCL)

TCL commands manage transactions made by DML commands. These commands help ensure that all data manipulations are safely applied to the database and can be rolled back if necessary.

Key TCL Commands:

• COMMIT: Permanently saves all changes made by DML commands in the current transaction.

       
        COMMIT;
     

• ROLLBACK: Reverts all changes made by DML commands in the current transaction.

       
        ROLLBACK;
     

• SAVEPOINT: Creates a point within a transaction that you can roll back to without affecting the entire transaction.

       
      SAVEPOINT savepoint_name;
         

      ROLLBACK TO savepoint_name;
     

In SQL, each column in a table is associated with a data type that defines the kind of values that can be stored in that column. These data types set specific characteristics for the column, such as how the data will be treated and what operations can be performed on it.

For instance, we can perform arithmetic operations on values stored in the NUMBER data type, but not on those stored in the VARCHAR2 data type, which is used for text.

In Oracle, there are 12 major data types, grouped into six different categories:

Categories of Oracle Data Types

1. Character Data Types

Character data types are used to store alphanumeric values (letters, numbers, and special characters). Oracle supports several character data types, with the most commonly used being:

• VARCHAR or VARCHAR2: Stores variable-length strings and is recommended for storing text data.

      
        CREATE TABLE employees (
        name VARCHAR2(100) or VARCHAR(100)
        ); 
    

• CHAR: Stores fixed-length strings, padded with spaces if shorter than the defined length.

      
        CREATE TABLE departments (
        dept_code CHAR(5)
        );
    

• LONG: Stores variable-length character data up to 2 GB. Considered obsolete and replaced by CLOB.

2. Numeric Data Types

Numeric data types allow you to store integers, floating-point numbers, and real numbers. The NUMBER data type is the most versatile in Oracle:

• NUMBER: Can store both integers and floating-point numbers with optional precision and scale.

      
        CREATE TABLE products (
        price NUMBER(8, 2)
        );
    

3. Date Data Types

Oracle uses the DATE data type to store both date and time information, including the century, year, month, day, hour, minute, and second.

• DATE: The format for DATE in Oracle is DD-MM-YYYY HH:MI:SS, but it can be customized. Widely used to store timestamps.

      
        CREATE TABLE orders (
        order_date DATE
        ); 
    

4. LOB (Large Object) Data Types

LOB data types store large volumes of data, such as text, images, and multimedia:

• BLOB: Stores large binary objects like images, audio, or video.
• CLOB: Stores large amounts of character data.
• BFILE: Stores a file reference (a pointer) to a file stored externally.

5. Binary Data Types

Binary data types allow the storage of raw binary data (like files or encryption data):

• RAW: Stores binary data in variable length.
• LONG RAW: Stores large binary data, similar to RAW, but up to 2 GB. Now considered obsolete.

6. Row Identifier Data Types

Each row in an Oracle table is identified by a unique address or ROWID, which is useful for locating specific rows within a database:

      
        SELECT ROWID, name 
        FROM employees;