08. SQL/exercise/02. exercise-select-from-where.ipynb

{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.7.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**This notebook is an exercise in the [SQL](https://www.kaggle.com/learn/intro-to-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/dansbecker/select-from-where).**\n\n---\n","metadata":{}},{"cell_type":"markdown","source":"# Introduction\n\nTry writing some **SELECT** statements of your own to explore a large dataset of air pollution measurements.\n\nRun the cell below to set up the feedback system.","metadata":{}},{"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql.ex2 import *\nprint(\"Setup Complete\")","metadata":{"execution":{"iopub.status.busy":"2022-07-11T18:09:10.956750Z","iopub.execute_input":"2022-07-11T18:09:10.957265Z","iopub.status.idle":"2022-07-11T18:09:49.532624Z","shell.execute_reply.started":"2022-07-11T18:09:10.957175Z","shell.execute_reply":"2022-07-11T18:09:49.531735Z"},"trusted":true},"execution_count":1,"outputs":[]},{"cell_type":"markdown","source":"The code cell below fetches the `global_air_quality` table from the `openaq` dataset.  We also preview the first five rows of the table.","metadata":{}},{"cell_type":"code","source":"from google.cloud import bigquery\n\n# Create a \"Client\" object\nclient = bigquery.Client()\n\n# Construct a reference to the \"openaq\" dataset\ndataset_ref = client.dataset(\"openaq\", project=\"bigquery-public-data\")\n\n# API request - fetch the dataset\ndataset = client.get_dataset(dataset_ref)\n\n# Construct a reference to the \"global_air_quality\" table\ntable_ref = dataset_ref.table(\"global_air_quality\")\n\n# API request - fetch the table\ntable = client.get_table(table_ref)\n\n# Preview the first five lines of the \"global_air_quality\" table\nclient.list_rows(table, max_results=5).to_dataframe()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T18:11:12.478033Z","iopub.execute_input":"2022-07-11T18:11:12.478413Z","iopub.status.idle":"2022-07-11T18:11:13.892597Z","shell.execute_reply.started":"2022-07-11T18:11:12.478384Z","shell.execute_reply":"2022-07-11T18:11:13.891247Z"},"trusted":true},"execution_count":5,"outputs":[]},{"cell_type":"markdown","source":"# Exercises\n\n### 1) Units of measurement\n\nWhich countries have reported pollution levels in units of \"ppm\"?  In the code cell below, set `first_query` to an SQL query that pulls the appropriate entries from the `country` column.\n\nIn case it's useful to see an example query, here's some code from the tutorial:\n\n```\nquery = \"\"\"\n        SELECT city\n        FROM `bigquery-public-data.openaq.global_air_quality`\n        WHERE country = 'US'\n        \"\"\"\n```","metadata":{}},{"cell_type":"code","source":"# Query to select countries with units of \"ppm\"\nfirst_query = \"\"\"\n            SELECT country\n            FROM `bigquery-public-data.openaq.global_air_quality`\n            WHERE unit = \"ppm\"\n            \"\"\"\n\n# Set up the query (cancel the query if it would use too much of \n# your quota, with the limit set to 10 GB)\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nfirst_query_job = client.query(first_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nfirst_results = first_query_job.to_dataframe()\n\n# View top few rows of results\nprint(first_results.head())\n\n# Check your answer\nq_1.check()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T18:15:26.458411Z","iopub.execute_input":"2022-07-11T18:15:26.458831Z","iopub.status.idle":"2022-07-11T18:15:41.788477Z","shell.execute_reply.started":"2022-07-11T18:15:26.458801Z","shell.execute_reply":"2022-07-11T18:15:41.786937Z"},"trusted":true},"execution_count":12,"outputs":[]},{"cell_type":"markdown","source":"For the solution, uncomment the line below.","metadata":{}},{"cell_type":"code","source":"#q_1.solution()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T18:15:41.790863Z","iopub.execute_input":"2022-07-11T18:15:41.791280Z","iopub.status.idle":"2022-07-11T18:15:41.796664Z","shell.execute_reply.started":"2022-07-11T18:15:41.791245Z","shell.execute_reply":"2022-07-11T18:15:41.795202Z"},"trusted":true},"execution_count":13,"outputs":[]},{"cell_type":"markdown","source":"### 2) High air quality\n\nWhich pollution levels were reported to be exactly 0?  \n- Set `zero_pollution_query` to select **all columns** of the rows where the `value` column is 0.\n- Set `zero_pollution_results` to a pandas DataFrame containing the query results.","metadata":{}},{"cell_type":"code","source":"# Query to select all columns where pollution levels are exactly 0\nzero_pollution_query = \"\"\"\n                    SELECT *\n                    FROM `bigquery-public-data.openaq.global_air_quality`\n                    WHERE value = 0\n                    \"\"\"\n\n# Set up the query\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nquery_job = client.query(zero_pollution_query, job_config=safe_config)\n\n# API request - run the query and return a pandas DataFrame\nzero_pollution_results = query_job.to_dataframe()\n\nprint(zero_pollution_results.head())\n\n# Check your answer\nq_2.check()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T18:17:43.801960Z","iopub.execute_input":"2022-07-11T18:17:43.802989Z","iopub.status.idle":"2022-07-11T18:18:07.414508Z","shell.execute_reply.started":"2022-07-11T18:17:43.802943Z","shell.execute_reply":"2022-07-11T18:18:07.413240Z"},"trusted":true},"execution_count":16,"outputs":[]},{"cell_type":"markdown","source":"For the solution, uncomment the line below.","metadata":{}},{"cell_type":"code","source":"#q_2.solution()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T18:18:13.040208Z","iopub.execute_input":"2022-07-11T18:18:13.040570Z","iopub.status.idle":"2022-07-11T18:18:13.045558Z","shell.execute_reply.started":"2022-07-11T18:18:13.040541Z","shell.execute_reply":"2022-07-11T18:18:13.044385Z"},"trusted":true},"execution_count":17,"outputs":[]},{"cell_type":"markdown","source":"That query wasn't too complicated, and it got the data you want. But these **SELECT** queries don't organizing data in a way that answers the most interesting questions. For that, we'll need the **GROUP BY** command. \n\nIf you know how to use [`groupby()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html) in pandas, this is similar. But BigQuery works quickly with far larger datasets.\n\nFortunately, that's next.","metadata":{}},{"cell_type":"markdown","source":"# Keep going\n**[GROUP BY](https://www.kaggle.com/dansbecker/group-by-having-count)** clauses and their extensions give you the power to pull interesting statistics out of data, rather than receiving it in just its raw format.","metadata":{}},{"cell_type":"markdown","source":"---\n\n\n\n\n*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/intro-to-sql/discussion) to chat with other learners.*","metadata":{}}]}