SQL

Structured Query Language is standard language for DATABASE.

Core JAVA Selenium

Automation Testing

SQL (Structured Query Language)


SQL: SQL stands for Structured Query Language. SQL is itself not a programming language, but it is the standard language for relational database management systems (RDBMS). SQL is used to communicate with all database such as MySQL, SQL Server, MS Access and Oracle. It is standard language for defining, manipulating and retrieving data in database.

Uses of SQL:
• Retrieve data from databases
• Insert new records
• Update existing data
• Delete data
• Create and manage database structures




SQL language statement mainly divided into 4 group:

DDL:DDL stands for Data Definition Language. CREATE, ALTER and DROP are main statement of DDL.
DML:DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are the main statement of DML.
DCL:DCL stands for Data Control Language. GRANT and REVOKE are the main statement of DCL.
TCL:TCL stands for Transaction Control Language. COMMIT, ROLLBACK and SAVEPOINT are the main statement of TCL.

SELECT statement is the most commonly used data query language (DQL) command.
SELECT statement is also known as Data Manipulation Language(DML) command.


SQL Commands:

i) CREATE DATABASE - creates a new database
ii) ALTER DATABASE - modifies a database
iii) DROP DATABASE – delete the database
iv) CREATE TABLE - creates a new table
v) ALTER TABLE - modifies a table
vi) DROP TABLE - deletes a table
vii) INSERT INTO - inserts new data into a database
viii) UPDATE - updates data in a database
ix) DELETE - deletes data from a database
x) SELECT - extracts data from a database



SQL Syntax for most important SQL statements:


CREATE DATABASE database_name;

DROP DATABASE database_name;

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, … );

INSERT INTO table_name (column1, column2, column3,…columnN) VALUES (value1, value2, value3,….valueN);

UPDATE table_name SET column1=value, col2=value,… WHERE some_column=some_valule

SELECT * FROM table_name;

SELECT column_name(s) FROM table_name;

SELECT DISTINCT column_name(s) FROM table_name;

SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC];

SELECT column_name(s) FROM table_name WHERE conditions AND|OR conditions;

SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC];

SQL Join: Join statement is used to combine table data or table row from two or more than two table.

Types of SQL JOIN:
i) INNER JOIN or JOIN
ii) LEFT JOIN or LEFT OUTER JOIN
iii) RIGHT JOIN or RIGHT OUTER JOIN
iv) OUTER JOIN or FULL JOIN

i) INNER JOIN: INNER JOIN returns the all record that have matching value in both table. In simple words, it provide the common table rows data from given table. INNER JOIN is also known as simple JOIN.

ii) LEFT JOIN: LEFT JOIN is also known as OUTER LEFT JOIN. It returns the all records of first table or left table and matching record of another table or right table.

iii) RIGHT JOIN: RIGHT JOIN is also known as OUTER RIGHT JOIN. It returns the all record of right table or another table and matching value of left table or first table.

iv) FULL JOIN: FULL JOIN returns the all records of left table as well as right table and all the matched or unmatched record of both table. FULL JOIN is also known as FULL OUTER JOIN.

CROSS JOIN: CROSS JOIN returns all the possible combination between each row of left table and right table. In simple word, it provide the value of number of row in left table multiply with the number of row in right table. CROSS JOIN is also known as Cartesian Product.

Abbreviation: t1= table1, t2= table2, col1= column1, col2= column2, matching_col= Matching column in both table.


SELECT t1.col1, t1.col2, t2.col1, t2.col2…FROM t1 INNER JOIN t2 ON t1.matching_col=t2.matching_col;

SELECT t1.col1, t1.col2, t2.col1, t2.col2…FROM t1 LEFT JOIN t2 ON t1.matching_col=t2.matching_col;

SELECT t1.col1, t1.col2, t2.col1, t2.col2…FROM t1 FULL JOIN t2 ON t1.matching_col=t2.matching_col;