There are two tables. One is CITY, the other is COUNTRY. CREATE TABLE CITY ( ID

Question

There are two tables. One is CITY, the other is COUNTRY.

CREATE TABLE CITY (

ID INTEGER NOT NULL,

Name NVARCHAR2(50) NOT NULL ,

CountryCode CHAR(3) NOT NULL ,

District NVARCHAR2(50) NOT NULL ,

Population INTEGER NOT NULL,

PRIMARY KEY (ID)

);

CREATE TABLE country (

Code     NVARCHAR2(3) DEFAULT ON NULL '' NOT NULL,

Name     NVARCHAR2(52) DEFAULT ON NULL '' NOT NULL,

--        'Asia','Europe','North America','Africa','Oceania','Antarctica','South America',

Continent    NVARCHAR2(20) DEFAULT ON NULL 'Asia' NOT NULL,

Region     NVARCHAR2(26) DEFAULT ON NULL '' NOT NULL,

SurfaceArea BINARY_FLOAT DEFAULT ON NULL 0 NOT NULL,

IndepYear     INTEGER DEFAULT 0,

Population     INTEGER DEFAULT ON NULL 0 NOT NULL,

LifeExpectancy      BINARY_FLOAT DEFAULT 0,

GNP      BINARY_FLOAT DEFAULT 0,

GNPOld      BINARY_FLOAT DEFAULT 0,

LocalName     NVARCHAR2(45) DEFAULT ON NULL '' NOT NULL,

GovernmentForm NVARCHAR2(45) DEFAULT ON NULL '' NOT NULL,

HeadOfState NVARCHAR2(60) DEFAULT NULL,

Capital     INTEGER DEFAULT 0,

Code2     NVARCHAR2(2) DEFAULT ON NULL '' NOT NULL,

PRIMARY KEY ( Code )

);

1. Write a single SQL statement to list all the city names, country names in Africa only from 2 tables city and country where the country codes matches and ordered by city name. Hint: Use a join.

2. Write a single SQL statement to find the count of all cities in the countries that are on the continent of "Europe".

Hint: Use a SQL join

Explanation / Answer

Answer: 1

SELECT T1.Name, T2.Name FROM CITY T1 INNER JOIN Country T2 ON T1.CountryCode = T2.Code WHERE T2.Continent = 'Africa' ORDER BY T1.NAME ASC

Explanation: Here in this sql query we have used inner join on the bases of common field country code. We also used where clause to get the cities of Africa Continent then we have used order by clause to get the cities in ascending order.

Answer: 2

SELECT COUNT(*) FROM (SELECT T1.Name, T2.Name FROM CITY T1 INNER JOIN Country T2 ON T1.CountryCode = T2.Code WHERE T2.Continent = 'Europe')

Explanation: Here in the sql query we first used join to get the cities of Europe continent then used count(*) to get the total count of all the cities.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.