RELATIONAL ALGEBRA IN DBMS

Dr. Pankaj Dadhich February 10, 2022

 RELATIONAL ALGEBRA IN DBMS

  • Relational Algebra is a set of basic operations used to manipulate the data in a relational model and display results by a new relation.
  • It is a procedural query language.
  • it shows what data is to be retrieved and how to be retrieved.

 Types of operations in relational algebra

We have divided these operations into the following categories:

·         Basic Operations

·         Relational Operations

·         Derived Operations

 

Basic Set Operations (From Set Theory)

  • 1.    Union (∪)
  • 2.    Set Difference (-)
  • 3.    Intersection (∩)
  • 4.    Cartesian product (X)•
  •  

Relational Operations (Unary Relational Operations )

  • 1) Select (σ)
  • 2. Project (∏)
  • 3) Rename (ρ)

 Derived Operations: (Binary Relational Operations)

1. Join (⋈)
2. Division (÷)

 UNION

·         It is symbolized by ∪ the symbol.

·         It includes all tuples that are in tables A or in table B.

·         It also eliminates duplicate tuples.

Syntax:-  

table_name1 ∪ table_name2        

ex. A ∪ B

    • Relations must be the same number of attributes.
  • ·         Attribute domains need to be compatible.
  • ·         Duplicate tuples should be automatically removed.

·         Example

·         


 SQL example:-

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Ex.:-

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;

 

Example:

DEPOSITOR RELATION                                    BORROW RELATION

CUSTOMER_NAME

LOAN_NO

Jones

L-17

Smith

L-23

Hayes

L-15

Jackson

L-14

Curry

L-93

Smith

L-11

Williams

L-17

CUSTOMER_NAME

ACCOUNT_NO

Johnson

A-101

Smith

A-121

Mayes

A-321

Turner

A-176

Johnson

A-273

Jones

A-472

Lindsay

A-284

 

Input:

1.    ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)  

Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner

Jones

Lindsay

Jackson

Curry

Williams

Mayes

 

Note:

1) Union is a commutative operation, i.e, R ∪ S = S ∪ R.

2) Union is an associative operation, i.e. R ∪ (S ∪ T) = (R ∪ S) ∪ T.

 

Intersection

 



o    An intersection is defined by the symbol ∩

o    Defines result as a new relation consisting of a common set of all tuple that are in both A and B.

o    A and B must be union-compatible.

Syntax of Intersection Operator (∩)

table_name1 ∩ table_name2


 Example:-


Note:

1) Intersection is a commutative operation, i.e., R1 ∩ R2 = R2 ∩ R1.

2) Intersection is an associative operation, i.e., R1 ∩ (R2 ∩ R3) = (R1 ∩ R2) ∩ R3

 SQL commands:-

SELECT column1 , column2 .... FROM table_names WHERE condition INTERSECT SELECT column1 , column2 .... FROM table_names WHERE condition;

 Ex.:-

SELECT  ID, NAME, Amount, Date  FROM Customers LEFT JOIN Orders ON Customers.ID = Orders.Customer_id INTERSECT SELECT  ID, NAME, Amount, Date  FROM Customers RIGHT JOIN Orders ON Customers.ID = Orders.Customer_id; 

Output:

Example: Using the above DEPOSITOR table and BORROW table

Input:

  1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)  

Output:

CUSTOMER_NAME

Smith

Jones

 

 Set Difference (minus)

  • It includes all tuples that are in A but not in B.
  • The attribute name of A has to match with the attribute name in B.
  • The two-operand relations A and B should be either compatible or Union compatible.
  • It defined tuples that are in relation A, but not in B.
  • It is denoted by intersection minus (-).
Syntax of Set Difference (-)
table_name1 - table_name2

Example

Note: -

1) Difference operation is not commutative, i.e., R1 – R2 R2 – R1 ≠

2) Difference operation is not associative, i. e., R1 ─ (R2 – R3) (R1 – R2) – R3

 Example: Using the above DEPOSITOR table and BORROW table

Input:

  1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)  

Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

 SQL commands:-

SELECT column1 , column2 , ... column FROM table_name WHERE condition MINUS SELECT column1 , column2 , ... columnn FROM table_name WHERE condition;

Ex.

SELECT NAME, AGE, GRADE FROM Table1 MINUS  SELECT NAME, AGE, GRADE  FROM Table2;

 Cartesian product or Cross JOIN

  • It is also called Cross Product or Cross Join.
  • It merges columns from two relations.
  • It is denoted by the X symbol.
  • Example:- Two relations are R1 and R2. Cartesian product of these two relations (R1 X R2) would combine each tuple of first relation R1 with each tuple of second relation R2.

  • Syntax of the Cartesian product (X)
  • R3= R1 X R2
  • The product is commutative and associative.

Degree (R3) =Degree of (R1) + Degree (R2).

Example


Example:

EMPLOYEE                                                                            DEPARTMENT

EMP_ID

EMP_NAME

EMP_DEPT

1

Smith

A

2

Harry

C

3

John

B

 

DEPT_NO

DEPT_NAME

A

Marketing

B

Sales

C

Legal

 

Input:

1.    EMPLOYEE X DEPARTMENT  

Output:

EMP_ID

EMP_NAME

EMP_DEPT

DEPT_NO

DEPT_NAME

1

Smith

A

A

Marketing

1

Smith

A

B

Sales

1

Smith

A

C

Legal

2

Harry

C

A

Marketing

2

Harry

C

B

Sales

2

Harry

C

C

Legal

3

John

B

A

Marketing

3

John

B

B

Sales

3

John

B

C

Legal

 Sql commands:-

SELECT table1.column1 , table1.column2, table2.column1... FROM table1 CROSS JOIN table2;

Or

SELECT * FROM table1 CROSS JOIN table2;

Ex:- SELECT Student.NAME, Student.AGE, StudentCourse.COURSE_ID FROM Student CROSS JOIN StudentCourse;

 Example:-

the class_info table,

 

ID

NAME

1

abhi

2

adam

4

alex

ID

Address

1

DELHI

2

MUMBAI

3

CHENNAI

 

Cross JOIN query will be,

SELECT * FROM class CROSS JOIN class_info;

The resultset table will look like,

ID

NAME

ID

Address

1

abhi

1

DELHI

2

adam

1

DELHI

4

alex

1

DELHI

1

abhi

2

MUMBAI

2

adam

2

MUMBAI

4

alex

2

MUMBAI

1

abhi

3

CHENNAI

2

adam

3

CHENNAI

4

alex

3

CHENNAI

 ------------------------------------------------------------------------------------------------------------------   

Relational Operations

 SELECT (σ)(sigma)

The select operation is used to select some specific records/tuples from the database according to some specific criteria.

This is a unary mathematical operation, denoted by σ.

Syntax:-

σ Condition/Predicate(Relation/Table name)


Notation:  σ p(r)

Where:

σ is selection prediction
r is relation 
p is a propositional logic formula that uses logical (AND OR and NOT) and relational operators(=, ≠, ≥, <, >, ≤.). Where clause is used to fetch a particular row or set of rows from a table as a filter according to given conditions.

SQL Where Clause Syntax

SELECT Column_name1, Column_name2, .... FROM Table_name WHERE Condition;
 
Use this clause with other SQL statements such as UPDATE, DELETE, etc.

 Example:
Consider the relation PERSON. display details of persons having age less than or equal to 30:

σAGE <=30 (PERSON)

The resultant relation will be as follows:

note:- Select operation is commutative; i.e.,

σ <condition1> (σ <condition2> (R)) = σ <condition2> (σ <condition1> (R)

  more than one condition can be applied using Boolean operators AND & OR etc.

 Operators allowed in The WHERE Clause conditions

Operators List:
> Greater than operator
< Less than operator
= Equal operator
>= Greater than or equal
<= Less than or equal
<> Not equal.
IN To specify the set of values
BETWEEN To specify the range of values
LIKE To specify the pattern

 
Query:

SELECT * FROM EMPLOYEES WHERE EMP_SALARY > 5000 AND EMP_AGE > 23;
Result:

+------+----------+---------+----------+
|SSN   | EMP_NAME | EMP_AGE |EMP_SALARY|
+------+----------+---------+----------+
|  499 | Chaitanya|  29     |  6588.00 |
|  689 | Rajat    |  24     |  8900.00 |
+------+----------+---------+----------+
 

Select Operator (σ) Example
   


BRANCH_NAME

LOAN_NO

AMOUNT

Downtown

L-17

1000

Redwood

L-23

2000

Perryride

L-15

1500

Downtown

L-14

1500

Mianus

L-13

500

Roundhill

L-11

900

Perryride

L-16

1300

Input:

1.    σ BRANCH_NAME="perryride" (LOAN)  

Output:

BRANCH_NAME

LOAN_NO

AMOUNT

Perryride

L-15

1500

Perryride

L-16

1300

 ============================================================

The PROJECT operation

  • This is denoted by Π (pie).
  • It is used to select the records with specified attributes/ columns and discard the others.
  • It is based on some specific criteria and eliminates duplicate values.
  • It shows the list of those attributes that want to appear in the result.
  • The rest of the attributes are eliminated from the table.
  • It defines a relation that contains a vertical subset of Relation.

 Π List of attributes for the project (Relation)

1.    : ∏ A1, A2, An (r)   

Where

A1, A2, A3 is used as an attribute-name of relation r.

 Example of Projection:

Relation:- customer

CustomerID

CustomerName

Status

1

Google

Active

2

Amazon

Active

3

Apple

Inactive

4

Alibaba

Active

the projection of CustomerName and status:-

o    Π CustomerName, Status (Customers)

CustomerName

Status

Google

Active

Amazon

Active

Apple

Inactive

Alibaba

Active

 Note: -

1)        Π<List1>

2)     (Π <list2> (R))= Π<list1> (R)

As long as<list2> contains attributes in <list1>.

 SQL commands: -  One column:

SELECT column_name FROM table_name;

 

More than one column:


SELECT column_name_1, column_name_2, ... FROM table_name;

SELECT column_a, column_x from ABC;

 


For fetching the entire table:


SELECT * FROM table_name;

 SELECT

statement Clause

Clauses are:

WHERE Clause in SQL: 


WHERE clause filters the records according to specify a condition in the SQL statement:-


ORDER BY Clause in SQL: 


ORDER BY clause is used to return the rows in ascending or descending order of the data.


GROUP BY Clause in SQL: 


GROUP BY clause groups the rows with the same data, used along with the aggregate functions.


HAVING Clause in SQL: 


HAVING clause filters the records, just like the WHERE clause, and is used with the GROUP BY clause so it displays the records according to the set of rows produced by the GROUP BY clause.

 

Example: CUSTOMER RELATION

NAME

STREET

CITY

Jones

Main

Harrison

Smith

North

Rye

Hays

Main

Harrison

Curry

North

Rye

Johnson

Alma

Brooklyn

Brooks

Senator

Brooklyn

Input:

1.    ∏ NAME, CITY (CUSTOMER)  

Output:

NAME

CITY

Jones

Harrison

Smith

Rye

Hays

Harrison

Curry

Rye

Johnson

Brooklyn

Brooks

Brooklyn

===========================================

Rename (ρ)

Rename is a unary operation used for renaming attributes of a relation.

It is denoted by rho (ρ).

Rename (ρ) Syntax:
ρ(new_relation_name, old_relation_name)

ρ (a/b)

R will rename the attribute 'b' of relation by 'a '.

Example:-

From a table customer, fetch customer names and rename the resulting relation to CUST_NAMES.

Query:

ρ(CUST_NAMES, ∏(Customer_Name)(CUSTOMER))


Output:

CUST_NAMES

----------

Steve

Raghu

Chaitanya

Ajeet

Carl

 ================================================================


JOIN


SQL Join is used to fetch data from two or more tables(combining column(tuples) from two or more tables), and show in one relation.

JOIN Keyword is used in SQL queries.

The minimum required condition for joining table is (n-1) where n, is a number of tables.

Join operation is use a cartesian product with a selection criterion in a query.

Join operation denoted by ⋈.

Example:

SALARY                                         EMPLOYEE

EMP_CODE

SALARY

101

50000

102

30000

103

25000

EMP_CODE

EMP_NAME

101

Stephan

102

Jack

103

Harry

 

1.    Operation: (EMPLOYEE ⋈ SALARY)   

Result:

EMP_CODE

EMP_NAME

SALARY

101

Stephan

50000

102

Jack

30000

103

Harry

25000

 

Types of Join operations:

Various forms of join operation are:

Inner Joins:

  • Theta join
  • EQUI join
  • Natural join

Outer join:

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

 A table can also join itself, known as, Self Join.

 INNER Join or Equi Join

  • Equijoin(⋈): Equijoin is use equality condition holds between a pair of attributes.
  • only one attribute will appear in the result.
  • It is the most common join. It is based on matched data as per the equality condition (operator
  • (=)).  

 Inner Join Syntax:-

SELECT column-name-list FROM table-name1 INNER JOIN table-name2 WHERE table-name1.column-name = table-name2.column-name;

 Example: Select students whose ROLL_NO is equal to EMP_NO of employees

STUDENT⋈STUDENT.ROLL_NO=EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product, selection, and projection)

∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE EMPLOYEE.NAME, EMPLOYEE.ADDRESS, EMPLOYEE.PHONE, EMPLOYEE>AGE)(σ (STUDENT.ROLL_NO=EMPLOYEE.EMP_NO) (STUDENT×EMPLOYEE))

RESULT:

ROLL_NO

NAME

ADDRESS

PHONE

AGE

NAME

ADDRESS

PHONE

AGE

1

RAM

DELHI

9455123451

18

RAM

DELHI

9455123451

18

4

SURESH

DELHI

9156768971

18

SURESH

DELHI

9156768971

18

 Example:-

Consider a class table, the class_info table,

ID

NAME

1

Abhi

2

Adam

3

Alex

4

Anu

ID

Address

1

DELHI

2

MUMBAI

3

CHENNAI

 

SELECT * from class INNER JOIN class_info were class.id = class_info.id;

Output:-

ID

NAME

ID

Address

1

Abhi

1

DELHI

2

Adam

2

MUMBAI

3

Alex

3

CHENNAI

Example:

CUSTOMER RELATION                                      PRODUCT

CLASS_ID

NAME

    1

John

2

Harry

3

Jackson

   PRODUCT_ID

CITY

1

Delhi

2

Mumbai

3

Noida

 

 

 

 Input:

  1. CUSTOMER ⋈ PRODUCT    

Output:

CLASS_ID

NAME

PRODUCT_ID

CITY

1

John

1

Delhi

2

Harry

2

Mumbai

3

Harry

3

Noida

 

Natural JOIN (⋈): 

  • Natural Join is a type of Inner join which is based on a column having the same name and same datatype present in both the tables to be joined.
  • In this join no need to use the equality operator.
  • It will return the similar attributes only once as their value will be same in resulting relation.

 The syntax

SELECT * FROM table-name1 NATURAL JOIN table-name2;

Example of Natural JOIN

 Here is the class table, and the class_info table,

ID

NAME

1

Abhi

2

Adam

3

Alex

4

Anu

ID

Address

1

DELHI

2

MUMBAI

3

CHENNAI

  

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

The resultset table will look like this,

ID

NAME

Address

1

Abhi

DELHI

2

Adam

MUMBAI

3

Alex

CHENNAI

 Conditional Join(⋈c): 

  • Conditional Join is used to join two or more relations based on some conditions also called theta join.

Example: Select students whose ROLL_NO is greater than EMP_NO of employees

STUDENT⋈c STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

  • In terms of basic operators (cross product and selection) :

σ (STUDENT.ROLL_NO>EMPLOYEE.EMP_NO)(STUDENT×EMPLOYEE)

SQL commands:-

SELECT * FROM table-name1 NATURAL JOIN table-name2 where conditions;

 OUTER JOIN

  • In an outer join, use matching criteria, and also include some or all tuples that do not match the criteria (deal with missing information).
  • An outer join is basically three types:

  1. Left outer join
  2. Right outer join
  3. Full outer join

 LEFT Outer Join(⟕):

In this join, in the result, all tuples of the left relation, all matching tuples of right relations and if there is no matching tuple is found in right relation, then the attributes of right relation in the join result are filled with null values.

Example:-

two relations A and B, Left Outer Joins gives all tuples of A in the result set. The tuples of A which do not satisfy the join condition will have values as NULL for attributes of B.

  • Left outer join contains the set of tuples of all combinations in A and B that are equal on their common attribute names.
  • In the left outer join, tuples in A have no matching tuples in B.
  • It is denoted by ⟕.

 The syntax for Left Outer Join is,

SELECT column-name-list FROM table-name1 LEFT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

To specify a condition, we use the ON keyword with Outer Join.

 Example of Right Outer Join

Once again the class table,

ID

NAME

1

abhi

2

adam

3

alex

4

anu

5

ashish

and the class_info table,

ID

Address

1

DELHI

2

MUMBAI

3

CHENNAI

7

NOIDA

8

PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id = class_info.id);

 The resultant table will look like,

ID

NAME

ID

Address

1

abhi

1

DELHI

2

adam

2

MUMBAI

3

alex

3

CHENNAI

4

anu

null

null

5

ashish

null

null

 RIGHT Outer Join ((⟖): )

Just opposite to left outer join.

This join operation allows keeping all tuples in the right relation. if there is no matching tuple is found in the left relation, then the attributes of the left relation in the join result are filled with null values.

  • Right outer join contains the set of tuples of all combinations in A and B that are equal on their common attribute names.
  • In right outer join, tuples in B have no matching tuples in A.

It is denoted by ⟖.



The syntax for Right Outer Join

SELECT column-name-list FROM table-name1 RIGHT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

 Example of Right Outer Join

Once again the class table,

ID

NAME

1

abhi

2

adam

3

alex

4

anu

5

ashish

and the class_info table,

ID

Address

1

DELHI

2

MUMBAI

3

CHENNAI

7

NOIDA

8

PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id = class_info.id);

 The resultant table will look like,

ID

NAME

ID

Address

1

abhi

1

DELHI

2

adam

2

MUMBAI

3

alex

3

CHENNAI

null

null

7

NOIDA

null

null

8

PANIPAT

  Full Outer Join ((⟗)): 

  • In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition tuples filled by null value in both relations.

 Syntax of Full Outer Join:-

SELECT column-name-list FROM table-name1 FULL OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

 An example of Full outer join is,

The class table,

ID

NAME

1

abhi

2

adam

3

alex

4

anu

5

ashish

and the class_info table,

ID

Address

1

DELHI

2

MUMBAI

3

CHENNAI

7

NOIDA

8

PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info ON (class.id = class_info.id);

The resultset table will look like this,

ID

NAME

ID

Address

1

abhi

1

DELHI

2

adam

2

MUMBAI

3

alex

3

CHENNAI

4

anu

null

null

5

ashish

null

null

null

null

7

NOIDA

null

null

8

PANIPAT

 ==========================================

The DIVISION operation: (÷): 

  • Division operator A÷B can be applied if and only if:
  • §  Attributes of B is a proper subset of Attributes of A.
  • §  The relation returned by division operator will have attributes = (All attributes of A – All Attributes of B)
  • §  The relation returned by the division operator will return those tuples from relation A which are associated with every B’s tuple.

Example: -

Binary operator

 R ÷ S

• Attributes of S must be a subset of the attributes of R

• attr(R ÷ S) = attr(R) – attr(S)

• t tuple in (R ÷ S) iff (t × S) is a subset of R

It is “Inverse” of the cross product

 Example:

  • Interpretation of the division operation A/B:
    • Divide the attributes of A into 2 sets: A1 and A2.
    • Divide the attributes of B into 2 sets: B2 and B3.
    • Where sets A2 and B2 have the same attributes.
    • For each set of values in B2:
      • Search in A2 for the sets of rows (having the same A1 values) whose A2 values (taken together) form a set that is the same as the set of B2’s.
      • For all the set of rows in A which satisfy the above search, pick out their A1 values and put them in the answer.

Note:

Degree of relation: Degree (R÷S)=Degree of R – Degree of S.

 SQL commands:-

SELECT * FROM R WHERE x not in ( SELECT x FROM ( (SELECT x , y FROM (select y from S ) as p cross join (select distinct x from R) as sp) EXCEPT (SELECT x , y FROM R) ) AS r );

 Or

 SELECT * FROM R as sx WHERE NOT EXISTS ( (SELECT p.y FROM S as p ) EXCEPT (SELECT sp.y FROM  R as sp WHERE sp.x = sx.x ) );

Ex.

SELECT * FROM suppliers WHERE sid not in ( SELECT sid FROM ( (SELECT sid, pid FROM (select pid from parts) as p cross join  (select distinct sid from supplies) as sp) EXCEPT (SELECT sid, pid FROM supplies)) AS r );

Or

 SELECT * FROM suppliers as s WHERE NOT EXISTS (( SELECT p.pid FROM parts as p ) EXCEPT  (SELECT sp.pid FROM supplies sp WHERE sp.sid = s.sid ) );

 ========================================

Aggregate Functions and Operations

• Aggregation function takes a collection of values and returns a single value as a result.

avg: average value

min: minimum value

max: maximum value

sum: sum of values

count: number of values

SQL commands:-

SELECT     COUNT(*) FROM     products;

SELECT    AVG(unitsinstock) FROM     products;

SELECT   categoryid, SUM(unitsinstock) FROM     products GROUP BY categoryid;

SELECT     MIN(unitsinstock) FROM  products;

SELECT      MAX(unitsinstock) FROM     products;

 

================================================

 NESTED QUERIES (Sub-queries )

• A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. They are often referred to as a NESTED SELECT or SUB SELECT or INNER SELECT.

• The sub-query (inner query) executes first before the main query. The result of the sub-query is used by the main query (outer query).

• Sub-query can be placed in WHERE or HAVING or FROM clauses.

• Format of using sub-queries:

SELECT<select_list> FROM<table> WHERE expr OPERATOR (SELECT <select_list> FROM <TABLE>WHERE);

Operator includes a comparison operator (single or multiple row operators)

Single row operators: >, =, >=, <, <=, <>

 Multiple row operators: IN, ANY, ALL

• Order by clause cannot be used in sub-query, if specified it must be the last clause in the main select statement.

• Types of sub-queries:

·         Single-row sub-query: It returns only one row from the inner select statement.

·         Multiple row sub-queries: it returns more than one row from the inner select statement

·         Multiple column sub-queries: it returns more than one column from the inner select statement.

 Single row operators are used with single row sub-queries and multiple row operators are used with multiple-row subqueries.

• The Outer and Inner queries can get data from different tables.

• Group Functions can be used in subqueries.

 SELECT ENAME, JOB, SAL FROM EMP WHERE SAL = ( SELECT MIN (SAL) FROM EMP);

Output: ENAME

JOB

SAL

Nirmal

MANAGER

2975

 Example: Display the employees whose job title is the same as that of employee 7566 and salary is more than the salary of employee 7788.

SELECT ENAME, JOB FROM EMP WHERE JOB = ( SELECT JOB FROM EMP WHERE EMPPNO = 7566) AND SAL > ( SELECT SAL FROM EMP WHERE EMPPNO=7788);

Output: Job title for the employee 7566 happens to be ‘MANAGER’)

ENAME

JOB

Ashwini

MANAGER

 Having Clause with subqueries: First, we recollect the GROUP BY clause. The following query finds the minimum salary in each department.

SELECT DEPTNO, MIN(SAL) FROM.EMP GROUP BY DEPTNO;

Output:

DEPTNO

SAL

10

2975

20

3000

 Example: Find the name, department number, and salary of employees drawing minimum salary in that department.

SELECT ENAME, SAL, DEPTNO FROM EMP WHERE SAL IN (SELECT MIN (SAL) FROM EMP GROUP BY DEPTNO);

Output:

ENAME

SAL

DEPTNO

Nirmal

2975

10

Ashwin

3000

20

 

Find the salary of employees employed as an ANALYST

SELECT SAL FROM EMP WHERE JOB= ' ANALYST '

Output: SAL

3000

3000

 

Example: Find the salary of employees who are not ‘ANALYST’ but get a salary less than or equal to any person employed as ‘ANALYST’.

SELECT EMPNO, ENAME, JOB, SAL FROMEMP WHERE SAL <= ANY ( SELECT SAL FROM EMP WHERE JOB = 'ANALYST' ) AND JOB<>'ANALYST' ;

Output: EMPNO

ENAME

JOB

SAL

7566

Nirmal

MANAGER

2975

 

Find the average salary in each department

SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;

Result: DEPTNO

SAL

10

2987.5

20

4000

 

Example: Find out the employee who draws a salary more than the average salary of all the departments.

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL> ALL (SELECT AVG (SAL) FROM EMP GROUP BY DEPTNO);

Output: EMPNO

ENAME

JOB

SAL

7839

Karuna

PRESIDENT

5000

 

Example: Find the employee name, salary, department number, and average salary of his/her department, for those employees whose salary is more than the average salary of that department.

SELECT A.ENAME, A.SAL, A.DEPTNO, B.AVGSAL FROM EMP A, ( SELECT DEPTNO, AVG (SAL) AVGSAL FROM EMP GROUP BY DEPTNO) B WHERE A.DEPTNO=B.DEPTNO AND A.SAL> B. AVGSAL;

Output

: ENAME

SAL

DEPTNO

AVGSAL

Kailash

3000

10

2987.5

Karuna

5000

20

4000

 

Multiple column Queries:

Syntax:

SELECT COLUMN1, COL2,…… FROM TABLE WHERE (COLUMN1, COL2, …) IN (SELECT COLUMN1, COL2,…. FROM TABLE

WHERE <CONDITION>);

Example: Find the department number, name, job title, and salary of those people who have the same job title and salary as those are in department 10.

SELECT DEPTNO,ENAME, JOB, SAL FROM EMP WHERE (JOB, SAL) IN ( SELECT JOB, SAL FROM EMP WHERE EPTNO=10);

Output: DEPTNO

ENAME

JOB

SAL

10

Nirmal

MANAGER

2975

10

Kailash

ANALYST

3000

20

Ashwin

ANALYST

3000

 

==============================================================

 

Summary

Operation(Symbols)

Purpose

Select(σ)

Used for selecting a subset of the tuples according to a given selection condition

Projection(π)

Eliminates all attributes of the input relation but those mentioned in the projection list.

Union Operation(∪)

Includes all tuples that are in tables A or in B.

Set Difference(-)

Includes all tuples that are in A but not in B.

Intersection(∩)

Includes all tuple that are in both A and B.

Cartesian Product(X)

Merge columns from two relations.

Inner Join

Includes only those tuples that satisfy the matching criteria.

Theta Join(θ)

JOIN operation according to the conditional operator.

EQUI Join

Join uses only equivalence conditions.

Natural Join(⋈)

Join performed if there is a common attribute (column) between the relations.

Outer Join

Includes tuples that satisfy the matching criteria.

Left Outer Join( )

keeps all tuples in the left relation and common tuples of a right relation with null(not matching with left).

Right Outer join()

keeps all tuples in the right relation and common tuples of left relation with null(not matching with right).

Full Outer Join()

Keeps all tuples from both relations are included in the result irrespective of the matching condition fill null values.

 ==================================================

Tags

Post a Comment

0 Comments