<aside> <img src="/icons/table_red.svg" alt="/icons/table_red.svg" width="40px" /> Table of Contents

</aside>

Data Manipulation Language (DML)   ●       INSERT ●       UPDATE ●       DELETE ●       SELECT   Data Control Language (DCL)   ●       GRANT ●       REVOKE ●       ROLES | 09-09-24 | | | | 2 | SQL SELECT Statements   ●       Selecting All Columns ●       Selecting Specific Columns ●       Column Alias ●       Concatenation Operator ●       Arithmetic Operators ●       Comparison Conditions ●       Logical Conditions ●       ORDER BY Clause | 19-9-24 | | | | 3 | Functions   ●       Single Row Functions: ●       Character Functions ●         Number Functions ●       Date Functions, Conversion ●       Functions ●       General Functions ●       Multiple Row Functions | 25-10-24 | | | | 4 | Constraints   ●       Not Null ●       Unique Key ●       Primary Key ●       Foreign Key ●       Check ●       Dropping a Constraint ●       Enabling & Disabling   Subquery   ●       Group ●       Function ●       Having Clause | 09-09-24                   11-11-24 | | | | 5 | Joins   ●       Equijoins ●       Non-Equijoins ●       Joining Three Tables ●       Self Joins, Left Outer Joins ●       Right Outer Joins ●       Full Outer Joins ●       Cross Joins ●       Natural Joins | 11-11-24 | | | | 6 | Sequence, View, Index, Synonyms | | | | | 7 | PL/SQL Practical Programming   ●       Variable ●       Identifiers ●       Comment ●       PL/SQL Block Structure   IF Statements

● Simple IF Statements ● Compound IF Statements ● IF-THEN-ELSE Statements | 21-11-24 | | | | 8 | Loop   ●       BasicLoop ●       WHILE Loop ●         FOR Loop   Transaction Control   ●         Commit ●       Rollback ●       Save point | 21-11-24       07-10-24 | | | | 10 | Cursor   ●       Types of Cursor ●       Explicit Cursor Life Cycle ●       Explicit Cursor Attributes | 25-11-24 | | | | 11 | Exceptions   ●       Block Structure ●       Exception Handlers ●       Types of Exceptions   Records   ●       Table-Based ●       Cursor-Based ●       Programmer-Defined | 25-11-24           25-11-24 | | | | 8 | Loop   ●       BasicLoop ●       WHILE Loop ●         FOR Loop   Transaction Control   ●         Commit ●       Rollback ●       Save point | 21-11-24       07-10-24 | | | | 10 | Cursor   ●       Types of Cursor ●       Explicit Cursor Life Cycle ●       Explicit Cursor Attributes | 25-11-24 | | | | 11 | Exceptions   ●       Block Structure ●       Exception Handlers ●       Types of Exceptions   Records   ●       Table-Based ●       Cursor-Based ●       Programmer-Defined | 25-11-24           25-11-24 | | | | 12 | Functions   ●       Create Function ●       Function with Arguments ●       Executing Function ●       Dropping Function | 25-11-24 | | | | 13 | Procedures   ●       Block Structure of Subprogram ●       Types of Subprograms ●       Procedure with Parameters ●       Executing Procedures ●       Dropping Procedures | 05-12-24 | | | | 14 | Trigger   ●       Trigger ●       Statement Trigger ●       Row Trigger ●       Using Conditional Operations ●       DML Operations | 25-11-24 | | | | 15 | Packages   ●       Package Specification ●       Package Body ●       Creating Packages ●       Execution ●       Dropping Packages | 05-12-24 | | |

Practical 1 - Introduction to Structured Query Language

1. Prerequisites

   1. DDL - Data Definition Language:

      Command	Description
      CREATE	Creates a new table, a view of a table, or other objects in database
      ALTER	Modifies an existing database object, such as a table.
      DROP	Deletes an entire table, a view of a table or other object in the database.

   2. DML - Data Manipulation Language:

      Command	Description
      INSERT	Creates a record
      UPDATE	Modifies records.
      DELETE	Deletes records.

   3. DML - Data Control Language:

      Command	Description
      GRANT	Gives a privilege to user.
      REVOKE	Takes back privileges granted from User.

   4. DML - Data Query Language:

      Command	Description
      SELECT	Retrives certain records from one or more tables.

2. To create a Table.

   1. Synatax:

      CREATE TABLE schema_name.table_name (
          column_1 data_type column_constraint,
          column_2 data_type column_constraint,
          ...
          table_constraint
      );
      

3. Create a relation CUSTOMERS with the following attributes.

   1. ID of integer type and primary key.

   2. Name 20 characters.

   3. Age integer.

   4. Address 25 characters.

   5. Salary floating point 10.2 assign a default value 5000.

   6. Solution Command:

      CREATE TABLE customers_1 (
          ID int primary key,
          cname varchar(20),
          age int,
          Address varchar(20),
          salary number(10, 3) default 1000.00
      );
      

   7. Solution Output:

      

4. Create a table SUPPLIER.

   1. Supplier_id.

   2. Supplier_name.

   3. Contact_name.

   4. Solution Command 1:

      CREATE TABLE supplier_1 (
          supplier_id int primary key,
          supplier_name varchar(20),
          contact varchar(20)
      );
      

   5. Solution Output 1:

      

   6. Solution Command 2:

      CREATE TABLE supplier_2 (
          supplier_id numeric(10),
          supplier_name varchar2(50),
          contact_name varchar2(50),
          CONSTRAINT supplier_pk_1 PRIMARY KEY(supplier_id)
      );
      

   7. Solution Output 2:

      

5. Create a table SUPPLIER, but with COMPOSITE KEY.

   1. Solution Command:

      CREATE TABLE supplier_3 (
          supplier_id numeric(10) not null,
          supplier_name varchar2(50) not null,
          contact_name varchar2(50),
          CONSTRAINT supplier_pk_3 PRIMARY KEY(supplier_id, supplier_name)
      );
      

   2. Solution Output:

      

6. Describe all the tables, which we’ve created above by using command DESCRIBE.

7. Character Datatypes:

8. Numeric Datatypes:

9. DATE/TIME Datatypes: