C Prog Java Data Structures DBMS Web Prog

Table Creation

			
(a)Create the table as below-
Table name- Client_Master
Description – used to store client information


 


CREATE TABLE `client_master` (
  `client_no` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `address1` varchar(30) DEFAULT NULL,
  `address2` varchar(30) DEFAULT NULL,
  `city` varchar(15) DEFAULT NULL,
  `pincode` int(8) DEFAULT NULL,
  `state` varchar(15) DEFAULT NULL,
  `bal_due` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`client_no`)
);

		
		(b)Table name- Product_Master
Description -  Used to store product information

  

  


CREATE TABLE `product_master` (
  `product_no` varchar(6) NOT NULL,
  `description` varchar(15) NOT NULL,
  `profit_measure` float(4,2) NOT NULL,
  `unit_measure` varchar(10) NOT NULL,
  `qty_on_hand` int(8) NOT NULL,
  `reorder_lvl` int(8) NOT NULL,
  `sell_price` float(8,2) NOT NULL,
  `cost_price` float(8,2) NOT NULL,
  PRIMARY KEY (`product_no`)
);

		
		(c)Table name- salesman_master
Description-  used to store salesman working for the company.  


  	

Ytd (year to date)
CREATE TABLE `salesman_master` (
  `salesman_no` varchar(6) NOT NULL,
  `salesman_name` varchar(20) NOT NULL,
  `address1` varchar(30) NOT NULL,
  `address2` varchar(30) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `pincode` varchar(8) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `sal_amt` float(8,2) NOT NULL,
  `tgt_to_get` float(6,2) NOT NULL,
  `ytd_sales` float(6,2) DEFAULT NULL,
  `remarks` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`salesman_no`)
);


(d)Table name- Sales_order
Description – Used to store client’s order

 

CREATE TABLE `sales_order` (
`order_no` varchar(6) NOT NULL,
`order_date` date DEFAULT NULL,
`client_no` varchar(6) DEFAULT NULL,
`dely_addr` varchar(25) DEFAULT NULL,
`salesman_no` varchar(6) DEFAULT NULL,
`dely_type` char(1) DEFAULT 'F',
`billed_yn` char(1) DEFAULT NULL,
`dely_date` date DEFAULT NULL,
`order_status` varchar(10) DEFAULT NULL,
PRIMARY KEY (`order_no`),
KEY `client_no` (`client_no`),
KEY `salesman_no` (`salesman_no`),
CONSTRAINT `sales_order_ibfk_1` FOREIGN KEY (`client_no`) 
REFERENCES `client_master` (`client_no`) ON UPDATE CASCADE,
CONSTRAINT `sales_order_ibfk_2` FOREIGN KEY (`salesman_no`) 
REFERENCES `salesman_master` (`salesman_no`) ON UPDATE CASCADE    );


(e)Table name- Sales_order_details
Description  Used to store client’s orders with details of each product ordered.

 


CREATE TABLE `sales_order_details` (
  `order_no` varchar(6) NOT NULL DEFAULT '',
  `product_no` varchar(6) NOT NULL DEFAULT '',
  `qty_ordered` int(8) DEFAULT NULL,
  `qty_disp` int(8) DEFAULT NULL,
  `product_rate` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_no`,`product_no`),
  KEY `product_no` (`product_no`),
  CONSTRAINT `sales_order_details_ibfk_1` FOREIGN KEY (`order_no`) 
  REFERENCES `sales_order` (`order_no`) ON UPDATE CASCADE,
  CONSTRAINT `sales_order_details_ibfk_2` FOREIGN KEY (`product_no`) 
  REFERENCES `product_master` (`product_no`) ON UPDATE CASCADE
);

Table Insertion

				
2) Insert the following data into their respective tables:

A) Data for client_master table-

 

INSERT INTO client_master VALUES 
('C00001', 'Ivan Bayross', NULL, NULL, 'Bombay', 400054, 'Maharashtra', 15000);
INSERT INTO client_master VALUES 
('C00002', 'Vandana Saitwal', NULL, NULL, 'Madras', 780001, 'Tamil Nadu', 0);
INSERT INTO client_master VALUES 
('C00003', 'Pramada Jaguste', NULL, NULL, 'Bombay', 400057, 'Maharashtra', 5000);
INSERT INTO client_master VALUES 
('C00004', 'Basu Navindgi', NULL, NULL, 'Bombay', 400056, 'Maharashtra', 0);
INSERT INTO client_master VALUES 
('C00005', 'Ravi Sreedharan', NULL, NULL, 'Delhi', 100001, 'Delhi', 2000);
INSERT INTO client_master VALUES 
('C00006', 'Rukmini', NULL, NULL, 'Bombay', 400050, 'Maharashtra', 0);

					
B) Data for Product_master table

 

INSERT INTO product_master VALUES ('P00001', '1.44 Floppies', 5, 'Piece', 100, 20, 525, 500);
INSERT INTO product_master VALUES ('P03453', 'Monitors', 6, 'Piece', 10, 3, 12000, 11280);
INSERT INTO product_master VALUES ('P06734', 'Mouse', 5, 'Piece', 20, 5, 1050, 1000);
INSERT INTO product_master VALUES ('P07865', '1.22 Floppies', 5, 'Piece', 100, 20, 525, 500);
INSERT INTO product_master VALUES ('P07868', 'Keyboards', 2, 'Piece', 10, 3, 3150, 3050);
INSERT INTO product_master VALUES ('P07885', 'CD Drive', 2.5, 'Piece', 10, 3, 5250, 5100);
INSERT INTO product_master VALUES ('P07965', '540 HDD', 4, 'Piece', 10, 3, 8400, 8000);
INSERT INTO product_master VALUES ('P07975', '1.44 Drives', 5, 'Piece', 10, 3, 1050, 1000);
INSERT INTO product_master VALUES ('P08865', '1.22 Drive', 5, 'Piece', 2, 3, 1050, 1000);

					
C) Data for Salesman_master table-

 

INSERT INTO salesman_master VALUES 
('S00001', 'Kiran', 'A/14', 'Worli', 'Bombay', '400002', 'Maharastra', 3000, 100, 50, 'Good');
INSERT INTO salesman_master VALUES 
('S00002', 'Manish', '65', 'Nariman', 'Bombay', '400001', 'Maharastra', 3000, 200, 100, 'Good');
INSERT INTO salesman_master VALUES 
('S00003', 'Ravi', 'P-7', 'Bandra', 'Bombay', '400032', 'Maharastra', 3000, 200, 100, 'Good');
INSERT INTO salesman_master VALUES 
('S00004', 'Ashish', 'A/5', 'Juhu', 'Bombay', '400044', 'Maharastra', 3500, 200, 150, 'Good');

					
d) Data for sales _ order table



e) Data for Sales_order_details table-

 

INSERT INTO sales_order_details VALUES ('O19001', 'P00001', 4, 4, 525);
INSERT INTO sales_order_details VALUES ('O19001', 'P07885', 2, 1, 5250);
INSERT INTO sales_order_details VALUES ('O19001', 'P07965', 2, 1, 8400);
INSERT INTO sales_order_details VALUES ('O19002', 'P00001', 10, 0, 525);
INSERT INTO sales_order_details VALUES ('O19003', 'P03453', 2, 2, 1050);
INSERT INTO sales_order_details VALUES ('O19003', 'P06734', 1, 1, 12000);
INSERT INTO sales_order_details VALUES ('O19008', 'P00001', 10, 5, 525);
INSERT INTO sales_order_details VALUES ('O19008', 'P07975', 5, 3, 1050);
INSERT INTO sales_order_details VALUES ('O46865', 'P00001', 10, 10, 525);
INSERT INTO sales_order_details VALUES ('O46865', 'P03453', 4, 4, 1050);
INSERT INTO sales_order_details VALUES ('O46865', 'P07868', 3, 3, 3150);
INSERT INTO sales_order_details VALUES ('O46865', 'P07885', 3, 1, 5250);
INSERT INTO sales_order_details VALUES ('O46866', 'P07965', 1, 0, 8400);
INSERT INTO sales_order_details VALUES ('O46866', 'P07975', 1, 0, 1050);

Exercises on Computation on Data Table

		
a) Find the name of all clients having ‘a’ as the second letter in their names.
select * from client_master
select * from client_master
where name like '%a_';

b) Find out the clients who stay in a city whose second letter is ‘a’.
select * from client_master
where city like '_a%';

c) Find the list of all clients who stay in ‘Bombay’ or ‘Delhi’.
select * from client_master
where city='bombay' or city='delhi';

d) print the list of clients whose bal_due is greater than value 10000.
select * from client_master
where bal_due>10000;

e) Print the information from sales_order table for orders placed in the 
month of January .
select * from sales_order
where month(order_date)=1;

f) Display the order information for client_no ‘C00001’ and ‘C00002’
select * from sales_order
where client_no='C00001' or client_no='C00002';

g) Find products whose selling price is greater than 2000 and less than or 
equal to 5000.
select * from product_master
where sell_price>5000;

h) Find the products whose selling price is more than 1500 . calculate a new 
selling price as ,original selling price *.15. rename the new column in the 
above query as new _price.
select sell_price,sell_price*0.15 as new_price from product_master
where sell_price>=1500;

i) List the names , city, and the state of clients who are not in the state 
of ‘maharashtra’.
select name,city,state from client_master
where state!='maharashtra';

j) Count the total number of orders.
select count(order_no) from sales_order;

k) calculate the average price of all the products.
select avg(sell_price) from product_master;

l) determine the maximum and minimum product prices , rename the output 
as max_price and min_price respectively.
select max(sell_price) as max_price,min(sell_price) as min_price from 
product_master;

m) Count the number of products having price greater than or equal to 1500.
select count(product_no) from product_master
where sell_price>=1500;

n) Find all the products whose qty_on_hand is less than record_ level.
select * from product_master
where qty_on_hand

Exercises on Manipulation on Data Table

				
4) Exercise on data manipulation:
a) Display the order number and day on which clients placed their order.
select order_no,date_format(order_date,'%a')as fff from sales_order;

b) Display the month ( in alphabets) and date when the order .
select date_format(order_date,'%M')as month,date_format(order_date,'%D') 
as date from sales_order;

c) Display the order_date in the format “DD-MM-YY” eg 12-jan-96
select date_format(order_date,'%d-%M-%y')from sales_order;

d) Find the date , 15 days after today’s date.
select date_add (order_date,  INTERVAL 15 DAY)from sales_order;

OR 

select date_add (CURDATE(),  INTERVAL 15 DAY);

e) Find the number of days elapsed between today’s date and the delivery 
date of the orders placed by the clients.
select datediff(curdate(), dely_date) from sales_order;

Exercises on Having and Group by Class

				
a) Print the description and total qty sold for each product.

select description, sum(qty_ordered) 
from product_master p inner join sales_order_details s
on p.product_no=s.product_no
group by (s.product_no);

b) Find the value of each product sold.

select description,sum(qty_ordered)*product_rate as value
from product_master p inner join sales_order_details s
on p.product_no=s.product_no
group by s.product_no;

c) Calculate the average qty sold for each client that has 
a maximum  order value of 15000.00
select client_no,avg(qty_ordered) 
from sales_order s1 inner join sales_order_details s2
on s1.order_no=s2.order_no
group by client_no
having max(qty_ordered*product_rate)<=15000;

d) find out the sum total of all the billed orders for the month 
of January.
select sum(order_no) from sales_order
where billed_yn='y' and month(order_date)=1;

Exercises on Joins and Corelation

				
a) Find out the products , which have been sold to ‘IVAN’.
select name,description
from client_master c inner join sales_order s1 inner join sales_order_details s 
inner join product_master p
on c.client_no=s1.client_no
and name='ivan bayross'
and s1.order_no=s.order_no
and s.product_no=p.product_no;

b) Find out the products and their quantities that will have to delivered 
in the current month.

select pm.product_no, pm.description, s.qty_ordered – s.qty_disp
from product_master pm inner join sales_order_details s 
on p.product_no=s.product_no 
and month(dely_date)=month(curDate());

select pm.product_no, pm.description, s.qty_ordered – s.qty_disp as 
‘Qty_balance’, so.dely_date
from product_master pm inner join sales_order_details s inner join 
sales_order so on p.product_no=s.product_no 
and s.order_no=s1.order_no 
and month(dely_date)=month(curDate());

c) Find the Product_no and description of constantly sold i.e. rapidly 
moving products.
select description, sum(qty_ordered)
from product_master p inner join sales_order_details s
on p.product_no=s.product_no 
group by s.product_no 
having sum(qty_ordered)>=(select max(qty_ordered) from sales_order_details);

d) Find the names of clients who have purchased ‘cd drive’.
select name,description 
from product_master p inner join sales_order_details s inner join sales_order 
s1 inner join client_master c
on p.product_no=s.product_no
and description='cd drive'
and s.order_no=s1.order_no
and s1.client_no=c.client_no;

e) list the product_no and order_no of customers having qty_ordered less than 5 
from the sales_order_details table for the product “1.44 floppies”.
select s.product_no,order_no,qty_ordered
from product_master p inner join sales_order_details s
on p.product_no=s.product_no
and description='1.44 floppies'
and qty_ordered<5; 

f) Find the products and their quantities for the orders palced by 
“Ivan bayross” and “ vandana saitwal”.
select name, description, qty_ordered
from client_master c inner join sales_order s1 inner join sales_order_details s 
inner join product_master p 
on c.client_no=s1.client_no
and name in('ivan’ ,'vandana')
and s1.order_no=s.order_no
and s.product_no=p.product_no;

g) Find the products and their quantities for the orders palces by 
client_no ‘C00001’
and ‘C00002’
select client_no,description,qty_ordered 
from sales_order s1 inner join sales_order_details s inner join product_master p
on s1.order_no=s.order_no
and client_no in('C00001','C00002')
and s.product_no=p.product_no;

Exercises on Sub-Queries

				
a) Find the product_no and description of non-moving products ie products 
not being sold.
select description,product_no from product_master 
where product_no not in(select product_no from sales_order_details
                        group by product_no);
   
b) Find the customer name , address 1, address 2, city and pin code 
for the client who has placed order no ‘O19001’.
select name,address1,address2,city,pincode from client_master
where client_no=(select client_no from sales_order
                 where order_no='O19001');

c) Find the clients names who have placed orders before the month of may 96.
select name from client_master 
where client_no in(Select client_no from sales_order 
                   where month(order_date)<5 and year(order_date)<1996); 
								
d) Find out if the product ‘1.44’ drive has been ordered by any client and 
print the client_no , name to whom it was sold.
select client_no,name from client_master
where client_no=(select client_no 
			 from product_master p inner join sales_order_details s 
			 inner join sales_order s1
			 on p.product_no=s.product_no
			 and description='1.44 drive'
			 and s.order_no=s1.order_no);

e) Find the names of clients who have placed orders worth rs 10000 or more.

select name from client_master  where  client_no  in (select client_no   
from sales_order s1 inner join sales_order_details  s on s1.order_no=s.order_non 
and  qty_ordered*product_rate>=10000);

Visit Our New Website

Talenthome Training

C Prog Java Data Structures DBMS Web Prog

Table Creation

Table Insertion

Exercises on Computation on Data Table

Exercises on Manipulation on Data Table

Exercises on Having and Group by Class

Exercises on Joins and Corelation

Exercises on Sub-Queries

Our Programs