Sunday, October 2, 2011

Basic SQL Tips


CREAT A TABLE
--------------
CREATE TABLE EMPLOYEE (EMPID  INT, EMPNAME CHAR(10),EMPADD CHAR(20))

CREATE TABLE EMPTAB2(EMPID  INT, EMPNAME CHAR(10),EMPADD CHAR(20))

INSERT THE VALUE
----------------
INSERT INTO EMPTAB2 VALUES(100,'VIDYA BHUSHAN','MADHUBANI','98717103
 67')  

 INSERT INTO EMPTAB2 VALUES(100,'VIDYA BHUSHAN','MADHUBANI','98717103
 67')            
                                                 
RETRIVE THE RECORDS
--------------------
SELECT * FROM KMGEDU5/EMPTAB2

UPDATE THE RECORDS
------------------
UPDATE KMGEDU5/EMPTAB2 SET EMPMOB='99999999' WHERE EMPID=100
                             
SELECT * FROM KMGEDU5/EMPTAB2

DELETE THE RECORDS
------------------
DELETE EMPADD FROM AMGEDU5/EMPTAB2 WHERE EMPID=100

DELETE EMPMOB FROM KMGEDU5/EMPTAB2 WHERE EMPID=100

INSERT THE RECORDS
------------------                                                                    
INSERT INTO KMGEDU5/EMPTAB2 VALUES(100, 'VIDYA' ,'MADHUBANI','987171
0367')

UPDATE THE RECORDS
-------------------
UPDATE EMPTAB2 SET EMPMOB= ' ' WHERE EMPID =100

RETRIVE THE RECORDS
-------------------
SELECT * FROM EMPTAB2

CREATE THE TABLE
-----------------
CREATE TABLE KMGEDU5/EMP (EMPNAME FOR COLUMN EMPL_NAME VARCHAR (25
), EMPSAL FOR COLUMN EMPL_SAL DECIMAL (9 , 2) NOT NULL, EMPADD FOR
COLUMN EMPL_ADD VARCHAR (25 ), EMPDES FOR COLUMN EMPL_DESGN VARCHAR
(25 ), EMPACNO FOR COLUMN EMPL_ACCNT DECIMAL (14 ))

INSERT THE VALUES
-----------------
INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('VIDYA BHUSHAN THAKUR', 10000, 'NOIDA', 'S/W TRAINEE', 27262)

INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('VIDYA BHUSHAN THAKUR', 10000, 'NOIDA', 'S/W TRAINEE', 27262)

INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('SAURABH RAI', 12000.50, 'BINA INDORE', 'SW TRAINEE',    
225632)                                                          

INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('GUNJAN GUPTA, 12200.50, 'LOCKNOW', 'SW TRAINEE',          
256632)                                                          

 INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
 VALUES('GUNJAN GUPTA', 12500.00, 'LOCKNOW', 'SW TRAINEE', 2567891)

 INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
 VALUES('KIRAN REDDY', 12500.00, 'HYDRABAD, 'SW TRAINEE', 2567891)

UPDATE THE VALUES
-----------------
UPDATE KMGEDU5/EMP EMPNAME SET EMPNAME = 'GUNJAN GUPTA', EMPSAL =
15000.00 WHERE EMPSAL >= 10000                                                                  

2 comments:

  1. Hi, I have one question.. sapose I use of Batch job.. and meanwhile one error occurred that is DATA DECIMAL error. so what will step you follow for know in which program DATA DECIMAL error has been error occured ?

    ReplyDelete
  2. Just check the job in MSGW and with option 7 check the error. For detail look into the job log with option 10 or . Check the compiler listing for statement where error occoured. Data decimal errors are when we are assigning some value to filed with incorrect data or forget to initialize the standalone field or data structure we are using to assign the value.

    ReplyDelete