SQL

SELF JOIN A table join with itself. SYNTAX SELECT  comma_separeted_ column_names FROM   table1 alias1, table1 alias2 WHERE   condition ; EXAMPLE SELECT t1.username User1, t2.username User2 FROM users T1, users T2 WHERE t1.id <> t2.id  AND t1.area = t2.area ORDER BY t1.area Note: This query will select the username which are belonging to same area

INNER JOIN  SYNTAX SELECT  comma_separeted_ column_names FROM   table1 INNER   JOIN   table2  ON   table1.column_name  =  table2.column_name ; EXAMPLE SELECT  user.username, orders.order_id FROM   users INNER   JOIN  orders   ON  users.id = orders.order_id ORDER   BY  users.username; Note:  Here this query will retrieve only those records which have matching both the tables. FULL OUTER JOIN  SYNTAX SELECT  comma_separeted_ column_names FROM   table1 OUTER   JOIN   table2  ON   table1.column_name  =  table2.column_name ; EXAMPLE SELECT  user.username, orders.order_id FROM   users FULL OUTER   JOIN  orders   ON  users.id = orders.order_id ORDER   BY  users.username; Note:  Here this query will retrieve all the records from both the table if there is at least one matching...

LEFT JOIN  SYNTAX SELECT  comma_separeted_ column_names FROM   table1 LEFT   JOIN   table2  ON   table1.column_name  =  table2.column_name ; EXAMPLE SELECT  user.username, orders.order_id FROM   users LEFT   JOIN  orders   ON  users.id = orders.order_id ORDER   BY  users.username; Note: Here this query will retrieve all the records from  right table (users) and only matching record from orders table RIGHT JOIN SYNTAX SELECT  comma_separeted_ column_names FROM   table1 RIGHT   JOIN   table2  ON   table1.column_name  =  table2.column_name ; EXAMPLE SELECT  users.username, orders.order_id FROM   orders RIGHT   JOIN  users ON  users.id = orders.order_id ORDER   BY  users.username; Note:  Here this query will retrieve all the records from  right table (users) and only ma...

DML  is abbreviation of Data Manipulation Language.  It is used to retrieve, store, modify, delete, insert and update data in database.  Examples: SELECT, UPDATE, INSERT statements are used for DML.  DDL  is abbreviation of Data Definition Language.  It is used to create and modify the structure of database objects in database. Create table,  Alter table,  Modify Column 

Aliases are used to give a temporary name to particular column and table SYNTAX SELECT column1 as alias_name, column2 as alias_name, ... FROM table_name as alias_name EXAMPLE SELECT id as user_id, name as user_name FROM users as mobile_users JOINS : used to select columns from multiple table based on the related columns between them. There are different types joins available as mentioned below. INNER JOIN : It gives matching records in both the joined tables. FULL OUTER JOIN: It gives all records from left and right table if there is at least one matching record in both of the tables. LEFT JOIN: It gives all the records from the left table and only matching records from the right table. RIGHT JOIN : It gives all the records from the right table and only matching records from the left table. SELF JOIN: It is used to make relationship between column of the same table and then retrieving data based on condition on same column in same table.

LIKE operator is used in a WHERE clause to search for a specific pattern data in a specific column. Two wildcards used in with the LIKE operator: % : The percent sign represents zero, one, or multiple characters _  : The underscore represents a single character SYNTAX SELECT column1, column2, column3,... FROM table_name  WHERE column_name  LIKE pattern;  EXAMPLE SELECT id, name, mobile_number  FROM mobile_users WHERE name; LIKE '_a%'  IN operator is used to specify multiple values in a WHERE clause. SYNTAX SELECT column1, column2, column3,... FROM table_name  WHERE column_name IN('val1', ' val3 ', 'val3 ');  EXAMPLE SELECT id, name, mobile_number  FROM mobile_users WHERE name IN('raj', 'gaurang', 'nishidh');  BETWEEN is used to selects values within a given range. Values can be numbers, text, dates. SYNTAX SELECT comma_separated_column_names FROM table_name WHERE column_name BETWEEN val1 ...

MIN()  Retrieves the smallest value for the selected column. MAX()  retrieves the largest value for the selected column. COUNT()  Gives total number of rows that matches the specified condition. AVG()  Gives the average for the specified numeric column. SUM()  Gives the total sum for the specified numeric column. SYNTAX SELECT min (column_name) WHERE condition;   EXAMPLE MIN SELECT min (salary)  FROM users   WHERE Country='INDIA';   SYNTAX SELECT max (column_name) WHERE condition;   EXAMPLE MAX SELECT max (salary)  FROM users   WHERE Country='INDIA';     SYNTAX SELECT count (column_name) WHERE condition;   EXAMPLE COUNT SELECT count (name)  FROM users   WHERE Country='INDIA';  SYNTAX SELECT sum (column_name) WHERE condition;   EXAMPLE SUM SELECT sum (salary)  FROM users   WHERE Country='INDIA';  SY...