SQL

SQL = Structured Query Language

Basic Commands

CREATE

To Create a table.

CREATE TABLE emp1 (
empno CHAR(4),
ename VARCHAR(30),
sal NUMERIC(10,2),
city VARCHAR(30),
deptno SMALLINT
);

Insert

Adds new data into an existing table.

insert into emp1 values (‘1′,’Adams’,1000.0,’Mumbai’,10);
insert into emp1 values (‘2′,’Black’,2000.0,’Delhi’,10);
insert into emp1 values (‘3′,’Allen’,2500.0,’Mumbai’,20);
insert into emp1 values (‘4′,’King’,3000.0,’Delhi’,30);
insert into emp1 values (‘5′,’Ford’,4000.0,’Mumbai’,40);

SELECT

Used to fetch data from one or more tables.
SELECT * FROM emp1;

WHERE

To restrict the rows
SELECT * FROM emp1
where deptno =10;
- more about WHERE clause

Relational Operator

< , > , <= , >=, <>, !=, =

SELECT * from emp1 
where sal > 2000;

Logical Operator

NOT, AND, OR

SELECT * from emp1 
where sal > 2000 and sal < 3000;

Computed Column

Computed column never get saved in Harddisk.
SELECT ename, sal, sal*12 as annual_sal from emp1;

Arithmetic Operator

+, - , * , /

DISTINCT

To Supress the duplicates .
SELECT DISTINCT job FROM emp1;

ORDER BY clause

Order by clause is the last clause in the select statement.
SELECT ename, sal, deptno from emp1
ORDER by deptno;

LIKE, BETWEEN, IN, NOT IN, ANY - special operator

% = is a symbol for wild card - Meaning any character & any number of characters. _ = is used for pattern matching.
SELECT * from emp1
WHERE ename like A%;

UPDATE

Used to update existing record.
UPDATE emp1 
set sal =10000
where empno=1;

DELETE

Used to delete existing record.
DELETE from emp1
where empno=1;

DROP

Drop is a DDL command. You cannot use WHERE clause with DROP.
DROP table emp1;

Functions - concat, || , upper, lower, initcap

Functions - lpad, rpad, ltrim, rtirm, substr, transalte

Back to CheatSheets