Show Menu
Cheatography

SQL Commands Cheat Sheet (DRAFT) by

Basic SQL Commands to Work With...

This is a draft cheat sheet. It is a work in progress and is not finished yet.

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(CO­LUM­N1));
CREATE TABLE TABLENAME2 ( COLUMN1 DATATYPE (SIZE), COLUMN2 DATATYPE (SIZE), COLUMN3 DATATYPE (SIZE) , FOREIGN KEY(CO­LUMN1) REFERENCES TABLEN­AME­1(C­OLU­MN1));

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

DESC TABLENAME;

Delete Table Data

DELETE FROM TABLENAME;
DELETE FROM TABLENAME WHERE COLUMN­1='­VALUE';
DELETE FROM TABLENAME WHERE COLUMN­2='­VALUE' AND/OR COLUMN­3='­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(SA­LARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SA­LAR­Y)FROM EMPLOYEE)

Select Nth Lowest salary from Employee

SELECT MAX(SA­LARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALAY ASC ) WHERE ROWNUM <= N;

Select 2nd Min Salary from Employee

SELECT MIN(SA­LARY) FROM EMPLOYEE WHERE SALARY NOT INT (SELECT MIN(SA­LARY) FROM EMPLOYEE);

Select Nth highest Salary

SELECT MIN(SA­LARY) 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 COLUMN­2='­VALUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3<'­VALUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3>'­VALUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3<=­'VA­LUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3>=­'VA­LUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3<>­'VA­LUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3='­VALUE' OR COLUMN­2='­VALUE';
SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE COLUMN­3='­VALUE' AND COLUMN­2='­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 TABLEN­AME1;

Update Table

UPDATE TABLENAME SET COLUMN­1='­VALUE' WHERE COLUMN­2='­VALUE';

Alias

SELECT COLUMN2 AS NOTCOLUMN2 FROM TABLENAME
SELECT * FROM TABLENAME NOTTAB­LENAME;
SELECT COLUMN3 FROM TABLENAME AS XYZCOLUMN;

Aggregate Functions

SELECT SUM(CO­LUMN2) FROM TABLENAME;
SELECT AVG(CO­LUMN2) FROM TABLENAME;
SELECT MIN(CO­LUMN2) FROM TABLENAME;
SELECT MAX(CO­LUMN2) FROM TABLENAME;
SELECT COUNT(­COL­UMN2) FROM TABLENAME;
SELECT COUNT(­DIS­TINCT COLUMN2) FROM TABLENAME;

SQL Joins

SELECT * FROM TABLENAME INNER JOIN TABLENAME2 ON TABLEN­AME.CO­LUMN1 = TABLEN­AME­2.C­OLUMN1;
SELECT * FROM TABLENAME LEFT JOIN TABLENAME2 ON TABLEN­AME.CO­LUMN1 = TABLEN­AME­2.C­OLUMN1;
SELECT * FROM TABLENAME RIGHT JOIN TABLENAME2 ON TABLEN­AME.CO­LUMN1 = TABLEN­AME­2.C­OLUMN1;
SELECT * FROM TABLENAME FULL JOIN TABLENAME2 ON TABLEN­AME.CO­LUMN1 = TABLEN­AME­2.C­OLUMN1;