What is DQL in Oracle? & Is SELECT a DQL Command?

DQL in Oracle

For all relational databases, including Oracle Database, the essential access tool is the Structured Query Language (SQL). SQL statements fall into a number of general categories, including Retrieval (also known as Data Query Language, or DQL), Transaction Control, Data Definition Language (DDL), and Data Manipulation Language (DML).

The SELECT command is the main focus of Data Query Language (DQL), and it is used to retrieve data from databases. It is perhaps the most often used SQL statement. Enabling end users to extract information without assistance for each report is the main goal of DQL and SQL in general.

Prerequisites

Typically, DDL and DML instructions are used to define and populate database objects before executing a query in DQL.

Data Definition Language

2DL statements allow you to create, modify, and drop schema objects. DDL statements, like CREATE TABLE, define or delete database objects. A table’s name, as well as the names and datatypes of its columns, are specified when the CREATE TABLE command is used.

Here’s an example of making a basic table called MY_table:

-- DDL Command to create a table
CREATE TABLE MY_table (
   what VARCHAR2(10),  -- Variable length string, max 10 characters
   who VARCHAR2(10),   -- Variable length string, max 10 characters
   mark CHAR(1)         -- Fixed length string, 1 character
);

Output Example (Table Creation): Upon successful execution of a DDL statement like CREATE TABLE, the database typically confirms the action:

Table created. 

You can then confirm the defined structure using the DESCRIBE command:

SQL> DESC MY_table;
Name                            Null?    Type 
------------------------------- -------- -------------------
WHAT                                     VARCHAR2(10)
WHO                                      VARCHAR2(10)
MARK                                     CHAR(1)

Data Manipulation Language

The data within that object can be accessed and altered using DML commands after the table structure has been established. You can insert a row of data straight into a table using the INSERT command.

If values are given for each column in the proper sequence, you can either mention the target columns explicitly or leave them out.

-- DML Commands to insert rows
INSERT INTO MY_table (what, who, mark) 
VALUES ('Hello', 'world', '!' ); 

INSERT INTO MY_table VALUES ('Bye bye', 'ponies', '?' ); 

INSERT INTO MY_table (what) 
VALUES('Hey');

COMMIT; -- Commit ensures the transaction is permanently recorded.

Output Example (Insertion): For each successful INSERT command executed, the database provides confirmation:

1 row created.

This message would appear three times following the execution of the three insert statements mentioned above.

Data Query Language (DQL) Commands

The SELECT command is the foundation of DQL and is used to get data from the database. The keywords SELECT and FROM must be present in each SQL statement that obtains data.  SELECT, FROM, WHERE, and ORDER BY are the four primary keywords that are typically used while creating queries.

Basic Retrieval

The table (object) holding the data is named by the FROM clause, while the SELECT clause indicates which columns you wish to obtain. Asterisks (*) are used to retrieve all of the table’s columns.

Example 1: Selecting all data from the table

SELECT what, who, mark 
FROM MY_table;

Output: The result returns all rows and the specified columns:

WHAT       WHO        MARK
---------- ---------- ----
Hello      world      !
Bye bye    ponies     ?
Hey                   

Filtering Data ()

The SELECT statement’s optional WHERE clause is used to filter the result set according to conditional criteria. To limit the number of rows that are returned, the criteria employ comparison and logical operators (e.g., =, >, <, IN, BETWEEN).

Example 2: Selecting specific filtered data

SELECT what, who 
FROM MY_table 
WHERE what='Hello';

Output: Only the row satisfying the condition is returned:

WHAT       WHO
---------- ----------
Hello      world

Ordering Results ()

Queries may return records in a random or unpredictable order unless otherwise noted. In order to ensure that the output is organised and facilitates data review and analysis, the optional ORDER BY clause is used to sort the result set according to one or more columns. You can choose between descending order (DESC) and ascending order (ASC), which is usually the default.

Example 3: Sorting filtered data

SELECT what, who 
FROM MY_table 
WHERE who IS NOT NULL 
ORDER BY what DESC;

Output: The two rows where who is not null are retrieved and sorted descending based on the ‘what’ column:

WHAT       WHO
---------- ----------
Hello      world
Bye bye    ponies

Advanced DQL Capabilities

DQL uses complicated query structures, functions, and aggregation to go beyond simple filtering and selection.

Functions

SELECT statements employ functions, which are strong tools for modifying column contents or retrieving data about them.

1. String/Character Functions: These work with textual information. For instance, LENGTH yields a string’s character count, whereas LOWER changes text to lowercase.
2. Numeric Functions: Calculating logarithms and rounding are examples of mathematical operations carried out by numerical functions.
3. Date Functions: These perform operations on date and time information, such as determining the current date or time zone offsets.

Grouping and Aggregation

With the use of functions  like COUNT, SUM, AVG, MIN, and MAX, DQL facilitates data analysis and aggregation.

1. GROUP BY: The GROUP BY clause is necessary when utilising aggregate functions in order to gather rows with similar values and turn them into summary output rows.
2. HAVING: After grouping, the results are filtered by the HAVING clause, which enables criteria to be based on the output of an aggregate function.

Complex Queries

DQL allows complex requests to be constructed:

• Joins: Join procedures allow queries to merge data from several tables, such as combining the DEPARTMENTS and EMPLOYEES tables.
• Subqueries: Subqueries are nested SELECT statements that are utilised inside other SQL statements. A set of rows to be evaluated or values for a WHERE clause can be produced by subqueries.

The SELECT statement and its auxiliary clauses (FROM, WHERE, ORDER BY, etc.) serve as the fundamental building blocks of DQL, enabling users to effectively obtain, filter, and organise data that has been pulled from relational databases.