Hello! Help with SQL$,$please$!$

We have the following database: world.

CREATE TABLE city $($

id int$4$ NOT NULL,

"name" text NOT NULL,

countrycode bpchar$(3)$ NOT NULL,

district text NOT NULL,

population int$4$ NOT NULL,

CONSTRAINT city$\_$pkey PRIMARY KEY $($id$)$

$)$;

CREATE TABLE country $($

code bpchar$(3)$ NOT NULL,

"name" text NOT NULL,

continent text NOT NULL,

region text NOT NULL,

surfacearea float$4$ NOT NULL,

indepyear int$2$ NULL,

population int$4$ NOT NULL,

lifeexpectancy float$4$ NULL,

gnp numeric$(10,2)$ NULL,

gnpold numeric$(10,2)$ NULL,

localname text NOT NULL,

governmentform text NOT NULL,

headofstate text NULL,

capital int$4$ NULL,

code$2$ 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$)$

$)$;

$--$ country foreign keys

ALTER TABLE country ADD CONSTRAINT country$\_$capital$\_$fkey FOREIGN KEY $($capital$)$ REFERENCES city$($id$)$;

$--$ city foreign keys

ALTER TABLE city ADD CONSTRAINT city$\_$fk FOREIGN KEY $($countrycode$)$ REFERENCES country$($code$)$;

In this task, students will be asked to rewrite existing queries using different SQL elements.

Use the world database to test your code.

The first query:

The following query finds cities with high population $(>=8$m$)$ which are not capitals.

Rewrite this query without a subquery:

select $*$ from city as ct

where not exists $($select $1$ from country as c

where c$.$capital$=$ct$.$id$)$

and population $>=8000000$;

The second query:

You have a query to find cities that have population greater than average city population in the same country.

Rewrite this query using a derived table in FROM. The new version of the query should not include a subquery in where.

select c$.$name countryname, ct$.$name cityname, ct$.$population

as city$\_$population

from city as ct

join country as c on c$.$code $=$ ct$.$countrycode

where ct$.$population $>$

$($select avg$($population$)$ as avg$\_$population

from city

where countrycode $=$ ct$.$countrycode $)$

order by c$.$"name" $,$ ct$.$"name";