Objective:

The objective of this assignment is to get more experience in SQL and Relational Databases. Basically, you will design, create, and write SQL queries using MySQL DBMS.

Requirements:

In this assignment, you are asked to design and create a Weather database that includes weather reports on wind and temperature that were collected eight different stations.

Creating the database and importing data:

The data to be loaded in the database is provided to you in three CSV files. You will use the following 3 files, located in D2L (location.csv,temperature.csv, and wind.csv), for this Assignment. Open each file and familiarize yourself with the data format. The data in these files is interpreted as follows:

location.csv: station name, latitude, longitude

wind.csv: station name, year, month, wind speed

temperature.csv: station name, year, month, temperature

Create database tables to hold the data given in the three files. Make sure to identify primary keys and foreign keys as appropriate.

Load the data from the given data files into the database tables. In MySQL you can load data using the following syntax (assuming the file is placed in the directory c:/ProgramData/MySQL/MySQL Server 8.0/Uploads on your c drive):

load data infile 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/location.csv' into table Location fields terminated by ',' lines terminated by ' ';

The Wind table has null values (as does the temperature table) so the load might have to look like this:

load data infile 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/wind.csv' into table wind fields terminated by ',' lines terminated by ' ' (stationname, year, month, @vfour) SET windspeed = NULLIF(@vfour,'');

If you get an error from MySQL that the file cannot be read, you can change the file permissions as follows: browse to the directory including the file using the file browser, right click on file name, choose Properties and make sure all permissions are set to be Read and Write.

Example for first table location:

mysql> show variables like "secure_file_priv";

+------------------+------------------------------------------------+

| Variable_name | Value |

+------------------+------------------------------------------------+

| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |

+------------------+------------------------------------------------+

1 row in set (0.00 sec)

mysql> create database weather;

Query OK, 1 row affected (0.02 sec)

mysql> use weather;

Database changed

mysql> CREATE TABLE location(stationname varchar(50), latitude int, Longitude int, PRIMARY KEY(stationname));

Query OK, 0 rows affected (0.05 sec)

mysql> load data infile 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/location.csv'

-> into table Location fields terminated by ',' lines terminated by ' ';

Query OK, 8 rows affected (0.03 sec)

Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from location;

+-------------+----------+-----------+

| stationname | latitude | Longitude |

+-------------+----------+-----------+

| Adelaide | 67 | 69 |

| Clean_Air | 90 | 0 |

| Faraday | 65 | 64 |

| Grytviken | 54 | 36 |

| Halley | 75 | 26 |

| Neumayer | 70 | 8 |

| Rothera | 67 | 68 |

| Signy | 60 | 45 |

+-------------+----------+-----------+

8 rows in set (0.00 sec)

SQL Queries:

For each question below, write one or more SQL query to find the required output.

Produce a list of station name, year, month, wind speed, temperature.

For each station, find the total number of valid wind reports and the total number of valid temperature reports. (Note: do not count NULL as a valid report).

For each station, find the total number of wind reports and the total number of temperature reports in each year. From the output, identify stations that did not report a valid reading every month.

Find how many wind speed reports are collected in each month? How many temperature reports are collected each month?

For each station, find the first year at which the station started to report wind speeds and the first year at which the station started to report temperature readings.

Find the coldest and hottest temperatures for each station.

What is the maximum and minimum temperatures reported in 2000? What are the stations that reported these maximum and minimum temperatures?

What is the average wind speed at 90-degree latitude? (Note: do not look at the data to find what stations are located at 90-degree latitude, however, you have to use 90 in your query)

The name of the weather station at the South Pole is called Clean Air, because very little man-made pollution can be found there. Find out what temperatures were recorded at the South Pole such that the output is sorted by temperatures. When is it Summer and when it is winter in South pole?

For each station, find the maximum, minimum, and average temperature reported in a certain month of the year. Comment on when do you think it is summer and when it is winter for each station.

See below on what to submit:

What to submit:

Submit only one .sql script (file) that includes SQL statements to:

create the database

create the tables

load tables with data

answers to all the 10 queries.

whenever needed, write a comment with each query to answer the question asked on the output of that query.

Make sure that your scripts runs on MySQL without giving any errors. You can test your script on MySQL as follows:

Write all your sql commands in a file and save with extensions .sql (e.g, SQL_and_Advanced_SQL.sql)

Assume, you saved your sql script under the directory c:/ICS311/homework,. Then you can run the script using the following command: