Ples answer the quries only DATABASE IS PROVIDED BELOW -- Database: `Nevins_Book

Question

Ples answer the quries only

DATABASE IS PROVIDED BELOW

-- Database: `Nevins_BookORama`

--

-- --------------------------------------------------------

--
-- Table structure for table `Books`
--

CREATE TABLE IF NOT EXISTS `Books` (
`ISBN` char(13) NOT NULL,
`Author` char(50) DEFAULT NULL,
`Title` char(100) DEFAULT NULL,
`Price` float(4,2) DEFAULT NULL,
PRIMARY KEY (`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Books`
--

INSERT INTO `Books` (`ISBN`, `Author`, `Title`, `Price`) VALUES
('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99),
('0-672-31745-1', 'Thomas Down', 'Installing Debain GNU/Linux', 24.99),
('0-672-31509-2', 'Pruitt, et al.', 'Teach Yourself GIMP in 24 Hours', 24.99),
('0-672-31769-9', 'Thomas Schenk', 'Caldera OpenLinux System Administration Unleashed', 49.99),
('1-337-27472-0', 'D.S. Malik', 'C++ Programming: From Problem Analysis to Program Design', 99.99),
('0-672-31509-5', 'Sandy Nevins', 'Look Ma No Hands', 2.26),
('9-781-13352-6', 'D.S. Malik', 'C++ Programming: Program Design Including Data Structures, 6th Edition', 59.95),
('1-111-53053-X', 'D.S. Malik', 'Java™ Programming: From Problem Analysis to Program Design', 99.99),
('0-538-79810-6', 'D.S. Malik', 'Lab Manual for Malik''s C++ Programming: From Problem Analysis to Program Design', 16.57),
('0-619-15950-2', 'D.S. Malik', 'Data Structures Using Java', 21.49),
('0-619-21285-3', 'D.S. Malik', 'Discrete Mathematical Structures: Theory and Applications', 99.99),
('0-070-40035-0', 'D.S. Malik', 'Fundamentals of Abstract Algebra', 89.95),
('9-814-41693-2', 'D.S. Malik', 'Introduction to Graph Theory', 69.95),
('9-810-23628-X', 'D.S. Malik', 'Fuzzy Commutative Algebra (Pure Mathematics)', 59.00),
('8-170-35643-1', 'D.S. Malik', 'Texitle Pollution', 32.88),
('1-123-12345-1', 'D.S. Malik', 'Water Pollution and Management', 99.99);

-- --------------------------------------------------------

--
-- Table structure for table `Book_Reviews`
--

CREATE TABLE IF NOT EXISTS `Book_Reviews` (
`ISBN` char(13) NOT NULL,
`Review` text,
PRIMARY KEY (`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Book_Reviews`
--

INSERT INTO `Book_Reviews` (`ISBN`, `Review`) VALUES
('0-672-31697-8', 'The Morgan book is clearly written and goes well beyond most of the basic Java books out there.');

-- --------------------------------------------------------

--
-- Table structure for table `Customers`
--

CREATE TABLE IF NOT EXISTS `Customers` (
`CustomerID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` char(50) NOT NULL,
`Address` char(100) NOT NULL,
`City` char(30) NOT NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `Customers`
--

INSERT INTO `Customers` (`CustomerID`, `Name`, `Address`, `City`) VALUES
(1, 'Julie Smith', '25 Oak Street', 'Airport West'),
(2, 'Alan Wong', '1/47 Haines Avenue', 'Box Hill'),
(3, 'Michelle Arthur', '357 North Road', 'Yarraville');

-- --------------------------------------------------------

--
-- Table structure for table `Orders`
--

CREATE TABLE IF NOT EXISTS `Orders` (
`OrderID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CustomerID` int(10) unsigned NOT NULL,
`Amount` float(6,2) DEFAULT NULL,
`Date` date NOT NULL,
PRIMARY KEY (`OrderID`),
KEY `CustomerID` (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `Orders`
--

INSERT INTO `Orders` (`OrderID`, `CustomerID`, `Amount`, `Date`) VALUES
(1, 3, 69.98, '2017-04-02'),
(2, 1, 49.99, '2017-04-15'),
(3, 2, 74.98, '2017-04-19'),
(4, 3, 24.99, '2017-05-01');

-- --------------------------------------------------------

--
-- Table structure for table `Order_Items`
--

CREATE TABLE IF NOT EXISTS `Order_Items` (
`OrderID` int(10) unsigned NOT NULL,
`ISBN` char(13) NOT NULL,
`Quantity` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`OrderID`,`ISBN`),
KEY `ISBN` (`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Order_Items`
--

INSERT INTO `Order_Items` (`OrderID`, `ISBN`, `Quantity`) VALUES
(1, '0-672-31697-8', 2),
(2, '0-672-31769-9', 1),
(3, '0-672-31769-9', 1),
(3, '0-672-31509-2', 1),
(4, '0-672-31745-1', 3);

QUESTION:

a) List the customer name with the order that has the highest amount

b)List the customer name with the MOST orders.  

b) List the customer name who spent the most all together!

Explanation / Answer

a

Create view c1 as Select CustomerID, max(Amount) from Orders;

Select c.Name from Customers as c,c1 where c.CustomerID = c1.CustomerID

c

CREATE view c3 AS SELECT CustomerID, SUM(Amount) as Amount from Orders group by CustomerID;
CREATE view c2 AS Select CustomerID, max(Amount) from c3;
Select c.Name from Customers as c,c2 where c.CustomerID = c2.CustomerID

b.

CREATE view c4 AS SELECT CustomerID, count(*) as count1 from Orders group by CustomerID;
CREATE view c5 AS Select CustomerID, max(count1) from c4;
Select c.Name from Customers as c,c5 where c.CustomerID = c5.CustomerID

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects