New Assignments for Mysql Part 2

Now that you have experience creating a database of your own, it is time to test your ability at accessing an existing database.Two databases have been setup for this purpose; meta and obs. You will have select privileges on these databases, which will allow you to query them for information, but no other privileges, which will prevent you from changing them.You will be asked to design queries to gather information from the database and put your answers into a file, Results.txt.
1. Create/open Results.txt with your favorite editor. Put your name and student number on the first line.
2. Open another terminal and log into MySQL by typing mysql -p at the command line. Input the password you were given.
3. Your first step will be to get familiar with meta and obs. List out the tables of each and copy those into Results.txt. Check the structure of each table (hint desc) and copy those into Results.txt.
4. Using the meta database, design a query to determine how many stations are in the database. There are a couple ways to do this. One way involves using COUNT() which returns the number of entries of a given attribute (ie COUNT(Station_ID). Put your query into Results.txt.Test your query on the command line. How many stations are there? Write a query to determine the lowest and highest Station_IDs. You can find the minimum value by using the MIN() function on an attribute (ie)MIN(Station_ID)). MAX() finds the maximum. Put the query into Results.txt and run it on the command line.What are they?Write a query to determine all the different Providing_Agency_IDs.
Make sure you don’t have duplicates!Put the query into Results.txt and run it on the command line.How many different agencies are there and what are their IDs? Finally, produce a query to find out how many stations last reported an observation on Jan 31, 2007. (Hint, Last_Record contains the datetime when a station last reported).Put the query into Results.txt and run it on the command line. How many were there?
5. Continuing with the meta database, design a query to determine how many cities are in the database. Write this into Results.txt and run it. How many cities are there? Design a query to determine how many different city names there are. Write the query into Results.txt and run it. How many different city names are there?
How can you find all the cities that are located in British Columbia?How could we change the database to make this simpler?
6. Design a query to determine what range of dates that are contained in Spacetimes (ie what is the lowest and highest date). Write this into Results.txt and run it. What is the range? Suppose you wanted to know the most common time for entries. Design a query that will determine the most popular time based on the number of Spacetimes for that time. (hint GROUP BY, ORDER BY, and COUNT() are all useful for this). If you need to order or group results that are given by a function (like COUNT()), you can use AS to rename the results
and reference them by . (ie SELECT COUNT(TIME) AS tm FROM Spacetimes ORDER BY tm) When using ORDER BY, you can get enough results that they scroll off the screen and you might not see the initial results. You can change the ordering to be descending by putting DESC after your ORDER BY clause (ie ORDER BY Time DESC).Write your query into Results.txt and run it. What is the most common time for entries? How many entries share this time? What is the least common time? How many entries share that time? (If necessary, use DESC to reverse the order).
7. Now it is time to try some queries involving multiple databases.Switch to the obs database. List out the tables in obs. Copy them into Results.txt. Check the structure of each table. You will notice a certain similarity in structure between tables that are for weather variables. Based on the structure of the Air_Temperature table, how do you tie observations from obs to dates, times and stations from meta? (ie what attributes form relations?)
8. Design a query that would select all precipitation values for the stations near Vancouver, BC (can you do this without using the latitude and longitude?) on Jan 30, 2007 between 3 and 5 UTC.(all time values are stored in UTC) Run the query and write the results and query to Results.txt.
9. Design a query to find the highest and lowest air temperature values.Run the query and write the result and query to Results.txt.Design similar queries for wind speed and precipitation.Run them and write the results and queries to Results.txt. Design a query to find the stations, dates, and time that match the highest and lowest
air temperature values. Design similar queries for the wind speed and precipitation values. Run all the queries and write their results and the queries to Results.txt.
10. Save and close Results.txt.

xvideos

Admin has written 171 articles