Cheat Sheet for SQL

SQL Language

1. DDL( Data Definition Language) 2. DML( Data Manipulation Language) 3. DRL/DQL ( Data Retrieval Language/Data Query Language) 4. TCL ( Transaction Control Language) 5. DCL ( Data Control Language)

SQL Commands • DDL: create, alter, drop, truncate, rename • DML: insert, update, delete • DRL/DQL: select • TCL • Commit, Rollback, save point • DCL • GRANT, REVOKE

Creating Table • create table <<TABLE NAME>>(col1 datatype,col2 datatype, col3 datatype......); • Ex: • CREATE TABLE STUDENT(SNO NUMBER(5),SNAME VARCHAR(15),MARKS NUMBER(3));

Inserting data into table • INSERT INTO <<TABLE NAME>> VALUES(VAL1,AL2,VAL3....); • INSERT INTO STUDENT VALUES(101,'kiran',80); • INSERT INTO STUDENT(SNAME,SNO,MARKS) VALUES('RAM',102,60); • INSERT INTO STUDENT VALUES(103,'kRISHNA',NULL); • INSERT INTO STUDENT VALUES(&SNO,&SNAME,&MARKS);

Selecting Rows from a table • SELECT * FROM EMPLOYEES; • SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES; • SELECT EMPLOYEE_ID EMPID,FIRST_NAME FNAME,SALARY+300 SAL FROM EMPLOYEES;

SQL Data types • CHAR/ VARCHAR • ename char(5)----->'pavan; • ename char(5)---> 'pa' • ename varchar2(5)----'pavan' • ename varchar2(5)----'pa' • NUMBER • salary number(5) • length number(5,3) // scale & precision • DATE • HireDate Date

• LONG • Similar to varchar2 type but allows 2 GB • RAW • Used to store images, voice, videos files, text files etc..... • LONGRAW • similar to RAW datatypes • CLOB, BLOB, BFILE etc.......

Where clause

• Used for selecting the rows based on condition.(Filtering the rows using where condition)

• SELECT * FROM EMPLOYEES;

• SELECT * FROM EMPLOYEES WHERE SALARY>3000;

• SELECT * FROM EMPLOYEES WHERE SALARY<=3000;

• SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=30;

• SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT is null;

• SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Jennifer';

• SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;

• SELECT distinct * FROM EMPLOYEES;

Updating data into table

• UPDATE STUDENT SET MARKS=50 WHERE MARKS IS NULL; • UPDATE STUDENT SET SNAME='PAVAN',MARKS=70 WHERE SNO=106;

Logical Operators • AND • OR • NOT • SELECT * FROM EMP WHERE SAL>1000 AND JOB='CLERK'; • SELECT * FROM EMP WHERE SAL>2000 OR JOB='CLERK'; • SELECT * FROM EMP WHERE NOT ENAME='SMITH';

Between & IN Operators

• Between --> used to display the rows which is following in the range of values.

• Not Between

• SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 5000;

• SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 5000;

• IN --> IN operator return the rows when the values are matching in the list

• Not In

• SELECT * FROM EMPLOYEES WHERE SALARY=3600 OR SALARY=4000 OR SALARY=3900;

• SELECT * FROM EMP WHERE SAL IN(800,1600,1300);

• SELECT * FROM EMP WHERE SAL NOT IN(800,1600,1300);

PATTERN MATCHING OPEARATORS( Whiled card characters)

• % --> many characters • _ --> single character • SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%'; • SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%r'; • SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%r'; • SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%m%' • SELECT * FROM EMPLOYEES WHERE FIRST_NAME NOT LIKE 'S%'; • SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%e_'; • SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '___';

DDL Commands( Data Definition Language) 1) CREATE 2) ALTER 3) DROP 4) TRUNCATE 5) RENAME

Create & alter • CREATE is used to create database objects(Table, views, synonymes etc...) • ALTER 1. Adding a new column 2. Dropping the existing column 3. Modifying the existing column ( Increase/Decrease size of the column & change the data type of the column) 4. Renaming a column

• Adding a new column • ALTER TABLE STUDENT ADD(grade varchar(2)); • Dropping a column from table • ALTER TABLE STUDENT DROP(GRADE); • Modifying the existing column • We can increase/decrease the size of the column. • We can decrease the column size ONLY when existing column values can fit into new size.. • Column should be empty should be empty to change its data type. • ALTER TABLE STUDENT MODIFY(GRADE NUMBER(2)); • Renaming a column • ALTER TABLE STUDENT RENAME COLUMN SNAME TO STUNAME;

• DROP • Used to dropping the table definition with data • DROP TABLE STUDENT; • TRUNCATE • Used to remove all the rows from the table. • TRUNCATE TABLE STUDENT; • DELETE • Used for deleting all the rows from the table. • DELETE FROM TABLE;

Differences between Drop, Truncate & delete

• DROP TABLE STUDENT; -- Drops the structure & data

• TRUNCATE TABLE STUDENT; -- Removes the all the rows permanently

• DELETE FROM STUDENT; -- Removes the all the rows temporarily. We can Roll back the rows.

• RENAME

• used for changing the name of the table

• RENAME STUDENT TO STU;

SQL Functions • 1. Built-in Functions • 2. User Defined Functions (PL/SQL) • There are 2 types of Built-in functions 1. Group Functions (Multiple row functions) 2. Scalar Functions (Single row functions) • Dual Table: it is a Dummy table generally used for some calculations. It has one row and one column.

Group Functions • AVG() • SUM() • MAX() • MIN() • MAX() • COUNT() • SELECT AVG(SALARY) FROM EMPLOYEES; • SELECT SUM(SALARY) FROM EMPLOYEES; • SELECT MIN(SALARY) FROM EMPLOYEES; • SELECT MAX(SALARY) FROM EMPLOYEES; • SELECT COUNT(*) FROM EMPLOYEES; • sysdate: provides current system date. • select sysdate from dual;

Scalar Functions • 1. Character functions • 2. Number/Numeric Functions

• 3. Date Functions • 4. Conversion Functions

Character Functions

• Upper(): converts into upper case letters.

• Lower() : converts into lower case letters.

• Initcap(): converts first letter is capital and remaining are lowqer case letters.

• SELECT UPPER(First_name) from employees;

• SELECT LOWER(First_name) from employees;

• SELECT INITCAP(First_Name) from employees;

• Length(): return the length of string. • SELECT LENGTH('oracle') from dual; • SELECT * FROM EMPLOYEES WHERE LENGTH(FIRST_NAME)=4; • LDAP(): Pads the character towards the left side. • RPAD(); Pads the character towards the right side. • SELECT RPAD('ORACLE',10,'XXX') FROM DUAL; // ORACLEZZZZ • SELECT LPAD('ORACLE',10,'YYY') FROM DUAL; //YYYYORACLEF

• TRIM(): Removes the specified characters from both sides. • SELECT TRIM(' ORACLE ') FROM DUAL; • SELECT TRIM('z' from 'zzoraclezz') from dual; • INSTR(): Returns the position of the character within a string. • SELECT INSTR('ORACLE','E') FROM DUAL; • SUBSTR(): Returns the substring of the string. • SELECT SUBSTR('ORACLE',2,3) FROM DUAL; //RAC • SELECT SUBSTR('ORACLE',3,3) FROM DUAL; //ACL • SELECT SUBSTR('ORACLE',4,3)FROM DUAL; //CLE • SELECT SUBSTR(FIRST_NAME,1,3)||'****' FROM EMPLOYEES;

• CONCAT(): To join two strings. • SELECT CONCAT('ORACLE','TRAINING') FROM DUAL; • SELECT CONCAT(FIRST_NAME,LAST_NAME) ENAME FROM EMPLOYEES;

Number/Numeric Functions • abs(): return absolute value. • SELECT ABS(-40) FROM DUAL; • SELECT ABS(40) FROM DUAL; • sqrt(): returns square root of provided value. • select SQRT(25) from dual; • Mod(): return reminder value • select MOD(10,3) FROM DUAL; //1 • Power(): return power value (2*2*2*2*2) • select power(2,5) from dual;

• Trunc(): removes the decimal points. • select TRUNC(40.9) FROM DUAL; // 40 • select TRUNC(40.1234,3) FROM DUAL; // 40.123 • select TRUNC(40.1234,2) FROM DUAL; // 40.12 • Select TRUNC(6876,-1) FROM DUAL; //6870 • Select TRUNC(6876,-2) FROM DUAL; //6800 • Select TRUNC(68763456,-5) FROM DUAL; //68700000

• Greatest() & Least(): returns greatest, least values in the provided values. • select GREATEST(100,200,300,400,500) FROM DUAL; • SELECT LEAST(100,200,300) FROM DUAL;

Date Functions • ADD_MONTHS() • MONTHS_BETWEEN() • NEXT_DAY() • LAST_DAY()

• ADD_MONTHS(): this will add months to provided date.

• SELECT ADD_MONTHS(sysdate,6) from dual;

• SELECT ADD_MONTHS('15-FEB-2016',8) from dual;

• Months_between(): returns number of months between given dates.

• SELECT MONTHS_BETWEEN(SYSDATE,'10-JULY-2015') FROM DUAL;

• SELECT FIRST_NAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE))“ Exp in Months" FROM EMPLOYEES;

• SELECT FIRST_NAME,trunc(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) “ Exp in Years" FROM EMPLOYEES;

• Next_Day(): returns date of the specified date • SELECT NEXT_DAY(sysdate,'friday') from dual;

• Last_day(): Returns the last day of the month. • select last_day('01-Feb-2016') from dual;

Conversion Functions • TO_CHAR() • TO_NUMBER() • TO_DATE()

Source: Google and Pavan - SDET- QA Automation Techie