Scribd
Upload
108 views2 pages

Answers To Interactive SQL Exercise: STEP-1 SQL Statement For Crea NG The Tables

The document provides the SQL statements to: 1) Create tables to store client, product, and salesman data with various columns; 2) Insert data into the tables with multiple rows for each table; 3) Write SQL queries to retrieve, update, modify and delete data from the tables including selecting specific columns, filtering by criteria, updating values, altering columns, and renaming/dropping tables.

Uploaded by

Shopping Guide
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
108 views2 pages

Answers To Interactive SQL Exercise: STEP-1 SQL Statement For Crea NG The Tables

The document provides the SQL statements to: 1) Create tables to store client, product, and salesman data with various columns; 2) Insert data into the tables with multiple rows for each table; 3) Write SQL queries to retrieve, update, modify and delete data from the tables including selecting specific columns, filtering by criteria, updating values, altering columns, and renaming/dropping tables.

Uploaded by

Shopping Guide
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Answers to Interactive Sql Exercise

STEP-1 SQL Statement for crea ng the tables:


a) Table Name: CLIENT_MASTER
CREATE TABLE CLIENT_MASTER(CLIENTNO varchar2(6),NAME varchar2(20),ADDRESS1
varchar2(30),ADDRESS2 varchar2(30),CITY varchar2(15),PINCODE number(8), STATE
varchar2(15),BALDUE number(10,2));
b) Table Name: PRODUCT_MASTER
CREATE TABLE PRODUCT_MASTER(PRODUCTNO varchar2(6),DESCRIPTION varchar2(15),
PROFITPERCENT number(4,2),UNITMEASURE varchar2(10),QTYONHAND number(8),
REORDERLVL number(8),SELLPRICE number(8,2),COSTPRICE number(8,2));
c) Table Name: SALESMAN_MASTER
CREATE TABLE SALESMAN_MASTER(SALESMANNO varchar2(6),SALESMANNAME
varchar2(20),ADDRESS 1 varchar2(30),ADDRESS2 varchar2(30),CITY
varchar2(20),PINCODE number(8),STATE varchar2(20),SALAMT number(8,2),TGTTOGET
number(6,2),YTDSALES number(6,2),REMARKS varchar2(60));

STEP-2 SQL Statement for inser ng into their respec ve tables:


a) Data for CLIENT_MASTER table:
INSERT INTO Client_Master(ClientNo,Name,City,PinCode,State,BalDue)
VALUES('C00001','Ivan Bayross','Mumbai',400054,'Maharashtra',15000);
INSERT INTO Client_Master(ClientNo,Name,City,PinCode,State,BalDue)
VALUES('C00002','Mamta Muzumdar','Madras",780001,'Tamil Nadu',0);
INSERT INTO Client_Master(ClientNo,Name,City,Pincode,State,BalDue)
VALUES('C00003','Chhaya Bankar','Mumbai',400057,'Maharashtra',5000);
INSFRT INTO Client_Master(ClientNo,Name,City,PinCode,State,BalDue)
VALUES('C00004',Ashwini Joshi','Bangalore',560001,'Karnataka',0);
INSERT INTO Client_Master(ClientNo,Name,City,PinCode,State,BalDue)
VALUES('C00005','Hansel Colaco','Mumbai',400060,'Maharashtra',2000);
INSERT INTO Client_Master(ClientNo,Name,City,PinCode,State,BalDue)
VALUES ('C00006','Deepak Sharma','Mangalore',560050,'Karnataka',0);
b) Data for PRODUCT_MASTER table
INSERT INTO Product_Master VALUES('P00001','T-Shirts',5,'Piece',200,50,350,250);
INSERT INTO Product_Master VALUES('P03453','Shirts',6,'Piece',150,50,500,350);
INSERT INTO Product_Master VALUES('P06734','Cotton Jeans',5,'Piece',100,20,600,450);
INSERT INTO Product_Master VALUES('P07865','Jeans',5,'Piece',100,20,750,500);
INSERT INTO Product_Master VALUES('P07868','Trousers',2,'Piece',150,50,850,550);
INSERT INTO Product_Master VALUES('P07885','Pull Overs',2.5,'Piece',80,30,700,450);
INSERT INTO Product_Master VALUES('P07965','Denim Shirts',4,'Piece',100,40,350,250);
INSERT INTO Product_Master VALUES('P07975','Lycra Tops',5,'Piece',70,30,300,175);
INSERT INTO Product_Master VALUES('P08865','Skirts',5,'Piece',75,30,450,300);
c) Data for SALESMAN_MASTER table
INSERT INTO Salesman_Master VALUES('S00001','Aman',A/14','Worii','Mumbai',400002,
'Maharashtra',3000,100,50,'Good');
INSERT INTO Salesman_Master VALUES('S00002','Omkar','65','Nariman','Mumbai',400001,
‘Maharashtra', 3000,200, 100, 'Good');
INSERT INTO Salesman_Master VALUES('S00003','Raj','P-7','Bandra','Mumbai',400032,
'Maharashtra',3000,200,100,'Good');
INSERT INTO Salesman_Master VALUES('S00004',Ashish','A/5','Mm','Bombay',400044,
'Maharashtra',3500,200,150,'Good');
Answers to Questions
a) Find out the names of all the clients.
SELECT Name FROM Client_Master;
b) Retrieve the en re contents of the Client_Master table.
SELECT * FROM ClientMaster;
c) Retrieve the list of names, city and the sate of all the clients.
SELECT Name, City, State FROM Client_Master;
d) List the various products available from the Product_Master table.
SELECT Description FROM Product_Master;
e) List all the clients who are located in Mumbai.
SELECT * FROM Client_Master WHERE City='Mumbai';
f) Find the names of salesmen who have a salary equal to Rs.3000.
SELECT Salesman_name FROM Salesman_Master WHERE SalAmt=3000;
g) Change the city of ClientNo 'C00005' to 'Bangalore'.
UPDATE Client_Master SET City='Bangalore' WHERE Client_No = 'C00005';
h) Change the BalDue of ClientNo 'C00001' to Rs. 1000.
UPDATE Client_Master SET BalDue=1000 WHERE Client_no='C00001';
i) Change the cost price of'Trousers' to Rs. 950.00.
UPDATE Product_Master SET CostPrice = 950.00 WHERE Description = 'Trousers';
j) Change the city of the salesman to Pune.
UPDATE Client_Master SET City = 'Pune';
k) Change the size of SellPrice column in Product_Master to 10,2.
ALTER TABLE Product_Master MODIFY (SellPrice number(10,2));
l) Destroy the table Client_Master along with its data.
DROP TABLE Client_Master;
m) Change the name of the Salesman_Master table to sman_mast.
RENAME Salesman_Master To sman_mast;

You might also like