Scribd
Upload
375 views7 pages

SQL Joins and Set Operations Lab

The document describes an experiment involving SQL queries using set operations and joins. It provides the syntax and examples of different types of SQL joins, including inner joins, left outer joins, right outer joins, full outer joins, and natural joins. It also discusses cross joins. The experiment involves writing SQL queries on tables in an Oracle database to demonstrate these join types.

Uploaded by

pratik43roy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
375 views7 pages

SQL Joins and Set Operations Lab

The document describes an experiment involving SQL queries using set operations and joins. It provides the syntax and examples of different types of SQL joins, including inner joins, left outer joins, right outer joins, full outer joins, and natural joins. It also discusses cross joins. The experiment involves writing SQL queries on tables in an Oracle database to demonstrate these join types.

Uploaded by

pratik43roy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Experiment No.

3
Environment: Microsoft Windows
Tools/ Language: Oracle/SQL

Objective: Write the SQL queries using Set Operations and Joins.

Theory & Concepts:

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed
whenever two or more tables are joined in a SQL statement.

There are different types of SQL joins:

SQL INNER JOIN (or sometimes called simple join)


SQL CROSS JOIN
SQL NATURAL JOIN
SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
SQL FULL OUTER JOIN (or sometimes called FULL JOIN)

SQL INNER JOIN (SIMPLE JOIN)


SQL INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax
The syntax for the SQL INNER JOIN is:

SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];

If the tables COUNTRIES and CITIES have two common columns named
POPULATION and COUNTRY_ISO_CODE, JOIN applies equality condition on ISO
codes with cities having less POPULATION attributes:

SELECT * FROM
COUNTRIES
INNER JOIN CITIES
On COUNTRIES. COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] > [Link];

SQL LEFT OUTER JOIN


Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows
from the LEFT-hand table specified in the ON condition and only those rows from the
other table where the joined fields are equal (join condition is met).

Syntax
The syntax for the SQL LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];
In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

SELECT * FROM
COUNTRIES
LEFT JOIN CITIES
On COUNTRIES. COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] > [Link];

SQL RIGHT OUTER JOIN


Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all
rows from the RIGHT-hand table specified in the ON condition and only those rows
from the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the SQL RIGHT OUTER JOIN is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON [Link] = [Link];
In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

SELECT * FROM
COUNTRIES
RIGHT JOIN CITIES
On COUNTRIES. COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] > [Link];

SQL FULL OUTER JOIN


Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all
rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the
join condition is not met.

Syntax
The syntax for the SQL FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON [Link] = [Link];
In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

SELECT * FROM
COUNTRIES
FULL JOIN CITIES
On COUNTRIES. COUNTRY_ISO_CODE=CITIES. COUNTRY_ISO_CODE
And [Link] > [Link];
SQL NATURAL JOIN

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you
based on the common columns in the two tables being joined. Common columns are
columns that have the same name in both tables.

If the SELECT statement in which the NATURAL JOIN operation appears has an
asterisk (*) in the select list, the asterisk will be expanded to the following list of
columns (in this order):

 All the common columns


 Every column in the first (left) table that is not a common column
 Every column in the second (right) table that is not a common column

An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to


every column of that table that is not a common column.

Syntax
Select *
FROM table1
NATURAL JOIN table2;
Examples

If the tables COUNTRIES and CITIES have two common columns named COUNTRY
and COUNTRY_ISO_CODE, NATURAL JOIN applies equality condition on both
attributes:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES;

CROSS JOIN operation

A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.
Unlike other JOIN operators, it does not let you specify a join clause. You may,
however, specify a WHERE clause in the SELECT statement.
Examples
The following SELECT statements are equivalent:
SELECT * FROM CITIES CROSS JOIN SELECT * FROM CITIES, FLIGHTS
FLIGHTS
Practical Assignment - 3

Department: Computer Engineering & Applications


Course: [Link]. (CSE)
Subject: Database Management System Lab (CSE3083)
Year: 2nd Semester: 3rd

Run the following Script:

BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;
/

commit;
drop table College;
drop table Student;
drop table Apply;

create table College(collegeName varchar2(10) primary key, state


varchar2(10), enrollment int);
create table Student(sID int primary key, sName varchar2(10), GPA
real, sizeHS int);
create table Apply(sID int, cName varchar2(10), major varchar2(20),
decision char(1), primary key(sID, major, cName), constraint sID_fk
Foreign key(sID) references Student, constraint cName_fk Foreign
key(cName) references College);

delete from Student;


delete from College;
delete from Apply;

insert into Student values (123, 'Amy', 3.9, 1000);


insert into Student values (234, 'Bob', 3.6, 1500);
insert into Student values (345, 'Craig', 3.5, 500);
insert into Student values (456, 'Doris', 3.9, 1000);
insert into Student values (567, 'Edward', 2.9, 2000);
insert into Student values (678, 'Fay', 3.8, 200);
insert into Student values (789, 'Gary', 3.4, 800);
insert into Student values (987, 'Helen', 3.7, 800);
insert into Student values (876, 'Irene', 3.9, 400);
insert into Student values (765, 'Jay', 2.9, 1500);
insert into Student values (654, 'Amy', 3.9, 1000);
insert into Student values (543, 'Craig', 3.4, 2000);
insert into College values ('Stanford', 'CA', 15000);
insert into College values ('Berkeley', 'CA', 36000);
insert into College values ('MIT', 'MA', 10000);
insert into College values ('Cornell', 'NY', 21000);
insert into College values ('Harvard', 'MA', 50040);
insert into Apply values (123, 'Stanford', 'CS', 'Y');
insert into Apply values (123, 'Stanford', 'EE', 'N');
insert into Apply values (123, 'Berkeley', 'CS', 'Y');
insert into Apply values (123, 'Cornell', 'EE', 'Y');
insert into Apply values (234, 'Berkeley', 'biology', 'N');
insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
insert into Apply values (345, 'Cornell', 'CS', 'Y');
insert into Apply values (345, 'Cornell', 'EE', 'N');
insert into Apply values (678, 'Stanford', 'history', 'Y');
insert into Apply values (987, 'Stanford', 'CS', 'Y');
insert into Apply values (987, 'Berkeley', 'CS', 'Y');
insert into Apply values (876, 'Stanford', 'CS', 'N');
insert into Apply values (876, 'MIT', 'biology', 'Y');
insert into Apply values (876, 'MIT', 'marine biology', 'N');
insert into Apply values (765, 'Stanford', 'history', 'Y');
insert into Apply values (765, 'Cornell', 'history', 'N');
insert into Apply values (765, 'Cornell', 'psychology', 'Y');
insert into Apply values (543, 'MIT', 'CS', 'N');
commit;
Student Apply
sID sName GPA sizeHS sID cName major decision
123 Amy 3.9 1000 123 Stanford CS Y
234 Bob 3.6 1500 123 Stanford EE N
345 Craig 3.5 500 123 Berkeley CS Y
456 Doris 3.9 1000 123 Cornell EE Y
567 Edward 2.9 2000 234 Berkeley biology N
678 Fay 3.8 200 345 MIT bioengineering Y
789 Gary 3.4 800 345 Cornell bioengineering N
987 Helen 3.7 800 345 Cornell CS Y
876 Irene 3.9 400 345 Cornell EE N
765 Jay 2.9 1500 678 Stanford history Y
654 Amy 3.9 1000 987 Stanford CS Y
543 Craig 3.4 2000 987 Berkeley CS Y
College 876 Stanford CS N

collegeName state enrollment 876 MIT biology Y


876 MIT marine biology N
Stanford CA 15000
765 Stanford history Y
Berkeley CA 36000
765 Cornell history N
MIT MA 10000
765 Cornell psychology Y
Cornell NY 21000
543 MIT CS N
Harvard MA 50040

Write SQL Queries for the following:


Q1. Produce a combine table in which each student is combine with every other application.
Q2. Give Student ID, name, GPA and name of college and major each student applied to.
Q3. Find detail of applications who applied to California State.
Q4. IDs, name, GPA of students and name of college with GPA > 3.7 applying to Stanford
Q5. Find detail of Student who apply to CS major and their application are rejected
Q6. Find detail of student and application who applied to colleges at New York
Q7. Find detail of student who have not applied to any of college
Q8. Find college where no student have applied
Q9. Find sID who have only one application
Q10. Find name and GPA of applicants who apply to any college whose enrollment is not more
than 25000.
Q11. Find pair of students (sID) having same GPA. (each pair should occur just once in result)
Exercise
For each of the following you need to write three queries
i.e. three version first using :CROSS Join
Second using: Natural Join
And third using: Inner Join
You are also advised to observe output of all three
Q12. Find student and major he / she applied to.
Q13. Find detail of student who came from high school have size less than 20000 and applied to CS
at Stanford.
Q14. Provide complete detail of each student where they applied what major they applied to what
was the decision and complete detail of college they applied.
Q15. Names and GPAs of students with HS>1000 who applied to CS and were rejected
Q16. Names and GPAs of students with HS>1000 who applied to CS at college with
enr>20,000 and were rejected

Pre Experiment Questions


1. When we need to combine two tables?
2. Difference between Equi Join and Theta Join
3. Difference between Natural join and Inner Join
Post Experiment Questions
1. When can we use natural join?
2. When we are bound to use inner join?
3. Can we implement all joins using cross join?
4. Where and in what kind of queries require outer joins?

Common questions

Powered by AI

The SQL INNER JOIN explicitly defines the condition upon which two tables should be joined, offering flexibility and control over the join process. This ensures that only rows meeting specific conditions are returned, allowing for precise data retrieval . In contrast, NATURAL JOIN automatically identifies common columns across tables and applies the join based on these columns, reducing the need for specifying join conditions . While NATURAL JOIN simplifies join conditions, it may inadvertently join tables on unintended columns if they have identical names, leading to unexpected results, making it less controllable than INNER JOIN .

SQL JOINS, particularly complex ones involving multiple tables, can significantly impact database performance as they often require considerable computational resources to execute. The performance implications include increased processing time and resource utilization, especially in joins like FULL OUTER and CROSS JOIN, which handle large volumes of data by default . Optimization can be achieved using indexed columns in join conditions, minimizing the dataset size retrieved by filtering rows upfront (using WHERE clauses), and selecting only necessary columns to reduce the data volume handled in joins . Proper query planning and leveraging efficient indexing structures can ameliorate the computational overheads associated with joins .

The main challenge in deciding between a NATURAL JOIN and a CROSS JOIN comes from their inherently different operations and impacts on query outcomes. NATURAL JOIN automatically joins tables based on common column names, which can lead to unintended joins if care is not taken to verify the schema . CROSS JOIN, producing Cartesian products, can result in large, unwieldy datasets impacting performance negatively . Addressing these challenges involves validating common columns for NATURAL JOINs and employing filtering constraints to manage data volumes in CROSS JOINs . A precise understanding of desired dataset outcomes and careful schema review is essential for correctly selecting between these join operations .

A SQL NATURAL JOIN can lead to unintended results when tables have unexpectedly matching column names that were not intended to be used in a join context. This occurs because NATURAL JOIN automatically uses columns with the same name across both tables for the join condition . This can be mitigated by carefully analyzing table schema before using NATURAL JOIN to ensure no irrelevant columns share names, or by using more explicit join types like INNER JOIN where the join conditions are clearly specified .

Understanding set operations and joins is fundamental for securing databases as they dictate how data is interlinked and retrieved, which can potentially expose sensitive information if misconfigured. Properly structured joins ensure least privilege access by only retrieving data necessary for specific queries, preventing unintentional access to non-related or sensitive information . Additionally, comprehension of joins allows for setting precise access controls and queries audit trails, mitigating risks of unauthorized data access and exfiltration by limiting the dataset scope during retrieval processes . Implementing these security practices involves careful definition of roles and permissions aligned with the join operations utilized in query execution .

Understanding the type of SQL JOIN used is critical because different joins have distinct mechanisms that significantly influence the dataset retrieved. Each join type - INNER, LEFT, RIGHT, or FULL OUTER JOIN - dictates which rows are included based on the join conditions and table orientation (left or right). INNER JOINS will only show matching rows, while OUTER JOINS include unmatched rows from one or both tables . Misinterpreting the JOIN type can lead to incorrect data analysis, such as losing essential records or misrepresenting information in aggregate queries .

SQL INNER JOIN and FULL OUTER JOIN can provide the same results when every row in one table matches with every row in another table according to the join condition, as INNER JOIN only includes rows with matched join keys, and FULL OUTER JOIN includes the same and fills non-matching entries with nulls. When there are no unmatched rows in either table, the complete datasets will overlap, and both joins will return identical results . This ideal scenario occurs predominantly in well-normalized datasets where foreign key constraints ensure total referential integrity .

A SQL FULL OUTER JOIN is preferred when it is essential to retrieve all rows from both tables being joined, displaying nulls where there is no match. This join type is useful in scenarios where one requires a comprehensive view of all data points from two datasets, even if some data do not have corresponding entries in the other table . In contrast, LEFT or RIGHT OUTER JOINs only prioritize returning all data from one specific table, potentially leaving out unmatched rows from the other table, which could lead to loss of important information on non-matching data entries .

SQL CROSS JOIN differs in that it performs a Cartesian product of the two tables involved, resulting in a set combining each row from the first table with each row from the second table. This differs from other joins like INNER, LEFT, RIGHT, and FULL OUTER JOINs, which rely on defined join conditions to limit the number of combined rows . The primary drawback of CROSS JOIN is that it can produce an extremely large number of entries if either table involved has a significant number of rows, which can lead to performance issues and computational inefficiencies . This makes it less practical for large datasets unless further filtered by a WHERE clause .

A RIGHT OUTER JOIN is more appropriate when it is necessary to include all rows from the right-hand table in the query results, even if there are no corresponding matches in the left-hand table. This is opposite to a LEFT OUTER JOIN, where the focus is on including all data from the left table irrespective of matches in the right . For example, when the primary concern is to ensure complete visibility and integrity of data originating from the right-hand table, RIGHT OUTER JOIN guarantees complete data representation compared to a LEFT OUTER JOIN .

You might also like