window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-63172957-1');
Sound
Awwwards
</html>
Created by potrace 1.16, written by Peter Selinger 2001-2019
Back to blog
SQL & Databases

Database Queries to One Table (Part 3)

Article contents:

This is an excerpt from the Mondial database that is widely used for practice exercises in this article.

Table 12: Extract from the MONDIAL Database
Country
NameCodeCapitalProvinceAreaPopulationContinent%
AustraliaAUSCanberraAustraliaCapitalTerritory768685018260863Australia/ Oceania100
BoliviaBOLLa PazBolivia10985807165257America100
BrazilBRBrasiliaDistrito Federal8511960162661214America100
CanadaCDNOttawaOntario99761409976140America100
ChinaTJBeijingBeijing (munic.)95969601210004956Asia100
EgyptETCairoEl Qahira100145063575107Africa90
EgyptETCairoEl Qahira100145063575107Asia10
FijiFJISuvaFiji18270782381Australia/ Oceania100
FranceFParisIle de France54703058317450Europe100
GermanyDBerlinBerlin35691083536115Europe100
IndiaINDNew DelhiDelhi3287590952107694Asia100
IndonesiaRIJakartaIndonesia1919440206611600Asia80
IndonesiaRIJakartaIndonesia1919440206611600Australia/ Oceania20
LibyaLARTripoliLibya17595405445436Africa100
MaliRMMBarnakoMali12400009653261Africa100
TurkeyTRAnkaraAnkara78058062484478Asia95
United StatesUSAWashington DCDistr. Columbia9372610266476278America100

Subqueries with Nested SELECT Statements

n SQL, it is possible to formulate further SELECT statements within SELECT statements. These nested SELECT statements are called subqueries or subselects. They are often used to formulate filter conditions in the context of WHERE or HAVING.

The following example illustrates the use of subqueries using the Country table: a duplicate-free, sorted list is to be output with names and areas of all countries that are larger than the average area of all countries. The SQL statement used for this purpose is shown in picture

The subquery shown here is used to determine a dynamic comparison value from the current dataset. A general schema for subqueries in SELECT statements is shown in picture

Subqueries are used when the comparison criterion is dynamic and not known at the time the SQL query is created. In the above example, the comparison value was calculated on the basis of the current dataset of a table. Which comparison operators are possible is determined by the result type of the subquery. Subqueries with exactly one return value can be distinguished from subqueries with multiline returns.

Subqueries with Exactly One Return Value

Although the result set of a SELECT statement is always a table, it can be required that this table contain exactly one value. In this case the result set of the SELECT statement of the subquery will contain exactly one column that contains exactly one data record. Only if these conditions are fulfilled can the already-known comparison operators be used. As shown in the above example, these conditions are fulfilled by the use of aggregate functions. In the event that no aggregate function is used, it must be ensured that the conditions for the result set are still fulfilled. Otherwise, an error message will be issued when the SELECT statement is executed.

Subqueries with Multiline Returns

An example of subqueries with multiline returns is shown in the picture which queries the names and capitals of the countries in the Country table that are located on a continent for which more than six countries are stored in Country.

The result table of the subquery contains two records:

Continent
Asien
Europe

The comparison operator IN checks whether a particular value (specified to the left of IN) is contained in a set of values (specified to the right of IN). Further typical comparison operators for quantities are explained in Table

 Typical Comparison Operators for Quantities
OperatorDescriptionExample
Columncomparative operatorALL value setThe value from the named column is compared with all values of the value set by a comparison operator (e.g., <, >, >=, <=). ALL is evaluated to TRUE if the comparison for all records of the subquery evaluate to TRUE. Population FROM Country WHERE (Population/Area) > ALL (SELECT Population/Area FROM Country WHEREContinent=╩╝Europe╩╝)ORDER BY Name;Returns the countries whose population density is greater than that of all countries in Europe. Note: Uses division as a mathematical function of SQL.
Column comparative operatorANY value setThe value of the named column is compared with all values of the value set by a comparison operator (e.g., <, >, >=, <=). ANY is evaluated to TRUE if the comparison is evaluated to TRUE for at least one subquery record. SELECT> Name, Area FROM Country WHERE Area >= ANY (SELECT SUM(Area) FROM Country GROUP BY Continent); Returns the countries whose area is greater than or equal to the total area of a continent.
EXISTSChecks whether the result set contains at least one data record. Usually requires a link from the main query to the subquery in the WHERE part of the subquery.SELECT Lastname, Firstname FROM Customer a WHERE EXISTS (SELECTDelivery AddressForCustomer FROM Address b WHEREb.Delivery AdressForCustomer =a.CustomerID)Returns names and first names of customers for whom a delivery address has been saved.

 



Back to blog

</html>
Wordpress Developer Loader, Web Developer Loader , Front End Developer Loader Jack is thinking