Contents
SELECT All Rows and All Columns
DESCRIBE the Structure of a Table
SELECT Only COLUMNS of Your Choice
Arithmetic Operators in SELECT Statement
NULL Values in Arithmetic Expressions
Using the CONCATENATION Operator
Displaying a Single QUOTATION Mark
The contents of this work are based on;
This text on SQL is based on the EP-European Parliament as Data Model.
The European Parliament (EP) is the parliamentary institution of the European Union (EU).
The European Parliament is elected by the citizens of the European Union to represent their interests.
Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.
Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the European Union's nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.
Please Note:
The sources for the data, which are all freely obtainable from the European Union website, and from several leaflets of the EU institutions, are listed in the References section.
These sources are used to create the fictitious Business Narrative of the "Data Modelling and Normalization" Chapter.
We called this Business Narrative; "the Story of the European Union" which explains about the EU and EP, for purposes of Data Modeling.
The Story of the European Union is the last Chapter of Kaye is Learning SQL.
This text on SQL is based on the Oracle Database as RDBMS — Relational Database Management System.
Although the SQL Statements used in this text, apply specifically to the Oracle Database implementation of SQL, they are generally compatible with many other RDBMS SQL implementations.
The knowledge in the following sections are based on my personal work experience, as well as on several Oracle Documentation, which are freely downloadable from Oracle websites.
You can find a list of these documents in the References.
The name of the documents may change in time.
You can refer to the Oracle Documentation if you need more detailed information on any of the topics in this text.
Any errors present, are mine.
An SQL SELECT statement retrieves information from the database.
With an SQL statement, you can select columns of your choice from a table.
You can choose all the columns or some of the columns of a table.
This is called the PROJECTION capability of an SQL SELECT statement.
In the example above, only two columns of the table are chosen:
The user will see the data in these two columns only.
This is the PROJECTION capability of an SQL SELECT statement.
PROJECTION takes Vertical Slices from a table.
With an SQL statement you can select rows of your choice from a table.
You can choose all the rows or some of the rows of a table.
This is called the SELECTION capability of an SQL SELECT statement.
In the example above, only some of the rows are chosen.
These are the rows where the STATUS column has the value 'C'.
The user will only see the data in these rows.
This is the SELECTION capability of an SQL SELECT statement.
SELECTION takes Horizontal Slices from a table.
With an SQL statement you can select data that is stored in more than one table in the database by joining these tables.
This is called the JOIN capability of an SQL SELECT statement.
Tables are joined on the Columns with the Same Domain — generally a Foreign Key to Primary Key Relationship.
JOINING the two tables COUNTRIES and MEPS on their Primary Key(COUNTRY_ID) and Foreign Key(COUNTRY_ID) respectively, enabled us to include the "COUNTRY_NAME" column of the COUNTRIES table in the Result Set of the Query, as well as columns from the MEPS table.
This is how the JOIN Operation in an SQL SELECT Statement works.
SELECT * | column_name | expression [ alias ] FROM table_name;
[ alias ] which is in square brackets, is optional.
An alias names a column heading.
The statement below selects all rows and all columns in the table EU_INSTITUTES.
SELECT * FROM EU_INSTITUTES;
The asterisk(*) is used to display all rows and all columns.
*
You can also retrieve the same data by listing all the column names in the table:
Below is the code selecting all the columns from EU_INSTITUTES.
SELECT INSTITUTE_ID,
INSTITUTE_CODE,
INSTITUTE_NAME,
ROLE,
MEMBERS,
TERM_OF_OFFICE,
MEETINGS,
ADDRESS,
TEL,
INTERNET
FROM EU_INSTITUTES;
The structure of a table can be displayed by the DESCRIBE command.
To find out;
Below is an example in SQL Developer, for the table EU_INSTITUTES.
Let's have a look at the details marked with rectangles:
Here is another example of the DESCRIBE command, this time on the column MEMBERS.
We can also use the key combination (Shift + Function + F4) when we are on the table name EU_INSTITUTES, to describe the table.
(Shift + Function + F4)
When we press Shift + Function + F4 keys, a pop-up window appears as seen below.
Note: The keys might differ from one tool to another.
Now that we can find out which columns the table has using the DESCRIBE command, we can choose the columns that we want to SELECT.
Here is an example.
SELECT INSTITUTE_ID, INSTITUTE_NAME, ROLE, INTERNET FROM EU_INSTITUTES;
You can SELECT Columns from a table in any order you want.
You can change the SELECT Statement above as follows, to alter the order of the columns:
SELECT INSTITUTE_NAME,
INTERNET,
ROLE,
INSTITUTE_ID
FROM EU_INSTITUTES;
Definition of "Lexical" in Merriam Webster Online
Relating to words or the vocabulary of a language as distinguished from its grammer and construction.
Definition of "Lexical" in Wiktionary
Concerning the vocabulary, words or morphemes of a language.
Morpheme is the smallest linguistic unit within a word that carry a meaning and cannot be further divided.
For example, the word "unbreakable" is made up of these smallest linguistic units which carry a meaning and cannot be divided further: "un" + "break" + "able".
Here, "un", "break", and "able" are morphemes.
Another example: The word "cats" is formed of the units of "cat"(an animal) + "s"(plural indicator).
Here, "cat" and "s" are the two morphemes of the word "cats".
Some beloved cats are seen below.
... and the word "cat" has only one morpheme.
Below is another beloved cat.
The following Lexical Rules for issuing SQL Statements apply specifically to the Oracle Database implementation of SQL, but are generally acceptable in other SQL implementations.
When you issue a SQL statement, you can include;
Thus, the database evaluates the following SQL statements in the same manner:
Statement 1
select first_name , last_name , salary , round(salary/12,-3) , country_id from meps where pg_id=100 order by mep_id;
Statement 2
select first_name, last_name, salary,
round(salary/12,-3), country_id
from meps
where pg_id=100
order by mep_id;
CASE is INSIGNIFICANT in reserved words, keywords, identifiers and parameters.
Therefore, the database evaluates the following SQL statements in the same manner:
Statement 1
select first_name , last_name , salary , round(salary/12,-3) , country_id from meps where pg_id=100 order by mep_id;
Statement 2
SELECT first_name , last_name , salary , ROUND(salary/12,-3) , country_id FROM meps WHERE pg_id=100 ORDER BY mep_id;
CASE is SIGNIFICANT in text literals and quoted names.
Therefore, the database CANNOT evaluate the following SQL statements in the same manner:
select * from countries where country_name='Luxembourg';
select * from countries where country_name='LUXEMBOURG';
SQL statements are terminated differently in different programming environments.
In SQL*Plus and in SQL Developer, ";", the "semicolon" is used to terminate a statement.
You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values.
The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype.
An Example
select first_name
, last_name
, salary
, round(salary/12)
, country_id
, pg_id
from meps
order by pg_id;
This example uses the "Division" operator to calculate the monthly salary of MEPs: "salary/12".
It then uses the ROUND function to round the result to the nearest whole number.
Example
select first_name , last_name , salary , round(salary/12) , round(1000 + salary/12) , country_id , pg_id from meps order by pg_id;
The result of the SELECT statement is seen below — not all rows are displayed.
Example which uses Parenthesis
select first_name , last_name , salary , round(salary/12) , round((1000 + salary)/12) , country_id , pg_id from meps order by pg_id;
The result of the SELECT statement is seen below — not all rows are displayed.
Zero (0) is a number, and blank space (' ') is a character.
ALL operators (except CONCATENATION) return NULL when given a NULL OPERAND.
Let's take an example we have seen previously.
Example for NULL values
select first_name , last_name , salary , round(salary/12) , round(1000 + salary/12) , country_id , pg_id from meps order by pg_id;
Example
select first_name , last_name , salary , round(salary * 1.2) salary_1 , round(salary + 3000) as salary_2 , country_id , pg_id from meps order by pg_id;
Another Example
select first_name , last_name , salary , round(salary * 1.2) "Salary Trial 1" , round(salary + 3000) "Salary Trial 2" , country_id , pg_id from meps order by pg_id;
select first_name||' '||last_name "MEP Name" , email "e-mail" from meps order by mep_id;
The SQL statement above concatenates the first_name, a space character denoted with ' ', and the last_name columns together to produce one column.
The alias "MEP Name" is used for the resultant column.
To be able to display the single quotation mark itself, you have to type two single quotation marks, as seen in the SQL statement below.
select first_name||' '||last_name||'''s e-mail is: '||email "MEPs and their e-mails" from meps;
'''s e-mail is: '
is a character string and it is also called a literal.
A literal is output once for each row returned from the table — as seen in the output above.
q is the QUOTE OPERATOR which enables us to print Single Quotation Marks freely.
These are the steps to use it:
That's all! 😄
It seems like we have to work hard for freedom 😉
Complete SQL Statement is below:
select first_name||' '||last_name||q'('s e-mail is: )'||email "MEPs and their e-mails"
from meps;
The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned rows, including duplicates of NULL, if they exist.
Specify DISTINCT if you want the database to return ONLY ONE COPY of each set of the duplicate rows selected.
In the examples for DISTINCT, we will use the table MEPS.
The MEPS table holds details of the Members of the European Parliament.
For each Member of the Parliament, it holds details such as first name, last name, e-mail, country, political group, and so on.
This table has 787 Members of the Parliament — as many as the members elected in 2004 EP elections.
Most of these members share a country, and share a political group.
If we want to query the countries of the members of the parliament, we issue the following SQL Statement:
select country_id from meps;
A random selection is seen below.
We can see that some country ids are repeated.
If we want to query the political groups of the members of the parliament, we issue the following SQL Statement:
select pg_id from meps;
A random selection is seen below.
We can see that pg ids are repeated.
But, we do NOT want to see the repeated country ids or the repeated political group ids.
All we want to see, is the set of UNIQUE countries the members of the parliament come from, or the set of UNIQUE political groups they belong to.
This is where DISTINCT comes in.
We specify DISTINCT for the database to return ONLY ONE COPY of a country id, or ONLY ONE COPY of a political group id.
In order to display only distinct or unique values of the country_id column, the DISTINCT keyword is used:
select distinct country_id from meps;
This SELECT statement displays only the distinct COUNTRY_IDs in the MEPS table.
None of the COUNTRY_IDs repeat.
In order to display only distinct or unique values of the pg_id column, the DISTINCT keyword is used:
select distinct pg_id from meps;
This SELECT statement displays only the distinct PG_IDs in the MEPS table.
None of the PG_IDs repeat.
Multiple columns can be specified after the DISTINCT keyword.
select distinct country_id, pg_id from meps
SQL statement above will display distinct political groups of the MEPs within each country.
Only unique combinations of (country_id, pg_id) will be displayed.
What we see below is a random selection from the result set.
Another Example
select distinct pg_id, country_id from meps order by pg_id, country_id
SQL statement above will display distinct countries of the MEPs within each political group.
Only unique combinations of (pg_id, country_id) will be displayed.
What we see below is a random selection from the result set.
WHERE Clause in a SELECT Statement enables us to limit the rows in a query.
For instance, instead of selecting information about all the countries from the COUNTRIES table, we would like to select information only about countries that are Candidates for Accession to the EU.
In our database, these are the countries that have a STATUS of 'C'.
This is when we use a WHERE Clause.
Here is the SELECT Statement which uses the WHERE Clause to limit the rows only to the Candidate Countries:
select *
from countries
where status='C';
Here is the output of the SELECT Statement in SQL Developer.
We can use a WHERE Clause with many Operators and Conditions.
Below, we will see examples on some of these Operators and Conditions.
Here we go.
Retrieve information only about countries that joined the EU on '01-JAN-86'.
select * from countries where join_date='01-JAN-86';
Retrieve information only about the country Luxembourg.
select * from countries where country_name='Luxembourg';
As seen in the last two examples above, dates and character strings are enclosed in single quotation marks ''.
SELECT statement below will not return any rows, because country names are stored in INITCAP format in the database.
INITCAP format is, the Initial is a Capital letter / UPPERCASE and the rest is in LOWERCASE, like Luxembourg.
select * from countries where country_name='LUXEMBOURG';
Retrieve information about those countries which joined EU on or before the date of '01-JAN-86'.
select * from countries where join_date <='01-JAN-86';
GREATER THAN OR EQUAL TO condition is represented by >= .
Retrieve information about countries that joined EU earlier than '01-JAN-86'.
select *
from countries
where join_date < '01-JAN-86';
GREATER THAN condition is represented by > .
Retrieve information about those countries in our database, that are NOT Members of the EU.
Member countries have the value 'M' in their STATUS column.
select *
from countries
where status <> 'M';
NOT EQUAL TO condition can also be represented by != and ^= .
BETWEEN Condition retrieves those rows in a given RANGE, where the rows satisfying the LOWER and UPPER Limit Conditions are INCLUDED in the Result Set.
The LOWER limit must be specified before the UPPER limit, otherwise NO rows will be returned.
Retrieve information about those countries which have a gdp_pps in the range 25000 to 30000.
What is GDP_PPS?
select *
from countries
where gdp_pps between 25000 and 30000;
IN Condition retrieves those rows where the column value is IN the given List.
If the datatype of the values in the list are character strings or dates, then they must be enclosed in Single Quotes ' ' .
Retrieve information about countries that joined the EU either on '25-MAR-57' or on '01-JAN-73'.
select country_id
, country_name
, join_date
, currency_code
, currency_name
from countries
where join_date in ('25-MAR-57','01-JAN-73');
LIKE Condition retrieves those rows where the column values PARTIALLY MATCH the VALUE in the given LIKE condition.
'%' (Percent Sign) or '_' (Underscore), when used with the LIKE Condition, are special pattern-matching characters :
% (percent sign) in LIKE Condition
_ (underscore) in LIKE Condition
ESCAPE Characters in LIKE Condition
Retrieve those countries that have the letter 'K' in their Currency Names.
select country_id , country_name , join_date , currency_code , currency_name from countries where currency_name like '%K%';
Retrieve those countries which joined the EU during the year of 2004.
select * from countries where join_date like '%04';
Retrieve those countries that have the letter 'e' as the second (2.) character in their CAPITAL names.
select country_id , country_name , status , capital from countries where capital like '_e%';
We can include the actual characters '%' or '_' in the LIKE Condition Search Pattern by using the ESCAPE Clause and an escape character.
If the escape character precedes '%' or '_' , then Oracle interprets '%' or '_' literally in the search pattern rather than as a special pattern-matching character.
We would like to find our ROLE CODES which have the character '_' Underscore in them.
We query the database as follows.
select role_code , role_title from roles where role_code like '%_%';
This statement retrieves ALL rows from the ROLES table, even if the ROLE_CODE column do NOT have the character '_' Underscore in it, as seen below.
😦 🙃
This is because;
We now include an ESCAPE Character in our query in the WHERE Clause.
We use the character '?' as our ESCAPE character.
Any character which is not in the search pattern can be used as an ESCAPE Character.
select role_code, role_title from roles where role_code like '%?_%' ESCAPE '?';
ESCAPE Character is the character that comes immediately after the ESCAPE keyword.
In the SQL statement above, the ESCAPE Character is '?'.
We are telling Oracle database to interpret the character which comes after the ESCAPE character literally as it is.
'%?_%' means that the '_' character will now be interpreted as '_', rather than a pattern matching character.
Any character which is NOT in the search pattern can be used as an ESCAPE Character.
Here is the result set of this query:
We have now correctly listed those role codes which have the character underscore _ in them!
The Logical Operators used in the WHERE Clause of a SELECT Statement are:
When a Logical Operator is used in a WHERE Clause, a row is retrieved and displayed ONLY IF THE OVERALL RESULT OF THE CONDITION IS TRUE.
Several conditions can be used in a WHERE Clause by joining them using the Logical Operators AND, OR, NOT.
OR returns TRUE if AT LEAST One Condition is TRUE
We have already seen the Definition of NULL in a previous chapter, related to Arithmetic Expressions.
The definition given below, is the same. However, the Usage will be different.
Zero (0) is a number, and blank space (' ') is a character.
ALL operators (except CONCATENATION) return NULL when given a NULL OPERAND.
To test for nulls, ONLY two Comparison Conditions are available:
If any other condition is used with nulls and the result depends on the value of the null, then the result is UNKNOWN which also evaluates to NULL.
Because NULL represents a LACK OF DATA, because a NULL is UNKNOWN;
A Logical Condition that evaluates to "UNKNOWN/NULL" acts ALMOST like FALSE, but NOT EXACTLY THE SAME:
For example,
NOT FALSE evaluates to TRUE,
but
NOT "UNKNOWN/NULL"
evaluates to "UNKNOWN/NULL" .
Table below shows examples of various evaluations involving NULLS in conditions.
| Conditions | Value of "a" | Value of the Condition |
|---|---|---|
| a IS NULL | 10 | FALSE |
| a IS NOT NULL | 10 | TRUE |
| a IS NULL | NULL | TRUE |
| a IS NOT NULL | NULL | FALSE |
| a = NULL | 10 | UNKNOWN / NULL |
| a != NULL | 10 | UNKNOWN / NULL |
| a = NULL | NULL | UNKNOWN / NULL |
| a != NULL | NULL | UNKNOWN / NULL |
| a = 10 | NULL | UNKNOWN / NULL |
| a != 10 | NULL | UNKNOWN / NULL |
If the conditions evaluating to UNKNOWN / NULL were used in the WHERE clause of a SELECT statement, then NO ROWS would be returned for that query.
The AND Operator returns TRUE ONLY IF BOTH CONDITIONS are TRUE.
Therefore, a ROW is RETURNED IF BOTH CONDITIONS joined by the AND Operator in a WHERE clause are TRUE.
Retrieve those countries whose status is either 'C' or 'M', and whose gdp_pps < 10000.
Both conditions must be satisfied for a row to be returned.
select country_id
, country_name
, status
, gdp
, gdp_pps
from countries
where status in ('C','M')
and gdp_pps < 10000;
| AND | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | FALSE |
| NULL | NULL | FALSE | NULL |
A Row will be Returned when the Result is TRUE.
As we can see in the "AND Truth Table" above, the AND Operator returns TRUE only if BOTH CONDITIONS return TRUE.
A TRUE is hard to find! — when using the AND Operator.
STRONGEST: FALSE
When two conditions are joined with the AND Operator, the STRONGEST condition is the FALSE condition.
A FALSE condition always makes the result FALSE whatever the other condition evaluates to, whether NULL or FALSE or TRUE.
SECOND: NULL
When two conditions are joined with the AND Operator, the Second Strongest condition is NULL.
If a NULL condition is joined with a TRUE condition, the result is always NULL.
If NULL is joined with NULL the result is again NULL.
If NULL is joined with FALSE, the result is FALSE as stated above.
WEAKEST: TRUE
The TRUE condition is the WEAKEST when AND is used to join two conditions.
TRUE only makes the result TRUE if the other condition is also TRUE.
The OR Operator returns TRUE ONLY IF ONE OF THE CONDITIONS is TRUE.
Therefore, a ROW is RETURNED IF ONE OF THE CONDITIONS joined by the OR Operator in a WHERE clause is TRUE.
Retrieve those countries whose status is either 'C' or 'M', or whose gdp_pps < 10000.
It is sufficient for one of the conditions to be satisfied for a row to be returned.
select country_id
, country_name
, status
, gdp
, gdp_pps
from countries
where status in ('C','M')
or gdp_pps < 10000;
| OR | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | NULL |
| NULL | TRUE | NULL | NULL |
As we can see in the "OR Truth Table" above, the OR Operator returns TRUE EVEN IF ONE OF THE CONDITIONS is TRUE.
These are the cases when a ROW IS RETURNED from a Query.
STRONGEST: TRUE
When two conditions are joined with the OR Operator, the STRONGEST condition is the TRUE condition.
A TRUE condition always makes the result TRUE whatever the other condition evaluates to, whether NULL or FALSE or TRUE.
SECOND: NULL
When two conditions are joined with the OR Operator, the Second Strongest condition is NULL.
If a NULL condition is joined with a FALSE condition, the result is always NULL.
If NULL is joined with NULL the result is again NULL.
If NULL is joined with TRUE, the result is TRUE as stated above.
WEAKEST: FALSE
The FALSE condition is the WEAKEST when OR is used to join two conditions.
FALSE only makes the result FALSE if the other condition is also FALSE.
The NOT Operator returns TRUE, ONLY IF the Condition following NOT is FALSE.
This is when a ROW IS RETURNED from a Query.
| TRUE | FALSE | NULL | |
|---|---|---|---|
| NOT | FALSE | TRUE | NULL |
NOT TRUE becomes FALSE.
NOT FALSE becomes TRUE.
NOT NULL becomes NULL.
NOT FALSE which becomes TRUE, returns a row from the Query.
Example
select country_id
, country_name
, status
, gdp
, gdp_pps
from countries
where status not in ('C','M');
Let's examine the WHERE Clause of the SELECT Statement above:
WHERE STATUS NOT IN ('C' , 'M')
The WHERE Clause above is identical to:
WHERE (STATUS != 'C') AND (STATUS != 'M')
This SELECT Statement retrieves those rows where STATUS IS NOT EQUAL TO 'C' AND STATUS IS NOT EQUAL TO 'M'.
However, it will NOT retrieve those rows whose STATUS is NULL.
WHY?
select country_id , country_name , status , gdp , gdp_pps from countries where gdp_pps not between 10000 and 40000;
select country_id , country_name , status , join_date from countries where join_date not like '%04';
Definitely, NOT an Ordinary Flower!
Operator Priority in order of evaluation is as follows:
Parenthesis () are used to OVERRIDE the Default Priority.
select country_id , country_name , status , gdp_pps from countries where gdp_pps < 10000 or gdp_pps > 30000 and status='M';
WHERE Clause of this SELECT Statement, has 3 Conditions:
The 1st Condition is joined to the 2nd Condition by an "OR Operator".
The 2nd Condition is joined to the 3rd Condition by an "AND Operator".
According to the Operator Priority evaluation above, "AND Operator" is evaluated BEFORE "OR Operator".
Therefore, we now have the following 2 Conditions:
The two conditions above will be joined by the "OR Operator".
If either of the two conditions evaluates to TRUE, a row will be returned from the query.
These two conditions joined by OR reads as the following:
Select the row if, "gdp_pps of the country is greater than 30000 and the status of the country is 'M' ", or "the gdp_pps of the country is less than 10000".
Result set of this query is seen below.
select country_id , country_name , status , gdp_pps from countries where (gdp_pps < 10000 or gdp_pps > 30000) and status='M';
This SELECT Statement is almost the same as in Example 1.
However, in Example 2, the Default Operator Priority is overriden by using paranthesis ().
where (gdp_pps < 10000 or gdp_pps > 30000) and status='M';
The SELECT Statement now reads as:
Select the row if, "the gdp_pps of the country is less than 10000 or greater than 30000", and "the country has a status of 'M' ".
The result set of example 2 can be seen below.
Question
You query the database with this SQL Statement:
SELECT column1 FROM table1 WHERE column1 between 1 AND 250 OR (column1 IN (190,500,600) AND column1 BETWEEN 250 AND 500);
Which value could the statement return?
Answer
Correct answer is 100, the last one.
Please note that the question asks;
Which value could the statement return?
It does NOT ask for the Whole Result Set.
The Whole Result Set is:
(1, 2, 3, ... 249, 250, 500)
The Multiple Choice question wants only one of the integers in this Result Set.
100 is in this Result Set.
Tricky Question 😅 😉
The Order of Rows returned in a query is "undefined".
The SELECT Statement below returns the rows in no particular order.
select country_id , country_name , join_date , capital , currency_code , currency_name from countries where status='M';
The ORDER BY Clause is used to sort the rows.
select country_id , country_name , join_date , capital , currency_code , currency_name from countries where status='M' order by join_date;
Member countries are now sorted in their order of joining the EU.
Rows can be sorted in ASCENDING or DESCENDING order.
Merriam-Webster Dictionary
https://www.merriam-webster.com/dictionary/ascending
Here is the Definition of Ascending in Merriam-Webster Dictionary:
ASCENDING a Mountain — Going UP a Mountain:
Cambridge Dictionary
https://dictionary.cambridge.org/dictionary/english/ascending
Definition of Ascending in Cambridge Dictionary:
increasing in size or value
These Circles are in ASCENDING ORDER of Size — they are INCREASING in Size:
We can say that;
DESCENDING is the OPPOSITE of ASCENDING.
Hence, DESCENDING is;
These beads are in DESCENDING ORDER of Size — they are DECREASING in Size:
Here is a DESCENDING elevator in a skyscraper. It is going DOWN from the 21st Floor.
We can say that;
e.g. order by join_date asc
What about NULL values?
If a column has a NULL value, how will it be ordered with ASC, which is By Default?
NULL values are treated .. as if they are .. INFINITY
e.g. order by join_date desc
Below is the previous query sorted in DESCENDING Order and the result set.
select country_id , country_name , join_date , capital , currency_code , currency_name from countries where status='M' order by join_date desc;
Member Countries are now sorted in DESCENDING Order of joining the EU.
Countries which joined the EU LAST, are now the FIRST in the list.
The Order of the Column in the SELECT list can be used to SORT the rows.
select country_id , country_name , join_date , capital , currency_code , currency_name from countries where status='M' order by 3;
In the SELECT Statement above, the Order of the Column in the SELECT list is used in the ORDER BY Clause.
The 3rd column is the JOIN_DATE, hence the result set is sorted in ascending order of JOIN_DATE.
Although this is a convenient method for SELECT Statements in ad-hoc reports, it is NOT recommended in hard-copy programming.
The Column Alias in the SELECT list can be used to SORT the rows.
select country_id , country_name , gdp , population , round(gdp / population) gdp_per_capita , gdp_pps from countries where status='M' order by gdp_per_capita;
In the SELECT Statement above, the alias gdp_per_capita is given to the arithmetic expression round(gdp / population).
The rows are ordered by the alias gdp_per_capita.
order by gdp_per_capita
Although, the keywords ASC or DESC are not used, Default SORT Order is used.
As we have already seen, the Default SORT Order is ASCENDING.
The SELECT Statement below, SORTS / ORDERS the rows by the STATUS column in ASCENDING Order by Default, and then within each STATUS Set, the rows are SORTED / ORDERED BY in DESCENDING Order of GDP_PPS.
In our database, there are only three values in the STATUS column:
'C','M', and 'O'
'C' for CANDIDATE, 'M' for MEMBER, and 'O' for OTHER.
select country_id , country_name , status , gdp_pps from countries order by status, gdp_pps desc;
As seen in the Result Set below, the rows are in ASCENDING Order of STATUS.
Since this column holds character values, rows are sorted in alphabetical order.
Within each STATUS Set, i.e. within the set of rows, that have the same STATUS Value, the rows are then SORTED in DESCENDING Order of GDP_PPS — HIGHEST VALUES FIRST and LOWEST VALUES LAST.
Since, NULLS come FIRST in a DESCENDING Sort , the countries, 'India', 'China', 'Japan', 'USA', 'Russian Federation', and 'Iceland', that have a NULL value in their GDP_PPS column , are displayed first within the set of countries that have a STATUS value of 'O'.
| ID | COLOR | TYPE | STOCK LEVEL | PRICE |
|---|---|---|---|---|
| 215 | REDS | SHORT SLEEVE | 128 | 400 |
| 140 | WHITES | SLEEVELESS | 256 | |
| 603 | BROWNS | LONG SLEEVE | 318 | 210 |
| 725 | ROSES | LONG SLEEVE | 1200 | 175 |
| 218 | REDS | LONG SLEEVE | 125 | 315 |
| 220 | BLACKS | SHORT SLEEVE | 420 | |
| 126 | WHITES | SHORT SLEEVE | 289 | |
| 751 | ROSES | SLEEVELESS | 200 | 100 |
Evaluate the following SELECT Statement.
SELECT COLOR, TYPE FROM T_SHIRTS ORDER BY PRICE, STOCK_LEVEL;
Which of the statements below are TRUE? (Choose all that apply.)
There are two correct answers.
I am glad you came this far in this text.
Here are some Beautiful Roses for you to enjoy.
