Question: Help with SQL , please! We have the following database: world CREATE TABLE public.city ( id int 4 NOT NULL, name text NOT NULL, countrycode

Help with SQL, please!
We have the following database: world
CREATE TABLE public.city (
id int4 NOT NULL,
"name" text NOT NULL,
countrycode bpchar(3) NOT NULL,
district text NOT NULL,
population int4 NOT NULL,
CONSTRAINT city_pkey PRIMARY KEY (id)
);
-- public.city foreign keys
ALTER TABLE public.city ADD CONSTRAINT city_fk FOREIGN KEY (countrycode) REFERENCES public.country(code);
CREATE TABLE public.country (
code bpchar(3) NOT NULL,
"name" text NOT NULL,
continent text NOT NULL,
region text NOT NULL,
surfacearea float4 NOT NULL,
indepyear int2 NULL,
population int4 NOT NULL,
lifeexpectancy float4 NULL,
gnp numeric(10,2) NULL,
gnpold numeric(10,2) NULL,
localname text NOT NULL,
governmentform text NOT NULL,
headofstate text NULL,
capital int4 NULL,
code2 bpchar(2) NOT NULL,
CONSTRAINT country_continent_check CHECK (((continent = 'Asia'::text) OR (continent = 'Europe'::text) OR (continent = 'North America'::text) OR (continent = 'Africa'::text) OR (continent = 'Oceania'::text) OR (continent = 'Antarctica'::text) OR (continent = 'South America'::text))),
CONSTRAINT country_pkey PRIMARY KEY (code)
);
-- public.country foreign keys
ALTER TABLE public.country ADD CONSTRAINT country_capital_fkey FOREIGN KEY (capital) REFERENCES public.city(id);
CREATE TABLE public.countrylanguage (
countrycode bpchar(3) NOT NULL,
"language" text NOT NULL,
isofficial bool NOT NULL,
percentage float4 NOT NULL,
CONSTRAINT countrylanguage_pkey PRIMARY KEY (countrycode, language)
);
-- public.countrylanguage foreign keys
ALTER TABLE public.countrylanguage ADD CONSTRAINT countrylanguage_countrycode_fkey FOREIGN KEY (countrycode) REFERENCES public.country(code);
Write a query to "world" database to find country with surface area under 200000 wich has the greatest number of people who speak English.
What country has more than 4 words in its local name and has area per capita >0.02?
Write a SQL query to find out which worlds capital has the least population?

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!