Unit 2

SQL and PL/SQL

By - Prof. Priyanka Shingate

 Department of Computer Engineering, ZCOER, Pune

SQL
 SQL is a language to specify queries in structured manner.
 Structured means relational data.
 SQL is a language to specify queries in a relational database.
 SQL is a standard language for accessing and manipulating databases.
 IBM Sequel language developed as part of System R project at the IBM San
Jose Research Laboratory.
 Renamed Structured Query Language (SQL).
 SQL allows users to communicate with Relational Databases and retrieve
data from their table.
 In database management systems like PostgreSQL, MySQL, Microsoft
SQL Server, Oracle Database and many others, SQL in DBMS is widely
used in these.

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

SQL

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

SQL-What Can SQL do?

o SQL can execute queries against a database
o SQL can retrieve data from a database
o SQL can insert records in a database
o SQL can update records in a database
o SQL can delete records from a database
o SQL can create new databases
o SQL can create new tables in a database
o SQL can create stored procedures in a database
o SQL can create views in a database
o SQL can set permissions on tables, procedures, and views

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Characteristics of SQL
 SQL in DBMS is a Domain-specific language:
 Domain-specific language means it will only work with relational databases, because in a
relational database, the data is stored in the form of Tables or relations, and each table has rows
and columns.
 SQL in DBMS is considered as the declarative language:
 Declarative language basically tells what to do. like the simple query needs to be written which
will tell what to do and all the things will be taken care of by the database then.
 SQL is an ANSI and ISO standard computer language for creating and manipulating databases.
 ANSI- American National Standards Institute
 ISO- International Organization for Standardization
 SQL allows the user to create, update, delete, and retrieve data from a database.
 SQL is very simple and easy to learn.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Advantages of SQL
 Faster Query Processing –
• Large amount of data is retrieved quickly and efficiently. Operations like Insertion, deletion,
manipulation of data is also done in almost no time.
 No Coding Skills –
• For data retrieval, large number of lines of code is not required. All basic keywords such as
SELECT, INSERT INTO, UPDATE, etc. are used and also the syntactical rules are not complex in
SQL, which makes it a user-friendly language.
 Standardized Language –
• Due to documentation and long establishment over years, it provides a uniform platform
worldwide to all its users.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Advantages of SQL
 Portable –
• It can be used in programs in PCs, server, laptops independent of any platform
(Operating System, etc). Also, it can be embedded with other applications as per
need/requirement/use.
 Interactive Language –
• Easy to learn and understand, answers to complex queries can be received in
seconds.
 Multiple data views –
• We can provide different views of contents of a database to different users.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Advantages of SQL
 Data Integrity:
• SQL databases enforce data integrity by enforcing constraints such as unique
keys, primary keys, and foreign keys, which help prevent data duplication and
maintain data accuracy.
 Backup and Recovery:
• SQL databases have built-in backup and recovery tools that help recover data in
case of system failures, crashes, or other disasters.
 Data Consistency:
• SQL databases ensure consistency of data across multiple tables through the use
of transactions, which ensure that changes made to one table are reflected in all
related tables.
Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

Disadvantages of SQL
 Cost – Some versions are costly and hence, programmers cannot access it.
 Difficulty in Interfacing: Interfacing an SQL database is more complex than adding a
few lines of code.
 Complexity: SQL databases can be complex to set up and manage, requiring skilled
database administrators to ensure optimal performance and maintain data integrity.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Data Types in SQL

• Data types define what type of data a column can hold integer, character, money, date and
time, binary, and so on.
• Data types mainly classified into three categories for every database.
• String Data types
• Numeric Data types
• Date and time Data types

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Data Types in SQL

1. String Data types:
 char(n): A FIXED length string (can contain letters, numbers, and special characters). The
size parameter specifies the column length in characters - can be from 0 to 255. Default is 1.

 varchar(n): A VARIABLE length string (can contain letters, numbers, and special
characters). The size parameter specifies the maximum string length in characters - can be
from 0 to 65535.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Data Types in SQL

2. Numeric Data types
 int: A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range
is from 0 to 4294967295. The size parameter specifies the maximum display width (which is
255)
 smallint: A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0
to 65535. The size parameter specifies the maximum display width (which is 255).
 numeric(p,d): Fixed point number, with user-specified precision of p digits, with d digits to
the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly, but not 4444.5
or 0.32)
 float(n): Floating point number, with user-specified precision of at least n digits.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Data Types in SQL

3. Date and time Data types:

 Date: Store a date only. From January 1, 0001 to December 31, 9999

 Time: it is used to specify the time format. Its format is hh:mm:ss. Its supported range is
from '-838:59:59' to '838:59:59'

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

SQL Literals
 A literal is a source code representation of a specific data value.
 There are several types of SQL literals –
strings, integers, decimal numbers and date and time values.
 String Literal: String literals are always in single quotes (‘).
E.g. 'Hello, world!’
 Integer Literal: Integer literals can be both positive and negative numbers, but they
cannot contain decimal fractions.
E.g. 435, +435, -435

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

SQL Literals
 Decimal Literal: Decimal literals can be both positive and negative numbers and
contain decimal fractions.
E.g. 43.5, +43.5, -43.5

 Date and Time Literal: symbolic representations of date and time values enclosed in
single quote.
E.g. 'May 09, 2019’, '2019/05/09’, '2019/05/09 10:47:33'

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

SQL
 SQL commands mainly categorized into five categories as:
1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DCL – Data Control Language
4. TCL – Transaction Control Language

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

DDL
 Data Definition Language
 DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
 All the command of DDL are auto-committed that means it permanently save all the changes in the
database.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

DDL
 List of DDL commands:
• CREATE: This command is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
• DROP: It is used to delete both the structure and record stored in the table.
• ALTER: This is used to alter the structure of the database.
 Alter command is helpful to change or modify the structure of the database or its object.
• TRUNCATE: This is used to remove all records from a table, including all spaces allocated
for the records are removed.
• RENAME: This is used to rename an object existing in the database.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

CREATE:
 It is used to create database,tables.
 his command is used to create a new table in SQL. The user has to give information like
table name, column names, and their datatypes.
 Syntax: CREATE DATABASE DATABASE_NAME;
 CRAETE DATABASE STUDENT;

DROP:
 This command is used to remove an existing table along with its structure
from the Database.

 Syntax: DROP TABLE TABLE_Name;

 Example: drop table student;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

TRUNCATE:
 It is used to delete all the rows from the table and free the space containing the table.
 Syntax: TRUNCATE TABLE table_name;
 Example: TRUNCATE TABLE EMPLOYEE;

RENAME:
 RENAME command is used to set a new name for any existing table.
 Following is the syntax,
 Syntax:
RENAME TABLE old_table_name to new_table_name
 Example:
RENAME TABLE student to students_info;
The above query will rename the table student to students_info.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

ALTER:
 It is used to alter the structure of the database. This change could be either to modify the
characteristics of an existing attribute or probably to add a new attribute.
 To add a new column in the table
• Syntax: ALTER TABLE table_name ADD column_name COLUMN-definition;
• Example: ALTER TABLE STUDENT_DETAILS ADD(ADDRESS VARCHAR2(20));

 To modify existing column in the table

• Syntax: ALTER TABLE table_name MODIFY(column_definitions....);
• Example: ALTER TABLE STUDENT_DETAILS MODIFY (NAME VARCHAR2(20));

 To delete a single column in the table

• Syntax: ALTER TABLE TABLE_NAME DROP Column_name;
• Example: alter table student drop name;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

DML
 Data Manipulation Language
• DML commands are used to modify the database. It is responsible for all form of changes
in the database.
• The command of DML is not auto-committed that means it can't permanently save all
the changes in the database. They can be rollback.
• Here are some commands that come under DML:
• SELECT
• INSERT
• UPDATE
• DELETE

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune
INSERT:
 The INSERT statement is a SQL query. It is used to insert data into the row of a table.
 Insert One Record:
• Syntax: INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
• Example: insert into student values(1,’abc’);
 Insert multiple records
• Syntax:INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3
, .... valueN);
• Example: INSERT INTO book (Author, Subject) VALUES (“korth", "DBMS");

UPDATE:
 This command is used to update or modify the value of a column in the table.
 Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

 For example:
UPDATE students SET User_Name = ’ABC' WHERE Student_Id = '3'

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

DELETE
 Delete command is used to delete records from a database table.
 The syntax for the delete command is as follows –
 Syntax:
Delete from <table_name>WHERE condition;
 Example
DELETE from Employee WHERE Emp_id=002;

SELECT:
 Select command is used to retrieve data from the database.
 Display all records:
 Syntax: SELECT * FROM TABLE_NAME;
 Example: select * from student;

 Display one record:

 Syntax: SELECT * FROM TABLE_NAME where condition;
 Example: select * from student where rollno=9;

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

DCL

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

DCL
Grant:
It is used to give user access privileges to a database.
Syntax: GRANT SELECT, UPDATE,
DELETE,INSERT ON MY_TABLE TO SOME_USER, ANOTHER_USER;
Example: GRANT SELECT, INSERT ON STUDENT TO RADHIKA;

• Revoke:
• It is used to take back permissions from the user.
• Syntax: REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
• Example: REVOKE SELECT, INSERT ON STUDENT TO RADHIKA;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Transaction Control Commands

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Commit
The COMMIT command is the transactional command used to save changes invoked by a
transaction to the database.

When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these
commands are not permanent, until the current session is closed, the changes made by these
commands can be rolled back.

To avoid that, we use the COMMIT command to mark the changes as permanent.

The syntax : for the COMMIT command is as follows: COMMIT;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Rollback
This command restores the database to last committed state.

If we have used the UPDATE command to make some changes into the database, and realize that
those changes were not required, then we can use the ROLLBACK command to rollback those
changes, if they were not committed using the COMMIT command.

Following is rollback command's syntax,

ROLLBACK;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Savepoint
SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that
point whenever required.

Following is savepoint command's syntax:

SAVEPOINT savepoint_name;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

SQL operators
 An operator is a reserved word or a character.
 These Operators are used to specify conditions in an SQL statement and to serve as
conjunctions for multiple conditions in a statement.
1. Arithmetic operators
2. Bitwise operators
3. Comparison operators
4. Logical operators

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

Arithmetic Operators

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

Bitwise Operators

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

Comparison Operators

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

Logical Operators

Some more LIKE Operators :

•%N: Searches for a value that ends with ‘N.’
•N%: Searches for a value that starts with
‘N.’
•N_____: Searches for a value that starts
with ‘N’ and has a length of 5 characters.
•N%s: Searches for a value that begins with
‘N’ and ends with ‘s.’
•%in%: Searches for a value that has ‘in’ at
someplace.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Logical Operators

ID NAME SCORE

1 NINJA_1 23

2 NINJA_2 43

3 NINJA_3 21

4 NINJA_4 43

5 NINJA_5 10

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Constraints
 SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the
accuracy and integrity of the data inside table.
 Constraints are used to make sure that the integrity of data is maintained in the database.
 Constraints can be divided into the following two types,
1. Column level constraints: Limits only column data.
2. Table level constraints: Limits whole table data.
 Following are the most used constraints that can be applied to a table.
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

NOT NULL Constraint

 By default, a column can hold NULL values. If you do not want a column to have a NULL value, use the
NOT NULL constraint.
 It restricts a column from having a NULL value.
 The NOT NULL constraint enforces a column to NOT accept NULL values.
 We use ALTER statement and MODIFY statement to specify this constraint.

 NOT NULL on CREATE TABLE:

 The following SQL ensures that the “rollno" column will NOT accept NULL values when the
“Student" table is created:
 Syntax: create table table_name(col_name datatype NOT NULL,…….);
 Example: create table stud(rollno int not null,name varchar(90));

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

NOT NULL Constraint

 NOT NULL on ALTER TABLE:
 To create a NOT NULL constraint on the "Age" column when the “Stud" table is already created, use
the following.
• Syntax: alter table table_name modify column_name datatype NOT NULL;
• Example: alter table stud modify age int not null;

 DROP NOT NULL:

 Syntax: alter table table_name modify age int;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Unique Constraint
 The UNIQUE constraint ensures that all values in a column are different.
 Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of
columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint.
 UNIQUE Constraint on CREATE TABLE
• For Single Column:
 The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is
created:
 CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Unique Constraint
 For Multiple columns:
 To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns,
use the following SQL syntax:
 CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Unique Constraint
 UNIQUE Constraint on ALTER TABLE
 For one Column:
 To create a UNIQUE constraint on the "ID" column when the table is already created, use the
following SQL:
 Syntax: alter table table_name add unique(column_name);
 Example: ALTER TABLE Persons ADD UNIQUE (ID);

 For Multiple Column:

• To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the
following SQL syntax:
 Syntax: alter table table_name add constraint const_name unique(col_name1,col_name2,…);
 Example: ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Unique Constraint
• DROP a UNIQUE Constraint:
• To drop a Single UNIQUE constraint, use the following SQL:
 Syntax: alter table table_name drop index column_name;
 Example: ALTER TABLE Persons DROP INDEX age;

• To drop a Multiple UNIQUE constraint, use the following SQL:

 Syntax: alter table table_name drop index index_name;
 ALTER TABLE Persons DROP INDEX UC_Person;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Primary Key Constraint

 Primary key constraint uniquely identifies each record in a database.
 A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used
to index the data inside the table.
 PRIMARY KEY on CREATE TABLE
• For One Column:
 The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
 Example:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Primary Key Constraint

 For Multiple Column:
• To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on
multiple columns, use the following SQL syntax:
• CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the
VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Primary Key Constraint

 PRIMARY KEY on ALTER TABLE
• For One column:
 To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use
the following SQL:
 Syntax: alter table table_name add primary key(column_name);
 Example: ALTER TABLE Persons ADD PRIMARY KEY (ID);
• For Multiple Column:
 To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on
multiple columns, use the following SQL syntax:
 Syntax: alter table table_name add constraint const_name primary
key(co_name1,col_name2…);
 Example: ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY
(ID,LastName);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Primary Key Constraint

 DROP a PRIMARY KEY Constraint
• To drop a PRIMARY KEY constraint, use the following SQL:
 Example: ALTER TABLE Persons DROP PRIMARY KEY;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Foreign Key Constraint

 Foreign Key is used to relate two tables.
 The relationship between the two tables matches the Primary Key in one of the tables with a
Foreign Key in the second table.
 This is also called a referencing key.
 The table with the foreign key is called the child table, and the table with the primary key is called the
referenced or parent table.
 A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in
another table.
 We use ALTER statement and ADD statement to specify this constraint.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Persons Table Orders Table

PersonID LastName Age OrderID OrderNumber PersonID

1 xyz 30 1 77895 3

2 pqr 20 2 44678 3

3 22456 2
3 abc 25
4 24562 1
 Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the
"Persons" table.
 The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
 The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

 FOREIGN KEY on CREATE TABLE:

• The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is
created:
• Example:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
constraint fk FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
 FOREIGN KEY on ALTER TABLE
• To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is
already created, use the following SQL:
• Example: ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES
Persons(PersonID);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

 DROP a FOREIGN KEY Constraint:

• To drop a FOREIGN KEY constraint, use the following SQL:
 Example: ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Check Constraint
 CHECK constraint is used to restrict the value of a column between a range.
 It performs check on the values, before storing them into the database. Its like condition checking before
saving data into a column.
 The CHECK constraint is used to limit the value range that can be placed in a column.
 If you define a CHECK constraint on a column it will allow only certain values for this column.
 If you define a CHECK constraint on a table it can limit the values in certain columns based on values in
other columns in the row.
 CHECK on CREATE TABLE
 For single column:
 The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created.
The CHECK constraint ensures that the age of a person must be 18, or older:
 Example: CREATE TABLE Persons (
ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age
int CHECK (Age>=18));

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Check Constraint
 For Multiple Column:
• To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple
columns, use the following SQL syntax
 Example:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Check Constraint
 CHECK on ALTER TABLE
 For single column:
• To create a CHECK constraint on the "Age" column when the table is already created, use the
following SQL:
 Example: ALTER TABLE Persons ADD CHECK (Age>=18);

 For multiple column:

• To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns,
use the following SQL syntax:
 Example:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Check Constraint
 DROP a CHECK Constraint:
• To drop a CHECK constraint, use the following SQL:
 Example:
ALTER TABLE Persons DROP CHECK CHK_PersonAge;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Default Constraint
 The DEFAULT constraint is used to set a default value for a column.
 The default value will be added to all new records, if no other value is specified.

 DEFAULT on CREATE TABLE

• The following SQL set a default values for the city column when persons table is created.
 Example:
 CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Default Constraint
 DEFAULT on ALTER TABLE
• To create a default constraint on the city column when the table is already used of the following
SQL:
 Example:
ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';

 DROP a DEFAULT Constraint

• To drop a DEFAULT constraint, use the following SQL:
 Example:
• ALTER TABLE Persons ALTER City DROP DEFAULT;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
• Writing a query inside another query is known as nested query or subquery.
• The inner query gets executed first, then output of an inner query is given as input to another
query.
• It is embedded in the HAVING or WHERE clause of the SQL statements.

Following are the important rules which must be followed by the SQL Subquery:
1. The SQL subqueries can be used with the following statements along with the SQL expression
operators:
• SELECT statement,
• UPDATE statement,
• INSERT statement, and
• DELETE statement.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
2. The subqueries in SQL are always enclosed in the parenthesis and placed on the right side of
the SQL operators.
3. We cannot use the ORDER BY clause in the subquery. But, we can use the GROUP BY clause,
which performs the same function as the ORDER BY clause.
4. If the subquery returns more than one record, we have to use the multiple value operators before
the Subquery.
5. We can use the BETWEEN operator within the subquery but not with the subquery.

1. Subquery with SELECT statement

• In SQL, inner queries or nested queries are used most frequently with the SELECT statement.
• The syntax of Subquery with the SELECT statement is described in the following block:

SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
Example
Consider the CUSTOMERS table having the following records
−
ID
ID NAME
NAME AGE
AGE ADDRESS
ADDRESS SALARY
SALARY
12 Ramesh
Khilan 35
25 Ahmedaba
Delhi 2000.00
1500.00 SELECT * FROM CUSTOMERS WHERE
d
ID IN (SELECT ID FROM CUSTOMERS
3 kaushik 23 Kota 2000.00
2 Khilan 25 Delhi 1500.00 WHERE SALARY > 4500) ;
4 Chaitali 25 Mumbai 6500.00
3 kaushik 23 Kota 2000.00
Output:
6 Komal 22 MP 4500.00
4 Chaitali 25 Mumbai 6500.00
7 Muffy 24 Indore 10000.00 ID NAME AGE ADDRESS SALARY
5 Hardik 27 Bhopal 8500.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 MP 4500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00
7 Muffy 24 Indore 10000.00

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
2. Subqueries with the INSERT Statement:
• Subqueries also can be used with INSERT statements. The INSERT statement uses the data
returned from the subquery to insert into another table.
• The basic syntax is as follows.
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]

• Example
Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to
copy the complete CUSTOMERS table into the CUSTOMERS_BKP table, you can use the
following syntax.

SQL> INSERT INTO CUSTOMERS_BKP

SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
3. Subqueries with the UPDATE Statement:
• The subquery can be used in conjunction with the UPDATE statement. Either single or multiple
columns in a table can be updated when using a subquery with the UPDATE statement.
• The basic syntax is as follows.
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
• Example
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table. The
following example updates SALARY by 0.25 times in the CUSTOMERS table for all the customers
whose AGE is greater than or equal to 27.
SQL> UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
3. Subqueries with the UPDATE Statement:

Output
Table: Customer:

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
4. Subqueries with the DELETE Statement
• The subquery can be used in conjunction with the DELETE statement like with any other
statements mentioned above.
• The basic syntax is as follows.
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

• Example
Assuming, we have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS
table. The following example deletes the records from the CUSTOMERS table for all the customers
whose AGE is greater than or equal to 27.

SQL> DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sub Query
4. Subqueries with the DELETE Statement
Output:

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Use of group by clause

• It is used for organizing similar data into groups. The data is further organized with the help of
equivalent function.
• It means, if different rows in a precise column have the same values, it will arrange those rows in
a group.
• 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.
Rules:
• The SELECT statement is used with the GROUP BY clause in the SQL query.
• WHERE clause is placed before the GROUP BY clause in SQL.
• ORDER BY clause is placed after the GROUP BY clause in SQL.

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

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Employee
Group By single column: Group By single column is used to place all
Sr. No. Name Age Salary the rows with the same value. These values are of that specified
column in one group. It signifies that all rows will put an equal amount
through a single column, which is of one appropriate column in one
1 Jaydip 24 25000
group.
Consider the below query:
2 Nikita 22 22000 SELECT NAME, SUM (SALARY) FROM Employee
GROUP BY NAME;
3 Athrava 25 15000
The output of the query is:
4 Nikita 22 22000
Name Salary

5 Jaydip 24 25000 Jaydip 50000

Nikita 44000
Athrava 15000

Database Management Unit 2

Department of Computer Engineering, ZCOER, Pune

Use of having clause

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Employee
SELECT NAME, SUM(SALARY) FROM Employee
Sr. No. Name Age Salary GROUP BY NAME
HAVING SUM(SALARY)>45000;
1 Jaydip 24 25000
The output of the query is:
2 Nikita 22 22000
Name Salary
3 Athrava 25 15000 Jaydip 50000

4 Nikita 22 22000

5 Jaydip 24 25000

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Use of order by clause

• The ORDER BY clause is used in SQL queries to sort the data returned by a query in ascending
or descending order. If we omit the sorting order, it sorts the summarized result in the ascending
order by default.
• ASC denotes ascending order, while DESC denotes descending order.

The following is the syntax to use the ORDER BY clause in a SQL statement:
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

SELECT Name, Age, Salary

Employee FROM Employee
ORDER BY Age ASC;
Sr. No. Name Age Salary
The output of the query is:
1 Jaydip 24 25000 Name Age Salary

2 Nikita 22 22000 Nikita 22 22000

3 Athrava 25 35000 Nikita 22 22000

4 Nikita 22 22000 Jaydip 24 25000

5 Jaydip 24 25000 Jaydip 24 25000

Athrava 25 35000

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Index
• Indexing is a data structure technique which allows you to quickly retrieve records from a
database file.
• An Index is an ordered list of contents of a column, (or a group of columns) of a table.
• By indexing we can improve the speed of searching and retrieving the record from table.

Index is created on one or more columns. Based on the number of columns included in the index,
an index can be:
• Simple index
• Composite Index

creating Simple Index

• An index created on a single column of a table is called a Simple Index. The syntax for creating
simple index that allows duplicate values is as described.

• Syntax:
CREATE INDEX <IndexName> ON <TableName> (<ColumnName>);

Eg. create index index1 on student_details(Class);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Index
Creating Composite Index
• An index created on more than one column is called a Composite Index.
The syntax for creating a composite index that allows duplicate values is:

• syntax:
CREATE INDEX <Index Name> ON <Table Name> (<ColumnNamel> <ColumnName2>);
Eg. create index index2 on student_details(Name, Class);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Index
Creation of Unique index
• A unique index can also be created on one or more columns. If an index is created on a
single column, it is called a Simple Unique Index. The syntax for creating a simple unique
index is as follows:

• Syntax:
CREATE UNIQUE INDEX <Index Name> ON <Table Name> (<Column Name>);

• If an index is created on more than one column, it is called a Composite Unique Index. The
syntax for creating a composite unique index is as follows:

CREATE UNIQUE INDEX <Index Name> ON <Table Name> (<Column Name>, <Column Name>);

• Eg. CREATE UNIQUE INDEX index_id on student_details(Student_id);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Sequence
• Sequence is a feature supported by some database systems to produce unique values on
demand.
• Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.
• AUTO_INCREMENT is applied on columns, it automatically increments the column value by 1
each time a new record is entered into the table.

• E.g. CREATE TABLE Persons (

Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

View
A View in SQL as a logical subset of data from one or more tables.
Views are used to restrict data access.
A View contains no data of its own but its like window through which data from tables can be viewed
or changed.
The table on which a View is based are called BASE Tables.

There are 2 types of Views in SQL:

1. Simple View
2. Complex View.

Simple views can only contain a single base table.

Complex views can be constructed on more than one base table. In particular, complex views can
contain: join conditions, a group by clause, a order by clause.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a single table or
multiple tables.
Syntax: Student_Detail
CREATE VIEW view_name AS
SELECT column1, column2..... STU_ID NAME ADDRESS
FROM table_name 1 RIYA Delhi
WHERE condition;
2 KARTIK Noida
Example 1: 3 PAYAL Ghaziabad
CREATE VIEW DetailsView AS 4 SUNNY Gurugram
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;
STU_ID NAME ADDRESS
we can query the view to view the data:- 1 RIYA Delhi
2 KARTIK Noida
SELECT * FROM DetailsView;
3 PAYAL Ghaziabad

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Example 2:
create view vw_student_details as(select RollNo,Name from student_details);

2. Dropping view
A view can be deleted using the Drop View statement.
Syntax
DROP VIEW view_name;

3. Updating view

Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Set Operations
The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

1.Union
2.UnionAll
3.Intersect
4.Minus

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Union
• The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
• In the union operation, all the number of datatype and columns must be same in both the tables on
which UNION operation is being applied.
• The union operation eliminates the duplicate rows from its resultset.

Syntax:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune
The First table The Second table
ID Name ID Name

1 Riya 3 Tanvee

2 Vijaya 4 Sejal

3 Tanvee 5 Pooja

ID Name
Union SQL query will be:
SELECT * FROM First 1 Riya
UNION
SELECT * FROM Second; 2 Vijaya

The resultset table will look 3 Tanvee

like:
4 Sejal

5 Pooja

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Union All
• Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.

Syntax:
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune
The First table The Second table
ID Name ID Name
1 Riya 3 Tanvee

2 Vijaya 4 Sejal

3 Tanvee 5 Pooja

ID Name

1 Riya
Union ALL SQL query will be:
2 Vijaya
SELECT * FROM First
UNION ALL 3 Tanvee
SELECT * FROM Second;
3 Tanvee

The resultset table will look like: 4 Sejal

5 Pooja

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Intersect
• It is used to combine two SELECT statements. The Intersect operation returns the common rows
from both the SELECT statements.
• In the Intersect operation, the number of datatype and columns must be the same.
• It has no duplicates and it arranges the data in ascending order by default.

Syntax
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune
The First table The Second table
ID Name ID Name
1 Riya 3 Tanvee

2 Vijaya 4 Sejal

3 Tanvee 5 Pooja

INTERSECT SQL query will be: ID Name

SELECT * FROM First 3 Tanvee
INTERSECT
SELECT * FROM Second;

The resultset table will look like:

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Minus
• It combines the result of two SELECT statements. Minus operator is used to display the rows
which are present in the first query but absent in the second query.
• It has no duplicates and data arranged in ascending order by default.

Syntax
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune
The First table The Second table
ID Name ID Name
1 Riya 3 Tanvee

2 Vijaya 4 Sejal

3 Tanvee 5 Pooja

Minus SQL query will be: ID Name

SELECT * FROM First 1 Riya

MINUS
2 Vijaya
SELECT * FROM Second;

The resultset table will look like:

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Predicates
• Predicates are keywords that specify a relationship between two expressions.
• A Predicate in DBMS is a condition expression which evaluates and results in Boolean value
either true or false which enables decision making in retrieving and manipulating a record.

• A predicate is a condition that is specified for:

 Filtering the data using the WHERE clause,

 Pattern matching in LIKE operator,
 Specifying a set of list for using IN operator,
 Manipulating a range of values using BETWEEN operator, etc

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Predicates in Where Clause

1) The predicate in where clause: select * from emp where
[job='MANAGER'];
Table :emp Output:

In our emp table, there are

three managers that’s why only three
records are displayed, because the
condition is true for only those three rows i.e
when the job is a manager.
Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

Predicates in ‘IN’ clause

2. The predicate in ‘IN’ clause
It is used to specify a set of values and where manipulation is performed on all the values specified
in the set and if any of the value that is present in the list matches with the values present in a
table then it returns true and is operation is performed

Example
select empno,job,sal,hiredate from emp where [ename in('SCOTT','FORD','SMITH','JONES')];

Records of all those

employees that are
specified in the list of
in clause are
displayed.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Predicate in ‘BETWEEN’ CAUSE

It is used to perform data comparison and manipulation over a range of values present in the
database table
Example
select empno,job,sal,hiredate from emp where [sal between 800 and 2900];

Output:
• The details of those employees
whose salary is present in the range
between 800/- to 2900/- are
retrieved and it also considers
specified values inclusive of the
range.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Predicate in ‘‘LIKE ‘ clause

The like operator is a pattern matching operator that returns those records that match with the
specified data pattern
Example

select empno,ename,hiredate,sal,job from emp where [ename like 'S%'];

Output:

All the records of employees whose names

starting with the letter ‘S’ are displayed.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Predicate in ‘IS NULL‘ clause

All operations upon null values present in the table must be done using this is null operator, we
cannot compare null value using the assignment operator(=).
Example
select * from emp where [comm is null];

Output:

The details of those employees whose

commission value is Null are displayed.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Predicate in ‘NOT‘ clause

Not operator is negation operator which is used along with like, between, is null, in operators, It
performs the reverse action of all these operators
Example
select * from emp where [sal NOT between 800 and 2900 ];

Output:

The details of those employees

whose salary does not fall in
the range between 800 to 2900
are displayed.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Join
• In case of SQL, JOIN means "to combine two or more tables".
• The SQL JOIN clause takes records from two or more tables in a database and combines it
together.

ANSI standard SQL defines five types of JOIN :

1. inner join,
2. left outer join,
3. right outer join,
4. full outer join,

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Inner Join
The INNER JOIN keyword selects all rows from both the tables as long as the condition
satisfies. 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 same.

Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 INNER JOIN table2
ON table1.matching_column = table2.matching_column;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

EMPLOYEE PROJECT

Output

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Left Join
This join returns all the rows of the table on the left side of the join and matching rows for the
table on the right side of join. The rows for which there is no matching row on right side, the
result-set will contain null. 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;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

EMPLOYEE PROJECT

Output

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Right Join
RIGHT JOIN is similar to LEFT JOIN. This 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 join. The rows for which there is no matching row on 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;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

EMPLOYEE PROJECT

Output

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Full Join
FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will
contain all the rows from both the tables. 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;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

EMPLOYEE PROJECT

Output

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Set Membership
 SQL allows testing tuples for membership in a relation.
 use the in and not in operations for set membership

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

IN Operator
 It is used to avoid multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
 Syntax
expression IN (value1, value2, .... value_n);
oexpression: It specifies a value to test.
o value1, value2,.. value_n: It specifies the values to test against expression.
 Example
SELECT * FROM Students
WHERE Students IN (‘Aditee', ‘Vijaya', ‘Tanvee’);
OR
SELECT * FROM Students
WHERE name = ‘Aditee’ OR name = ‘Vijaya' OR name = ‘Tanvee’;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

NOT IN Operator
 Syntax
expression NOT IN (value1, value2, .... value_n);
oexpression: It specifies a value to test.
o value1, value2,.. value_n: It specifies the values to test against expression.
 Example
SELECT * FROM Students
WHERE Students NOT IN (‘Aditee', ‘Vijaya', ‘Tanvee’);

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Example:
Student table:

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Aggregation Functions
• SQL aggregation function is used to perform the calculations on multiple rows of a single
column of a table. It returns a single value.
• It is also used to summarize the data.
• Types of SQL Aggregation Function:
1. count()
2. sum()
3. avg()
4. max()
5. min()

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

count()
• COUNT function is used to Count the number of rows in a database table. It can work on both
numeric and non-numeric data types.
• COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table.
COUNT(*) considers duplicate and Null.
 Syntax:
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

• Example:

STUDENT
Roll No Name Age Address
1. SELECT COUNT(*) FROM STUDENT;
1 A 15 Nashik
Output: 7
2 B 16 Pune

3 C 18 Mumbai
2. SELECT COUNT(*) FROM STUDENT;
4 D 12 Pune WHERE AGE>=18;
5 E 19 Nashik Output: 3
6 F 21 Nashik

7 G 17 Aurangabad

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

sum()
• Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
• Syntax:
SUM()
or
SUM( [ALL|DISTINCT] expression )
• Example:
SELECT SUM(COST) FROM PRODUCT;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

• Example:

PRODUCT

Product Compan QTY Rate Cost

y
1. SELECT SUM(COST) FROM PRODUCT;
1 A 2 15 20

2 B 3 16 35 Output: 555

3 C 4 18 160 2. SELECT SUM(COST) FROM PRODUCT

WHERE QTY>=3;
4 D 1 12 45
Output: 295
5 E 2 19 120

6 F 2 21 75

7 G 3 17 100

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

avg()
• The AVG function is used to calculate the average value of the numeric type. AVG function returns the
average of all non-Null values.
• Syntax:
AVG()
or
AVG( [ALL|DISTINCT] expression )

• Example:
SELECT AVG(COST) FROM PRODUCT;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

• Example:

PRODUCT

Produ Compa QTY Rate Cost

ct ny
1 A 2 15 20 SELECT AVG(COST) FROM PRODUCT;
2 B 3 16 35
Output: 79.28
3 C 4 18 160

4 D 1 12 45

5 E 2 19 120

6 F 2 21 75

7 G 3 17 100

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

max()
• MAX function is used to find the maximum value of a certain column. This function determines the largest
value of all selected values of a column.
• Syntax:
MAX()
or
MAX( [ALL|DISTINCT] expression )

• Example:
SELECT MAX(RATE) FROM PRODUCT;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

• Example:

PRODUCT

Produ Compa QTY Rate Cost

ct ny
SELECT MAX(RATE) FROM PRODUCT;
1 A 2 15 20

2 B 3 16 35 Output: 21

3 C 4 18 160

4 D 1 12 45

5 E 2 19 120

6 F 2 21 75

7 G 3 17 100

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

min()
• MIN function is used to find the minimum value of a certain column. This function determines the smallest
value of all selected values of a column.
• Syntax:
MIN()
or
MIN( [ALL|DISTINCT] expression )

• Example:
SELECT MIN(COST) FROM PRODUCT;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

• Example:

PRODUCT

Produ Compa QTY Rate Cost

ct ny
SELECT MIN(COST) FROM PRODUCT;
1 A 2 15 20

2 B 3 16 35 Output: 20

3 C 4 18 160

4 D 1 12 45

5 E 2 19 120

6 F 2 21 75

7 G 3 17 100

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Built-in Functions
Numeric Functions
Numeric Functions are used to perform operations on numbers and return numbers.
1. ABS(): It returns the absolute value of a number.
Syntax: SELECT ABS(-243.5);
Output: 243.5

2. ACOS(): It returns the cosine of a number. The specified number must be between -1 to 1, otherwise this function
returns NULL.
Syntax: SELECT ACOS(0.25);
Output: 1.318116071652818

3. ASIN(): It returns the arc sine of a number.

Syntax: SELECT ASIN(0.25);
Output: 0.25268025514207865

4. ATAN(): It returns the arc tangent of a number.

Syntax: SELECT ATAN(2.5);
Output: 1.1902899496825317

5. CEIL(): It returns the smallest integer value that is >= to a number.

Syntax: SELECT CEIL(25.75);
Output: 26 Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

OR
6. CEILING(): It returns the smallest integer value that is >= to a number.
Syntax: SELECT CEILING(25.75);
Output: 26

7. COS(): It returns the cosine of a number.

Syntax: SELECT COS(30);
Output: 0.15425144988758405

8. COT(): It returns the cotangent of a number.

Syntax: SELECT COT(6);
Output: -3.436353004180128

9. DEGREES(): It converts a radian value into degrees.

Syntax: SELECT DEGREES(1.5);
Output: 85.94366926962348

10. DIV(): It is used for integer division.

Syntax: SELECT 10 DIV 5;
Output: 2

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

11. FLOOR(): It returns the largest integer value that is <= to a number.
Syntax: SELECT FLOOR(25.75);
Output: 25

12. GREATEST(): It returns the greatest value in a list of expressions.

Syntax: SELECT GREATEST(30, 2, 36, 81, 125);
Output: 125

13. LOG(): Return the natural logarithm of a number or the logarithm of a number to a specified base.
Syntax: Select Log(2);
Output: 0.693147

14. LOG10(): It returns the base-10 logarithm of a number.

Syntax: SELECT LOG(2);
Output: 0.6931471805599453

15. LOG2(): It returns the base-2 logarithm of a number.

Syntax: SELECT LOG2(6);
Output: 2.584962500721156

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

Syntax: SELECT POW(4, 2);
Output: 16
Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

String Functions
String Functions are used to perform an operation on input string and return an output string.
1. ASCII(): This function is used to find the ASCII value of a character.
Syntax: SELECT ascii('t’);
Output: 116

2. CHAR_LENGTH(): This function is used to find the length of a word.

Syntax: SELECT char_length('Hello!’);
Output: 6

3. CONCAT(): This function is used to add two words or strings.

Syntax: SELECT ‘Hello' || ' ' || ‘World!!!' FROM dual;
Output: ‘HelloWorld!!!’

4. CONCAT_WS(): This function is used to add two words or strings with a symbol as concatenating symbol.
Syntax: SELECT CONCAT_WS('_', ‘hello', ‘world’);
Output: hello_world

5. FIND_IN_SET(): This function is used to find a symbol from a set of symbols.

Syntax: SELECT FIND_IN_SET('b', 'a, b, c, d, e, f’);
Output: 2

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

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

Syntax: LENGTH(‘HelloWorld’);
Output: 10

7. LOWER(): This function is used to convert the upper case string into lower case.
Syntax: SELECT LOWER(‘HELLOWORLD’);
Output: helloworld

8. REPEAT(): This function is used to write the given string again and again till the number of
times mentioned.
Syntax: SELECT REPEAT(‘hello', 2);
Output: hellohello

9. REVERSE(): This function is used to reverse a string.

Syntax: SELECT REVERSE(‘database’);
Output: ‘esabatad’

10. SUBSTR(): This function is used to find a sub string from the a string from the given position.
Syntax: SUBSTR('geeksforgeeks', 1, 6);
Output: ‘geeksf’

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/ SQL
 PL/SQL stands for Procedural Language extensions to the Structured Query
Language (SQL).
 PL/SQL is a combination of SQL along with the procedural features of
programming languages.
 Oracle uses a PL/SQL engine to processes the PL/SQL statements.
 PL/SQL includes procedural language elements like conditions and loops.
 It allows declaration of constants and variables, procedures and functions, types
and variable of those types and triggers.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Advantages of PL/ SQL

 Reduces network traffic :
This one is great advantages of PL/SQL. Because PL/SQL nature is entire block of SQL
statements execute into oracle engine all at once so it's main benefit is reducing the
network traffic.

 Procedural language support:

PL/SQL is a development tools not only for data manipulation futures but also provide the
conditional checking, looping or branching operations same as like other programming
language.

 Error handling :
PL/SQL is dealing with error handling, It's permits the smart way handling the errors and
giving user friendly error messages, when the errors are encountered.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Advantages of PL/ SQL

 Declare variable :
PL/SQL gives you control to declare variables and access them within the block. The
declared variables can be used at the time of query processing.

 Portable application:
Applications are written in PL/SQL are portable in any Operating system. PL/SQL
applications are independence program to run any computer.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/ SQL
Features of PL/SQL:
 PL/SQL is basically a procedural language, which provides the functionality of
decision making, iteration and many more features of procedural programming
languages.
 PL/SQL can execute a number of queries in one block using single command.
 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.
 PL/SQL provides a feature to handle the exception which occurs in PL/SQL block
known as exception handling block.
 Applications written in PL/SQL are portable to computer hardware or operating system
where Oracle is operational.
 PL/SQL Offers extensive error checking.
PL/SQL Concepts:
Cursors, Stored Procedures, Stored Functions, Database Triggers.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Difference between SQL & PL/SQL

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/ SQL Basic Syntax

1.Structure of PL/SQL Block:

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.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/ SQL Basic Syntax

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

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
Output:

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/ SQL Comments

The PL/SQL single-line comments start with the delimiter -- (double
hyphen) and multi-line comments are enclosed by /* and */.

DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/*
* PL/SQL executable statement(s)
*/
dbms_output.put_line(message);
END;
/

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL - Data Types

• Datatypes defines the domain of the columns in the table or variables in the code.
• They define whether the column or variable contains numbers, alphabets, Boolean
values etc.
Following are the types of inbuilt data types
 Scalar data types : – Used to store scalar values like numbers, decimals etc.
 Composite data types : – It is combination of other datatypes usually scalar
datatypes.
 Reference data types : – Used to store the information about another datatype. It can
be referred as pointers in C.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL - Data Types

PL/SQL program is written to manipulate different types of data.
Following are the Data types:
1. NUMBER : Zero, Positive and negative numbers
2. NUMBER(p,s) : Number with precision and scale.
3. BOOLEAN: stores the values True or False.
4. CHAR(Size): fixed length string of maximum 32767 characters.
5. VARCHAR2(size): variable length string Maximum characters.
6. DATE: stores the date & time data.
7. ROWID: The ROWID data type represents the actual storage address of a row.
8. %RowType: Stores the datatype of all the columns in a table. All columns are identified by
%ROWTYPE datatype
.E.g. EMP%ROWTYPE–> a table will be assigned with the EMP table’s entire column datatype.
Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

Types of Variables
• A variable is a meaningful name which facilitates a programmer to store data temporarily
during the execution of code.
• It helps you to manipulate data in PL/SQL programs.
• It is nothing except a name given to a storage area.
• Each variable in the PL/SQL has a specific data type which defines the size and layout of the
variable's memory.

declare variable in PL/SQL

You must declare the PL/SQL variable in the declaration section or in a package as a global
variable.
Syntax for declaring variable:
<Variable_name> <datatype> [NOT NULL] [:= <value>];
Example:
1. Radius Number := 5;
2. Date_of_birth date;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Naming rules for PL/SQL variables

• Following are the Naming rules for PL/SQL variables:
• The variable in PL/SQL must follow some naming rules like other programming
languages.
• The variable_name should not exceed 30 characters.
• Variable name should not be the same as the table table's column of that block.
• The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so
it could be either lowercase or uppercase. For example: v_data and V_DATA refer to the
same variables.
• You should make your variable easy to read and understand, after the first character, it
may be any number, underscore (_) or dollar sign ($).
• NOT NULL is an optional specification on the variable.
Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

Variable Scope in PL/SQL:

:
Variable Scope in PL/SQL:
• PL/SQL allows nesting of blocks. A program block can contain another inner block.
• If you declare a variable within an inner block, it is not accessible to an outer block.
• There are two types of variable scope:
 Local Variable: Local variables are the inner block variables which are not accessible
to outer blocks.
 Global Variable: Global variables are declared in outermost block.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

PL/SQL has a number of control structures which includes:
1. Conditional controls: Conditional selection statements, which run
different statements for different data values. The conditional selection
statements are IF and CASE.

2. Iterative or loop controls: Loop statements, which run the same

statements with a series of different data values. The loop statements
are the basic LOOP, FOR LOOP, and WHILE LOOP.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

1. Conditional controls: Conditional selection statements, which run
different statements for different data values. The conditional selection
statements are IF and CASE.
2. The IF statement has these forms:
1. IF THEN
2. IF THEN ELSE
3. IF THEN ELSIF

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

1. IF THEN Statement:
• The IF THEN statement either runs or skips a sequence of one or more
statements, depending on a condition.
• The IF THEN statement has this structure:
IF condition THEN
statements
END IF;

2. IF THEN ELSE Statement

• The IF THEN ELSE statement has this structure:
IF condition THEN
statements
ELSE
else_statements
END IF;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

3. IF THEN ELSIF Statement:
The IF THEN ELSIF statement has this structure:
IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
[ ELSIF condition_3 THEN
statements_3
]...
[ ELSE
else_statements
]
END IF;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

2. Iterative or loop controls: Loop statements, which run the same statements with a
series of different data values.
Loop statements run the same statements with a series of different values.
The loop statements are:
1. LOOP
2. WHILE LOOP
3. FOR-LOOP

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

1) LOOP: 2) While LOOP
LOOP
...SQL WHILE condition LOOP
Statements... ...<SQL Statements... >
END LOOP;
EXIT;
END LOOP;

3. For Loop

FOR <variable(numeric)> IN [REVERSE] <lowerbound>..<upperbound>

LOOP
<statements>.... .....
END LOOP;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

4. Exiting LOOP using EXIT WHEN:
Condition can be specified with EXIT statement itself.
Syntax:

LOOP
<statements>
EXIT WHEN <condition>;
END LOOP

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

PL/SQL Control Structure

2. Write a PL?SQL block to print the sum of numbers from 1 to 50.

DECLARE
n number(5) : = 1;
sum number(5) : = 0;
BEGIN OUTPUT:
Loop
sum : = sum + n; sum of first 50 numbers is 1275
n : = n+1;
Exit when n > 50; PL/SQL procedure successfully
End loop; completed.
dbms_output.put_line(‘sum of first 50 numbers is’||sum);
END;
/

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Cursor
Context area:
When processing an SQL statement, Oracle creates a temporary work area in the system
memory which contains all the information needed for processing the statement known
as context area.

Cursor:
A cursor is a pointer to context area i.e. Context area is controlled by the cursor. It is
used to fetch and manipulate the data returned by the SQL statement.
Note: The set of rows the cursor holds is known as active set.
A cursor is a pointer to the work area or context area, used by the oracle engine
for executing SQL statements.

Types of cursors:
1. Implicit cursors.
2. Explicit cursors.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Implicit Cursor
Implicit Cursor
• The cursor which is automatically created, maintained and closed by the Oracle engine while
execution of any DML(Data Manipulation Language) queries like INSERT, UPDATE or DELETE are
called Implicit Cursor.
• Implicit Cursors are controlled by Oracle and programmers cannot access its information.
• When a DML statement is executed an implicit cursor is created and attached to it.
• Following are the Implicit Cursor Attributes

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Explicit Cursor
Explicit Cursor
• Explicit cursors are the user defined cursors to gain more control over the context area.
• The cursor which has to be created, maintained and closed by a program through PL/SQL code for
the execution of any SELECT query that returns more than one row is called Explicit Cursor.
• It is a user-defined cursor declared in the Declare section of PL/SQL block and is used in its
Executable section.
• Syntax for creating an explicit cursor:
• CURSOR cursor_name IS select_statement;

How to use explicit cursor?

1. DECLARE the cursor for initialization in the declaration section.
2. OPEN the cursor for memory allocation in the execution section.
3. FETCH the cursor for retrieving data in the execution section.
4. CLOSE the cursor to release allocated memory in the execution section.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Explicit Cursor Syntax

1. Declare Cursor:
A cursor is a select statement, defined in the declaration section in MySQL.
Syntax
CURSOR <cursor_name> IS <SELECT statement>

Example:

CURSOR cur_students IS
SELECT rollNo, name, address FROM students;

2. Open Cursor:
After declaring the cursor the next step is to open the cursor using open statement.
Syntax: Open cursor_name;

Example:

Open cur_students;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Explicit Cursor Syntax

3. Fetch Cursor
After declaring and opening the cursor, the next step is to fetch the cursor. It is used to fetch the row or
the column.
Syntax: FETCH <cursor_name> INTO <cursor_variable>;

Example:

Fetch cur_students into sname;

4. Close Cursor
The final step is to close the cursor.
Syntax: Close cursor_name;

Example: close cur_students;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Explicit Cursor Syntax

DECLARE
CURSOR <cursor_name> IS <SELECT statement>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Example of Explicit Cursor

Example: Write a PL/SQL block of code using parameterized Cursor that will merge the
data available in the newly created table N_RollCall with the data available in the table
O_RollCall. If the data in the first table already exist in the second table then that data
should be skipped.

Table1: o_rollcall Table2: n_rollcall

Name Rollno Address Name Rollno Address

Komal 1 pune Komal 1 pune

Raj 2 nashik leena 4 solapur

Rudra 3 Mumbai shital 5 satara

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Example of Explicit Cursor

create procedure n1(in rno1 int)
begin
declare rno2 int;
declare exit_cond boolean;
declare c1 cursor for select rollno from o_rollcall where rollno>rno1; Output:
declare continue handler for not found set exit_cond=TRUE; Select * from n_rollcall
open c1;
l1:loop Name Rollno Address
fetch c1 into rno2; Komal 1 pune
if not exists(select * from n_rollcall where rollno=rno2)then
insert into n_rollcall select * from o_rollcall where rollno=rno2; leena 4 solapur
end if;
if exit_cond then shital 5 satara
close c1;
Raj 2 nashik
leave l1;
end if; Rudra 3 Mumbai
end loop l1;
end
//
Database Management Unit 2
 Department of Computer Engineering, ZCOER, Pune

Stored Procedure
Stored Procedures are created to perform one or more DML operations on Database.
It is nothing but the group of SQL statements that accepts some input in the form of parameters and
performs some task and may or may not returns a value.
Syntax : Creating a Procedure
CREATE or REPLACE PROCEDURE name(parameters)
IS
variables;
BEGIN
//statements;
END;

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Stored Procedure
There are 3 different types of parameters, they are as follows:
IN:
• This is the Default Parameter for the procedure. It always receives the values from calling
program.
• A parameter whose value is passed into a stored procedure/function module.
• The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

OUT:
• This parameter always sends the values to the calling program.
• A parameter whose value is passed out of the stored procedure/function module, back to the calling
PL/SQL block. An OUT parameter must be a variable, not a constant.

IN OUT:
This parameter performs both the operations. It Receives value from as well as sends the values to
the calling program.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Database Trigger
Triggers in oracle are blocks of PL/SQL code which oracle engine can execute automatically based
on some action or event.
A trigger is a stored procedure in database which automatically invokes whenever a special event in
the database occurs. For example, a trigger can be invoked when a row is inserted into a specified
table or when certain table columns are being updated.

These events can be:

• DDL statements (CREATE, ALTER, DROP, TRUNCATE)
• DML statements (INSERT, SELECT, UPDATE, DELETE)
• Database operation like connecting or disconnecting to oracle (LOGON, LOGOFF, SHUTDOWN)

Triggers are automatically and repeatedly called upon by oracle engine on satisfying certain
condition.

Triggers can be activated or deactivated depending on the requirements.

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Database Trigger
Syntax:
create trigger [trigger_name] [before | after] {insert | update | delete} on [table_name] [for each row]
[trigger_body]

Explanation of syntax:
1.create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
2.[before | after]: This specifies when the trigger will be executed.
3.{insert | update | delete}: This specifies the DML operation.
4.on [table_name]: This specifies the name of the table associated with the trigger.
5.[for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being
affected.
6.[trigger_body]: This provides the operation to be performed as trigger is fired

Database Management Unit 2

 Department of Computer Engineering, ZCOER, Pune

Example of Trigger
Example: Write a database trigger on Library table. The System should keep track of the records that
are being updated or deleted. The old value of updated or deleted records should be added in
Library_Audit table.
Program:
delimiter //
create trigger tr_ins77_Borrower
before insert
on table1
for each row
begin
insert into table2 values(new.rollno,new.name,new.nameofbook);
end
//
Output: Table2:
mysql> insert into table1 values(1,'gg','ggh');
rollno name nameofbook
-> //
Query OK, 1 row affected (0.06 sec) 1 gg ggh
mysql> select * from table2;
-> //
Database Management Unit 2
Thank You !!!