Database Management System B.

Tech(CSE) II Year II Sem

UNIT III
SQL Functions: Arithmetic & logical operations, SQL functions (Numeric, String,
Date and Time, and String conversion).
Advanced SQL concepts: Creating tables with relationship, implementation of key
and integrity constraints, nested queries, sub queries, grouping, aggregation,
ordering, implementation of different types of joins, view(updatable and non-
updatable), relational set operations, Triggers, Indexes.

Arithmetic & logical operations

Operators are useful to perform the operation. These are unary or Binary operator.
Types of Operator
SQL operators are categorized in the following categories:

• SQL Arithmetic Operators(+, - ,* ,/ )

• SQL Comparison Operators(< , > , !=, = , >=, <=)
• SQL Logical Operators(AND, OR, NOT)
• SQL Set Operators(Union, Union all, Intersect, Minus)

Examples:
Select sal+1000 from emp;
Ans: all salaries with addition of 1000
Select * from EMP where sal>=5000;

SQL Functions
Functions are methods used to perform data operations. SQL has many in-built functions used to
perform string concatenations, mathematical calculations etc.

SQL functions are categorized into the following two categories:

• Scalar Functions
• Aggregate Functions

SCALAR FUNCTIONS:
The Scalar Functions in SQL are used to return a single value from the given input value. Each
record is operated independently by the function.
Types of Functions:
1)Numeric Functions
2)String Functions
3)Date & Time Functions
4)Conversion Functions

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

Numberic Functions

Function Value Returned

ABS ( m ) Absolute value of m
MOD ( m, n ) Remainder of m divided by n
POWER ( m, n ) m raised to the nth power
ROUND ( m , n ) m rounded to the nth decimal place
TRUNC ( m, n ) m truncated to the nth decimal place
SQRT ( n ) positive square root of n
EXP ( n ) e raised to the power n
LN ( n ) natural logarithm of n
LOG ( n2, n1 ) logarithm of n1, base n2
CEIL ( n ) smallest integer greater than or equal to n
FLOOR ( n ) greatest integer smaller than or equal to n

Examples:
select ABS(-65) from dual;
Ans:65
select CEIL(18.2) from dual;
Ans: 19
select FLOOR(18.2) from dual;
Ans:18
select POWER(10,2) from dual;
Ans:100
select SQRT(16) from dual;
Ans:4

STRING Functions

Function Value Returned

INITCAP ( s ) First letter of each word is changed to uppercase and all other
letters are in lower case.
LOWER ( s ) All letters are changed to lowercase.
UPPER ( s ) All letters are changed to uppercase.
CONCAT ( s1, s2 ) Concatenation of s1 and s2.
LTRIM ( s , set] ) Returns s with characters removed up to the first character not in
set; defaults to space
RTRIM ( s , set ) Returns s with final characters removed after the last character
not in set; defaults to space
REPLACE ( s, s1,s2 ) Returns s with every occurrence of s1 in s replaced by s2 ;
default removes s1
SUBSTR ( s, m , n ) Returns a substring from s, beginning in position m and n
characters long; default returns to end of s.
LENGTH ( s ) Returns the number of characters in s.
CHR(n) Returns the character which is equal to ASCII n value

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

Examples:
select CHR(37) , CHR(100), CHR(101) from dual;
select CONCAT('Tamota','soup') "Dinner" from dual;
Ans: Tamotasoup
select RPAD(name,5,'$') from student;
select RTRIM(‘JNTUK ’), LTRIM(‘ UNIVERSITY’) FROM DUAL;
Ans: JNTUKUNIVERSITY
select REPLACE('This and That','Th','B')"First" from dual;
Ans: Bis and Bat
select TRANSLATE('abcdefghij','abcdef','123456') from dual;
Ans:123456ghij

Date and Time Functions

Function Value Returned

ADD_MONTHS ( d, n ) Date d plus n months
LAST_DAY ( d ) Date of the last day of the month containing date d.
MONTHS_BETWEEN ( d, e ) Number of months by which e precedes date d
NEXT_DAY ( d, day ) Date of the first day of the week after date d
SYSDATE Current date and time
GREATEST ( d1, d2, ..., dn ) Latest of the given dates
LEAST ( d1, d2, ..., dn ) Earliest of the given dates
Examples:
select SYSDATE from dual;
select ADD_MONTHS(sysdate, 4) from dual ;
select MONTHS_BETWEEN('05-jan-99','05-jan-98') from dual;

Date Conversion Functions

Function Input Argument Value Returned
d = date value, fmt = The date d converted to a string in the
TO_CHAR ( d [, fmt ] )
format for string given format. It returns finally a string.
s = character string, fmt
TO_DATE ( s [, fmt ] ) String s converted to a date value
= format for date
d = date value, fmt = Date d rounded as specified by the
ROUND ( d [, fmt ] )
format for string format
d = date value, fmt = Date d truncated as specified by the
TRUNC ( d [, fmt ] )
format for string format

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

Examples:
select SYSDATE, TO_CHAR(SYSDATE,'DAY') from dual;
Ans: Monday (if the sysdate is 09-05-2022)
Select TO_CHAR(sysdate, 'yyyy/mm/dd') from dual;
Ans: ‘2022/05/09’ if the sysdate is 09/05/2022
Select TO_CHAR(sysdate, 'Month DD, YYYY') from dual;
Ans: ‘May 05,2022 if the sysdate is 09/05/2022
SELECT TO_DATE('09-05-2022', 'DD-MM-YYYY') FROM DUAL;
Converts ‘09-05-2022’ i.e string into Date type
SELECT TO_DATE('1999-JAN-05', 'YYYY-MON-DD') FROM DUAL;

AGGREGATE FUNCTIONS

In database management an aggregate function is a function where the values of multiple rows
are grouped together as input on certain criteria to form a single value of more significant
meaning.

The following are the most commonly used SQL aggregate functions:
• AVG – calculates the average of a set of values.
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.

Exampes:
select sum(sal) from emp;
select max(sal) from emp where job='salesman';
select min(sal) from emp;
select avg(sal),count(*) from emp where deptno=20;

CREATING TABLES WITH RELATIONS:

The user has to do the following to set relationship between two tables(one is Parent table and
second is Child table)
Parent table: Parent table is the table on which the child data is dependant.
It should have a Primary key column.
The child table maintains a foreign key with this column

Example: Create dept table as parent table, in this deptno is the primary key
attribute.
create table dept
( deptno number(2) primary key,
dname varchar2(10) not null,
loc varchar2(8));

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

Child Table: Child table is the table, the data of this table should references to the column in
parent table..
It should have same column name of the parent table.
The child table maintains a foreign key on the same column of the parent table.

Example: Create emp table as child table, in this deptno is the foreign key attribute.
create table emp
( empno number(5) primary key,
ename varchar2(10) not null,
sal number(7,2),
deptno number(2),
foreign key(deptno) references dept);

Implementation of Key and Integrity Constraints:

Constraints: You can place constraints to limit the type of data that can go into a table.
Common types of constraints include the following:
• UNIQUE Constraint : Ensures that all values in a column are distinct.
• NOT NULL Constraint : Ensures that a column cannot have NULL value.
• CHECK Constraint : Makes sure that all values in a column satisfy certain criteria.
• PRIMARY KEY Constraint : Ensures that all values in a column are distinct and a
Column can’t have NULL value.

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

• FOREIGN KEY Constraint : Used to ensure referential integrity of the data.

UNIQUE Constraint:-
SQL> CREATE TABLE Customer
(SID integer Unique,
First_Name varchar2(30),
Last_Name varchar2(30));

SID FIRST_NAME LAST_NAME

1212 RAJENDRA PRASAD
1201 PURNA CHANDRARAO
1215 LITHEN KUMAR
1242 DIVYA SREE
1243 BHAVYA SREE
1238 SUDHEER BABU
Executing the following SQL statement,
SQL> INSERT INTO Customer values ('1242','Reshma','Lee');

It will result in an error because '1242' already exists in the SID column, thus trying to insert
another row with that value violates the UNIQUE constraint.

NOT NULL Constraint:-

SQL>CREATE TABLE Customer2
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar (30));

Executing the following SQL statement,

SQL> INSERT INTO Customer2(Last_name,First_name) values (‘Rama’ , ‘Rao’);
It will result in an error

Check Constraint:-

SQL> create table emp (empno number(5) ,ename varchar2(10) sal number(7,2) check(sal>=500
and sal<=10000));
Executing the following SQL statement,
SQL> INSERT INTO emp values (100, ‘Rama’ , 20000);
It will result in an error

Primary key and Foreign key Constraint:-

SQL> create table dept (deptno number(4) primary key,
dname varchar2(10) );
SQL> insert into dept values(10,'accounting’);
1 row created.
SQL> insert into dept values(20,'research’);

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

1 row created.
SQL> select * from dept;
Deptno dname
10 Accounting
20 Research

SQL> create table emp (empno number(5) primary key,

ename varchar2(10),sal number(7,2) ,
deptno number(2),
foreign key(deptno) references dept);
SQL> insert into emp values(7369,'smith‘,800,20);
1 row created.
SQL> insert into emp values (7499,'allen‘,300,30);
Deptno 30 violates the constraint and give an error.

NESTED QUERY(SUB QUERY):

A query in other query is called as Nested query. In other words we can say that a Sub query is a
query that is embedded in WHERE clause of another SQL query.

• A subquery is a query within another query. The outer query is called as main query and
inner query is called as subquery.
• The subquery generally executes first, and its output is used to complete the query
condition for the main or outer query.
• Subquery must be enclosed in parentheses.
• A sub query is typically appears within the where clause of a query.

Syntax:
SELECT column_name FROM table_name WHERE column_name
expression_operator ( SELECT COLUMN_NAME from TABLE_NAME
WHERE ……... );

Example: Find the names of sailors who have reserved at least one boat.

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

NESTED SUB QUERY:

A subquery can be nested inside other subqueries. SQL has an ability to nest queries within one
another. A subquery is a SELECT statement that is nested within another SELECT statement and
which return intermediate results. SQL executes innermost subquery first, then next level.

Example: Find the names of sailors who have reserved a red or green boat.

Select [Link], [Link] from sailors s where [Link] in

(select [Link] from reserves r where [Link] in
(select [Link] from Boats b where [Link]=’green’ or [Link]=’red’));

CORRELATED QUERY –
In Correlated Query, Outer query executes first and for every Outer query row Inner query is
executed. Hence, Inner query uses values from Outer query.
Example –
Orders (OrderID, CustomerID, OrderDate);
Customers (CustomerID, CustomerName, ContactName, Country);
Find details of customers who have ordered.
SELECT CustomerName
FROM Customers
WHERE EXISTS (SELECT CustomerID
FROM Orders
WHERE [Link]= [Link]);

SQL | ORDER BY, GROUP BY , HAVING CLAUSES

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.

ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Example:
1. The Following shows the employee table in ascending order on Ename column
Select * from Emp order by Ename;
2. The Following shows the employee table in descending order on Ename column
Select * from Emp order by Ename DESC;

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of
some functions. i.e if a particular column has same values in different rows then it will arrange
these rows in a group.
Important Points:
• GROUP BY clause is used with the SELECT statement.
• In the query, GROUP BY clause is placed after the WHERE clause.
• In the query, GROUP BY clause is placed before ORDER BY clause if used any.
Syntax:
SELECT column1, function_name(column2)
FROM table_name WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;
function_name: Name of the function used for example, SUM() , AVG().
table_name: Name of the table.
condition: Condition used.

HAVING Clause
We can use HAVING clause to place conditions to decide which group will be the part of final
result-set. Also we can not use the aggregate functions like SUM(), COUNT() etc. with WHERE
clause. So we have to use HAVING clause if we want to use any of these functions in the
conditions
Syntax:
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;

Examples:

[Link] following query counts deptno wise count of employees:

select deptno,count(*) from emp group by deptno;
[Link] following query sums the salary on deptno wise of employees:
select deptno,sum(sal) from emp emp group by deptno;
[Link] following query gives the jobwise maximum salary having maximum salary >= 500
select job,max(sal) from emp group by job having max(sal)>=500;

SET OPERATIONS:
Union, Intersect, and Except (SET operators)
The SQL Set operation is used to combine the two or more SQL SELECT statements.

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

They are useful when you need to combine the results from separate queries into one single
result.
They differ from a join in that entire rows are matched and, as a result, included or excluded
from the combined result.
The UNION, INTERSECT, and EXCEPT are the set operations.
The other set operations are ANY, ALL, IN, NOT IN, EXISTS, NOT EXISTS.

UNION Operator
The Union operator returns rows from both tables. If used by itself, UNION returns a distinct
list of rows.
Using UNION ALL, returns all rows from both tables.
A UNION is useful when you want to sort results from two separate queries as one combined
result.
For instance if you have two tables, Vendor, and Customer, and you want a combined list of
names, you can easily do so using:
SELECT ‘Vendor’, [Link]
FROM Vendor V
UNION
SELECT ‘Customer’, [Link]
FROM Customer C
ORDER BY Name;

Note the ORDER BY clause applies to the combined result.

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

INTERSECT Operator
Use an intersect operator to returns rows that are in common between two tables; it returns
unique rows from both the left and right query.
This query is useful when you want to find results that are in common between two queries.
Continuing with Vendors, and Customers, suppose you want to find vendors that are also
customers. You can do so easily using:

SELECT [Link]
FROM Vendor V
INTERSECT
SELECT [Link]
FROM Customer C
ORDER BY Name

EXCEPT Operator
Use the EXCEPT Operator to return only rows found in the left query.
It returns unique rows from the left query that aren’t in the right query’s results.
This is similar to MINUS command in other sql softwares. This query is useful when you’re
looking to find rows that are in one set but not another.
For example, to create a list of all vendors that are not customers you could write:

SELECT [Link]
FROM Vendor V
EXCEPT
SELECT [Link]
FROM Customer C
ORDER BY Name

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);

Example: The following query display the Sailors who reserves at least one boat.

SELECT * FROM SAILOR S

WHERE EXISTS ( SELECT SID FROM RESERVES);

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

ANY Operator
The ANY operator returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of the values in the
range.

ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Example: The following query display the employees whose salary is lessthan ‘SALESMAN’
SELECT * FROM EMP E
WHERE [Link] > ANY ( SELECT [Link] FROM EMP E2
WHERE [Link]>[Link] AND [Link]=’SALESMAN’);

ALL Operator
The ALL operator
• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition is used with SELECT,
WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.

ALL Syntax With SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);

Example: The following query display the employees whose salary is lessthan ‘SALESMAN’
SELECT * FROM EMP E
WHERE [Link] > ALL ( SELECT [Link] FROM EMP E2
WHERE [Link]>[Link] AND [Link]=’SALESMAN’);

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Example: The following query display the employees whose job is clerk or analyst
SELECT * FROM EMP WHERE JOB IN (‘CLERK’, ‘ANALYST’);

JOINS
A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.
Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records
from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right
table

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

Examples:
Inner join
Example: The query displays all employees with their working department name:
SELECT [Link],[Link],[Link] FROM EMP E
INNER JOIN DEPT D ON [Link]=[Link];

ENAME SAL DNAME

SMITH 800 RESEARCH
ALLEN 1600 SALES
WARD 1250 SALES
JONES 2975 RESEARCH
MARTIN 1250 SALES
BLAKE 2850 SALES
CLARK 2450 ACCOUNTING
SCOTT 3000 RESEARCH
KING 5000 ACCOUNTING
TURNER 1000 SALES
ADEMS 1100 RESEARCH
JAMES 950 SALES
FORD 3000 RESEARCH
MILLER 1300 ACCOUNTING

Left Outer joins

Example: The query displays all employees with their working department name:

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

SELECT [Link],[Link],[Link] FROM EMP E

LEFT OUTER JOIN DEPT D ON [Link] [Link];

ENAME SAL DNAME

SMITH 800 RESEARCH
ALLEN 1600 SALES
WARD 1250 SALES
JONES 2975 RESEARCH
MARTIN 1250 SALES
BLAKE 2850 SALES
CLARK 2450 ACCOUNTING
SCOTT 3000 RESEARCH
KING 5000 ACCOUNTING
TURNER 1000 SALES
ADEMS 1100 RESEARCH
JAMES 950 SALES
FORD 3000 RESEARCH
MILLER 1300 ACCOUNTING

Displays all employees because every employ has deptno.

Right Outer joins

Example: The query displays all employees with their working department name and
additionally Department nameOPERATIONS without any employee:

SELECT [Link],[Link],[Link] FROM EMP E

RIGHT OUTER JOIN DEPT D ON [Link] [Link];

ENAME SAL DNAME

SMITH 800 RESEARCH
ALLEN 1600 SALES
WARD 1250 SALES
JONES 2975 RESEARCH
MARTIN 1250 SALES
BLAKE 2850 SALES
CLARK 2450 ACCOUNTING
SCOTT 3000 RESEARCH
KING 5000 ACCOUNTING
TURNER 1000 SALES

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur

Database Management System [Link](CSE) II Year II Sem

ADEMS 1100 RESEARCH

JAMES 950 SALES
FORD 3000 RESEARCH
MILLER 1300 ACCOUNTING
OPERATIONS

Equi join
select [Link],[Link],[Link],[Link] from emp e,dept d where [Link]=[Link];

ENAME SAL DNAME

SMITH 800 RESEARCH
ALLEN 1600 SALES
WARD 1250 SALES
JONES 2975 RESEARCH
MARTIN 1250 SALES
BLAKE 2850 SALES
CLARK 2450 ACCOUNTING
SCOTT 3000 RESEARCH
KING 5000 ACCOUNTING
TURNER 1000 SALES
ADEMS 1100 RESEARCH
JAMES 950 SALES
FORD 3000 RESEARCH
MILLER 1300 ACCOUNTING

Self join
A table can join with the same table. We can join using the alias names
Example: The following query displays the department details whose deptNo is lessthan
other deptNos.
SELECT [Link],[Link],[Link] FROM DEPT D1, DEPT D2
WHERE [Link]< [Link];

DNAME LOC DEPTNO

ACCOUNTING NEWYORK 20
ACCOUNTING NEWYORK 30
RESEARCH DALLAS 30
ACCOUNTING NEWYORK 40
RESEARCH DALLAS 40
SALES CHICAGO 40

Prepared by Ch Samsonu, [Link], CSED,KHIT, Guntur