create all the tables and also fill the info in the
table by creating each table then do the given
computation
Below are step-by-step Oracle SQL commands to:
Create each table based on standard assumptions from names and your exercise.
Insert sample records (so you can run the queries).
Perform the computations as requested in your exercise.
Table columns are chosen to match typical attributes; adjust as per your assignment if needed.
1. Create Tables
1.1 Client_Master
CREATE TABLE Client_Master (
ClientNo VARCHAR2(8) PRIMARY KEY,
ClientName VARCHAR2(30),
City VARCHAR2(20),
State VARCHAR2(20),
BalDue NUMBER
);
1.2 Product_Master
CREATE TABLE Product_Master (
ProductNo VARCHAR2(8) PRIMARY KEY,
Description VARCHAR2(30),
ProfitPercent NUMBER(5,2),
UnitMeasure VARCHAR2(10),
QtyOnHand NUMBER,
ReorderLevel NUMBER,
Selling_Price NUMBER,
Cost_Price NUMBER
);
1.3 Salesman_Master
CREATE TABLE Salesman_Master (
SalesmanNo VARCHAR2(8) PRIMARY KEY,
SalesmanName VARCHAR2(30),
Address VARCHAR2(50),
City VARCHAR2(20),
PhoneNo VARCHAR2(15)
);
1.4 Sales_Order
CREATE TABLE Sales_Order (
OrderNo VARCHAR2(8) PRIMARY KEY,
OrderDate DATE,
ClientNo VARCHAR2(8),
SalesmanNo VARCHAR2(8),
DelvDate DATE,
OrderStatus VARCHAR2(10)
);
1.5 Sales_Order_Details
CREATE TABLE Sales_Order_Details (
OrderNo VARCHAR2(8),
ProductNo VARCHAR2(8),
QtyOrdered NUMBER,
FOREIGN KEY (OrderNo) REFERENCES Sales_Order(OrderNo),
FOREIGN KEY (ProductNo) REFERENCES Product_Master(ProductNo)
);
2. Insert Sample Data
2.1 Client_Master
INSERT INTO Client_Master VALUES ('C00001', 'Raman', 'Delhi', 'Delhi', 1200
INSERT INTO Client_Master VALUES ('C00002', 'Samar', 'Mumbai', 'Maharashtra', 500
INSERT INTO Client_Master VALUES ('C00003', 'Amit', 'Bangalore', 'Karnataka', 900
INSERT INTO Client_Master VALUES ('C00004', 'Sohan', 'Mangalore', 'Karnataka', 1100
INSERT INTO Client_Master VALUES ('C00005', 'Karan', 'Chennai', 'Tamil Nadu', 700
2.2 Product_Master
INSERT INTO Product_Master VALUES ('P0001', 'Pen', 12.5, 'pcs', 100, 20, 20, 15);
INSERT INTO Product_Master VALUES ('P0002', 'Pencil', 10.0, 'pcs', 50, 15, 10, 8);
INSERT INTO Product_Master VALUES ('P0003', 'Notebook', 15.0, 'pcs', 80, 25, 55, 40);
INSERT INTO Product_Master VALUES ('P0004', 'Bag', 20.0, 'pcs', 25, 10, 550,400);
INSERT INTO Product_Master VALUES ('P0005', 'Box', 18.0, 'pcs', 18, 30, 600,480);
2.3 Salesman_Master
INSERT INTO Salesman_Master VALUES ('S00001', 'Aakash', '123 St.', 'Delhi', '9512345678
INSERT INTO Salesman_Master VALUES ('S00002', 'Rohit', '456 Ave', 'Mumbai', '9123456789
INSERT INTO Salesman_Master VALUES ('S00003', 'Deepak', '789 Blvd','Bangalore','93214567
2.4 Sales_Order
INSERT INTO Sales_Order VALUES ('O0001', TO_DATE('2025-06-10','YYYY-MM-DD'), 'C00001', 'S
INSERT INTO Sales_Order VALUES ('O0002', TO_DATE('2025-05-15','YYYY-MM-DD'), 'C00002', 'S
INSERT INTO Sales_Order VALUES ('O0003', TO_DATE('2025-06-25','YYYY-MM-DD'), 'C00004', 'S
2.5 Sales_Order_Details
INSERT INTO Sales_Order_Details VALUES ('O0001', 'P0001', 10);
INSERT INTO Sales_Order_Details VALUES ('O0001', 'P0003', 5);
INSERT INTO Sales_Order_Details VALUES ('O0002', 'P0002', 15);
INSERT INTO Sales_Order_Details VALUES ('O0003', 'P0004', 2);
3. Computations (as Oracle SQL)
All the queries given in my previous response will now work using these tables.
You can now proceed with the computation queries provided in my previous message, executed
one by one after this setup. If you need a specific query’s output from this sample data, let me
know and I’ll show you the actual result set!
⁂
1. image.jpg
