DBMS LAB Part-A 2

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

Create table client_master.

SQL> CREATE TABLE CLIENT_MASTER(
     CLIENT_NO VARCHAR2(6) PRIMARY KEY,
     NAME VARCHAR2(20) NOT NULL,
     ADDRESS1 VARCHAR2(30) NOT NULL,
     ADDRESS2 VARCHAR2(30) NOT NULL,
     CITY VARCHAR2(15) NOT NULL,
     PINCODE NUMBER(8) NOT NULL,
     STATE VARCHAR2(15) NOT NULL,
     BAL_DUE NUMBER(10,2) NOT NULL);

Insert values into table
SQL> INSERT INTO CLIENT_MASTER                     
          VALUES('C01001','ALIS','MADI','BEJAI','MANGALORE','574144      
         ','KARNATAKA','10000');

1 row created.

Display information about all Employees:

SQL> SELECT * FROM CLIENT_MASTER;

CLIENT    NAME       ADDRESS1     ADDRESS2     CITY                   PINCODE STATE                BAL_DUE
C01001    ALIS           MADI                 BEJAI            MANGALORE   574144    KARNATAKA    10000

C01002    HARI         KASAR              NAGAR        KASARGOD        671125    KERALA               15000

C01003    GURU        SERE                 G.NAGAR     VAMANJOOR    562235    KARNATAKA   11000

C01004    SHIVRAJ   KS                      BEJAI             MANGALORE   575003    KARNATAKA   15000

C01005    MAYUR     PANDE            YAKKAR        MANGALORE    567651   KARNATAKA   13500

C01006    RAMESH    PERMUDE     BCROAD       MANGALORE    575223    KARNATAKA  18000

C01007    LIKHIT       MAROLI         BEJAI            MANGALORE      567876    KARNATAKA  12400

C01008    RAKESH    MALALI         SURALPAD   MANGALORE     574144    KARNATAKA  564333

C01009    NAMAN     POLALI          PADAVU        UDUPI                  564333    KARNATAKA  19000

C01010    BOBBY     G.NAGAR         BAJPE           KASARGOD          475767    GOA                  11000

10 rows selected.

1. Display the structure of the client_master.

SQL> describe client_master1;

     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 NULLNUMBER(8)
     STATE             NOT NULL VARCHAR2(15)
     BAL_DUE        NOT NULL NUMBER(10,2)

2. From the table client_master create a new table client1 that contains only client number and name with all records of client_master.

SQL> CREATE TABLE CLIENT1(CLIENT_NO,NAME)
           AS SELECT CLIENT_NO,NAME
            FROM CLIENT_MASTER;

Table created.

SQL> SELECT * FROM CLIENT1;

CLIENT   NAME
---------------------
C01001   ALIS
C01002   HARI
C01003   GURU
C01004   SHIVRAJ
C01005   MAYUR
C01006   RAMESH
C01007   LIKHIT
C01008   RAKESH
C01009   NAMAN
C01010   BOBBY

10 rows selected.

3. From the table client_master create a new table client2 that has a same structure as client_master but with no records.

SQL> CREATE TABLE
CLIENT2(CLIENT_NO,NAME,ADDRESS1,ADDRESS2,CITY,PINCODE,STATE, BAL_DUE)
AS SELECT * FROM CLIENT_MASTER
WHERE 1=2;

Table created.

SQL> SELECT * FROM CLIENT2;

No rows selected

4. Insert record into client3, from the client_master table where client_no is     ‘C01001’

SQL> CREATE TABLE
CLIENT3(CLIENT_NO,NAME,ADDRESS1,ADDRESS2,CITY,PINCODE,STATE,BAL_DUE)
AS SELECT * FROM CLIENT_MASTER
WHERE CLIENT_NO='C01001';

Table created.

SQL> SELECT * FROM CLIENT3;

CLIENT    NAME       ADDRESS1     ADDRESS2     CITY                   PINCODE STATE                BAL_DUE
C01001    ALIS           MADI                 BEJAI            MANGALORE   574144    KARNATAKA    10000

5. Delete all records of table client1

SQL> DELETE FROM CLIENT1;

10 rows deleted.

SQL> SELECT * FROM CLIENT1;

No rows selected