Difference between revisions of "CSC220 Exercises with MySQL"
(→store) |
(→Dept. Courses and Enrollment) |
||
| (9 intermediate revisions by the same user not shown) | |||
| Line 25: | Line 25: | ||
;Question 5 | ;Question 5 | ||
: how many different items do we have in the store? | : 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= | =Database Dumps= | ||
| Line 59: | Line 114: | ||
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ; | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ; | ||
| − | |||
| − | |||
| − | |||
INSERT INTO `Course` (`Id`, `Name`, `deptId`) VALUES | INSERT INTO `Course` (`Id`, `Name`, `deptId`) VALUES | ||
| Line 72: | Line 124: | ||
(7, '111', 4); | (7, '111', 4); | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
CREATE TABLE IF NOT EXISTS `Dept` ( | CREATE TABLE IF NOT EXISTS `Dept` ( | ||
Latest revision as of 10:40, 18 October 2010
--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);
