Most commonly used SQL commands

ALTER TABLE EMPLOYEES ADD AUDIT_ID NUMBER NOT NULL

ALTER TABLE EMPLOYEES DROP COLUMN AUDIT_ID NUMBER NOT NULL


- This query converts the 'String' Date Time object to date object and stores into database, Explains about using to_date() function.

INSERT INTO AUDIT(AUDIT_ID,ENTITY_NAME,AUDIT_KEY,ENTITY_ID,OPERATION,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE) VALUES(23,'HEADERS','AGREEMENT_HEADER_ID','283','INSERT','admin',to_date('20090202121212','yyyyMMddHH24MISS'),'admin','admin',to_date('20090202121212','yyyyMMddHH24MISS'))

// Create DB Sequence

CREATE SEQUENCE  "HR"."EMPLOYEE_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER  NOCYCLE ;

// Test DB Sequence using SQL Worksheet
select EMPLOYEE_ID_SEQ.nextval from dual

// Get first n Rows from the Database

SELECT * FROM EMPLOYEES WHERE ROWNUM <=5

// Alter the database table column
ALTER TABLE EMPLOYEE MODIFY SALARY NUMBER;

// Left Outer Join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;