SR Page Signature

No. Content No.

1. DBMS and its Characteristics

2. Advantages and Disadvantages

3. SQL

4. Create, Alter, Rename and Drop Statement

5. Insert into, Delete and Update Statement

6. Simple select statement

7. Nesting of Queries
8. Aggregate Functions

9. String Functions
10. Joins

11. Cursors

12. Triggers
13. Exercise 1

14. Exercise 2
15. PL/SQL

16. Write a PL/SQL code to add two numbers and display the
result. Read the numbers during run time.

17. Write a PL/SQL code to find sum of first 10 natural

numbers using while and for loop.

18. Write a program to create a trigger which will convert the

name of a student to upper case before inserting or
updating the name column of student table.

19. Write a PL/SQL block to count the number of rows affected

by an update statement using SQL%ROWCOUNT.

20. Write a PL/SQL block to increase the salary of all doctors

INDEX
by 1000.
 DBMS and its Characteristics
What is Database Management System?
DBMS is a software for storing and retrieving users’ data
while considering appropriate security measures. It consists
of a group of programs which manipulate the database. The
DBMS accepts the request for data from an application and
instructs the operating system to provide the specific data. In
large systems, a DBMS helps users and other third-party
software to store and retrieve data.

Characteristics of DBMS
Real-world entity: Modern DBMS are more realistic and uses
real world entities to design its architecture. It uses the
behaviour and attributes too. For example, a school database
may use student as entity and their age as their attribute.

Relation-based tables: DBMS allows entities and relations

among them to form as tables. This eases the concept of data
saving. A user can understand the architecture of database
just by looking at table names etc.

Isolation of data and application: A database system is entirely

different than its data. Where database is said to active entity, data is
said to be passive one on which the database works and organises.
DBMS also stores metadata which is data about data, to ease its own
process.
Less redundancy: DBMS follows rules of normalization, which
splits a relation when any of its attributes is having redundancy in
values. Following normalization, which itself is a mathematically rich
and scientific process, make the entire database to contain as less
redundancy as possible.

Consistency: Consistency is a state where every relation in

database remains consistent. There exist methods and techniques,
which can detect attempt of leaving database in inconsistent state.

Multiple views: DBMS offers multiples views for different users. A

user who is in sales department will have a different view of
database than a person working in production department. This
enables user to have a concentrate view of database according to
their requirements.

Security: Features like multiple views offers security at some extent

where users are unable to access data of other users and
departments. DBMS offers methods to impose constraints while
entering data into database and retrieving data at later stage.
 Advantages and Disadvantages

Advantages of DBMS
[Link] Integrity: Data integrity means data is consistent and
accurate in the database. It is essential as there are multiple
databases in DBMS. All these databases contain data which is visible
to multiple users. Therefore, it is essential to ensure that data is
consistent and correct in all databases for all users.

[Link] Security: Data security is a vital concept in a database. Only

users their identity must be authenticated using username and
password.

[Link] data integration: Due to the database management

system, we have access to well managed and synchronized
form of data making it easy to handle. It also gives an
integrated view of how a particular organization is working
and keeps track of how one segment of the company affects
another segment.
Minimized Data Inconsistency: Data inconsistency occurs
between files when various versions of the same data appear in
different places. Data consistency is ensured in the database; there is
no data redundancy. Besides, any database changes are immediately
reflected by all users, and there is no data inconsistency.
[Link] Data Access: The database management system helps
the users to produce quick answers to queries making data accessing
accurate and faster.
Disadvantages of DBMS

[Link] Cost: The high cost of software and hardware is the main
disadvantage of the database management system. Database users
require a high-speed processor and huge memory size to use the
database on the DBMS. Sometimes, users require costly machines for
maintaining databases.

[Link] Size: The size of the database is not big at the initial state,
but when the user stores a large amount of data, then it creates
many problems. Due to the huge data, database systems do not
provide good results and do not run efficiently. That's why the size is
another limitation of the database systems.

[Link]: Database management system (DBMS) is so complex

for non-technical users. So, it isn't easy to manage and maintain
database systems. Therefore, training for the designers, users, and
administrators is necessary to efficiently run the database systems.

[Link] Staff Cost: DBMS requires an educated and skilled

staff for managing and maintaining the databases. So, we need to
spend a lot of money to get this level of trained and experienced
staff.

[Link] of Data Conversion: It is one of the big disadvantages of

the database management system because the cost of data
conversion is very high. There is a requirement for trained, skilled,
and experienced database administrators for converting the data
smoothly.

[Link] of Technical Staff: A non-technical people can't

understand the complexity of the database. So, the technical staff is
required for maintaining and handling the database management
system.
 SQL
Introduction to SQL
Structure Query Language (SQL) is a database query language used
for storing and managing data in Relational DBMS. SQL was the first
commercial language introduced for E.F
Codd's Relational model of database. Today almost all RDBMS
(MySQL, Oracle, Informix, Sybase, MS Access) use SQL as the
standard database query language. SQL is used to perform all types
of data operations in RDBMS.
CREATE, ALTER, RENAME and DROP
statement
CREATE: It is used to create a new table in the database.
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Creating the Table

DROP: It is used to delete both the structure and record

stored in a table.

Syntax:
DROP TABLE table_name;

Dropping the Table

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.
Syntax:
ALTER TABLE table_name ADD column_name COLUMN-definition;
Alter the Table

Rename: To change the name of an existing table

Syntax:
Rename Old_table_name To New_table_name ;

Rename the Table

Insert into, Delete and Update
Statement
INSERT: The INSERT statement is a SQL query. It is used to
insert data into the row of a table.
Syntax: INSERT INTO TABLE_NAME
(col1, col2, col3,. col N)
VALUES (value1, value2, value3, valueN);

Inserting value of table

UPDATE: This command is used to modify or update the

value of a column in a table.
Syntax:
UPDATE table_name SET [column_name1=
value1,...column_nameN = value N] [WHERE CONDITION]
Update the table

DELETE: It is used to remove one or more row from a table.

Syntax: DELETE FROM table_name [WHERE condition];

Delete the Table

 Simple select statement

Select statement in where condition

Select statement Order by

Select Statement Group by

Having clause select statement

Simple Select Statement

Nesting of Queries

Q. List the name and salary of the employees who get salary
greater than the minimum salary of the table?

Aggregate Functions
Min aggregate function

Max aggregate function

Count aggregate function

Sum aggregate function

Average Aggregate Function

String Functions
All string functions with their screenshots
 JOINS

Cursors
declare
begin
update doctor
set sal=sal+1000;
if SQL%FOUND then
DBMS_OUTPUT.PUT_LINE('Records are updated');
end if;
end;
select * from doctor;
 Triggers
Exercise 1

Consider the following schema for a Library Database:

BOOK (Book id, Title, Publisher Name. Pub Year) PUBLISHER

(Name, Address, Phone) BOOK_COPIES (Book_id, Branch_id,
No-of_Copies)

BOOK AUTHORS (Book_id, Author_Name)

BOOK LENDING (Book id, Branch_id, Card No. Date Out, Due
Date)

LIBRARY BRANCH (Branch id, Branch Name, Address)

Write SQL queries to

1. Retrieve details of all books in the library_id, title, name of

publisher, authors, number of copies in each branch, etc.

2. Get the particulars of borrowers who have borrowed more

than 3 books between Jan 2018 to Jun 2018

3. Delete a book in BOOK table. Update the contents of other

tables to reflect this data manipulation operation.

4. Partition the BOOK table based on year of publication.

Demonstrate its working with a simple query.
5. Create a view of all books and its number of copies that
are currently available in the library.
 Exercise2
Consider the following schema for Order Database:
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade,
Salesman_id) ORDERS (Ord_No, Purchase_Amt, Ord_Date,
Customer_id, Salesman_id)

Write SQL queries to

1. Count the customers with grades above Amritsar's
average.
2. Find the name and numbers of all salesmen who had more
than one customer,
3. List all salesmen and indicate those who have and don't
have customers in their cities(Use UNION operation.)
4. Create a view that finds the salesman who has the
customer with the highest order of a day.
5. Demonstrate the DELETE operation by removing salesman
with id 1000. All his orders must also be deleted.
PL/SQL
PL/SQL is a block structured language that enables developers to
combine the power of SQL with procedural statements. All the
statements of a block are passed to oracle engine all at once which
increases processing speed and decreases the traffic.

Basics of 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.

Advantages and Disadvantages of SQL:

Features of PL/SQL:

(16) Write a PL/SQL code to add two numbers

and display the result. Read the numbers during
run time.
Declare
x number(5);
y number(5);
z number(7);
begin
x:=&x;
y:=&y;
z:=x+y;
dbms_output.put_line('Sum is '||z);
end;
Write a PL/SQL code to find sum of first 10
natural numbers using while and for loop.

Write a program to create a trigger which will

convert the name of a student to upper case
before inserting or updating the name column of
student table.
create table student1 (rollno number(4),name
varchar2(14),marks number(3));

INSERT INTO student1 VALUES (101, 'abhay', '75');

INSERT INTO student1 VALUES (102, 'seema', '72');
INSERT INTO student1 VALUES (103, 'chaitanya', '69');
select * from student1;

CREATE OR REPLACE TRIGGER std

BEFORE INSERT OR UPDATE ON student1
FOR EACH ROW
DECLARE
std_flag INTEGER;
BEGIN
:[Link] := UPPER(:[Link]);
END;
/

INSERT INTO student1 values (106,'manisha',56);

INSERT INTO student1 values (107,'ravi',83);
select * from student1;

UPDATE student1 SET name = 'sakshi' WHERE rollno = 103;

SELECT * from student1;

Write a PL/SQL block to count the number of
rows affected by an update statement using SQL
%ROWCOUNT.
CREATE TABLE EMPL (EMPNO NUMBER(4) NOT NULL,ENAME
VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER(7, 2));

INSERT INTO EMPL VALUES (7369, 'SMITH', 'CLERK', 800);

INSERT INTO EMPL VALUES (7475, 'ALLEN',
'MANAGER',1200);
INSERT INTO EMPL VALUES (7500, 'FORD', 'PRESIDENT',
3500);
INSERT INTO EMPL VALUES (7218, 'MARTIN', 'CLERK', 900);
INSERT INTO EMPL VALUES (7645, 'JOHN', 'SALESMAN',
2700);

SELECT * FROM EMPL;

BEGIN
UPDATE EMPL
SET sal = SAL+SAL*0.25;
DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||
SQL%ROWCOUNT);
END;

SELECT * FROM EMPL;

Write a PL/SQL block to increase the salary of all
doctors by 1000.
CREATE TABLE DOCTOR(DOCID NUMBER(4) NOT
NULL,DNAME VARCHAR2(10), DEPT VARCHAR2(9), SAL
NUMBER(7));

INSERT INTO DOCTOR VALUES (1401, 'ARJUN', 'ORTHO',

70000);
INSERT INTO DOCTOR VALUES (1255, 'RADHIKA',
'PHYSIO',45000);
INSERT INTO DOCTOR VALUES (1248, 'SEEMA',
'ORTHO',60000);
INSERT INTO DOCTOR VALUES (1429, 'MANISH', 'SURGEON',
90000);
INSERT INTO DOCTOR VALUES (1356, 'DEEPAK', 'NEURO',
55000);

SELECT * FROM DOCTOR;

BEGIN
UPDATE DOCTOR
SET SAL = SAL+1000;
END;

SELECT * FROM DOCTOR;