22IT480 - DBMS - LabManual UPDATED

22IT480-Lab Manual 2023_24
Thiagarajar College of Engineering, Madurai – 625015

Department of Information Technology
“Evolve into a centre of Excellence for Education and Research in Information Technology”
LAB MANUAL
Course Code : 22IT480
Course Name : Database Management Systems Lab
Programme : B.Tech. (IT)
Semester : IV
Faculty Incharge (s) : Dr.S.Sridevi ,Dr.K.V.Uma

Ms.S.Pudumalar , Dr.A.M.Abirami
Course Outcomes
On the successful completion of the course, students will be able to
COs Course Outcomes TCE Expected Expected

Proficiency Proficiency Attainment
Scale in % Level %
Design a database with integrity constraints and TPS3 80 90
CO1 appropriate normal forms for real-world
applications.
Implement Data Manipulation Languages for a TPS3 80 90
CO2
given application
Apply complex SQL queries for a given TPS3 80 90
CO3
application
Apply various composite data types to execute TPS3 80 90
CO4
PL/SQL block
Implement Procedures, Functions, Triggers, TPS3 80 90
CO5 Cursors, exceptions and
Packages for the given application.
Implement No SQL data model for a given TPS3 80 90
CO6
application
Mapping with Programme Outcomes and Programme Specific Outcomes

COs PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO3
CO1 S M L M M M M M M M M
CO2 S M L M M M M M M M M
CO3 S M L M S M M S M M M
CO4 S M L M S M M M M M M
CO5 S M L M S M M S M M M
CO6 S M L S S M M S M M M
S- Strong; M-Medium; L-Low
Cognitive Model Examination Terminal Examination

Levels
Remember
Understand 20 20
Apply 80 80
Analyse
Evaluate
Create
Index Sheet
Cos No.of
S.No List of Experiments
Hours
1. Identification of Mini Project and UI design CO1 2
2. Creation and Modification of relations CO1 2
Integrity constraint enforcement and simple SQL CO1 2
3.
queries
4. Creation and updation of views CO2 2
5. Complex SQL Queries -Date, String, Joins, Subquery CO3 2
6. Query tuning and Normalization CO1 2
PL/SQL block creation and usage of various CO4 2
7.
composite data types
Cursor management, Creation of Triggers and CO5 2
8.
Exceptions in SQL
9. Procedures, functions, and packages in PL/SQL CO5 2
10. Installation of MongoDb and creation of Collections CO6 2
11. Simple and Join Queries in MongoDb CO6
12. Perform MongoDb Query and Projection operation CO6 2
13. Demonstrate Mini-Project as Web application CO5 2
Total 24
Ex.No:1 IDENTIFICATION OF MINI PROJECT and UI Design
Aim:
To identify the application and to draw the Schema and ER diagram.
Description:
• The data which is stored in the database at a particular moment of time is called an
instance of the database.
• The overall design of a database is called schema.
• A database schema is the skeleton structure of the database. It represents the logical
view of the entire database
Procedure
• Identify the application based on your Area of Interest/SIH problems which you will
use throughout all the experiments.
• Identify the entities and its attributes
• Describe the major functionalities ( atleast 6 ) of your Application
• Draw the Schema diagram for the application
• Draw the ER diagram for the application with the relationship.
Schema Diagram Example:

ER Diagram Example:
UI DESIGN FOR COURSE REGISTATION SYSTEM:
( draw the consolidated UI Design / include the UI Design stage by stage)
Result:
Thus the application __________is identified and Schema and ER diagram were drawn.
Ex.No:2 CREATION AND MODIFICATION OF RELATIONS
Aim: To execute different Data Definition Language commands.
Description:
DDL
DDL is short name of Data Definition Language, which deals with database schemas and
descriptions, of how the data should reside in the database.
• CREATE – to create database and its objects like (table, index, views, store procedure,
function and triggers)
• ALTER – alters the structure of the existing database
• DROP – delete objects from the database
• TRUNCATE – remove all records from a table, including all spaces allocated for the records
are removed
• COMMENT – add comments to the data dictionary
• RENAME – rename an object
DML
DML is short name of Data Manipulation Language which deals with data manipulation, and
includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used
to store, modify, retrieve, delete and update data in database.
• SELECT – retrieve data from the a database

• INSERT – insert data into a table
• UPDATE – updates existing data within a table
• DELETE – Delete all records from a database table
• MERGE – UPSERT operation (insert or update)
• CALL – call a PL/SQL or Java subprogram
• EXPLAIN PLAN – interpretation of the data access path
• LOCK TABLE – concurrency Control
DCL
DCL is short name of Data Control Language which includes commands such as GRANT, and mostly
concerned with rights, permissions and other controls of the database system.
• GRANT – allow users access privileges to database

• REVOKE – withdraw users access privileges given by using the GRANT command
TCL
TCL is short name of Transaction Control Language which deals with transaction within a database.
• COMMIT – commits a Transaction

• ROLLBACK – rollback a transaction in case of any error occurs
• SAVEPOINT – to roll back the transaction making points within groups
• SET TRANSACTION – specify characteristics for the transaction
Procedure
Step no Details of the step
1 Create a table.
2 Execute different DDL commands such as Create, Alter, Drop and Truncate.
Query:
1. CREATE TABLE:
To create a table, you have to name that table and define its columns and datatype for each column.
1a) Create Table

Syntax:
1. CREATE TABLE table_name
2. (
3. column1 datatype ,
4. column2 datatype ,
5. ...
6. column_n datatype
7. );
Example
1. CREATE TABLE customers

2. (customer_id number(10) NOT NULL,
3. customer_name varchar2(50) NOT NULL,
4. city varchar2(50)
5. );
This table contains three columns
• customer_id: It is the first column created as a number datatype (maximum 10 digits in
length) and cannot contain null values.
• customer_name: it is the second column created as a varchar2 datatype (50 maximum
characters in length) and cannot contain null values.
• city: This is the third column created as a varchar2 datatype. It can contain null values.
1b) CREATE TABLE AS

The CREATE TABLE AS statement is used to create a table from an existing table by copying the
columns of existing table.
Syntax:
Example:
a)CREATE TABLE new_table AS (SELECT * FROM old_table);
b)CREATE TABLE newcustomers AS (SELECT * FROM customers WHERE customer_id < 5000);
1c) Create Table Example: Copying selected columns of another table
Syntax:
CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table);
Example:
CREATE TABLE newcustomers2 AS (SELECT customer_id, customer_name FROM customers

WHERE customer_id < 5000);
2. ALTER TABLE
ALTER TABLE statement specifies how to add, modify, drop or delete columns in a table. It is
also used to rename a table.
2a) Add column in a table
Syntax:
ALTER TABLE table_name ADD column_name column-definition;
Example:
Consider that already existing table customers. Now, add a new column customer_age into
the table customers.
ALTER TABLE customers ADD customer_age varchar2(50);
2b) Add multiple columns in the existing table
Syntax:
ALTER TABLE table_name ADD (column_1 column-definition, column_2 column-

definition,
... column_n column_definition);
Example
ALTER TABLE customers ADD (customer_type varchar2(50), customer_address

varchar2(50));
Now, two columns customer_type and customer_address will be added in the table
customers.
2c)Modify column of a table
Syntax:
ALTER TABLE table_name MODIFY column_name column_type;
Example:
ALTER TABLE customers MODIFY customer_name varchar2(100) not null;
Now the column column_name in the customers table is modified to varchar2 (100) and
forced the column to not allow null values.
2d)Modify multiple columns of a table
Syntax:
ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type,
... column_n column_type);

Example:
ALTER TABLE customers MODIFY (customer_name varchar2(100) not null, city

varchar2(100));
This will modify both the customer_name and city columns in the table.
2e)Drop column of a table
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
ALTER TABLE customers DROP COLUMN customer_name;
This will drop the customer_name column from the table.
2f) Rename column of a table
Syntax:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
Example: ALTER TABLE customers RENAME COLUMN customer_name to cname;
This will rename the column customer_name into cname.
2g)Rename table
Syntax:
ALTER TABLE table_name RENAME TO new_table_name;
Example:
ALTER TABLE customers RENAME TO retailers;
This will rename the customer table into "retailers" table.
3) DROP TABLE Statement
3a) DROP TABLE statement is used to remove or delete a table from the Oracle database.
Syntax
DROP TABLE table_name;
Example
DROP TABLE customers;

This will drop the table named customers.
Drop table Emp cascade;
3b)DROP TABLE Example with PURGE parameter
DROP TABLE customers PURGE
This statement will drop the table called customers and issue a PURGE so that the space associated
with the customers table is released and the customers table is not placed in recycle bin. So, it is not
possible to recover that table if required.
4. TRUNCATE TABLE
TRUNCATE TABLE statement is used to remove all records from a table.
Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE customers;
Result Thus the Data Definition Language command was executed and verified successfully.
Ex.No:3 INTEGRITY CONSTRAINT ENFORCEMENT AND SIMPLE SQL QUERIES
Aim:
To create Primary key, Foreign key, Not null, Unique, Check constraints for the relations
and to execute simple SQL queries.
Description
A.KEYS
1. Primary key
It is the first key which is used to identify one and only one instance of an entity uniquely. An entity
can contain multiple keys as we saw in PERSON table. The key which is most suitable from those
lists become a primary key.
In the EMPLOYEE table, ID can be primary key since it is unique for each employee. In the
EMPLOYEE table, we can even select License_Number and Passport_Number as primary key since
they are also unique.
For each entity, selection of the primary key is based on requirement and developers.
SYNTAX:
Example:
CREATE TABLE Employee(

ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Example
ALTER TABLE Emplyee ADD PRIMARY KEY (ID);
2. Candidate key
o A candidate key is an attribute or set of an attribute which can uniquely identify a tuple.
o The remaining attributes except for primary key are considered as a candidate key. The
candidate keys are as strong as the primary key.
For example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the attributes
like SSN, Passport_Number, and License_Number, etc. are considered as a candidate key.
3. Super Key
Super key is a set of an attribute which can uniquely identify a tuple. Super key is a superset of a
candidate key.
For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME) the name of two
employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can
also be a key.
The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-NAME), etc.
4. Foreign key
o Foreign keys are the column of the table which is used to point to the primary key of
another table.
o In a company, every employee works in a specific department, and employee and
department are two different entities. So we can't store the information of the department
in the employee table. That's why we link these two tables through the primary key of one
table.
o We add the primary key of the DEPARTMENT table, Department_Id as a new attribute in the
EMPLOYEE table.
o Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.
"Persons" table:
PersonID FName LName Age
"Orders" table:
OrderID OrderNumber PersonID
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.
Example :
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int REFERENCES Persons(PersonID)
);
INSERT QUERIES
1) Insert Query
Oracle insert query is used to insert records into table.
Insert Statement
In Oracle, INSERT statement is used to add a single record or multiple records into the table.
Syntax: (Inserting a single record using the Values keyword):
INSERT INTO table (column1, column2, ... column_n ) VALUES (expression1, expression2, ...
expression_n );
Example:
insert into customers values(101,'rahul','delhi');
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (50, 'Flipkart');
Output:
1 row(s) inserted.
Insert Example: By SELECT statement
Syntax:
INSERT INTO table (column1, column2, ... column_n ) SELECT expression1, expression2, ...
expression_n FROM source_table WHERE conditions;
Parameters:
1) table: The table to insert the records into.

2) column1, column2, ... column_n: The columns in the table to insert values.
3) expression1, expression2, ... expression_n: The values to assign to the columns in the table. So
column1 would be assigned the value of expression1, column2 would be assigned the value of
expression2, and so on.
4) source_table: The source table when inserting data from another table.
5) conditions: The conditions that must be met for the records to be inserted.
Example:
In this method, we insert values to the "suppliers" table from "customers" table. Both tables are
already created with their respective columns.
INSERT INTO suppliers (supplier_id, supplier_name) SELECT age, address FROM customers WHERE
age > 20;
Output:
4 row(s) inserted.
Example
SELECT count(*) FROM customers WHERE age > 20;
Output:
Count(*)
4
B.SQL Constraints
SQL constraints are used to specify rules for the data in a table.
Constraints 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 table. If there is any violation between the constraint and the data
action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and table
level constraints apply to the whole table.
The following constraints are commonly used in SQL:
• NOT NULL - Ensures that a column cannot have a NULL value

• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in
a table
• FOREIGN KEY - Uniquely identifies a row/record in another table
• CHECK - Ensures that all values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column when no value is specified
• INDEX - Used to create and retrieve data from the database very quickly
Query:
1.NOT NULL Constraint
Syntax:
Create table tablename (columnname datatype() not null);
Example 1
CREATE TABLE Persons (

ID int NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
Example 2:
ALTER TABLE Persons MODIFY Age int NOT NULL;
2.UNIQUE CONSTRAINT
Syntax:
Create table tablename (columnname datatype() unique);
Example 1:

ID int NOT NULL UNIQUE,
Age int
);
Example 2 : (Using Alter Table)
ALTER TABLE Persons ADD UNIQUE (ID);
Example 3: (Multiple Columns)
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
Example 4 : (Drop Constraint)
ALTER TABLE Persons DROP CONSTRAINT UC_Person;
3.CHECK Constraint
Syntax
Create table tablename(columnname datatype() CHECK(condition));
Example 1 :

ID int NOT NULL,
Age int CHECK (Age>=18)
);
Example 2 : (Multiple Columns)

ID int NOT NULL,
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
Example 3:
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
4. DEFAULT CONSTRAINT
Example 1:

ID int NOT NULL,
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
Example 2:
ALTER TABLE Persons

MODIFY City DEFAULT 'Sandnes';
5.INDEX
Syntax:
CREATE INDEX index_name

ON table_name (column1, column2, ...);
Example:
CREATE INDEX idx_lastname

ON Persons (LastName);
SIMPLE QUERIES
1)Select Query
Oracle select query is used to fetch records from database.
Syntax:
SELECT expressions FROM tables WHERE conditions;
Example:
SELECT * from customers;
WHERE clause uses some conditional selection
= Equal
> greater than
< less than
>= greater than or equal
<= less than or equal
<> not equal to

2.SQL SELECT DISTINCT
The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique
data.
In a table, there may be a chance to exist a duplicate value and sometimes we want to retrieve only
unique values. In such scenarios, SQL SELECT DISTINCT statement is used.
Note: SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are same.
Let's see the syntax of select distinct statement.
SELECT DISTINCT column_name ,column_name FROM table_name;
Student_Name Gender Mobile_Number HOME_TOWN
Rahul Ojha Male 7503896532 Lucknow
Disha Rai Female 9270568893 Varanasi
Sonoo Jaiswal Male 9990449935 Lucknow
Here is a table of students from where we want to retrieve distinct information For example:
distinct home-town.
SELECT DISTINCT home_town FROM students
Now, it will return two rows.
HOME_TOWN
Lucknow
Varanasi
3.SQL SELECT COUNT
The SQL COUNT() function is used to return the number of rows in a query.
Syntax
SELECT COUNT (expression) FROM tables WHERE conditions;
Example: SQL SELECT COUNT(column_name)
SELECT COUNT(name) FROM employee_table;
It will return the total number of names of employee_table. But null fields will not be counted.
Example :SQL SELECT COUNT(*)
SELECT COUNT(*) FROM employee_table;
The "select count(*) from table" is used to return the number of records in table.
Example : SQL SELECT COUNT(DISTINCT column_name)
SELECT COUNT(DISTINCT name) FROM employee_table;
4.SQL SELECT TOP
The SQL SELECT TOP Statement is used to select top data from a table. The top clause specifies that
how many rows are returned.
Example:
SELECT TOP 2 * FROM employee
5.SQL SELECT FIRST
The SQL first() function is used to return the first value of the selected column.
Syntax:
SELECT FIRST(column_name) FROM table_name;
Example:
SELECT FIRST(customer_name) AS first_customer FROM customers;
6.SQL SELECT LAST
Example:
SELECT LAST (CUSTOMER_NAME) AS LAST_CUSTOMER FROM CUSTOMERS;
7) Update Query
Update query is used to update records of a table.
Syntax:
UPDATE table_name SET attribute_name = value WHERE condition;
Example:
update customers set name='bob', city='london' where id=101;
8) Delete Query
Oracle update query is used to delete records of a table from database.
Example:
delete from customers where id=101;
9. SQL AND
The SQL AND condition is used in SQL query to create two or more conditions to be met.
It is used in SQL SELECT, INSERT, UPDATE and DELETE statements.
Syntax:
SELECT columns FROM tables WHERE condition 1 AND condition 2;
Example:UPDATE suppliers
SET supplier_name = 'HP' WHERE supplier_name = 'IBM' AND offices = 8;
10.SQL OR
The SQL OR condition is used in a SQL query to create a SQL statement where records are
returned when any one of the condition met. It can be used in a SELECT statement, INSERT
statement, UPDATE statement or DELETE statement.
Syntax:
SELECT columns FROM tables WHERE condition 1 OR condition 2;
Example:
SELECT * FROM suppliers WHERE city = 'New York' OR available_products >= 250;
11.SQL ORDER BY Clause
The SQL ORDER BY clause is used for sorting data in ascending and descending order based on one
or more columns.
Some databases sort query results in ascending order by default.
Syntax:
SELECT expressions FROM tables WHERE conditions ORDER BY expression [ASC | DESC];
Example:
Let us take a CUSTOMERS table having the following records:
ID NAME AGE ADDRESS SALARY
1 Himani gupta 21 Modinagar 22000
2 Shiva tiwari 22 Bhopal 21000
3 Ajeet bhargav 45 Meerut 65000
4 Ritesh yadav 36 Azamgarh 26000
5 Balwant singh 45 Varanasi 36000
6 Mahesh sharma 26 Mathura 22000
Example:
SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;
This would produce the following result.
3 Ajeet bhargav 45 Meerut 65000
5 Balwant singh 45 Varanasi 36000
1 Himani gupta 21 Modinagar 22000
6 Mahesh sharma 26 Mathura 22000
4 Ritesh yadav 36 Azamgarh 26000
2 Shiva tiwari 22 Bhopal 21000

12.The SQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
Example
SELECT MIN(Price) AS SmallestPrice FROM Products;
MAX() Syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
Example
SELECT MAX(Price) AS LargestPrice FROM Products;
13. AVG() Syntax

SELECT AVG(column_name) FROM table_name WHERE condition;
Example:
SELECT AVG(Price) FROM Products;
14.SUM() Syntax
SELECT SUM(column_name) FROM table_name WHERE condition;
Example;
SELECT AVG(Price) FROM Products;
15. The SQL GROUP BY Statement
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.
GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)
ORDER BY column_name(s);
Example
SELECT COUNT(CustomerID), Country FROM Customers
GROUP BY Country;
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
16.The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with
aggregate functions.
HAVING Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)
HAVING condition ORDER BY column_name(s);
Example:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country

HAVING COUNT(CustomerID) > 5;
Result:
Thus Primary key, Foreign key, Not null, Unique, Check constraints were created for the
relations and simple SQL queries were executed.
Exp.No:4 CREATION AND UPDATION OF VIEWS
AIM:
To execute commands in creation and updating of views.
VIEW:
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.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the
data were coming from one single table.
A view always shows up-to-date data
QUERIES:
1. Create view
SQL> create table particulars(sys_no int, gametype varchar(50),amount float(30));
Table created.
SQL> insert into particulars(sys_no, gametype, amount)values('01','Racing','30');
1 row created.
SYNTAX:
CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name
WHERE condition;
EXAMPLE
SQL> create view particulars_view as select sys_no,gametype from particulars;
View created.
2. Insert in view
SQL> insert into particulars_view(sys_no, gametype)values('08', 'Racing');
1 row created.
3. Delete in view
SQL> delete from particulars_view where sys_no=03;
1 row deleted.
SQL> select *from particulars_view;
SYS_NO GAMETYPE
---------- --------------------------------------------------
1 Racing
2 Cooking games
4 VR games
5 VR games
6 Racing
4. Update view
SQL> update particulars_view set sys_no=03 where sys_no=02;
1 row updated.
5. Drop view
SQL> drop view particulars_view;
View dropped.
6. Order by command
SQL> select *from particulars_view order by sys_no;
SYS_NO GAMETYPE
---------- --------------------------------------------------
1 Racing
3 Cooking games
4 VR games
5 VR games
6 Racing
7. With read only constraint
SQL> create view part_view as select *from particulars with read only constraint rea
dcons;
View created.
8. Execute DML commands in view table and check whether it is reflected in main table.
SQL> insert into particulars_view(sys_no, gametype)values('08', 'Racing');
1 row created.
SYS_NO GAMETYPE
---------- --------------------------------------------------
1 Racing
3 Cooking games
4 VR games
5 VR games
6 Racing
8 Racing
6 rows selected.
SQL> select *from particulars;
SYS_NO GAMETYPE AMOUNT
---------- -------------------------------------------------- ----------
1 Racing 30
3 Cooking games 25
4 VR games 50
5 VR games 50
6 Racing 30
8 Racing
6 rows selected.
IT IS REFELECTED.
9. Execute DML commands in main table and check whether it is reflected in view table.
SQL> insert into particulars(sys_no, gametype, amount)values('12', 'Racing','30');
1 row created.
SQL> select *from particulars;
SYS_NO GAMETYPE AMOUNT
---------- -------------------------------------------------- ----------

1 Racing 30
3 Cooking games 25
4 VR games 50
5 VR games 50
6 Racing 30
8 Racing
10 Cooking games 25
12 Racing 30
8 rows selected.
SYS_NO GAMETYPE
---------- --------------------------------------------------
1 Racing
3 Cooking games
4 VR games
5 VR games
6 Racing
8 Racing
10 Cooking games
12 Racing
IT IS REFLECTED.
10. Do DML operations in view table with read only constraint and tell what happens.
SQL> insert into part_view(sys_no, gametype, amount)values('10', 'Cooking games', '25');
insert into part_view(sys_no, gametype, amount)values('10', 'Cooking games', '25')
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
RESULT:
Thus the commands on creation and updation of views were executed and verified
successfully.
Exp No: 5 COMPLEX SQL QUERIES – Date,Strings,Joins,Subquery
Aim:
To execute the given Queries and to perform different Join operation for the application chosen .
Description:
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between
them.
Different Types of SQL JOINs
• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from
the right table
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records
from the left table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
INNER JOIN:
Syntax:
SELECT column_name(s)FROM table1 INNER JOIN table2

ON table1.column_name = table2.column_name;
EXAMPLE:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records
from the right table (table2). The result is NULL from the right side, if there is no match.
Syntax:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON
table1.column_name = table2.column_name;
Example
SELECT Customers.CustomerName, Orders.OrderID FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records
from the left table (table1). The result is NULL from the left side, when there is no match.
Syntax
SELECT column_name(s) FROM table1 RIGHT JOIN table2
Example:
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders

RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
SQL FULL OUTER JOIN
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or
right (table2) table records.
SYNTAX
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2

EXAMPLE
SELECT Customers.CustomerName, Orders.OrderID FROM Customers

FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
SQL Self JOIN
A self-JOIN is a regular join, but the table is joined with itself.
Syntax
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
EXAMPLE
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City

FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City
ORDER BY A.City;
SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order
Syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
Example
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
UNION ALL
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION
ALL:
Syntax
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
EXAMPLE
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.Aliases are often used
to make column names more readable.An alias only exists for the duration of the query.
Alias Column Syntax

SELECT column_name AS alias_name FROM table_name;
Alias Table Syntax

SELECT column_name(s) FROM table_name AS alias_name;
QUERIES:
Customer Table:
customer_id cust_name city grade salesman_id

----------- ------------ ---------- ---------- -----------
3002 Nick Rimando New York 100 5001
3005 Graham Zusi California 200 5002
3001 Brad Guzan London 100 5005
3004 Fabian Johns Paris 300 5006
3007 Brad Davis New York 200 5001
3009 Geoff Camero Berlin 100 5003
3008 Julian Green London 300 5002
3003 Jozy Altidor Moncow 200 5007
Orders Table:
ord_no purch_amt ord_date customer_id salesman_id

---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001
Salesman Table:
salesman_id name city commission

----------- ---------- ---------- ----------
5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5003 Lauson Hen San Jose 0.12

5007 Paul Adam Rome 0.13
Boolean and Relational operators
1. Write a query to display all customers with a grade above 100.

2. Write a query statement to display all customers in New York who have a grade value above
100.
3. Write a SQL statement to display all customers, who are either belongs to the city New York
or had a grade above 100.
4. Write a SQL statement to display all the customers, who are either belongs to the city New
York or not had a grade above 100.
5. Write a SQL query to display those customers who are neither belongs to the city New York
nor grade value is more than 100.
6. Write a SQL statement to display either those orders which is not issued on date 2012-09-
10 and issued by the salesman whose ID is 505 and below or those orders which purchase
amount is 1000.00 and below.
7. Write a SQL statement to display salesman_id, name, city and commission who gets the
commission within the range more than 0.10% and less than 0.12%.
8. Write a SQL statement to display all information where purchase amount less than a
specified amount or reverse order date greater than or equal to a specified date and
customer id less than a specified number
9. Write a SQL query to display all orders where purchase amount less than a specified
amount or reverse orders in a specified date and customer ID less than a specified number.
10. Display all reverse orders where order dates equal to a specified date or customer id
greater than a specified number and purchase amount less than a specified amount
11. Write a SQL query to display order number, purchase amount, archived, unachieved
percentage for those order which exceeds the 50% of target value of 6000.
Aggregate Functions and Group by
Create a table employee with the following fields:
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE |

JOB_ID
1. Write a query to list the number of jobs available in the employees table.
2. Write a query to get the total salaries payable to employees.
3. Write a query to get the minimum salary from employees table
4. Write a query to get the maximum salary of an employee working as a Programmer.
5. Write a query to get the average salary and number of employees working the department 90.
6. Write a query to get the highest, lowest, sum, and average salary of all employees
7. Write a query to get the number of employees with the same job.
8. Write a query to get the difference between the highest and lowest salaries
9. Write a query to find the manager ID and the salary of the lowest-paid employee for that
manager.
10. Write a query to get the department ID and the total salary payable in each department
String functions:
1. Write a query to update the portion of the phone_number in the employees table, within the
phone number the substring '124' will be replaced by '999'.
2. Write a query to get the details of the employees where the length of the first name greater
than or equal to 8
3. Write a query to display leading zeros before maximum and minimum salary.
4. Write a query to append '@example.com' to email field.
5. Write a query to get the employee id, email id (discard the last three characters)
6. Write a query to extract the last 4 character of phone numbers.
7. Write a query to get the locations that have minimum street length
8. Write a query to display the length of first name for employees where last name contain
character 'c' after 2nd position.
9. Write a query that displays the first name and the length of the first name for all employees
whose name starts with the letters 'A', 'J' or 'M'. Give each column an appropriate label. Sort
the results by the employees' first names.
10. Write a query to display the first name and salary for all employees. Format the salary to be
10 characters long, left-padded with the $ symbol. Label the column SALARY.
Date functions :
1. Write a query to display the first day of the month (in datetime format) three months before
the current month.
2. Write a query to get the distinct Mondays from hire_date in employees tables.
3. Write a query to get the last day of the current year
4. Write a query to get the current date in the following format.
Sample date : 2014-09-04

Output : September 4, 2014
5. Write a query to get the current date in the following format.

Thursday September 2014
6. Write a query to get the first name and hire date from employees table where hire date
between '1987-06-01' and '1987-07-30'
Joins: Perform all the types of Join for your application.
Result:
Thus the given Queries were executed and Join operations were performed for the application
chosen
Ex. No: 6 QUERY TUNING and NORMALIZATION
Aim:
To execute a minimum of 15 different Query tuning commands along with reasoning.Also

,check and convert your application that it satisfies the maximum Normal form.
Procedure:
1) SELECT fields instead of using SELECT *

If a table has many fields and many rows, this taxes database resources by
querying a lot ofunnecessary data.
2) Use WHERE instead of HAVING to define filters
HAVING statements are calculated after WHERE statements. If the intent is to

filter a querybased on conditions, a WHERE statement is more efficient.
3) Select only required columns:
Avoid unnecessary details to have faster results
4) Use EXISTS, IN suitably in your query
o Usually IN has the slowest execution.

o IN is effective when a large portion is in the sub-query.
o EXISTS is useful.
5) Use EXISTS Instead of DISTINCT when using joins which includes tables
having the one-to-many relationship.
Distinct sorts the retrieved rows before suppressing the duplicate rows
6) Use UNION ALL instead of UNION.
Union all returns all the records retrieved by queries

7) Use proper conditions in WHERE statement
Using AND in where clause is preferable because it avoids data alias

8) Use DECODE to stay away from the checking of same columns or joining a
similar table monotonously. It can likewise be made used instead of GROUP BY
or ORDER BY statement.
It avoids joining the same table repeatedly
9) Use non-column expression on one side of the query
Because it will be processed earlier.

10) SQL optimization technique concerns the use of Exists()
11. Use a single case for each word
Follow the SQL standard rules for efficient query performance

12. Create views with only essential columns
View needn’t have extra information

13. Create joins with inner joins to avoid Cartesian problem:
Avoid cartesian product as it creates combination of each record of one table with
every recordof other
14. Use wildcards only at the end of the phrase
Using wildcards at the end improves efficiency as giving at front leads to prolonged search
15.Use BETWEEN instead of less than greater than
BETWEEN is efficient than the relational operators < >
NORMALIZATION:
Normalization is a database design technique that reduces data redundancy and eliminates
undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules
divides larger tables into smaller tables and links them using relationships. The purpose of
Normalisation in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
Database Normal Forms :
1. 1NF (First Normal Form):
2. 2NF (Second Normal Form)
3. 3NF (Third Normal Form)
4. BCNF (Boyce-Codd Normal Form)
5. 4NF (Fourth Normal Form)
6. 5NF (Fifth Normal Form)
Example:
First Normal Form:
A relation is in first normal form only if the relational table doesn’t contain any multivalued
attributes. (The relation contains only single-valued attributes).
Second Normal Form:
A relation is in second normal form if:

•It is in first normal form or 1NF
•It doesn’t contain any partial dependencies.
•It shouldn’t have any non-prime attribute which is functionally dependent on any proper subset of
the candidate key of the relation.
Third Normal Form :
The relation should be in 2NF

If A->B and B->C are two FDs then A->C is called transitive dependency
Result:
Thus, 15 Query tuning commands and normalization are executed with reasoning.
Ex.No: 7 PL/SQL BLOCK CREATION AND USAGE OF VARIOUS COMPOSITE DATA TYPES
Aim:
To practice with PL/SQL block creation and various composite data types
Description:
PL/SQL is a combination of SQL along with the procedural features of programming languages. It
was developed by Oracle Corporation to enhance the capabilities of SQL.
Syntax of a basic loop in PL/SQL programming language is:
LOOP
//Sequence of statements;//
END LOOP;
Procedure:
1. Execute any 5 sample PL/SQL programs

2. Execute any 5 PL/SQL program for your application
Example:
Adding two Numbers
PL/SQL program to add two numbers or integers and fetching the result into a third variable. This
program takes two inputs one for each variable and adds the result to a third variable and prints it.
Declare
Var1 integer;
Var2 integer;
Var3 integer;
Begin
Var1:=&var1;
Var2:=&var2;
Var3:=var1+var2;
Dbms_output.put_line(var3);
End;
/
PL/SQL Program to Find Factorial of a Number
declare
n number;
fac number:=1;
i number;
begin
n:=&n;
for i in 1..n
loop
fac:=fac*i;
end loop;
dbms_output.put_line('factorial='||fac);
end;
/
Sample Output
Enter value for n: 10

old 7: n:=&n;
new 7: n:=10;
factorial=3628800
PL/SQL Program for Armstrong Number
declare
n number:=407;
s number:=0;
r number;
len number;
m number;
begin
m:=n;
len:=length(to_char(n));
while n>0
loop
r:=mod(n,10);
s:=s+power(r,len);
n:=trunc(n/10);
end loop;
if m=s
then
dbms_output.put_line('armstrong number');
else
dbms_output.put_line('not armstrong number');
end if;
end;
/
Sample Output
armstrong number
PL/SQL Program to Check Number is Odd or Even
declare
n number:=&n;
begin
if mod(n,2)=0
then
dbms_output.put_line('number is even');
else
dbms_output.put_line('number is odd');
end if;
end;
Sample output:
Enter value for n: 7

old 2: n number:=&n;
new 2: n number:=7;
number is odd
Result
Thus the PL/SQL block has been created and executed successfully.
Ex.No: 8 Cursor management, Creation of Triggeres and Exceptions in SQL
Aim:
To practice and implement the concepts of cursor management in different ways so as to get a
complete knowledge about the implementation of cursors
Description:
Cursor:
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A
cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds
is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the rows
returned by the SQL statement, one at a time. There are two types of cursors −
• Implicit cursors
• Explicit cursors
Sample input:
Program to write a Cursor to list all the Administrators in the Database Table named as
Admin
Declare
CURSOR c1 IS
SELECT aname from Admin;
rec c1%rowtype;
begin
for rec in c1
loop
dbms_output.put_line(rec.aname);
end loop;
end;
Sample Output:
Update the table and increase the salary of each customer by 500 and use
the SQL%ROWCOUNT attribute to determine the number of rows affected
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
Sample Output:
6 customers selected
PL/SQL procedure successfully completed.
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An
explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a
SELECT Statement which returns more than one row.
The syntax for creating an explicit cursor is −
CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −
• Declaring the cursor for initializing the memory

• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the data
• Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For
example
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows
returned by the SQL statement into it. For example, we will open the above defined cursor as
follows
OPEN c_customers;
Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the
above-opened cursor as follows −
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close the above-
opened cursor as follows
CLOSE c_customers;
Example
Following is a complete example to illustrate the concepts of explicit cursors
DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
When the above code is executed at the SQL prompt, it produces the following result
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
Triggers:
Triggers are stored programs, which are automatically executed or fired when some events occur.
Triggers are, in fact, written to be executed in response to any of the following events −
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is associated.
Creating Triggers
The syntax for creating a trigger is −
CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Where,
• CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger
with the trigger_name.
• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The
INSTEAD OF clause is used for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
• [OF col_name] − This specifies the column name that will be updated.
• [ON table_name] − This specifies the name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for
various DML statements, such as INSERT, UPDATE, and DELETE.
• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for
each row being affected. Otherwise the trigger will execute just once when the SQL
statement is executed, which is called a table level trigger.
• WHEN (condition) − This provides a condition for rows for which the trigger would fire.
This clause is valid only for row-level triggers.
Example :
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |

• | 6 | Komal | 22 | MP | 4500.00 |
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Trigger created.
Exception:
An exception is an error condition during a program execution. PL/SQL supports programmers to
catch such conditions using EXCEPTION block in the program and an appropriate action is taken
against the error condition. There are two types of exceptions −
• System-defined exceptions
• User-defined exceptions
Syntax for Exception Handling
The general syntax for exception handling is as follows. Here you can list down as many exceptions
as you can handle. The default exception will be handled using WHEN others THEN −
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
........
WHEN others THEN
exception-handling-statements;
end;
DECLARE
c_id customers.id%type := 8;
c_name customerS.Name%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
No such customer!
Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal
database error, but exceptions can be raised explicitly by the programmer by using the
command RAISE. Following is the simple syntax for raising an exception −
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
You can use the above syntax in raising the Oracle standard exception or any user-defined
exception. In the next section, we will give you an example on raising a user-defined exception.
You can raise the Oracle standard exceptions in a similar way.
User-defined Exceptions
PL/SQL allows you to define your own exceptions according to the need of your program. A user-
defined exception must be declared and then raised explicitly, using either a RAISE statement or
the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is −

DECLARE
my-exception EXCEPTION;
Example
The following example illustrates the concept. This program asks for a customer ID, when the user
enters an invalid ID, the exception invalid_id is raised.
DECLARE
c_id customers.id%type := &cc_id;
c_name customerS.Name%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!
Result
Thus the implementation of cursor, triggers and exceptions have been done and verified
successfully
Ex No : 9 Procedures, Functions and packages in PL/SQL

Date :
Aim:
To execute the functions, procedures and Packages in SQL
Description
Functions:
A function is a named PL/SQL Block which is similar to a procedure. The major difference
between a procedure and a function is, a function must always return a value, but a procedure
may or may not return a value.
SYNTAX
CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
Sample Programs:
Factorial of a number:
SQL> DECLARE
2 num number;
3 fact number;
4 FUNCTION factorial(x number)
5 RETURN number
6 IS f number;
7 BEGIN
8 IF x=0 THEN
9 f:=1;
10 ELSE
11 f:=x*factorial(x-1);
12 END IF;
13 RETURN f;
14 END;
15 BEGIN
16 num:='&num';
17 fact:=factorial(num);
18 dbms_output.put_line('FACTORIAL OF ' || num || ' IS ' || fact);
19 END;
20 /
Enter value for num: 8
old 16: num:='&num';
new 16: num:='8';
FACTORIAL OF 8 IS 40320
Application :
SQL> CREATE OR REPLACE FUNCTION bloodstock1
2 RETURN number IS
3 total number(2) := 0;
4 BEGIN
5 SELECT count(fname) into total from donor123
6 where bloodtype='O+ve';
7 RETURN total;
8 END;
9 /
Function created.
SQL> DECLARE
2 c number(2);
3 BEGIN
4 c :=bloodstock1();
5 dbms_output.put_line(c);
6 END;
7 /
SQL> select * from donor123;
FNAME LNAME AGE WEIGHT BLOODTYPE ID
---------- ---------- ---------- ---------- ------- ----------------------------------------
Meenu Kumaran 19 52 A-ve 101
Salai Priya 24 66 B+ve 103
Ramya Ram 29 45 A1+ve 104
Shalu Sekar 34 66 O-ve 106
Hema latha 34 45 A+ve 107
Sumathi Sundaram 45 66 B+ve 109
Karthika Kumar 66 77 A1-ve 110
Sowmiya Ram 24 55 A-ve 111
Sumaya Raj 33 66 A+ve 112
shivani suraj 44 88 O-ve 113
Procedures :
• A procedure is a group of PL/SQL statements that you can call by name.
• A procedure is a module performing one or more actions , it does not need
to return any values.
Creating a Procedure:
Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END
procedure_name;
Sample Programs:
Creation and dropping of procedure:
SQL> CREATE OR REPLACE PROCEDURE greetings
2 AS
3 BEGIN
4 dbms_output.put_line('Hello World!');
5 END;
6 /
Procedure created.
SQL> EXECUTE greetings;
Hello World!
SQL> DROP PROCEDURE greetings;
Procedure dropped.
SQL> DECLARE
2 a number;
3 b number;
4 c number;
5 PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
6 BEGIN
7 IF x < y THEN
8 z:= x;
9 ELSE
10 z:= y;
11 END IF;
12 END;
13 BEGIN
14 a:= 23;
15 b:= 45;
16 findMin(a, b, c);
17 dbms_output.put_line(' Minimum of (23, 45) : ' || c);
18 END;
19 /
Minimum of (23, 45) : 23
Square of a number:
SQL> DECLARE
2 a number;
3 PROCEDURE squareNum(x IN OUT number) AS
4 BEGIN
5 x:=power(x,x);
6 END;
7 BEGIN
8 a:='&a';
9 squareNum(a);
10 dbms_output.put_line('SQUARE :: ' || ' IS ' || a);
11 END;
12 /
Enter value for a: 8
old 8: a:='&a';
new 8: a:='8';
SQUARE :: IS 16777216
PACKAGES:
Packages are schema objects that groups logically related PL/SQL types, variables,
and subprograms.
A package will have two mandatory parts −
• Package specification
• Package body or definition .
SYNTAX:
For creating package:
CREATE [OR REPLACE] PACKAGE <package_name>
IS
<sub_program and public element declaration>
.
.
END <package name>
For creating package body:

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<global_declaration part>
<Private element definition>
<sub_program and public element definition>
.
<Package Initialization>
END <package_name>
SQL> select * from customers;
---------- --------------- ---------- -------------------- -------------------------------
1 Meena 19 Madurai 8000
2 Kalai 23 AnnaNagar 3000
3 Vani 43 KK Nagar 2500
4 Sri 12 Thanakankulam 1000

5 Vidhya 67 Thirumangalam 3400
6 Devi 38 Annupanadi 5600
6 rows selected.
SQL> set serveroutput on;
SQL> CREATE OR REPLACE PACKAGE c_package AS
2 -- Adds a customer
3 PROCEDURE addCustomer(c_id customers.id%type,
4 c_name customers.name%type,
5 c_age customers.age%type,
6 c_addr customers.address%type,
7 c_sal customers.salary%type);
9 -- Removes a customer
10 PROCEDURE delCustomer(c_id customers.id%TYPE);
11 --Lists all customers
12 PROCEDURE listCustomer;
13
14 END c_package;
15 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY c_package AS
2 PROCEDURE addCustomer(c_id customers.id%type,
3 c_name customers.name%type,
4 c_age customers.age%type,
5 c_addr customers.address%type,
6 c_sal customers.salary%type)
7 IS
8 BEGIN
9 INSERT INTO customers (id,name,age,address,salary)
10 VALUES(c_id, c_name, c_age, c_addr, c_sal);
11 END addCustomer;
12
13 PROCEDURE delCustomer(c_id customers.id%type) IS
14 BEGIN
15 DELETE FROM customers
16 WHERE id = c_id;
17 END delCustomer;
18
19 PROCEDURE listCustomer IS
20 CURSOR c_customers is
21 SELECT name FROM customers;
22 TYPE c_list is TABLE OF customers.name%type;
23 name_list c_list := c_list();
24 counter integer :=0;
25 BEGIN
26 FOR n IN c_customers LOOP
27 counter := counter +1;
28 name_list.extend;
29 name_list(counter) := n.name;
30 dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
31 END LOOP;
32 END listCustomer;
33
34 END c_package;
35 /
Package body created.
SQL> DECLARE
2 code customers.id%type:= 8;
3 BEGIN
4 c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
5 c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
6 c_package.listcustomer;
7 c_package.delcustomer(code);
8 c_package.listcustomer;
9 END;
10 /
Output:
Customer(1)Meena
Customer(2)Kalai
Customer(3)Vani
Customer(4)Sri
Customer(5)Vidhya
Customer(6)Devi
Customer(7)Rajnish
Customer(8)Subham
Customer(1)Meena
Customer(2)Kalai
Customer(3)Vani
Customer(4)Sri
Customer(5)Vidhya
Customer(6)Devi
Customer(7)Rajnish
Result:
Thus the functions , procedures and packages are successfully executed in SQL.
EX.NO.10 INSTALLATION OF MONGODB AND CREATION OF COLLECTIONS
Aim:
To install MongoDB and create a collection with it.
Description:
MongoDB is an open-source document database and leading NoSQL database.
MongoDB iswritten in C++. It is a cross-platform document-oriented database program.
Classified as a NoSQLdatabase program, MongoDB uses JSON-like documents with
optional schemas.
Procedure:
Installation:
STEP 1: Go to https://www.mongodb.com/download-center/community. Select the
necessaryconfiguration and click on the Download button.
STEP 2: In the installation wizard, Click Next.
STEP 3: Accept the user agreement and Select the setup type as Complete and click Next.
STEP 4: Click the Next button in the upcoming two windows.
STEP 5: Click on the install button to start the installation.

STEP 6: Click on the Finish button to finish the set up.
STEP 7: MongoDB requires a data directory under the C drive which will have a db file. Create it.
STEP 8: In the command prompt, enter the following command “cd C:\Program Files\MongoDB\Server\4.2\bin”.
Once inside the folder, type “mongod” command.
STEP 9: After the service is started open another command prompt and move to the directory
specified before. Type “mongo” in order to open the mongo shell.
MongoDB is installed successfully.

Creation of collection:
1. Create database using “use databasename” command.
2. Create a collection and insert a value using
“db.collectionname.insert({"key":value,"key":"value})” command.
Result:
Thus, the MongoDB was installed and the collections is created successfully.
EX.NO.11
SIMPLE AND JOIN QUERIES IN MONGODB
Aim:
To perform simple queries and join operations using MongoDB.
Procedure:
INSERT:
Single Record:
db.collection_name.insert({key:value,…})
Multiple Records:
db. collection_name.insertMany([
{key:value,…},
{key:value,…},
{key:value,…}
])
FIND: (Select in SQL)

All columns
Specific column(s)
UPDATE:
Single record:
db.collection_name.update({“Identifier”:value},{$set:{“updating coloumn”:value}}})
Multiple records:
db.collection_name.updateMany({“identifier”:val},{$set:{“updating coloumn”:val}}})
DELETE:
Single Record:
db.collection_name.remove({“key”:value})
Multiple Records:
db.collection_name.deleteMany({“key”:value})
COUNT:
SORT:
db.collec
tion_na
me.coun
t()
JOIN:
db.collection_name.aggregation([
{
$lookup:
{
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,as:
<output array field>
}
}
}
])
Result:
Thus, simple queries are executed and join operations are performed successfully.
EX.NO.12 PERFORM MONGODB QUERY AND PROJECTION OPERATION
Aim:
To execute queries and projection operations using MongoDB.
Procedure:
PROJECTION:
MongoDB Projection is used when we want to get the selected fields of the documentsrather
than all fields.
Specific column(s)
Hide specific columns(s)
QUERIES:
SELECT DISTINCT:
db.collection_name.distinct(“key”)
FIND: (Select in SQL)

All columns
CONDITIONAL SELECT:db.collection_name.find( {key:value} )
AND:
db.collection_name.find( { $and: [ {key:value},…,{key:value} ] } )
OR:
db.collection_name.find( { $or: [ {key:value},…,{key:value} ] } )
COUNT:
db.collection_name.count()
SORT:
Ascending:
db.collection_name.find().sort({key:1})
Descending:
db.collection_name.find().sort({key:-1})
Display certain column(s) after sorting
db.collection_name.find({},{key:0}).sort({key:-1})
SKIP:
To skip the top number of rows as specified in the query result
db.collection_name.find().skip(number)
DROP COLLECTION:
db.collection_name.drop()
Result:
Thus, queries are executed and projection operations are performed successfully.
EX.No:13 DEMONSTRATE MINI-PROJECT AS WEB APPLICATION
Aim:
To develop and demonstrate the application chosen as a Miniproject.
Procedure:
• List the functionalities implemented in Web application.
• Design Webpage for the functionalities mentioned.
• Create the Database for your application
• Connect the Database with Webapplication
• Have the screenshot of the Webpage , Queries and output
FRONT END DESIGN FOR COURSE REGISTATION SYSTEM:
( draw the consolidated UI Design / include the UI Design stage by stage)
Result:
Thus Web application has been created for the application chosen with Database Connectivity.

22IT480 - DBMS - LabManual UPDATED

Uploaded by

Copyright:

Available Formats

22IT480 - DBMS - LabManual UPDATED

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

22IT480 - DBMS - LabManual UPDATED

Uploaded by

Copyright:

Available Formats

22IT480-Lab Manual 2023_24

Thiagarajar College of Engineering, Madurai – 625015

Course Code : 22IT480

Course Name : Database Management Systems Lab

Programme : B.Tech. (IT)

Faculty Incharge (s) : Dr.S.Sridevi ,Dr.K.V.Uma

COs Course Outcomes TCE Expected Expected

Mapping with Programme Outcomes and Programme Specific Outcomes

S- Strong; M-Medium; L-Low

Cognitive Model Examination Terminal Examination

Ex.No:1 IDENTIFICATION OF MINI PROJECT and UI Design

To identify the application and to draw the Schema and ER diagram.

• The overall design of a database is called schema.

• Identify the entities and its attributes

• Describe the major functionalities ( atleast 6 ) of your Application

• Draw the Schema diagram for the application

• Draw the ER diagram for the application with the relationship.

Schema Diagram Example:

UI DESIGN FOR COURSE REGISTATION SYSTEM:

( draw the consolidated UI Design / include the UI Design stage by stage)

Ex.No:2 CREATION AND MODIFICATION OF RELATIONS

Aim: To execute different Data Definition Language commands.

• SELECT – retrieve data from the a database

• GRANT – allow users access privileges to database

• COMMIT – commits a Transaction

Step no Details of the step

1a) Create Table

1. CREATE TABLE customers

1b) CREATE TABLE AS

1c) Create Table Example: Copying selected columns of another table

CREATE TABLE newcustomers2 AS (SELECT customer_id, customer_name FROM customers

2a) Add column in a table

ALTER TABLE table_name ADD column_name column-definition;

ALTER TABLE customers ADD customer_age varchar2(50);

2b) Add multiple columns in the existing table

ALTER TABLE table_name ADD (column_1 column-definition, column_2 column-

... column_n column_definition);

ALTER TABLE customers ADD (customer_type varchar2(50), customer_address

2c)Modify column of a table

ALTER TABLE table_name MODIFY column_name column_type;

ALTER TABLE customers MODIFY customer_name varchar2(100) not null;

2d)Modify multiple columns of a table

ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type,

... column_n column_type);

ALTER TABLE customers MODIFY (customer_name varchar2(100) not null, city

2e)Drop column of a table

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE customers DROP COLUMN customer_name;

This will drop the customer_name column from the table.

2f) Rename column of a table

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Example: ALTER TABLE customers RENAME COLUMN customer_name to cname;

This will rename the column customer_name into cname.

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE customers RENAME TO retailers;

This will rename the customer table into "retailers" table.

3) DROP TABLE Statement

DROP TABLE table_name;

DROP TABLE customers;