Tag Archives: sql tutorials for beginners
SQL – UNIQUE Key
SQL UNIQUE Key Constraint :
1 . At The Time Of Table Creation :
UNIQUE Key Constraint is used to identify every record uniquely. Multiple Unique Key can be in a sigle table.
SQL – UNIQUE Key Constraint Syntax Example :
Example
ID int(11) NOT NULL,
Name varchar(110) NOT NULL,
Email varchar(100) NOT NULL,
CONSTRAINT USER_EMAIL_UNIQUE_CONSTRAINT UNIQUE (Email)
) ;
Where USER_EMAIL is Constraint’s name you can choose constraint name as per you understanding.
2 .In Existing Table :
Example
ADD CONSTRAINT USER_EMAIL_UNIQUE_CONSTRAINT UNIQUE (Email);
Drop UNIQUE Key Constraint :
Example
DROP CONSTRAINT USER_EMAIL_UNIQUE_CONSTRAINT;
Note : In Mysql You have to Use Following Query to Drop the Constraint :
Example
DROP INDEX USER_EMAIL_UNIQUE_CONSTRAINT;
SQL Joins – INNER JOIN
SQL JOINS
SQL – Joins are used to fetch data collectivly from two or more than two tables. It Selects column values from the two tables & results it as combined data.
SQL Joins (INNER JOIN)
Sql INNER JOIN Selects the data from Both the tables only the matching records from both table.
Create An Order Table Which We will Use for joins.
Order Table Used for JOINS
CREATE TABLE IF NOT EXISTS `Orders` (
`OrderId` int(11) NOT NULL,
`UserId` int(11) NOT NULL,
`OrderItem` varchar(100) NOT NULL,
`Sysdate` datetime NOT NULL
) ;
ALTER TABLE `orders`
ADD PRIMARY KEY (`OrderId`);
INSERT INTO `customersdb`.`orders` (`orderId`, `userId`, `orderItem`, `Sysdate`) VALUES (‘1’, ‘2’, ‘Tees’, ‘2015-03-19 00:00:00’);
INSERT INTO `customersdb`.`orders` (`orderId`, `userId`, `orderItem`, `Sysdate`) VALUES ( ‘2’,’3′, ‘Shoes’, ‘2015-03-20 00:00:00’);
INSERT INTO `customersdb`.`orders` (`orderId`, `userId`, `orderItem`, `Sysdate`) VALUES (‘3′,’5’, ‘Shirt’, ‘2015-03-21 00:00:00’);
INSERT INTO `customersdb`.`orders` (`orderId`, `userId`, `orderItem`, `Sysdate`) VALUES (‘4’, ‘6’, ‘Watch’, ‘2015-03-21 00:00:00’);
INSERT INTO `customersdb`.`orders` (`orderId`, `userId`, `orderItem`, `Sysdate`) VALUES (‘5′,’8’, ‘Ipad’, ‘2015-03-22 00:00:00’);
Now We Have The Following Tables :
Orders Table :
Note : “UserId” In “Orders” Table is the id of the users which is assigned to users in “Users” Table in “ID” column. The Values stored in “UserId” Column are reffered from “Users” Table.
And
Users Table :
Simple Join Example Can Be Explained Using The Above Two Tables :
Example
Note : Simple JOIN keyword also refers to the INNER JOIN.
ON – : Condition is key for the joining two tables which related the tables using common columns reffered from the tables..
After Running The Query the following Result will be produced :
SQL Between
SQL Between Clause
SQL – Between Clause is used to select the between the two specified range.
SQL Between Clause Syntax
SELECT ColumnName1,ColumnName2… FROM TableName WHERE columnName BETWEEN Range1 AND Range2 ;
Suppose We Have the following Table :
Example
Will Produce The Following Result :
SQL – NOT Between Clause Syntax-
NOT BETWEEN Clause selects only those records which does not come in the specified range.
SELECT ColumnName1,ColumnName2… FROM TableName WHERE columnName NOt BETWEEN Range1 AND Range2 ;
Example
Will Produce The Following Result :
SQL In Operator
SQL – In Clause :
SQL In Clause is used to pass multiple values in WHERE condtion .
SQL In CLause Syntax :
SQL NOT In CLause Syntax :
SQL NOT selectes the results excluding the spcified value .(values in NOT IN clause).
Example
Example
Will Produce The Follwoing Result.
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
Will Produce The Follwoing Result.
Example :
Example
Will Produce The Follwoing Result.
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 :
Example : We want to delete row where id is ‘9’
Example
This Will Delete the row where id is ‘9’ . The Table will look Now :
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
SET columnName1 = newValue1,
SET columnName2 = newValue2,
SET columnName3 = newValue3
WHERE columnName = someColumnValue;
Suppose We have Following Data :
Update Example :
Example
The Above Query Will Update the row where email is ‘david@yahoo.com’ the update data will be as :
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
:
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 :
ORDER BY DESC Example :
Example
The Above Query Will Sort the result in descening order by Id. The Query Produces the result as :
ORDER BY ASC Example :
Example
The Above Query Will Sort the result in ascending order by Id. The Query Produces the result as :
ORDER BY Multiple Columns Example :
Example
The Above Query Will Sort the result in ascending order by Name & Email. The Query Produces the result as :
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 :
AND Clause Example :
Example
Where [City=”DELHI” and Name=’Riya’] Both Conditions are satisfied.
This Will Produce The Result As :
OR Clause Example :
Example
Where [City=”DELHI” OR Name=’Riya’] Either condition is satisfied.
This Will Produce The Result As :
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
This Will Produce The Result As :
