Department Of Computer Science

B. Sc.IT (Data Science) -II

U72A3DMS - DATABASE MANAGEMENT SYSTEM
UNIT-4 Interactive SQL Part II:
Oracle function serve the purpose of manipulating data items and returns a result

Function can be classified into two types.

A. Group Function(Aggregate function):

Function that act on a set of values is called Group Function.

B. Scalar Function(Single row functions)

Function that act only on one value at a time are called scalar functions.

(1) String functions

(2) Numeric functions
(3) Date functions
(4) Conversion functions
Group function(Aggregate function)

AVG() function:- Returns the average of values

Syntax: select avg(column name) from tablename

SQL> select * from stud_marks;

SID SNAME CITY MARKS

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

101 Anjali Baroda 89

102 Raj Baroda 70

103 Tanishka Ahmedabad 92

104 anaya Ahmedabad 56

105 Ruhi surat 67

106 khushi surat 77

6 rows selected.

SQL> select avg(marks) "value" from stud_marks;

 value

----------

75.1666667

MIN() function: The aggregate function SQL MIN() is used to find the minimum value or
lowest value of a column or expression. This function is useful to determine the smallest of
all selected values of a column.
Syntax : MIN([ALL | DISTINCT] expression )
SQL> select min(marks) "value" from stud_marks;
value
----------

56
MAX() function
The aggregate function SQL MAX() is used to find the maximum value or highest value of a
certain column or expression. This function is useful to determine the largest of all selected
values of a column.
SQL Syntax : MAX ([ALL | DISTINCT] expression
SQL> select max(marks) "value" from stud_marks;
value
----------
92

SUM() function
The SQL AGGREGATE SUM() function returns the sum of all selected column.
SQL Syntax : SUM ([ALL | DISTINCT] expression )
Example : SUM()
SQL> select sum(marks) "value" from stud_marks;
value
----------

451
COUNT() function
The SQL COUNT function returns the number of rows in a table satisfying the criteria
specified in the WHERE clause. It sets on the number of rows or non NULL column values.
SQL Syntax : COUNT(*) , COUNT( [ALL|DISTINCT] expression )

pg. 2
Example : COUNT()

SQL> select count(*) from stud_marks;

COUNT(*)

----------

SCALAR FUNCTIONS

SQL Server Scalar Functions allow you to perform different calculations on data values.
These functions operate on single rows only and produce one result per row. There are
different types of Scalar Functions, this tutorial covers the following:

 String functions – functions that perform operations on character values.

 Numeric functions – functions that perform operations on numeric values.
 Date functions – functions that perform operations on date values.
 Conversion functions – functions that convert column data type.

String functions
are used to perform an operation on input string and return an output string.
Following are the string functions defined in SQL:

LENGTH(): This function is used to find the length of a line.

SQL> select length('Nikita') "Length" from dual;

Length

----------

LOWER(): This function is used to convert the upper case string into lower case.

SQL> select lower('WELCOME') "lower" from dual;

lower

-------

Welcome

1. UPPER(): This function is used to convert the lower case string into upper case.

SQL> select upper('welcome')"upper" from dual;

pg. 3
upper

-------

WELCOME

INITCAP(CHAR): It returns a string with the first letter of each word in uppercase.

SQL> select initcap('welcome home') "initcap" from dual;

initcap

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

Welcome Home

SUBSTR(): This function is used to find a sub string from the a string from the given position.

SQL> select substr('hello world',2,4) "sub string" from dual;

sub

----

Ello

LTRIM(): This function is used to cut the given sub string from the original string.

SQL> select ltrim('NIKiTA','N') "left" from dual;

left

-----

IKiTA

RTRIM(): This function is used to cut the given sub string from the original string.

SQL> select rtrim('anjali','i') from dual;

RTRIM

-----

anjal

LPAD(): This function is used to make the given string of the given size by adding the given
symbol.

pg. 4
SQL> select lpad('hello',10,'*') "left pad" from dual;

left pad

----------

*****hello

RPAD(): This function is used to make the given string as long as the given size by adding the
given symbol on the right.

SQL> select rpad('hello',10,'*') "right pad" from dual

right pad

----------

hello*****

Numeric functions

Numeric Functions are used to perform operations on numbers and return numbers.

ABS(): It returns the absolute value of a number.

CEILING(): It returns the smallest integer value that is greater than or equal to a number.

FLOOR(): It returns the largest integer value that is less than or equal to a number.

LEAST(): It returns the smallest value in a list of expressions.

EXP(): It returns e raised to the power of number.

MOD(): It returns the remainder of n divided by m.

POWER(): It returns m raised to the nth power.

ROUND(): It returns a number rounded to a certain number of decimal places.

TRUNCATE(): This doesn’t work for SQL Server. It returns 7.53635 truncated to 2 places right
of the decimal point.

SQRT(): It returns the square root of a number.

Example:-

SQL> Select abs(-15) from dual;

pg. 5
 ABS(-15)

----------

15

SQL> Select power(5,2) "raised" from dual;

raised

----------

25

SQL> SELECT ROUND(5.553) "round" from dual;

round

----------

SQL> select round(15.19,1) "round value" from dual;

round value

-----------

15.2

SQL> select sqrt(25) "sqrt" from dual;

sqrt

----------

SQL> select mod(18,4) "mode" from dual;

mode

----------

pg. 6
SQL> SELECT POWER(4, 2) "power" from dual;

power

----------

16

SQL> SELECT LEAST(30, 2, 36, 81, 125) "least" from dual;

least

----------

DATE FUNCTIONS

Add_months(d,n)

It returns the date after adding the number of months specified in the
function.

Select add_months(‘1-AUG-11’,2)”ANS” from dual;

Output ANS

01-OCT-11

Last_day(d):

It returns the last date of the month specified in the function.

Select last_day(’01-AUG-11’)”ANS” from dual;

Select sysdate,last_day(sysdate)”Ans” from dual;

Ans

31-aug-2022

Months_between(d1,d2):

It returns the number of months between d1 and d2.

SQL> select months_between('02-feb-1998','2-jan-1998') "months" from dual;

months

pg. 7
----------

SQL> select next_day('04-feb-98','sunday') from dual;

NEXT_DAY(

---------

08-FEB-98

current_timestamp: It returns date,time of current date.

Select current_timestamp from dual;

current_date :It returns current date.

Select current_date from dual;

SQL> Select current_timestamp from dual;

CURRENT_TIMESTAMP

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

14-MAR-23 03.40.59.964000 PM +05:30

SQL> select sysdate,last_day(sysdate) from dual;

SYSDATE LAST_DAY(

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

15-MAR-23 31-MAR-23

SQL> select add_months(sysdate,4) from dual;

ADD_MONTH

---------

15-JUL-23

Conversion Functions

If the programmer wants,they can explicitly convert data types from one to another. There
are SQL functions provided for this express purpose. These SQL functions are −

pg. 8
TO_NUMBER

Converts character value containing a number in the value of the number datatype

select to_number(substr('$100',2,3)) from dual;

TO_NUMBER(SUBSTR('$100',2,3))

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

100

update product_master set sell_price= sell_price + to_number(substr('$100',2,3));

Here, the value 100 will be added to every products selling price in the product
master table

TO_CHAR (NUMBER CONVERSION)

Number is converted to char values exactly long enough to hold significant digits

Select to_char(12345,’$099,999’) “ char” from dual’

char

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

$012,345

TO_CHAR (DATE CONVERSION)

select to_char(sysdate,'month dd, yyyy') from dual;

TO_CHAR(SYSDATE,'M

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

FEBURARY 24, 2011

SQL Function Description SQL Query Example

Math function
Abs(n) Returns the absolute value of n. Select Abs(-15);
O/P : 15
Sign(n) Returns the sign of x as -1,0,1 Select Sign(-15);
O/P : -1
Ceiling(n) Returns the smallest integer value Select ceil(24.8);
that is smaller than or equal to a O/P : 25
number.

pg. 9
Floor(n) Returns the largest integer value Select Floor(24.8);
that is greater than or equal to a O/P : 24
number.
Power (m,n) Returns m raised to nth power. Select power(3,2);
O/P : 9
Round (n,m) Returns n rounded to m places the Select round(15.91,1);
right of decimal point. O/P : 15.9
Square(n) Returns square of n. Select Square(4);
O/P : 16
Sqrt(n) Returns square root of n. Select sqrt(25);
O/P : 5
Exp(n) Returns e raised to the nth power, Select exp(1); O/P : 1
e=2.17828183.
X%Y Returns reminder of X modulus Y. Select 5%3;
O/P : 2
Pi() Returns the value of pi. Select Pi();
O/P : 3.14159265358979
Sin(n) Returns the sine value of n. Select Sin(0);
O/P : 0
Cos(n) Returns the cosine value of n. Select Cos(0);
O/P : 1
Tan(n) Returns the tangent value of n. Select Tan(0);
O/P : 0
Rand(n) Returns a random decimal number Select Rand();
between 0 and 1. O/P : 0.845610816728278
Log(n) Returns the log value of n having Select Log(1);
base e. O/P : 0
Log10(n) Returns the log value of n having Select Log10(1000);
base 10. O/P : 3

String function
ASCII(x) Returns ASCII value of character. Select ASCII(‘A’);
O/P : 65
Char(x) Returns a character of int ASCII Select CHAR(65);
code. O/P : A
Concat() Concatenates two strings. Select CONCAT(‘great’,’DIET’);
O/P : greatDIET
Len () Returns the number of character in Select LEN(‘DIET’);
x. O/P : 4
Lower() Converts the string to lower case. Select LOWER(‘DIET’);
O/P : diet
Upper() Converts the string to upper case. Select UPPER(‘diet’);
O/P : DIET
Ltrim() Trim blanks from the left of x. Select LTRIM(‘ diet ’);
O/P : diet
Rtrim() Trim blanks from the right of x. Select RTRIM(‘ diet ’);
O/P : diet
Replace() Looks for the string and replace the Select Replace(‘this is college’,’is’,’may
string every time it occurs. be’);
O/P :thmay be may be college
Substring() Returns the part of string Select Substring(‘this is college’,6,7);
O/P : is my c
Left() Returns the specified number of Select Left(‘this is college’,7);
characters from the left. O/P : this is

pg. 10
 Right() Returns the specified number of Select Right(‘this is college’,7);
characters from the right. O/P : college
Reverse() Returns the reversed string. Select Reverse(‘DIET’);
O/P : TEID
Space() Returns n number of spaces Select Space(10);
O/P :
Replicate() Returns repeated string for n Select Replicate(‘DIET’,2);
number of times. O/P :DIETDIET

Date function
Getdate() Returns current date and time. Select Getdate();
O/P : 2018-09-08 10:42:02.113
Day() Returns day of a given date. Select Day(‘23/JAN/2018’);
O/P : 23
Month() Returns month of a given date. Select Month(‘23/JAN/2018’);
O/P : 1
Year() Returns year of a given date. Select Year(‘23/JAN/2018’);
O/P : 2018
Isdate() Returns 1 if the expression is a valid Select Isdate(‘31/FEB/2018’);
date, otherwise 0. O/P : 0
Datename() Returns the specified part of a given Select Datename(month,‘1-23-2018’);
date as varchar value. O/P : January
Datepart() Returns the specified part of a given Select Datepart(month,‘1-23-2018’);
date as int value. O/P : 1
Dateadd() Returns datetime after adding n Select Dateadd(day,5,‘23/JAN/2018’);
numbers of datepart to a given O/P : 2018-01-28 00:00:00.000
date.
Datediff() Returns the difference between two Select Datediff(day,5,
date values, based on the interval ‘23/JAN/2018’,’23/FEB/2018’);
specified. O/P : 31
Eomonth() Returns the last day of month. Select Eomonth(’23/FEB/2018’);
O/P : 2018-01-31

Discuss aggregate functions with example(s).

 Aggregate functions perform a calculation on a set of values and return a single
value.
 Aggregate functions ignore NULL values except COUNT(*).
 It is used with the GROUP BY clause of the SELECT statement.
Example: Consider following table

Student
Rollno Name SPI
1 Raju 8
2 Hari 9
3 Mahesh 7
4 NULL 9
5 Anil 5
1. Avg() : It returns the average of the
data values.Select Avg(SPI)
FROM Student;

pg. 11
 Output: 7
2. Sum() : It returns the addition of the
data values.Select Sum(SPI)
FROM Student;
Output: 38
3. Max() : It returns maximum value for a
column.Select Max(SPI) FROM
Student; Output: 9
4. Min() : It returns Minimum value for a
column.Select Min(SPI) FROM
Student; Output: 5
5. Count() : It returns total number of values in a given
column.Select Count(Name) FROM Student;
Output: 4
6. Count(*) : It returns the number of rows in
a table.Select Count(*) FROM
Student;
Output: 5
Group by and Having Clause
 The group by and having clause are parallel to the order by and where clause, except
that they act on record sets, and not on individual records.
 The group functions perform an operation on a group of rows and return one result.
 When a column is used in GROUP BY clause, the result is sorted in ascending order
by that column by default. i.e., GROUP BY has implied ORDER BY. Still ORDER BY can
be used to explicitly change the implied sorted order.
 The having clause is used to give conditions and filter the groups created by group by
clause.
 The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".
 The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(),
MIN(), SUM(), AVG()) to group the result-set by one or more columns.
 GROUP BY Syntax
 SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Example:-
CUSTOMER

SQL> select * from con;

pg. 12
 CID CNAME COUNTRY
---------- -------------------- --------------------
1 nikita india
2 rahul canada
3 siya india
4 anjali india
5 parul chin
6 rahul canada
7 laxman india
Example 1:
select country from con group by country;

COUNTRY
--------------------
chin
india
canada
Example 2:
select count(cid)"country",country from con group by country;

country COUNTRY
-------- --------------------
1 chin
4 india
2 canada

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.

HAVING Syntax
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

SQL HAVING Examples

The following SQL statement lists the number of customers in each country. Only include
countries with more than 5 customers:

Example
SQL> select count(cid)"country",country from con group by country having count(cid)=4;

pg. 13
 country COUNTRY
---------- --------------------

4 india

Group by using Rollup and Cube operator

The ROLLUP operator is used with the GROUP BY clause. It is used to create subtotals and
grand totals for a set of columns. The summarized amounts are created based on the columns
passed to the ROLLUP operator.

The CUBE operators, like the ROLLUP operator produces subtotals and grand totals as
well. But unlike the ROLLUP operator it produces subtotals and grand totals for every
permutation of the columns provided to the CUBE operator.

Example:- (Rollup)

select count(cid)"country",country from con group by rollup(country);

country COUNTRY

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

2 canada

1 chin

4 india

Cube Example:

select count(cid)"country",country from con group by cube(country)

country COUNTRY

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

1 chin

4 india

2 canada

pg. 14
Subquery:-

A subquery is a SQL query nested inside a larger query.

 The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement
or inside another subquery.
 A subquery is usually added within the WHERE Clause of another SQL SELECT
statement.
 You can use the comparison operators, such as >, <, or =. The comparison operator
can also be a multiple-row operator, such as IN, ANY, or ALL.
 A subquery is also called an inner query or inner select, while the statement
containing a subquery is also called an outer query or outer select.
 The inner query executes first before its parent query so that the results of an inner
query can be passed to the outer query.
 In a subquery, the outer query's result is dependent on the result-set of the inner
subquery. That's why subqueries are also called nested queries.
 ( SELECT [DISTINCT] subquery_select_parameter
 FROM {table_name | view_name}
 {table_name | view_name} ...
 [WHERE search_conditions]
 [GROUP BY column_name [,column_name ] ...]
 [HAVING search_conditions] )

Here, the SQL command

1. executes the subquery first; selects minimum age from the Customers table.
2. executes the outer query; selects rows where age is equal to the result of subquery.

 You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to

perform the following tasks:

Example:- (select)

SQL> select * from cust;

CID CNAME AGE COUNTRY

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

1 john 31 USA

2 robert 22 USA

3 david 22 UK

4 john 25 UK

5 betty 28 UAE

pg. 15
SQL> select * from cust where age=(select min(age) from cust);

CID CNAME AGE COUNTRY

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

2 robert 22 USA

3 david 22 UK

Example (insert)

SQL> create table product1(pid int,pro_name varchar(10),price number(10));

Table created

SQL> select * from product1;

PID PRO_NAME PRICE

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

101 Monitor 15000

102 Keyboard 2500

103 Mouse 2000

104 printer 20000

105 scanner 17000

102 mouse 1000

103 mouse 600

103 mouse 1200

106 mouse 800

107 mouse 1100

SQL> create table order12(oid int,product_sold varchar(10),sell_price number(10));

Table created.

SQL> insert into order12 select pid,pro_name,price from product1

pg. 16
 where pid in(select pid from product1 where price>1000);

9 rows created.

SQL> select * from order12;

OID PRODUCT_SO SELL_PRICE

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

101 Monitor 15000

102 mouse 1000

102 Keyboard 2500

103 mouse 1200

103 mouse 600

103 Mouse 2000

104 printer 20000

105 scanner 17000

107 mouse 1100

9 rows selected.

Update subquery: -

SQL>update order12 set sell_price=(select max(sell_price)+100 from order12)

where oid=101;

updated.

SQL>select * from order12;

Delete subquery:-

SQL> delete from order12 where oid in(select pid from product1 where
pro_name='scanner');

1 row deleted.

SQL> select * from order12;

pg. 17
Types of Subqueries

 Single Row Sub Query: Sub query which returns single row output. ...
 Multiple row sub query: Sub query returning multiple row output. ...
 Correlated Sub Query: Correlated subqueries depend on data provided by the outer
query.

MULTIPLE ROW SUB QUERY

In Multiple row, subquery returns one or more rows to the outer SQL statement.

You may use the IN, ANY, or ALL operator in outer query to handle a subquery that
returns multiple rows.

SELECT * FROM Student2

WHERE AGE IN ( SELECT AGE
FROM Student1
WHERE COUNTRY=’UK’);

Correlated Sub Query:

Correlated subqueries are used for row-by-row processing. Each subquery is

executed once for every row of the outer query.

A correlated subquery is evaluated once for each row processed by the parent
statement.

The parent statement can be a SELECT, UPDATE, or DELETE statement.

SELECT column1, column2, ....

FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 =
outer.expr2);

What is join? List and explain various types of joins.

 A SQL Join statement is used to combine data or rows from two or more tables
based ona common field between them.
 Different types of Joins are:
 Inner Join
 Outer Join
1. Left Outer Join
2. Right Outer Join

pg. 18
 3. Full Outer Join
 Cross join
 Self Join
INNER JOIN
 It returns records that have matching values in both tables.
Syntax:
SELECT columns
FROM table1 INNER JOIN
table2 ON table1.column =
table2.column;
Example:
Consider the following tables:
Student Result
RNO Name Branch RNO SPI
101 Raju CE 101 8.8
102 Amit CE 102 9.2
103 Sanjay ME 104 8.2
104 Neha EC 105 7
105 Meera EE 107 8.9
106 Mahesh ME

SELECT Student.RNO, Student.Name, Student.Branch,

Result.SPIFROM Student
INNER JOIN Result
ON Student.RNO = Result.RNO;

Output:
Inner Join
RNO Name Branch SPI
101 Raju CE 8.8
102 Amit CE 9.2
104 Neha EC 8.2
105 Meera EE 7

LEFT OUTER JOIN

 The LEFT JOIN keyword returns all records from the left table (table1), and the
matchedrecords from the right table (table2).
 The result is NULL from the right side, if there is no match.
Syntax:
SELECT columns
FROM table1 LEFT OUTER
JOIN table2ON table1.column
= table2.column;

pg. 19
Example:
Consider the Student and result tables:
SELECT Student.RNO, Student.Name, Student.Branch,
Result.SPIFROM Student LEFT OUTER JOIN Result
ON Student.RNO = Result.RNO;
Output:
Left Join
RNO Name Branch SPI
101 Raju CE 8.8
102 Amit CE 9.2
103 Sanjay ME NULL
104 Neha EC 8.2
105 Meera EE 7
106 Mahesh ME NULL

RIGHT OUTER JOIN

 The RIGHT JOIN keyword returns all records from the right table (table2), and
thematched records from the left table (table1).
 The result is NULL from the left side, if there is no match.
Syntax:
SELECT columns
FROM table1 RIGHT OUTER JOIN
table2ON table1.column =
table2.column;
Example:
Consider the Student and result tables:
SELECT Student.RNO, Student.Name, Student.Branch,
Result.SPIFROM Student RIGHT OUTER JOIN Result
ON Student.RNO = Result.RNO;
Output:
Right Join
RNO Name Branch SPI
101 Raju CE 8.8
102 Amit CE 9.2
104 Neha EC 8.2
105 Meera EE 7
NULL NULL NULL 8.9

FULL OUTER JOIN

 The FULL OUTER JOIN keyword return all records when there is a match in
either left(table1) or right (table2) table records.
Syntax:
SELECT columns
FROM table1 FULL OUTER

pg. 20
 JOIN table2ON table1.column
= table2.column;
Example:
Consider the Student and result tables:
SELECT Student.RNO, Student.Name, Student.Branch,
Result.SPIFROM Student FULL OUTER JOIN Result
ON Student.RNO = Result.RNO;
Output:
Full Join
RNO Name Branch SPI
101 Raju CE 8.8
102 Amit CE 9.2
103 Sanjay ME NULL
104 Neha EC 8.2
105 Meera EE 7
106 Mahesh ME NULL
NULL NULL NULL 8.9

CROSS JOIN

 When each row of first table is combined with each row from the second table,
knownas Cartesian join or cross join.
 SQL CROSS JOIN returns the number of rows in first table multiplied by the
number ofrows in second table.
Syntax:
SELECT columns
FROM table1 CROSS JOIN table2;
Example:
Consider the following tables:
Color Size
Code Name Amount
1 Red Small
2 Blue Large

SELECT Color.Code, Color.Name, Size.AmountFROM Color CROSS JOIN Size;

Output:
Cross Join
Code Name Amount
1 Red Small
2 Blue Small
1 Red Large
2 Blue Large

SELF JOIN

pg. 21
  A self join is a regular join, but the table is joined with itself.
 Here, we need to use aliases for the same table to set a self join between single
table.
Syntax:
SELECT a.column, b.column
FROM tablename a CROSS JOIN
tablename bWHERE
a.column=b.column;
Example:
Consider the following table:
Employee
EmpNo Name MngrNo
E01 Tarun E02
E02 Rohan E05
E03 Priya E04
E04 Milan NULL
E05 Jay NULL
E06 Anjana E03

SELECT e.Name as Employee, m.Name as

EmployeeFROM Employee e INNER JOIN
Employee m
ON e.MngrNo=m.EmpNo;
Output:
Employee
Employee Manager
Tarun Rohan
Rohan Jay
Priya Milan
Anjana Priya

Practice

SQL> create table student(rno number(5),name varchar(10),branch varchar(10));

Table created.

SQL> create table result(rno number(5),spi number(10));

Table created.

SQL> insert into student values('&rno','&name','&branch');

SQL> select * from student;
RNO NAME BRANCH
---------- ---------- ----------
101 Raju CE

pg. 22
 102 Amit CE
103 Sanjay ME
104 Neha EC
105 Meera EE
106 Mahesh ME
SQL> insert into result values('&rno','&spi');
SQL> select * from result;
RNO SPI
---------- ----------
101 9
102 9
104 8
105 7
107 9
SQL> SELECT Student.RNO, Student.Name, Student.Branch, Result.SPI FROM Student
INNER JOIN Result ON Student.RNO = Result.RNO;
SQL> SELECT Student.RNO, Student.Name, Student.Branch, Result.SPI FROM Student LEFT
OUTER JOIN Result ON Student.RNO = Result.RNO;
SQL> SELECT Student.RNO, Student.Name, Student.Branch, Result.SPI FROM Student RIGHT
OUTER JOIN Result ON Student.RNO = Result.RNO;
SQL> SELECT Student.RNO, Student.Name, Student.Branch, Result.SPI FROM Student FULL
OUTER JOIN Result ON Student.RNO = Result.RNO;

SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Practice
SQL> select * from result where
exists(select name from student where
student.rno=result.rno);
SQL> select * from student;
SQL> select * from result where
not exists(select name from student where
student.rno=result.rno);
SQL> select * from student;

pg. 23
Introduction to SQL Set Operators

SQL set operators are used to combine the results obtained from two or more queries into a

single result.

The queries which contain two or more subqueries are known as compounded queries.

SQL Set Function

Operator

Union Combines distinct results of two or more SELECT statements.

Intersect Returns only the common records obtained from two or more SELECT

statements.

Minus Returns only those records which are exclusive to the first table.

 Condition to perform set operation:

 Both relations (queries) must be union compatible :
 Relations R and S are union compatible, if
 Both queries should have same (equal) number of columns,
and
 Corresponding attributes should have the same data type.
 Types of set operators:
1. Union
2. Intersect (Intersection)
3. Minus (Set Difference)
Union
 Operation: Selects tuples those are in either or both of the relations.
 Symbol : U (Union)
 Notation : Relation1 U Relation2
 Example :

pg. 24
 R S RUS
A 1 A 1 A 1
B 2 C 2 B 2
D 3 D 3 C 2
F 4 E 4 D 3
E 5 F 4
E 5
E 4

 Consider following tables

Emp Cst
Id Name Id Name
1 Manisha 1 Manisha
2 Anisha 2 Anisha
3 Nisha 4 Isha
 Example:
∏Name (Emp) U ∏Name (Cst)
Name
Manisha
Anisha
Nisha
Isha

Intersection
 Operation: Selects tuples those are common in both relations.
 Symbol : ∩ (Intersection)
 Notation : Relation1 ∩ Relation2
 Example
R S R∩S
A 1 A 1 A 1
B 2 C 2 D 3
D 3 D 3
F 4 E 4
E 5

 Consider following tables

Emp Cst
Id Name Id Name
1 Manisha 1 Manisha
2 Anisha 2 Anisha
3 Nisha 4 Isha


pg. 25
 
 Example:
∏Name (Emp) ∩ ∏Name (Cst)
Name
Manisha
Anisha
Difference:-
 Operation: Selects tuples those are in first (left) relation but not in second
(right)relation.
 Symbol : — (Minus)
 Notation : Relation1 — Relation2
 Example :
R S R—S
A 1 A 1 B 2
B 2 C 2 F 4
D 3 D 3 E 5
F 4 E 4
E 5

 Consider following tables

Emp Cst
Id Name Id Name
1 Manisha 1 Manisha
2 Anisha 2 Anisha
3 Nisha 4 Isha
 Example:
∏Name (Emp) - ∏Name (Cst)
Name
Nisha

∏Name (Cst) - ∏Name (Emp)

Name
Isha

pg. 26