VtysLab2.sql

CREATE DATABASE `VtysLabDB`;
SHOW DATABASES;
USE `VtysLabDB`;

SHOW VARIABLES LIKE 'character_set_database';
SHOW TABLES;
SELECT * FROM information_schema.routines WHERE routine_schema = 'VtysLabDB' ORDER BY routine_name;
SHOW TABLES IN information_schema LIKE 'ENGINES';

CREATE TABLE `ClientMaster` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ClientNo` varchar(6) DEFAULT NULL,
  `Name` varchar(20) DEFAULT NULL,
  `Address1` varchar(30) DEFAULT NULL,
  `Address2` varchar(30) DEFAULT NULL,
  `City` varchar(15) DEFAULT NULL,
  `State` varchar(15) DEFAULT NULL,
  `PinCode` int(6) DEFAULT NULL,
  `BalDue` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `ProductMaster` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ProductNo` varchar(6) DEFAULT NULL,
  `Description` varchar(200) DEFAULT NULL,
  `ProfitPercent` int(100) DEFAULT NULL,
  `UnitMeasure` varchar(20) DEFAULT NULL,
  `QtyOnHand` int(100) DEFAULT NULL,
  `ReorderValue` int(11) DEFAULT NULL,
  `SellPrice` int(11) DEFAULT NULL,
  `CostPrice` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

CREATE TABLE `SalesmanMaster` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `SalesmanNo` varchar(6) DEFAULT NULL CHECK (SalesmanNo like 's%'),
  `Name` varchar(20) NOT NULL DEFAULT '',
  `Address` varchar(400) NOT NULL DEFAULT '',
  `City` varchar(20) DEFAULT NULL,
  `State` varchar(20) DEFAULT NULL,
  `PinCode` int(6) DEFAULT NULL,
  `SaleAmount` int(9) NOT NULL,
  `TgtToGet` int(7) NOT NULL,
  `YtdSales` int(7) NOT NULL,
  `Remarks` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE `SalesOrder` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `OrderNo` varchar(6) NOT NULL DEFAULT '' CHECK (OrderNo like '0%'),
  `OrderDate` date DEFAULT NULL,
  `ClientNo` varchar(25) DEFAULT NULL,
  `DelyAdd` varchar(6) DEFAULT NULL,
  `SalesmanNo` varchar(6) DEFAULT NULL,
  `DelyType` char(1) DEFAULT 'F',
  `Billed` char(1) DEFAULT NULL,
  `DelyDate` date DEFAULT NULL constraint DelyDate CHECK (DelyDate > OrderDate),
  `OrderStatus` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `SalesmanNo` (`SalesmanNo`),
  CONSTRAINT `SalesmanRelation` FOREIGN KEY (`SalesmanNo`) REFERENCES `SalesmanMaster` (`SalesmanNo`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `SalesOrderDetails` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `OrderNo` varchar(6) DEFAULT NULL,
  `ProductNo` varchar(6) DEFAULT NULL,
  `QtyOrder` int(8) DEFAULT NULL,
  `QtyDisp` int(8) DEFAULT NULL,
  `ProductRate` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `OrderNo` (`OrderNo`),
  CONSTRAINT `OrderNo` FOREIGN KEY (`OrderNo`) REFERENCES `OrderNo` (`SalesOrder`),
  KEY `ProductNo` (`ProductNo`),
  CONSTRAINT `ProductNo` FOREIGN KEY (`ProductNo`) REFERENCES `ProductNo` (`ProductMaster`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

INSERT INTO `ClientMaster` (`id`, `ClientNo`, `Name`, `Address1`, `Address2`, `City`, `State`, `PinCode`, `BalDue`) VALUES (NULL, '0001', 'Ivan', NULL, NULL, 'Bombay', 'Maharashtra', NULL, NULL);
INSERT INTO `ClientMaster` (`id`, `ClientNo`, `Name`, `Address1`, `Address2`, `City`, `State`, `PinCode`, `BalDue`) VALUES (NULL, '0002', 'Vandana', NULL, NULL, 'Madras', 'Tamşnadu', NULL, NULL);
INSERT INTO `ClientMaster` (`id`, `ClientNo`, `Name`, `Address1`, `Address2`, `City`, `State`, `PinCode`, `BalDue`) VALUES (NULL, '0003', 'Pramada', NULL, NULL, 'Bombay', 'Maharashtra', NULL, NULL);
INSERT INTO `ClientMaster` (`id`, `ClientNo`, `Name`, `Address1`, `Address2`, `City`, `State`, `PinCode`, `BalDue`) VALUES (NULL, '0004', 'Basu', NULL, NULL, 'Bombay', 'Maharashtra', NULL, NULL);
INSERT INTO `ClientMaster` (`id`, `ClientNo`, `Name`, `Address1`, `Address2`, `City`, `State`, `PinCode`, `BalDue`) VALUES (NULL, '0005', 'Ravi', NULL, NULL, 'Delhi', '', NULL, NULL);
INSERT INTO `ClientMaster` (`id`, `ClientNo`, `Name`, `Address1`, `Address2`, `City`, `State`, `PinCode`, `BalDue`) VALUES (NULL, '0006', 'Rukmini', NULL, NULL, 'Bombay', 'Maharashtra', NULL, NULL);

INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P0001', '1.44 Floppies', '5', 'Piece', '100', '20', '525', '500');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P03453', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P06734', 'Mouse', '5', 'Piece', '20', '5', '1050', '500');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P07865', '1.22 Floppies', '5', 'Piece', '100', '20', '525', '500');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P07868', 'Keyboards', '2', 'Piece', '10', '3', '3150', '3050');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P07885', 'CD Drive', '2.5', 'Piece', '10', '3', '5250', '5100');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P07965', '540 HDD', '4', 'Piece', '10', '3', '8400', '8000');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P07975', '1.44 Drive', '5', 'Piece', '10', '3', '1050', '1000');
INSERT INTO `ProductMaster` (`id`, `ProductNo`, `Description`, `ProfitPercent`, `UnitMeasure`, `QtyOnHand`, `ReorderValue`, `SellPrice`, `CostPrice`) VALUES (NULL, 'P08865', '1.22 Drive', '5', 'Piece', '2', '3', '1050', '1000');

INSERT INTO `SalesmanMaster` (`id`, `SalesmanNo`, `Name`, `Address`, `City`, `State`, `PinCode`, `SaleAmount`, `TgtToGet`, `YtdSales`, `Remarks`) VALUES (NULL, '500001', 'Kiran', 'A/14 Worli', 'Bombay', 'Mah', '400002', '3000', '100', '50', 'Good');
INSERT INTO `SalesmanMaster` (`id`, `SalesmanNo`, `Name`, `Address`, `City`, `State`, `PinCode`, `SaleAmount`, `TgtToGet`, `YtdSales`, `Remarks`) VALUES (NULL, '500001', 'Kiran', 'A/14 Worli', 'Bombay', 'Mah', '400002', '3000', '100', '50', '0');
INSERT INTO `SalesmanMaster` (`id`, `SalesmanNo`, `Name`, `Address`, `City`, `State`, `PinCode`, `SaleAmount`, `TgtToGet`, `YtdSales`, `Remarks`) VALUES (NULL, '500002', 'Manish', '65 Nariman', 'Bombay', 'Mah', '400001', '3000', '200', '100', 'Good');
INSERT INTO `SalesmanMaster` (`id`, `SalesmanNo`, `Name`, `Address`, `City`, `State`, `PinCode`, `SaleAmount`, `TgtToGet`, `YtdSales`, `Remarks`) VALUES (NULL, '500003', 'Ravi', 'P-7 Bandra', 'Bombay', 'Mah', '400032', '3000', '200', '100', 'Good');
INSERT INTO `SalesmanMaster` (`id`, `SalesmanNo`, `Name`, `Address`, `City`, `State`, `PinCode`, `SaleAmount`, `TgtToGet`, `YtdSales`, `Remarks`) VALUES (NULL, '500004', 'Ashish', 'A/5 Juhu', 'Bombay', 'Mah', '499944', '3500', '200', '150', 'Good');

INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '019001', '12.01.1996', '0001', NULL, '50001', 'F', 'N', '20.01.1996', 'Ip');
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '019001', '12.01.1996', '0001', NULL, '50001', 'F', 'N', '20.01.1996', 'Ip');
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '', '12.01.1996T00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '', '1996-01-17', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '019002', '1996-01-25', '0002', NULL, '50002', 'P', 'N', '1996-01-27', 'C');
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '016865', '1996-02-18', '0003', NULL, '500003', 'F', 'Y', '1996-02-20', 'F');
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '019003', '1996-04-03', '0001', NULL, '500001', NULL, NULL, NULL, NULL);
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '046866', '1996-05-20', '0004', NULL, '500002', 'P', 'N', '1996-05-22', 'C');
INSERT INTO `SalesOrder` (`id`, `OrderNo`, `OrderDate`, `ClientNo`, `DelyAdd`, `SalesmanNo`, `DelyType`, `Billed`, `DelyDate`, `OrderStatus`) VALUES (NULL, '010008', '1996-05-24', '0005', NULL, '500004', 'F', 'N', '1996-05-26', 'Ip');

INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '019001', 'P00001', '4', '4', '525');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '019001', 'P07965', '2', '1', '8400');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '019001', 'P07885', '2', '1', '5250');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '019002', 'P00001', '10', '0', '525');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '046865', 'P07868', '3', '3', '3150');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '046865', 'P07885', '10', '10', '5250');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '019003', 'P00001', '4', '4', '1050');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '019003', 'P03453', '2', '2', '1050');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '046866', 'P06734', '1', '1', '12000');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '046866', 'P07965', '1', '0', '8400');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '010008', 'P07975', '1', '0', '1050');
INSERT INTO `SalesOrderDetails` (`id`, `OrderNo`, `ProductNo`, `QtyOrder`, `QtyDisp`, `ProductRate`) VALUES (NULL, '010008', 'P00001', '10', '5', '525');


/* 1 */
CREATE INDEX ClientIndex ON ClientMaster (ClientNo);

/* 2 */
CREATE INDEX SalesIndex ON SalesOrder (OrderNo);

/* 3 */
CREATE INDEX SalesCompositeIndex ON SalesOrder (OrderNo, ProductNo);

/* 4 */
DROP INDEX SalesCompositeIndex ON SalesOrder;

/* 5 */
CREATE VIEW SalesView AS SELECT SaleAmount FROM SalesOrder WHERE SaleAmount > 3500;

/* 6 */
CREATE VIEW ClientView (add1, add2, city, pcode, state) AS SELECT * FROM ClientMaster;

/* 7 */
SELECT Name FROM ClientView WHERE State = 'Bombay';

/* 8 */
DROP VIEW ClientView;

/* 9 */
CREATE VIEW SelectDailyOrdersView AS SELECT * FROM SalesOrder WHERE OrderDate = CURDATE();

/* 10 */
SELECT C.Name, P.Description
FROM SalesOrder 
INNER JOIN ClientMaster C ON SalesOrder.ClientNo = C.`ClientNo` 
INNER JOIN ProductMaster P ON SalesOrder.OrderNo = P.`ProductNo`
WHERE OrderDate > CURDATE() + 10;