Use the following Relationships diagram to answer all questions.

Question 1 Write Create Table commands to create these five tables. Note that SUPERVISOR_ID must match the RECORDER_ID of another Recorder. Make sure you define all primary and foreign keys. Make reasonable choices for data types. CREATE TABLE STORM( STORM_ID INTEGER, STORM_NAME VARCHAR(30), STORM_START DATE, STORM_END DATE, CONSTRAINT PK1 PRIMARY KEY (STORM_ID))

CREATE TABLE RECORDER( RECORDER_ID INTEGER, RECORDER_NAME VARCHAR(30), RECORDER_DOB DATE, RECORDER_PHONE CHAR(10), SUPERVISOR_ID INTEGER, CONSTRAINT PK2 PRIMARY KEY (RECORDER_ID), CONSTRAINT FK1 FOREIGN KEY (SUPERVISOR_ID) REFERENCES RECORDER) CREATE TABLE STATION( STATION_ID INTEGER, STATION_NAME VARCHAR(30), STATION_CITY VARCHAR(30), STATION_STATE VARCHAR(30), CONSTRAINT PK2 PRIMARY KEY (STATION_ID)) CREATE TABLE REPORT( STATION_ID INTEGER, REPORT_DATE DATE, RECORDER_ID INTEGER, STORM_ID INTEGER, SNOW_TOTAL FLOAT, CONSTRAINT PK4 PRIMARY KEY (STATION_ID, REPORT_DATE), CONSTRAINT FK4 FOREIGN KEY (STATION_ID) REFERENCES STATION, CONSTRAINT FK5 FOREIGN KEY (RECORDER_ID) REFERENCES RECORDER, CONSTRAINT FK6 FOREIGN KEY (STORM_ID) REFERENCES STORM ) CREATE TABLE INTERN( INTERN_ID INTEGER, INTERN_NAME VARCHAR(30), INTERN_DOB DATE, INTERN_PHONE CHAR(10), RECORDER_ID INTEGER, CONSTRAINT PK5 PRIMARY KEY (INTERN_ID), CONSTRAINT FK8 FOREIGN KEY (RECORDER_ID) REFERENCES RECORDER)

Question 2 Write a query that will display the name and city of all stations in the state of New York. SELECT STATION_NAME, STATION_CITY FROM STATION WHERE STATION_STATE = 'New York'

Question 3 Write a query that will display the name of all storms that started between October 1, 2013 and May 1, 2014. SELECT STORM_NAME FROM STORM WHERE STORM_START BETWEEN #10/1/2013# and #5/1/2014# Question 4 Write a query that will display the name, start date, and end date for all storms with names that begin with the letter Q. SELECT STORM_NAME, STORM_START, STORM_END FROM STORM WHERE STORM_NAME LIKE 'Q*' Question 5 Write a query that will display the name and phone of all interns and recorders. Assume that no one is both an intern and a recorder. SELECT INTERN_NAME, INTERN_PHONE FROM INTERN UNION SELECT RECORDER_NAME, RECORDER_PHONE FROM RECORDER

Question 6 Write a query that will display the report date for all reports from station 23 where the snow total was NULL. SELECT REPORT_DATE FROM REPORT WHERE STATION_ID = 23 AND SNOW_TOTAL IS NULL Question 7 Write a query that will display the name of interns with the name of the recorder they work with. SELECT INTERN_NAME, RECORDER_NAME FROM INTERN, RECORDER WHERE INTERN.RECORDER_ID = RECORDER.RECORDER_ID Question 8 Write a query that will display the name of recorders with the name of their supervisor. SELECT RECORDER.RECORDER_NAME, SUPERVISOR.RECORDER_NAME FROM RECORDER, RECORDER AS SUPERVISOR WHERE RECORDER.SUPERVISOR_ID = SUPERVISOR.RECORDER_ID Question 9 Write a query that will display the maximum snow total in a report for station 23. SELECT MAX(SNOW_TOTAL) FROM REPORT WHERE STATION_ID = 23 Question 10 Write a query that will display the sum of all snow totals for station 23 in 2013. SELECT SUM(SNOW_TOTAL) FROM REPORT WHERE STATION_ID = 23 AND YEAR(REPORT_DATE) = 2013

Question 11 Write a query that will display the sum of all snow totals in 2013 for all stations. SELECT STATION_ID, SUM(SNOW_TOTAL) FROM REPORT WHERE YEAR(REPORT_DATE) = 2013 GROUP BY STATION_ID Question 12 Write a query that will display the sum of all snow totals in 2013 for all stations where the sum of the snow total is over 12. SELECT STATION_ID, SUM(SNOW_TOTAL) FROM REPORT WHERE YEAR(REPORT_DATE) = 2013 GROUP BY STATION_ID HAVING SUM(SNOW_TOTAL) > 12

Question 13 Write a query that will display the number of days in 2013 where the snow total was not NULL at station 23. SELECT COUNT(*) FROM REPORT WHERE YEAR(REPORT_DATE) = 2013 AND STATION_ID = 23 AND SNOW_TOTAL IS NOT NULL Question 14 Write a query that will display for all reports the name of the station, the report date, the recorder name, the storm name, and the snow total. SELECT STATION_NAME, REPORT_DATE, RECORDER_NAME, STORM_NAME, SNOW_TOTAL FROM REPORT, RECORDER, STATION, STORM WHERE REPORT.STATION_ID = STATION.STATION_ID AND REPORT.STORM_ID = STORM.STORM_ID AND RECORDER.RECORDER_ID = REPORT.RECORDER_ID

Question 15 Write an INSERT statement that will insert a new record into the STORM table. The storm ID should be 121, the name of the new storm is Alice, the start date is 2/20/2014, and the end date is 2/23/2014. INSERT INTO STORM VALUES(121, 'Alice', #2/20/2014#, #2/23/2014#) Question 16 Write an UPDATE statement that will change the name of storm 121 to Alex. UPDATE STORM SET STORM_NAME = 'Alex' WHERE STORM_ID = 121