top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

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




44 views0 comments

Recent Posts

See All

Beginner Friendly Java String Interview Questions

Hello Everyone! Welcome to the second section of the Java Strings blog. Here are some interesting coding questions that has been solved with different methods and approaches. “Better late than never!”

Commentaires

Noté 0 étoile sur 5.
Pas encore de note

Ajouter une note
bottom of page