CSE 2212 Database Systems Lab Manual

User Manual: Pdf

Open the PDF directly: View PDF PDF.
Page Count: 148

DownloadCSE 2212 - Database Systems Lab Manual
Open PDF In BrowserView PDF
DEPARTMENT OF COMPUTER SCIENCE &
ENGINEERING

CERTIFICATE
This is to certify that Ms./Mr. .…………………...………………………………
Reg. No.: …..………………… Section: …………… Roll No.: ………………...
has satisfactorily completed the lab exercises prescribed for Database Systems Lab
[CSE 2212] of Second Year B.Tech. (Computer Science and Engineering) Degree
at MIT, Manipal, in the academic year 2017-2018.

Date: ……...................................

Signature
Faculty in Charge

i

ii

CONTENTS
LAB NO.

TITLE

PAGE NO.

COURSE OBJECTIVES AND
OUTCOMES

v

EVALUATION PLAN

v

INSTRUCTIONS TO THE STUDENTS

vi

SAMPLE LAB OBSERVATION NOTE
PREPARATION

viii

1.

MS ACCESS

1

2.

INTRODUCTION TO SQL

6

3.

INTERMEDIATE SQL

11

4.

INTEGRITY CONSTRAINTS IN SQL

16

5.

ADDITIONAL EXERCISES ON SQL

23

6.

PL/SQL BASICS

26

7.

EXCEPTION HANDLING AND
CURSORS

37

8.

ADDITIONAL CURSOR
CONSTRUCTS AND TRANSACTIONS

45

9.

PROCEDURES, FUNCTIONS AND
PACKAGES

52

10.

TRIGGERS

65

11.

MINI PROJECT (PHASE I)

70

12.

MINI PROJECT (PHASE II)

71

APPENDIX

73

iii

REMARKS

iv

Course Objectives
• To illustrate and explain basic concepts and terminology related to database and
storage management.
• Learn to develop stored procedures, functions and packages.
• To develop an application software with host language interface.
Course Outcomes
At the end of this course, students will have the
• Ability to write queries for design and manipulation of database tables using
ORACLE.
• Ability to understand the effective use of stored procedures, functions and packages.
• Design and develop applications using ORACLE.
Evaluation Plan
•

Internal Assessment Marks: 60%
9 Continuous evaluation component (for each experiment):10 marks.
9 The assessment will depend on punctuality, program execution, maintaining the
observation note and answering the questions in viva voce.
9 Total marks for the 10 experiments is 40 Marks.
9 Mini project is for 20 Marks.

•

End semester assessment of 2 hour duration: 40%

v

INSTRUCTIONS TO THE STUDENTS
Pre-Lab Session Instructions
•

Students should carry the Lab Manual Book and the required stationery to every lab
session.

•

Be in time and follow the institution dress code.

•

Must Sign in the log register provided.

•

Make sure to occupy the allotted seat and answer the attendance.

•

Adhere to the rules and maintain the decorum.

In-Lab Session Instructions
•

Follow the instructions on the allotted exercises.

•

Show the program and results to the lab Teacher on completion of experiments.

•

You must have your lab notebook signed by your lab Teacher before you leave lab
each day. Any pages not signed on the day the experiment was performed will
adversely affect your lab notebook grade.

•

Prescribed textbooks and class notes can be kept ready for reference if required.

General Instructions for the exercises in Lab
•

Implement the given exercise individually and not in a group.

•

The programs should meet the following criteria:
o Programs should perform input validation (Data type, range error, etc.).
o Use meaningful names for variables and functions.

•

Plagiarism (copying from others) is strictly prohibited and would invite severe
penalty in evaluation.

•

The exercises for each week are divided under two sets:
o Lab Exercises – to be completed during lab hours.

vi

o Additional Exercises – to be completed outside the lab or in the lab to enhance
the skill.
•

In case a student misses a lab class, he/she must ensure that the experiment is
completed during the repetition lab with the permission of the faculty concerned but
credit will be given only to one day’s experiment(s).

•

Questions for lab tests and examination are not necessarily limited to the questions
in the manual, but may involve some variations and/or combinations of the
questions.

•

A sample note preparation is given as a model for observation.

THE STUDENTS SHOULD NOT
•

Bring mobile phones or any other electronic gadgets to the lab.

•

Go out of the lab without permission.

vii

Sample Lab Observation Note Preparation
LAB NO.:

Date:
Title: STRUCTURED QUERY LANGUAGE

1.

Implement the Bank Database and execute the given queries/updates

Bank Database Schema:

•

•

ACCOUNT(ACCOUNT_NUMBER, BRANCH_NAME, BALANCE)

•

BRANCH(BRANCH_NAME, BRANCH_CITY, ASSETS)

•

CUSTOMER(CUSTOMER_NAME CUSTOMER_STREET,
CUSTOMER_CITY)

•

LOAN(LOAN_NUMBER, BRANCH_NAME, AMOUNT)

•

DEPOSITOR(CUSTOMER_NAME, ACCOUNT_NUMBER)

•

BORROWER(CUSTOMER_NAME, LOAN_NUMBER)
Creating Tables
CREATE TABLE BRANCH
(BRANCH_NAME VARCHAR (15) PRIMARY KEY,
BRANCH_CITY VARCHAR (20),
ASSETS NUMBER (10));
CREATE TABLE ACCOUNT
(ACCOUNT_NUMBER NUMBER (10) PRIMARY KEY,
BRANCH_NAME VARCHAR (15) REFERENCES BRANCH,
BALANCE NUMBER (8));
CREATE TABLE CUSTOMER
(CUSTOMER_NAME VARCHAR (20) PRIMARY KEY,
CUSTOMER_STREETVARCHAR (15),
CUSTOMER_CITY VARCHAR (10));
CREATE TABLE LOAN
(LOAN_NUMBER NUMBER (10) PRIMARY KEY,
viii

BRANCH_NAME VARCHAR (15) REFERENCES BRANCH,
AMOUNT NUMBER (10))
CREATE TABLE DEPOSITOR
(CUSTOMER_NAME VARCHAR (2) REFERENCES CUSTOMER,
ACCOUNT_NUMBER NUMBER (10) REFERENCES ACCOUNT,
PRIMARY KEY (CUSTOMER_NAME, ACCOUNT_NUMBER));
CREATE TABLE BORROWER
(CUSTOMER_NAME VARCHAR(2) REFERENCES CUSTOMER,
LOAN_NUMBER NUMBER(10) REFERENCES LOAN,
PRIMARY KEY(CUSTOMER_NAME,LOAN_NUMBER));

Queries/Update on Bank Database
(Questions followed by SQL statements)
Retrieving records from a table:
1. List all branch names and their assets
SELECT BRANCH_NAME, ASSETS FROM BRANCH;
2. List all accounts of Brooklyn branch
SELECT * FROM ACCOUNT WHERE BRANCH_NAME= ‘BROOKLYN’;
3. List all loans with amount > 1000.
SELECT * FROM LOAN WHERE AMOUNT>1000;
Updating records from a table:
4. Change the assets of Perryridge branch to 340000000.
UPDATE BRANCH SET ASSETS=340000000
WHERE BRANCH_NAME=‘Perryridge’;

ix

x

LAB NO.: 1

Date:

MS ACCESS
Objectives:
In this lab, student will be able to do:
•

Basic features of MS Access, including tables, queries, forms, and reports.

Creating a table in a new database:
1.

Click the Microsoft Office Button, and then click New.

2.

In the File Name box, type a file name for the new database.

3.

To browse to a different location to save the database, click the folder icon.

4.

Click Create.

The new database opens, and a new table named Table 1 is created and opens in
Datasheet view.

Start the Form Wizard:
1.

On the Create tab, in the Forms group, click More Forms, and then click Form
Wizard.

2.

Follow the directions on the pages of the Form Wizard.
NOTE: If you want to include fields from multiple tables and queries on the form,
do not click Next or Finish after you select the fields from the first table or query
on the first page of the Form Wizard. Instead, repeat the steps to select a table or
query, and then click any additional fields that you want to include on the form.
Then click Next or Finish to continue.

3.

On the last page of the wizard, click Finish.
The Form Wizard can create a variety of results depending on the options that you
select. As a result, we recommend that you run the wizard several times,
experimenting with different options each time, until you get the results that you
want.

1

Create a table relationship by using the Relationships window
1.

On the File tab, click Open.

2.

In the Open dialog box, select and open the database.

3.

On the Database Tools tab, in the Relationships group, click Relationships.

4.

If you have not yet defined any relationships, the Show Table dialog box
automatically appears. If it does not appear, on the Design tab, in the
Relationships group, click Show Table.

Filter by Selection
1. Start Microsoft Access, and then open the database that you are working with.
2. In a field on a form, a sub-form, a datasheet, or a sub-datasheet, select one instance
of the value that you want to filter by (for example, a name or a number).
3. On the Records menu, point to Filter, and then click Filter by Selection.
4. Repeat steps 2 and 3 until you have the set of records that you are looking for.
NOTE: You can also filter for records that do not have a certain value. After you select
a value, right-click, and then click Filter Excluding Selection.
Filter by Form
1. Open a form in form view, or a table, a query, or a form in Datasheet view. To filter
records in a sub-datasheet, display the sub-datasheet by clicking its expand
indicator.
2. On the Records menu, point to Filter, and then click Filter by Form to switch to the
Filter by Form window.
3. You can specify criteria for the form, the subform, the main datasheet, or any subdatasheet that is displayed.
NOTE: If you don’t like the results you get with the Report tool, you can use Layout
view to build a report manually, a process that gives you more control. You drag fields
from a list, arrange them to your liking, and group them.
The Report Wizard is a fast way to create a report with a large number of fields and a
complex layout.
Click the Create tab, and in the Reports group, click Report Wizard.
2

Complete the wizard. As part of that, you select a data source, and then... Select the
fields you want to use, grouping options, a style, and more.
Lab Exercises:
1. Create four tables for the VideoParlour database using Design view. The tables are
Member to hold members details, Video to hold details of videos, VideoForRent to
hold the details of copies of videos for rent, and Rental Agreement to hold the
details of video rentals by members.
The Member table has the following fields (with the data type of each in brackets):
memberNo (AutoNumber), Fname (Text), Lname (Text), gender (Text), DOB
(Date/Time), address (Text), dateJoined (Date/Time), comments (Memo)
The primary key is memberNo.
(Also for this table, set the format property of the gender field to a field size of 1.
Also, set this field with a Validation Rule =“M” or “F” and Validation Text Please
enter M or F. If you do not understand the purpose of the properties associated with
each field, Use the help facility using the F1 key).
The Video table has the following fields (with the data type of each in brackets):
catalogNo (Text), title (Text), category (Text), dailyRental (Currency), price
(Currency), directorNo (Text).
The primary key is catalogNo
The VideoForRent table has the following fields (with the data type of each in
brackets): videoNo (Text), available (Yes/No), catalogNo (Text)
The primary key is videoNo
The RentalAgreement table has the following fields (with the data type of each in
brackets): rentalNo (AutoNumber), dateOut (Date/Time), dateReturn (Date/Time),
memberNo (Number), videoNo(Text)
The primary key is rentalNo
(Also for this table, set the format property for the dateOut and dateReturn fields to
Medium Date format e.g. 10-Oct-00.)

3

2. Open your VideoParlour database. Create a form for your Video table using the
Form Wizard facility and name this form VideoForm1. Use the form to view
records in your Video table. Practice, changing between viewing your Video table
using Form view and Datasheet view.
3. Create relationships between your Member, RentalAgreement, VideoForRent,
Video tables using the Relationship window.
4. Apply filters to the members and video records. For example, create the following
filters to view:
• Only male members of the video shop.
• Only male members of the shop who joined the shop this year in order of last
name and then first name.
• All members born in the 1960s.
• Only videos in the Children category with a daily rental rate of less than 40.00
and sorted according to video title.
• Only videos currently available for rent with a certification of “PG” or “U”.
• Only videos by a certain director.
5. Using the Select Query window, select your Member, RentalAgreement,
VideoForRent, Video tables. Practice joining and deleting the join lines between
your tables. Examine the join properties of the join lines relating your tables.
6. Create a report for your Video table containing the catalogNo, title, category and
certificate fields. Group your records according to the values in the category field
and then sort on the values in the title field.
• Create a report for your Video table containing the category, dailyRental and
price fields. Group your records according to the values in the category field and
then sum the values in the dailyRental and price fields.
• Create a report based on a query that contains the following fields.
memberNo, Fname, Lname, videoNo, title, dateOut and dateReturn. Group your
records according to memberNo and then order by videoNo.
7. Using Access SQL, create simple select queries on the tables of your StayHome
database. For example, create and save the following queries on the Video table.
• List the catalogNo, title and category of the Video table, ordered by video title.
4

• List title, certificate, category and dailyRental of the Video table for videos in
the “Childrens” category with a rental rate less than £4.00.
• List all videos with a certification of “PG” or “18” in the Video table.
Additional Exercise:
1.

Modify the field properties of the member table so that the Memberno, FName, and
LName are required fields. FName and LName should not allow zero length
entries. Set the default value for Address to “Manipal”. Set the default value for
DateJoined to today’s date (enter Date ()).

2.

Set validation rules (and respective validation texts) for the DOB and DateJoined
fields so that they are always before or equal to today’s date.

3.

Set a validation rule and text for the DailyRental field so that it is empty or greater
than zero.

4.

Write a query in SQL that displays a video titlealong with video price and its
category.

5

LAB NO.: 2

Date:

INTRODUCTION TO SQL
Objectives:
In this lab, student will be able to:
• Understand the working of DDL/DML commands.
CREATION OF TABLE:
SYNTAX:
create table(column1 datatype,column2 datatype...);
EXAMPLE:
SQL>create table STUDENT (sno number (5),sname varchar(20),age number(5),sdob
date,sm1 number (4,2),sm2 number(4,2),sm3 number(4,4));
Table created.
SQL>insert into std values (101, ‘AAA’,16, ‘03-jul-88’,80,90,98);
DDL COMMANDS:
1. The Create Table Command: It defines each column of the table uniquely. Each
column has minimum of three attributes, a name, data type and size.
Syntax:
Create table  ((),
));
Ex.: create table emp(empno number(4) primary key, ename char(10));
2. Modifying the structure of tables
a) Add new columns
Syntax:
Alter table add (datatype(size));
Ex.:
alter table emp add(sal number(7,2));
3. Dropping a column from a table
Syntax:
Alter table  drop column ;
Alter table emp drop column sal;
6

4. Modifying existing columns
Syntax: Alter table modify (());
Ex.: Alter table emp modify (enamevarchar2 (15));
5. Renaming the tables
Syntax:
Rename  to ;
Ex.:
Rename emp to emp1;
6. Truncate the table
Syntax:
Trunc table ;
Ex.:
Trunc table emp1;
7. Delete the table structure
Syntax:
Drop table ;
Ex.:
drop table emp;

DML commands (ADDITIONAL EXAMPLES):
1. Inserting Data into Tables: Once a table is created the most natural thing to do is
load this table with data to be manipulated later.
Syntax:
Insert into  (,) values (,);
2. Delete Operations
a) Remove all rows
Syntax:
Delete from ;

7

b) Removal of specified row/s
Syntax:
Delete from  where ;
3. Updating the contents of a table
a) Updating all rows
Syntax:
Update  set =, =;
b) Updating selected records
Syntax:
Update  set =,=where ;
LAB EXERCISE:
Implement the University Database and execute the given queries/updates
University Database Schema:
Student (ID, name,dept-name, tot-cred)
Instructor (ID, name, dept-name, salary)
Course (Course-id, title,dept-name, credits)
Takes (ID, course-id, sec-id, semester, year, grade)
Classroom (building, room-number, capacity)
Department (dept-name, building, budget)
Section (course-id, section-id, semester, year, building, room-number, time-slot-id)
Teaches (id, course-id, section-id, semester, year)
Advisor (s-id, i-id)
Time-slot (time-slot-id, day, start-time, end-time)
Prereq (course-id, prereq-id)
Queries/Updations on University Database
Retrieving records from a table:
1.
2.
3.
4.

List all Students with names and their department names.
List all instructors in CSE department.
Find the names of courses in CSE department which have 3 credits.
For the student with ID 12345 (or any other value), show all course-id and title of
all courses registered for by the student.
5. List all the instructors whose salary is in between 40000 and 90000.
8

Updating records from a table:
6. Change the advisor id of students with id between 100 and 200 to 1001.
7. Transfer all the instructors of IT department to CSE department.
8. Raise the salary of all the instructors by 5%.
Deleting records from a table:
9. Delete the IT department.
10. Waive off perquisite for the course with course id 101.
11. Delete all courses that have never been offered.
Modifying the structure of tables:
12. Add a column date-of-birth to student table.
13. Change the size of the dept-name to varchar (20).
14. Drop the column date-of-birth from student table.
Retrieving records from multiple tables
15. Display the IDs of all instructors who have never taught a course.
16. Find the student names, course names, and the year, for all students those who
have attended classes in room-number 303.
Rename and Tuple Variables (Use as in select and from)
17. For all students who have opted courses in 2015, find their names and course id’s
with the attribute course title replaced by c-name.
18. Find the names of all instructors whose salary is greater than the salary of at least
one instructor of CSE department and salary replaced by inst-salary.
String Operations (Use %, _, LIKE)
19. Find the names of all instructors whose department name includes the substring
‘ch’.
Ordering the display of Tuples (Use ORDER BY ASC DESC)
20. List all the courses with prerequisite courses, then display course id in increasing
order, course name in decreasing order and the prerequisite course names
increasing order.

9

Additional Exercise:
1.

Find the names of all departments with instructor, and remove duplicates.

2.

For all instructors who have taught some course, find their names and the course
ID of the courses they taught.

3.

Find all the instructors with the courses they taught.

4.

List all the students with student name, department name, advisor name and the
number of courses registered.

10

LAB NO.: 3

Date:

INTERMEDIATE SQL
Objectives:
In this lab, student will be able to:
• Understand the set operations and intermediate level queries.
SET Operations in SQL:
Multiple queries using the set operators UNION, UNION ALL, INTERSECT,
and MINUS. All set operators have equal precedence. If a SQL statement contains
multiple set operators, then Oracle Database evaluates them from the left to right unless
parentheses explicitly specify another order.
The corresponding expressions in the select lists of the component queries of a
compound query must match in number and must be in the same data type group (such
as numeric or character).
The UNION operator returns only distinct rows that appear in either result.
SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;
The following statement combines the results with the INTERSECT operator, which
returns only those rows returned by both queries:
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;
The following statement combines results with the MINUS operator, which returns only
unique rows returned by the first query but not by the second:
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

11

Aggregate Functions
Aggregate functions return a single result row based on groups of rows, rather than on
single rows. Aggregate functions can appear in select lists and HAVING clauses. They
are commonly used with the GROUP BY clause in a SELECT statement, where Oracle
Database divides the rows of a queried table or view into groups. In a query containing
a GROUP BY clause, the elements of the select list can be aggregate functions,
GROUP BY expressions, constants, or expressions involving one of these. Oracle
applies the aggregate functions to each group of rows and returns a single result row for
each group.
WITH Clause
The WITH clause may be processed as an inline view or resolved as a temporary table.
The advantage of the latter is that repeated references to the subquery may be more
efficient as the data is easily retrieved from the temporary table, rather than being
re-queried by each reference. The syntax of the WITH clause is as follows.
Syntax:
WITH  AS (
SELECT 
FROM 

GROUP BY )
SELECT  from 

CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement. A view
contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.

12

SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
LAB EXERCISE:
Implement the following Queries on UNIVERSITY Database:
Set Operations
UNION (Use union all to retain duplicates):
21. Find courses that ran in Fall 2009 or in Spring 2010
INTERSECT (Use intersect all to retain duplicates):
22. Find courses that ran in Fall 2009 and in spring 2010
EXCEPT(Minus):
23. Find courses that ran in Fall 2009 but not in Spring 2010
Aggregate Functions (avg, min, max, sum, count) / Group By
24. Find the average salary of instructors in the CSE department.
25. Find the total number of instructors who teach a course in the spring 2010 semester.
26. Find the number of students in each course.
27. Find those departments where the average number of students are greater than 10.
28. Find the total number of courses in each department.
29. Find the names and average salaries of all departments whose average salary is
greater than 42000.
Null values
30. Find the name of the course for which none of the students registered.
Nested Subqueries
Set Membership (in/not in):
31. Find courses offered in Fall 2009 and in Spring 2010.

13

32. Find the total number of students who have taken course taught by the instructor
with ID 10101.
33. Find courses offered in Fall 2009 but not in Spring 2010.
Set Comparison (>some/all)
34. Find names of instructors with salary greater than that of some (at least one)
instructor in the Biology department.
35. Find the names of all instructors whose salary is greater than the salary of all
instructors in the Biology department.
Test for Empty Relations (exists/not exists)
36. Find all courses taught in both the Fall 2009 semester and in the Spring 2010
semester.
37. Find all students who have taken all courses offered in the Biology department.
Test for Absence of Duplicate Tuples
38. Find all courses that were offered at most once in 2009.
39. Find all the students who have opted at least two courses offered by CSE
department.
Complex Queries
Derived Relations
40. Find the maximum total salary across the departments.
41. Find the average instructors’ salaries of those departments where the average salary
is greater than $42,000.
With Clause
42. Find all departments with the maximum budget.
43. Find all departments where the total salary is greater than the average of the total
salary at all departments.
Views
44. Create a view all_courses consisting of course sections offered by Physics
department in the Fall 2009, with the building and room number of each section.
45. Select all the courses from all_courses view.
14

46. Create a view department_total_salary consisting of department name and total
salary of that department.
Modification of the Database
(Use ROLLBACK (and SAVEPOINT) to undo the effect of any modification on
database before COMMIT)
47.
48.
49.
50.

Delete all the instructors of Finance department.
Delete all courses in CSE department.
Transfer all the students from CSE department to IT department.
Increase salaries of instructors whose salary is over $100,000 by 3%, and all others
receive a 5% raise.
51. Add all instructors to the student relation with tot_creds set to 0.
52. Delete all instructors whose salary is less than the average salary of instructors.
Additional Exercise:
1.

List all the courses for which more than three students registered, retrieve the
course number, the course name, and the number of students registered.

2.

For each department that has more than two instructors, retrieve the department
number and the number of its instructors who are making more than $40,000.

3.

Find the sum of the salaries of all instructors of the ‘CSE’ department, as well as
the maximum salary, the minimum salary, and the average salary in this
department.

4.

Retrieve the name of each student who registered for all the subjects offered by
‘CSE’ department.

15

LAB NO.: 4

Date:

INTEGRITY CONSTRAINTS IN SQL
Objectives:
In this lab, student will be able to:
• Understand the use of integrity constraints.
Integrity Constraints:
Constraints are the rules enforced on data columns on table. These are used to limit the
type of data that can go into a table. This ensures the accuracy and reliability of the
data in the database. Constraints could be column level or table level. Column level
constraints are applied only to one column, whereas table level constraints are applied
to the whole table.
Following are commonly used constraints available in SQL.
•

NOT NULL Constraint: Ensures that a column cannot have NULL value.

•

DEFAULT Constraint: Provides a default value for a column when none is
specified.

•

UNIQUE Constraint: Ensures that all values in a column are different.

•

PRIMARY Key: Uniquely identifies each row/record in a database table.

•

FOREIGN Key: Uniquely identifies row/record in another database table.

•

CHECK Constraint: The CHECK constraint ensures that all values in a column
satisfy certain conditions.

Constraints can be specified when a table is created with the CREATE TABLE
statement or you can use ALTER TABLE statement to create constraints even after the
table is created.
Dropping Constraints:
Any constraint that you have defined can be dropped using the ALTER TABLE
command with the DROP CONSTRAINT option.

16

For example, to drop the primary key constraint in the EMPLOYEES table, you can use
the following command:
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
Integrity Constraints Syntax:
•

Ensure the uniqueness of the primary key(PRIMARY KEY)
Æ column_name data_type primary key
Æ Primary key(column_name(s))

•

Ensure the uniqueness of the candidate key which is not the primary key
Æ column_name data_type unique

•

Ensure that child records in related tables have a parent record.
Æ foreign key(column_name) references table_name(column_name)

•

Delete child records when the parent record is deleted.
Æ foreign key(column_name) references table_name(column_name) on delete
cascade.

•

Ensure that columns always contain a value.
Æ column_name data_type not null

•

Ensure that a column contains a value within a set/specific range.
Æ check (column_name in (value1, value2,..))
Æ check (predicate)

•

Ensure that a default value is placed in a column.
Æ column_name data_type default (value)

Naming Constraints:
• Constraints can have unique user defined name as given below:
CONSTRAINT  
e.g. constraint account_pk primary key(account_number)
• Modifying Constraints:
ALTER TABLE 
ADD/MODIFY/DROP/DISABLE/ENABLE/VALIDATE/NOVALIDATE
CONSTRAINT 
17

Built-in Functions:
LENGTH (string)
The Oracle/PLSQL LENGTH function returns the length of the specified string.
Syntax:
The syntax for the LENGTH function in Oracle/PLSQL is:
LENGTH(string1)

LOWER (string)
The Oracle/PLSQL LOWER function converts all letters in the specified string to
lowercase. If there are characters in the string that are not letters, they are unaffected by
this function.
Syntax:
• The syntax for the LOWER function in Oracle/PLSQL is:
• LOWER(string)
SUBSTR (string, start, count)
The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.
Syntax:
• The syntax for the SUBSTR function in Oracle/PLSQL is:
•

SUBSTR(string, start_position [, length])

UPPER (string)
The Oracle/PLSQL UPPER function converts all letters in the specified string to
uppercase. If there are characters in the string that are not letters, they are unaffected by
this function.
Syntax:
• The syntax for the UPPER function in Oracle/PLSQL is:
• UPPER (string)

18

NVL (column name, substitute value)
The Oracle/PLSQL NVL function lets you substitute a value when a null value is
encountered.
Syntax:
The syntax for the NVL function in Oracle/PLSQL is:
NVL (string, replace_with)
ROUND (value, precision)
The Oracle/PLSQL ROUND function returns a number rounded to a certain number of
decimal places.
Syntax:
The syntax for the ROUND function in Oracle/PLSQL is:
ROUND (number [, decimal places])
TO_CHAR (date1, format)
The Oracle/PLSQL TO_CHAR function converts a number or date to a string.
Syntax:
• The syntax for the TO_CHAR function in Oracle/PLSQL is:
• TO_CHAR( value [, format mask] [, nls_language] )
LAST_DAY (date)
The Oracle/PLSQL LAST_DAY function returns the last day of the month based on a
date value.
Syntax:
• The syntax for the LAST_DAY function in Oracle/PLSQL is:
• LAST_DAY(date)
MONTHS_BETWEEN (date1, date2)
The Oracle/PLSQL MONTHS_BETWEEN function returns the number of months
between date1 and date2.

19

Syntax:
The syntax for the MONTHS_BETWEEN function in Oracle/PLSQL is:
MONTHS_BETWEEN (date1, date2)
NEXT_DAY (date1, ‘day’)
The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than
a date.
Syntax:
The syntax for the NEXT_DAY function in Oracle/PLSQL is:
NEXT_DAY (date, weekday)
TO_DATE (string, ‘format’)
The Oracle/PLSQL TO_DATE function converts a string to a date.
Syntax:
The syntax for the TO_DATE function in Oracle/PLSQL is:
TO_DATE( string1 [, format_mask] [, nls_language] )
e.g. to_date (‘12021998’, ‘DDMMYYYY’)

LAB EXERCISES:
Consider the following schema:
Employee (EmpNo., EmpName, Sex, Salary, Address, DNo.)
Department (DeptNo., DeptName, Location)
1. Create Employee table with following constraints:
• Make EmpNo. as Primary key.
• Do not allow EmpName, Sex, Salary and Address to have null values.
• Allow Sex to have one of the two values: ‘M’, ‘F’.
• Set the default salary value to Rs. 1000.
2. Create Department table with following:
• Make DeptNo. as Primary key.
• Make DeptName as candidate key.
20

3. Make DNo. of Employee as foreign key which refers to DeptNo. of Department.
4. Insert few tuples into Employee and Department which satisfies the above
constraints.
5. Insert few tuples into Employee and Department which violates some of the above
constraints.
6. Modify/Delete a tuple which violates a constraint.
(e.g. drop a department tuple which has one or more employees)
7. Modify the foreign key constraint of Employee table such that whenever a
department tuple is deleted, the employees belonging to that department will also
be deleted.
Naming Constraints:
8. Repeat some of the examples (1 to 7) with named constraints.
9. Try some more examples with ADD, DROP, DISABLE, ENABLE, VALIDATE
INVALIDATE and NOVALIDATE.
Built-in Functions:
(Use University database for the exercise problems given below)
10. List the student names along with the length of the student names.
11. List the instructor names in lower case.
12. List the department names and 3 characters from 3rd position of each department
name.
13. List the instructor names in upper case.
14. Replace NULL with value1(say 0) for a column in any of the table.
15. List the balance and balance/3 rounded to nearest hundred from account.
(Add data of birth column DOB to Employee Table. Insert appropriate DOB values
for different employees and try the exercise problems given below)
16. Display the birth date of all the employees in the following format:
• ‘DD-MON-YYYY’
• ‘DD-MON-YY’
• ‘DD-MM-YY’
21

17. List the employee names and the year(fully spelled out) in which they are born
• ‘YEAR’
• ‘Year’
• ‘year’
18. List the employee names and the day of the week (fully spelled out) in which they
are born
• ‘DAY’
• ‘Day’
19. List the employee names and the month(fully spelled out) in which they are born
• ‘MONTH’
• ‘Month’
20. Find the last day of the month(and its day of the week) in which employee Mr. X is
born
21. Find the age of all the employees
22. Find the Saturday following the Employee’s 60th birthday
23. List the employees whose birthday falls in the given year X
24. List the employees whose birthday fall between the given years X and Y
25. List the employees who will retire on the given year X.
[Hint: use & with the variable name (e.g. & X) in the SQL query to read the value
from the user]
Additional Exercise:
1. Modify the employee table to check the salary of every employee to be greater than
$5000.
2. Find the quarter of year from the given date.
3. Convert seconds to hours, minutes and seconds format.
4. Find the week of the year from the given date.

22

LAB NO.: 5

Date:

ADDITIONAL EXERCISES ON SQL
Objectives:
In this lab, student will be able to:
•
•

Understand how to convert ER Diagram into SQL Tables.
Design the database according to customer requirements.

LAB EXERCISES:
Design the database for the following ER Diagram

23

Implement the following queries:
1. Retrieve the birth date and address of the employee(s) whose name is ‘John B.
Smith’.
2. Retrieve the name and address of all employees who work for the ‘Research’
department.
3. For every project located in ‘Stanford’, list the project number, the controlling
department number, and the department manager’s last name, address, and birth
date.
4. Find all distinct salaries of employees.
5. For each employee, retrieve the employee’s first and last name and the first and last
name of his or her immediate supervisor.
6. Make a list of all project numbers for projects that involve an employee whose last
name is ‘Smith’, either as a worker or as a manager of the department that controls
the project.
7. Retrieve all employees who reside in Houston, Texas.
8. Show the resulting salaries if every employee working on the ‘ProductX’ project is
given a 10 percent raise.
9. Retrieve all employees in department 5 whose salary is between $30,000 and
$40,000.
10. Retrieve a list of employees and the projects they are working on, ordered by
department and, within each department, ordered alphabetically by last name, then
first name.
11. Retrieve the names of all employees who do not have supervisors.
12. Retrieve the name of each employee who has a dependent with the same first name
and is the same sex as the employee.
13. Retrieve the names of employees who have no dependents.
14. List the names of managers who have at least one dependent.
15. Retrieve the Social Security numbers of all employees who work on project
numbers 1, 2, or 3.
16. Find the sum of the salaries of all employees, the maximum salary, the minimum
salary, and the average salary.
24

17. Find the sum of the salaries of all employees of the ‘Research’ department, as well
as the maximum salary, the minimum salary, and the average salary in this
department.
18. For each project, retrieve the project number, the project name, and the number of
employees who work on that project.
19. For each project on which more than two employees work, retrieve the project
number, the project name, and the number of employees who work on the project.
20. For each department that has more than five employees, retrieve the department
number and the number of its employees who are making more than $40,000.
Additional Exercise:
1. Find the names of employees who work on all the projects controlled by department
number 5.
2. Find the names of all employees who have a higher salary than some instructor in
‘Research’ department.
3. Find the total number of (distinct) employees who have worked on project
‘ProductX’.

25

LAB NO.: 6

Date:

PL/SQL BASICS
Objectives:
In this lab, student will be able to:
• Understand the need for PL/SQL and its syntax.
• Use basic PL/SQL data types.
• Use PL/SQL Conditional, Iterative and Sequential Control.
PL/SQL
PL/SQL is the Oracle procedural extension of SQL. It is a portable, high-performance
transaction-processing language.
Though SQL is the natural language of Oracle DB, it has some disadvantages when
used as a programming language:
1.

SQL does not have any procedural capabilities like condition checking, looping and
branching.

2.

SQL statements are passed on to the Oracle engine one at a time. This adds to the
network traffic in a multi-user environment and decreases data processing speed.

3.

SQL has no facility for programmed error handling.

Main Features of PL/SQL
1.

PL/SQL combines the data-manipulating power of SQL with the processing power
of procedural languages.

2.

PL/SQL allows users to declare constants and variables, control program flow,
define subprograms, and trap runtime errors.

3.

Complex problems can be broken into easily understandable subprograms, which
can be reused in multiple applications.

4.

PL/SQL sends the entire block of SQL to the Oracle engine in one go, reducing
network traffic and leading to faster query processing.
26

5.

Variables in PL/SQL blocks can be used to store intermediate result of a query for
later processing.

Generic PL/SQL Block
The basic unit of a PL/SQL source program is the block, which groups related
declarations and statements. The syntax for which is shown in Figure 7.1

COMPONENTS OF A PL/SQL BLOCK
1. Declare Section
Code block starts with a declaration section, in which memory variables and other
Oracle objects can be declared and if required initialized. This section is optional.
2. Begin Section
It consists of a set of SQL and PL/SQL statements. Data manipulation, retrieval,
looping and branching constructs are specified in this section. This is a required section.
3. Exception Section
This section deals with handling of errors that arise during execution of data
manipulation statements in the PL/SQL code block. Errors can arise due to syntax, logic
and/or validation rule violation. The exception section is optional.
4. End Section
This marks the end of PL/SQL block. This is a required section.

27

Example PL/SQL Program
declare
message varchar2(20):='Hello, World!';
begin
dbms_output.put_line(message);
end;
/

Note:
A slash ‘/’ should be added at last line after the end section.
‘Setserveroutput on’ command should be issued before executing the PL/SQL block.
Alternatively, it can be included in the beginning of every PL/SQL block.
SQL DATA TYPES
The default data types that can be declare in PL/SQL are
1. number
2. char
3. date
4. boolean
Null values are allowed for number, char and date but not boolean data type.
%TYPE
PL/SQL can use %Type to declare variables based on column definition in a table.
Hence, if a column’s attribute changes, the variable’s attribute will change as well.
Not Null
‘Not null’ causes creation of a variable or a constant that cannot be assigned ‘null’
value. Attempt to assign null value to such a variable or constant will return an internal
error.
VARIABLES
In PL/SQL a variable name must begin with a character with maximum length of 30.
Space not allowed in variable names. Reserve words cannot be used as variable names
unless enclosed within double quotes. Case is insignificant when declaring variables.
28

Value can be assigned to variable by:
1. Using the assignment operator :=(a colon followed by an ‘equal to’).
2. Selecting or fetching table data values into variables.
Constants can be declared with constant keyword.
E.g.: pi constant number := 3.141592654;
DISPLAYING USER MESSAGES ON SCREEN
dbms_output is a package that includes procedures and functions that accumulate
information in a buffer so that it can be retrieved later.
put_line puts a piece of information in the package buffer and can be used to display
information in the buffer. It expects a single parameter of character data type. To
display messages serveroutput should be set on.
COMMENTS
Comment can be of two forms, as:
1. The comment line begins with a double hypen (--). The entire line is considered as a
comment.
2. The comment line begins with a slash followed by an asterisk (/*) and ends with
asterisk followed by slash (*/). All lines within is considered as comments.
CONDITIONAL CONTROL: If-Then-ElsIf-Else-End If
Syntax:
IF < condition> THEN

ELSIF  THEN

ELSE

END IF;

Example:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
29

IF grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
DBMS_OUTPUT. PUT_LINE('Fair');
ELSIF grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;
END;
/

Output:
Very Good

ITERATIVE CONTROL: Simple Loop
Syntax:
LOOP

END LOOP;

Once a loop begins to execute, it will go on forever. A conditional statement to control
the number of times loop executes should accompany the simple loop construct.
Example:
DECLARE
x NUMBER := 0;
BEGIN
LOOP

30

DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' ||
TO_CHAR(x));
x := x + 1;
IF x > 3 THEN
EXIT;
END IF;
END LOOP;
-- After EXIT, control resumes here
DBMS_OUTPUT.PUT_LINE(' After loop: x = ' || TO_CHAR(x));
END;

Output:
Inside
Inside
Inside
Inside
After

loop:
loop:
loop:
loop:
loop:

x
x
x
x
x

=
=
=
=
=

0
1
2
3
4

ITERATIVE CONTROL: While Loop
Syntax:
WHILE 
LOOP

END LOOP;

If the condition is true, the statements run and control returns to the top of the loop,
where condition is evaluated again. If the condition is not true, control transfers to the
statement after the WHILE LOOP statement.
Example:
DECLARE
done BOOLEAN := FALSE;
BEGIN
WHILE done
31

LOOP
DBMS_OUTPUT.PUT_LINE ('This line does not
print.');
done := TRUE; -- This assignment is not made.
END LOOP
WHILE NOT done
LOOP
DBMS_OUTPUT.PUT_LINE ('Hello, world!');
done := TRUE;
END LOOP;
END;
/

Output:
Hello, world!

ITERATIVE CONTROL: For Loop
Syntax:
FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
statements
END LOOP;

Without REVERSE, the value of index starts at lower_bound and increases by one with
each iteration of the loop until it reaches upper_bound. If lower_bound is greater than
upper_bound, then the statements never run.
With REVERSE, the value of index starts at upper_bound and decreases by one with
each iteration of the loop until it reaches lower_bound. If upper_bound is less than
lower_bound, then the statements never run.

32

Example:
BEGIN
DBMS_OUTPUT.PUT_LINE ('lower_boundupper_bound');
FOR i IN 3..1 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/

Output:
lower_boundupper_bound

SEQUENTIAL CONTROL: GOTO Statement
The GOTO statement transfers control to a label unconditionally. The label must be
unique in its scope and must precede an executable statement or a PL/SQL block. When
run, the GOTO statement transfers control to the labeled statement or block.

33

Syntax:
GOTO Label
The code block is marked using tags
<