DBMS-306

1323215

+-------------------+
| PRACTICAL 1 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1.A) Database Creation and Table Design

● Task: Create a database named "Company Records".
● Tables: Create the following tables:

I)client_master ii) product_master

1.B) Insert the following data into their respective tables:

● For client_master

● For product_master

QUERY FOR --> 1.A

+----------------------------------------+
| mysql> CREATE DATABASE Company_Record; |
| Query OK, 1 row affected (0.01 sec) |
+----------------------------------------+

I)client_master
+----------------------------------------+
DBMS-306
1323215
| CREATE TABLE client_master ( |
| client_no VARCHAR(6), |
| name VARCHAR(20), |
| address1 VARCHAR(30), |
| address2 VARCHAR(30), |
| city VARCHAR(15), |
| state VARCHAR(15), |
| pincode INT(6), |
| bal_due FLOAT(10,2) ); |
| |
+----------------------------------------+

mysql> DESC CLIENT_MASTER;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| client_no | varchar(6) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| address1 | varchar(30) | YES | | NULL | |
| address2 | varchar(30) | YES | | NULL | |
| city | varchar(15) | YES | | NULL | |
| state | varchar(15) | YES | | NULL | |
| pincode | int | YES | | NULL | |
| bal_due | float(10,2) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+

ii) product_master
+----------------------------------------+
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
+----------------------------------------+

QUERY FOR --> 1.B

I)client_master
+--------------------------------------------------------------------------+
DBMS-306
1323215
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
+--------------------------------------------------------------------------+

ii) product_master
+--------------------------------------------------------------------------+
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
+--------------------------------------------------------------------------+
DBMS-306
1323215

1.C) On the basis of above two tables answer the following Questionnaires:
QUERY FOR --> 1.C
i. Find out the names of all the clients.

ii. Retrieve the list of names and cities of all the clients.

iii. List the various products available from the product master table.

iv. List all the clients who are located in Bombay.

DBMS-306
1323215

v. Display the information for client no 0001 and 0002.

vi. Find the products with description as '1.44 drive' and '1.22 Drive'.

vii. Find all the products whose sell price is greater then 5000.

viii. Find the list of all clients who stay in city 'Bombay' or city 'Delhi' or 'Madras'.

ix. Find the product whose selling price is greater than 2000 and less than or equal to 5000.
DBMS-306
1323215

x. List the name, city and state of clients not in the state of 'Maharashtra'.
DBMS-306
1323215

+-------------------+
| PRACTICAL 2 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Using the table client master and product master answer the following Questionnaires.

I. Change the selling price of '1.44floppy’ drive to Rs. 1150.00

II. Delete the record with client 0001 from the client master table.

III. Change the city of client_no'0005' to Bombay

IV. Change the bal_due of client_no '0001, to 1000.

V. Find the products whose selling price is more than 1500 and also find the new selling price as original
selling price *15.

VI. Find out the clients who stay in a city whose second letter is a.
DBMS-306
1323215

VII. Find out the name of all clients having 'a' as the second letter in their names.

VIII.List the products in sorted order of their description.

IX. Calculate the average price of all the products.

X. Calculate the minimum price of products.

DBMS-306
1323215

XI. Count the number of products having price greater than or equal to 1500.
DBMS-306
1323215

+-------------------+
| PRACTICAL 3 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Create the ‘ salesman_master ‘ table described below:

------------------------------------------------------------------------------
SOLUTION
+-------------+
DBMS-306
1323215
Insert the following data into the table:

------------------------------------------------------------------------------
SOLUTION
+-------------+

Create the ‘ sales_order ‘ table described below:

SOLUTION
+-------------+
DBMS-306
1323215

Insert the following data into the table:

------------------------------------------------------------------------------
SOLUTION
+-------------+
DBMS-306
1323215

Create the ‘ sales_order_details ‘ table described below:

------------------------------------------------------------------------------
SOLUTION
+-------------+

Insert the following data into the table:

DBMS-306
1323215

------------------------------------------------------------------------------
SOLUTION
+-------------+

Using the Database “Company_Records” answer the following

Questionnaires.
DBMS-306
1323215
i. List all the information from the Sales_Order table for orders placed in the month of June.

ii. Count the total number of orders.

iii. List the order number and day on which clients placed their order

iv. List the month (in alphabets) and date when the orders must be delivered.
DBMS-306
1323215

v. List the order date in the format ‘DD-MONTH-YY’.

DBMS-306
1323215

vi. List the date, 15 days after today’s date.

vii. Print the description and total qty sold for each product.
DBMS-306
1323215

+-------------------+
| PRACTICAL 4 |
+-------------------++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

TABLE: Challan_Header

+---------------------------------------------
SOLUTION
+-------------+

-- Create the challan_header table

CREATE TABLE challan_header (
Challan_no VARCHAR2(6) PRIMARY KEY,
s_order_no VARCHAR2(6) REFERENCES salse_order,
challan_date DATE NOT NULL,
billed_yn CHAR(1) DEFAULT 'N',
CHECK (billed_yn IN ('Y', 'N'))
);

-- Insert data into challan_header table

INSERT INTO challan_header (Challan_no, s_order_no, challan_date, billed_yn)
VALUES ('CH9001', 'o19001', TO_DATE('12-DEC-95', 'DD-MON-YY'), 'Y');

INSERT INTO challan_header (Challan_no, s_order_no, challan_date, billed_yn)

VALUES ('CH865', 'o46866', TO_DATE('12-NOV-95', 'DD-MON-YY'), 'Y');

INSERT INTO challan_header (Challan_no, s_order_no, challan_date, billed_yn)

VALUES ('CH3965', 'o10008', TO_DATE('12-OCT-95', 'DD-MON-YY'), 'Y');

TABLE: Challan_Details
DBMS-306
1323215

-
SOLUTION
+-------------+

-- Create the challan_details table

CREATE TABLE challan_details (
Challan_no VARCHAR2(6),
Qty_disp NUMBER(4, 2) NOT NULL,
product_no VARCHAR2(6) REFERENCES product_master,
PRIMARY KEY (Challan_no, product_no)
);

-- Insert data into challan_details table

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)
VALUES ('CH9001', 4, 'P00001');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH9001', 1, 'P07965');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH9001', 1, 'P07885');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH6865', 3, 'P07868');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH6865', 4, 'P03453');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH6865', 10, 'P00001');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH3965', 5, 'P00001');

INSERT INTO challan_details (Challan_no, Qty_disp, product_no)

VALUES ('CH3965', 2, 'P07975');

1. Add a new column phone_no in the client_master table.

ALTER TABLE client_master

ADD COLUMN phone_no VARCHAR(15);

2. Add the not null constraint in the product_master table with the
columns description, profit percent , sell price and cost price

ALTER TABLE product_master

MODIFY COLUMN description VARCHAR(255) NOT NULL;

ALTER TABLE product_master

MODIFY COLUMN profit_percent DECIMAL(5, 2) NOT NULL;
DBMS-306
1323215
ALTER TABLE product_master
MODIFY COLUMN sell_price DECIMAL(10, 2) NOT NULL;

ALTER TABLE product_master

MODIFY COLUMN cost_price DECIMAL(10, 2) NOT NULL;

3. Change the size of client_no field in the client_master table

ALTER TABLE client_master

MODIFY COLUMN client_no VARCHAR(20);

4. Select product_no, description where profit percent is between 20 and

30 both inclusive

SELECT product_no, description

FROM product_master
WHERE profit_percent BETWEEN 20 AND 30;
DBMS-306
1323215

+-------------------+
| PRACTICAL 5 |
+-------------------++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1. find out the product which has been sold to 'ivan bayroos'

SELECT p.product_no, p.description

FROM product_master p
JOIN orders o ON p.product_no = o.product_no
JOIN client_master c ON o.client_no = c.client_no
WHERE c.client_name = 'Ivan Bayroos';

2. find out the product and their quantities that will have to delivered in the current
month.

SELECT p.product_no, p.description, o.quantity

FROM product_master p
JOIN orders o ON p.product_no = o.product_no
WHERE MONTH(o.delivery_date) = MONTH(CURRENT_DATE())
AND YEAR(o.delivery_date) = YEAR(CURRENT_DATE());

3. find the product_no and description of moving products

SELECT DISTINCT p.product_no, p.description

FROM product_master p
JOIN orders o ON p.product_no = o.product_no
WHERE o.quantity > 0;

4. find the names of the clients who have purchased 'CD Drive'.

SELECT c.client_name
FROM client_master c
JOIN orders o ON c.client_no = o.client_no
JOIN product_master p ON o.product_no = p.product_no
WHERE p.description = 'CD Drive';

5. List the product_no and s_order_no of customers having qty_ordered less than 5
from the order detail Table for the product '1.44 Floppies'.

SELECT od.product_no, od.s_order_no

FROM order_detail od
JOIN product_master p ON od.product_no = p.product_no
WHERE p.description = '1.44 Floppies'
AND od.qty_ordered < 5;
DBMS-306
1323215

6. Find the products and their quantities for the orders placed by 'Vandana Saitwal'
and 'Ivan Bayross'.

SELECT p.product_no, p.description, o.quantity

FROM product_master p
JOIN orders o ON p.product_no = o.product_no
JOIN client_master c ON o.client_no = c.client_no
WHERE c.client_name IN ('Vandana Saitwal', 'Ivan Bayross');
DBMS-306
1323215

+-------------------+
| PRACTICAL 6 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To implement the concept of grouping of Data(Order

By,Group By ,Having)

1. Retrieve Sales Ordered by Amount

Write a query to retrieve all records from the sales table, ordered by sales_amount in descending order.

SELECT *
FROM sales
ORDER BY sales_amount DESC;

2. Group Sales by Region

Write a query to display the total sales_amount for each region from the sales table.
DBMS-306
1323215

SELECT region, SUM(sales_amount) AS total_sales

FROM sales
GROUP BY region;

3. Filter Grouped Sales with a Condition

Using the sales table, display the total sales amount for each region where the total sales exceed ₹40,000.
Use the HAVING clause.

SELECT region, SUM(sales_amount) AS total_sales

FROM sales
GROUP BY region
HAVING SUM(sales_amount) > 40000;

4. Group Employees by Department and Sort by Salary

Write a query to display the average salary for each department from the employees table, grouped by
department_id, and order the results by average salary in ascending order.

SELECT department_id, AVG(salary) AS avg_salary

FROM employees
GROUP BY department_id
ORDER BY avg_salary ASC;
DBMS-306
1323215

+-------------------+
| PRACTICAL 7 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Aggregation and Subqueries(Using the Union, Intersect

and Minus Clause)

1. Union - Combine Orders and Customers

Retrieve all unique customer IDs from both the orders table and the customers table.

SELECT customer_id
FROM orders
UNION
SELECT customer_id
FROM customers;

2. Intersect - Common Customers

DBMS-306
1323215
Retrieve customer IDs that are present in both the orders table and the customers table.

SELECT customer_id
FROM orders
INTERSECT
SELECT customer_id
FROM customers;

3. Minus - Customers Without Orders

Retrieve customer IDs from the customers table who have not placed any orders.

SELECT customer_id
FROM customers
MINUS
SELECT customer_id
FROM orders;

4. Aggregation with Subquery

Find the total amount spent by each customer (use the orders table). For customers without
orders, display their names with total_amount as NULL.

SELECT c.customer_id, c.name,

(SELECT SUM(o.total_amount)
FROM orders o
WHERE o.customer_id = c.customer_id) AS total_amount
FROM customers c;
DBMS-306
1323215

+-------------------+
| PRACTICAL 8 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To implement the concept of Indexes and views (Creating

and Drop index

1. Create an Index
Create an index on the category_id column of the products table to optimize queries that
frequently filter products by category.

CREATE INDEX idx_category_id

ON products (category_id);

2. Drop an Index
Drop the previously created index idx_category_id.

DROP INDEX idx_category_id;

3. Create a View
Create a view named expensive_products that shows the product_name, price, and stock_quantity
of products with a price greater than ₹25,000.

CREATE VIEW expensive_products AS

SELECT product_name, price, stock_quantity
FROM products
WHERE price > 25000;

4. Query the View

Retrieve all records from the expensive_products view.
DBMS-306
1323215

SELECT *
FROM expensive_products;

5. Drop a View
Drop the view expensive_products.

DROP VIEW expensive_products;

6. Practical Use of Index

Write a query to retrieve all product_name and price for products in category 102. Explain how an
index on category_id would help.

SELECT product_name, price

FROM products
WHERE category_id = 102;

7. Create a Composite Index

Create a composite index on the price and stock_quantity columns to optimize queries that sort or
filter on these columns.

CREATE INDEX idx_price_stock

ON products (price, stock_quantity);
DBMS-306
1323215

+-------------------+
| PRACTICAL 9 |
+-------------------+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PL/SQL Functions

1. Create a Function to Calculate Bonus

Write a PL/SQL function calculate_bonus that calculates the bonus for an employee as 10% of
their salary.

CREATE OR REPLACE FUNCTION calculate_bonus(emp_salary

NUMBER)
RETURN NUMBER IS
bonus NUMBER;
BEGIN
bonus := emp_salary * 0.10;
RETURN bonus;
END;
/

2. Call the Bonus Function

Write a query to display the employee_id, name, and bonus for each employee using the
calculate_bonus function.

SELECT employee_id, name, calculate_bonus(salary) AS bonus

FROM employees;
DBMS-306
1323215

3. Function to Retrieve Employee Details

Create a PL/SQL function get_employee_details that returns the employee’s name and salary for
a given employee_id.

CREATE OR REPLACE FUNCTION get_employee_details(emp_id

NUMBER)
RETURN VARCHAR2 IS
emp_details VARCHAR2(100);
BEGIN
SELECT name || ' earns ₹' || salary
INTO emp_details
FROM employees
WHERE employee_id = emp_id;
RETURN emp_details;
END;
/

4. Call the Employee Details Function

Retrieve details for the employee with employee_id = 3 using the get_employee_details function.

SELECT get_employee_details(3) AS employee_details

FROM dual;
DBMS-306
1323215

+--------------------+
| PRACTICAL 10 |
+--------------------++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Transaction Management and Security

1. Begin a Transaction and Rollback

Simulate a scenario where a transfer of ₹2000 from Alice to Bob is initiated but fails midway. Use
BEGIN TRANSACTION, UPDATE, and ROLLBACK.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 2000
WHERE account_id = 1; -- Deduct from Alice

-- Simulate an error before crediting Bob

ROLLBACK;

SELECT * FROM accounts;

DBMS-306
1323215

2. Commit a Successful Transaction

Transfer ₹1000 from Charlie to Diana and commit the changes.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 3; -- Deduct from Charlie

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 4; -- Add to Diana

COMMIT;

SELECT * FROM accounts;

3. Add a Check for Transaction Amount

Update the transactions table to ensure no debit transaction exceeds the account balance. Use a
CHECK constraint.

ALTER TABLE transactions

ADD CONSTRAINT chk_transaction_amount
CHECK (
DBMS-306
1323215
transaction_type = 'Credit' OR
amount <= (SELECT balance FROM accounts WHERE
accounts.account_id = transactions.account_id)
);

4. Grant Privileges
Grant and revoke privileges to control user access to the accounts table.

Grant Privileges:

GRANT SELECT, UPDATE ON accounts TO banking_user;

Revoke Privileges:

REVOKE UPDATE ON accounts FROM banking_user;

DBMS-306
1323215

+--------------------+
| PRACTICAL 11 |
+--------------------++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Complex queries

1. Multi-table Join
Write a query to display the employee name, department name, and project name for employees
working in departments assigned to projects.

SELECT
e.name AS employee_name,
d.department_name,
p.project_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
JOIN projects p ON d.department_id = p.department_id;
DBMS-306
1323215

2. Nested Subquery
Find the name and salary of employees who earn more than the average salary of their
department.

SELECT name, salary

FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);

3. Using EXISTS
Retrieve the names of employees who are managers (i.e., their employee_id is referenced in the
manager_id column).

SELECT name
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM employees e2
WHERE e2.manager_id = e1.employee_id
);
DBMS-306
1323215

4. Using CASE
Write a query to display the employee name and a performance rating based on their salary:
● High: Salary > ₹70,000
● Medium: ₹50,000 ≤ Salary ≤ ₹70,000
● Low: Salary < ₹50,000

SELECT
name,
CASE
WHEN salary > 70000 THEN 'High'
WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'
ELSE 'Low'
END AS performance_rating
FROM employees;

5. Complex Filtering
Retrieve the names of employees working in departments with more than one project.
SELECT DISTINCT e.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IN (
SELECT department_id
FROM projects
GROUP BY department_id
HAVING COUNT(project_id) > 1
);