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