top of page
Search
Writer's pictureponmani kullappan
Jul 20, 20235 min read

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




75 views

Recent Posts

See All
bottom of page