Difference between revisions of "CSC220 Midterm 2010"

From dftwiki3
Jump to: navigation, search
(Created page with '--~~~~ ---- =Problem 1= * For this problem, you have to create a series of queries and store them in a text file called '''test.sql''', the same way you did in the previous hom…')
 
(→‎Problem 1)
Line 4: Line 4:
  
 
* For this problem, you have to create a series of queries and store them in a text file called '''test.sql''',  the same way you did in the previous homework.
 
* For this problem, you have to create a series of queries and store them in a text file called '''test.sql''',  the same way you did in the previous homework.
−
* You have to generate queries that answer the different questions, assuming that you have 4 tables in your database named '''DataPhone''', '''Student''', '''Trip''', and '''Weather''', and the dump of which is shown below:
+
* You have to generate queries that answer the different questions, assuming that you have 4 tables in your database named '''DataPhone''', '''Student''', '''Trip''', and '''Weather''', and the dump of which is available [[CSC220 Midterm Database Dump| here]].
−
 
 
−
<code><pre>
 
−
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
−
 
 
−
-- --------------------------------------------------------
 
−
 
 
−
--
 
−
-- Table structure for table `DataPhone`
 
−
--
 
−
 
 
−
DROP TABLE IF EXISTS `DataPhone`;
 
−
CREATE TABLE IF NOT EXISTS `DataPhone` (
 
−
  `Id` int(11) NOT NULL auto_increment,
 
−
  `Name` varchar(15) NOT NULL,
 
−
  PRIMARY KEY  (`Id`)
 
−
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
 
−
 
 
−
--
 
−
-- Dumping data for table `DataPhone`
 
−
--
 
−
 
 
−
INSERT INTO `DataPhone` (`Id`, `Name`) VALUES
 
−
(10, 'iPhone'),
 
−
(2, 'Google Android'),
 
−
(3, 'Blackberry'),
 
−
(4, 'Nokia');
 
−
 
 
−
-- --------------------------------------------------------
 
−
 
 
−
--
 
−
-- Table structure for table `Student`
 
−
--
 
−
 
 
−
DROP TABLE IF EXISTS `Student`;
 
−
CREATE TABLE IF NOT EXISTS `Student` (
 
−
  `Id` int(1) NOT NULL auto_increment,
 
−
  `Name` varchar(30) default 'anonymous',
 
−
  `Email` varchar(30) default NULL,
 
−
  `Status` varchar(1) NOT NULL default 'N',
 
−
  `Phone` int(1) default NULL,
 
−
  PRIMARY KEY  (`Id`)
 
−
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
 
−
 
 
−
--
 
−
-- Dumping data for table `Student`
 
−
--
 
−
 
 
−
INSERT INTO `Student` (`Id`, `Name`, `Email`, `Status`, `Phone`) VALUES
 
−
(1, 'Marie', 'Marie@email.smith.edu', 'F', 1),
 
−
(2, 'Alma', 'Alma@gmail.com', 'O', 2),
 
−
(3, 'Missie', 'Missie@yahoo.com', 'S', 3),
 
−
(4, 'Claire', 'Claire@smith.edu', 'J', 1),
 
−
(5, 'Fred', 'Fred@hampshire.edu', 'J', 2),
 
−
(6, 'Frida', 'Frida@smith.edu', 'S', 2);
 
−
 
 
−
-- --------------------------------------------------------
 
−
 
 
−
--
 
−
-- Table structure for table `Trip`
 
−
--
 
−
 
 
−
DROP TABLE IF EXISTS `Trip`;
 
−
CREATE TABLE IF NOT EXISTS `Trip` (
 
−
  `Id` int(1) NOT NULL auto_increment,
 
−
  `Date` date NOT NULL,
 
−
  `KML` text NOT NULL,
 
−
  `Xmin` float NOT NULL,
 
−
  `Xmax` float NOT NULL,
 
−
  `Ymin` float NOT NULL,
 
−
  `Ymax` float NOT NULL,
 
−
  `StudentId` int(1) NOT NULL,
 
−
  PRIMARY KEY  (`Id`)
 
−
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
 
−
 
 
−
--
 
−
-- Dumping data for table `Trip`
 
−
--
 
−
 
 
−
INSERT INTO `Trip` (`Id`, `Date`, `KML`, `Xmin`, `Xmax`, `Ymin`, `Ymax`, `StudentId`) VALUES
 
−
(1, '2010-10-20', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, -71.6946, 41.344, 43.3424, 1),
 
−
(2, '2010-10-20', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 2),
 
−
(3, '2010-10-19', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 1),
 
−
(4, '2010-10-20', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 1),
 
−
(5, '2010-10-18', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 3),
 
−
(6, '2010-10-19', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 3),
 
−
(7, '2010-10-20', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 2),
 
−
(8, '2010-10-21', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 4),
 
−
(9, '2010-10-17', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 4),
 
−
(10, '2010-10-20', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 4),
 
−
(11, '2010-10-19', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 2),
 
−
(12, '2010-10-20', '<Folder>\r\n<name><![CDATA[Path]]></name>\r\n<open>0</open>\r\n<Placemark>\r\n<name>Segment 1</name>\r\n<styleUrl>#currentPathStyle</styleUrl>\r\n<LineString>\r\n<extrude>0</extrude>\r\n<tessellate>1</tessellate>\r\n<altitudeMode>clampToGround</altitudeMode>\r\n<coordinates>\r\n-71.69459750750001,42.34444296000000\r\n-72.69435600000000,42.34400928333333\r\n-72.69431068333333,42.34405935000000\r\n-72.70036616666665,42.34702501666668\r\n-72.69271225000001,43.34235086666666\r\n-72.69267878333333,42.34254801666666\r\n-72.69267330000000,42.34261633333334\r\n-72.69409016666667,42.34391306666667\r\n-72.69424906666666,41.34403235000000\r\n-72.69433810000001,42.34412963333332\r\n-73.69433810000001,42.34412963333332\r\n</coordinates>\r\n</LineString>\r\n</Placemark>\r\n</Folder>', -73.6943, 41.344, -71.6946, 43.3424, 2);
 
−
 
 
−
-- --------------------------------------------------------
 
−
 
 
−
--
 
−
-- Table structure for table `Weather`
 
−
--
 
−
 
 
−
DROP TABLE IF EXISTS `Weather`;
 
−
CREATE TABLE IF NOT EXISTS `Weather` (
 
−
  `Id` int(1) NOT NULL auto_increment,
 
−
  `Date` date NOT NULL,
 
−
  `Temperature` int(1) NOT NULL,
 
−
  PRIMARY KEY  (`Id`)
 
−
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;
 
−
 
 
−
--
 
−
-- Dumping data for table `Weather`
 
−
--
 
−
 
 
−
INSERT INTO `Weather` (`Id`, `Date`, `Temperature`) VALUES
 
−
(1, '2010-10-01', 63),
 
−
(2, '2010-10-02', 69),
 
−
(3, '2010-10-02', 55),
 
−
(4, '2010-10-03', 56),
 
−
(5, '2010-10-04', 47),
 
−
(6, '2010-10-05', 55),
 
−
(7, '2010-10-06', 66),
 
−
(8, '2010-10-07', 63),
 
−
(9, '2010-10-08', 45),
 
−
(10, '2010-10-09', 59),
 
−
(11, '2010-10-10', 56),
 
−
(12, '2010-10-11', 64),
 
−
(13, '2010-10-12', 50),
 
−
(14, '2010-10-13', 47),
 
−
(15, '2010-10-14', 46),
 
−
(16, '2010-10-15', 47),
 
−
(17, '2010-10-16', 55),
 
−
(18, '2010-10-17', 66),
 
−
(19, '2010-10-18', 65),
 
−
(20, '2010-10-19', 55),
 
−
(21, '2010-10-20', 42),
 
−
(22, '2010-10-21', 63),
 
−
(23, '2010-10-22', 57),
 
−
(24, '2010-10-23', 57),
 
−
(25, '2010-10-24', 42),
 
−
(26, '2010-10-25', 59),
 
−
(27, '2010-10-26', 68),
 
−
(28, '2010-10-27', 62),
 
−
(29, '2010-10-28', 66),
 
−
(30, '2010-10-29', 45),
 
−
(31, '2010-10-30', 44),
 
−
(32, '2010-10-31', 44);
 
−
</pre></code>
 

Revision as of 20:02, 20 October 2010

--D. Thiebaut 00:58, 21 October 2010 (UTC)

Problem 1

  • For this problem, you have to create a series of queries and store them in a text file called test.sql, the same way you did in the previous homework.
  • You have to generate queries that answer the different questions, assuming that you have 4 tables in your database named DataPhone, Student, Trip, and Weather, and the dump of which is available here.
Retrieved from "http://dominiquethiebaut.com/dftwiki3/index.php?title=CSC220_Midterm_2010&oldid=8790"