I have provided the data please help answer the quries -- Database: `Nevins_Book
ID: 3705267 • Letter: I
Question
I have provided the data please help answer the quries
-- 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);
QUESTIONS
List all titles of books that have the word 'Java' in the title.
List all authors that wrote a book that have the word 'Java' in the title.
List all customer names and addresses that made an order that was more than $30.00
List the customer name with the order that has the highest amount
List the customer name with the MOST orders.
List the customer name who spent the most all together!
Explanation / Answer
1. List all titles of books that have the word 'Java' in the title.
Answer: SELECT TITLE FROM BOOKS WHERE TITLE LIKE '%Java%';
2. List all authors that wrote a book that have the word 'Java' in the title.
SELECT Author FROM BOOKS WHERE TITLE LIKE '%Java%';
3. List all customer names and addresses that made an order that was more than $30.00
select c.Name, c.Address from
customers c
inner join orders o on (o.CustomerID = c.CustomerID) where o.Amount > 30.00
4. List the customer name with the order that has the highest amount
select c.Name from customer inner join orders o on (o.CustomerID = c.CustomerID) where
o.amount = (select max(amount) from orders);