Category Archives: Sql

SQL – Like

SQL Like Clause

Like operator is used to filter the results . Like operator compares the results using the wild cards.

There are two types of wildcards used for the filtering & matching the result.

1. % – (Percent Symbol).
2. _ – (Underscore Symbol).

SQL Like CLause Syntax (Wildcard %)

SELECT columnName1, ColumnName2.. FROM TableName WHERE ColumnName LIKE ‘%Pattern’;

Or

SELECT columnName1, ColumnName2.. FROM TableName WHERE ColumnName LIKE ‘Pattern%’;

or

SELECT columnName1, ColumnName2.. FROM TableName WHERE ColumnName LIKE ‘%Pattern%’;

Explanation :

‘Pattern%’ – : Mathes & finds the pattern where column value starts with the provided pattern (Ex . – ‘%John’ – Will find the records where Name(Column) starts with ‘John’).

‘%Pattern’ – : Mathes & finds the pattern where column value ends with the provided pattern (Ex . – ‘John%’ – Will find the records where Name(Column) ends with ‘John’ ).

‘%Pattern%’ – : Mathes & finds the pattern where pattern find at any place in column value (Ex . – ‘%John%’ – Will find the records where Name(Column) find at any where ).

SQL Like CLause Syntax (Wildcard _ ) –

SELECT columnName1, ColumnName2.. FROM TableName WHERE ColumnName LIKE ‘_Pattern’;

Or

SELECT columnName1, ColumnName2.. FROM TableName WHERE ColumnName LIKE ‘Pattern_’;

or

SELECT columnName1, ColumnName2.. FROM TableName WHERE ColumnName LIKE ‘_Pattern_’;

Explanation :

‘_Pattern’ – : Mathes & finds the pattern where column value has the pattern at second place (Ex . – ‘_J’ – Will match the column value where second value is ‘J’).

‘Pattern_’ – : Mathes & finds the pattern where column value start with the pattern (Ex . – ‘J%’ – Will match the column value where column value starts with ‘J’ ).

‘_Pattern_’ – : Mathes & finds the pattern where pattern where (Ex . – ‘_Jn_’ – Will find the records where 2nd value is ‘J’ and seond last value is ‘n’ ).

Example :

Example

SELECT *FROM Users WHERE Name LIKE ‘J%’;

Will Produce The Follwoing Result.

$like

Example :

Example

SELECT *FROM Users WHERE Name LIKE ‘%i’;

Will Produce The Follwoing Result.

SQL Like

SQL – DELETE

SQL DELETE Query

SQL – DELETE Query is used to delete records(rows) from table.

SQL DELETE Syntax :

DELETE FROM TableName WHERE columnName = someColumnValue;

Suppose We Have Following Data :

tableFull

Example : We want to delete row where id is ‘9’

Example

DELETE FROM Users WHERE ID = ‘9’;

This Will Delete the row where id is ‘9’ . The Table will look Now :

SQL Delete onrowDeleted

SQL – UPDATE

SQL Update Query

SQL – UPDATE Statement is used to update or change the records in table which are existing in the table.

SQL Update Query with Syntax

UPDATE TableName
SET columnName1 = newValue1,
SET columnName2 = newValue2,
SET columnName3 = newValue3
WHERE columnName = someColumnValue;

Suppose We have Following Data :

tableFull

Update Example :

Example

UPDATE USERS SET Name = ‘David Camron’ , City=”NEW LONDON” WHERE Email= ‘david@yahoo.com’;

The Above Query Will Update the row where email is ‘david@yahoo.com’ the update data will be as :

SQL UPDATE Query

SQL ORDER BY

SQL ORDER BY Clause

ORDER BY Clause is used to sort the data on the basis of columns in ascending or descing order.

SQL ORDER BY Syntax

:

SELECT columnName1, columnName2, columnName3
from TableName ORDER By columnName1 , columnName2 ASC | DESC

Note : If We do not use the ASC or DESC keyword the Default order ASC is considered.

Suppose We Have The Following Data :

tableFull

ORDER BY DESC Example :

Example

SELECT * FROM `Users` ORDER BY ID Desc;

The Above Query Will Sort the result in descening order by Id. The Query Produces the result as :
sortIdDec

ORDER BY ASC Example :

Example

SELECT * FROM `Users` ORDER BY ID ASC;

The Above Query Will Sort the result in ascending order by Id. The Query Produces the result as :

sql order by sortAscid

ORDER BY Multiple Columns Example :

Example

SELECT * FROM `Users` ORDER BY Name, Email ASC;

The Above Query Will Sort the result in ascending order by Name & Email. The Query Produces the result as :

sortByMultipleCOl

SQL – AND & OR

SQL – AND & OR Clauses

SQL – AND & OR Explained

AND Clause :

And Clause displays the results where both the conditions are fullfilled.

OR Clause :

OR Clause displays the results where atleast one conditions is fullfilled.

Suppose we have following data :

tableFull

AND Clause Example :

Example

SELECT * FROM Users WHERE (City=”DELHI” and Name=’Riya’);

Where [City=”DELHI” and Name=’Riya’] Both Conditions are satisfied.

This Will Produce The Result As :

SQL - AND & OR

OR Clause Example :

Example

SELECT * FROM Users WHERE (City=”DELHI” OR Name=’Riya’);

Where [City=”DELHI” OR Name=’Riya’] Either condition is satisfied.

This Will Produce The Result As :

SQL - AND & OR

AND & OR Clause Together Example :

This will select the result where city is (“delhi” or name is “riya” )[produces two rows]
with the condition followed by name “linga” [Selects one from the two rows where name is “linga”]

Example

SELECT * FROM Users WHERE (City=”DELHI” OR Name=’Riya’) AND Name=’linga’;

This Will Produce The Result As :

SQL - AND & OR

SQL – WHERE Clause

SQL WHERE Clause

SQL WHERE Clause is used to filter the recoreds on the basis of some condition.
This will fetch the results which will fullfill the condition specified.

SQL WHERE Syntax :

SELECT ColumnName1,ColumnName2,ColumnName3 FROM TableName WHERE Condition

Suppose The “Users” Table has following records :

SQL WHERE Clause tableFull

Suppose we have to find those records which have ‘city’ value as “DELHI”

Example 1 – : The Following Example will fetch the results as

Example

SELECT * FROM Users WHERE city = “DELHI”;

The Above Query Will Fetch the results :

resultDelhi

Example 2 – : Fetch The Results where user’s ID is ‘5’

Example

SELECT * FROM Users WHERE ID = “5”;

The Above Query Will Produce the result as :

idResult

SQL – SELECT

SQL – SELECT Statement :

Sql Select Statement is used to fetch records from database table.

SQL – SELECT Syntax 1 (Select All Columns) :

SELECT *FROM TableName;

SQL – SELECT Syntax 2 (Select few Columns) :

SELECT ColumnName1, ColumnName2, ColumnName3,…. FROM TableName;

SQL Select All Columns Example :

Example

SELECT * FROM Users;

The above Query will display the results as :

 sql select all-c…sql-query

Select-all-c…ql-query

SQL Select Few Columns Example :

Example

SELECT ID, Name, Email FROM Users;

The above Query will display the results as :

Sql-INsert-in-Selected-columns

SQL – INSERT

SQL – INSERT Statement:

SQL INSERT INTO Statement is used to insert a new row(ie. record) in a table.

SQL INSERT INTO Syntax:

INSERT INTO TableName VALUES(Value1,Value2,….);

Note: The Above Syntax inserts rows in all columns of the table.

If You want to insert values in selected columns the following syntax is used :

INSERT INTO TableName (ColumnName1,ColumnName2,….)VALUES(Value1,Value2,….);

EXAMPLE 1 (Insert in all column) :

Example

INSERT INTO Users VALUES(‘1′,’John’,’john@yahoo.com’,’USA’,’NewYork’);

1 New row will be inserted.

Now the table has its first record after the selection it will show:

MysqlInsertStatement

EXAMPLE 2 (Insert in few columns only):

Example

INSERT INTO Users (ID,Name,Email)VALUES(‘2′,’Devid’,’david@yahoo.com’);

Note: We need to specify the column name in which we want to insert values Like If want to insert values in ID, Name and Email Columns only…

Now the table will look like:

SQL - INSERT-in-Selected-columns

Sql-Insert-in-Selected-columns

SQL – CREATE TABLE

SQL Create Table Syntax is used to create a table in database .

Tables are collection of rows & columns.

SQL – CREATE TABLE Syntax

CREATE TABLE Table_Name
(
ColumnName1 datatype,
ColumnName2 datatype,
ColumnName3 datatype,
….
….
….

);

ColumnName1, ColumnName2, ColumnName3, .. are name of columns.
datatype specifies the type of data to be stored in that column example : integer, variable , text or decimal etc..

SQL – CREATE TABLE Example

Example

CREATE TABLE Users
(
ID int,
Name varchar(100),
Email varchar(100),
Address varchar(100),
City varchar(100),
PRIMARY KEY (ID)
);

* PRIMARY KEY : Primary creates constraint on the column ID which will contain unique & not null values. We will Explain it in later topics.

Users Table will be created in CustomersDb.

To Verify the table Creation Use The following Command .

DESC TableName;

Example

DESC Users;

Which will Display Created table AS :

SQL create table

Mysql Table