piurizza.com

Introduction

In the first part we took a look at how the examination will be structured and we introduced the first exam topic talking about “Relational Database concepts”.
The second topic is about retrieving data with one of the most popular SQL statements: SELECT.

Retrieving data using the SQL SELECT Statement

The SQL SELECT statement

The Oracle SELECT statement is used to retrieve records from one or more tables in an Oracle database.
The syntax for this statement is the following:

SELECT expressions
FROM tables
[WHERE conditions];

• expressions are the columns or calculations that you wish to retrieve. To select all columns you can use the wildcard *.
• tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
• where is an optional condition that must be satisfied in order to filter the records. All records will be chosen if no conditions are specified.

For example

SELECT first_name, last_name
FROM bloggers
WHERE first_name = 'Pietro';

This query will retrieve the first name and the last name of all bloggers with ‘Pietro’ as the first name.

Column aliases

Oracle aliases can be used to create a temporary name for columns or tables.
Column aliases are used to make column titles easier to understand in the result set.
Table aliases are are used to abbreviate SQL statements for better reading or while executing a self join (For example, listing the same table more than once in the FROM clause).
The syntax to alias a column is:

column_name AS alias_name

While the syntax to alias a table is:

table_name alias_name

• column_name is the original name of the column that you wish to alias;
• table_name is the original name of the table you wish to alias;
• alias_name is the temporary name to assign.

Remember that if the alias_name contains spaces, you must enclose it in double quotes.
When aliasing a column name, it is allowed to use spaces. However, using spaces while aliasing a table name is not normally recommended. Moreover, consider that the alias_name is only valid within the scope of the SQL statement.
For example

SELECT first_name, last_name AS "blogger surname"
FROM bloggers
WHERE first_name = 'Pietro';

This query will retrieve the first name and the last name of all bloggers with ‘Pietro’ as the first name. In this case, the last_name column will be named blogger surname

The concatenation operator

The concatenation operator || in Oracle allows you to concatenate 2 or more strings together returning a string value. string || string2 [ || string_n ]

• string1 is the first string to concatenate;
• string2 is the second string to concatenate;
• string_n (optional) is the nth string to concatenate.

For example

'c' || 'i' || 'a' || 'o'

Returns 'ciao'

Literal character string

In the Oracle perspective, a literal can be considered a constant.
The most important literals are:

• text
• integer
• number
• date/time

Text literals must be surrounded by single quotes (‘).
For example

'Pietro'

Integer literals can be up to 38 digits and can be either positive numbers or negative numbers. Without specifying a sign, the number will be interpreted as a positive one.
For example

-123456

Date and time literals are encapsulated in single quotes and follow the same format as text literals. Text literals are transformed to date or time literals using the to_date() function.
For example

SELECT to_date('2023/01/01', 'yyyy/mm/dd')
FROM dual;

The dual table is a special table provided from Oracle that has one column named DUMMY whose data type is VARCHAR2() and contains one row with a value X. It’s used for selecting data from system functions and calculations when you don’t need any data from the database.

Alternative quote operator

What if we want to use an apostrophe in a literal value? To overcome this problem Oracle introduces an operator known as Alternative Quote Operator (q).
We can use any character such as ‘{‘, ‘[’, ‘<’, ‘(‘, ‘!’ to delimite the literal value. These characters are known as delimiters.
For example

SELECT q'{ Pietro's blog }'
FROM dual;

The DISTINCT Keyword

The DISTINT clause is used with SELECTstatements to remove duplicates from the result set.

SELECT DISTINCT expressions
FROM tables;

In Oracle, the DISTINCT clause doesn’t ignore NULL value. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

Using arithmetic expressions

An arithmetic operator with one or two arguments is used to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype.
Unary arithmetic operators return the same datatype as the numeric datatype of the argument.
For binary arithmetic operators, Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

When + and - denote a positive or negative expression, they are unary operators.

SELECT *
FROM employees
WHERE -salary < 0;

In this case, the query will return the whole row of every employee who has a salary greater than 0.
When they add or subtract, they are binary operators.

SELECT hire_date
FROM employees
WHERE sysdate - hire_date > 365;

This query will retrieve the hire date of those employee hired more than one year.

The multiply * and divide / are binary operators.

UPDATE employees
SET salary = salary * 1.5;

This UPDATE statement will raise the salary of every employee of 50%.

Using NULL values

The NULL value is neither equal to nor not equal to anything. In fact, the result of

NULL = <anything>

is always unknown.
To check if a column is null or not, we can use the IS NULL condition and its reverse IS NOT NULL.

SELECT *
FROM bloggers
WHERE post_number IS NOT NULL;

This query will return the whole row of every blogger who has at least one post written.

Sample questions

Let’s try answering a few questions to see if you master the concepts.

Consider that:

• VARCHAR2 is used to store variable-length character strings;
• DATE is used to store date variables;
• NUMBER is used to store numeric variables.

The full explanation is deferred to the discussion of the following topics.

Examine the description of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

1. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS
2. SELECT member_id AS “MEMBER ID”, due_date AS “DUE DATE”, $2 AS “LATE FEE” FROM BOOKS_TRANSACTIONS
3. SELECT member_id AS “MEMBER ID”, due_date AS “DUE DATE”, ‘$2’ AS “LATE FEE” FROM BOOKS_TRANSACTIONS
4. SELECT member_id ‘MEMBER ID’, due_date ‘DUE DATE’, ‘$2 AS LATE FEE’ FROM BOOKS_TRANSACTIONS;

Which queries execute successfully?

1. SELECT q’! Pietro’s blog !’

   ‘is awesome’ FROM dual;

2. SELECT “{ Pietro’s blog }” || ‘is awesome’ FROM dual;
3. SELECT q’[ Pietro’s blog ]’ || is awesome FROM dual;
4. SELECT q’* Pietro’s blog *’ || ‘’ FROM dual;
5. SELECT p’{ Pietro’s blog }’ || ‘is awesome’ FROM dual;

Conclusion

That concludes the second part of this journey.

We have seen how to retrieve data using the SQL SELECT statement and other useful concepts. Be sure to master these notions, as they will lay the foundation for what we will discuss next.

I’ll see you in the next part!