1.

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

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.

Some common Aggregate functions with Syntax and description are shown in the table below.

Aggregate
Function Description Syntax

SELECT AVG(column_name) FROM

Calculates the average value
AVG() table_name;

SELECT COUNT(column_name) FROM

Counts the number of rows
COUNT() table_name

Returns the first value in an ordered set SELECT FIRST(column_name) FROM

FIRST() of values table_name;

Returns the last value in an ordered set SELECT LAST(column_name) FROM

LAST() of values table_name;

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

MAX() column table_name;

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

Aggregate
Function Description Syntax

column table_name;

Calculates the total sum of values in a SELECT SUM(column_name) FROM

SUM() numeric column table_name;

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.

Scalar
function Description Syntax

SELECT UCASE(column_name) FROM

Converts a string to uppercase
UCASE() table_name;

SELECT LCASE(column_name) FROM

Converts a string to lowercase
LCASE() table_name;

SELECT MID(column_name, start, length) FROM

Extracts a substring from a string
MID() table_name;

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

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

ROUND() number of decimals table_name;
Scalar
function Description Syntax

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

Formats a value with the specified SELECT FORMAT(column_name, format) FROM

FORMAT() format table_name;

SQL Functions Examples

Let's look at some examples of SQL Functions. We will cover examples of SQL aggregate functions and
scalar functions.

We will perform queries on the given SQL table:

Aggregate Functions Examples

Let's look at the examples of each aggregate function in SQL.

AVG() Function Example

Computing average marks of students.

Query:

SELECT AVG(MARKS) AS AvgMarks FROM Students;

Output:
AvgMarks

80

COUNT() Function Example

Computing total number of students.

Query:

SELECT COUNT(*) AS NumStudents FROM Students;

Output:

NumStudents

FIRST() Function Example

Fetching marks of first student from the Students table.

Query:

SELECT FIRST(MARKS) AS MarksFirst FROM Students;

Output:

MarksFirst

90

LAST() Function Example

Fetching marks of last student from the Students table.

Query:

SELECT LAST(MARKS) AS MarksLast FROM Students;

Output:

MarksLast

85

MAX() Function Example

Fetching maximum marks among students from the Students table.

Query:

SELECT MAX(MARKS) AS MaxMarks FROM Students;

Output:

MaxMarks

95

MIN() Function Example

Fetching minimum marks among students from the Students table.

Query:

SELECT MIN(MARKS) AS MinMarks FROM Students;

Output:

MinMarks

50

SUM() Function Example

Fetching summation of total marks among students from the Students table.

Query:
SELECT SUM(MARKS) AS TotalMarks FROM Students;

Output:

TotalMarks

400

Scalar Functions Examples

Let's look at some examples of each Scalar Function in SQL.

UCASE() Function Example

Converting names of students from the table Students to uppercase.

Query:

SELECT UCASE(NAME) FROM Students;

Output:

NAME

HARSH

SURESH

PRATIK

DHANRAJ

RAM

LCASE() Function Example

Converting names of students from the table Students to lowercase.

Query:

SELECT LCASE(NAME) FROM Students;

Output:

NAME

Harsh

Suresh

Pratik

dhanraj

Ram

MID() Function Example

Fetching first four characters of names of students from the Students table.

Query:

SELECT MID(NAME,1,4) FROM Students;

Output:

NAME

HARS

SURE
NAME

PRAT

DHAN

RAM

LEN() Function Example

Fetching length of names of students from Students table.

Query:

SELECT LENGTH(NAME) FROM Students;

Output:

NAME

ROUND() Function Example

Fetching maximum marks among students from the Students table.

Query:

SELECT ROUND(MARKS,0) FROM Students;

Output:

MARKS

90

50

80

95

85

NOW() Function Example

Fetching current system time.

Query:

SELECT NAME, NOW() AS DateTime FROM Students;

Output:

NAME DateTime

HARSH 1/13/2017 1:30:11 PM

SURESH 1/13/2017 1:30:11 PM

NAME DateTime

PRATIK 1/13/2017 1:30:11 PM

DHANRAJ 1/13/2017 1:30:11 PM

RAM 1/13/2017 1:30:11 PM

FORMAT() Function Example

Formatting current date as 'YYYY-MM-DD'.

Query:

SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Students;

Output:

NAME Date

HARSH 2017-01-13

SURESH 2017-01-13

PRATIK 2017-01-13

DHANRAJ 2017-01-13

RAM 2017-01-13
[Link] JOINS OPERATIONS

SQL joins are fundamental tools for combining data from multiple tables in relational databases.

• For example, consider two tables where one table (say Student) has student information with id
as a key and other table (say Marks) has information about marks of every student id. Now to
display the marks of every student with name, we need to join the two tables.

• Please remember, we store data into multiple tables as part of database normalization to avoid
anomalies and redundancies.

Types of SQL Joins

Let us visualize how each join type operates:

1. SQL INNER JOIN

The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This
keyword will create the result set by combining all rows from both the tables where the condition
satisfies i.e value of the common field will be the same.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN

table2 ON table1.matching_column = table2.matching_column;

Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Inner Join

Example of INNER JOIN

Consider the two tables, Student and StudentCourse, which share a common column ROLL_NO. Using
SQL JOINS, we can combine data from these tables based on their relationship, allowing us to retrieve
meaningful information like student details along with their enrolled courses.

1. Student Table:
ROLL_NO NAME ADDRESS PHONE AGE

1 HARSH DELHI XXXXXXXXXX 18

2 PRATIK BIHAR XXXXXXXXXX 19

3 RIYANKA SILIGURI XXXXXXXXXX 20

4 DEEP RAMNAGAR XXXXXXXXXX 18

5 ANITA MUMBAI XXXXXXXXXX 21

2. StudentCourse Table:

COURSE_ID ROLL_NO

1 1

2 2

3 3

6 7

Let's look at the example of INNER JOIN clause, and understand it's working. This query will show the
names and age of students enrolled in different courses.

Query:

SELECT StudentCourse.COURSE_ID, [Link], [Link] FROM Student

INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:

COURSE_ID NAME AGE

1 HARSH 18

2 PRATIK 19

3 RIYANKA 20

2. SQL LEFT JOIN

A LEFT JOIN returns all rows from the left table, along with matching rows from the right table. If there is
no match, NULL values are returned for columns from the right table. LEFT JOIN is also known as LEFT
OUTER JOIN.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.

Left JOIN

LEFT JOIN Example

In this example, the LEFT JOIN retrieves all rows from the Student table and the matching rows from the
StudentCourse table based on the ROLL_NO column.

Query:
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output:

NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 3

DEEP NULL

ANITA NULL

3. SQL RIGHT JOIN

RIGHT JOIN returns all the rows of the table on the right side of the join and matching rows for the table
on the left side of the join. It is very similar to LEFT JOIN for the rows for which there is no matching row
on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.

Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

Key Terms

• table1: First table.

• table2: Second table

• matching_column: Column common to both the tables.

Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the same

Right JOIN

RIGHT JOIN Example

In this example, the RIGHT JOIN retrieves all rows from the StudentCourse table and the matching rows
from the Student table based on the ROLL_NO column.

Query:

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output:

NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 3

NULL 6

4. SQL FULL JOIN

FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set
will contain all the rows from both tables. For the rows for which there is no matching, the result-set will
contain NULL values.
Syntax

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Key Terms

• table1: First table.

• table2: Second table

• matching_column: Column common to both the tables.

FULL JOIN Example

This example demonstrates the use of a FULL JOIN, which combines the results of both LEFT JOIN and
RIGHT JOIN. The query retrieves all rows from the Student and StudentCourse tables. If a record in one
table does not have a matching record in the other table, the result set will include that record
with NULL values for the missing fields

Query:

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output :

NAME COURSE_ID

HARSH 1
NAME COURSE_ID

PRATIK 2

RIYANKA 3

DEEP NULL

ANITA NULL

NULL 6

[Link] language in 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.

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

Structure of PL/SQL Block

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural
language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are
made up of blocks, which can be nested within each other.

Typically, each block performs a logical action in the program. A block has the following structure:

DECLARE
declaration statements;

BEGIN
executable statements

EXCEPTIONS
exception handling statements
END;

PL/SQL code is written in blocks, which consist of three main sections:

• Declare section starts with DECLARE keyword in which variables, constants, records as cursors
can be declared which stores data temporarily. It basically consists definition of PL/SQL
identifiers. This part of the code is optional.

• Execution section starts with BEGIN and ends with END keyword. This is a mandatory section
and here the program logic is written to perform any task like loops and conditional statements.
It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.

• Exception section starts with EXCEPTION keyword. This section is optional which contains
statements that are executed when a run-time error occurs.

• PL/SQL Identifiers

In PL/SQL, identifiers are names used to represent various program elements like variables, constants,
procedures, cursors, triggers etc. These identifiers allow you to store, manipulate, and access data
throughout your PL/SQL code.

1. Variables in PL/SQL

Like several other programming languages, variables in PL/SQL must be declared prior to its use. A
variable is like a container that holds data during program execution. Each variable must have a valid
name and a specific data type.

Syntax for declaration of variables:

variable_name datatype [NOT NULL := value ];

• variable_name: The name of the variable.

• datatype: The data type of the variable (e.g., INTEGER, VARCHAR2).

• NOT NULL: This optional constraint means the variable cannot be left empty.

• := value: This optional assignment assigns an initial value to the variable.

Example: Declaring Variables

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;

BEGIN
null;
END;
/

Output:

PL/SQL procedure successfully completed.

Explanation:

• SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output.

• var1 INTEGER : It is the declaration of variable, named var1 which is of integer type. There are
many other data types that can be used like float, int, real, smallint, long etc. It also supports
variables used in SQL as well like NUMBER(prec, scale), varchar, varchar2 etc.

• Slash (/) after END;: The slash (/) tells the SQL*Plus to execute the block.

• Assignment operator (:=) : It is used to assign a value to a variable.

2. Displaying Output in PL/SQL

The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user to
display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages,
and triggers. Let us see an example to see how to display a message using PL/SQL :

Example: Displaying Output

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var varchar2(40) := 'I love GeeksForGeeks' ;

BEGIN
dbms_output.put_line(var);

END;
/

Output:

I love GeeksForGeeks
PL/SQL procedure successfully completed.

Explanation: dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.
3. Comments in PL/SQL

Like in many other programming languages, in PL/SQL also, comments can be put within the code which
has no effect in the code. There are two syntaxes to create comments in PL/SQL :

• Single Line Comment: To create a single line comment , the symbol - - is used.

• Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is
used.

Example: Adding Comments

-- This is a single-line comment

/*
This is a multi-line comment
that spans over multiple lines.
*/

4. Taking input from users

In PL/SQL we can take input from the user and store it in a variable using substitution variables. These
variables are preceded by an & symbol. Let us see an example to show how to take input from users in
PL/SQL:

Example: Taking Input from Users

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a number := &a;

-- taking input for variable b

b varchar2(30) := &b;

BEGIN
null;

END;
/

Output:
Enter value for a: 24
old 2: a number := &a;
new 2: a number := 24;
Enter value for b: 'GeeksForGeeks'
old 3: b varchar2(30) := &b;
new 3: b varchar2(30) := 'GeeksForGeeks';

PL/SQL procedure successfully completed.

Explanation:

• &a and &b are substitution variables where the user will be prompted to provide values.

• The user is asked to enter values for a and b when the code runs.

PL/SQL Practical Example

Let’s combine all the above concepts into one practical example. We’ll create a PL/SQL block that takes
two numbers from the user, calculates their sum, and displays the result.

--PL/SQL code to print sum of two numbers taken from the user.
SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a integer := &a ;

-- taking input for variable b

b integer := &b ;
c integer ;

BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);

END;

Execution:

Enter value for a: 2

Enter value for b: 3

Sum of 2 and 3 is = 5
PL/SQL procedure successfully completed.

PL/SQL Execution Environment

The PL/SQL engine resides in the Oracle engine. When a PL/SQL block is executed, it sends a single
request to the Oracle engine, which processes the SQL and PL/SQL statements in the block together.
This reduces network traffic, making PL/SQL more efficient for batch processing and handling complex
logic.

Differences Between SQL and PL/SQL

Feature SQL PL/SQL

SQL is a single query

that is used to PL/SQL is a block of codes that used to write the entire program blocks/
Purpose
perform DML and procedure/ function, etc.
DDL operations.

It is declarative, that
defines what needs
Nature to be done, rather PL/SQL is procedural that defines how the things needs to be done.
than how things need
to be done.

Executes single
Execution Executes block of code
statement.

Data retrieval,
manipulation and
Use Case definition( eg. Mainly used to create an application.
SELECT, INSERT,
UPDATE)

Syntax SQL statements only SQL Statements combined with procedural logic
Feature SQL PL/SQL

Performs actions
Data Can contain SQL inside its blocks and is used for more control over data
directly on the
Handling handling
database.

4. System development life cycle .

System Development Life Cycle

The System Development Life Cycle (SDLC) provides a well-structured framework that gives an idea, of
how to build a system. It consists of steps as follows - Plan, Analyze, Design, Develop, Test, Implement
and Maintain. In this article, we will see all the stages of system development.

1. Planning

The foundation stage where project goals, scope, resources, and timelines are defined. It ensures the
system aligns with organizational objectives.

2. Analysis

In this phase, system requirements are gathered and analyzed through stakeholder input and process
study. It helps identify the system’s needs and problems to be solved.

3. Design

Translates the analyzed requirements into a detailed blueprint. It includes database design, architecture,
and interface layouts to guide the development process.
4. Development

The actual coding and creation of the system take place here. Developers build modules and perform
preliminary testing to ensure system functionality.

5. Testing

The system undergoes multiple testing levels—unit, integration, and user acceptance—to detect and
correct errors before implementation.

6. Implementation

The system is deployed in a live environment. This includes installation, data migration, user training,
and system configuration for real-time use.

7. Maintenance

An ongoing process of monitoring, updating, and improving the system. Bug fixes, performance
upgrades, and security patches keep the system effective and reliable.

Difference Between SDLC and System Design Life Cycle

Aspect System Development Life Cycle (SDLC) System Design Life Cycle (SDLC)

Covers entire system development from planning Focuses specifically on the design
Scope
to maintenance. phase.

Planning, Analysis, Design, Development, Testing, Preliminary Design, Detailed Design,

Phases
Implementation, Maintenance. Testing, Maintenance.

Creating detailed design specifications

Emphasis Overall system development and management.
and architecture.

Provide blueprints for developers to

Objective Deliver a complete, functional information system.
build the system.

Primarily involves designers and

Stakeholders Involves analysts, developers, testers, and users.
architects.

Technical design documents and

Output Fully developed and maintained system.
diagrams.

Significance of System Design in SDLC

System Design acts as a bridge between requirement analysis and system development.
It transforms user needs into technical blueprints, ensuring the final system meets desired
performance, functionality, and scalability.
Good design ensures smooth development, reduces errors, and enhances long-term system efficiency.

[Link] life cycle

Database Development Life Cycle (DDLC)

Database Development Life Cycle is a structured process imposed upon the development of the
database portion of the application. To develop an effective application a database must be created in a
structured process. At every step of DDLC, the database is involved and refined. With DDLC, the data
elements are more stable and make the database logically very sound.

There are various stages of the Database Development Life Cycle they are -

• Database initial study

• Database Design

• Implementation and loading

• Testing and evaluation

• Operation

• Maintenance

Block Diagram of Database Development Life Cycle

1. Database Initial Study

To begin with this, there will be different stages -

Phase-1

Stage 1 analyzes the company's circumstances. This stage too considers-

• What are the organization and its common requirements?

• What is its mission inside that environment?

• What is the organization's structure?

• What are the necessities for changing the database?

Phase-2

In stage 2 issues and imperatives are characterized. This stage considers-

• How does the existing framework work?

• What input does the framework require?

• How is the framework yield utilized? And, by whom?

• What reports does the framework generate?

• What is the operational relationship among trade units?

• What limits and limitations does the framework have?

Phase-3

In stage 3 the Goals are characterized. This stage considers-

• Objective of all proposed systems.

• Does The framework require sharing the information with other frameworks or users?

• Will the framework interface with the other existing or future frameworks in the company?

Phase-4

In stage 4 the scopes and boundaries are characterized.

• Scope What is the extent(size) of the plan based on operational requirements?

 • Boundaries such as Budget, equipment and computer program, and, Outside organizational
alter are required.

2. Database Design

The evaluation phase of a database design is the process of creating a detailed data model or blueprint
of the database. This data model consists of all the logical and physical design options and physical
storage parameters that are required to generate a design in data definition language that can be used
to create a database.

The database design is divided into four types.

• Conceptual Design

• DBMS software selection

• Logical Design

• Physical Design

1. Conceptual Design

Database modeling is used to create an abstract database structure, which permits you to emphasize
the big picture without getting into details. The model produced at this stage is from the client's
worldview, not the real world. Here you can split this model into small parts for better understanding.

2. DBMS Software Selection

For this software selection, we have to take special care of a few factors affecting the decision such as
Cost, Maintenance, Operational, License, Installation, Training, and Conversion costs.

3. Logical Design

The logical design translates the conceptual design into an internal model of the chosen DBMS. It
specifies a high-level language. It specifies what tables and connections between them should exist
Splitting a table into a small table and associating it with relations is called normalization

4. Physical Design

Physical design is a relocation of the expected schema into the actual database structure. At this time
we have to map the entities into tables, relationships to foreign keys, and unique identifiers to unique
keys.

3. Implementation and Loading

At this stage in the lifecycle:

 • Create a database storage group.

• Create a database within the storage group.

• Assign permissions to database administrators to use the database.

• Create tablespaces within the database.

4. Testing and Evaluation

Testing and evaluation is a way to determine the subject merit, worth, and significance, using the
criteria governed by a set of standards phase occur in parallel with the application programming..

5. Operation

The testing and evaluation phase is followed by the operation phase.

At the start of the operation phase, the process of system development always begins, as the system
evolves from a simple to a more complex form.

6. Maintenance

The maintenance phase plays a crucial role in database development as it includes major tasks such as
access management, database recovery, database backup, enhancing security, software updates, and
hardware maintenance.