DATABASE MANAGEMENT
SYSTEMS
Laboratory Manual
304
SCHOOL OF DISTANCE EDUCATION
ANDHRA UNIVERSITY,
VISAKHAPATNAM - 530 003
All copyrights and privileges reserved by the School of Distance
Education. No part of the publication may be reproduced in
any form without the prior permission of the copyright owner.
Information relating to various courses may be obtained from
the office of the School of Distance Education, Andhra
university, Visakhapatnam - 530 003.
DIRECTOR
SCHOOL OF DISTANCE EDUCATION
ANDHRA UNIVERSITY, VISAKHAPATNAM - 3
SCHOOL OF DISTANCE EDUCATION
ANDHRA UNIVERSITY
VISAKHAPATNAM
Prepared By
Dr. G.P. Saradhi Varma
Head of I.T. Dept
S.R.K.R. Engineering College
BHIMAVARAM - 534 204.
CONTENTS
S.l No Particulars Page.No.
1. INTRODUCTION TO RDBMS: 01
1.1 Advantages of Oracle 02
1.2Oracle as a DBA 02
1.3 Introduction to Oracle tools 02
1.4 SQL plus 04
1.5 Data types in Oracle 04
1.6 DDL commands 05
1.7 DML commands 05
2. SQL FOR ORACLE: 06
2.1 Overview of Data bases 07
2.2 History of RDBMS 07
2.3 Providing a given Database in RDBMS 08
2.4 Under which way RDBMS supports security 08
3. INTRODUCTION TO SQL: 11
3.1 Projection 16
3.2 Restriction 16
33 Different types of Operators 17
3.4 Between ..and Operator 17
3.5 In Operator 18
3.6 Like Operator 18
3.7 Is Null Operator 19
3.8 Cross Product 19
3.9 Clauses 19
3.10 SQL *plus Commands 22
3.11 SQL Number Functions 24
3.12 SQL String Functions 28
3.13 Date Functions 33
3.14 Conversion Functions 37
3.15 Joins 39
3.16 Group-By Clause 44
3.17 Having Cluse 48
3.18 SQL Command Set 48
4. QUERY LIST-1: 54
4.1 QUERIES:
4.1.1 Simple Queries 55
4.1.2 String Operations 55
4.1.3 Set Operations 55
4.1.4 Aggregate 56
4.1.5 Nested Sub Queries 56
4.1.6 Views 56
4.1.7 Modification of DataBases 56
4.2 ANSWERS TO QUERIES LIST-1 60
4.2.1 Customer
4.2.2 Branch
4.2.3 Account
4.2.4 Loan
4.2.5 Depositor
4.2.6 Borrower
5. QUERIES LIST-2 83
5.1 Relations
5.1.1. S
5.1.2. P
5.1.3. J 5.1.4 SPJ
6. ANSWERS TO QUERIES LIST-2 88
7. QUERIES LIST-3 92
8. ANSWERS TO QUERIES LIST-3 95
8.1 Relations
8.1.1 Employee
8.1.2 Department
9. QUERIES LIST-4 108
10. ANSWERS TO QUERIES LIST-4 113
10.1 Relations
10.1.1 Sailors
10.1.2 Boats
10.1.3 Reserves
11. INTRODUCTION TO PL/SQL 119
11.1 Structure of PL/SQI block 11.2 Syntax 120
11.3 Comments 120
11.4 Begin..End section 121
11.5 Declare section 121
11.6 Variable assignment 121
11.7 Analysis 122
11.8 Cursor 123
11.9 % TYPE attribute 123
11.10 %ROWTYPE attribute 124
11.11 %ROWCOUNT attribute 124
11.12 Procedure section 125
11.13 Cursor control commands 126
11.14 Conditional statements 128
11.15 Exception section 132
11.16 Raising Exception 132
11.17 Handling Exception 133
11.18 Displaying Output to User 134
11.19 Transaction control in PL/SQL 136
12. LIST TO PL/SQL PROGRAMS 137
13. ANSWERS TO PL/SQL PROGRAMS 139
13.1 To find the factorial of a given number. 140
13.2 GCD of two numbers 141
13.3 To check whether a given number is perfect or not. 142
13.4 To generate Fibonacci series. 142
13.5 To check whether a given number is palindrome or not 142
13.6 Display output in a given format 143
13.7 To update commission of an employee 144
13.8 To increase the salary of an employee in department number 10
for user given empno 145
13.9 To increase salary of an employee if sal<3000 or else 20% 146
13.10.To display details of a particular employee reading employee
name from keyboard 147
13.11 Display the details of top 8 employees in company 148
13..12 Display the details of employee for a given empno and delete
other records having same deptno. 149
14. PL/SQL PROGRAMS LIST-2 150
15. ANSWERS TO PL/SQL PROGRAMS LIST-2 152
15.1 Express a given number as a sum of two prime numbers. 153
15.2 Check whether a given number is strong or not 155
15.3 Procedure to update salaries of master table by scanning
transaction table. 156
