Under construction...
TOPICS (Click to Navigate)
- Advanced Database Concepts
- Data structures, Operating Systems
- Natural Language Processing
- Quiz Questions and Answers
- DBMS, ADBMS Question Bank
- SQL
- RDBMS Exam and Interview Questions
- Parallel Databases
- ADBMS Quizzes
- Advanced DBMS Concepts
- Distributed Databases
- Modern Databases - Special Purpose Databases
- Object Based Database Systems
Showing posts with label Set Operators. Show all posts
Showing posts with label Set Operators. Show all posts
Tuesday, June 10, 2014
SQL Set Operations
SQL Set Operations / SQL Set Operators / UNION, INTERSECT, and EXCEPT or MINUS Set Operators / Set Operators in DBMSs Oracle, MySQL, and DB2 / Set Operators with examples
SQL Set Operations
In SQL we have a set of
operations called Set Operations. Those are UNION, INTERSECT, and MINUS. They
are exactly like the relational-algebra set operations U, ∩,
and -.
We can write a SQL set operation
query using the following general syntax;
(Expression 1) θ (Expression 2),
Where, Expression 1 and 2 can be
any valid SQL query, and θ any one of the set operators U, ∩,
and -.
For example, expression 1 and
expression 2 can be something like,
SELECT * FROM student;
SELECT name, regno FROM student;
SELECT name, regno FROM student
WHERE name like ‘%s’;
In simple words, these
expressions are any of the valid SQL queries. Only requirement is that the
SELECT clause of both the expressions must be same. Read the next paragraph to
know about the basis requirements to perform Set operations.
Example:
Let us use the following relation
schemas for discussing the examples;
UG_Student (Regno:varchar,
Name:varchar, Phone:number)
PG_Student (Regno:varchar,
Name:varchar, Phone:number)
To retrieve both UG and PG
students’ Regno and Phone details, the query will look like Query 1 given below;
(SELECT Regno, Phone
FROM UG_Student) U (SELECT Regno, Phone FROM UG_Student)
----- Query 1
To retrieve the same phone
numbers held by both UG and PG students, the query will look like Query 2 given
below;
(SELECT Phone FROM
UG_Student) ∩ (SELECT Phone FROM PG_Student)
----- Query 2
Set Operation Properties:
To perform set operations, the queries have to satisfy the following conditions;
Condition 1: The Expression1 and Expression2
must specify the same number of attributes in the SELECT clause.
For example, see Query 1. In Query
1, the SELECT clause of expression 1 holds two attributes, Regno and Phone. In expression
2, the SELECT clause have two attributes.
Condition 2: The domain of the
attributes specified in the SELECT clause of both expressions should be in the same
order and the domains of the attributes also must be same.
For example, see Query 1. In
Query 1, the SELECT clause of Expression 1 has two attributes in the order
Regno and Phone. The SELECT clause of Expression 2 too has the same set of
attributes in the same order, i.e, Regno and Phone. And the domain of Regno is
varchar and Phone is numbers which are same in Expression 1 and 2.
Click below for Set operations.
Important Points to Remember
- Number of columns must be same in the expressions
- The domains of attributes and the order in which they represented in all the expressions should be same
- We can use many operators in one query. For example, we can write a query as follows;
- SELECT * FROM a UNION SELECT * FROM b UNION SELECT * FROM c;
- SELECT * FROM a UNION SELECT * FROM b INTERSECT SELECT * FROM c;
Subscribe to:
Posts (Atom)
Featured Content
Multiple choice questions in Natural Language Processing Home
MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...
All time most popular contents
-
Relational algebra in database management systems solved exercise Relational algebra – solved exercise Question: Consider the fo...
-
Top 5 Machine Learning Quiz Questions with Answers explanation, Interview questions on machine learning, quiz questions for data scienti...
-
Parallel query execution - example SQL queries / Inter-query, Intra-query parallelism examples / Inter-operation and Intra-operation paral...
-
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
-
Top 5 Machine Learning Quiz Questions with Answers explanation, Interview questions on machine learning, quiz questions for data scienti...