11SELECT-1

SELECT statement (1)

Outline

Functionality and syntax
Projection queries
Queries with row functions
Queries with group functions
Special queries

SELECT statement, can be used to retrieve data from a relational database.

The outcomes of SELECT statement can be considered as a transient relational table. When SELECT statement is processed online, through either command line or graphical user interface, then such transient relational table with the outcomes of SELECT statement is displayed to a user and later on it is deleted.

When SELECT statement is processed within a database application written in a host programming language like Java or Python, then such transient relational table is stored by a database systems for a period of time when it is processed by the application. When a database application is completed, a transient relational table is deleted.

CREATE TABLE statement, can be used to save the results of SELECT statement, in a persistent relational table.

Application of data definition and data manipulation statements with the SELECT statement will be discussed later in the subject.

Functionality and syntax

Functionality
- SELECT statement retrieves data from a relational database
- The results of SELECT statement can be considered as a transient relational table
- The results of SELECT statement can be saved as a persistent relational table

A query is a formula expressed in a formal or natural language, that determines information to be retrieved from a database, without providing instructions on how to do it.

A simple group of queries are so called: selection queries. Selection queries retrieve the complete rows from a single relational table. An implementation of a selection query in SQL is SELECT statement, that consists of: SELECT, FROM and WHERE clauses.

SELECT clause, has a star as its only argument. A star means: "provide a complete row retrieved from a relational table".

FROM clause, has a single name of a relational table, that is accessed when searching for the required information.

WHERE clause, determines a condition that must be satisfied by the rows retrieved from a relational table. A condition is a Boolean expression of the propositional logic. Such expression consist of terms, Boolean operators: and, or, not, left and right parenthesis. Evaluation of a condition in the WHERE clause, also called: WHERE condition, is performed for every row in a relational table listed in: FROM clause. If evaluation of the WHERE condition, for a row returns true, then the row is included into the results from processing of SELECT statement. Otherwise, the row is skipped.

A simple computational model for SELECT statement, iterates over the rows in a relational table and evaluates each row against WHERE condition. SELECT clause, determines which columns should be included into the results from the query processing.

In the example given on the present slide, evaluation of the first and the third row returns: true, and only the first and the third rows are inserted into the result.It is always convenient, to imagine the results as a transient relational table.

Functionality and syntax

Selection queries select complete rows from a relational table

SELECTION

Another important category of queries includes projection queries.

Projection queries retrieve the selected and complete columns from a relational table. An implementation of projection query in SQL is the SELECT statement that consists only of: SELECT and FROM clauses.

SELECT clause, contains a list of names of columns separated with commas.

FROM clause, determines a relational table processed by the SELECT statement.

In a simple computational model, projection query iterates over the rows in a relational table and selects the values located in the columns included in SELECT list.

In the example given on the present slide, column 2 and column n are included into the results. Like before, it is convenient to imagine the results as a transient relational table, that consists of two columns selected from a relational table.

Functionality and syntax

Projection queries select complete columns from a relational table

PROJECTION

In this presentation, we use the same sample database, that contains information about the departments and about the courses offered by the departments.

Information about the departments is kept in a relational table: DEPARTMENT.

The relational table: DEPARTMENT, consists of six columns: department name (name), department code (code), total staff number, chairperson (chair ), and budget.

A column: name, is a primary key and the values in a column: name, are the variable size strings, no longer than 50 characters, A column: name, is a primary key and because of that all values in the column are mandatory (NOT NULL).

A column: code, is a candidate key and all it’s values are fixed size strings 5 characters long. If the codes are shorter than 5 characters, then such values are padded with a sequence of blanks, up to 5 characters long. The values in a column code are mandatory (NOT NULL).

The values in a column: total staff, are integer values, in a range from one to fifty. The values in a column: total staff, are mandatory (NOT NULL). Total staff number per department cannot be greater than 50.

A column: chair, contains the variable size strings, no more than 50 characters long, and all its values are optional (NULL)

A column: budget, contains the real numbers, no more than 9 digits long, with at most one position after a decimal dot. The values in a column budget are mandatory (NOT NULL).

A relational table COURSE contains the descriptions of course.

A description of course consists of a course number, title, credits and name of department that offers a course.

A column course number is a unique identifier of each course. A value of course number is a fixed length string of characters, 7 characters long.

A column title contains titles of the courses. A title is a variable length string of characters, maximum 200 characters long.

A column credits determine the credits granted fro passing a course.The granted credits can be equal to either 6 or 12.

A column offered_by contains the names of departments that offer courses.

A column offered_by is a foreign key referencing the names of departments in. column name in a table DEPARTMENT.

Functionality and syntax

Sample database

CREATE TABLE DEPARTMENT(
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)           NULL, /* In this presentation */
  CONSTRAINT dept_pkey PRIMARY KEY(name),        /*  budget can be NULL  */
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

 CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

In the present slide we look at the various syntactical constructions of the SELECT statement

A query: "find a name and code of a department that has more than 30 staff members", can be expressed as a combination of selection and projection queries.

To implement such query as SELECT statement, we have to first identify the relational tables that contain information related to the search conditions, and information to be retrieved from a database. In the present case, a search condition is imposed on the values of an attribute: total staff number in a relational table: DEPARTMENT.Data we would like to retrieve are located in the columns: name and code, in a relational table: DEPARTMENT. It means that a relational table: DEPARTMENT is the only relational table that must be processed by SELECT statement. Then, FROM clause, includes only a name of a relational table: DEPARTMENT.

A WHERE clause, contains a condition: "total staff number greater than 30", that is used to filter out the rows from a relational table: DEPARTMENT.

A SELECT clause, contains a list the names of columns from where the output values are retrieved.

A computational model of the SELECT statement is the following. Pick the first row from a relational table: DEPARTMENT. Next, evaluate a condition in a WHERE clause, against the row. If the result of the evaluation is false, then skip the row, otherwise extract from the row the values located in the columns: name and code and append both values to the final result. Next, pick the next row from a relational table: DEPARTMENT and repeat the evaluation of WHERE condition. And so on, until the last row is processed.

It may happen that information needed by WHERE condition, is located in a different relational table from information we would like to get from a database. Such case happens in a query: find a code of a department and a number of course offered by the department and such that the course provides 6 credit points.

A condition: "credits greater than 6" applies to a column: credits, in a relational table: COURSEWe would like to retrieve the values in the following columns: code, in a relational table DEPARTMENT, cnum and title in a relational table COURSE. Hence, the SELECT statement, must operate on more than one relational table. Then, we can either join the relational tables as it is presented in the second example, or create a nested query like in the last example, in the present slide. Both join queries and nested queries will be thoroughly discussed in the future.

Functionality and syntax

Examples

Find a name and code of a department that has more than 30 staff members

SELECT code, name
FROM DEPARTMENT
WHERE total_staff_number > 30;

Find a code of a department and course number such that a course is offered by a department and a course provides 6 credit points

SELECT DEPARTMENT.code, COURSE.cnum, title
FROM DEPARTMENT JOIN COURSE
                ON DEPARTMENT.name = COURSE.offered_by 
WHERE credits = 6;

Find all information about courses offered by a department whose chair is James Bond

SELECT *
FROM COURSE
WHERE offered_by IN ( SELECT name 
                      FROM DEPARTMENT
                      WHERE chair = 'James Bond' );

In the first example, we create an inline view to implement a query: "find all information about the courses offered by a department whose chair is James Bond". Inline view is a SELECT statement, enclosed within left and right parenthesis, followed by a name of inline view and included in FROM clause, as a temporary relational table. Such temporary relational table, is deleted at the end of processing of SELECT statement. A concept of inline view is based on our earlier assumption, that any result from processing of SELECT statement can be considered as a relational table.

Processing of SELECT statement, with an inline view starts from processing of the inline view. The results of processing are saved in a temporary table. Then, a name of such temporary table replaces a specification of inline view in SELECT statement. Finally, SELECT statement, is processed in the usual way.

A concept of inline view, allows for application of the "divide and conquer" principle to implementation of complex queries. A specification of a complex query is divided in to the specifications of simpler queries and each step is implemented as a separate inline view. Then, all inline views are assembled into the final SELECT statement. In order to avoid a very large SELECT statement, after inline views are assembled into the final statement, it is possible to append inline views to WITH clause, preceding the final SELECT statement.

In the second example, a query the same as in the first example, is implemented as SELECT statement with WITH clause. WITH clause, contains a list of inline views that must be created before processing of the final SELECT. A WITH clause, is also known as common table expression (CTE) and application of the WITH clause, as subquery factoring.

Both inline views and WITH clause will be thoroughly discussed in the future.

Functionality and syntax

Examples

Find all information about courses offered by a department whose chair is James Bond

SELECT COURSE.*
FROM COURSE JOIN ( SELECT name 
                   FROM DEPARTMENT
                   WHERE chair = 'James Bond' ) JB
	    ON COURSE.offered_by = JB.name;

Find all information about courses offered by a department whose chair is James Bond

WITH JAMES AS
	  ( SELECT name 
            FROM DEPARTMENT
            WHERE chair = 'James Bond' ),
     JAMESCOURSE AS
          ( SELECT *
            FROM COURSE JOIN JAMES
            ON COURSE.offered_by = JAMES.name )
SELECT JAMESCOURSE.* FROM JAMESCOURSE;

Functionality and syntax

Keywords

SELECT code, name
FROM DEPARTMENT
WHERE total_staff_number > 30;

SELECT code, cnum, title
FROM DEPARTMENT, COURSE
WHERE name = offered_by AND credits = 6;

SELECT *
FROM COURSE
WHERE offered_by IN ( SELECT name
                      FROM DEPARTMENT
                      WHERE chair = 'James Bond' );

SELECT COURSE.*
FROM COURSE JOIN ( SELECT name 
                   FROM DEPARTMENT
                   WHERE chair = 'James Bond' ) JB
	    ON COURSE.offered_by = JB.name;

A list of names following SELECT keyword, in SELECT clause, consists of column names. In a special case, when all columns from the results of selection must be returned, a list of columns can be replaced with a star.

If FROM clause, includes more than one name of relational table, then the columns listed in SELECT clause, must belong to only one relational table. If two relational tables have the columns with the same names and both columns are included in the results, then each column must be prefixed with the name of the relational table it comes from, followed by a dot. Please see the second SELECT statement in the present slide.

If more than one relational table is used in the SELECT statement, and all columns from only one of the tables must be included into the results, then a name of relational table is followed by a dot and a star.

Functionality and syntax

Selected columns

SELECT code, name
FROM DEPARTMENT
WHERE total_staff_number > 30;

SELECT code, cnum, title
FROM DEPARTMENT, COURSE
WHERE name = offered_by AND credits = 6;

SELECT *
FROM COURSE
WHERE offered_by IN ( SELECT name
                      FROM DEPARTMENT
                      WHERE chair = 'James Bond' );

SELECT COURSE.*
FROM COURSE JOIN ( SELECT name 
                   FROM DEPARTMENT
                   WHERE chair = 'James Bond' ) JB
	    ON COURSE.offered_by = JB.name;

The names of relational tables used by SELECT statement are listed in FROM clause. It is possible to list the names of several relational tables separated with a comma. It is one of the ways how, a join of several relational tables, can be expressed.

In such a case, a join condition must be present in WHERE clause. If join condition is absent from WHERE clause, then a Cartesian Product of the relational tables listed in FROM clause, is processed.

Another way to join the relational tables uses a join expression in FROM clause.

A join expression, is a pair, that consists of the names of relational tables or inline views (SELECT statements enclosed within left and right round brackets) separated with a name of operation: JOIN.

An inline view can be followed by a name of the view. For example, see the last SELECT statement in the slide where a relational table: COURSE, is joined with the results of processing of inline view: JB. JOIN expression is followed by the ON clause, that provides a join condition.

The syntax of more complex SELECT statements, that join three or more relational tables will be explained in the future.

Functionality and syntax

Relational tables used

SELECT code, name
FROM DEPARTMENT
WHERE total_staff_number > 30;

SELECT code, cnum, title
FROM DEPARTMENT, COURSE
WHERE name = offered_by AND credits = 6;

SELECT *
FROM COURSE
WHERE offered_by IN ( SELECT name
                      FROM DEPARTMENT
                      WHERE chair = 'James Bond' );

SELECT COURSE.*
FROM COURSE JOIN ( SELECT name 
                   FROM DEPARTMENT
                   WHERE chair = 'James Bond' ) JB
	    ON COURSE.offered_by = JB.name;

A condition following WHERE keyword, is a Boolean expression created from the elementary terms like: "total staff number greater than 30", Boolean operators: AND, OR, NOT, left and right round parenthesis. An elementary term is a comparison of a column with a constant, or with another column or with the results from processing of the SELECT statement.

The first SELECT statement, uses an elementary term: "total staff number greater than 30" to find all rows from a relational table: DEPARTMENT, that have a value in a column: total staff number, greater than 30. The first term: "name equals to offered by", is a join condition used to join the relational tables: DEPARTMENT and COURSE. The second elementary term in the same SELECT statement, finds all rows in a relational table: COURSE, that have the values in a column: credits, equal to 6.

A Boolean operator: AND, is used to connect the elementary terms in the second SELECT statement, into a conjunction. An elementary term in the third SELECT statement, compares a value in a column: offered by, with a set of values returned from the processing of a nested SELECT statement, (inline view).

An elementary term: "COURSE dot offered by equal to JB dot name", is used to express a join condition of a join operation on a relational table: COURSE, and on an inline view: JB.

Functionality and syntax

Conditions

SELECT code, name
FROM DEPARTMENT
WHERE total_staff_number > 30;

SELECT code, cnum, title
FROM DEPARTMENT, COURSE
WHERE name = offered_by AND credits = 6;

SELECT *
FROM COURSE
WHERE offered_by IN ( SELECT name
                      FROM DEPARTMENT
                      WHERE chair = 'James Bond' );

SELECT COURSE.*
FROM COURSE JOIN ( SELECT name 
                   FROM DEPARTMENT
                   WHERE chair = 'James Bond' ) JB
	    ON COURSE.offered_by = JB.name;

Functionality and syntax

Subqueries

SELECT *
FROM COURSE
WHERE offered_by IN ( SELECT name
                      FROM DEPARTMENT
                      WHERE chair = 'James Bond' );

SELECT COURSE.*
FROM COURSE JOIN   ( SELECT name 
                     FROM DEPARTMENT
                     WHERE chair = 'James Bond' ) JB
	    ON COURSE.offered_by = JB.name;

The WITH clause, allows for the decomposition of a complex query into a sequence of inline views.

The earlier created inline views, can be referenced by the new inline views and by the final SELECT statement.

An example in the present slide starts from the creation of an inline view: JAMES, that contains the names of departments chaired by James Bond.

Next, an inline view: JAMES, is joined with a relational table: COURSE, to create a new inline view: JAMESCOURSE.

Finally, and inline view: JAMESCOURSE, is used in SELECT statement to find the complete information about the courses offered by a department chaired by James Bond.

Decomposition of the complex SELECT statements, into the inline views, will be thoroughly discussed in one of the future presentations.

Functionality and syntax

WITH clause

WITH JAMES AS
	  ( SELECT name 
            FROM DEPARTMENT
            WHERE chair = 'James Bond' ),
     JAMESCOURSE AS
          ( SELECT *
            FROM COURSE JOIN JAMES
            ON COURSE.offered_by = JAMES.name )
SELECT JAMESCOURSE.*
FROM JAMESCOURSE;

SELECT statement (1)

A projection query selects the complete columns from a relational table.

SELECT statements, implemented as projection queries do not use WHERE clause with a filtering condition. All rows are retrieved from a relational table, and only the values of the selected columns are included into the results from processing of the SELECT statement.

For example, a query like: "find full information about all departments", can be expressed as a SELECT statement, with only SELECT and FROM clauses. SELECT clause, lists all columns in a relational table: DEPARTMENT.

When all columns from a relational table are selected, then it is possible to use a star as a shortcut denoting: "all columns", as in the second SELECT statement, in the slide. In order to pick from a relational table only few columns, the names of such columns must be listed in a SELECT clause, like it is demonstrated by the third SELECT statement in the present slide.

It is possible to pick only a single column from a relational table, see implementation of a query, that finds only the titles of all courses. If two courses have the same title, then such title is listed twice. It is the major departure from a definition of a relational table where it is described as a set of values. Formally, a set of values must have no repetitions.

All commercial relational database systems, do not automatically remove the repetitions of values, that maybe obtained from the processing of projection queries. It is explained in the next slide, how to remove such repetitions of values.

Projection queries

Projection queries select the entire columns from a relational table and do not have WHERE clause

Find full information about all departments

SELECT code, name, total_staff_number, chair, budget
FROM DEPARTMENT;

SELECT *
FROM DEPARTMENT;

Find the names and chairpersons of all departments
Find the titles of all courses

A keyword: DISTINCT, in SELECT clause, can be used to eliminate the duplicates from the results of a query. For example, consider a query: "find the credit points of all courses".

However, it may happen, that two or more courses have the same credits. Then, a relational database system returns the same value of credits two or more times. To eliminate the duplicates, we append a keyword DISTINCT, to a keyword SELECT. Then, if processing of SELECT statement returns more than two or more identical rows, then the duplicates are automatically removed from the results. Please see the bottom two SELECT statements, in the present slide.

Note, that elimination of the duplicates significantly delays processing of the SELECT statement, if the results from processing are large.

Also note, that elimination of duplicates through the side effects of GROUP BY clause or through the side effects of set algebra queries is not recommended, because if its negative impact on performance.

Projection queries with duplicates/ no duplicates

A keyword DISTINCT can be used to eliminated duplicates from the results of a query
Find the credit points of all courses
Find the distinct credit points of all courses
Find the distinct total number of staff members in each department

SELECT statement (1)

Outline

Functionality and syntax
Projection queries
Queries with row functions
Queries with group functions
Special queries

A row function: is a function, that takes zero or more values, as the arguments and it returns only one value. A row function is called "row" because it operates only on a single row taken from a relational table. When invoked in SELECT statement, a row function is computed one time for each row selected from a relational table.

Assume, that we would like to retrieve all names of departments and list such names in an uppercase format. A row function: UPPER, is used in the first SELECT statement, in the slide, to convert each value in a column: name, in a relational table: DEPARTMENT into an uppercase format. Note, that if a relational table consist of five rows and all values in a column: name, are not null, then function: UPPER, will be invoked 5 times.

In the second example, we use a row function: substr (it means: substring), to extract the first three characters from a course code, and later on we concatenate it with a dash and a value taken from a column: title. These operations are repeated for each row in a relational table: COURSE.

An interesting question is, what happens when a row function is applied to the values in a given column and some rows do not have a value in the column. Then, interpretation of a lack of value, or an empty argument of a function depends on a function. This is why, it is strongly recommended to use a default value when an argument of a function is NULL.

For example, in the last SELECT statement in the present slide, a row function: IF NULL, is used to provide a default value of zero, when a row does not have a value in a column: budget.

If a row has a value in a column: budget, then such value is returned by a function IF NULL.

Queries with row functions

A row function is called and it is processed one time for each row selected from a relational table
List the names of departments in uppercase format
Find the first three characters from all course codes and full titles of all courses
Display the name of departments and budgets increased by 10%

SELECT statement (1)

Outline

Functionality and syntax
Projection queries
Queries with row functions
Queries with group functions
Special queries

A group function, is a function, that processes a group of rows and it returns only one value. A group function, is called "group" because, it operates on a group of rows taken from a relational table. In contrast: a row function, operates on a single row and it is applied to each row in a relational table. A relational table can be divided into the groups of rows, and, then a group function is applied to each group of rows in the table.

Assume, that we would like to find the total number of courses, or, in the other words, we would like to count the total number of rows in a relational table: COURSE. To count the total number of rows in a group of rows we use a group function: COUNT. A star is used as an argument of count, because we count the complete rows. In the present case entire relational table: COURSE, is considered as one single group.

In the future, we explain how to use GROUP BY clause, to divide a relational table into many disjoint groups of rows. If we would like to count the total number of values in a single column, then a name of such column must be used as an argument of COUNT function.

It is important to remember, that the repetitions of the values in a column are not removed, and identical values are counted as the separate values. If we would like to count the total number of distinct values, then a keyword DISTINCT, must be used before a name of column.

To count the total numbers of pairs, triples, etc, in a relational table, the pairs, triples, etc, must be concatenated with a row function: CONCAT.

A group function: SUM, can be used to compute summation of the values in a given column, in a given group of rows.

For example, we would like to find the total staff number in all departments. In the other words, we would like to compute the summation of all values in a column: total staff number, in a relational table: DEPARTMENT. To implement such query, we use a group function: SUM, with a name of a column: total staff number, as an argument, for example, see the second SELECT statement in the present slide.

If a column does not have a value, it means that NULL, denoting an empty slot is present in a relational table, then to correctly compute an average of all budgets we apply a row function IF NULL, to a column budget before application of a group function AVG, (it means average). If there is no value in a column budget, then IF NULL function, returns zero. Then zero is used for the computation of an average. In the last example, MAX function, is used to find the largest value in a column total staff number.

Group functions are almost always used to aggregate the values in the columns, and, because of that, such functions are commonly called: aggregation functions.

Queries with group functions

A group function is called and it is processed one time for a group of rows
Find the total number of courses
Find the total number of all staff members in all departments

Find an average budget per each department

SELECT AVG(IFNULL(budget, 0))
FROM DEPARTMENT;

Find the total number of staff members in the largest department

SELECT statement (1)

Outline

Functionality and syntax
Projection queries
Queries with row functions
Queries with group functions
Special queries

Assume, that we would like to use the SELECT statement, to compute a value of a given formula. For example, we would like to multiply a period of 30 hours by a financial reward of 90.3 dollars per hour. Any expression, that returns a single value can be used in the SELECT clause. Then, what name of a relational table should be used in the FROM clause?

In this case, we can use a dummy relational table called: DUAL, (hmmm, I really do not know why it is called: DUAL). A relational table: DUAL, consists of one column and one row. A relational table must have at least one column and in this case it has only one row because we would like to display the result of computation only one time.

The first SELECT statement in the slide is processed in the following way. The system accesses the first row in a relational table: DUAL, and processes an expression included in the SELECT clause. The multiplication of 30 by 90.3 is computed and the result is displayed. There is no more rows in a relational table: DUAL, and because of that, there is no need to process the next row and to compute the expression again. For example, if we assume that a relational table: DUAL, contains 2 rows, then multiplication is processed twice and two identical results are displayed.

In the remaining two SELECT statements, we apply a date arithmetic. Accordingly to the rules of date arithmetic, today's date plus 1, means tomorrow and today's date minus one, means yesterday. SYSDATE function returns today's date. The second SELECT statement, adds an interval of 1 day to the present date, to get tomorrow's date. The third SELECT statement adds an interval of 2 months to the present date, to get a date in two months time in the future.

Special queries

SQL as a calculator
Compute 30 hours * $90.30 per hour
SQL as a diary

What date is tomorrow ?

SELECT DATE_ADD(SYSDATE(), INTERVAL 1 DAY)
FROM DUAL;

Add 2 months to a current date

SELECT DATE_ADD(SYSDATE(), INTERVAL 2 MONTH )
FROM DUAL;

In this slide, we again apply a date arithmetic, to find how many days have passed since the first of January 2001. A row function: DATE DIF, subtracts from the today's date obtained from the SYSDATE function, a date: the first of January 2001. The results are measured in days.

In the second SELECT statement, we concatenate a string: "I am ", and a name of the present user connected to a database server. A function: CURRENT USER, returns a name of a user who processed the function.

The third SELECT statement, is the famous Hello world program, implemented in SQL.

Finally, the last SELECT statement shows how to implement string processing with the string functions: SUBSTR, INSTR and LENGTH. SUBSTR function operates on a string and it extracts a substring that starts from a given location in the string and has a given length.

In our example, SELECT statement extracts from a string: Hello world, a substring that starts from the first occurrence of a character e.

Special queries

How many days have passed since 1 January 2001 ?

SELECT DATEDIFF(SYSDATE(),'2001-01-01')
FROM DUAL;

SQL as word processor

Who am I ?

SELECT CONCAT('I am ', CURRENT_USER())
FROM DUAL;

Hello world !

Substring of 'Hello world' that starts from the first 'e'


SELECT SUBSTR('Hello world', INSTR('Hello world','e'), LENGTH('Hello world')) 
FROM DUAL;

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapter 5 Introduction to Structured Query Language, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapters 6.3.1 - 6.3.4 Data Manipulation, Pearson Education Ltd, 2015
D. Darmawikarta, SQL for MySQL A Beginner’s Tutorial, Chapters 2 - 5, Brainy Software Inc. First Edition: June 2014
How to ... ? Cookbook, How to implement queries in SQL, Recipe 5.1 How to implement SELECT statements with simple Boolean expressions ?