DBMS LAB Part-A 3

Ex 3:
To create table client_master using SQL command to store CLIENT_NO, NAME, ADDRESS1, ADDRESS2, CITY, PINCODE, STATE, BAL_DUE.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. For every client in client_master table decrease bal_due by 10%.

SQL> UPDATE CLIENT_MASTER
    SET BAL_DUE = BAL_DUE + (BAL_DUE*0.1);

       10 rows updated.

2. Update the table client_master, change the contents of the field name to ‘vijay kadam’ and the contents of the field address to ‘SCT Jay Apartment’ for the record with client_no=’C01002’.

    UPDATE CLIENT_MASTER SET
     NAME='VIJAY KADAM',
     ADDRESS1='SCT JAY APT'
     WHERE CLIENT_NO='C01002';

       1 row updated.

SQL> SELECT * FROM CLIENT_MASTER;

CLIENT      NAME        ADDRESS1          ADDRESS2   CITY      PINCODE     STATE               BAL_DUE
------------------------------------------------------------------------------------------------------------------------------
C01001      ANIS        MADI             BEJAI       UDUPI  574144      KARNARTAKA        9000

C01002     VIJAY KADAM        SCT JAY APT       NAGAR        UDUPI  671125       KERALA                   13500

C01003      GURU        SERE             KOTTARA   UPUPI   562232      KARNARTAKA       9900

C01004      DHANRAJ     KS               BEJAI           UDUPI   575003      KARNARTAKA        13500

C01005      MANOJ       PANDESHWAR        YAKKAR     UDUPI   567651      KARNARTAKA       12150

C01006      RAKSHITH    PERMUDE          BCROAD     UDUPI   575223      KARNARTAKA       16200

C01007      LIKHIN      MAROLI             BEJAI          UDUPI   567876      KARNARTAKA         11160

C01008      RAKSHAN     MALALI             SULYA        UDUPI   574144      KARNARTAKA         5000

C01009      NISHANK     POLALI             PADAV       UDUPI   564333      KARNARTAKA         17100

C01010      BHARATH     GANDHINAG        BAJPE         UDUPI   475767      GOA                            9900

         10 rows selected.

3. Add a new column name penalty number(10,2)to the table client_master.

SQL> ALTER TABLE CLIENT_MASTER ADD PENALTY NUMBER(10,2);

        Table altered.

SQL> DESC CLIENT_MASTER;

Name        Null?    Type
------------------------------------------------------
CLIENT_NO    NOT NULL    VARCHAR2(6)
NAME         NOT NULL    VARCHAR2(20)
ADDRESS1     NOT NULL    VARCHAR2(30)
ADDRESS2     NOT NULL    VARCHAR2(30)
CITY         NOT NULL    VARCHAR2(15)
PINCODE      NOT NULL    NUMBER(8)
STATE        NOT NULL    VARCHAR2(15)
BAL_DUE      NOT NULL    NUMBER(10,2)
PENALTY      NOT NULL    NUMBER(10,2)

4. Change the size of the column penalty to(8,2) in the client_master.

SQL> ALTER TABLE CLIENT_MASTER MODIFY PENALTY NUMBER(8,2);

          Table altered.

SQL> DESC CLIENT_MASTER;

Name        Null?     Type
-----------------------------------------------------
CLIENT_NO    NOT NULL   VARCHAR2(6)
NAME         NOT NULL   VARCHAR2(20)
ADDRESS1     NOT NULL   VARCHAR2(30)
ADDRESS2     NOT NULL   VARCHAR2(30)
CITY         NOT NULL   VARCHAR2(15)
PINCODE        NOT NULL   NUMBER(8)
STATE             NOT NULL   VARCHAR2(15)
BAL_DUE        NOT NULL   NUMBER(10,2)
PENALTY       NOT NULL   NUMBER(8,2)

5. Change the name of the table Client_master to client_master1.

SQL> RENAME CLIENT_MASTER TO CLIENT_MASTER1;

         Table renamed.

6. Delete all the newly created table.

SQL> DROP TABLE CLIENT_MASTER1;