UNIT 3

PROCEDURAL LANGUAGE SQL AND

• Syntax:
• Select * from table_name where Column_name(s) → outer query
• comparison operator
• (Select Column_Name(s)from table_name); → inner query

• Example:
• Select Name, Address, Phone, Salary
• from Employee Where Emp_id IN
• (Select Emp_id from Department where Dept_id=’1');
SUB QUERIES AND CORRELATED
SUB QUERIES
• Correlated Subquery is a type of subquery.
• Correlated Subquery is different from the normal subquery in terms of
execution.
• In this query, the correlated subquery is evaluated once for each row of
the outer query.
• Unlike the normal subquery,the inner query is dependent on the outer
query for values
• Each time the inner query gets executed it goes to the outer query for
values
• The Correlated subquery is slower compared to a normal subquery.
• It depends on the outer query.
SUB QUERIES AND CORRELATED
SUB QUERIES
• Syntax:
• SELECT column_name(s)
• FROM table1_name mainq
• WHERE column_name(s) operator
• (SELECT column_name(s) FROM table2_name
• WHERE expr1 =mainq.expr2);

• Example:
• Select Name, Address, Phone, Salary
• from Employee mainq Where Salary >
• (Select AVG(Salary) from Employee where Dept_id=mainq.Dept_id);
SQL FUNCTIONS
• SQL Functions are built-in programs that are used to perform
different operations on the database.
• There are two types of functions in SQL:
• Aggregate Functions
• Scalar Functions
SQL Aggregate Functions operate on a data group and return a singular
output. They are mostly used with the Group By clause to summarize
data.
SQL FUNCTIONS
Aggregate Function Description Syntax

AVG() Calculates the average value SELECT AVG(column_name) FROM table_name;

COUNT() Counts the number of rows SELECT COUNT(column_name) FROM table_name

FIRST() Returns the first value in an ordered set of values SELECT FIRST(column_name) FROM table_name;

LAST() Returns the last value in an ordered set of values SELECT LAST(column_name) FROM table_name;

MAX() Retrieves the maximum value from a column SELECT MAX(column_name) FROM table_name;

MIN() Retrieves the minimum value from a column SELECT MIN(column_name) FROM table_name;

SUM() Calculates the total sum of values in a numeric column SELECT SUM(column_name) FROM table_name;
SQL FUNCTIONS
• SQL Scalar functions
• SQL Scalar Functions are built-in functions that operate on a single
value and return a single value.
• Scalar functions in SQL helps in efficient data manipulation and
simplification of complex calculations in SQL queries.
SQL FUNCTIONS
Scalar function Description Syntax

UCASE() Converts a string to uppercase SELECT UCASE(column_name) FROM table_name;

LCASE() Converts a string to lowercase SELECT LCASE(column_name) FROM table_name;

MID() Extracts a substring from a string SELECT MID(column_name, start, length) FROM table_name;

LEN() Returns the length of a string SELECT LEN(column_name) FROM table_name;

ROUND() Rounds a number to a specified number of decimals SELECT ROUND(column_name, decimals) FROM table_name;

NOW() Returns the current date and time SELECT NOW();

FORMAT() Formats a value with the specified format SELECT FORMAT(column_name, format) FROM table_name;
VIRTUAL TABLES
• In a Database Management System (DBMS), a virtual table is a logical representation
of data that does not physically store the data itself. Instead, it is defined by a query
or a set of rules that retrieve and present data from one or more underlying base
tables or other virtual tables. The most common type of virtual table in DBMS is a
View.
• Here's a breakdown of virtual tables (Views) in DBMS:
• Key Characteristics:
• No Physical Storage:
• Views do not store data directly. They are essentially stored SQL queries that are
executed whenever the view is accessed.
• Derived from Base Tables:
• Views are created from existing tables (base tables) or other views.
VIRTUAL TABLES `
• Dynamic Data:
• The data presented by a view is always up-to-date, as it reflects the current
state of the underlying base tables.
• Simplified Data Access:
• Views can simplify complex queries by pre-defining joins, filters, and
aggregations, allowing users to interact with a more straightforward data
structure.
• Security and Data Abstraction:
• Views can restrict access to sensitive data by only exposing specific columns
or rows, and they can abstract the complexity of the underlying database
schema from users.
PROCEDURAL SQL
• PL/SQL (Procedural Language/SQL) is Oracle’s extension of SQL that adds
procedural features like loops, conditions, and error handling. It allows
developers to write powerful programs that combine SQL queries with logic to
control how data is processed. With PL/SQL, complex operations, calculations,
and error handling can be performed directly within the Oracle database, making
data manipulation more efficient and flexible.
• PL/SQL allows developers to:
• Execute SQL queries and DML commands inside procedural blocks.
• Define variables and perform complex calculations.
• Create reusable program units, such as procedures, functions, and triggers.
• Handle exceptions, ensuring the program runs smoothly even when errors occur.
PROCEDURAL SQL
• Key Features of PL/SQL
• PL/SQL brings the benefits of procedural programming to the relational database world.
Some of the most important features of PL/SQL include:
• Block Structure: PL/SQL can execute a number of queries in one block using single
command.
• Procedural Constructs: One can create a PL/SQL unit such as procedures, functions,
packages, triggers, and types, which are stored in the database for reuse by applications.
• Error Handling: PL/SQL provides a feature to handle the exception which occurs in PL/SQL
block known as exception handling block.
• Reusable Code: Create stored procedures, functions, triggers, and packages, which can be
executed repeatedly.
• Performance: Reduces network traffic by executing multiple SQL statements within a single
block
EMBEDDED SQL
• Embedded SQL is a powerful method that allows the integration of high?level programming
languages with (DBMS). It acts as a bridge between applications and databases which helps
in data manipulation and communication. Various database management systems offer
embedded SQL, giving developers the freedom to select the one that best serves their
requirements.
• Popular DBMS that support Embedded SQL are Altibase, IBM Db2, Microsoft Sql Server ,
Mimer SQL, Oracle Database, PostgreSQL, and SAP Sybase.
• Need of Embedded SQL
• The goal to make database interactions simpler for application developers and end users
determines the demand for embedded SQL. Users often enter values or submit requests
while interacting with an application, which requires accessing and changing data contained
in a database. Developers may conduct these database tasks without burdening the user
with SQL complexities by integrating SQL queries directly into the application code.