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 SQL. Show all posts
Showing posts with label SQL. 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;
Monday, March 3, 2014
SQL SELECT Statement
SELECT Statement in SQL Explained
As you know,
Structured Query Language (SQL) consists of various ways to define various
database components. Data Manipulation Language (DML) is one of the ways to view
the database components which is frequently used one. A DML statement is used
to view the data stored in tables, insert new records and update the existing
record values. In this post, let us discuss in clear about the SELECT
statement.
A DML
statement (also called as SQL expression) basic structure consists of at least
two clauses, namely, SELECT and FROM to retrieve some results. To pinpoint some
information in a table, we need one more clause called WHERE clause as part of
the basic SQL structure. To display the required information from one or more
specific tables in a required way, we could use these three basic clauses
differently.
Simple
structure of SQL statement;
SELECT parameters (*/list
of attributes/functions/…)
FROM parameters (table/set
of tables/views/…)
WHERE parameters (condition/set
of conditions);
See an
example SELECT statement;
SELECT * FROM
employee WHERE emp_id = ‘E101’;
The above SQL
query (here after mentioned as query) will display all the records whichever
satisfying the condition emp_id=’E101’ from employee table.
Every clause in
SQL is capable for representing something. Those are;
SELECT clause:
SELECT clause
uses
list of one or more attributes as its parameters. Using SELECT clause
one could mention what we would like to show as the result. That is,
- do you like to display complete table(s),
- do you like to display select attributes from table(s)
- do you like to perform some arithmetic operations
- do you like to display the result of application of functions etc.
As a result,
the SELECT clause is designed to mention all the above and more.
Parameters
|
Function/Result
|
Example Query
|
*
|
‘*’ means all the attributes of the table will be displayed as result
|
SELECT * FROM Employee;
|
Attribute/list of attributes
|
The attribute/list of attributes from the table will be shown in the
result. All other attributes of table will not be shown
|
SELECT Emp_ID, EName FROM Employee;
|
DISTINCT attribute_name
|
The unique values stored in the specified attribute will be shown as
result.
|
SELECT DISTINCT EName FROM Employee;
This will show only one Employee Name if more than one such names
exist.
|
Aggregate functions
(max(), min(), avg(), sum(), count())
|
According to the aggregate function used, the aggregated value will
be shown. Usually, the result will contain only one record in the result in
case of aggregate functions.
|
SELECT SUM(salary) FROM Employee;
|
Table 1 – List of parameters of SELECT clause
Detailed Example:
Emp_ID
|
EName
|
Department
|
Address
|
Salary
|
E101
|
Kumar
|
Finance
|
Chennai
|
15000
|
E102
|
Virat
|
Marketing
|
Bangalore
|
25000
|
E105
|
Sachin
|
Production
|
Mumbai
|
25000
|
E103
|
Gurucharan
|
Finance
|
Kolkata
|
30000
|
E110
|
Kumar
|
Maintenance
|
Chennai
|
14000
|
Table 2 - Employee
Result of the
queries given in the Example Query column of table 1 for the instance given in
table 2 are discussed below. (Note : note carefully the heading of results)
Query 1: SELECT * FROM Employee;
Result : Table 2 as it is.
Query 2: SELECT Emp_ID, EName FROM
Employee;
Result :
Emp_ID
|
EName
|
E101
|
Kumar
|
E102
|
Virat
|
E105
|
Sachin
|
E103
|
Gurucharan
|
E110
|
Kumar
|
Query 3: SELECT DISTINCT EName FROM
Employee;
Result :
DISTINCT EName
|
Kumar
|
Virat
|
Sachin
|
Gurucharan
|
Query 4: SELECT SUM(salary) FROM
Employee;
Result :
SUM(Salary)
|
109000
|
Query 5: SELECT Emp_ID, EName, salary*(10/100)
FROM Employee;
Result :
Emp_ID
|
EName
|
Salary*(10/100)
|
E101
|
Kumar
|
1500
|
E102
|
Virat
|
2500
|
E105
|
Sachin
|
2500
|
E103
|
Gurucharan
|
3000
|
E110
|
Kumar
|
1400
|
FROM clause:
The parameters
of the FROM clause are the list of one or more tables/list of one or more views/list
of both tables and views. You can refer to table 1 ‘Example Query’ column for
example usages of FROM clause for single table queries and single view queries.
For queries which involve multiple tables, you must understand the concept of
JOIN. We shall discuss about that later.
WHERE clause:
The parameters
of the WHERE clause are the list of
conditions written in the form,
(Attribute_Name θ
Value) Ф (Attribute_Name θ Value) Ф
(Attribute_Name θ Value)
Ф …
Here, θ is the
comparison operator [ >, <, ≥, ≤,
=, and <> (Not equal to) ],
and Ф is the logical
connective [and, or, not]
For example,
- Emp_ID = ‘E101’ (Character based values are provided within pair of apostrophes ‘ ’)
- Salary > 10000 (Number based attribute values can be provided without apostrophes)
- Emp_ID = ‘E101’ and Salary <5000
- Emp_ID = ‘E101’ and Salary <5000 or EName = ‘Kumar’
It is very
easy to pinpoint some information of any tables using WHERE conditions. Hence,
WHERE clause can be termed as Filtering Component of SQL query.
Let us see
the results of some queries with WHERE conditions; (Note : the satisfied
records are shown with Bolded and Italicized values.)
Query 1 :
SELECT * FROM Employee WHERE Emp_ID = ‘E110’;
Result :
Emp_ID
|
EName
|
Department
|
Address
|
Salary
|
E110
|
Kumar
|
Maintenance
|
Chennai
|
14000
|
The result
displays the table as it is because we used * in the SELECT clause.
Query 2 :
SELECT Emp_ID, EName FROM Employee WHERE EName = ‘Kumar’;
Result :
Emp_ID
|
EName
|
E101
|
Kumar
|
E110
|
Kumar
|
Query 3 : SELECT Emp_ID,
EName, Salary FROM Employee WHERE EName = ‘Kumar’ AND Salary > 14000;
Result :
Emp_ID
|
EName
|
Salary
|
E101
|
Kumar
|
15000
|
************************
Related links:
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...