Create A table
CREATE TABLE TABLENAME ( COLUMN1 DATATYPE (SIZE), COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE (SIZE)); |
Create Table Using Constrains
CREATE TABLE TABLENAME ( COLUMN1 DATATYPE (SIZE) NOT NULL , COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE (SIZE)); |
CREATE TABLE TABLENAME ( COLUMN1 DATATYPE (SIZE) UNIQUE, COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE (SIZE)); |
CREATE TABLE TABLENAME1 ( COLUMN1 DATATYPE (SIZE), COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE (SIZE), PRIMARY KEY(COLUMN1)); |
CREATE TABLE TABLENAME2 ( COLUMN1 DATATYPE (SIZE), COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE (SIZE) , FOREIGN KEY(COLUMN1) REFERENCES TABLENAME1(COLUMN1)); |
Insert Data/ Row to a table
INSERT INTO TABLENAME VALUES (FIRST VALUE, SECOND VALUE, THIRD VALUE); |
INSERT INTO TABLENAME (COLUMN1, COLUMN3 ) VALUES ( VALUE FOR COLUMN1, VALUE FOR COLUMN2); |
Describe a Table Structure
Delete Table Data
DELETE FROM TABLENAME; |
DELETE FROM TABLENAME WHERE COLUMN1='VALUE'; |
DELETE FROM TABLENAME WHERE COLUMN2='VALUE' AND/OR COLUMN3='VALUE' |
TRUNCATE TABLE TABLENAME; |
DROP TABLE TABLENAME; |
Ordering Data
SELECT * FROM TABLENAME ORDER BY COLUMN1 ASC; |
SELECT * FROM TABLENAME ORDER BY COLUMN1 DESC; |
Select 2nd Max Salary from Employee
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY)FROM EMPLOYEE) |
Select Nth Lowest salary from Employee
SELECT MAX(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALAY ASC ) WHERE ROWNUM <= N; |
Select 2nd Min Salary from Employee
SELECT MIN(SALARY) FROM EMPLOYEE WHERE SALARY NOT INT (SELECT MIN(SALARY) FROM EMPLOYEE); |
Select Nth highest Salary
SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM <= N; |
|
|
Printing a Table
SELECT * FROM TABLENAME; |
SELECT DISTINCT COLUMN2 FROM TABLENAME; |
| |
BY USING OPERATORS |
SELECT COLUMN1, COLUMN3 FROM TABLENAME; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN2='VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3<'VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3>'VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3<='VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3>='VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3<>'VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3='VALUE' OR COLUMN2='VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3='VALUE' AND COLUMN2='VALUE'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN2 BETWEEN 100 AND 200; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN3 IN (8,15,17,15); |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN2 LIKE '%SP'; |
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN2 NOT LIKE '%SP'; |
Alter Table
ALTER TABLE TABLNAME ADD COLUMN4 DATATYPE (SIZE); |
ALTER TABLE TABLENAME MODIFY COLUMN1 DATATYPE (SIZE); |
ALTER TABLE TABLENAME RENAME COLUMN COLUMN2 TO COLUMNXYZ; |
ALTER TABLE TABLENAME RENAME TO TABLENAME1; |
Update Table
UPDATE TABLENAME SET COLUMN1='VALUE' WHERE COLUMN2='VALUE'; |
Alias
SELECT COLUMN2 AS NOTCOLUMN2 FROM TABLENAME |
SELECT * FROM TABLENAME NOTTABLENAME; |
SELECT COLUMN3 FROM TABLENAME AS XYZCOLUMN; |
Aggregate Functions
SELECT SUM(COLUMN2) FROM TABLENAME; |
SELECT AVG(COLUMN2) FROM TABLENAME; |
SELECT MIN(COLUMN2) FROM TABLENAME; |
SELECT MAX(COLUMN2) FROM TABLENAME; |
SELECT COUNT(COLUMN2) FROM TABLENAME; |
SELECT COUNT(DISTINCT COLUMN2) FROM TABLENAME; |
SQL Joins
SELECT * FROM TABLENAME INNER JOIN TABLENAME2 ON TABLENAME.COLUMN1 = TABLENAME2.COLUMN1; |
SELECT * FROM TABLENAME LEFT JOIN TABLENAME2 ON TABLENAME.COLUMN1 = TABLENAME2.COLUMN1; |
SELECT * FROM TABLENAME RIGHT JOIN TABLENAME2 ON TABLENAME.COLUMN1 = TABLENAME2.COLUMN1; |
SELECT * FROM TABLENAME FULL JOIN TABLENAME2 ON TABLENAME.COLUMN1 = TABLENAME2.COLUMN1; |
|
