CP4152 Database Practices Lab Record

lOMoARcPSD|10518309
CP4152 database Practices lab record
Advanced Database Technologies (Anna University)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)
lOMoARcPSD|10518309
SARANATHAN COLLEGE OF ENGINEERING
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
REGULATION 2021
ACADEMIC YEAR 2022-2023
CP4152 DATABASE PRACTICES – INTEGRATED LAB

lOMoARcPSD|10518309
CONTENTS
S.NO DATE EXPERIMENT SIGNATURE
1A STUDY OF DATA DEFINITION LANGUAGE
1B STUDY OF DATA MANIPULATION LANGUAGE
1C STUDY OF TRANSCATION CONTROL LANGUAGE
2A INTEGRITY CONSTRAINTS
2B SQL FUNCTIONS AND OPERATORS
2C JOIN QUERIES AND SUB QUERIES
2D IMPLEMENTATION OF VIEWS
3A IMPLEMENTATION OF TRIGGERS
3B ACCESSING RELATIONAL DATABASES USING PYTHON
4 XML DATABASES
5 MONGODB DATABASE CREATION & INSERTION

lOMoARcPSD|10518309
INTRODUCTION OF SQL
SQL stands for “Structured Query Languages”. It is used to communicate with a database
which has been already created. According to American National Standard Institute [ANSI], it is
the standard language for relational database management system [DBMS].
SQL is a very powerful and diverse database language use to storing data into databases. SQL is
loosely typed language so you can learn easily.
SQL statements are used to perform tasks such as information updating or retrieval of
information from a database. Some common relational database management systems that use
SQL are,
 Oracle
 Microsoft SQL Server
 Microsoft Access
 MySQL
Although most DBMSs use SQL, they have their unique and own additional proprietary
extensions. Some of common, standard SQL commands are CREATE, SELECT, INSERT,
UPDATE, DELETE and DROP commands. These commands can be used to accomplish almost
everything that one heads to do with a database.
Oracle has provided the following data types which can be used while creating new table.
Data Type Specification Description

Character data type, size should
char char(size)
be specified in number.
Same as character data type. This
varchar varchar2(size)
can be used for less storage.
Used to store date values [DD-
date Date
MMM-YY]
Used for storing numerical data.
e.g.: number(5.2) can accept
value from 0.00 to 999.99
number number(size,decimal_places)
Number data type can also be
used for picture information and
binary large object.
integer Number Same as number data type.
float Number Same as number data type.

lOMoARcPSD|10518309

lOMoARcPSD|10518309
Rules:
1. Oracle reserved words cannot be used for naming tables and attributes.
2. Table name should be an unique name and it can have underscore, numerals, letters are
allowed but not blank space.
3. Maximum length for the table name is 30 characters.
5. Each attribute (column) should be followed data type along with size.
6. Integrity constraints can be specified while creating new table and default value for
attribute is ‘null’.

lOMoARcPSD|10518309
Ex. No: 1a
Study of DDL Commands
[Data Definition Language]
AIM:
To execute and verify the basic SQL DDL Commands
PROCEDURE
1. Create the table with its essential attributes.
2. Execute different Commands and extract information from the table.
DDL [Data Definition Language] Commands

DDL commands are concerned with creation, deletion and modification of objects in the
database like tables, indexes and views.
CREATE: To create objects in the database
ALTER: To alter the structure of the database objects
DROP: To delete objects from the database
TRUNCATE: To remove all records from the specified table including all spaces allocated
for the record are also removed.
1. CREATE TABLE COMMAND

It defines each column of the table uniquely. Each column has minimum of these attributes
such as name, data type, size and optionally integrity constraints.
Syntax:
CREATE TABLE <table_name>(<col 1> <datatype><size>,
<col 2> <datatype><size>, ...
<col n><datatype><size>);
2. MODIFYING THE STRUCTURE OF TABLE

 Adding a new column
Syntax:
ALTER TABLE <table_name> add(<new col> <datatype><size>);
 Deleting an existing column

Syntax:
ALTER TABLE <table_name> drop column <column_name>;
 Modifying an existing column

Syntax:
ALTER TABLE <table_name> modify(<col_name><datatype><size>);
 Renaming an existing column

Syntax:
ALTER TABLE <table_name> rename column <old_name> to
<new_name>;

lOMoARcPSD|10518309

lOMoARcPSD|10518309
3. RENAMING THE TABLE

Syntax:
RENAME <old_table_name> to <new_table_name>;
4. TRUNCATING THE TABLE

If there is no further use of records stored in a table but the structure has to be retained for
future, then the records alone can be deleted.
Syntax:
TRUNCATE TABLE <table_name>;
5. DESTROYING THE TABLE

It will delete the table structure as well as contents stored in that table..
Syntax:
DROP TABLE <table_name>;
6. DISPLAYING THE STRUCTURE OF TABLE

DESC is the command used to view the structure of the table.
Syntax:
DESC <table_name>;

lOMoARcPSD|10518309

lOMoARcPSD|10518309
Ex. No: 1b
Study of DML Commands
[Data Manipulation Language]
AIM:
To execute and verify the basic SQL DML Commands
PROCEDURE:
1. Create the table with its essential attributes.
2. Insert the record into table
3. Update the existing records into the table
4. Delete the records in to the table
DATA MANIPULATION LANGUAGE:

The Data Manipulation Language (DML) is a subset of SQL programming language
used to retrieve, insert and modify database information. These commands will be used by all
database users during routine operations of database.
The basic DML commands
include o
INSERT
o SELECT
o UPDATE
o DELETE
1. INSERTING DATA INTO THE TABLE

This is used to add one or more rows to a table. The values are separated by commas and
the data types char and date are enclosed in apostrophes. The values must be entered in
the same order as they are defined.
a) Inserting values for all columns (no need to specify the list of column names)
Syntax:
INSERT INTO <table_name> VALUES(<expr1>,<exp2>,…<expn>);
b) Inserting values only for a set of columns

Syntax:
INSERT INTO <table_name> (<col1>,<col2>,…<coln>)
VALUES(<expr1>,<exp2>,…<expn>);
c) Inserting values using identifier method

Syntax:
INSERT INTO <table_name> VALUES(&identifier1,&identifier2,
…);

lOMoARcPSD|10518309
2. DELETE OPERATIONS
a) Remove all rows
Syntax:
DELETE FROM <table_name>;
b) Removal of a specified rows

Syntax:
DELETE FROM <table_name> WHERE <condition>;
3. UPDATING THE CONTENTS OF A FILE TABLE

a) Updating all rows
Syntax:
UPDATE <table_name> SET <col1>=<value>;
b) Updating multiple columns (use comma to separate the columns)

Syntax:
UPDATE <table_name> SET
<col1>=<value1>,<col2>=<value2>;
c) Updating selected rows

Syntax:
UPDATE <table_name> SET <col1>=<expr> WHERE
<condition>;
4. VIEWING DATA IN THE TABLE

Once data has been inserted into a table, the next most logical operation would be to
view what has been inserted.
a) To display all data in the given table
Syntax:
SELECT * FROM <table_name>;
5. FILTERING TABLE DATA

SQL gives a method of filtering out data that is not required.
a) Selected rows and all columns
Syntax:
SELECT * FROM <table_name> WHERE <condition>;
b) Selected columns and all rows

Syntax:

lOMoARcPSD|10518309
SELECT <col1>,<col2> FROM <table_name>;
c) Selected columns and selected rows

Syntax:
SELECT * FROM <table_name> ORDER BY <col1> ASC|
DESC; ASC – Ascending Order
DESC – Descending Order

lOMoARcPSD|10518309
Ex. No: 1c
Study of TCL Commands
[Transaction Control Language]
AIM:
To execute and verify the basic SQL TCL Commands

PROCEDURE
1. Use save point if any changes occur in any portion of the record to undo its original
state.
2. Use rollback for completely undo the records
3. Use commit for permanently save the records.
Transaction Control Language [TCL]

A transaction control language (TCL) is a computer language and subset of SQL used
to control transactional processing in a database. Transaction control (TCL) statements are
used to manage the changes made by DML statements. It allows statements to be grouped
together into logically grouped transaction.
TCL commands are,

COMMIT: Save the database work done.
Syntax:
COMMIT;
SAVEPOINT: Identifying a point in a transaction to which can be roll back in future

instead
of rolling back the entire transaction.
Syntax:
SAVEPOINT <savepoint_name>;
ROLLBACK: Restore database to original since the last commit or last rollback done.
Syntax:
ROLLBACK TO <savepoint_name>;
(or)
ROLLBACK;

lOMoARcPSD|10518309
Ex. No: 2A
Integrity Constraints
AIM:
To study the various types of integrity constraints
INTRODUCTION:
Constraints are condition for the data item to be stored into a
database. Constraints can be specified when the table is created (with create table
statement) or after the table is created (with alter table statement).
SQL constraints are,
 NOT NULL
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
 DEFAULT
NOT NULL:
This enforces a column to not accept NULL values. It enforces the field to always
certain a value i.e. it cannot insert or update without adding value to this field.
e.g.:
CREATE TABLE person(ID integer NOT NULL, f_name varchar2(10), l_name
varchar2(10), city varchar2(10));
UNIQUE:
Uniquely identify the each record in the database table. The primary key constraints
automatically has a unique constraints defined on it. There can be many unique constraints
per table but only one primary constraint for a table.
Using CREATE table statement:

CREATE TABLE person(ID integer UNIQUE, f_name varchar2(10) l_name

lOMoARcPSD|10518309
To allow naming of unique constraints and for defining unique constraints on multiple
columns:
CREATE TABLE person(ID integer NOT NULL, f_name varchar2(10), l_name
varchar2(10), city varchar2(10), CONSTRAINT unique_name
UNIQUE(ID,f_name));
To drop a unique constraint:

ALTER TABLE person DROP constraint unique_name;
PRIMARY KEY CONSTRAINT:

Uniquely identifies each record in a database table. Primary key field should contain
unique value. It cannot contain NULL values. Each table should have primary key and these
can be only one primary key.
Using CREATE statement:

CREATE TABLE person(PID integer NOT NULL PRIMARY KEY, name
Using ALTER statement: To allow naming of primary key constraint

ALTER TABLE person ADD PRIMARY KEY(name);
(or)
ALTER TABLE person ADD CONSTRAINT primary_name PRIMARY KEY(name);
To drop a unique constraint:

ALTER TABLE person DROP CONSTRAINT primary_name;
ENABLE AND DISABLE CONSTRAINTS:

4. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
5. ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
FOREIGN KEY CONSTRAINT:

A foreign key in one table points to the primary key in another table.

lOMoARcPSD|10518309
PERSON TABLE
PID NAME CITY
1 JONES ATLANDA
2 JOHNSON CHICAGO
3 HAYES LONDON
ORDER TABLE
OID O_NUMBER PID
41 73560 2
42 55361 3
43 99440 1
The foreign key constraint is used to prevent actions that would destroy links between
tables. The foreign key constraints also prevent the invalid data from being inserted into the
foreign key column because it has to be one of the values contain in the table it points to.
Using CREATE Statement:

CREATE TABLE order(OID number(5) NOT NULL PRIMARY KEY, O_NUMBER
number(5) NOT NULL, PID number(5), FOREIGN KEY(PID) REFERENCES
person(PID);
Using ALTER Statement:

ALTER TABLE table_name ADD CONSTRAINTS constraint _name FOREIGN
KEY(attribute) REFERENCES table_name(attribute);
CHECK CONSTRAINTS:
CHECK constrains is used to limt the value range that can be placed in the column. If
we define check constraint in single column it allows only certain values for this column.
e.g.:
CREATE TABLE person(PID integer CHECK(PID>0), name varachar2(20), city
varchar2(10));

lOMoARcPSD|10518309
ALTER TABLE person ADD CONSTRAINT check_name CHECK(PID>0);

lOMoARcPSD|10518309
DEFAULT CONSTRAINTS:
The default constraint is used to insert default value into column. These default
values will be added to all the new record if no other value is specified.
e.g.:
CREATE TABLE friend(ID integer, name varchar2(10), city varchar2(10)
DEFAULT ‘TRICHY’);

lOMoARcPSD|10518309
Ex. No: 2B
SQL Functions and Operators
AIM:
To study the various operators and built-in functions in SQL in Oracle 9i.
PROCEDURE:
An operator is a reserved word or a character used primarily in an SQL statement's
WHERE clause to perform operation(s), such as comparisons and arithmetic operations.
Operators are used to specify conditions in an SQL statement and to serve as conjunctions for
multiple conditions in a statement.
 Arithmetic operators
 Comparison operators
 Logical operators
 Operators used to negate condition.
SQL OPERATORS:
ARITHMETIC
DESCRIPTION
OPERATORS
+, - ,*, / To perform basic arithmetic operations
RELATIONAL
DESCRIPTION
OPERATORS
= Equal to
!= Not equal to
< Less than
> Greater than
<= Less than (or) Equal to
>= Greater than (or) equal to
LIKE Column value is similar to specified characters
IN Column value is equal to any of the specified values
BETWEEN AND Column value is between two values
IS NULL Column value does not exist
LOGIC
DESCRIPTION
OPERATORS
OR For the row to be selected at least one of the condition must be true
AND For the row to be selected all the specified condition must be true.
NOT For the row to be selected the specified condition must be false.
SQL FUNCTIONS:

lOMoARcPSD|10518309
There are two types of built-in function in oracle.

1. SINGLE ROW FUNCTION
Single row/scalar function returns a value for every row that is processed in the query.
2. GROUP FUNCTION
These functions group the rows of data based on the values returned by the query. This is
discussed in SQL GROUP functions. The group functions are used to calculate the aggregate
values like total/average, which returns just one total/one average value after processing a
group of rows.
SINGLE ROW FUNCTION:

They are four types of single row functions. 1.
Numeric Functions:
They accept numeric inputs and return numeric values. 2.
Character/Text Functions:
They accept characters input and return both number and character values. 3.
Date Functions:
These are functions that take value that are of datatype DATE as input and return
values of datatype DATA, except for MONTHS. BETWEEN function that returns a number.
Conversion Functions:
These are function helps us to convert a value from one form to another .
USE OF DUAL TABLE IN ORACLE:

This is a single row and single column dummy table provided by oracle. This is used
to perform mathematical operations without using a table.
NUMERIC FUNCTIONS:
FUNCTION NAME DESCRIPTION
ABS(x) Absolute value of the number “x”
CEIL(x) Integer value that is greater than (or) equal to “x”
FLOOR(x) Integer value that is lesser than (or) equal to “x”
TRUNC(x,y) Truncate values of “x” upto “y” decimal places.

lOMoARcPSD|10518309
ROUND(x,y) Rounded off value of “x” upto “y” decimal places.
POWER(x,y) Returns the value of x^y.ie.,x=2,y=5 then x^y=32
SQRT(x) Returns the SQUARE ROOT value of x

SIN(x)
Returns the result of respective trigonometric function for
COS(x)
the given value
TAN(x)
Returns the result of logarithmic function for the given
LOG(x)
value
GREATEST(x1,x2,….,xn) Returns the greatest value in the set of input parameters
LEAST(x1,x2,….,xn) Returns the least value in the set of input parameters
MOD(N,M) This function returns the remainder of N divided by M.

RAND function that can be invoked to produce random
RAND()
numbers between 0 and 1
DATE FUNCTIONS:

ADD_MONTHS(date,n) Returns a date value after adding in months to the “date”
MONTHS_BETWEEN(x1,x2) Returns the number of months between dates x1 and x2
Returns the nextdate of the “weekday” on or after “x”
NEXTDAY(x, weekday)
occurs
Returns the number of days remaining in a month after
LAST_DAY(x)
“x”
SYSDATE Returns the systems current date and time
CONVERSION FUNCTIONS:

Converts numeric and date values to character string
TO_CHAR(x,[y])
value
Converts a valid numeric and character values to data
TO_DATE(x,[date])
value
The TO_NUMBER function converts a character
value to a numeric datatype. If the string being
TO_NUMBER (string1, format)
converted contains nonnumeric characters, the
function returns an error.

lOMoARcPSD|10518309
CHARACTER/TEXT FUNCTIONS:

All the letters in the “string_value” is converted to
LOWER(string_value)
lower case
All the letters in the “string_value” is converted to
UPPER(string_value)
upper case
ASCII(character) Returns numeric value of left-most character
CHAR(integer_value) Returns the character for each integer passed

All the letters in the “string_value” is converted into
INITCAP(string_value)
mixed case
All occurrences of “trim text” is removed from the
LTRIM(string_value,trim_text)
left of given input “string_value”
All occurrences of “trim text” is removed from the
RTRIM(string_value,trim_text)
right of given input “string_value”
All occurrences of “trim text” are removed from the
TRIM(trim_text)
given input “string_value”
Return ‘n’ number of characters from “string_value”
SUBSTR(string_value,m,n)
starting from mth position
LENGTH(string_value) Number of characters in given input “string_value”
HEX() Returns a string representation of a hex value
REVERSE(string) Reverses the characters in a string
Compares two strings and returns 0 if both strings are
equal, it returns -1 if the first argument is smaller than
STRCMP(str1, str2)
the second according to the current sort order
otherwise it returns 1.
SQL GROUP Functions
Group functions are built-in SQL functions that operate on groups of rows and return
one value for the entire group. These functions are: COUNT, MAX, MIN,
AVG, SUM, DISTINCT
COUNT() : This function returns the number of rows in the table that satisfies the
condition specified in the WHERE condition. If the WHERE condition is not specified,
then the query returns the total number of rows in the table.

lOMoARcPSD|10518309
For Example: If you want the number of employees in a particular department, the query
would be:
SELECT COUNT (*) FROM employee WHERE dept = 'Electronics';
If you want the total number of employees in all the department, the query would take the
form:
SELECT COUNT (*) FROM employee;
DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from employee table, the
query would be:
SELECT DISTINCT dept FROM employee;
To get the count of employees with unique name, the query would be:
SELECT COUNT (DISTINCT name) FROM employee;
MAX(): This function is used to get the maximum value from a column. To get
the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;
MIN(): This function is used to get the minimum value from a column. To get
the minimum salary drawn by an employee, he query would be:
SELECT MIN (salary) FROM employee;
AVG(): This function is used to get the average value of a numeric column. To
get the average salary, the query would be
SELECT AVG (salary) FROM employee;
SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the employees,
SELECT SUM (salary) FROM employee;

lOMoARcPSD|10518309
Ex. No: 2C
Join Queries and Sub Queries
AIM:
To study and implement join queries and sub queries in SQL.
INTRODUCTION:
JOIN QUERIES:
SQL joins are used to query data from two or more tables, based on a relationship
between certain columns in these tables. The JOIN keyword is used in an SQL statement to
query data from two or more tables based on a relationship between certain columns in these
tables.
General Format:
SELECT <COLUMN 1>,<COLUMN 2>,…
<COLUMN n> FROM <TABLE 1>,<TABLE
2>
WHERE
<TABLE 1>.<COLUMN NAME 1> join_op <TABLE 2>.<COLUMN NAME
2>;
Various types of JOIN in SQL are,

 EQUI JOIN
 CROSS JOIN
 NATURAL JOIN
 LEFT OUTER JOIN
 RIGHT OUTER JOIN
 FULL OUTER JOIN
EQUI JOIN:
An EQUI JOIN is a join with a join condition containing an equality operator (=) in
the where clause. An EQUI JOIN combines rows that have equivalent values for the specified
columns.
Syntax:
SELECT <COLUMN 1>,<COLUMN 2>,…
<COLUMN n> FROM <TABLE 1>,<TABLE
2>
WHERE
<TABLE 1>.<COLUMN_NAME 1> = <TABLE 2>.<COLUMN_NAME 2>;

lOMoARcPSD|10518309
CROSS JOIN:
SQL CROSS JOIN will return all records where each row from the first table is
combined with each row from the second table which also mean CROSS JOIN returns the
Cartesian product of the sets of rows from the joined tables.
A CROSS JOIN can be specified into two ways either by using JOIN Syntax or by
listing the tables in the FROM clause separated by comma, without using a WHERE clause to
supply join criteria.
Syntax:
SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2];
(or)
SELECT * FROM [TABLE 1], [TABLE 2];
NATURAL JOIN:
A NATURAL JOIN is a join statement that compares the common columns of both
tables with each other. One should check whether common columns exist in both tables
before doing a natural join. Natural joins may cause problems if columns are added or
renamed. Also No more than two tables can be joined using this method.
Syntax:
SELECT * FROM [TABLE 1] NATURAL JOIN [TABLE 2];
LEFT OUTER JOIN:

Returns all rows from the left table even if there are no matches in the right table. Left
outer join includes all records from the first (left) of two tables.
A = B (+);
RIGHT OUTER JOIN:

Returns all rows from the right table even if there are no matches in the right table.
Right outer join includes all records from the second (right) of two tables.
A (+) = B;
SQL ALIAS:
Alias is used to rename a table or a column by using another name. This can be used
when the table name or column name is very long or complex. An alias name could be
anything but usually it is short.
Syntax:
SELECT <column_list> FROM <table_name> ALIAS name;
e.g.:
SELECT * FROM emp a, emp b;
(Here a and b are alias names)

lOMoARcPSD|10518309
SUB QUERIES:
A sub query is a query that is nested inside a SELECT, INSERT, UPDATE or
DELETE statement or inside another sub query. Sub query are an alternate way of returning
data from multiple tables.
Sub queries can be used with the following SQL statements along with the
comparison operators like <, >, <=, >=, etc,. Sub query or inner query or nested query is a
query in a query.
Select select –list from table name where expr operator

(select select_list from table name)
There are two types of sub queries

6. Independent sub query
7. Correlated sub query
INDEPENDENT SUB QUERY:

It is also called as self-contained sub query. Inner query is independent of outer query
and it is executed first and the results are stored and then outer query will be executed on the
stored results.
 Single Row Subqueries: The subquery returns only one row. Use single row comparison
operators like =, > etc while doing comparisions.
 Multiple Row Subqueries: The subquery returns more than one row. Use multiple row
comparison operators like IN, ANY, ALL in the comparisons.
CORRELATED SUB QUERY:

A query is called correlated sub query when both the inner query and the outer query
are inter-dependent. For every row processed by the times inner query, the outer query is
processed as well. The inner query depends on the outer query before it can be processed.
The inner query is executed separately for each row of the outer query i.e. in
correlated sub queries, SQL performs a sub query one over and over again once for each row
of the main query.

lOMoARcPSD|10518309
Ex. No: 2D
Implementation of Views
AIM:
To study and implement View, Index, Sequence and Synonym concepts using Oracle
9i.
VIEW
INTRODUCTION:
A VIEW is a virtual table through which a selective portion of the data from one or
more table can be seen views do not certain data of their own. They are used to restrict access
to the database or to hide data complexity. A view is stored as a SELECT statement in the
database.
The DML operations on a view like INSERT, UPDATE and DELETE can be
performed only of the query that defines the views meets all of these restrictions.
 DISTINCT - must not be specified i.e., duplicate rows must not be eliminated
from the query results.
 The FROM clause must specify only one table that is the view must have a
single underlying source table.
 The SELECT list cannot expressions calculated columns or column functions.
 The WHERE clause must not include a sub query, only simple row-by-row
search conditions may appear.
A view can contain all rows of a table or select rows from a table. A view can be
created from one or many tables which depends on the written SQL query to create a view.
Views, which are kind of virtual tables, allow users to do the following:
8. Structure data in a way that users or classes of users find natural or intuitive.
9. Restrict access to the data such that a user can see and (sometimes) modify
exactly what they need and no more.
10. Summarize data from various tables which can be used to generate reports.
GENERAL FORMAT:
CREATE VIEW view_name AS

SELECT <column_list> FROM table_name [WHERE
condition];

lOMoARcPSD|10518309
The view_name is the name of the VIEW. The SELECT statement is used to define
the columns
and rows that have to be displayed.
FOR HORIZONTAL VIEW:

SELECT * FROM table_name [WHERE condition];
FOR VERTICAL VIEW:

SELECT <column_list> FROM table_name;
DROPPING A VIEW:
DROP VIEW view_name;
The WITH CHECK OPTION:
The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of
the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the
condition(s) in the view definition. If they do not satisfy the condition(s), the UPDATE or
INSERT returns an error.
The following is an example of creating same view CUSTOMERS_VIEW with the WITH
CHECK
OPTION:
CREATE VIEW CUSTOMERS_VIEW
AS SELECT * FROM CUSTOMERS WHERE age IS NOT NULL
WITH CHECK OPTION;
The WITH CHECK OPTION in this case should deny the entry of any NULL values
in the view's AGE column, because the view is defined by data that does not have a NULL
value in the AGE column.
Updating a View:
A view can be updated under certain conditions:
7. The SELECT clause may not contain the keyword DISTINCT.
8. The SELECT clause may not contain summary functions.
9. The SELECT clause may not contain set functions and set operators.

lOMoARcPSD|10518309
10. The SELECT clause may not contain an ORDER BY or GROUP BY or HAVING
clause.
11. The FROM clause may not contain multiple tables.
12. The WHERE clause may not contain sub queries.
13. Calculated columns may not be updated.
14. All NOT NULL columns from the base table must be included in the view in order
for the
INSERT query to function.
So if a view satisfies all the above-mentioned rules then you can update a view. Following
is an example to update the age of Ramesh:
UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name='Ramesh';
Note: This would ultimately update the base table CUSTOMERS and same would
reflect in the
CUSTOMERS_VIEW itself.
Force View Creation

‘Force’ keyword is used while creating a view. This keyword force to create View
even if the table does not exist. After creating a force View if we create the base table and
enter values in it, the view will be automatically updated. Syntax for forced View is,
CREATE or REPLACE force view view_name AS
SELECT <column_name> FROM table_name WHERE condition
Read-Only View
A view can be created with read-only option to restrict access to the view. Syntax to
create a view with Read-Only Access is as follows,
CREATE or REPLACE force view view_name AS
SELECT <column_name> FROM table_name
WHERE <condition> with read-only
The above syntax will create view for read-only purpose, Any Update or Insert data into read-
only view, will throw an error.

lOMoARcPSD|10518309
Ex. No: 3A
IMPLEMENTATION OF TRIGGERS
AIM:
To study about trigger and develop an application using database triggers.
INTRODUCTION:
A trigger is a SQL procedure that initiates an action when any one of event (INSERT,
DELETE or UPDATE) occurs, since triggers are event driven specialized procedures, they are
stored in and managed by the DBMS. A trigger cannot be called or executed; the DBMS
automatically fires the triggers as a result of a data modification to the associated table.
Triggers are used to maintain the referential integrity of data by changing the data in a
systematic function. Each trigger is attached to a single, specified table in the database.
Triggers are used to implement complex validation, security, auditing and replication of data.
In order to maintain the consistency of data during execution of triggers, oracle enforces a
restriction on the row trigger in terms of mutating and constraining table.
MUTATING TABLE:
A mutating table is the table, which is affected directly by DML statement or indirectly because
of ON_DELETE_CASCADE clause in referential integrity constraint.
CONSTRAINING TABLE:
A constraining table is the table, which is directly read by the SQL statement in the trigger or
indirectly because of foreign key constraint.
FOR DEFINING A TRIGGER:

Syntax:
CREATE [OR REPLACE] TRIGGER <trigger_name>
[BEFORE | AFTER]
[INSERT | DELETE | UPDATE]
ON <table_name> [FOR EACH ROW | FOR UPDATE]
BEGIN
-- TRIGGER BODY DEFINITION IN PL/SQL
END;
A trigger has the following basic parts,
1. TRIGGER STATEMENT:
It is a SQL statement that creates a trigger and fires it upon insertion, updation or
deletion of rows in a particular table.
2. TRIGGER ACTION:

lOMoARcPSD|10518309
A trigger action in the PL/SQL code will be executed when a triggering statement is
encountered. The PL/SQL block contains SQL and PL/SQL block have access to column
values of the table being processed.
 :new field name will contain the new value i.e. after execution of the triggering
statement.
 :old field name will contain the old value i.e. before execution of the triggering
statement.
3. BEFORE | AFTER TRIGGER:

BEFORE TRIGGERS execute the trigger action before the triggering statement
executed.
AFTER TRIGGERS execute the trigger action after the triggering statement executed.
ENABLING & DISABLING TRIGGER:
ALTER TRIGGER <trigger_name> ENABLE;
ALTER TRIGGER <trigger_name> DISABLE;
DELETING TRIGGER:
DROP TRIGGER <trigger_name>;
RAISE_APPLICATION_ERROR:
The oracle engine provides a procedure named raise_application_error, that allows

programmers to issue an user defined error messages.
It ends the execution of subprogram and roll backs any database changes it has made in
previous operations and returns the error message with user defined error code.
Syntax:
raise_application_error(<error_number>,<error_message>);
RESTRICTION ON TRIGGER CODE:
There are some restrictions on PL/SQL code, which have to be written in the trigger. The
restrictions are,
1. The user cannot write any transactional control statements like COMMIT, SAVEPOINT and
ROLL BACK, inside the trigger block.
2. If the row trigger is not properly written then it generates the effect which depends on order
in which rows are processed.
CONSTRAINTS ON A ROW TRIGGER:

1. The row trigger should not read from or modify mutating table.
2. The row trigger should not change PRIMARY KEY, FOREIGN KEY or UNIQUE column of
the constraining table.
EX:No : 3B Accessing a Relational Database using Python
AIM:

lOMoARcPSD|10518309
To access a Relational Database using Python

Procedure:
Python can connect to oracle using a python package called cx_Oracle. Oracle is one of
the famous and widely used database and python’s data processing features are leverages well
using this connectivity.
1. Download and install the relevant version from the following link:
https://pypi.python.org/pypi/cx_Oracle/5.2.1
2. You can test successful installation of cx_Oracle by executing following command on
Python shell:
>>> import cx_Oracle
>>>
3. No output confirms successful installation. Otherwise, error message will be returned.
4. Connect Database Create and Insert operations

After successful installation of cx_Oracle, we need to establish a connection
with Oracle database from Python program. connect() function helps to connect to
Oracle database from Python and returns the connection object.
5. Connected Database must be closed at the end of the program using close() function.
6. Commit() used to save the changes made in database otherwise the changes will not be
reflected in database.
7. A cursor holds the rows returned by SQL Statement.It helps to traverse over the records
returned. Need a cursor object to do all database operations. Cursor() function is used
to create a cursor
Syntax : <<connection_object>> =
cx_Oracle.connect('username/password@servicename')
>>>import cx_Oracle
>>>con = cx_Oracle.connect('system / system@localhost')
>>>cursor = con.cursor()
>>>con.commit()
>>>con.close()
8. Creation of Table
# importing module
import cx_Oracle
# Create a table in Oracle database

try:
con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
print(con.version)

lOMoARcPSD|10518309
# Now execute the sqlquery

cursor = con.cursor()
# Creating a table employee

cursor.execute(
"create table employee(empid integer primary key, name varchar2(30),
salary number(10, 2))")
print("Table Created successfully")
except cx_Oracle.DatabaseError as e:
print("There is a problem with Oracle", e)
# by writing finally if any error occurs

# then also we can close the all database operation
finally:
if cursor:
cursor.close()
if con:
con.close()
9. Insertion of Table
# importing module
import cx_Oracle
# Inserting a record into a table in Oracle database

try:
con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
cursor = con.cursor()
#con.autocommit = True
# Inserting a record into table employee
cursor.execute('insert into employee values(10001,\'Rahul\',50000.50)')
# commit() to make changes reflect in the database

con.commit()
print('Record inserted successfully')
except cx_Oracle.DatabaseError as e:
print("There is a problem with Oracle", e)
# by writing finally if any error occurs

# then also we can close the all database operation
finally:
if cursor:
cursor.close()
if con:
con.close()

lOMoARcPSD|10518309
Ex: No: 4 XML Databases
AIM:

lOMoARcPSD|10518309
To create and extract the XML Documents from relational databases.
INTRODUCTION :
XML database is a data persistence software system used for storing the huge
amount of information in XML format. It provides a secure place to store XML
documents.
XML-enable database works just like a relational database. It is like an

extension provided for the conversion of XML documents. In this database, data is
stored in table, in the form of rows and columns.
Native XML database is preferred over XML-enable database because it is

highly capable to store, maintain and query XML documents.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>

<!DOCTYPE Employee
[
<!ELEMENT address (name,company,phone)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT company (#PCDATA)>
<!ELEMENT phone (#PCDATA)>
]>
<address>
<name>Mayuri S</name>
<company>Tutorial</company>
<phone>91-9800000000</phone>
</address>
Ex: No: 5 MONGODB Database Creation & Insertion

lOMoARcPSD|10518309
AIM :
To create a Database and write a simple query using MongoDB
INTRODUCTION :
MongoDB is a NoSQL, open-source document database that is created using C++. It is

different from other Relational Databases like MySQL and is very high-performance in nature.
One of the most important characteristics of NoSQL databases like MongoDB is that they have
no schema. That is, unlike SQL, we do not specify a structure for the tables/databases.
MongoDB uses documents and collections instead of rows and tables like SQL.
To create a database using the MongoDB shell
MongoDB use DATABASE_NAME is used to create database. The command will

create a new database if it doesn't exist, otherwise it will return the existing database.
Syntax
Basic syntax of use DATABASE statement is as follows −
use DATABASE_NAME
Example
If you want to use a database with name <mydb>, then use DATABASE statement would be
as follows −
> use mydb
switched to db mydb
To check your currently selected database, use the command db
>db
mydb
If you want to check your databases list, use the command show dbs.
>show dbs
local 0.78125GB
test 0.23012GB
Your created database (mydb) is not present in list. To display database, you need to insert at
least one document into it.
>db.emp.insert({"name":"XXX"})
>show dbs
local 0.78125GB
mydb 0.23012GB
test 0.23012GB
In MongoDB default database is test. If you didn't create any database, then collections will be
stored in test database.

lOMoARcPSD|10518309
db.dropDatabase()
This syntax will delete the selected database. In the case you have not selected any database, it
will delete default "test" database.

CP4152 Database Practices Lab Record

Uploaded by

Copyright:

Available Formats

CP4152 Database Practices Lab Record

Uploaded by

Document Information

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

CP4152 Database Practices Lab Record

Uploaded by

Copyright:

Available Formats

lOMoARcPSD|10518309

CP4152 database Practices lab record

Advanced Database Technologies (Anna University)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

SARANATHAN COLLEGE OF ENGINEERING

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

ACADEMIC YEAR 2022-2023

CP4152 DATABASE PRACTICES – INTEGRATED LAB

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

S.NO DATE EXPERIMENT SIGNATURE

1A STUDY OF DATA DEFINITION LANGUAGE

1B STUDY OF DATA MANIPULATION LANGUAGE

1C STUDY OF TRANSCATION CONTROL LANGUAGE

2B SQL FUNCTIONS AND OPERATORS

2C JOIN QUERIES AND SUB QUERIES

3B ACCESSING RELATIONAL DATABASES USING PYTHON

5 MONGODB DATABASE CREATION & INSERTION

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

Data Type Specification Description

integer Number Same as number data type.

float Number Same as number data type.

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

To execute and verify the basic SQL DDL Commands

DDL [Data Definition Language] Commands

1. CREATE TABLE COMMAND

2. MODIFYING THE STRUCTURE OF TABLE

 Deleting an existing column

 Modifying an existing column

 Renaming an existing column

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

3. RENAMING THE TABLE

4. TRUNCATING THE TABLE

5. DESTROYING THE TABLE

6. DISPLAYING THE STRUCTURE OF TABLE

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

To execute and verify the basic SQL DML Commands

DATA MANIPULATION LANGUAGE:

1. INSERTING DATA INTO THE TABLE

b) Inserting values only for a set of columns

c) Inserting values using identifier method

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

b) Removal of a specified rows

3. UPDATING THE CONTENTS OF A FILE TABLE

b) Updating multiple columns (use comma to separate the columns)

c) Updating selected rows

4. VIEWING DATA IN THE TABLE

5. FILTERING TABLE DATA

b) Selected columns and all rows

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

SELECT <col1>,<col2> FROM <table_name>;

c) Selected columns and selected rows

Downloaded by PGPCET Examcell (pgpcetexamcell@gmail.com)

To execute and verify the basic SQL TCL Commands

Transaction Control Language [TCL]

TCL commands are,