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);

Courses

CREATE TABLE IF NOT EXISTS `Course` (
  `Id` int(1) NOT NULL auto_increment,
  `Name` varchar(4) NOT NULL,
  `deptId` int(1) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `Course`
--

INSERT INTO `Course` (`Id`, `Name`, `deptId`) VALUES
(1, '111', 1),
(2, '112', 1),
(3, '250', 1),
(4, '231', 1),
(5, '111', 2),
(6, '250', 3),
(7, '111', 4);

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

--
-- Table structure for table `Dept`
--

CREATE TABLE IF NOT EXISTS `Dept` (
  `Id` int(1) NOT NULL auto_increment,
  `Name` varchar(3) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

 
INSERT INTO `Dept` (`Id`, `Name`) VALUES
(1, 'CSC'),
(2, 'MTH'),
(3, 'EGR'),
(4, 'CHM');


CREATE TABLE IF NOT EXISTS `Enrollment` (
  `Id` int(1) NOT NULL auto_increment,
  `count` int(1) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

 
INSERT INTO `Enrollment` (`Id`, `count`) VALUES
(1, 40),
(2, 15),
(3, 10),
(4, 12),
(5, 60),
(6, 14),
(7, 200);