Category Archives: Sql

SQL FOREIGN Key

SQL FOREIGN Key Constraint

A Foreign key is used to define the relation between two tables . A Foreign key points to the primary key in another table.

Suppose We Have The Following Tables :

Orders Table :

OrderTable
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 referred from “Users” Table.

And

Users Table :

SQL FOREIGN Key tableFull

UserId In Orders table is the primary key in table Users ie . column ID(Primary Key).
Now to define this relation we have to make foreign key in Orders table.

There are Two Ways to Define Foreign Key :

1 . At the time of Creation of table :

Example

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,
CONSTRAINT usersID_FOREIGN_KEY FOREIGN KEY (UserId)
REFERENCES Users(ID);
) ;

2 . In Existing table :

Example

ALTER TABLE Orders ADD
CONSTRAINT usersID_FOREIGN_KEY FOREIGN KEY (UserId)
REFERENCES Users(ID);

Delete Foreign Key Constraint :

Example

ALTER TABLE Orders DROP
CONSTRAINT usersID_FOREIGN_KEY;

Note : In Mysql Following Query Works.

Example

ALTER TABLE Orders DROP
FOREIGN KEY usersID_FOREIGN_KEY;

SQL PRIMARY Key

SQL – PRIMARY Key Constraint :

1 . At The Time Of Table Creation :

SQL PRIMARY Key : PRIMARY Key Constraint is used to identify every record uniquely which can’t be null. There can be only one primary key in a table.

SQL – PRIMARY Key Constraint Syntax Example :

Example

CREATE TABLE UserProfile (
ID int(11) NOT NULL,
Name varchar(110) NOT NULL,
Email varchar(100) NOT NULL,
CONSTRAINT USER_ID_PRIMARY_KEY PRIMARY KEY (ID)
) ;

Where USER_EMAIL is Constraint’s name you can choose constraint name as per you understanding.

2 .In Existing Table :

Example

ALTER TABLE UserProfile
ADD
CONSTRAINT USER_ID_PRIMARY_KEY PRIMARY KEY (ID);

Drop PRIMARY Key Constraint :

Example

ALTER TABLE UserProfile
DROP CONSTRAINT USER_ID_PRIMARY_KEY;

Note : In Mysql You have to Use Following Query to Drop the Constraint :

Example

ALTER TABLE UserProfile
DROP INDEX USER_ID_PRIMARY_KEY;

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

CREATE TABLE UserProfile (
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

ALTER TABLE UserProfile
ADD CONSTRAINT USER_EMAIL_UNIQUE_CONSTRAINT UNIQUE (Email);

Drop UNIQUE Key Constraint :

Example

ALTER TABLE UserProfile
DROP CONSTRAINT USER_EMAIL_UNIQUE_CONSTRAINT;

Note : In Mysql You have to Use Following Query to Drop the Constraint :

Example

ALTER TABLE UserProfile
DROP INDEX USER_EMAIL_UNIQUE_CONSTRAINT;

SQL – FULL JOIN

SQL -FULL JOIN

SQL – FUll JOIN `selects all rows from left table and the right table. It Returns both left & Right Join’s data together.

Syntax : SQL – FULL JOIN

SELECT LeftTable.ColumnName1,LeftTable.ColumnName2…RightTable.ColumnName1,RightTable.ColumnName2… FROM LeftTable FULL JOIN RightTable ON (LeftTable.ColumnName = RightTable.ColumnName);

We Have The Following Tables :

Orders Table :

OrderTable
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 :

SQL - FULL JOIN  Example

Left Join Example Can Be Explained Using The Above Two Tables :

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users Full JOIN Orders ON (Users.ID= Orders.UserId);

Or

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users Full OUTER JOIN Orders ON (Users.ID= Orders.UserId);

Note : Few Databases do not support FULL JOIN.

For Those databases which do not support full join you can use following method :

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users LEFT JOIN Orders ON (Users.ID= Orders.UserId)
UNION ALL
SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users RIGHT JOIN Orders ON (Users.ID= Orders.UserId);

The Above Left Join Will Produce :

SQL - FULL JOIN  Example

SQL – RIGHT JOIN

SQL – RIGHT JOIN

SQL – LEFT JOIN `selects all rows from right table and the matching rows from the left table.

SQL – RIGHT JOIN Syntax

:

SELECT LeftTable.ColumnName1,LeftTable.ColumnName2…RightTable.ColumnName1,RightTable.ColumnName2… FROM LeftTable RIGHT JOIN RightTable ON (LeftTable.ColumnName = RightTable.ColumnName);

We Have The Following Tables :

Orders Table :

SQL - RIGHT JOIN
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 :

tableFull

Left Join Example Can Be Explained Using The Above Two Tables :

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users RIGHT JOIN Orders ON (Users.ID= Orders.UserId);

The Above Left Join Will Produce :

SQL - RIGHT JOIN

SQL – LEFT JOIN

SQL -LEFT JOIN

SQL – LEFT JOIN `selects all rows from left table and the matching rows from the right table.

Syntax : SQL – LEFT JOIN

SELECT LeftTable.ColumnName1,LeftTable.ColumnName2…RightTable.ColumnName1,RightTable.ColumnName2… FROM LeftTable LEFT JOIN RightTable ON (LeftTable.ColumnName = RightTable.ColumnName);

We Have The Following Tables :

Orders Table :

SQL - LEFT JOIN
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 :

SQL - LEFT JOIN

Left Join Example Can Be Explained Using The Above Two Tables :

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users LEFT JOIN Orders ON (Users.ID= Orders.UserId);

The Above Left Join Will Produce :

Sql_left_JOIN

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 :

OrderTable
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 :

tableFull

Simple Join Example Can Be Explained Using The Above Two Tables :

Example

SELECT users.Name,Users.Email,Orders.OrderItem FROM Users JOIN Orders ON (Users.ID= Orders.UserId);

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 Joins results

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 :

tableFull

Example

SELECT * FROM Users WHERE ID BETWEEN 5 AND 8 ;

Will Produce The Following Result :

Between_clause

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

SELECT * FROM Users WHERE ID NOT BETWEEN 5 AND 8 ;

Will Produce The Following Result :

SQL Between SQL_NOT_BETWEEN_CLAUSE

SQL In Operator

SQL – In Clause :

SQL In Clause is used to pass multiple values in WHERE condtion .

SQL In CLause Syntax :

SELECT ColumnName1, ColumnName2 FROM TableName WHERE ColumnName IN (value1,value2,value3,value4…valueN);

SQL NOT In CLause Syntax :
SQL NOT selectes the results excluding the spcified value .(values in NOT IN clause).

SELECT ColumnName1, ColumnName2 FROM TableName WHERE ColumnName NOT IN (value1,value2,value3,value4…valueN);


Example

Example

SELECT *FROM Users WHERE ID IN (1,5,7);

Will Produce The Follwoing Result.
SQL_IN_CLAUSE