SQL Tools(JOIN Operator(Inner join, Left join, Right join))

INNER JOIN Operator :-
INNER Join return all rows from the join of left table and right table if there are same data available in both tables if no data will match then it will return null
SELECT * FROM Table_Name_1 INNER JOIN Table_Name_2 WHERE Table_Name_1.Column_Name = Table_Name_2.Column_Name

You can divide your result in group like :-
If a table contains First_Name, Last_Name, Address, Ph_No and there are so many result according to First_Name then you can use Group_By Like this :-

SELECT * FROM Old_Persons As OP INNER JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

LEFT JOIN Operator :-
LEFT join return all rows from the left table even there are no rows in right table.

SELECT * FROM Old_Persons As OP LEFT JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

RIGHT JOIN Operator :-
RIGHT JOIN return all rows from the right table even if there are no rows available in left table.

SELECT * FROM Old_Persons As OP RIGHT JOIN New_Persons As NP WHERE OP.First_Name = NP.First_Name Group By OP.First_Name

Read More

SQL Tools(Alias Operator & In Operator)

ALIAS Name for Column :-
SELECT Column_Name AS Alias_Name FROM Table_Name

ALIAS Name for table :-
SELECT Column_Name From Table_Name AS Alias_Name

ALIAS Example :-
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons AS p, Product AS po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID > 2

Without using AS
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons p, Product po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID > 2

IN Operator :-
If you want to select OrderID of between 2,3,4,5 then :-
SELECT p.FirstName,p.LastName, po.OrderID
FROM Persons p, Product po
WHERE p.FirstName='Saurabh' AND p.LastName='Singh' AND po.OrderID IN (2,3,4,5)

OR :-
SELECT * FROM Persons
WHERE FirstName IN ('Saurabh','Saanjh', 'vivek')

Read More

SQL - Tools (BETWEEN Operator)

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2

SELECT * FROM COILS WHERE COIL_NAME BETWEEN SomeValue1 AND SomeValue2

Between operator is like It will select a row where COIL_NAME has SomeValue1 . Value depends on database to database . Some database will select rows where COIL_NAME has SomeValue1 and where COIL_NAME has SomeValue2.

We can use NOT BETWEEN operator if you you dont want to select those values like :-
SELECT * FROM COILS WHERE COIL_NAME NOT BETWEEN SomeValue1 AND SomeValue2
This query will select a value where coil_name is not equal to SomeValue1 and SomeValue2.

Read More

SQL - Tools(Top Clause AND Like Operator)

SELECT TOP 1 FROM Table_Name

This will select 1 row from table

SELECT TOP 1* FROM Table_Name (Select all columns of 1st row)
SELECT TOP 2* FROM Table_Name (Select first two rows of table)
SELECT TOP 50 PERCENT * FROM Table_Name (Select 50 % rows from table)

LIKE OPERATOR

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

IF a column name CITY from Persons table contains Kanpur, Delhi , NewDelhi , Kannauj , Nagar values then If you want to select city name starts from K then :-

SELECT * FROM Persons
WHERE CITY LIKE 'k%'

this will retrieve Kanpur , Kannauj.

If you want to select city name ends from i then :-
SELECT * FROM Persons
WHERE CITY LIKE '%i'


IF you want to select a city which has particular format like Select a city which has 'elh' pattern
SELECT * FROM Persons
WHERE City LIKE '%elh%'

this will retrieve Delhi , NewDelhi.

If you dont want to select a city which has particular format like 'elh'

SELECT * FROM Persons
WHERE City NOT LIKE '%elh%'

Read More

SQL - Tools ( INSERT,UPDATE and DELETE Statement)

INSERT INTO table_name
VALUES (value1, value2, value3,...)

IF a table Persons contains P_Id,LastName,FirstName,Address,City columns.If you want to insert values then :-
INSERT INTO Persons
VALUES (4,'Singh', 'Saurabh', 'Sector-23', 'Gurgaon')

IF NOT EXISTS (SELECT * FROM Persons WHERE P_Id = 4)
BEGIN
INSERT INTO Persons
VALUES (4,'Singh', 'Saurabh', 'Sector-23', 'Gurgaon')
END

By this way it will check firstly, if this p_id exist in the table then do not insert otherwise insert these values in the table.

Update statement :-
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

We use If not exist statement whether there is a need to update or not.

DELETE Statement :-
DELETE From table_name WHERE some_column=some_value
Note :- Never use Delete * use Delete tableName.*

Read More

SQL - Tools(ORDER BY)

ORDER BY keyword is used for sorting suppose in a table there is a column named first_name.
If you want to show the result in ascending order then :-

SELECT * FROM Persons WHERE Age >= 25 GROUP BY first_name ASC

If you want to show the result in descending order then :-
SELECT * FROM Persons WHERE Age >= 25 GROUP BY first_name DESC

By default it is ascendeng . if we use like GROUP BY first_name then it sorts in ascending order.

Read More

SQL - Tools (WHERE, AND, OR Clause)

Where clause is use for filtering like :-

SELECT * FROM table_name WHERE column_name (operator) value
(operator) :- =, >, <,! and more operators.

A Friends table contains firstName, secondName, address, phone_no and firstName values are :- saurabh, sandy, gaurav, somu, saurabh and address column values are :- kanpur, allahabad, delhi, kanpur, varanasi.

SELECT * FROM Friends WHERE firstName = 'Saurabh' AND address = 'kanpur'

This will select only first row because only first row satisfies WHERE condition

SELECT * FROM Friends WHERE firstName = 'Saurabh' OR address = 'kanpur'

This will select first row, fourth row and fifth row due to oR condition in where clause

Operators :-
< :- lessthan
> :- greaterthan
<= :- less than or equal to
>= :- greater than or equal to
<> :- not equal to
BETWEEN :- between an inclusive range
LIKE :- for search pattern
IN :- SEARCH for exact values like if i know values are 1987, 1989

Use of IN clause :-
EXAMPLE :- SELECT * FROM Friends WHERE year IN (1987, 1989)

Read More

SQL - Tools....(SELECT Clause)

SELECT :-

SELECT * FROM TABLE_NAME this will select whole table

If Coils table contains 5 columns p_id, coil_no, coil_width, coil_length, coil_name.Then you have to select coil_width and coil_no like :-

SELECT coil_width, coil_length FROM Coils

If you have to select distinct columns from table like coil_name columns have ABCDE12, ABCD13, ABCD14, ABCD12, ABCD14 then you have to choose only distinct values :-

SELECT DISTINCT column_name FROM table_name
SELECT DISTINCT coil_name FROM coils

Read More