Create the following tables in your database with the following schema: Table: Countries | Column Name 1 Туре | country_id country_name I varcharI I int country_id is the primary key for this table. Each row of this table contains the ID and the name of one country. Table: Weather | Column Name | Туре I ----- | int country_id weather_state | int day | date | +------+ (country_id, day) is the primary key for this table. Each row of this table indicates the weather state in a country for one day. Add the following data to your tables: Input: Countries table: | country_id I country_name | +- | USA | Australia 2 | 3 Peru I China | Morocco | Spain 9
Create the following tables in your database with the following schema: Table: Countries | Column Name 1 Туре | country_id country_name I varcharI I int country_id is the primary key for this table. Each row of this table contains the ID and the name of one country. Table: Weather | Column Name | Туре I ----- | int country_id weather_state | int day | date | +------+ (country_id, day) is the primary key for this table. Each row of this table indicates the weather state in a country for one day. Add the following data to your tables: Input: Countries table: | country_id I country_name | +- | USA | Australia 2 | 3 Peru I China | Morocco | Spain 9
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
Write the full sql query including (create,insert,select...) please
Transcribed Image Text:Create the following tables in your database with the following schema:
Table: Countries
+-
| Column Name
Туре
| country_id
| country_name
| int
| varcharI
-------+
country_id is the primary key for this table.
Each row of this table contains the ID and the name of one country.
Table: Weather
+-----
| Column Name
| Type |
country_id
| weather state
| day
| int
int
| date |
+-
+------+
(country_id,
Each row of this table indicates the weather state in a country for one day.
day) is the primary key for this table.
Add the following data to your tables:
Input:
Countries table:
+-
-----
| country_id | country_name |
+
| 2
| 3
| USA
| Australia
| Peru
I China
| Morocco
| Spain
| 5
+-
Weather table:
+--
| country_id | weather_state | day
| 2
| 2
| 2
| 3
| 3
| 3
| 5
| 5
| 5
| 7
| 7
| 3
| 16
| 18
| 21
| 25
| 22
| 20
| 25
| 27
| 31
| 15
| 12
| 12
| -2
| 2019-11-07 |
| 2019-11-09 I
| 2019-11-23 |
| 2019-11-28
| 2019-12-01 |
| 2019-12-02 |
| 2019-11-05 |
| 2019-11-15 |
| 2019-11-25 |
| 2019-10-23 |
| 2019-11-01
| 2019-10-28
| 2019-10-27
| 2019-11-10 |
| 2019-11-11
| 2019-11-12
| 8
| 9
| 3
2019-12-23
Transcribed Image Text:Write an SQL query to find the type of weather in each country for November 2019.
The type of weather is:
• Cold if the average weather_state is less than or equal 15,
• Hot if the average weather_state is greater than or equal to 25, and
• Warm otherwise.
Return result table in any order.
The results should be:
Output:
+-
I country_name | weather_type
| USA
| Australia
| Peru
| Morocco
| China
| Cold
I Cold
| Hot
| Hot
I Warm
Explanation:
Average weather_state in USA in November is (15) /1= 15 so weather type is Cold.
Average weather_state in Austraila in November is (-2 + 0 + 3) /3 = 0.333 so weather type is Cold.
Average weather_state in Peru in November is (25) / 1 = 25 so the weather type is Hot.
Average weather_state in China in November is (16 + 18 + 21)/3 = 18.333 so weather type is Warm.
Average weather_state in Morocco in November is (25 + 27+31)/3 = 27.667 so weather type is Hot.
We know nothing about the average weather_state in Spain in November so we do not include it in
the result table.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 3 steps
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY