Topic Name:joins Academic Year : 2018-2019

Subject Name:DBMS Semester : Third

A STYDY ON

The

MICRO PROJECT REPORT

Submitted in Nov 2018 by the group of……….students
Sr. Roll No Enrollment Seat No
Full name of Student
No (Sem-I) No (Sem-I)
1
2
3

Under the Guidance of

[ your guide name ]
in
Three Years Diploma Programme in Engineering & Technology of Maharashtra
State Board of Technical Education, Mumbai (Autonomous)
ISO 9001:2008 (ISO/IEC-27001:2013)
at

[ your college name ]

 MAHARASHTRA STATE BOARD OF TECHNICAL
EDUCATION, MUMBAI

Certificate
This is to certify that Mr. /Mrs

Roll No: of Third Semester of Diploma

Programme in Engineering & Technology at [ your college name] , has completed

the Micro Project satisfactorily in Subject DBMS in the academic year2018-2019 as

per the MSBTE prescribed curriculum of I Scheme.

Place:Pune EnrollmentNo:

Date: / /2018 Exam. SeatNo:

ProjectGuide Head ofthe Department Principal

Seal of Institute
 Index…

Sr. No Title Page No

1 Introduction

2 What is join?

3 Advantages of join

4 Types of joins

5 Inner join and example

6 Outer join and types

7 Left outer join and example

8 Second example of left outer join

9 Right outer join and example

10 Second example of right outer join

11 Full outer join and example

12 Second example of full outer join

Introduction:

One of the biggest issues beginning SQL writers have is being able to write queries
that use more than one table, that is to use database joins. In this series of articles
we are going to show you how to write a query that combines, or joins, data from
more than one table. Once you have gone through the examples you will understand
how to write the basic commands to make this happen and why data is separated in
the first place.

This first article introduces the concept of joining tables. The focus is going to be
more on the type of joins, not necessarily their syntax. The later articles focus on the
various types of joins. Through the narrative and examples you’ll become very
comfortable with each one.
What is join?

An Sql  join clause combines columns from one or more tables in a

relational database. It creates a set that can be saved as a table or used as it is.
A  JOIN  is a means for combining columns from one (self-join) or more tables by
using values common to each. ANSI-standard SQL specifies five types
of  JOIN :  INNER ,  LEFT OUTER ,  RIGHT OUTER ,  FULL OUTER  and  CROSS . As a
special case, a table (base table, view, or joined table) can  JOIN  to itself in a self-
join.

Advantages of joins:
1.Using JOINS, you can fetch exactly the data you want from any number of tables
with just one query, using any search parameter you chose to filter the results.
MySQL can also utilize things such as Indexes to maximize performance, which your
API code can not.

2. There is another advantage to the use of joins. By using joins instead of multiple
queries, you maximize the placement of the calculation burden on the database. This
means you can make better use of the database's abilities to search through, filter,
sort, etc.

Types of joins:
1.Inner join
2.outer join
3.left outer join
4.Right outer join
5.Full outer join

Inner join with example:

The inner JOIN is used to return rows from both tables that satisfy the given
condition.

Suppose , you want to get list of members who have rented movies together with
titles of movies rented by them. You can simply use an INNER JOIN for that, which
returns rows from both tables that satisfy with given conditions.

SELECT members.`first_name` , members.`last_name` , movies.`title`

FROM members ,movies
WHERE movies.`id` = members.`movie_id`

Executing the above script give

first_name last_name Title

Adam Smith ASSASSIN'S CREED: EMBERS
Ravi Kumar Real Steel(2012)
Susan Davidson Safe (2012)
Jenny Adrianna Deadline(2009)
Lee Pong Marley and me

Note the above results script can also be written as follows to achieve the same
results.

SELECT A.`first_name` , A.`last_name` , B.`title`

FROM `members`AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Outer join with types:

The joined table retains each row—even if no other matching row exists. Outer joins
subdivide further into left outer joins, right outer joins, and full outer joins, depending
on which table's rows are retained (left, right, or both).
(In this case left and right refer to the two sides of the  JOIN  keyword.)
No implicit join-notation for outer joins exists in standard SQL.

Types of outer join

1.left outer join

2.right outer join
3.full outer join

Left outer join and example:

Assume now you want to get titles of all movies together with names of members
who have rented them. It is clear that some movies have not being rented by any
one. We can simply use LEFT JOIN for the purpose.

. The LEFT JOIN returns all the rows from the table on the left even if no matching
rows have been found in the table on the right.  Where no matches have been
found in the table on the right, NULL is returned .

SELECT A.`title` , B.`first_name` , B.`last_name`

FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

Executing the above script in MySQL workbench gives.You can see that in the
returned result which is listed below that for movies which are not rented, member
name fields are having NULL values. That means no matching member found
members table for that particular movie.

Title first_name last_name

ASSASSIN'S CREED: EMBERS Adam Smith
Real Steel(2012) Ravi Kumar
Safe (2012) Susan Davidson
Deadline(2009) Jenny Adrianna
Marley and me Lee Pong
Alvin and the Chipmunks NULL NULL
The Adventures of Tin Tin NULL NULL
Safe House(2012) NULL NULL
GIA NULL NULL
The Dirty Picture NULL NULL
Note: Null is returned for non-matching rows on right
Second example of left outer join:
SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID =
department.DepartmentID;

Employee.Last Employee.Depart Department.Departme Department.Depart

Name mentID ntName mentID

Jones 33 Engineering 33

Rafferty 31 Sales 31

Robinson 34 Clerical 34

Smith 34 Clerical 34

Williams NULL NULL NULL

Heisenberg 33 Engineering 33

Alternative syntaxes[
Oracle supports the deprecated[8] syntax:

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)

SELECT *
FROM employee, department
WHERE employee.DepartmentID *= department.DepartmentID

SELECT *
FROM employee, OUTER department
WHERE employee.DepartmentID = department.DepartmentID
Right outer join and example:

RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the
columns from the table on the right even if no matching rows have been found in the
table on the left. Where no matches have been found in the table on the left, NULL is
returned.

In our example,  let's assume that you need to get names of members and movies
rented by them. Now we have a new member who has not rented any movie yet .

SELECT A.`first_name` , A.`last_name`, B.`title`

FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Executing the above script in MySQL workbench gives the following results. 

first_name last_name Title

Adam Smith ASSASSIN'S CREED: EMBERS
Ravi Kumar Real Steel(2012)
Susan Davidson Safe (2012)
Jenny Adrianna Deadline(2009)
Lee Pong Marley and me
NULL NULL Alvin and the Chipmunks
NULL NULL The Adventures of Tin Tin
NULL NULL Safe House(2012)
NULL NULL GIA
NULL NULL The Dirty Picture

Note: Null is returned for non-matching rows on left

Second example of right outer join:

SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;

Employee.Last Employee.Depart Department.Departme Department.Depart

Name mentID ntName mentID

Smith 34 Clerical 34

Jones 33 Engineering 33

Robinson 34 Clerical 34

Heisenberg 33 Engineering 33

Rafferty 31 Sales 31

NULL NULL Marketing 35

Right and left outer joins are functionally equivalent. Neither provides any
functionality that the other does not, so right and left outer joins may replace each
other as long as the table order is switched.
Full outer join and example:

In SQL the FULL OUTER JOIN combines the results of both left and rigth outer joins and
returns all (matched or unmatched) rows from the tables on both sides of the join clause.

Syntax:
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Syntax diagram - FULL OUTER JOIN

Example: SQL FULL OUTER JOIN

Let’s combine the same two tables using a full join.

SQL Code:

SELECT * FROM table_A

FULL OUTER JOIN table_B

ON table_A.A=table_B.A;

Copy

Output:

Because this is a full join, all rows (both matching and nonmatching) from both tables are
included in the output. There is only one match between table table_A and table table_B,
so only one row of output displays values in all columns. All remaining rows of output
contain only values from table table_A or table table_B, with the remaining columns set to
missing values

Second example of full outer join:

SELECT *
FROM employee FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;

Employee.LastNam Employee.DepartmentI Department.DepartmentNam Department.DepartmentI

e D e D

Smith 34 Clerical 34

Jones 33 Engineering 33

Robinson 34 Clerical 34

Williams NULL NULL NULL

Heisenberg 33 Engineering 33

Rafferty 31 Sales 31

NULL NULL Marketing 35

Result:
Conclusion:
REFRENCE:

1.Techmax book of dbms.

2.Technical book of dbms.

3.https://en.wikipedia.org/wiki/Join_(SQL)

Weekly Work / Progress Report …

 Details of 16 Engagement Hours of the Student
Regarding Completion of the Project
Timing
Week Sign of
Date Duration Work or activity Performed
No. From To the Guide
in hours
Discussion and Finalization of the
1 / /2018 One hour
Project Title
Two Preparation and Submission of
2 / /2018
hours Abstracts
Two
3 / /2018 Literature Review
hours
Two
4 / /2018 Collection of Data
hours
Two
5 / /2018 Collection of Data
hours

6 / /2018 One hour Discussion and Outline of Content

7 / /2018 Two hour Rough Writing of the Projects Contents

Editing and Proof Reading of the

8 / /2018 One hour
Contents

9 / /2018 Two hour Final Completion of the Project

Seminar Presentation, viva-vice,

10 / /2018 One hour
Assessment and Submission of Report

Name of Project Guide: