Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals Of Database Systems

Authors: Sham Navathe,Ramez Elmasri

5th Edition

B01FGJTE0Q, 978-0805317558

More Books

Students also viewed these Databases questions