This is an ITSE1303 class,using My SQL workbench, Import the following database: CREATE DATABASE COUNTRIES; USE COUNTRIES; DROP TABLE IF EXISTS `City`; CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1; -- -- Dumping data for table `City` -- -- ORDER BY: `ID` INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000); INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500); INSERT INTO `City` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800); INSERT INTO `City` VALUES (5,'Amsterdam','NLD','Noord-Holland',731200); INSERT INTO `City` VALUES (6,'Rotterdam','NLD','Zuid-Holland',593321);(3068,'Berlin','DEU','Berliini',3386667); INSERT INTO `City` VALUES (3069,'Hamburg','DEU','Hamburg',1704735); INSERT INTO `City` VALUES (3072,'Frankfurt am Main','DEU','Hessen',643821); INSERT INTO `City` VALUES (3073,'Essen','DEU','Nordrhein-Westfalen',599515); INSERT INTO `City` VALUES (3074,'Dortmund','DEU','Nordrhein-Westfalen',590213); INSERT INTO `City` VALUES (3075,'Stuttgart','DEU','Baden-WÃ¼rttemberg',582443); INSERT INTO `City` VALUES (3928,'Tallahassee','USA','Florida',150624); INSERT INTO `City` VALUES (3929,'Rockford','USA','Illinois',150115); INSERT INTO `City` VALUES (3930,'Pomona','USA','California',149473); INSERT INTO `City` VALUES (4061,'Fall River','USA','Massachusetts',90555); INSERT INTO `City` VALUES (4062,'Kenosha','USA','Wisconsin',89447); INSERT INTO `City` VALUES (4063,'Elgin','USA','Illinois',89408); INSERT INTO `City` VALUES (4064,'Odessa','USA','Texas',89293); -- -- Table structure for table `Country` -- DROP TABLE IF EXISTS `Country`; CREATE TABLE `Country` ( `Code` char(3) NOT NULL DEFAULT '', `Name` char(52) NOT NULL DEFAULT '', `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', `Region` char(26) NOT NULL DEFAULT '', `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00', `IndepYear` smallint(6) DEFAULT NULL, `Population` int(11) NOT NULL DEFAULT '0', `LifeExpectancy` float(3,1) DEFAULT NULL, `GNP` float(10,2) DEFAULT NULL, `GNPOld` float(10,2) DEFAULT NULL, `LocalName` char(45) NOT NULL DEFAULT '', `GovernmentForm` char(45) NOT NULL DEFAULT '', `HeadOfState` char(60) DEFAULT NULL, `Capital` int(11) DEFAULT NULL, `Code2` char(2) NOT NULL DEFAULT '', PRIMARY KEY (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `Country` -- -- ORDER BY: `Code` INSERT INTO `Country` VALUES ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,45.9,5976.00,NULL,'Afganistan/Afqanestan','IslamicEmirate','Mohammad Omar',1,'AF'); INSERT INTO `Country` VALUES ('DEU','Germany','Europe','Western Europe',357022.00,1955,82164700,77.4,2133367.00,2102826.00,'Deutschland','Federal Republic','Johannes Rau',3068,'DE'); INSERT INTO `Country` VALUES ('NLD','Netherlands','Europe','Western Europe',41526.00,1581,15864000,78.3,371362.00,360478.00,'Nederland','Constitutional Monarchy','Beatrix',5,'NL'); INSERT INTO `Country` VALUES ('USA','United States','North America','North America',9363520.00,1776,278357000,77.1,8510700.00,8110900.00,'United States','Federal Republic','George W. Bush',3813,'US'); -- -- Table structure for table `CountryLanguage` -- DROP TABLE IF EXISTS `CountryLanguage`; CREATE TABLE `CountryLanguage` ( `CountryCode` char(3) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F', `Percentage` float(4,1) NOT NULL DEFAULT '0.0', PRIMARY KEY (`CountryCode`,`Language`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `CountryLanguage` -- -- ORDER BY: `CountryCode`,`Language` ('USA','English','T',86.2); INSERT INTO `CountryLanguage` VALUES ('USA','French','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','German','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','Italian','F',0.6); INSERT INTO `CountryLanguage` VALUES ('USA','Japanese','F',0.2); ('USA','Portuguese','F',0.2); INSERT INTO `CountryLanguage` VALUES ('USA','Spanish','F',7.5); INSERT INTO `CountryLanguage` VALUES ('USA','Tagalog','F',0.4); INSERT INTO `CountryLanguage` VALUES ('USA','Vietnamese','F',0.2); Add to the database the following table and data: Add a EDUCATION table with the following columns; country code as a primary key, literacy rate, male literacy rate, female literacy rate Add data for the following countries AFG, 38, 52, 24 DEU, 99, 99, 99 NLD, 99, 99, 99 USA, 99, 99, 99 Create and execute the SQL statements for following queries and save the results. Display the languages of the United States Display the cities of Germany and the population of each city Display the country and the female literacy rate of all countries

This is an ITSE1303 class,using My SQL workbench, Import the following database: CREATE DATABASE COUNTRIES; USE COUNTRIES; DROP TABLE IF EXISTS `City`; CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1; -- -- Dumping data for table `City` -- -- ORDER BY: `ID` INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000); INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500); INSERT INTO `City` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800); INSERT INTO `City` VALUES (5,'Amsterdam','NLD','Noord-Holland',731200); INSERT INTO `City` VALUES (6,'Rotterdam','NLD','Zuid-Holland',593321);(3068,'Berlin','DEU','Berliini',3386667); INSERT INTO `City` VALUES (3069,'Hamburg','DEU','Hamburg',1704735); INSERT INTO `City` VALUES (3072,'Frankfurt am Main','DEU','Hessen',643821); INSERT INTO `City` VALUES (3073,'Essen','DEU','Nordrhein-Westfalen',599515); INSERT INTO `City` VALUES (3074,'Dortmund','DEU','Nordrhein-Westfalen',590213); INSERT INTO `City` VALUES (3075,'Stuttgart','DEU','Baden-WÃ¼rttemberg',582443); INSERT INTO `City` VALUES (3928,'Tallahassee','USA','Florida',150624); INSERT INTO `City` VALUES (3929,'Rockford','USA','Illinois',150115); INSERT INTO `City` VALUES (3930,'Pomona','USA','California',149473); INSERT INTO `City` VALUES (4061,'Fall River','USA','Massachusetts',90555); INSERT INTO `City` VALUES (4062,'Kenosha','USA','Wisconsin',89447); INSERT INTO `City` VALUES (4063,'Elgin','USA','Illinois',89408); INSERT INTO `City` VALUES (4064,'Odessa','USA','Texas',89293); -- -- Table structure for table `Country` -- DROP TABLE IF EXISTS `Country`; CREATE TABLE `Country` ( `Code` char(3) NOT NULL DEFAULT '', `Name` char(52) NOT NULL DEFAULT '', `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', `Region` char(26) NOT NULL DEFAULT '', `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00', `IndepYear` smallint(6) DEFAULT NULL, `Population` int(11) NOT NULL DEFAULT '0', `LifeExpectancy` float(3,1) DEFAULT NULL, `GNP` float(10,2) DEFAULT NULL, `GNPOld` float(10,2) DEFAULT NULL, `LocalName` char(45) NOT NULL DEFAULT '', `GovernmentForm` char(45) NOT NULL DEFAULT '', `HeadOfState` char(60) DEFAULT NULL, `Capital` int(11) DEFAULT NULL, `Code2` char(2) NOT NULL DEFAULT '', PRIMARY KEY (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `Country` -- -- ORDER BY: `Code` INSERT INTO `Country` VALUES ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,45.9,5976.00,NULL,'Afganistan/Afqanestan','IslamicEmirate','Mohammad Omar',1,'AF'); INSERT INTO `Country` VALUES ('DEU','Germany','Europe','Western Europe',357022.00,1955,82164700,77.4,2133367.00,2102826.00,'Deutschland','Federal Republic','Johannes Rau',3068,'DE'); INSERT INTO `Country` VALUES ('NLD','Netherlands','Europe','Western Europe',41526.00,1581,15864000,78.3,371362.00,360478.00,'Nederland','Constitutional Monarchy','Beatrix',5,'NL'); INSERT INTO `Country` VALUES ('USA','United States','North America','North America',9363520.00,1776,278357000,77.1,8510700.00,8110900.00,'United States','Federal Republic','George W. Bush',3813,'US'); -- -- Table structure for table `CountryLanguage` -- DROP TABLE IF EXISTS `CountryLanguage`; CREATE TABLE `CountryLanguage` ( `CountryCode` char(3) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F', `Percentage` float(4,1) NOT NULL DEFAULT '0.0', PRIMARY KEY (`CountryCode`,`Language`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `CountryLanguage` -- -- ORDER BY: `CountryCode`,`Language` ('USA','English','T',86.2); INSERT INTO `CountryLanguage` VALUES ('USA','French','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','German','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','Italian','F',0.6); INSERT INTO `CountryLanguage` VALUES ('USA','Japanese','F',0.2); ('USA','Portuguese','F',0.2); INSERT INTO `CountryLanguage` VALUES ('USA','Spanish','F',7.5); INSERT INTO `CountryLanguage` VALUES ('USA','Tagalog','F',0.4); INSERT INTO `CountryLanguage` VALUES ('USA','Vietnamese','F',0.2); Add to the database the following table and data: Add a EDUCATION table with the following columns; country code as a primary key, literacy rate, male literacy rate, female literacy rate Add data for the following countries AFG, 38, 52, 24 DEU, 99, 99, 99 NLD, 99, 99, 99 USA, 99, 99, 99 Create and execute the SQL statements for following queries and save the results. Display the languages of the United States Display the cities of Germany and the population of each city Display the country and the female literacy rate of all countries

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...

See similar textbooks

Related questions

Question

This is an ITSE1303 class,using My SQL workbench,

Import the following database:

CREATE DATABASE COUNTRIES; USE COUNTRIES; DROP TABLE IF EXISTS `City`; CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1; -- -- Dumping data for table `City` -- -- ORDER BY: `ID` INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000); INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500); INSERT INTO `City` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800); INSERT INTO `City` VALUES (5,'Amsterdam','NLD','Noord-Holland',731200); INSERT INTO `City` VALUES (6,'Rotterdam','NLD','Zuid-Holland',593321);(3068,'Berlin','DEU','Berliini',3386667); INSERT INTO `City` VALUES (3069,'Hamburg','DEU','Hamburg',1704735); INSERT INTO `City` VALUES (3072,'Frankfurt am Main','DEU','Hessen',643821); INSERT INTO `City` VALUES (3073,'Essen','DEU','Nordrhein-Westfalen',599515); INSERT INTO `City` VALUES (3074,'Dortmund','DEU','Nordrhein-Westfalen',590213); INSERT INTO `City` VALUES (3075,'Stuttgart','DEU','Baden-WÃ¼rttemberg',582443); INSERT INTO `City` VALUES (3928,'Tallahassee','USA','Florida',150624); INSERT INTO `City` VALUES (3929,'Rockford','USA','Illinois',150115); INSERT INTO `City` VALUES (3930,'Pomona','USA','California',149473); INSERT INTO `City` VALUES (4061,'Fall River','USA','Massachusetts',90555); INSERT INTO `City` VALUES (4062,'Kenosha','USA','Wisconsin',89447); INSERT INTO `City` VALUES (4063,'Elgin','USA','Illinois',89408); INSERT INTO `City` VALUES (4064,'Odessa','USA','Texas',89293);

-- -- Table structure for table `Country` -- DROP TABLE IF EXISTS `Country`; CREATE TABLE `Country` ( `Code` char(3) NOT NULL DEFAULT '', `Name` char(52) NOT NULL DEFAULT '', `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', `Region` char(26) NOT NULL DEFAULT '', `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00', `IndepYear` smallint(6) DEFAULT NULL, `Population` int(11) NOT NULL DEFAULT '0', `LifeExpectancy` float(3,1) DEFAULT NULL, `GNP` float(10,2) DEFAULT NULL, `GNPOld` float(10,2) DEFAULT NULL, `LocalName` char(45) NOT NULL DEFAULT '', `GovernmentForm` char(45) NOT NULL DEFAULT '', `HeadOfState` char(60) DEFAULT NULL, `Capital` int(11) DEFAULT NULL, `Code2` char(2) NOT NULL DEFAULT '', PRIMARY KEY (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `Country` -- -- ORDER BY: `Code` INSERT INTO `Country` VALUES ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,45.9,5976.00,NULL,'Afganistan/Afqanestan','IslamicEmirate','Mohammad Omar',1,'AF'); INSERT INTO `Country` VALUES ('DEU','Germany','Europe','Western Europe',357022.00,1955,82164700,77.4,2133367.00,2102826.00,'Deutschland','Federal Republic','Johannes Rau',3068,'DE'); INSERT INTO `Country` VALUES ('NLD','Netherlands','Europe','Western Europe',41526.00,1581,15864000,78.3,371362.00,360478.00,'Nederland','Constitutional Monarchy','Beatrix',5,'NL'); INSERT INTO `Country` VALUES ('USA','United States','North America','North America',9363520.00,1776,278357000,77.1,8510700.00,8110900.00,'United States','Federal Republic','George W. Bush',3813,'US'); -- -- Table structure for table `CountryLanguage` -- DROP TABLE IF EXISTS `CountryLanguage`; CREATE TABLE `CountryLanguage` ( `CountryCode` char(3) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F', `Percentage` float(4,1) NOT NULL DEFAULT '0.0', PRIMARY KEY (`CountryCode`,`Language`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `CountryLanguage` -- -- ORDER BY: `CountryCode`,`Language`

('USA','English','T',86.2); INSERT INTO `CountryLanguage` VALUES ('USA','French','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','German','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','Italian','F',0.6); INSERT INTO `CountryLanguage` VALUES ('USA','Japanese','F',0.2); ('USA','Portuguese','F',0.2); INSERT INTO `CountryLanguage` VALUES ('USA','Spanish','F',7.5); INSERT INTO `CountryLanguage` VALUES ('USA','Tagalog','F',0.4); INSERT INTO `CountryLanguage` VALUES ('USA','Vietnamese','F',0.2);

Add to the database the following table and data:

Add a EDUCATION table with the following columns;
1. country code as a primary key,
2. literacy rate,
3. male literacy rate,
4. female literacy rate
Add data for the following countries

AFG, 38, 52, 24

DEU, 99, 99, 99

NLD, 99, 99, 99

USA, 99, 99, 99

Create and execute the SQL statements for following queries and save the results.
1. Display the languages of the United States
2. Display the cities of Germany and the population of each city
3. Display the country and the female literacy rate of all countries

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

For this query (select name,female_literacy_rate from country INNER JOIN education on country.CIode2 = education.country_code;)

I ran it and I got the error as shown in the attached image.

### SQL Query Example: Understanding Joins and Troubleshooting Errors

**SQL Query:**
```sql
SELECT name, female_literacy_rate
FROM country
INNER JOIN education
ON country.Clode2 = education.country_code;
```

**Explanation:**
This SQL query attempts to retrieve the `name` and `female_literacy_rate` from the tables `country` and `education`. It uses an `INNER JOIN` to combine rows from both tables where the `country_code` in the `education` table matches the `Clode2` column in the `country` table.

**Error Description:**
- **Error Code: 1054**: "Unknown column 'country.Clode2' in 'on clause'"
- This error suggests there is a misspelling or incorrect column name in the `ON` clause of the `INNER JOIN`. The column `Clode2` likely does not exist in the `country` table. It may be a typo for `Code2` or another existing column name.

**Steps to Resolve:**
1. Check the column names in the `country` table to ensure correct spelling.
2. Replace `Clode2` with the correct column name.
3. Re-run the query after making corrections.

Understanding how to troubleshoot common SQL errors is crucial for effective database management and data analysis. By ensuring accurate column references, we can successfully join datasets and extract valuable insights.

Solution

by Bartleby Expert

SEE SOLUTION

Follow-up Question

There is no language attribute in the table So Not able to run any query.

this is what I have in the table .hopefully that will help to solve this part.

DROP TABLE IF EXISTS `CountryLanguage`; CREATE TABLE `CountryLanguage` ( `CountryCode` char(3) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F', `Percentage` float(4,1) NOT NULL DEFAULT '0.0', PRIMARY KEY (`CountryCode`,`Language`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `CountryLanguage` -- -- ORDER BY: `CountryCode`,`Language` INSERT INTO `CountryLanguage` VALUES ('AFG','Balochi','F',0.9); INSERT INTO `CountryLanguage` VALUES ('AFG','Dari','T',32.1); INSERT INTO `CountryLanguage` VALUES ('AFG','Pashto','T',52.4); INSERT INTO `CountryLanguage` VALUES ('AFG','Turkmenian','F',1.9); INSERT INTO `CountryLanguage` VALUES ('AFG','Uzbek','F',8.8); INSERT INTO `CountryLanguage` VALUES ('DEU','German','T',91.3); INSERT INTO `CountryLanguage` VALUES ('DEU','Greek','F',0.4); INSERT INTO `CountryLanguage` VALUES ('DEU','Italian','F',0.7); INSERT INTO `CountryLanguage` VALUES ('DEU','Polish','F',0.3); INSERT INTO `CountryLanguage` VALUES ('DEU','Southern Slavic Languages','F',1.4); INSERT INTO `CountryLanguage` VALUES ('DEU','Turkish','F',2.6); INSERT INTO `CountryLanguage` VALUES ('NLD','Arabic','F',0.9); INSERT INTO `CountryLanguage` VALUES ('NLD','Dutch','T',95.6); INSERT INTO `CountryLanguage` VALUES ('NLD','Fries','F',3.7); INSERT INTO `CountryLanguage` VALUES ('NLD','Turkish','F',0.8); INSERT INTO `CountryLanguage` VALUES ('USA','English','T',86.2); INSERT INTO `CountryLanguage` VALUES ('USA','French','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','German','F',0.7); INSERT INTO `CountryLanguage` VALUES ('USA','Italian','F',0.6); INSERT INTO `CountryLanguage` VALUES ('USA','Japanese','F',0.2); INSERT INTO `CountryLanguage` VALUES ('USA','Korean','F',0.3); INSERT INTO `CountryLanguage` VALUES ('USA','Polish','F',0.3); INSERT INTO `CountryLanguage` VALUES ('USA','Portuguese','F',0.2); INSERT INTO `CountryLanguage` VALUES ('USA','Spanish','F',7.5); INSERT INTO `CountryLanguage` VALUES ('USA','Tagalog','F',0.4); INSERT INTO `CountryLanguage` VALUES ('USA','Vietnamese','F',0.2);

Solution

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-engineering and related others by exploring similar questions and additional content below.