Basic SQL - Earthquakes

 

  Earthquakes

         1.  Drop the table USEarthquakes if it already exists. Create a new USEarthquakes table with 

              schema (Year, Month, Day, Hour, Minute, Second, Latitude, Longitude, Magnitude).             

              Populate the table with the data from us_earthquakes.csv file.

              First of all, to drop the table if it already exists, we use DROP TABLE <table> IF EXISTS. 

              After that, we use CREATE TABLE <table> to create a new USEarthquakes table. To populate 

              the table, we have to open us_earthquakes file and save the tuples in a list. Then we use             

              executemany because we are adding multiple tuples.

 

        

          Then we have filled USEarthquakes table.

      2.  Remove rows from USEarthquakes that have 0 as the value of the magnitude. Replace 0 

           values in the Day, Hour, Minute, and Second columns with NULL values.

            To remove rows, we generally use DELETE FROM. But, we have to remember that we remove 

            only certain rows. So, we will use WHERE clause. To replace values 0 to NULL, UPDATE 

            <table> SET ~ commands using WHERE also. 

          Therefore, we get this as a result.

      3. Query the USEarthquakes table for 19th and 20th century earthquakes magnitude 

          respectively. Also query the average magnitude of all the earthquakes. Lastly, plot two 

          subplots of the magnitudes from each 19th and 20th century.

          

          To query the magnitudes values, we will first use SELECT and fetchall commands while we are 

          using WHERE clause to get the earthquakes occurred in a correct category. To query the average 

          magnitude, on the other hand, we will also use AVG() function. After getting all the values, we 

          will subplot using Matplotlib.

               

          As result, we get 5.24777123633309 as the average value for the magnitude and the plots:

            *GitHub - https://github.com/KwakSukyoung/coding/blob/master/ACME/SQL1/sql1.py