Tribhuvan University

BHAKTAPUR MULTIPLE CAMPUS

Doodhpati-17, Bhaktapur

Assignment of
Advanced Database (CSC 461)

Submitted by:
Basanta Lamsal
Roll no: 12 (23228)

Submitted to:
Madan Nath
Contents

Assignment Title
1 Use the given SQL code to populate the database schema and
complete the query given below. (SQL Constraints, Views)

2 Consider the following two tables and answer the following

questions. (Aggregation and Joins)

3 Use the given HR Schema and answer the following questions.

4 Practice DDLs and Queries
5 Practice with Object Types and Collections in Oracle LiveSQL
6 Practice the given queries (Triggers)
7 Indexing and Query Cost Analysis
8 NoSQL Database Simple Examples
9 Object-Oriented Database Support in Oracle Database
Lab 1: Use the given SQL code to populate the database schema and complete
the query given below. (SQL Constraints, Views)

Creation:

CREATE DATABASE

AdvanceDataBaseLab1Basanta; use

AdvanceDataBaseLab1Basanta;

CREATE TABLE DEPARTMENT(

DEPARTMENT_ID integer,
DEPARTMENT_NAME varchar(30),
MANAGER_ID integer,
PRIMARY KEY(DEPARTMENT_ID)
);
--Note : Varchar2 is specific to Oracle. I'm using sql server so, varchar
CREATE TABLE JOB(
JOB_ID integer,
JOB_TITLE varchar(60),
MIN_SALARY decimal(9,2),
MAX_SALARY
decimal(9,2), PRIMARY
KEY(JOB_ID));

CREATE TABLE EMPLOYEE(

EMPLOYEE_ID integer,
FIRST_NAME varchar(30),
MIDDLE_NAME varchar(30),
LAST_NAME varchar(30),
EMAIL varchar(320),
PHONE_NUMBER varchar(15),
HIRE_DATE date,
JOB_ID integer,
SALARY varchar,
COMMISSION decimal(5,2),
MANAGER_ID integer,
DEPARTMENT_ID integer,
PRIMARY KEY (EMPLOYEE_ID),
FOREIGN KEY (JOB_ID) REFERENCES JOB(JOB_ID),
FOREIGN KEY (DEPARTMENT_ID) REFERENCES
DEPARTMENT(DEPARTMENT_ID)
);
CREATE TABLE JOB_HISTORY(
EMPLOYEE_ID integer,
START_DATE date,
END_DATE date,
JOB_ID integer,
DEPARTMENT_ID integer,
PRIMARY KEY (EMPLOYEE_ID, START_DATE),
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),
FOREIGN KEY (JOB_ID) REFERENCES JOB(JOB_ID),
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENT(DEPARTMENT_ID)
);

INSERT INTO DEPARTMENT VALUES (10, 'Administration',100);

INSERT INTO DEPARTMENT VALUES (20, 'Marketing', 101);
INSERT INTO DEPARTMENT VALUES (30, 'Human
Resources',102); INSERT INTO DEPARTMENT VALUES (40,
'Operations',103); INSERT INTO DEPARTMENT VALUES (50,
'Finance',104);
INSERT INTO DEPARTMENT VALUES (60, 'Accounts',105);
INSERT INTO DEPARTMENT VALUES (70, 'IT Support',NULL);
INSERT INTO DEPARTMENT VALUES (80, 'IT
Helpdesk',NULL); INSERT INTO DEPARTMENT VALUES (90,
'Executive',NULL);

INSERT INTO JOB VALUES (1, 'President', 20080, 40000);

INSERT INTO JOB VALUES (2, 'Vice President', 15000, 30000);
INSERT INTO JOB VALUES (3, 'Administrative Assistant', 3000, 6000);
INSERT INTO JOB VALUES (4, 'Finance Manager', 8200, 16000);
INSERT INTO JOB VALUES (5, 'Accountant', 4200, 9000);
INSERT INTO JOB VALUES (6, 'Manager', 8200, 16000);
INSERT INTO JOB VALUES (7, 'Software Engineer', 10000, 20080);
INSERT INTO JOB VALUES (8, 'Principal Engineer', 15000, 30000);
INSERT INTO JOB VALUES (9, 'Programmer', 5000, 10000);
INSERT INTO JOB VALUES (10, 'HR Representative', 4000, 9000);

INSERT INTO EMPLOYEE VALUES (100, 'Steven', NULL, 'King',

'sking','5151234567', '2003-06-17', 1, 24000, NULL, NULL, 10);
INSERT INTO EMPLOYEE VALUES (101, 'Neena', NULL,
'Kochhar', 'nkochhar','5151234440', '2005-09-21', 2, 21000, NULL,
NULL, 20); INSERT INTO EMPLOYEE VALUES (102, 'Lex', NULL,
'De Haan', 'ldehaan','5151234569', '2001-01-03', 2, 17500, NULL,
NULL, 30);
INSERT INTO EMPLOYEE VALUES (103, 'Alaxander', NULL,
'Hunold', 'ahunold','5102234321', '2005-02-07', 3, 5000, NULL, NULL,
10);
INSERT INTO EMPLOYEE VALUES (104, 'Bruce', NULL, 'Ernest',
'bernest','5102234560', '2006-06-28', 4, 12000, NULL, NULL, 20);
INSERT INTO EMPLOYEE VALUES (105, 'David', NULL, 'Lorentz',
'dlorentz','5102234563', '2002-07-02', 5, 8000, NULL, NULL, 30);
INSERT INTO EMPLOYEE VALUES (106, 'Nancy', NULL,
'Greenberg', 'ngreenberg','5151234555', '2004-12-10', 6, 13500, NULL,
NULL, 20); INSERT INTO EMPLOYEE VALUES (107, 'Daniel',
NULL, 'Faviet', 'dfaviet','5151244661', '2003-04-15', 3, 7500, NULL,
NULL,30 );
INSERT INTO EMPLOYEE VALUES (108, 'John', NULL, 'Chen',
'jchen','5151244662', '2001-11-13', 7, 11000, NULL, NULL, 10);
INSERT INTO EMPLOYEE VALUES (109, 'Luis', NULL, 'Popp',
'lpopp','5151244663', '2002-03-18', 8, 27000, NULL, NULL, 20);

Screenshot:
Questions:

Q1: Add a GENDER field to the EMPLOYEE table and add constraints to check that
GENDER is in ('M', 'F', 'O')

Description: This query adds a new column GENDER to the EMPLOYEE table and ensures that
the values are restricted to 'M', 'F', or 'O'.

Query and Screenshot:

Q2: Add constraint in JOB table to check MAX_SALARY is greater or equal to

MIN_SALARY

Description: This query adds a constraint to the JOB table to ensure that the MAX_SALARY is
always greater than or equal to MIN_SALARY.

Query and Screenshot:

Q3: Add constraint in JOB_HISTORY table to check that END_DATE is greater than
START_DATE

Description: This query ensures that the END_DATE in the JOB_HISTORY table is always later
than the START_DATE.

Query and Screenshot:

Q4: Create an index in the EMPLOYEE table using EMPLOYEE_ID,
DEPARTMENT_ID, JOB_ID

Description: This query creates an index on the EMPLOYEE table to improve query
performance for searches based on EMPLOYEE_ID, DEPARTMENT_ID, and JOB_ID.

Query and Screenshot:

Q5: Create a view to display details about the employee

Description: This query creates a view named employee_details to display comprehensive details
about the employees, including full name, department, job title, etc.

Query and Screenshot:

Q6: Create a procedure to display the EMPLOYEE_ID, name, and annual salary of all the
employees

Description: This query creates a stored procedure to display the EMPLOYEE_ID, full name,
and annual salary of all employees.

Query and Screenshot:

Q7: Create a procedure to display the number of employees according to gender

Description: This query creates a stored procedure to count the number of employees based on
their gender.
Query and Screenshot:
 Lab 2: Consider the following two tables and answer the following
questions. (Aggregation and Joins)

Objective: The objective of this lab is to practice SQL queries involving aggregation
functions and joins using two tables: CUSTOMERS and ORDERS.

Creation:
Database Setup:

1. Created a new database named "aDBMSLab2Basanta".

2. Created two tables: CUSTOMERS and ORDERS.
3. Inserted sample data into both tables.

Tasks and Solutions:

Task (a): Join CUSTOMERS and ORDERS tables using a SELECT

statement. Query and Output:

Task (b): Join CUSTOMERS and ORDERS tables using the SELECT statementQuery and
Output:

Task (c): Join CUSTOMERS and ORDERS tables using the SELECT

statement. Query and Output:

 Task (d): Add a new column "SEX" in the CUSTOMERS

table. Query and Output:

Task (e): Drop the "SEX" column from the CUSTOMERS table.

Query and Output:

Task (f): Delete complete data from the CUSTOMERS table using the
TRUNCATE TABLE command.

Query and Output:

 Task (g): Create a View "CUSTOMERS_VIEW" from CUSTOMERS
table selecting name and age.

Query and Output:

Task (h): Display records where the similar age count is more than or equal to 2.

Query and Output:

Task (i): Delete records from the CUSTOMERS table where age is 25 and
COMMIT changes.

Query and Output:

Task (j): Use the ROLLBACK command to undo the DELETE operation
performed in Task (i).

The ROLLBACK command is used to undo transactions that have not been saved to
the database. It undoes the changes made during the transaction.

Query and Output:

Conclusion: In this lab, various SQL queries involving aggregation functions and joins
were executed successfully. The tasks covered different aspects of data manipulation,
including adding and dropping columns, creating views, and performing deletions and
rollbacks.
 LAB-3: Use the given HR Schema and answer the following
questions.

First Make Schemas and insert data into tables:

CREATE TABLE
regions (
region_id INT PRIMARY KEY,
region_name VARCHAR(50)
);

CREATE TABLE
countries (
country_id CHAR(2) PRIMARY
KEY,
country_name VARCHAR(50),
region_id INT,
FOREIGN KEY (region_id)
REFERENCES regions (region_id)
);

CREATE TABLE
locations (
 location_id INT PRIMARY KEY,
street_address VARCHAR(100),
postal_code VARCHAR(100),
city VARCHAR(50),
state_province VARCHAR(50),
country_id CHAR(2),
FOREIGN KEY (country_id)
REFERENCES countries
(country_id)
);

CREATE TABLE
departments (
department_id INT PRIMARY
KEY,
department_name VARCHAR(50),
location_id INT,
manager_id INT,
FOREIGN KEY (location_id)
REFERENCES locations
(location_id)
);

CREATE TABLE
jobs (
job_id VARCHAR(10) PRIMARY
KEY,
job_title VARCHAR(50),
min_salary DECIMAL,
max_salary DECIMAL
);

CREATE TABLE
employees (
employee_id INT PRIMARY
KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(20),
 hire_date DATE,
job_id VARCHAR(10),
salary DECIMAL,
commission_pct DECIMAL,
manager_id INT,
department_id INT,
FOREIGN KEY (job_id)
REFERENCES jobs (job_id),
FOREIGN KEY (manager_id)
REFERENCES employees
(employee_id),
FOREIGN KEY (department_id)
REFERENCES departments
(department_id)
);
CREATE TABLE
job_history (
employee_id INT,
start_date DATE,
end_date DATE,
job_id VARCHAR(10),
department_id INT,
PRIMARY KEY (employee_id,
start_date),
FOREIGN KEY (employee_id)
REFERENCES employees
(employee_id),
FOREIGN KEY (job_id)
REFERENCES jobs (job_id),
FOREIGN KEY (department_id)
REFERENCES departments
(department_id)
);
-- Inserting data into regions
INSERT INTO regions (region_id, region_name) VALUES (1,
'Asia');

INSERT INTO regions (region_id, region_name) VALUES (2,

'Europe');

INSERT INTO regions (region_id, region_name) VALUES (3,

'America');

-- Inserting data into countries

INSERT INTO countries (country_id, country_name, region_id) VALUES
('US', 'United States', 3);
INSERT INTO countries (country_id, country_name, region_id) VALUES
('NP', 'Nepal', 1);
INSERT INTO countries (country_id, country_name, region_id) VALUES
('DE', 'Germany', 2);

-- Inserting data into locations

INSERT INTO locations (location_id, street_address, postal_code, city,
state_province, country_id)
VALUES (1000, '123 Main St', '12345', 'New York', 'NY', 'US');

INSERT INTO locations (location_id, street_address, postal_code, city,

state_province, country_id)
VALUES (1001, '456 Elm St', '67890', 'Berlin', 'Berlin', 'DE');

INSERT INTO locations (location_id, street_address, postal_code, city,

state_province, country_id)
VALUES (1002, 'Doodhpati', '44800', 'Bhaktapur', '3', 'NP');
-- Inserting data into departments
INSERT INTO departments (department_id, department_name,
location_id, manager_id)
VALUES (10, 'HR', 1000, NULL);

INSERT INTO departments (department_id, department_name,

location_id, manager_id)
VALUES (20, 'IT', 1002, NULL);

INSERT INTO departments (department_id, department_name,

location_id, manager_id)
VALUES (30, 'Finance', 1001, NULL);

-- Inserting data into jobs

INSERT INTO jobs (job_id, job_title,
min_salary, max_salary)
VALUES ('J1', 'Manager', 5000, 10000);
INSERT INTO jobs (job_id, job_title,
min_salary, max_salary)
VALUES ('J2', 'Developer', 3000, 8000);
INSERT INTO jobs (job_id, job_title,
min_salary, max_salary)
VALUES ('J3', 'Analyst', 4000, 9000);

-- Inserting data into employees

INSERT INTO employees (employee_id, first_name, last_name, email,
phone_number, hire_date, job_id, salary, commission_pct, manager_id,
department_id)

VALUES (1, 'John', 'Doe', '[Link]@[Link]', '123-456-7890', '2020-

01-15', 'J1', 9000, NULL, NULL, 10);

INSERT INTO employees (employee_id, first_name, last_name, email,

phone_number, hire_date, job_id, salary, commission_pct, manager_id,
department_id)

VALUES (2, 'Test', 'Bahadur', '[Link]@[Link]', '123-456-7890',

'2020-01-15', 'J1', 15000, NULL, NULL, 10);

INSERT INTO employees (employee_id, first_name, last_name, email,

phone_number, hire_date, job_id, salary, commission_pct, manager_id,
department_id)

VALUES (3, 'Bahadur', 'Tester', '[Link]@[Link]', '234-567-

8901', '2019-03-20', 'J2', 10000, 0.10, 1, 20);
INSERT INTO employees (employee_id, first_name, last_name, email,
phone_number, hire_date, job_id, salary, commission_pct, manager_id,
department_id)

VALUES (4, 'Jane', 'Smith', '[Link]@[Link]', '234-567-8901',

'2019-03-20', 'J2', 7000, 0.10, 1, 20);

INSERT INTO employees (employee_id, first_name, last_name, email,

phone_number, hire_date, job_id, salary, commission_pct, manager_id,
department_id)

VALUES (5, 'Very', 'Bahadur', '[Link]@[Link]', '345-678-9012',

'2021-06-25', 'J3', 9000, NULL, 1, 30);

INSERT INTO employees (employee_id, first_name, last_name, email,

phone_number, hire_date, job_id, salary, commission_pct, manager_id,
department_id)

VALUES (6, 'Sad', 'Bahadur', '[Link]@[Link]', '345-678-9012',

'2021-06-25', 'J3', 6000, NULL, 1, 30);

-- Inserting data into job_history

INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
VALUES (1, '2018-01-01', '2019-12-31', 'J2', 20);
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
VALUES (2, '2018-01-01', '2019-12-31', 'J2', 20);
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
VALUES (3, '2017-05-01', '2018-04-30', 'J3', 30);
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
VALUES (4, '2017-05-01', '2018-04-30', 'J3', 30);
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
VALUES (5, '2019-08-01', '2020-07-31', 'J1', 10);
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
VALUES (6, '2019-08-01', '2020-07-31', 'J1', 10);
SQL Queries:

1. Employee Details:

Write a SQL query to find the first name, last name, department number,

and department name for each employee.

2. Department Locations:Write a SQL query to display the department name, city,
and state/province for each department.

3. Salary Differential:

Write a SQL query to find the difference between the maximum salary of the

job and salary of the employees. Return job title, employee name, and salary

difference.
 4. Departmental Statistics:Write a SQL query to compute the average salary, number
of employees who received commission in that department. Return department name,
average salary, and number of employees in alphabetical order of departments.

5. Employees Above Average Salary:

Write a SQL query that returns data about employees whose salaries exceed

their department average.

6. Department Salary Extremes:Write a SQL query to return the

minimum and maximum salaries for the employees in each department

whose lowest salary is less than $5,000.

 LAB-4: Practice DDLs and Queries
Note: Use Oracle LiveSQL to complete the assignments. Oracle LiveSQL

Objective:

The objective of this lab is to practice the creation of database objects such as types,
tables, and procedures in Oracle LiveSQL, and perform basic operations including
insertion and querying of data.

Steps Taken:

1. Declare Classes:
○ address_typ: Defined as an object type representing an address.

2. phone_typ: Defined as an object type representing phone numbers.

3. employee_typ4: Defined as an object type representing employee information
including address and member functions.

4. Define Class Body:

○ Defined member functions for the employee_typ4 type.

5. Create Object Table:

○ Created a table employee_table to store employee data using the
employee_typ4 type.
6. Insert Records:
○ Inserted sample employee data into the employee_table.

7. Select Records:
○ Queried employee records from the employee_table.
sql

Copy code
 Practice Queries:
1. Retrieve Employee IDs and Names:

[Link] Employee Email Addresses:

3. Retrieve Employees by Job ID:

 4. Update Employee Salary:

Conclusion:

This lab exercise provided hands-on experience in creating database objects such as
object types and tables in Oracle LiveSQL. It also involved the insertion of sample data
into the created table and querying the data to verify the successful execution of the
operations. The included practice queries and answers further reinforce learning and
understanding of the concepts covered in the lab.
 LAB-5: Practice with Object Types and Collections in Oracle LiveSQL

Title: Practice the Given DDLs and Queries

Introduction

This lab explores Object-Relational features in Oracle by defining object types and
manipulating them using SQL statements. The provided code creates a sample schema for
purchase orders, customers, and stock items. It then demonstrates queries to retrieve and
analyze data related to purchase orders.

Materials

● Oracle Live SQL ([Link]

Lab Tasks

The provided code outlines several tasks achieved using Oracle Live SQL. Here's a
breakdown of each code snippet:

1. Object Type Definitions:

○ StockItem_objtyp: Defines the structure of a stock item, including
attributes like StockNo, Price, and TaxRate.
○ LineItem_objtyp: Defines the structure of a line item in a purchase order,
with attributes like LineItemNo, Stock_ref (a reference to a
StockItem_objtyp), Quantity, and Discount.
○ PurchaseOrder_objtyp: Defines the structure of a purchase order,
including attributes like PONo, Cust_ref (a reference to a
Customer_objtyp), OrderDate, ShipDate, LineItemList_ntab (a nested table
of LineItem_objtyp), and ShipToAddr_obj (an address object).
○ PhoneList_vartyp: Defines a varying array type to store phone numbers.
○ Address_objtyp: Defines the structure of an address with attributes like
Street, City, State, and Zip.
2. Creating Object Tables:
○ Customer_objtab: Stores instances of the Customer_objtyp object type. It
includes a primary key constraint on the Custno attribute.
○ Stock_objtab: Stores instances of the StockItem_objtyp object type. It
includes a primary key constraint on the StockNo attribute.
○ PurchaseOrder_objtab: Stores instances of the PurchaseOrder_objtyp
object type. It includes a primary key constraint on the PONo attribute and
a foreign key constraint referencing the Customer_objtab table.

3. Adding Scope for REF column:

○ The SCOPE FOR clause is added to the PoLine_ntab table to define the
scope of the Stock_ref column, specifying that it references the
Stock_objtab table.
4. Creating Index:
○ An index named Po_nidx is created on the NESTED_TABLE_ID column
of the PoLine_ntab table.

5. Inserting Sample Data:

○ Sample data is inserted into the Stock_objtab and Customer_objtab
tables.
 6. Creating Member Functions and Procedures:

○
Member functions and procedures are defined for the PurchaseOrder_objtyp
object type to perform various operations, including calculating the total
cost of line items (sumLineItems), retrieving the purchase order number
(getPONo), adding a new line item (purchase_item), updating a line item
(update_item), and displaying purchase order details (display).

Queries:
Several SQL queries are provided to retrieve specific information from the object tables,
such as line items and customer information for a purchase order, the total value of each
purchase order, line items and purchase numbers for specific stock items, and customer
phone numbers.
1. Retrieve Line Items and Customer Name for Each Purchase Order (2001):
2. Retrieve Total Value for Each Purchase Order:

3. Retrieve Line Items and Purchase Numbers for StockNo 1535:

4. Display Customer Phone Numbers:

 Conclusion:
This Lab demonstrates the use of object-oriented features in Oracle, including user-defined
object types, object tables, nested tables, object references, member functions, and
procedures. These features allow developers to model complex data structures and
relationships in the database more effectively.
 LAB-6: Practice the given queries (Triggers)

Step-by-Step Instructions

1. Create employee_salary Table

Create a table employee_salary with columns for employee ID, salary, and employee
name.

2. Create employee_salary_log Table

Create a table employee_salary_log to log salary updates, including columns for

employee ID, new salary, update date, and updated by.
3. Create Trigger trg_log_employee_salary

Create a trigger trg_log_employee_salary that will be executed after an update on the

SALARY column of the employee_salary table. This trigger will log the changes into the
employee_salary_log table.

4. Insert Data into employee_salary

Insert an employee record into the employee_salary table.

5. Select Data from employee_salary

Select all data from the employee_salary table to verify the insertion.

6. Update SALARY in employee_salary

Update the salary of the employee with EMP_ID 101.

7. Select Data from employee_salary

Select all data from the employee_salary table to verify the update.

8. Select Data from employee_salary_log

Select all data from the employee_salary_log table to verify the trigger execution and log
entry.

This completes the lab for practicing SQL queries involving triggers. Follow each step carefully
to ensure correct execution and logging of salary updates.
 LAB-7: Indexing and Query Cost Analysis
Create 4/5 tables, use indexes and non-index columns, and verify the query cost for such indexed and non-
index columns inserting millions of records.

Objective

In this lab, we will:

● Create multiple tables.

● Use indexes and non-index columns.
● Insert millions of records.
● Verify the query cost for indexed and non-index columns.

Step-by-Step Instructions

Step 1: Create Tables

We will create four tables to simulate a larger database environment. The primary table is
verylargetable, and additional tables are large_table_2, large_table_3, and large_table_4.
 Step 2: Insert Data

We will insert a large number of records into the verylargetable table using the all_objects
system view to generate data.

Step 3: Query Without Index

We will perform a query on the verylargetable table without an index and check the query
cost using the EXPLAIN PLAN statement.
 Step 4: Create Index

Next, we create an index on the fname column in the verylargetable table.

Step 5: Query With Index

We perform the same query on the verylargetable table with the index and check the
query cost.
Typically, the query cost should be lower with the index, indicating a more efficient query
execution.

Conclusion

This lab demonstrates how indexing can significantly improve query performance by
reducing the query cost. The EXPLAIN PLAN statement helps in analyzing the execution
plans and understanding the impact of indexing on query efficiency.
 LAB-8 NoSQL Database Examples
Objective:
The objective of this lab is to demonstrate basic CRUD (Create, Read, Update,
Delete) operations using MongoDB.

Step 1: Create Account on MongoDB Atlas

Visit MongoDB Atlas and create a free account.

Step 2: Install MongoDB on Visual Studio Code

In Visual Studio Code, click the Extensions icon in the left navigation.

Procedure

1. Open the Extensions View.

In Visual Studio Code, click the Extensions icon in the left navigation.

2. Search "MongoDB for VS Code" in the extension marketplace.

3. Click Install on the "MongoDB for VS Code" extension.

Once the installation is complete, the Install button changes to the Manage
gear Button. Once you install MongoDB for VS Code, you can view data in your
deployment and create Playgrounds to interact with your data.

To know your connection string to connect to VScode:

● Click on Database which is below Deployment.
● Choose your cluster.
 ● Click on the connect button.
● Click on MongoDB for vscode
● Copy this connection string.

To Connect Cloud MongoDB to vscode:

● Click on the leaf icon as shown in figure below.
● Click on Create New Playground.
● Clock on Play icon on the right top.
● Click on Click here to change Connection as shown in figure[3].

● The top search bar will appear and click on Add new Connection.
● Paste your connection string there and don't forget to put your password in
the blurred section.

● Click Enter and you are connected to the remote mongoDB cluster.

Start MongoDB Shell:

Open terminal in your VSCode and paste it:
brew install mongosh
Run your connection string in your command line
Use this connection string in your application
Eg : mongosh "mongodb+srv://[Link]/" --apiVersion 1 --
username userAdb
Enter your password, now you are connected via terminal.

To Run CRUD Operations:

Create or Switch to a Database:

use aDBMS_practice_userAdb

Create a Collection:

[Link]('sales')

Insert Documents:

[Link]([
{ '_id': 1, 'item': 'abc', 'price': 15, 'quantity': 3, 'date': new Date('2024-03-
02T[Link]Z')},
{ '_id': 2, 'item': 'jkl', 'price': 25, 'quantity': 2, 'date': new Date('2024-03-
02T[Link]Z') },
{ '_id': 3, 'item': 'xyz', 'price': 8, 'quantity': 15, 'date': new Date('2024-03-
16T[Link]Z')},
{ '_id': 4, 'item': 'xyz', 'price': 8, 'quantity': 25, 'date': new Date('2024-04-
05T[Link].736Z') },
{ '_id': 5, 'item': 'abc', 'price': 15, 'quantity': 15, 'date': new Date('2024-04-
05T[Link].331Z') },
{ '_id': 6, 'item': 'def', 'price': 10, 'quantity': 7, 'date': new Date('2024-05-
05T[Link]Z')},
{ '_id': 7, 'item': 'def', 'price': 10, 'quantity': 12, 'date': new Date('2024-06-
01T[Link]Z')},
{ '_id': 8, 'item': 'abc', 'price': 15, 'quantity': 7, 'date': new Date('2024-02-
07T[Link]Z') }
])
Query Documents:
[Link]().pretty()
Update a Document:
[Link]({ _id: 2 }, { $set: { item: 'abcdefg' } })

[Link]().pretty()
 Delete a Document:
[Link]({ _id: 1 })
[Link]().pretty()

By following these steps and examples, basic CRUD operations can be performed
using MongoDB.
LAB-9: Object-Oriented Database Support in Oracle Database

Objective

In this lab, we explore the object-oriented database support in Oracle Database. We will
create user-defined types (UDTs) to represent complex data structures and use them in
tables. Additionally, we will define methods within the UDTs to encapsulate behavior.

Step-by-Step Instructions

Step 1: Create User-Defined Types (UDTs)

We define two object types (address_type and phone_type) to represent address and phone
number information, respectively. We also create the employee_type object type to
encapsulate emp loyee data along with their address.
Step 2: Define Methods for UDTs

We define methods within the employee_type object type to encapsulate behavior. In this
case, we define get_idno() to return the employee ID and display_address() to display the
employee's address.
 Step 3: Create Table Using Object Type

We create a table employee_table of type employee_type.

Step 4: Insert Data

We insert data into the employee_table using the INSERT INTO ... VALUES statement.

Step 5: Query Data

Retrieve and display the data from the employee_table.

This PL/SQL block retrieves each record from the employee_table one by one using a
cursor loop, and then displays the details of each employee including their ID, name,
address, and phone number.

Conclusion

This lab demonstrates the usage of object-oriented database features in Oracle Database,
including the creation of user-defined types (UDTs), defining methods within UDTs, and
utilizing UDTs in tables. By encapsulating data and behavior within object types, Oracle
Database offers a more flexible and intuitive way to model complex data structures and
relationships.