Wednesday, April 15, 2020

Mastering RDBMS: Understanding the Types of Languages

RDBMS:
  • RDBMS is the abbreviation for ‘Relational DataBase Management System’.
      Any database system consists of two parts. They are
         a) Database Management system
         b) Database Application
  • The Database management system is required to organize and maintain the data.
  • The Database Application is a program to retrieve, insert and update the data in the system.

SERVICES OF DBMS
      A DBMS provides the following services to do the activities mentioned previously. Let us see some of the services.
   1) Data Definition
           This is a method of defining data and its storage.
   2) Data Maintenance
          This method verifies whether each record has fields containing all information as defined using data definition.
   3) Data Manipulation
          Data Manipulation allows to insert, update, delete and sorting of data in the database.
   4) Data Display
         Data Display allows viewing the data in the database.
  5) Data Integrity
        This method verifies the integrity or accuracy of data stored in the database.

DATA TYPES
  •  Data are stored in a table which is a collection of fields. Oracle supports the following data types.

A. Character data types
   1. CHAR
       Char data type is used when a fixed-length character string is required. Up to 2000 bytes can be stored in this data type. The default value is one byte.
    Syntax
        CHAR (size)
    Example: CHAR (20)

  2. VARCHAR2
       This data type is used to store variable-length character strings. It can also store alphanumerical values. The size of these data types varies from 1 to 4000 bytes.
Syntax
     VARCHAR2 (size)

 3. LONG
      Cells of this data type can store variable character strings up to 2GB. Binary data in ASCII format can also be used in such data types. Based on the memory space available on the computer, its length would be restricted.
NOTE:
   a) Only one column in a table can have this data type.
   b) This column should not contain a unique or primary key constraint.
   c) This column can’t be indexed
   d) This data type can’t be used as an argument in any procedure.

B. Number Data types
  1. NUMBER
       A number data type is used to declare a column to store only numbers. This type supports the storage of integers, real numbers, floating-point numbers, etc.
Example:
      Salary Number
      Age Number

C. Date Data Type
        All types of time-related information, including date and associated time, can be stored in a column declared with the date data type.
Syntax
        DATE
Example:
        Date_of_birth DATE

DATA DEFINITION LANGUAGE
      The Data Definition Language is used for the following purposes.
         1. To create an object
         2. To alter the structure of an object
         3. To drop an object
Note: Some of the database objects are Table, View, Sequence, Cursor, etc.

DDL used for a table
       The DDL used for table definition is classified as
        1. Create table command
        2. Alter table command
       3. Truncate table command
       4. Drop table command

1. CREATING A TABLE
Syntax
        CREATE TABLE <table name> (column1 datatype1,…, column data type n);

Example
         CREATE TABLE STUDENT_MASTER (Roll_No NUMBER (4), Name VARCHAR2 (15), Sex VARCHAR2 (6), DOB DATE, DOAD DATE, Class VARCHAR2 (6), Guardian VARCHAR2 (30), Address VARCHAR2 (100));

      If the above command is executed successfully, the message “table created” is displayed.

Important points:
     a) The first letter should be an alphabet
     b) Oracle reserve words cannot be used as table name
     c) The length of a table should not exceed 30 characters
    d) Different tables should have different names.
    e) Underscore, numerals and letters are allowed. Blank space and single quotes are not allowed.
    f) Double quotes can be used for naming a table.

2. ALTERING A TABLE
     The structure of a table can be changed using this command.
   a) Add a new column
   b) Change the width of a data type
   c) Change the data type of a column
   d) Include an integrity constraint
   e) Drop an integrity constraint

Syntax
    ALTER TABLE <table name>MODIFY (column datatype,…);
    ALTER TABLE <table name>ADD (column datatype,….);

Restrictions in altering a table
      The following are the major restrictions in altering a table
a) Cannot change the name of a column
b) Cannot drop a column from the table
c) Cannot change the name of a table
d) Cannot decrease the size of a column if the table is non-empty.
e) Cannot change the data type if the column contains data that conflicts with the data type.
f) Cannot add constraints if the data in the table violates the constraint.

Example:
     ALTER TABLE student_master MODIFY (name VARCHAR2 (25));
     ALTER TABLE student_master ADD (tel_no NUMBER(10));

3. TRUNCATING A TABLE
Syntax
     TRUNCATE TABLE < table_name>;

This command is used to delete all records stored in a table. If we use this command, the structure of the table is retained.

Example:
       TRUNCATE TABLE student_master;

4. DROPPING A TABLE
   This command is used to remove a table from the database.
Syntax
    DROP TABLE <table name>;

Note:
   We can’t drop a table if it is referenced by an existing table through a foreign key.

Data Manipulation Language (DML)
        Data manipulation languages are used in the query and to manipulate the existing objects in the database like tables. The DML commands are
      1. Insert command
      2. Select command
      3. Update command
      4. Delete command

1. Insert command
   The insert command is used to add one or more rows to a table.

Syntax
     INSERT INTO <table name> VALUES (list of values);

Note:
  The values are separated by commas
  Values of data type char, varchar2, raw, long and date must be enclosed in single quotes.
 The values must be entered in the same order as they appear in the table.

a) Inserting a row by entering all values for the columns defined in a table
Syntax
      INSERT INTO <table name> VALUES (value1,value2,…);
Example:
      INSERT INTO student_master VALUES (1001,’ R.BALU’,’ MALE’,’01-JAN-00’,
       ’11-JAN-03’,’CIVIL’, ’M.RAGU’,’28, Main Road, Nagercoil’);

b) Inserting values only for some particular columns
Syntax
     INSERT INTO <table name> (column1, column2…) VALUES (value1, value2,..);

Example:
     INSERT INTO student_master (roll_no, name) VALUES (2001,’ J.ARUN’);

c) Entering a large number of rows
     INSERT INTO staff_master
     VALUES (&staff_id,’&subject’,’&name’,’&doap’,’&street’,’&city’,’&pin’);

2. Select command
    The select command is used to retrieve the stored data from a table.
Syntax
    SELECT * FROM <table_name>;
                    (or)
    SELECT column1,column2,.. FROM <table_name>;

Selecting distinct rows
     To avoid the selection of duplicate rows, the distinct clause is used in the select statement.
Syntax
      SELECT DISTINCT field_name FROM <table_name>;

Selecting specific rows
    To select specific rows from a table, where clause is used in the select statement after the from clause.

Syntax
     SELECT column1,column2,.. FROM <table_name> WHERE <condition> ORDER BY column DESC

3. Update command
    The update command is used to update (changing values in one or two columns of a row) rows in a table. Specific rows can also be updated based on some conditions.
Syntax
      UPDATE <table_name> SET column1=expression,
      Column2=expression…WHERE <search_condition>

4. Delete command
     The delete command is used to delete rows from a table.
Syntax
     DELETE FROM <table_name> WHERE <searchcondition>;

Deletion of the specific row
     DELETE FROM staff_master WHERE name=’E. Suriya’;

Deletion of all rows
     DELETE FROM staff_master;

Transaction Control Language (TCL)
         All changes made to the database are defined as a transaction. It is a logical unit of work. The transaction can be made permanent in a database only if they are committed.
         The two transaction control commands are
             1. Commit
             2. Rollback

1. Commit
   The commit command is used to make all transaction changes permanent to the database.
   This command erases all savepoints and releases the transaction lock.
Syntax
    Commit;

2. Rollback
     The rollback command is used to undo all the changes made in the current transaction. We can either roll back the entire transaction or roll back a transaction to a savepoint.
Syntax
    Rollback;

No comments:

Post a Comment