Set Operations-Introduction




The SQL operations union, intersect, and except operate on relations and correspond to the mathematical set-theory operations ∪, ∩, and −. We shall now construct queries involving the union, intersect, and except operations over two sets.

• The set of all courses taught in the Fall 2009 semester:

select course_id
from section
where semester = ’Falland year= 2009;

• The set of all courses taught in the Spring 2010 semester:

Topics You May Be Interested In
Characteristics Of The Database Approach Sql Data Definition
Purpose Of Database Systems The Natural Join
Data Mining And Information Retrieval Weak Entity Types
Specialty Databases Access Control, User Accounts, And Database Audits
Relational Query Languages Control Measures

select course_id
from section
where semester = ’Spring’ and year= 2010;

Set Operations-Introduction

In our discussion that follows, we shall refer to the relations obtained as the result of the preceding queries as c1 and c2, respectively, and show the results when these queries are run on the section relation of Figure 2.6 in Figures 3.9 and 3.10.Observe that c2 contains two tuples corresponding to course_id CS-319, since two sections of the course have been offered in Spring 2010.

1. The Union Operation 

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Relational Operations
Data Storage And Querying Basic Structure Of Sql Queries
Transaction Management Relationship Types, Relationship Sets, Roles, And Structural Constraints
Data Mining And Information Retrieval Relationship Types Of Degree Higher Than Two
Structure Of Relational Databases Ordering The Display Of Tuples

To find the set of all courses taught either in Fall 2009 or in Spring 2010, or both,we write:

(select course_id
from section
where semester = ’Fall’ and year= 2009)
union
(select course_id
from section
where semester = ’Spring’ and year= 2010);

The union operation automatically eliminates duplicates, unlike the select clause.
Thus, using the section relation of Figure 2.6, where two sections of CS-319 are
offered in Spring 2010, and a section of CS-101 is offered in the Fall 2009 as well as
in the Fall 2010 semester, CS-101 and CS-319 appear only once in the result, shown
in Figure 3.11.

If we want to retain all duplicates, we must write union all in place of union:

Topics You May Be Interested In
Database-system Applications Relational Databases
Specialization And Generalization Data Mining And Information Retrieval
Data Abstraction, Knowledge Representation, And Ontology Concepts Structure Of Relational Databases
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Database Languages Control Measures

(select course_id
from section
where semester = ’Fall’ and year= 2009)
union all
(
select course_id
from section
where semester = ’Spring’ and year= 2010);

The number of duplicate tuples in the result is equal to the total number of
duplicates that appear in both c1 and c2. So, in the above query, each of CS-319
and CS-101 would be listed twice. As a further example, if it were the case that 4
sections of ECE-101 were taught in the Fall 2009 semester and 2 sections of ECE-101 were taught in the Fall 2010 semester, then there would be 6 tuples with ECE-101 in the result.

Set Operations-Introduction

 

Topics You May Be Interested In
Database-system Applications Database Schema
View Of Data Introduction To Database Security Issues
Relational Databases Basic Structure Of Sql Queries
Transaction Management A Sample Database Application
Database Architecture Database Security And The Dba

2. The Intersect Operation

To find the set of all courses taught in the Fall 2009 as well as in Spring 2010 we
write:

(select course_id
from section
where semester = ’Fall’ and year= 2009)
intersect
(select course_id
from section
where semester = ’Spring’ and year= 2010);

The result relation, shown in Figure 3.12, contains only one tuple with CS-101. The
intersect operation automatically eliminates duplicates. For example, if it were
the case that 4 sections of ECE-101 were taught in the Fall 2009 semester and 2
sections of ECE-101 were taught in the Spring 2010 semester, then there would be
only 1 tuple with ECE-101 in the result.

Topics You May Be Interested In
Relational Databases Entity Types, Entity Sets, Keys, And Value Sets-2
Discretionary Access Control Based On Granting And Revoking Privileges Initial Conceptual Design Of The Company Database
Introduction To Database Security Issues Example Of Notation- Uml Class Diagrams
Xml: Extensible Markup Language Relationship Types Of Degree Higher Than Two
Sql Data Definition Aggregate Functions

If we want to retain all duplicates, we must write intersect all in place of
intersect:

Set Operations-Introduction

(select course_id
from section
where semester = ’Fall’ and year= 2009)
intersect all
(select course_id
from section
where semester = ’Spring’ and year= 2010);

The number of duplicate tuples that appear in the result is equal to the minimum
number of duplicates in both c1 and c2. For example, if 4 sections of ECE-101
were taught in the Fall 2009 semester and 2 sections of ECE-101 were taught in the
Spring 2010 semester, then there would be 2 tuples with ECE-101 in the result.

Topics You May Be Interested In
Constraints And Characteristics Of Specialization And Generalization Hierarchies Database Users And Administrators
Using High-level Conceptual Data Models For Database Design Keys
Purpose Of Database Systems Schema Diagrams
Introduction To Transaction Processing Overview Of The Sql Query Language
Database Architecture Entity Types, Entity Sets, Attributes, And Keys-1

3. The Except Operation

To find all courses taught in the Fall 2009 semester but not in the Spring 2010
semester, we write:

(select course_id
from section
where semester = ’Fall’ and year= 2009)
except
(select course_id
from section
where semester = ’Spring’ and year= 2010);

The result of this query is shown in Figure 3.13. Note that this is exactly relation
c1 of Figure 3.9 except that the tuple for CS-101 does not appear. The except
operation7 outputs all tuples from its first input that do not occur in the second
input; that is, it performs set difference. The operation automatically eliminates
duplicates in the inputs before performing set difference. For example, if 4 sections
of ECE-101 were taught in the Fall 2009 semester and 2 sections of ECE-101 were
taught in the Spring 2010 semester, the result of the except operation would not
have any copy of ECE-101.

Topics You May Be Interested In
View Of Data Database Schema
Database-system Applications Relational Operations
A Sample University Eer Schema, Design Choices, And Formal Definitions Overview Of The Sql Query Language
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Er Diagrams, Naming Conventions, And Design Issues
Introduction To Transaction Processing Database Security And The Dba

If we want to retain duplicates, we must write except all in place of except:

(select course_id
from section
where semester = ’Falland year= 2009)
except all
(select course_id
from section
where semester = ’Springand year= 2010);

Set Operations-Introduction

The number of duplicate copies of a tuple in the result is equal to the number of
duplicate copies in c1 minus the number of duplicate copies in c2, provided that
the difference is positive. Thus, if 4 sections of ECE-101 were taught in the Fall
2009 semester and 2 sections of ECE-101 were taught in Spring 2010, then there are 2 tuples with ECE-101 in the result. If, however, there were two or fewer sections
of ECE-101 in the the Fall 2009 semester, and two sections of ECE-101 in the Spring
2010 semester, there is no tuple with ECE-101 in the result.

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Introduction To Database Security Issues
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Keys, And Value Sets-2
Schema Diagrams Weak Entity Types
Relational Query Languages Sensitive Data And Types Of Disclosures
Relational Operations Control Measures


Frequently Asked Questions

+
Ans: Four main control measures are used to provide security of data in databases: Access control, Inference control, Flow control, Data encryption view more..
+
Ans: SQL includes a between comparison operator to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value. view more..
+
Ans: The database administrator (DBA) is the central authority for managing a database system. The DBA’s responsibilities include granting privileges to users who need to use the system and classifying users and data in accordance with the policy of the organization view more..
+
Ans: The SQL operations union, intersect, and except operate on relations. view more..
+
Ans: Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations. view more..
+
Ans: Aggregate functions view more..
+
Ans: The main Idea for the Sampling Algorithm is to select a small sample , one that fits in main memory of the database of transaction view more..
+
Ans: The main idea of Sampling Algorithm is to select a small sample , one that fits in main memory , of the database of transaction view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: We have restricted our attention until now to the extraction of information from the database. Now, we show how to add,remove, or change information with SQL. view more..
+
Ans: We introduced the natural join operation. SQL provides other forms of the join operation, including the ability to specify an explicit join predicate, and the ability to include in the result tuples that are excluded by natural join. We shall discuss these forms of join in this section. view more..
+
Ans: In our examples up to this point, we have operated at the logical-model level. That is, we have assumed that the relations in the collection we are given are the actual relations stored in the database. view more..
+
Ans: A transaction consists of a sequence of query and/or update statements. view more..
+
Ans: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. view more..




Rating - 3/5
453 views

Advertisements