Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I have provided the data please help answer the quries -- Database: `Nevins_BookORama` -- -- -------------------------------------------------------- -- -- Table structure for table `Books` -- CREATE

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!

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Introduction To Data Mining

Authors: Pang Ning Tan, Michael Steinbach, Vipin Kumar

1st Edition

321321367, 978-0321321367

More Books

Students also viewed these Databases questions