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
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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 with 7 images
I use the My SQL Workbench ,this question is for My SQL class ,can you provide me the solution using the My SQL workbench ?
by Bartleby Expert
