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
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
- 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
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
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.
by Bartleby ExpertThere 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);
by Bartleby Expert
