DBMS Lab BCA-306 1323180
Practical 1
1. Database Creation and Table Design
• Task: Create a database named "Company_Records".
• Tables: Create the following tables: client_master, Product_master.
Database : Company_records
create database company_records;
Table : client_master
1
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
create table client_master;
2
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Table : Product_master
create table product_master;
3
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Practical 2
Data Manipulation- To Manipulate (insert, update and delete) the Operations on the
table and Pattern Searching and functions.
Manipulate : insert
INSERT INTO client_master (client_no, Name, City, Pincode, State, Bal_due)
VALUES
('0001', 'Ivan', 'Bombay', '400054', 'Maharashtra', 15000)
('0002', 'Vandana', 'Madras', '780001', 'Tamilnadu', 0);
4
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Manipulate : update
UPDATE Client_Master SET City = 'Bombay' WHERE client_no = '0005';
UPDATE Client_Master SET Bal_due = 1000 WHERE client_no = '0001';
Manipulate : delete
DELETE FROM Client_Master WHERE Client_no = '0001';
5
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Pattern Searching
Find all clients whose city or name contains 'a' as the second letter:
SELECT * FROM Client_Master WHERE City LIKE '_a%' OR Name LIKE '_a%';
6
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
7
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Functions :
Find the maximum and minimum sell prices of products:
SELECT MAX(Sell_price) AS Max_Price, MIN(Sell_price) AS Min_Price FROM Product_Master;
Find the average selling price of all products:
SELECT AVG(Sell_price) AS Average_Price FROM Product_Master;
8
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Functions :
finding the total number of clients and the sum of the balance due
SELECT COUNT(*) AS total_clients,
SUM(Bal_due) AS total_balance_due FROM Client_Master;
9
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Functions :
Find the products whose selling price is greater than 2000 and less than or equal to
5000:
SELECT * FROM Product_Master WHERE Sell_price > 2000 AND Sell_price <= 5000;
10
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Functions :
Find the names of all clients whose city is not 'Maharashtra':
11
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
DBMS Lab BCA-306 1323180
Functions :
SELECT Name, City, State FROM Client_Master WHERE State != 'Maharashtra';
12
MAHARISHI MARKANDESHWAR (DEEMED TO BE UNIVERSITY) MULLNA
