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

Database Queries to One Table (Part 1)

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
AustraliaAUSCanberraAustraliaCapitalTerritory768685018260863Australia/ Oceania100
BoliviaBOLLa PazBolivia10985807165257America100
BrazilBRBrasiliaDistrito Federal8511960162661214America100
ChinaTJBeijingBeijing (munic.)95969601210004956Asia100
EgyptETCairoEl Qahira100145063575107Africa90
EgyptETCairoEl Qahira100145063575107Asia10
FijiFJISuvaFiji18270782381Australia/ Oceania100
FranceFParisIle de France54703058317450Europe100
IndiaINDNew DelhiDelhi3287590952107694Asia100
IndonesiaRIJakartaIndonesia1919440206611600Australia/ Oceania20
United StatesUSAWashington DCDistr. Columbia9372610266476278America100

Querying Data (SELECT)

Querying data from a table in a database is a read-only operation, i.e., the data cannot be changed. The SQL command for a database query is SELECT. You can use SELECT to display the contents of one or more tables.

General Structure of SELECT

A SELECT command consists of the partially optional elements shown in Table 13. The example shown in the picture contains a simple SQL command (or SQL statement) to query all countries and their capitals from the Country example database.

Result (Excerpt)
BoliviaLa Paz

A valid SELECT statement to query data from a table consists of at least the elements SELECT and FROM. All other elements are optional. The end of a SELECT statement is marked by a semicolon (;). The result of SELECT is a virtual table that contains all of the records in the found set. Which columns are contained in the result table is defined in the SELECT statement. For example, you can use the result table for nested SELECT statements such as a database table, but it is not stored in the database.

Table 13: Elements of a SELECT Command
Elements of SELECTDescription
SELECT list of columnsNames of the columns to be output; with *, all columns of the requested table are output. The order of the columns corresponds to the order of the columns in the requested table.
FROM tableName of the table in which the search is performed; later, queries are also made to several tables. (There are also queries which do not operate on tables. In this case this part is not needed.)
optional:WHERE selection conditionConditions that must be fulfilled for a data record to be taken; restricts the result set.
optional:GROUP BY list of columnsSummarizes elements of the result into groups for which specific restrictions or calculations can be made.
optional, if used together with GROUP BY:HAVING conditionConditions that must be fulfilled by elements of a group.
optional:ORDER BY list of columnsCriterion by which the result of this query is sorted. In case of more than one column, the columns are separated by comma.

The statement SELECT expects a list of columns to be displayed in the result. If you want to output all columns, use the character *. The following statement outputs the entire contents of the Country table:

SELECT * FROM Country;
Result (Excerpt)
AustraliaAUSCanberraAustraliaCapitalTerritory768685018260863Australia/ Oceania100
BoliviaBOLLa PazBolivia10985807165257America100

Elimination of Duplicates in the Result (DISTINCT)

It is often the case that data records occur several times in the result set of a database query. A typical example is the multiple occurrences of places or postal codes in a customer database. If only the number of locations from which the customers originate is relevant and not the number of customers from each location, duplicate occurrences will interfere with distilling this information. Identical data records in the result of an SQL query can be combined with DISTINCT. “Identical” here means that all attributes of a data set are identical. The following example displays all continents represented in the list of countries.


Without DISTINCT the Continent attribute would be output for each data record in the table. With DISTINCT, multiple records containing the same continent are reduced to one data record.

Query Data with Condition (WHERE)

With a simple SELECT statement, the result set basically includes all data records of the requested table. Often, however, the result should be limited to a certain set of values. For example, if only those customers in the customer base who live in the city of Hamburg are of interest, the result of the database query should not contain any customers from Berlin. To restrict the result set, the keyword WHERE must be added to the SELECT statement. The following example queries all countries in the Country table, but restricts the result to countries on the American continent:

SELECT Name FROM Country WHERE Continent=’America’;
United States

As a selection condition of WHERE, the value of the Continent attribute is checked for equality with the character string America. Only records for which the selection condition is fulfilled are included in the result. If a restriction with WHERE is used in the SELECT statement, WHERE always follows the element FROM.

In the above example, a simple comparison for equal value was used as a selection condition. With SQL, however, several conditions can be linked together to create complex queries.

Logical Operators in the Selection Condition

One class of possible operators that can be used to formulate selection conditions are the logical operators NOT, AND, and OR. Without parentheses, these are evaluated in the order specified. Table 14 illustrates the use of logical operators in selection conditions.

Table 14: Examples of Logical Operators
OperatorDescriptionExampleSELECT Name FROM Country WHERE …
NOTNegation of a condition; excludes entries with the valueNOT Continent = ʼAmericaʼ;(Names of all countries not located in America)
ORSelects entries with either valueContinent=ʼAmericaʼ OR Continent=ʼAfricaʼ(Names of all countries located in America or Africa)
ANDSelects only entries with both valuesContinent=ʼEuropeʼ AND Percentage=100(Names of all countries whose base area is 100 percent in Europe)

Logical operators can also be used in combination. However, the correct use of parenthesis must be ensured here. The following examples illustrate this connection:

SELECT Name FROM Country WHERE Continent=’Europe’ AND NOT Percentage=100;

Result: Returns all countries in Europe whose base area is not 100 percent in Europe.

SELECT Name FROM Country WHERE NOT(Continent=’Europe’ AND Percentage=100);

Result: All countries that are not 100% located in Europe.

SELECT Name FROM Country WHERE NOT Continent=’Europe’ AND Percentage=100;

Result: All countries that are not located in Europe but with a base area on only one continent.

Comparison Operators in the Selection Condition

In addition to checking for equality with the operator “=,” other comparison operators can also be used in SQL to formulate selection conditions. Typical examples of such conditions would be a request for all customers whose last order was placed more than three months ago or a request for all customers whose highest invoice amount is greater than 1,000 euros. Similarities can also be found with comparison operators, such as a request for all customers whose surname begins with “S.”

Table 15: Comparison Operators
OperatorDescriptionExampleSELECT Name FROM Country WHERE …
=Checks for equality of valuesContinent=ʼAmericaʼ orPercentage=100
,also: !=Checks for inequalityContinent !=ʼAmericaʼ orPercentage 100
>, < Greater than,Less thanContinent > ʼAmericaʼ or Percentage < 100
>=, <= Greater than or equal to,Less than or equal toContinent <=ʼAmericaʼ or Percentage >= 100
BETWEENRestricts to certain value rangesPopulation BETWEEN 6000000 AND100000000
LIKERestricts to certain patterns of attributes:_ stands for any single character% stands for any sequence of zero or more characters.Code LIKE ʼ_ʼ (single digit value);Code LIKE ʼC__ʼ (Starts with C and consists of 3 characters);Name LIKE ʼ%Aʼ (Value ends with A);Name LIKE ʼG%Yʼ (Value begins with G and ends with Y)
IS NULLNo value stored in databaseCode IS NULL
IS NOT NULLAssurance that a value is stored in the databaseCode IS NOT NULL
INRestricts to values of a given value setContinent IN (ʼAmericaʼ, ʼEuropeʼ)
SELECT * FROM Country WHERE Continent=’Africa’ AND Name LIKE ’M%’;

Result: Output of all countries that are located in Africa and whose name begins with the letter M.

SELECT Name FROM Country WHERE Continent=’Asia’ AND Name BETWEEN ’C’ AND ’I’;

Result: Output of all countries located in Asia whose name has an initial letter between C and I. China is included in the result quantity, but Indonesia and India are not.

Selected Operators for Strings and Dates

The following operators for strings and dates are supported by most DBMSs. In addition, DBMSs usually support a large number of other functions, some of which are very DBMS-specific. Therefore, the developers of SQL queries have to inform themselves about which functions are actually available and how they are implemented based on the documentation of the DBMS used.

Date functions

Date functions are functions that output the current date or time and are used either to filter records or to write date and time information. Table 16 presents CURRENT_DATE and CURRENT_TIMESTAMP as examples of two date functions.

Table 16: Date Functions
OperatorDescriptionExampleSELECT Name FROM Country WHERE …
CURRENT_DATEReturns the current calendar date, often in the form YYYY-MM-DD.(CURRENT_DATE – orderdate) >= 3;Determines all orders placed three or more days ago.
CURRENT_TIMESTAMPReturns a current timestamp, often in the form YYYY-MM-DD HH:MM:SS.SELECT CURRENT_TIMESTAMP;Determines and outputs time and date.

In addition, relational DBMSs usually provide conversion functions for date and time values. The concrete details must be read in the documentation of the DBMS used in each case.

String functions

SQL provides a number of functions for editing character strings. Table 17 shows examples of functions that link character strings.

Table 17: String Functions
CONCAT(arg1, … argN)Combines the values passed as parameters to a character string.SELECT CONCAT(Name,’ ’, Code) FROM Country;
CONCAT_WS(separator, arg1,…,argN)WS means “with separator.”Combines the values passed as parameters to a character string; always inserts the characters passed as the first parameter as a separator between two values.SELECT CONCAT_WS(’ ’, Name, Code, Area, ’;’) FROM Country;

A concatenation of character strings is used, for example, to generate output texts (such as addresses) or to export data in structured text (such as XML) for technical interfaces.

Back to blog

Wordpress Developer Loader, Web Developer Loader , Front End Developer Loader Jack is thinking