sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

HAFH.sql 6.7KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295
  1. -- create tables
  2. CREATE TABLE manager (
  3. managerid CHAR(4) NOT NULL,
  4. nfname VARCHAR(15) NOT NULL,
  5. mlname VARCHAR(15) NOT NULL,
  6. mbdate DATE NOT NULL,
  7. msalary NUMERIC (9,2) NOT NULL,
  8. mbonus NUMERIC (9,2),
  9. mresbuildingid CHAR(3),
  10. PRIMARY KEY (managerid)
  11. );
  12. CREATE TABLE managerphone (
  13. managerid CHAR(4) NOT NULL,
  14. mphone CHAR(11) NOT NULL,
  15. PRIMARY KEY (managerid, mphone)
  16. FOREIGN KEY (managerid) REFERENCES manager (managerid)
  17. );
  18. CREATE TABLE building (
  19. buildingid CHAR(3) NOT NULL,
  20. bnooffloors INT NOT NULL,
  21. bmanagerid CHAR(4) NOT NULL,
  22. PRIMARY KEY (buildingid),
  23. FOREIGN KEY (bmanagerid) REFERENCES manager (managerid)
  24. );
  25. CREATE TABLE inspector (
  26. insid CHAR(3) NOT NULL,
  27. insname VARCHAR(15) NOT NULL,
  28. PRIMARY KEY (insid)
  29. );
  30. CREATE TABLE inspecting (
  31. insid CHAR(3) NOT NULL,
  32. buildingid CHAR(3) NOT NULL,
  33. datelast DATE NOT NULL,
  34. datenext DATE NOT NULL,
  35. PRIMARY KEY (insid, buildingid),
  36. FOREIGN KEY (insid) REFERENCES inspector (insid),
  37. FOREIGN KEY (buildingid) REFERENCES building (buildingid)
  38. );
  39. CREATE TABLE corpclient (
  40. ccid CHAR(4) NOT NULL,
  41. ccname VARCHAR(25) NOT NULL,
  42. ccindustry VARCHAR(25) NOT NULL,
  43. cclocation VARCHAR(25) NOT NULL,
  44. ccidreferredby CHAR(4),
  45. PRIMARY KEY (ccid),
  46. UNIQUE (ccname),
  47. FOREIGN KEY (ccidreferredby) REFERENCES corpclient (ccid)
  48. );
  49. CREATE TABLE apartment (
  50. buildingid CHAR(3) NOT NULL,
  51. aptno CHAR(5) NOT NULL,
  52. anppfbedrooms INT NOT NULL,
  53. ccid CHAR(4),
  54. PRIMARY KEY (buildingid, aptno),
  55. FOREIGN KEY (buildingid) REFERENCES building (buildingid),
  56. FOREIGN KEY (ccid) REFERENCES corpclient (ccid)
  57. );
  58. CREATE TABLE staffmember (
  59. smemberid CHAR(4) NOT NULL,
  60. smembername VARCHAR(15) NOT NULL,
  61. PRIMARY KEY (smemberid)
  62. );
  63. CREATE TABLE cleaning (
  64. buildingid CHAR(3) NOT NULL,
  65. aptno CHAR(5) NOT NULL,
  66. smemberid CHAR(4) NOT NULL,
  67. CONSTRAINT cleaningpk
  68. PRIMARY KEY (buildingid, aptno, smemberid),
  69. CONSTRAINT cleaningfk
  70. FOREIGN KEY (buildingid, aptno) REFERENCES apartment (buildingid, aptno)
  71. );
  72. -- insert statements
  73. INSERT INTO manager VALUES (
  74. 'M12', 'Boris', 'Grant', '1980-06-20', 60000, null, null
  75. );
  76. INSERT INTO manager VALUES (
  77. 'M23', 'Austin', 'Lee', '1975-10-30', 50000, 5000, null
  78. );
  79. INSERT INTO manager VALUES (
  80. 'M34', 'George', 'Sherman', '1976-01-11', 52000, 2000, null
  81. );
  82. INSERT INTO managerphone VALUES ('M12', '555-2222');
  83. INSERT INTO managerphone VALUES ('M12', '555-3232');
  84. INSERT INTO managerphone VALUES (`M23`, '555-9988');
  85. INSERT INTO managerphone VALUES ('M34', '555-9999');
  86. INSERT INTO building VALUES ('B1', '5', 'M12');
  87. INSERT INTO building VALUES ('B2', '6', 'M23');
  88. INSERT INTO building VALUES ('B3', '4', 'M23');
  89. INSERT INTO building VALUES ('B4', '4', 'M34');
  90. INSERT INTO inspector VALUES ('I11', 'Jane');
  91. INSERT INTO inspector VALUES ('I22', 'Niko');
  92. INSERT INTO inspector VALUES ('I33', 'Mick');
  93. INSERT INTO inspecting VALUES (
  94. 'I11','B1','2012-05-15','2013-05-14'
  95. );
  96. INSERT INTO inspecting VALUES (
  97. 'I11','B2','2013-02-17','2013-05-17'
  98. );
  99. INSERT INTO inspecting VALUES (
  100. 'I22','B2','2013-02-17','2013-05-17'
  101. );
  102. INSERT INTO inspecting VALUES (
  103. 'I22','B3','2013-01-11','2014-01-11'
  104. );
  105. INSERT INTO inspecting VALUES (
  106. 'I33','B3','2013-01-12','2014-01-12'
  107. );
  108. INSERT INTO inspecting VALUES (
  109. 'I33','B4','2013-01-11','2014-01-11'
  110. );
  111. INSERT INTO corpclient VALUES (
  112. 'C111', 'BlingNotes', 'Music', 'Chicago', null
  113. );
  114. INSERT INTO corpclient VALUES (
  115. 'C222', 'SkyJet', 'Airline', 'Oak Park', 'C111'
  116. );
  117. INSERT INTO corpclient VALUES (
  118. 'C777', 'WindyCT', 'Music', 'Chicago', 'C222'
  119. );
  120. INSERT INTO corpclient VALUES (
  121. 'C888', 'SouthAlps', 'Sports', 'Rosemont', 'C777'
  122. );
  123. INSERT INTO apartment VALUES ('B1', '21', 1, 'C111');
  124. INSERT INTO apartment VALUES ('B1', '41', 1, null);
  125. INSERT INTO apartment VALUES ('B2', '11', 2, 'C222');
  126. INSERT INTO apartment VALUES ('B2', '31', 2, null);
  127. INSERT INTO apartment VALUES ('B3', '11', 2, 'C777');
  128. INSERT INTO apartment VALUES ('B4', '11', 2, 'C777');
  129. INSERT INTO staffmember VALUES ('5432', 'Brian');
  130. INSERT INTO staffmember VALUES ('9876', 'Boris');
  131. INSERT INTO staffmember VALUES ('7652', 'Caroline');
  132. INSERT INTO cleaning VALUES ('B1', '21', '5432');
  133. INSERT INTO cleaning VALUES ('B1', '41', '9876');
  134. INSERT INTO cleaning VALUES ('B2', '31', '5432');
  135. INSERT INTO cleaning VALUES ('B2', '11', '9876');
  136. INSERT INTO cleaning VALUES ('B3', '11', '5432');
  137. INSERT INTO cleaning VALUES ('B4', '11', '7652');
  138. -- Alter Statement 5
  139. ALTER TABLE manager
  140. ADD CONSTRAINT fkresidesin
  141. FOREIGN KEY (mresbuildingid) REFERENCES building (buildingid);
  142. -- Update Statement 3
  143. UPDATE manager
  144. SET mresbuildingid = 'B1'
  145. WHERE managerid = 'M12';
  146. -- Update Statement 4
  147. UPDATE manager
  148. SET mresbuildingid = 'B2'
  149. WHERE managerid = 'M23';
  150. -- Update Statement 5
  151. UPDATE manager
  152. SET mresbuildingid = 'B3'
  153. WHERE managerid = 'M34';
  154. -- Alter Statement 6
  155. ALTER TABLE manager
  156. MODIFY mresbuildingid CHAR(3) NOT NULL;
  157. -- drop first 7 tables
  158. DROP TABLE cleaning;
  159. DROP TABLE staffmember;
  160. DROP TABLE apartment;
  161. DROP TABLE corpclient;
  162. DROP TABLE inspecting;
  163. DROP TABLE inspector;
  164. DROP TABLE managerphone;
  165. -- Alter Statement 7
  166. ALTER TABLE manager
  167. DROP FOREIGN KEY fkresidesin;
  168. -- drop last 2 tables
  169. DROP TABLE building;
  170. DROP TABLE manager;
  171. DROP TABLE manager;
  172. DROP TABLE building;
  173. -- Query 39
  174. SELECT c.ccname AS client, r.ccname AS recommender
  175. FROM corpclient c, corpclient r
  176. WHERE r.ccid = c.ccidreferredby;
  177. -- Query 40
  178. SELECT a.buildingid, a.aptno, c.ccname
  179. FROM apartment a, corpclient c
  180. ON a.ccid = c.ccid;
  181. -- Query 41
  182. SELECT a.buildingid, a.aptno, c.ccname
  183. FROM apartment a, LEFT OUTER JOIN corpclient c
  184. ON a.ccid = c.ccid;
  185. -- Query 42
  186. SELECT a.buildingid, a.aptno, c.ccname
  187. FROM apartment a, RIGHT OUTER JOIN corpclient c
  188. ON a.ccid = c.ccid;
  189. -- Query 43
  190. SELECT a.buildingid, a.aptno, c.ccname
  191. FROM apartment a, LEFT OUTER JOIN corpclient c
  192. ON a.ccid = c.ccid
  193. UNION
  194. --
  195. SELECT a.buildingid, a.aptno, c.ccname
  196. FROM apartment a, RIGHT OUTER JOIN corpclient c
  197. ON a.ccid = c.ccid;
  198. -- Query 44
  199. SELECT m.managerid, m.fname, m.mlname, s.smemberid
  200. FROM manager m, staffmember s
  201. WHERE m.mfname = s.smembername;
  202. -- Query 45
  203. SELECT *
  204. FROM manager
  205. WHERE mbonus IS NULL;
  206. -- Query 46
  207. SELECT *
  208. FROM building
  209. WHERE EXISTS (
  210. SELECT *
  211. FROM manager
  212. WHERE buildingid = mresbuildingid
  213. );
  214. -- Query 47
  215. SELECT *
  216. FROM building
  217. WHERE NOT EXISTS (
  218. SELECT *
  219. FROM manager
  220. WHERE buildingid = mresbuildingid
  221. );
  222. -- Create Table Statement 1
  223. CREATE TABLE cleaningdenormalized (
  224. buildingid CHAR(3) NOT NULL,
  225. aptno CHAR(5) NOT NULL,
  226. smemberid CHAR(4) NOT NULL,
  227. smembername VARCHAR(15) NOT NULL,
  228. PRIMARY KEY (buildingid, aptno, smemberid)
  229. );
  230. -- Insert Statement 2
  231. INSERT INTO cleaningdenormalized
  232. SELECT c.buildingid, c.aptno, s.smemberid, s.smembername
  233. FROM cleaning c, staffmember s
  234. WHERE c.smemberid = s.smemberid;