60 (2)

ASSIGNMENT-4
Create tables for Client, Product, and Salesman with the attributes given, implementing
DDL commands for specifying prime attributes, non-prime attributes, foreign keys,
cardinalities, null values, constraints etc. and the data types. Implement DDL commands
for drop, alter on the tables created.
CLIENT_MASTER :
CREATE TABLE Client(
client_no VARCHAR2(6)PRIMARY KEY CHECK(client_no like '%c'),
Name VARCHAR(20)NOT NULL,
City VARCHAR(15),
Pincode NUMBER(8),
State VARCHAR(15),
BalDue NUMBER(10,2)
);
DESC Client_Master;
INSERT INTO Client (Client_no, Name, City, Pincode, State, BalDue)

VALUES ('C001', 'Client 1', 'City A', 123456, 'State X', 500.00);

VALUES ('C002', 'Client 2', 'City B', 789012, 'State Y', 1200.00);

VALUES ('C003', 'Client 3', 'City C', 345678, 'State Z', 0.00);

VALUES ('C004', 'Client 4', 'City D', 901234, 'State A', 750.00);

VALUES ('C005', 'Client 5', 'City E', 567890, 'State B', 300.00);
PRODUCT_MASTER :
CREATE TABLE Product(
Product_no VARCHAR2(6) PRIMARY KEY CHECK (Product_no LIKE 'P%'),
Description VARCHAR2(15) NOT NULL,
QTY_ON_Hand NUMBER(8) NOT NULL,
Sell_Price NUMBER(8, 2) NOT NULL CHECK (Sell_Price <> 0),
Cost_Price NUMBER(8, 2) NOT NULL CHECK (Cost_Price <> 0)
);
DESC Product;
INSERT INTO Product (Product_no, Description, QTY_ON_Hand, Sell_Price, Cost_Price)

VALUES ('P001', 'Product A', 100, 50.00, 30.00);

VALUES ('P002', 'Product B', 200, 60.00, 40.00);
VALUES ('P003', 'Product C', 150, 70.00, 50.00);

VALUES ('P004', 'Product D', 300, 80.00, 60.00);

VALUES ('P005', 'Product E', 250, 90.00, 70.00);
SALESMAN_MASTER :
CREATE TABLE Saleman(
Salesman_no VARCHAR2(6) PRIMARY KEY CHECK (Salesman_no LIKE 'S%'),
Salesman_name VARCHAR2(20) NOT NULL,
City VARCHAR2(20) NOT NULL,
Pincode NUMBER(8) NOT NULL,
State VARCHAR2(20) NOT NULL,
Sal_Amt NUMBER(8, 2) NOT NULL CHECK (Sal_Amt <> 0)
);
DESC Salesman;
INSERT INTO Saleman (Salesman_no, Salesman_name, City, Pincode, State, Sal_Amt)

VALUES ('S001', 'Salesman 1', 'City X', 123456, 'State M', 1500.00);

VALUES ('S002', 'Salesman 2', 'City Y', 789012, 'State N', 1800.00);

VALUES ('S003', 'Salesman 3', 'City Z', 345678, 'State O', 2000.00);

VALUES ('S004', 'Salesman 4', 'City A', 901234, 'State P', 1700.00);

VALUES ('S005', 'Salesman 5', 'City B', 567890, 'State Q', 3000.00);
SALES_ORDER :
CREATE TABLE Sales_Orde (
Order_no VARCHAR2(6) PRIMARY KEY CHECK (Order_no LIKE 'O%'),
Client_no VARCHAR2(6),
Order_date DATE,
Salesman_no VARCHAR2(6),
Dely_type CHAR(1) DEFAULT ‘F’ CHECK (Dely_type IN ('F', 'P')),
Dely_date DATE,
FOREIGN KEY (Client_no) REFERENCES Client (Client_no),
FOREIGN KEY (Salesman_no) REFERENCES Salesman(Salesman_no)
);
DESC Sales_Orde;
INSERT INTO Sales_Orde (Order_no, Client_no, Order_date, Salesman_no, Dely_type, Dely_date)

VALUES ('O001', 'C001', TO_DATE('2024-03-14', 'YYYY-MM-DD'), 'S001', 'F', TO_DATE('2024-03-30',
'YYYY-MM-DD'));

VALUES ('O002', 'C002', TO_DATE('2024-03-15', 'YYYY-MM-DD'), 'S002', 'P', TO_DATE('2024-03-31',
'YYYY-MM-DD'));

'YYYY-MM-DD'));

VALUES ('O004', 'C004', TO_DATE('2024-03-17', 'YYYY-MM-DD'), 'S004', 'P', TO_DATE('2024-04-02',
'YYYY-MM-DD'));

'YYYY-MM-DD'));
SALES_ORDER_DETAILS :
CREATE TABLE Sales_Orde_Details (
Order_no VARCHAR2(6),
Product_no VARCHAR2(6),
Qty_disp NUMBER(8),
Product_rate NUMBER(10, 2),
FOREIGN KEY (Order_no) REFERENCES Sales_Orde (Order_no),
FOREIGN KEY (Product_no) REFERENCES Product(Product_no)
);
DESC SALES_ORDER_DETAILS;
INSERT INTO Sales_Orde_Details (Order_no, Product_no, Qty_disp, Product_rate)

VALUES ('O001', 'P001', 10, 45.00);

VALUES ('O002', 'P002', 5, 55.00);

VALUES ('O003', 'P003', 8, 65.00);

VALUES ('O004', 'P004', 15, 75.00);

VALUES ('O005', 'P005', 12, 85.00);
QUESTION 1
-- i. Find out the names of all clients.

SELECT Name FROM Client;
-- ii. Retrieve the entire contents of the Client_Master table.

SELECT * FROM Client;
-- iii. Retrieve the list of names, city, and state of all clients.
SELECT Name, City, State FROM Client;
-- iv. List the various products available from the Product_Master table.
SELECT Description FROM Product;
-- v. List all clients who are located in Mumbai.

SELECT Name FROM Client WHERE City = 'City C';
-- vi. Find the names of salesman who have a salary equal to 3000.
SELECT Salesman_name FROM Saleman WHERE Sal_Amt = 3000;
-- vii. Show the details of Product_Master according to Cost_Price in descending order.

SELECT * FROM Product ORDER BY Cost_Price DESC;
-- viii. Show different types of salary amounts of the salesman.

SELECT DISTINCT Sal_Amt FROM Saleman;
-- ix. Show only unique product details.

SELECT DISTINCT * FROM Product;
QUESTION 2
-- i. Change the city of client no ‘C001’ to ‘Bangalore’.

UPDATE Client SET City = 'Bangalore' WHERE Client_no = 'C001';
-- ii. Change the BalDue of client no ‘C006’ to Rs. 1000.

UPDATE Client SET BalDue = 1000 WHERE Client_no = 'C006';
-- iii. Change the cost price of ‘Trousers’ to Rs. 950.00 where Product No P001.
UPDATE Product SET Description = 'Trousers' WHERE Product_No = ‘P001’;
UPDATE Product SET Cost_Price = 950.00 WHERE Description = 'Trousers';
-- iv. Change the city of the salesman to ‘Pune’.

UPDATE Saleman SET City = 'Pune';
Question 3
Add a column called ‘Telephone’ of data type number and size = 10 to the Client_Master table.
Question 4
Change the size of Sell_Price column in Product_Master to 10, 2
Question 5
Drop the column Cost_Price from Product_Master.
Question 6
Delete all salesmen from the Salesman_Master whose salaries are equal to Rs. 3500
Question 7
Delete all products from Product_Master where the quantity on hand is equal to 100.
Question 8
Delete from Client_Master where the column state holds the value ‘Tamil Nadu’.
Question 9
Change the name of the Salesman_Master table to Sman_Mast.
Question 10
Destroy the table Client_Master along with its data.

60 (2)

Uploaded by

Copyright:

Available Formats

60 (2)

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

60 (2)

Uploaded by

Copyright:

Available Formats

ASSIGNMENT-4

INSERT INTO Client (Client_no, Name, City, Pincode, State, BalDue)

INSERT INTO Client (Client_no, Name, City, Pincode, State, BalDue)

INSERT INTO Client (Client_no, Name, City, Pincode, State, BalDue)

INSERT INTO Client (Client_no, Name, City, Pincode, State, BalDue)

INSERT INTO Client (Client_no, Name, City, Pincode, State, BalDue)

INSERT INTO Product (Product_no, Description, QTY_ON_Hand, Sell_Price, Cost_Price)

INSERT INTO Product (Product_no, Description, QTY_ON_Hand, Sell_Price, Cost_Price)

INSERT INTO Product (Product_no, Description, QTY_ON_Hand, Sell_Price, Cost_Price)

INSERT INTO Product (Product_no, Description, QTY_ON_Hand, Sell_Price, Cost_Price)

INSERT INTO Saleman (Salesman_no, Salesman_name, City, Pincode, State, Sal_Amt)

INSERT INTO Saleman (Salesman_no, Salesman_name, City, Pincode, State, Sal_Amt)

INSERT INTO Saleman (Salesman_no, Salesman_name, City, Pincode, State, Sal_Amt)

INSERT INTO Saleman (Salesman_no, Salesman_name, City, Pincode, State, Sal_Amt)

INSERT INTO Saleman (Salesman_no, Salesman_name, City, Pincode, State, Sal_Amt)

INSERT INTO Sales_Orde (Order_no, Client_no, Order_date, Salesman_no, Dely_type, Dely_date)

INSERT INTO Sales_Orde (Order_no, Client_no, Order_date, Salesman_no, Dely_type, Dely_date)

INSERT INTO Sales_Orde (Order_no, Client_no, Order_date, Salesman_no, Dely_type, Dely_date)

INSERT INTO Sales_Orde (Order_no, Client_no, Order_date, Salesman_no, Dely_type, Dely_date)

INSERT INTO Sales_Orde (Order_no, Client_no, Order_date, Salesman_no, Dely_type, Dely_date)

INSERT INTO Sales_Orde_Details (Order_no, Product_no, Qty_disp, Product_rate)

INSERT INTO Sales_Orde_Details (Order_no, Product_no, Qty_disp, Product_rate)

INSERT INTO Sales_Orde_Details (Order_no, Product_no, Qty_disp, Product_rate)

INSERT INTO Sales_Orde_Details (Order_no, Product_no, Qty_disp, Product_rate)

INSERT INTO Sales_Orde_Details (Order_no, Product_no, Qty_disp, Product_rate)

-- i. Find out the names of all clients.

-- ii. Retrieve the entire contents of the Client_Master table.

-- v. List all clients who are located in Mumbai.

-- vii. Show the details of Product_Master according to Cost_Price in descending order.

-- viii. Show different types of salary amounts of the salesman.

-- ix. Show only unique product details.

-- i. Change the city of client no ‘C001’ to ‘Bangalore’.

-- ii. Change the BalDue of client no ‘C006’ to Rs. 1000.

-- iv. Change the city of the salesman to ‘Pune’.

You might also like