CSC220 Exercises with MySQL

--D. Thiebaut 14:19, 18 October 2010 (UTC)

---

Store Inventory

We have a database called store with the following contents:

	Id	Name	Qty	Price
	1	apple	10	1
	2	pear	5	2
	3	banana	10	1.5
	6	lemon	100	0.1
	5	orange	50	0.2

Question 1

List all the items sorted alphabetically. Then list only the first 3. Then the last 3.

Question 2

list only the items that are more than $1 per unit price

Question 3

list all the items with their extended price (quantity * price)

Question 4

list the total cost of all the items in the store

Question 5

how many different items do we have in the store?

Database Dumps

store

CREATE TABLE IF NOT EXISTS `store` (
  `Id` int(1) NOT NULL auto_increment,
  `Name` varchar(40) NOT NULL,
  `Qty` int(1) NOT NULL,
  `Price` float NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;


INSERT INTO `store` (`Id`, `Name`, `Qty`, `Price`) VALUES
(1, 'apple', 10, 1),
(2, 'pear', 5, 2),
(3, 'banana', 10, 1.5),
(6, 'lemon', 100, 0.1),
(5, 'orange', 50, 0.2);