RDBMS

(3rd Semester-2021 Scheme)

22CSE153
[Link].(CSE)

SQL
Reference: Database management systems, , Ramakrishnan, and Gehrke, 3rd Edition, 2014,
McGraw Hill

1
OUTLINE
 Advances in SQL
 More complex SQL retrieval queries
 Specifying constraints as assertions
 Triggers, Views in SQL
 Schema change statements in SQL.

2
 MORE COMPLEX SQL RETRIEVAL
QUERIES
Comparisons Involving NULL and Three-Valued Logic
 Unknown value. A person’s date of birth is not known,
so it is represented by NULL in the database. An example
of the other case of unknown would be NULL for a person’s
home phone because it is not known whether or not the
person has a home phone.
 Unavailable or withheld value. A person has a home
phone but does not want it to be listed, so it is withheld
and represented as NULL in the database.
 Not applicable attribute. An attribute
LastCollegeDegree would be NULL for a person who has
no college degrees because it does not apply to that person.
3
 NESTED QUERIES
 A Subquery or Inner query or a Nested query is a query
within another SQL query and embedded within the
WHERE clause.
 A subquery is used to return data that will be used in the
main query as a condition to further restrict the data to be
retrieved.
 Subqueries can be used with the SELECT, INSERT,
UPDATE, and DELETE statements along with the
operators like =, <, >, >=, <=, IN, BETWEEN, etc.
 A subquery can return a single value, a single row, a single
column, or a table containing one or more rows of one or
more columns.
4
SUBQUERIES

5
NESTED QUERIES (SINGLE ROW SUBQUERY EXAMPLES)
 Retrieve the customers who earn salary more than the
salary of Customer with ID 6??

SELECT * FROM CUSTOMERS

WHERE salary > (SELECT salary FROM
CUSTOMERS WHERE id=6)
 Retrieve the customers who earn salary same as that of
Ramesh

SELECT * FROM CUSTOMERS

WHERE salary = (SELECT salary FROM
CUSTOMERS WHERE NAME=‘Ramesh’)
6
 NESTED QUERIES (MULTIPLE ROW SUBQUERY EXAMPLES)

 Write a query to find the employees whose salary is

greater than at least one employee in department of id
500?
SELECT EMPLOYEE_ID, SALARY FROM
EMPLOYEES WHERE SALARY > ANY ( SELECT
SALARY FROM EMPLOYEES WHERE
DEPARTMENT_ID = 500 )
 Write a query to find the employees whose salary is less
than the salary of all employees in department of id 100?
SELECT EMPLOYEE_ID, SALARY FROM
EMPLOYEES WHERE SALARY < ALL ( SELECT
SALARY FROM EMPLOYEES WHERE
DEPARTMENT_ID = 100 )
7
NESTED QUERIES
 Write a query to find the employees whose salary is
equal to the salary of at least one employee in
department of id 300?
SELECT EMPLOYEE_ID, SALARY FROM
EMPLOYEES WHERE SALARY IN ( SELECT
SALARY FROM EMPLOYEES WHERE
DEPARTMENT_ID = 300 )
 Update SALARY by 0.25 times in the CUSTOMERS
table for all the customers whose salary is same as that
of Ramesh.
UPDATE CUSTOMERS SET SALARY = SALARY *
0.25 WHERE salary IN (SELECT SALARY FROM
CUSTOMERS WHERE NAME=‘Ramesh’ );
8
9
10
11
12
13
14
15
The IN operator is equivalent to =ANY.

For example, to display the employees who have subordinates,

use the following SQL statement:
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);

Alternatively, a WHERE clause can be included in the subquery

to display all employees who do not have any subordinates:
SELECT last_name FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
16
FROM employees
where manager_id is not null);
NESTED QUERIES
 Delete the records from CUSTOMERS table for all the
customers whose salary is same as that of Ramesh.
DELETE FROM CUSTOMERS
WHERE SALARY IN (SELECT SALARY FROM
CUSTOMERS WHERE NAME=‘Ramesh’ );
 Select the Essns of all employees who work the same
(project, hours) combination on some project that
employee ‘John Smith’ (whose Ssn = ‘123456789’)
works on.
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN ( SELECT Pno, Hours FROM
WORKS_ON WHERE Essn = ‘123456789’ );
17
NESTED QUERY/SUB QUERY
 List products with order quantities greater than 100.

18
NESTED QUERY/SUB QUERY
 List all customers with their total number of orders

SELECT [Link], [Link], OrderCount

FROM Customer C JOIN (SELECT [Link] as custid,
count([Link]) as OrderCount FROM Order O GROUP BY 19
[Link]) ON [Link] = custid;
NESTED QUERY/SUB QUERY
 Return the names of employees whose
salary is greater than the salary of all the employees in
department 5:

SELECT Lname, Fname

FROM EMPLOYEE
WHERE Salary > ALL ( SELECT Salary
FROM EMPLOYEE
WHERE Dno = 5 );

20
GROUP BY CLAUSE
The GROUP BY Clause is utilized in SQL with the
SELECT statement to organize similar data into groups.
It combines the multiple records in single or more
columns using some functions.

 GROUP BY Clause is utilized with the SELECT

statement.
 GROUP BY aggregates the results on the basis of
selected column: COUNT, MAX, MIN, SUM, AVG, etc.
 GROUP BY returns only one result per group of data.

 GROUP BY Clause always follows the WHERE Clause.

 GROUP BY Clause always precedes the ORDER BY

21
GROUP BY CLAUSE

22
GROUP BY CLAUSE
 Return the AVERAGE salary of the respective department

SELECT Avg(Salary)
FROM EMPLOYEE
GROUP BY DEPT ID ;

 Return the names of employees whose salary is greater than

the AVERAGE salary of the all the departments

SELECT Lname, Fname

FROM EMPLOYEE
WHERE Salary > ALL( SELECT Avg(Salary)
FROM EMPLOYEE
GROUP BY DEPT ID);
23
SQL HAVING CLAUSE
 HAVING is like WHERE but operates on grouped
records returned by a GROUP BY.
 HAVING applies to summarized group records, whereas
WHERE applies to individual records.
 Only the groups that meet the HAVING criteria will be
returned.
 HAVING requires that a GROUP BY clause is present.

 WHERE and HAVING can be used in the same query.

24
 SQL HAVING CLAUSE
 List the number of customers in each country.
Only include countries with more than 10 customers.

SELECT COUNT(Id), Country

FROM Customer
GROUP BY Country
HAVING COUNT(Id) > 10
25
26
27
 NESTED QUERY
 Retrieve the name of each employee who has a
dependent with the same first name and is the same
gender as the employee

SELECT [Link], [Link]

FROM EMPLOYEE AS E
WHERE [Link] IN ( SELECT [Link]
FROM DEPENDENT AS D
WHERE [Link] = D.Dependent_name
AND [Link] = [Link] );

28
CORRELATED NESTED QUERIES
 Whenever a condition in the WHERE clause of a nested
query references some attribute of a relation declared in
the outer query, the two queries are said to be correlated

29
CORRELATED NESTED QUERIES
 Retrieve the names and Ids of employees whOse salary is
greater than the average salary of their department

SELECT E1.EMP_NAME, E1.EMP_ID

FROM employee E1
WHERE SALARY>(SELECT AVG(SALARY)
FROM employee E2
WHERE E2.DEP_ID=E1.DEP_ID
GROUP BY DEP_ID);

30
EXISTS / NOT EXISTS
 Retrieve the names of employees who have dependents.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn = Essn );
 Retrieve the names of employees who do not have dependents.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn = Essn );

31
EXISTS / NOT EXISTS
 List the names of managers who have at least one dependent.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn = Essn )
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE Ssn = Mgr_ssn );

32
RENAME
 Retrieve the last name of each employee and his or her
supervisor while renaming the resulting attribute names as
Employee_name and Supervisor_name

SELECT E.EMP_NAME AS Employee_name,

S.EMP_NAME AS Supervisor_name
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.MANAGER_ID = S.EMP_ID;

33
AGGREGATE FUNCTIONS
 Find the sum of the salaries of all employees, the maximum
salary, the minimum salary, and the average salary.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMP

 Retrieve the total number of employees in the company

SELECT COUNT (*)
FROM EMPLOYEE;

 Retrieve the total number of employees in the ‘Research’

department

SELECT COUNT (*)

FROM EMPLOYEE, DEPARTMENT
34
WHERE DNO = DNUMBER AND DNAME = ‘Research’;
JOIN CONDITIONS

 There may be at least one join condition either in the FROM

clause or in the WHERE clause for joining two tables. It
compares two columns from different tables and combines
pair of rows, each containing one row from each table, for
which join condition is true.
 Oracle INNER JOIN

 Inner Join is the simplest and most common type of join. It

is also known as simple join. It returns all rows from
multiple tables where the join condition is met.
SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];
35
JOIN CONDITIONS
 Oracle INNER JOIN

SELECT SUPPLIERS.SUPPLIER_ID, SUPPLIERS.SUPPLIER_NAME,

ORDER1.ORDER_NUMBER
FROM SUPPLIERS
INNER JOIN ORDER1
ON SUPPLIERS.SUPPLIER_ID = ORDER1.SUPPLIER_ID;
36
 JOIN CONDITIONS
 Oracle OUTER JOIN
Left Outer Join
 Left Outer Join returns all rows from the left (first) table
specified in the ON condition and only those rows from
the right (second) table where the join condition is met
SELECT SUPPLIERS.SUPPLIER_ID,
SUPPLIERS.SUPPLIER_NAME,
ORDER1.ORDER_NUMBER
FROM SUPPLIERS
LEFT OUTER JOIN ORDER1
ON SUPPLIERS.SUPPLIER_ID =
ORDER1.SUPPLIER_ID;
;

37
JOIN CONDITIONS
 Oracle OUTER JOIN
Right Outer Join
 The Right Outer Join returns all rows from the right-hand
table specified in the ON condition and only those rows
from the other table where the join condition is met.
SELECT SUPPLIERS.SUPPLIER_ID,
SUPPLIERS.SUPPLIER_NAME,
ORDER1.ORDER_NUMBER
FROM SUPPLIERS
RIGHT OUTER JOIN ORDER1
ON SUPPLIERS.SUPPLIER_ID =
ORDER1.SUPPLIER_ID;

38
 JOIN CONDITIONS
Full Outer Join
 The Full Outer Join returns all rows from the left hand
table and right hand table. It places NULL where the join
condition is not met.

SELECT SUPPLIERS.SUPPLIER_ID, SUPPLIERS.SUPPLIER_NAME,

ORDER1.ORDER_NUMBER
FROM SUPPLIERS
FULL OUTER JOIN ORDER1
ON SUPPLIERS.SUPPLIER_ID = ORDER1.SUPPLIER_ID;;
;

39
JOIN CONDITIONS
Oracle EQUI JOIN
Retrieve the names of the managers

SELECT ENAME
FROM EMPLOYEE, DEPARTMENT
WHERE MGR_NO=SSN;

OR

SELECT [Link]
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.MGR_NO=[Link];
40
JOIN CONDITIONS
SELFJOIN
A self join is a join in which a table is joined with itself (which is also called Unary relationships),
especially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

 List of employees and their supervisor

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "Employee Name", 41
b.emp_id AS "Supervisor ID",b.emp_name AS "Supervisor Name" FROM
employee a, employee b WHERE a.emp_supv = b.emp_id;
JOIN CONDITIONS
 Oracle Cross Join (Cartesian Products)
 The CROSS JOIN specifies that all rows from first table
join with all of the rows of second table. If there are "x"
rows in table1 and "y" rows in table2 then the cross join
result set have x*y rows.

SELECT * FROM customer,supplier

42
 SPECIFYING GENERAL CONSTRAINTS AS
ASSERTIONS IN SQL
 In SQL, users can specify general Constraints via declarative
assertions, using the CREATE ASSERTION statement of the
DDL. Each assertion is given a constraint name and is specified
via a condition similar to the WHERE clause of an SQL query.
 For example, to specify the constraint that the salary of an
employee must not be greater than the salary of the manager of
the department that the employee works for in SQL, we can write
the following assertion:

CREATE ASSERTION SALARY_CONSTRAINT

CHECK ( NOT EXISTS ( SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE [Link]>[Link] AND [Link]=[Link]
AND D.Mgr_ssn=[Link] ) ); 43
 TRIGGERS
 A database trigger is a stored PL/SQL program unit associated with a
specific database table. ORACLE executes (fires) a database trigger
automatically when a given SQL operation (like INSERT, UPDATE
or DELETE) affects the table. Unlike a procedure, or a function,
which must be invoked explicitly, database triggers are invoked
implicitly.

 Three parts:
 Event (activates the trigger)
 Condition (tests whether the triggers should run) [Optional]
 Action (what happens if the trigger runs)

 Events could be :

44
BEFORE|AFTER INSERT|UPDATE|DELETE ON <tableName>

e.g.: BEFORE INSERT ON Professor

 TRIGGERS
Assume our DB has a relation schema :
Professor (pNum, pName, salary)
We want to write a trigger that :
Ensures that any new professor inserted has salary >= 100000

Eg.,
CREATE TRIGGER minSalary BEFORE INSERT ON Professor

for what context ?

BEGIN

check for violation here ?

45

END;
TRIGGERS
CREATE TRIGGER min_Salary BEFORE INSERT ON
EMPLOYEE

FOR EACH ROW

BEGIN

IF (:[Link] < 10000)

THEN RAISE_APPLICATION_ERROR (-
20004,'Violation of Minimum Salary');
END IF;
46

END;
TRIGGERS
R1: CREATE TRIGGER Total_sal1
AFTER INSERT ON EMPLOYEE
FOR EACH ROW R3: CREATE TRIGGER Total_sal3
BEGIN AFTER UPDATE OF Dno ON
IF ( :[Link] IS NOT NULL ) EMPLOYEE
FOR EACH ROW
Then
BEGIN
UPDATE DEPARTMENT
UPDATE DEPARTMENT
SET Total_sal = Total_sal + :[Link] SET Total_sal = Total_sal + :[Link]
WHERE Dno = :[Link]; WHERE Dno = :[Link];
End if; end; UPDATE DEPARTMENT
SET Total_sal = Total_sal – :[Link]
R2: CREATE TRIGGER Total_sal2 WHERE Dno = :[Link];
AFTER UPDATE OF Salary ON EMPLOYEE
END;
FOR EACH ROW
R4: CREATE TRIGGER Total_sal4
Begin AFTER DELETE ON EMPLOYEE
if ( :[Link] IS NOT NULL ) FOR EACH ROW
UPDATE DEPARTMENT BEGIN
SET Total_sal = Total_sal + :[Link] IF ( :[Link] IS NOT NULL)
– :[Link] UPDATE DEPARTMENT 47
WHERE Dno = :[Link]; SET Total_sal = Total_sal – :[Link]
End if; end; WHERE Dno = :[Link]; end if; end;
VIEW
You can present logical subsets or combinations of data by creating views of
tables. A view is a logical table based on a table or another view. A view
contains no data of its own but is like a window through which data from
tables can be viewed or changed. The tables on which a view is based are
called base tables. The view is stored as a SELECT statement in the data
dictionary.

48
CREATING A VIEW
 Create a view, EMPVU80, that contains details of
employees in department 80.

CREATE OR REPLACE VIEW empvu80

AS SELECT employee_id, last_name,
salary
FROM employees
WHERE department_id = 80;

SELECT * FROM empvu50

DROP VIEW view_name; 49

CREATING A VIEW

50
 USES OF A VIEW :
A good database should contain views due to the given reasons:

 Restricting data access –

Views provide an additional level of table security by restricting access to a
predetermined set of rows and columns of a table.

 Hiding data complexity –

A view can hide the complexity that exists in a multiple table join.

 Simplify commands for the user –

Views allows the user to select information from multiple tables without requiring
the users to actually know how to perform a join.

 Rename Columns –
Views can also be used to rename the columns without affecting the base tables
provided the number of columns in view must match the number of columns
specified in select statement. Thus, renaming helps to hide the names of the
columns of the base tables. 51

 Multiple view facility –

CASE STATEMENTS
 Suppose we want to give employees different raise amounts depending on
which department they work for:
 for example, employees in department 5 get a $2,000 raise, those in
department 4 get $1,500 and those in department 1 get $3,000

UPDATE EMPLOYEE
SET Salary =
CASE WHEN Dno = 5 THEN Salary + 2000
WHEN Dno = 4 THEN Salary + 1500
WHEN Dno = 1 THEN Salary + 3000
ELSE Salary + 0 ;

52
SCHEMA CHANGE STATEMENTS IN
SQL

ALTER COMMAND
 DROP A CONSTRAINT

 ADD A CONSTRAINT

 ADD A COLUMN

 DROP A COLUMN

 MODIFY THE COLUMN

53
STORED PROCEDURE

 A stored procedure is a prepared SQL code that you can

save, so the code can be reused over and over again.
 So if you have an SQL query that you write over and
over again, save it as a stored procedure, and then just
call it to execute it.
 You can also pass parameters to a stored procedure, so
that the stored procedure can act based on the parameter
value(s) that is passed.
 Procedures promote reusability and maintainability.

54
STORED PROCEDURES AND
FUNCTIONS
 In our presentation of database programming techniques so far, there was an implicit
assumption that the database application program was running on a client
 it is sometimes useful to create database program modules—procedures or functions
—that are stored and executed by the DBMS at the database server

 The general form of declaring stored procedures is as follows:

CREATE PROCEDURE <procedure name> (<parameters>)

<local declarations>
<procedure body> ;

The parameters and local declarations are optional, and are specified only if needed.
55
STORED PROCEDURES AND
FUNCTIONS
 For declaring a function, a return type is necessary, so the declaration form is:

CREATE FUNCTION <function name> (<parameters>)

RETURNS <return type>
<local declarations>
<function body> ;

56
SIMPLE EXAMPLE :STORED PROCEDURE AND FUNCTION
CREATE OR REPLACE PROCEDURE PrintHello AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, Oracle SQL!');
END;
/
-- Execute the stored procedure
EXEC PrintHello;;

CREATE OR REPLACE FUNCTION AddNumbers (a IN NUMBER,b IN NUMBER)

RETURN NUMBER
AS
result NUMBER;
BEGIN
result := a + b;
RETURN result;
END;
/

57
-- Use the function in a SELECT statement
SELECT AddNumbers(5, 7) AS SumResult FROM DUAL;
;
GOOD EXAMPLE :STORED PROCEDURE
CREATE OR REPLACE PROCEDURE GetEmployeeInfo (p_employee_id IN NUMBER)
AS
v_employee_name employees.employee_name%TYPE;
v_job [Link]%TYPE;
v_salary [Link]%TYPE;
BEGIN
-- Retrieve employee information based on the provided ID
SELECT employee_name, job, salary
INTO v_employee_name, v_job, v_salary
FROM employees
WHERE employee_id = p_employee_id;

-- Print the employee information to the console

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
DBMS_OUTPUT.PUT_LINE('Job: ' || v_job);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

-- Execute the stored procedure with a specific employee ID 58

EXEC GetEmployeeInfo(101);
GOOD EXAMPLE : FUNCTIONS
CREATE OR REPLACE FUNCTION GetEmployeeGrade (
p_employee_id IN NUMBER
)
RETURN VARCHAR2
AS
v_salary NUMBER;
v_years_of_service NUMBER;
v_grade VARCHAR2(10);
BEGIN
-- Retrieve salary and years of service for the employee
SELECT Salary, YearsOfService
INTO v_salary, v_years_of_service
FROM Employees
WHERE EmployeeID = p_employee_id;

-- Determine the employee grade based on salary and years of service

IF v_salary >= 80000 AND v_years_of_service >= 5 THEN
v_grade := 'A+';
ELSIF v_salary >= 60000 AND v_years_of_service >= 3 THEN
v_grade := 'A';
ELSIF v_salary >= 40000 THEN
v_grade := 'B'; -- Example usage of the function
ELSE
SELECT EmployeeID, EmployeeName,
v_grade := 'C';
END IF; GetEmployeeGrade(EmployeeID) AS
59
EmployeeGrade
RETURN v_grade; FROM Employees;
END;
/
CURSORS
 In order to PL behave like SQL returning a set of rows from a relation, cursor is used
in the procedural language. We can declare a cursor on any relation or on any SQL
query (because every query returns a set of rows). Once a cursor is declared, we can
open it (which positions the cursor just before the first row); fetch the next row; move
the cursor (to the next row, to the row after the next n, to the first row, or to the
previous row, etc., by specifying additional parameters for the FETCH command); or
close the cursor.

 In Oracle PL/SQL, cursors are used to process the result set of a query. There are two
types of cursors: implicit cursors and explicit cursors.

 Implicit

 Explicit

60
IMPLICIT CURSORS

 Implicit cursors are managed by Oracle automatically.

 They are created and managed implicitly for each SQL query executed in PL/SQL.
 Implicit cursors are typically used in scenarios where you are dealing with single-
row queries or simple data manipulation.

 Example

DECLARE
v_employee_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE
employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/

61
EXPLICIT CURSORS
 Explicit cursors are declared explicitly by the programmer.

 You have to declare an explicit cursor before using it in your code.

 Explicit cursors provide more control and flexibility.

 You declare explicit cursors using the ‘CURSOR’ keyword

 You need to define the query associated with the cursor

 Explicit cursors are used in scenarios where you need more control over the cursor
operations, such as processing multiple rows, handling exceptions, or performing
complex data manipulations.

62
EXPLICIT CURSORS: EXAMPLE
DECLARE
E_ID EMPLOYEE.EMP_ID%type;
E_name EMPLOYEE.EMP_NAME%type;
E_SALARY [Link]%type;
CURSOR EMPLOYEE_DETAILS is
SELECT EMP_ID, EMP_NAME, SALARY FROM EMPLOYEE;
BEGIN
OPEN EMPLOYEE_DETAILS;
LOOP
FETCH EMPLOYEE_DETAILS into E_ID, E_NAME, E_SALARY;
EXIT WHEN EMPLOYEE_DETAILS%notfound;
dbms_output.put_line(E_ID || ' ' || E_NAME|| ' ' ||E_SALARY);
END LOOP;
CLOSE EMPLOYEE_DETAILS;
END; 63
SUMMARY
 Advances in SQL
 More complex SQL retrieval queries
 Specifying constraints as assertions
 Triggers, Views in SQL
 Stored Procedures, functions, and cursor in SQL
 Schema change statements in SQL.

64