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?

Dept. Courses and Enrollment

We have a database with 3 tables:

	Courses
	Id	Name	deptId
	1	111	1
	2	112	1
	3	250	1
	4	231	1
	5	111	2
	6	250	3
	7	111	4

	Dept
 	Id	Name
	1	CSC
	2	MTH
	3	EGR
	4	CHM

	Enrollment
	Id	Count
	1	40
	2	15
	3	10
	4	12
	5	60
	6	14
	7	200

Question 1

List all the CS classes.

Question 2

What is the total enrollment over all the classes?

Question 3

How many different classes are taught?

Question 4

How many different departments are there?

Question 5

List all the classes in the database, with the department name and the class name on the same line, e.g. CSC 111, CSC 112, ..., EGR 250, ... CHM 111

Question 6

List the name of the CS classes so that they are output as "CSC111", "CSC112", etc... (in other words, concatenate department with class number.)

Question 7

List all the information in the database, where each class appears on 1 line, along with its department, and its enrollment.

Question 8

List all the information as formatted in Question 4, but only the classes with an enrollment larger than 50.

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 ;


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


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