MartinYanchev-99

sql script

Dec 8th, 2020
1,100
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  2. SET time_zone = "+00:00";
  3.  
  4. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  5. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  6. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  7. /*!40101 SET NAMES utf8mb4 */;
  8.  
  9. DROP DATABASE IF EXISTS routes;
  10. CREATE DATABASE IF NOT EXISTS routes DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  11. USE routes;
  12.  
  13. DROP TABLE IF EXISTS ROUTE;
  14. CREATE TABLE IF NOT EXISTS ROUTE (
  15.   ROUTEID int(11) NOT NULL AUTO_INCREMENT,
  16.   ROUTETITLE varchar(30) NOT NULL,
  17.   DEPARTUREPOINT varchar(30) NOT NULL,
  18.   PRIMARY KEY (ROUTEID)
  19. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  20.  
  21. INSERT INTO ROUTE (ROUTEID, ROUTETITLE, DEPARTUREPOINT) VALUES
  22. (1, 'Oor Wullie Route', 'GCU'),
  23. (2, 'Religious Route', 'Glasgow Cathedral'),
  24. (3, 'Art Route', 'Kelvingrove Art Gallery and Museum'),
  25. (4, 'Education Route', 'GCU');
  26.  
  27. DROP TABLE IF EXISTS STAGE;
  28. CREATE TABLE IF NOT EXISTS STAGE (
  29.   STAGEID int(11) NOT NULL AUTO_INCREMENT,
  30.   STAGENUMBER int(11) NOT NULL,
  31.   ENDPOINT varchar(30) NOT NULL,
  32.   STAGELENGTH DECIMAL(5,2) NOT NULL,
  33.   ROUTEID int(11) NOT NULL,
  34.   PRIMARY KEY (STAGEID),
  35.   KEY ALBUMID (ROUTEID)
  36. ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;
  37.  
  38. INSERT INTO STAGE (STAGEID, STAGENUMBER, ENDPOINT, STAGELENGTH, ROUTEID) VALUES
  39. (1, 1, 'City Chambers', 0.75, 1),
  40. (2, 2, 'Sir Chris Hoy Velodrome', 3.8, 1),
  41. (3, 3, 'People''s Palace', 2.7, 1),
  42. (4, 4, 'Riverside Museum', 5.4, 1),
  43. (5, 5, 'Botanic Gardens', 2.4, 1),
  44. (6, 6, 'GCU', 3.4, 1),
  45. (7, 1, 'St Andrew''s Cathedral', 1.8, 2),
  46. (8, 2, 'Central Mosque', 0.75, 2),
  47. (9, 3, 'University Chapel', 5.4, 2),
  48. (10, 4, 'Om Hindu Mandir', 1.3, 2),
  49. (11, 5, 'Gurdwara Singh Sabha', 0.6, 2),
  50. (12, 6, 'Quaker Meeting House', 1.2, 2),
  51. (13, 7, 'Glasgow Buddhist Centre', 0.35, 2),
  52. (14, 8, 'Garnethill Synagogue', 0.45, 2),
  53. (15, 9, 'Glasgow Cathderal', 3.3, 2),
  54. (16, 1, 'Hunterian Art Gallery', 1.2, 3),
  55. (17, 2, 'MacKintosh Building', 2.2, 3),
  56. (18, 3, 'Gallery Of Modern Art', 1.4, 3),
  57. (19, 4, 'St. Mungo Museum Of Religious Life & Art', 1.3, 3),
  58. (20, 5, 'People''s Palace', 2.0, 3),
  59. (21, 6, 'The Burrell Collection', 7.1, 3),
  60. (22, 7, 'House For An Art Lover', 2.8, 3),
  61. (23, 8, 'Kelvingrove Art Gallery and Museum', 5.0, 3),
  62. (24, 1, 'University Of Strathclyde', 0.65, 4),
  63. (25, 2, 'City Of Glasgow College - Riverside Campus', 1.4, 4),
  64. (26, 3, 'School of Simulation and Visualisation', 3.9, 4),
  65. (27, 4, 'Glasgow Science Centre', 0.7, 4),
  66. (28, 5, 'University of Glasgow', 2.4, 4),
  67. (29, 6, 'The Mitchell Library', 1.9, 4),
  68. (30, 7, 'Glasgow School Of Art', 0.9, 4),
  69. (31, 8, 'Royal Conservatoire Of Scotland', 0.75, 4),
  70. (32, 9, 'GCU', 0.6, 4);
  71.  
  72.  
  73. ALTER TABLE STAGE
  74.   ADD CONSTRAINT STAGE_ibfk_1 FOREIGN KEY (ROUTEID) REFERENCES ROUTE (ROUTEID);
  75.  
  76. DELIMITER $$
  77. --
  78. -- Procedures
  79. --
  80. DROP PROCEDURE IF EXISTS `AddRoute`$$
  81. CREATE DEFINER=`root`@`localhost` PROCEDURE `AddRoute` (IN `inRouteTitle` TEXT, IN `inDeparturePoint` TEXT)  BEGIN
  82.  INSERT INTO ROUTE (ROUTETITLE, DEPARTUREPOINT)
  83.         VALUES (inRouteTitle, inDeparturePoint);
  84. END$$
  85.  
  86. DROP PROCEDURE IF EXISTS `AddStage`$$
  87. CREATE DEFINER=`root`@`localhost` PROCEDURE `AddStage` (IN `inStageNumber` INT, IN `inEndPoint` TEXT, IN `inStageLength` FLOAT, IN `inRouteId` INT)  BEGIN
  88.  INSERT INTO STAGE (STAGENUMBER, ENDPOINT, STAGELENGTH, ROUTEID)
  89.         VALUES (inStageNumber, inEndPoint, inStageLength, inRouteId);
  90. END$$
  91.  
  92. DROP PROCEDURE IF EXISTS `GetRoute`$$
  93. CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRoute` (IN `inRouteId` INT)  BEGIN
  94.  SELECT *
  95.  FROM     ROUTE
  96.  WHERE  ROUTEID = inRouteId ;
  97. END$$
  98.  
  99. DROP PROCEDURE IF EXISTS `GetRoutes`$$
  100. CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRoutes` ()  READS SQL DATA
  101. BEGIN
  102.   Select ROUTEID, ROUTETITLE, DEPARTUREPOINT From ROUTE Order By ROUTEID;    
  103. END$$
  104.  
  105. DROP PROCEDURE IF EXISTS `GetRouteStages`$$
  106. CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRouteStages` (IN `inRouteId` INT)  NO SQL
  107. BEGIN
  108.   SELECT STAGEID, STAGENUMBER, ENDPOINT, STAGELENGTH FROM STAGE
  109.   WHERE ROUTEID = inRouteId
  110.   Order By STAGENUMBER;
  111. End$$
  112.  
  113. DELIMITER ;  
  114.  
  115. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  116. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  117. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  118.  
RAW Paste Data