something i did to show my love towards SQL..^^

those are useful sql statements that are commonly used..

COMMIT;


SELECT * FROM TAB;


DESC TABLENAME;


SET LINESIZE 1000
SET PAGESIZE 1000
/
SELECT * FROM TABLENAME ORDER BY COLUMNNAME ASC;

CREATE VIEW VIEWNAME ( VIEWCOLUMN) AS
SELECT TABLECOLUMN FROM TABLE
WHERE TABLECOLUMN LIKE "%S" /*/*/ "_A%" /*/*/ IS NULL /*/*/ IS NOT NULL /*/*/ = 'STRING'
GROUP BY TABLECOLUMN;


SELECT * FROM VIEWNAME;

DELETE VIEWNAME WHERE VIEWCOLUMN = ' ';

INSERT INTO VIEWNAME VALUES ('ASDFSD','ASDF');

BETWEEN 'A' AND 'B'; BETWEEN 100 AND 200;

DELETE TABLE TABLENAME; /*CLEAR DATA*/ /*OR*/ TRUNCATE TABLE TABLENAME;

UPDATE TABLENAME SET COLUMNAME = 'APA' WHERE COLUMNAME = 'SIAPA';

CREATE TABLE TABLENAME(
COLUMNNAME VARCHAR2(10) CONSTRAINT A_PK PRIMARY KEY,
COLUMNNAME VARCHAR2(20) CONSTRAINT A_NNULL NOT NULL,
COLUMNNAME NUMBER(5,2),
COLUMNNAME DATE,
COLUMNNAME VARCHAR2(6) CHECK (COLUMNNAME IN ('A','B')),
COLUMNNAME NUMBER(6) CHECK (COLUMNNAME > '0'), /*BIGGER THEN ZERO*/ /*OR*/ (COLUMNNAME > 0) /*OR*/ (COLUMNNAME > COLUMNNAME2)
COLUMNNAME VARCHAR2(20) CONSTRAINT A_UQ UNIQUE,
COLUMNNAME VARCHAR2(4) DEFAULT 'HR',
COLUMNNAME NUMBER(2) CONSTRAINT A_FK REFERENCES TABLENAME (COLUMNNAME)
);

/*COMPOSITE PK*/ CREATE TABLE TABLENAME ( 
COLUMNNAME NUMBER(6), COLUMNNAME VARCHAR(2),
CONSTRAINT TABLE_PK PRIMARY KEY (COLUMNNAME,COLUMNNAME)
);

ALTER TABLE TABLENAME ADD CONSTRAINT PKEY1 PRIMARY KEY(COLUMNNAME);
ALTER TABLE TABLENAME DROP CONSTRAINT PKEY1;
ALTER TABLE TABLENAME RENAME COLUMN COLUMNNAME TO COLUMNNAME2;
ALTER TABLE TABLENAME RENAME TO NEWTABLENAME;


SELECT CUSTID FROM CUST WHERE DATEOFREG IS NULL 
UNION /*OR*/ UNION ALL /*UNION ALL INCLUDE DUPLICATE ROWS*/ /*OR*/ INTERSECT /*/*/ MINUS 
SELECT CUSTID FROM CUSTPURCHASE;

/*LEFT JOIN*/
SELECT TABLE1.EMPID, TABLE2.CUSTID FROM TABLE1 , TABLE2
WHERE TABLE1.EMPID = TABLE2.EMPID(+);

SELECT MAX(SUM(NETPRICE)) FROM MANA;


^^