Tribhuvan University
Amrit Campus
Lab Report – Advance Database
(Subject Teacher: Narayan Chalise)
Submitted by
Yubaraj Subedi (20213/075)
Submitted to
Department of Computer Science and Information Technology
Amrit Campus
Institute of Science and Technology
Tribhuvan University
Table of Contents
1. Data Definition Language (DDL) Commands ..................................................................................... 1
2. Data Manipulation Language (DML) Commands .................................................................................. 3
3. Nested and Join Queries ...................................................................................................................... 5
4. Perform the String Functions, Date Functions, and Mathematical functions supported by Mysql ........ 8
5. Create an index and compare data retrieval with an index and without an index. ............................... 9
6. Create a function to calculate bonuses based on basic salary and execute while performing the
selection operation. .............................................................................................................................. 11
7. Create a simple stored Procedure for calculating factorial and execute it by passing different values.
............................................................................................................................................................. 11
8. Create a Stored Procedure to calculate the total mark, percentage, and grade of the student based on
multiple subject marks. ......................................................................................................................... 12
9. Perform CRUD operation on Object-Oriented database using ODL and OQL. ..................................... 14
10. Perform query optimization by retrieving/joining multiple tables and comparing them. ................. 15
11. Perform CRUD operation on MongoDB. ........................................................................................... 16
12. Perform CRUD operation on Redis. .................................................................................................. 17
13. Install Hadoop and perform some basic file operations in HDFS....................................................... 18
14. Write a MapReduce word count program in Java and execute it against Hadoop. Input should be text
files and output should be unique words with their occurrence count. .................................................. 19
15. Perform all types of triggers i.e. row level and statement level as well as before and after triggers on
insert, update, and delete operations. .................................................................................................. 23
16. Perform CRUD operation to show temporal database concepts. ...................................................... 30
17. Perform CRUD operation to show spatial database concepts. .......................................................... 34
1. Data Definition Language (DDL) Commands
A) Create a table called EMP with the following structure.
Name Type
---------- -------------
EMPNO NUMBER(6)
ENAME VARCHAR(20)
JOB VARCHAR(10)
DEPTNO NUMBER(3)
SAL NUMBER(7,2)
Allow NULL for all columns except ename and job. empno as the primary key.
B) Add a column experience to the emp table with data type number and allow null.
C) Modify the column width of the job field of the emp table.
1
D) Create a dept table with the following structure.
Name Type ------------ -----------
DEPTNO NUMBER(2) DNAME VARCHAR(10) LOC VARCHAR(10) Deptno as the primary key.
E) Create the emp1 table with ename and empno, add constraints to check the empno value
while entering (i.e) empno > 100.
OUTPUT:
DEPT, EMP and EMP1 Table will be created as shown below with provided
2
2. Data Manipulation Language (DML) Commands
A) Insert more than a record into the emp table using a single insert command.
Output:
B) Update the emp table to set the salary of all employees to Rs15000/- who are working as
ASP.
3
C) Create a pseudo table employee with the same structure as the table emp and insert rows
into the table using select clauses.
D) Delete only those who are working as lecturers.
E) List the records in the emp table orderby salary in descending order.
Output:
This will show the data in descending order on the basis of salary
4
3. Nested and Join Queries
A) Display all employee names and salary whose salary is greater than the company's
minimum salary and job title starts with 'A‘.
Output: It’s showing blank as there is no data as mentioned
B) Display the details of those who draw a salary greater than the average salary.
Output:
5
C) Write a query to perform left outer join.
Output:
D) Write a query to perform the right outer join.
Output:
6
E) Write a query to perform a full outer join.
Output:
7
4. Perform the String Functions, Date Functions, and Mathematical
functions supported by Mysql
String Function:
Query
Output:
Date Function:
Query
Output
Mathematical Function:
Query
Output:
8
5. Create an index and compare data retrieval with an index and
without an index.
Query:
Output:
Retrieve data without index:
Output:
9
Retrieve Data without index:
Output:
Comparing both methods:
Without Index:
With Index:
10
6. Create a function to calculate bonuses based on basic salary and
execute while performing the selection operation.
Employee (id, name, address, basic_salary, designation)
Query
Output:
7. Create a simple stored Procedure for calculating factorial and
execute it by passing different values.
Query
Output:
11
8. Create a Stored Procedure to calculate the total mark, percentage,
and grade of the student based on multiple subject marks.
Student(rollno, name, course,sub1_mark,sub2_mark,sub3_mark,
sub4_mark,sub5_mark,total_Mark,percentage,grade)
Query:
12
Once the procedure invoked invoking Call the output on the student table will be:
13
9. Perform CRUD operation on Object-Oriented database using ODL
and OQL.
Assume we have an OODB for managing books, and the book schema is defined in ODL as follows:
For CRUD:
1) Create (Insert) Operation using ODL
2) Read (Query) Operation using OQL:
3) Update operation using ODL:
4) Delete Operation using OQL:
14
10. Perform query optimization by retrieving/joining multiple tables
and comparing them.
First of all, let's create the table:
Non-Optimized Query:
Optimized Query:
15
11. Perform CRUD operation on MongoDB.
Create (Insert) Operation:
Read (Query) Operation:
Update Operation:
16
Delete Operation:
12. Perform CRUD operation on Redis.
Create (Insert) Operation:
Read (Retrieve) Operation:
Update Operation:
Delete Operation:
17
13. Install Hadoop and perform some basic file operations in HDFS.
File operations in HDFS.
A) List Files and Directories:
Command: hdfs dfs -ls /
Output:
B) Create a Directory:
Command: hdfs dfs -mkdir /mydir
C) Upload (Copy) a File to HDFS
Command: hdfs dfs -copyFromLocal [Link] /mydir/
D) Remove a File Directory:
To remove file command is hdfs dfs -rm /mydir/[Link]
To remove file and its content the command is hdfs dfs -rm -r /mydir
Etc
18
14. Write a MapReduce word count program in Java and execute it
against Hadoop. Input should be text files and output should be unique
words with their occurrence count.
Source Code:
Mapper Class:
import [Link];
import [Link].*;
import [Link].*;
public class WordCountMapper extends Mapper<LongWritable, Text, Text, IntWritable> {
private final static IntWritable one = new IntWritable(1);
private Text word = new Text();
@Override
public void map(LongWritable key, Text value, Context context)
throws IOException, InterruptedException {
String line = [Link]();
String[] words = [Link]("\\s+"); // Split by whitespace
for (String w : words) {
[Link](w);
[Link](word, one);
}
19
Reducer Class:
import [Link];
import [Link].*;
import [Link].*;
public class WordCountReducer extends Reducer<Text, IntWritable, Text, IntWritable> {
private IntWritable result = new IntWritable();
@Override
public void reduce(Text key, Iterable<IntWritable> values, Context context)
throws IOException, InterruptedException {
int sum = 0;
for (IntWritable val : values) {
sum += [Link]();
[Link](sum);
[Link](key, result);
Configuring the Handoop Job:
import [Link];
import [Link];
import [Link].*;
20
import [Link].*;
public class WordCount {
public static void main(String[] args) throws Exception {
Configuration conf = new Configuration();
Job job = [Link](conf, "word count");
[Link]([Link]);
[Link]([Link]);
[Link]([Link]);
[Link]([Link]);
[Link]([Link]);
[Link](job, new Path(args[0])); // Input directory
[Link](job, new Path(args[1])); // Output directory
[Link]([Link](true) ? 0 : 1);
Compile and Package the Java Code:
javac -classpath $(hadoop classpath) -d ./build src/*.java
jar -cvf [Link] -C ./build .
21
Run the Hadoop Job
hadoop jar [Link] WordCount /user/yubarajsubedi/input /user/yubarajsubedi/output
View Result:
hadoop fs -cat /user/yubarajsubedi/output/part-r-00000
Input:
Hello world
Hello Hadoop
Hadoop is fun
Hello MapReduce
MapReduce is powerful
Output:
22
15. Perform all types of triggers i.e. row level and statement level as
well as before and after triggers on insert, update, and delete
operations.
Row-Level Triggers:
Before Insert Row-Level Trigger (Sample Logic):
This trigger logs the new employee's name and action.
SQL code:
DELIMITER //
CREATE TRIGGER before_insert_row_trigger
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_log (event_description)
VALUES (CONCAT('New employee added: ', [Link]));
END;
//
DELIMITER ;
After Insert Row-Level Trigger (Sample Logic):
This trigger updates the employee count in another table.
SQL Code
DELIMITER //
CREATE TRIGGER after_insert_row_trigger
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
23
UPDATE employee_count SET count = count + 1;
END;
//
DELIMITER ;
Before Update Row-Level Trigger (Sample Logic):
This trigger prevents salary reduction.
SQL code
DELIMITER //
CREATE TRIGGER before_update_row_trigger
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
IF [Link] < [Link] THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary reduction is not allowed.';
END IF;
END;
//
DELIMITER ;
After Update Row-Level Trigger (Sample Logic):
This trigger logs the salary change.
SQL Code:
DELIMITER //
CREATE TRIGGER after_update_row_trigger
AFTER UPDATE ON employee
24
FOR EACH ROW
BEGIN
INSERT INTO salary_change_log (employee_id, old_salary, new_salary)
VALUES ([Link], [Link], [Link]);
END;
//
DELIMITER ;
Before Delete Row-Level Trigger (Sample Logic):
This trigger prevents deleting employees with high-performance ratings.
SQL Code:
DELIMITER //
CREATE TRIGGER before_delete_row_trigger
BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
IF OLD.performance_rating > 4.0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot delete high-performing employees.';
END IF;
END;
//
DELIMITER ;
After Delete Row-Level Trigger (Sample Logic):
25
This trigger updates the employee count.
SQL Code:
DELIMITER //
CREATE TRIGGER after_delete_row_trigger
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
UPDATE employee_count SET count = count - 1;
END;
//
DELIMITER ;
Statement-Level Triggers:
Before Insert Statement-Level Trigger (Sample Logic):
This trigger logs the number of employees being inserted.
SQL Code:
DELIMITER //
CREATE TRIGGER before_insert_statement_trigger
BEFORE INSERT ON employee
BEGIN
INSERT INTO employee_log (event_description)
VALUES (CONCAT('Inserting ', (SELECT COUNT(*) FROM NEW)));
END;
//
DELIMITER ;
After Insert Statement-Level Trigger (Sample Logic):
This trigger updates the total employee count.
26
SQL Code
DELIMITER //
CREATE TRIGGER after_insert_statement_trigger
AFTER INSERT ON employee
BEGIN
UPDATE employee_count SET count = (SELECT COUNT(*) FROM employee);
END;
//
DELIMITER ;
Before Update Statement-Level Trigger (Sample Logic):
This trigger logs the number of rows being updated.
SQL Code:
DELIMITER //
CREATE TRIGGER before_update_statement_trigger
BEFORE UPDATE ON employee
BEGIN
INSERT INTO employee_log (event_description)
VALUES (CONCAT('Updating ', (SELECT COUNT(*) FROM OLD)));
END;
//
DELIMITER ;
After Update Statement-Level Trigger (Sample Logic):
This trigger logs the update timestamp.
SQL Code
DELIMITER //
27
CREATE TRIGGER after_update_statement_trigger
AFTER UPDATE ON employee
BEGIN
INSERT INTO update_timestamp_log (update_time)
VALUES (NOW());
END;
//
DELIMITER ;
Before Delete Statement-Level Trigger (Sample Logic):
This trigger logs the number of rows being deleted.
SQL Code:
DELIMITER //
CREATE TRIGGER before_delete_statement_trigger
BEFORE DELETE ON employee
BEGIN
INSERT INTO employee_log (event_description)
VALUES (CONCAT('Deleting ', (SELECT COUNT(*) FROM OLD)));
END;
//
DELIMITER ;
After Delete Statement-Level Trigger (Sample Logic):
This trigger updates the total employee count.
SQL Code
DELIMITER //
CREATE TRIGGER after_delete_statement_trigger
AFTER DELETE ON employee
28
BEGIN
UPDATE employee_count SET count = (SELECT COUNT(*) FROM employee);
END;
//
DELIMITER ;
Output:
The "output" of these triggers is the effect they have on the database, such as logging data,
preventing certain actions, or updating counts in other tables. You would typically examine the data
in the specified log tables or check if certain actions are blocked by the triggers to understand their
impact.
29
16. Perform CRUD operation to show temporal database concepts.
For this example, let's create a "employees" temporal database with the following structure:
SQL code
In this table, we store employee records with a "start_date" and an "end_date" to track when
employees join and leave the company.
Output:
1. Create (Insert) Operation:
SQL Code:
-- Insert a new employee record
30
This inserts a new employee record for John Doe, who joined on January 15, 2023, and has no end
date, indicating that he is currently employed.
Output:
2. Read (Select) Operation:
To read the current employees:
SQL Code:
-- Select current employees
Output:
31
SQL Code:
-- Select employees as of a specific date
This query retrieves employees who were employed as of September 20, 2023, or had no end date at
that time.
Output:
3. Update Operation:
To update an employee's salary:
SQL Code
-- Update an employee's salary
Output:
32
4. Delete Operation:
To mark an employee as terminated (set an end date):
SQL Code
-- Terminate an employee
Output:
33
17. Perform CRUD operation to show spatial database concepts.
1. Create (Insert) Operation:
Let's create a simplified spatial database to store geographical locations of cities with their names
and coordinates.
SQL code
Output:
To insert a city with its coordinates:
SQL Code:
-- Insert a new city
34
This inserts a new city "New York" with its latitude and longitude coordinates.
Output:
2. Read (Select) Operation:
To select all cities:
SQL code
-- Select all cities
This retrieves all cities and their coordinates.
35
Output:
To find cities within a specific radius from a given point (e.g., within 50 kilometers from a reference
point):
SQL Code
-- Select cities within a radius from a reference point
This query calculates the distance between each city's location and a reference point (New York) and
selects cities within a 50-kilometer radius.
Output:
3. Update Operation:
To update the location of a city (e.g., change New York's coordinates):
SQL Code
36
-- Update New York's location
This updates the location of "New York" to new coordinates.
Output:
4. Delete Operation:
To delete a city (e.g., remove "New York" from the database):
SQL code
-- Delete New York from cities
This deletes the "New York" city record from the database.
Output:
37