Menu
Can anyone help me with the following:
Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions.
my try:
select x.name, x.region
from bbc x
where x.population >all
(select population*3
from bbc y
where y.region = x.region)
syntax is correct but no records are returned (should return 3 rows)
Find each country that belongs to a region where all populations are less than 25000000. Show name, region and population.
my try:
select name, region, population
from bbc
where region not in
(select distinct region from bbc
where population >= 25000000)
I used "not in". Is there a way to use "in" ?
675
A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
They help us target specific rows to perform various operations in SQL. They are used to SELECT, UPDATE, INSERT and DELETE records in SQL. There are different types of SQL subquery, like Single-row subquery, multiple row subquery, multiple column subquery, correlated subquery, and nested subquery.
There are three broad types of a subquery in SQL. This chapter from OCA Oracle Database 11g: SQL Fundamentals I Exam Guide explains differences between a single-row subquery, multiple-row subquery and correlated subquery .
SELECT name, region
FROM bbc x
WHERE population/3 >= ALL
(SELECT population
FROM bbc y
WHERE y.region=x.region
AND x.name != y.name)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
