NAME: Akshara Srivastava

REG NO:23BAI10585 DBMS LAB ASSESMENT

PRACTICAL-1
AIM
To practice and demonstrate proficiency in formulating SQL queries to retrieve specific information
from relational databases, using an airline flight management system schema. The objective includes:

 Performing join operations across multiple relations.

 Applying filtering conditions, subqueries, and set operations.

 Handling complex queries involving universal quantification and negation.

 Identifying key relationships between flights, pilots, aircraft, and certifications.

PROCEDURE
1. Understand the Schema:
Review the structure of the following relational tables:

o Flights(flno, from, to, distance, departs, arrives)

o Aircraft(aid, aname, cruisingrange)

o Certified(eid, aid)

o Employees(eid, ename, salary)

2. Analyze the Relationships:

o Note that only pilots are certified, and every pilot is associated with at least one
aircraft in the Certified table.

o Aircraft and Flights are connected through the distance and cruisingrange attributes.

3. Formulate SQL Queries:

o Use INNER JOINS and WHERE clauses to match rows across multiple tables.

o Apply string pattern matching (LIKE '%Boeing%') for identifying specific aircraft.

o Use subqueries and nested SELECT statements to handle complex conditions like:

 Universal quantification (e.g., all pilots with salary > 100000).

 Negation (e.g., pilots not certified for Boeing aircraft).

4. Test the Queries:

o Execute the SQL statements in a DBMS environment like MySQL, PostgreSQL, or

Oracle.
 o Verify results for accuracy and correctness against expected outcomes.

5. Optimize and Document:

o Refactor queries for clarity and efficiency.

o Record the output and include the SQL syntax, result set, and brief explanation for
each query in the report.

CODES

OUTPUT

PRACTICAL-2
AIM:
To write and execute SQL queries for retrieving, filtering, and analyzing data from the given relational
database schema involving SAILORS, BOATS, and RESERVES tables. The objective is to gain hands-on
experience with SQL operations such as SELECT, JOIN, GROUP BY, ORDER BY, aggregate functions,
subqueries, and set operations.

PROCEDURE:
1. Understand the Schema:
o Analyze the structure and relationships of the following tables:

 SAILORS(SID, SNAME, RATING, AGE)

 BOATS(BID, BNAME, COLOR)

 RESERVES(SID, BID, DAY)

2. Set Up the Database:

o Create the tables using SQL CREATE TABLE statements.

o Insert sample data using INSERT INTO statements to populate the tables for query
testing.

3. Write SQL Queries:

o Use appropriate SQL statements to solve each query requirement.

o Apply:

 Selection (WHERE)

 Projection (SELECT)

 Joins (INNER JOIN, LEFT JOIN)

 Grouping and Aggregation (GROUP BY, COUNT, MAX, AVG, etc.)

 Subqueries (IN, ANY, ALL, NOT IN)

 Sorting (ORDER BY)

4. Execute the Queries:

o Run each SQL query on the database system (e.g., MySQL, PostgreSQL, SQLite).

o Verify the output for accuracy based on the sample data.

5. Analyze the Output:

o Interpret the results of each query to ensure it meets the expected outcome.

o Modify the queries if the output is not as expected.

6. Document Results:
 o Record the query along with its result or expected output.

o Reflect on the purpose and SQL concepts used in each query.

CODES
OUTPUT
 PRACTICAL-3
AIM
To design a relational database for a wholesale furniture company that captures essential business
entities such as furniture, customers, sales, and time, and supports analytics on furniture types,
categories, materials, customer location, quantity sold, income, and discounts.

ENTITY-RELATIONSHIP MODEL (ERD)

You can imagine the following tables (entities and relationships):

1. Furniture Table

Column Name Data Type Description

FurnitureID INT (PK) Unique ID for each furniture item

Name VARCHAR Furniture name (e.g., "Modern Sofa")

Type VARCHAR Type (e.g., chair, table, etc.)

Category VARCHAR Category (e.g., kitchen, office)

Material VARCHAR Material (e.g., wood, marble)

UnitPrice DECIMAL Price per unit

2. Customers Table

Column Name Data Type Description

CustomerID INT (PK) Unique ID for each customer

Name VARCHAR Customer name

City VARCHAR City of residence

Region VARCHAR Region of residence

State VARCHAR State of residence

3. Time Table
Column Name Data Type Description

TimeID INT (PK) Unique ID for the time record

Date DATE Date of sale

Month VARCHAR Month of sale

Quarter VARCHAR Quarter (e.g., Q1, Q2, Q3)

Year INT Year of sale

4. Sales Table (Fact Table)

Column Name Data Type Description

SaleID INT (PK) Unique Sale ID

CustomerID INT (FK) Linked to Customers table

FurnitureID INT (FK) Linked to Furniture table

TimeID INT (FK) Linked to Time table

Quantity INT Number of units sold

Discount DECIMAL Discount offered on sale (e.g., 0.10 for 10%)

TotalIncome DECIMAL Calculated as (UnitPrice * Quantity) - DiscountAmount

PRACTICAL-4
AIM:
To develop a PL/SQL program using an implicit cursor that retrieves and displays the expected arrival
date, quantity expected, item description, color, and quantity on hand for a given Ship ID from the
Shipments table.

PROCEDURE:
1. Create the Database Table:

o Define the structure of the Shipments table with appropriate fields and data types to
store shipment information like Ship_id, Date_expected, Qty_expected, Description,
Color, Qty_hand, and Itemrate.

2. Insert Sample Data:

o (Optional) Add sample records into the table to test the PL/SQL code.

3. Write the PL/SQL Program:

o Use an implicit cursor with a FOR loop to retrieve shipment details based on a user-
supplied Ship ID.

o Use DBMS_OUTPUT.PUT_LINE to display the output.

4. Execute the Program:

o Run the PL/SQL code in an environment like SQL*Plus or Oracle SQL Developer.

o The program prompts for a Ship ID and displays the corresponding shipment
information.

5. Test with Various Inputs:

o Validate the functionality by providing different Ship IDs, including those that may
not exist, to ensure robust behavior.

CODES

OUTPUT

PRACTICAL-5
AIM:
To implement an audit trail mechanism using triggers that logs deleted and updated records from the
Client_master table into the auditclient table. This ensures data traceability and accountability.

PROCEDURE:
1. Create the original Client_master table.
2. Create the auditclient table to store audit logs with fields:
o client_no, name, bal_due, operation (UPDATE/DELETE), userid, and updated_on
(timestamp).

3. Create two triggers:

o One for BEFORE DELETE on Client_master.

o One for BEFORE UPDATE on Client_master.

4. Each trigger should:

o Insert the original data into the auditclient table.

o Capture the user ID using the USER function.

o Capture the current timestamp using SYSDATE.

CODES
OUTPUT

PRACTICAL-6
AIM:
To develop a PL/SQL program using an explicit cursor that:

 Reads and displays all parts from the Parts table (ordered by part number),

 Deletes every tenth part while iterating,

 Commits the transaction after each tenth deletion,

 Demonstrates the use of SAVEPOINT, COMMIT, and optionally ROLLBACK,

 Uses foreign key constraint with ON DELETE CASCADE to maintain referential integrity with
related tables (e.g., SupplierParts).

PROCEDURE:
1. Ensure that the Parts table exists and has a related table like SupplierParts referencing it with
ON DELETE CASCADE.

2. Create a PL/SQL block using an explicit cursor to iterate through each part.

3. Use a counter to track the number of processed rows.

4. Use a SAVEPOINT before each deletion.

5. Delete every 10th part, print its details, and COMMIT the transaction.

6. Continue processing the rest of the parts.

PRACTICAL-7
AIM:
To develop a PL/SQL program that identifies doctors who have both treated and prescribed
medicine to the same patient, which is a violation of business rules, and to raise and display an
exception if such a case occurs.

PROCEDURE:
1. Assume the schema includes:

o Treatment(doctor_id, patient_id, treatment_date)

o Prescription(doctor_id, patient_id, prescription_date)

2. Write a PL/SQL program using a cursor to find entries where a doctor appears in both tables
for the same patient.

3. If such a case is found:

o Raise a user-defined exception,

o Display a message indicating the doctor ID and patient ID involved in the violation.

PRACTICAL-8
AIM:
To write a PL/SQL program that includes:

1. A procedure getCleanerDetails which takes a cleaner number and returns the cleaner’s name
and salary.

2. A stored function getCleanersLocation which takes a cleaner number and returns the depot
address of the cleaner.

3. A SQL query that selects the cleaner’s name and location using the stored function.

PROCEDURE:
1. Assume a table structure as follows:

CREATE TABLE Cleaners (

cleaner_id NUMBER PRIMARY KEY,

cleaner_name VARCHAR2(100),

salary NUMBER,

depot_address VARCHAR2(200)

);

2. Create a procedure getCleanerDetails that accepts cleaner_id and returns cleaner_name and
salary.

3. Create a stored function getCleanersLocation that accepts cleaner_id and returns depot_address.

4. Call the function from within a SELECT statement to display the name and location.

PRACTICAL-9
AIM:
To write a PL/SQL program that includes:

 A procedure getCleanerDetails that accepts a cleaner number as input and returns the
cleaner’s name and salary.

 A main PL/SQL block that:

o Calls the procedure with cleaner number '113'.

o Increases the retrieved salary by 10%.

o Displays the cleaner’s name and updated salary.

PROCEDURE:
1. Assume the existence of a table named Cleaners:

CREATE TABLE Cleaners (

cleaner_id NUMBER PRIMARY KEY,

cleaner_name VARCHAR2(100),

salary NUMBER

);

2. Create a procedure getCleanerDetails(p_id, p_name, p_salary).

3. In the main block, call the procedure with cleaner number 113, compute a 10% increase on
the salary, and display both name and updated salary using DBMS_OUTPUT.

PRACTICAL-10
AIM:
To create a trigger that:

 Prevents INSERT and UPDATE operations on a specific database table.

 Raises a user-defined error message whenever someone attempts to insert or update the
data.

PROCEDURE:
1. Choose a table to restrict (e.g., Client_master).

2. Create a BEFORE INSERT OR UPDATE trigger on the table.

3. Inside the trigger, use the RAISE_APPLICATION_ERROR procedure to:

o Block the operation.

o Display a custom error message.

PRACTICAL-10
AIM:
To retrieve meaningful information from multiple tables in a relational database using JOIN
operations in SQL. This includes displaying employee details along with their departments,
managers, and hierarchical relationships within the organization.

PROCEDURE:
1. Understand the Schema:

o Identify the relationships between the tables.

o For example, EMPLOYEE.dept_id is a foreign key referencing DEPARTMENT.dept_id.

2. Write SQL Queries using JOINs:

o Use INNER JOIN to retrieve matching records from both tables.

o Use LEFT JOIN to include all records from one table even if there's no match in the
joined table (useful for showing employees without a manager).

o Use aggregate functions like COUNT() and GROUP BY to get summarized data.

3. Run the Queries:

o Execute each SQL query using a database system (e.g., MySQL, Oracle, SQL Server).

o Validate the output by comparing it with expected results.

4. Sort and Filter if Needed:

o Use ORDER BY to sort results.

o Use WHERE clause for filtering based on specific criteria.

5. Handle NULLs Gracefully:

o Use COALESCE() or IS NULL to deal with missing or undefined relationships (e.g., boss
of president).