Hello, SQL!

SQL, or Structured Query Language, is the industry standard language for handling relational database management systems (RDBMS), such as Oracle, MySQL, or MS SQL Server. It is specifically designed for tasks such as defining relational data models, managing database objects, and performing CRUD operations (Create, Read, Update, Delete) on data tables. SQL itself is not the database, but rather the means to interact with database system.

SQL commands can be used on a stand-alone basis or they can be invoked from within other applications written in other languages. Many popular programming languages, such as Java, Python, and others, can issue SQL statements to relational databases. These languages embed SQL statements to transfer data captured in execution and retrieve query results from databases.

SQL's syntax is clear and English-like, which makes it accessible to a wide range of users, including database administrators, data analysts, application developers, and even non-programmers with their specific needs. Furthermore, SQL excels in data retrieval and manipulation. It classifies data in a table into specific sets and performing operations on these sets as a whole^[1]. This property makes SQL well-suited for its intended purposes.

Types of SQL Statements

SQL commands can be broadly categorized into Data Definition Language (DDL), Data Manipulation Language (DML), and Transaction Control Language (TCL). DDL consists of those SQL statements that are used to build database objects. DML refers to the statements used to retrieve and manipulate data records stored in a table. TCLs are special commands used to manage transactions, which involve data manipulation processes within a database.

DDL Statements^[2]:

• CREATE: Creates a user, table, view, index, synonym or other objects in the database.
• ALTER: Used to modify the structure, name, or other attributes of an existing database object, with exceptions for SESSION and SYSTEM. ALTER SESSION changes settings for the current session, lasting only until the connection ends. ALTER SYSTEM modifies system-wide parameters, effective until the database restarts. Technically, neither ALTER SESSION nor ALTER SYSTEM is a DDL statement; they fall under a different category.
• DROP: Removes a database object from the database that has already been created with the CREATE statement.
• RENAME: Changes the name of an existing database object.
• TRUNCATE: Removes all records from an existing table. 
• GRANT: Provides privileges to user objects and enables them to perform specific tasks in the database.
• REVOKE: Removes privileges that have been issued with the GRANT statement.
• FLASHBACK: Restores an earlier version of a table or database.
• PURGE: Irrevocably removes database objects from the recycle bin.
• COMMENT: Add comments to the data dictionary for an existing database object.

DML Statements:

• SELECT: Retrieves data from a table or view
• INSERT: Adds data records to a database table, either directly or through a view.
• UPDATE: Modifies existing data records in a table, either directly or through a view.
• DELETE: Removes existing data records in a table, either directly or through a view. 
• MERGE: Performs a combinations of INSERT, UPDATE, and DELETE statements in a single statement, depending on some condition.

TCL Statements: 

• COMMIT: Saves a set of DML modifications performed in the current database session.
• ROLLBACK: Undoes a set of DML modifications performed during the current database session.
• SAVEPOINT: Marks a point in a session to prepare for a future ROLLBACK to enable ROLLBACK to restore data at the selected point in a session.

Hello, SQL!

Every SQL statement begins with a keyword that defines its action(e.g., SELECT, INSERT, UPDATE, DELETE.) Although SQL keywords are not case sensitive, conventionally, we use uppercases letters for them. SQL has a large number of keywords and relies on the code readers' ability to distinguish them versus identifiers. Using uppercase enhances readability by clearly distinguish them from the identifiers, such as columns or tables.

Depending on the keyword, there might be additional clauses that provide specific details to its action. For example, the SELECT statement shown in the figure above comprises of two clauses: the SELECT clause and FROM clause. The SELECT clause typically specifies which columns to retrieve from a table, while the FROM clause specifies the table where these columns are located.

The provided SQL statement, SELECT 'Hello, SQL!' FROM DUAL;, instructs the Oracle Database to return the text string 'Hello, SQL!' itself, not data from any table. The FROM DUAL is used here because the SELECT clause doesn't require any data from a specific table. In Oracle SQL, DUAL is a special table that acts as a placeholder and always return a single column (often named DUMMY.) It is a convenient way to satisfy the syntax requirement of having a table name in the FROM clause.

Lastly, every SQL statement must end with a semicolon (;)^[3]. Omitting a semicolon is a common mistake that even an experienced SQL programmer often makes. So, it is important to make it a habit to include semicolons consistently even for single statements.

Adding Comments

Comments are lines or sections of code that are ignored by the computer but are intended for human readers. They are essentially explanatory notes that don't affect the program's execution but provide clarity, improve readability and aid in understanding the code's logic. Particularly, when you are working in a team environment, comments act as a communication tool for your colleagues. Clear comments explain your code's intent, facilitating collaboration and knowledge sharing. 

In SQL, there are two ways to add comments. One of the ways to use two hyphens (--) at the beginning of your comment. Everything following the -- on the line is considered a comment and ignored by the SQL engine. Another way is to use a /* opening delimiter and */ closing delimiter to enclose a comment. This can span multiple lines and everything between the two delimiters is considered a comment. Here's an example demonstrating both:

---

^[1] Typically defined by columns. For example, querying the employees table with a condition where job_id column value is 'IT_PROG' retrieves sets of data records that meet this criterion. Similarly, to delete any row where the job_id column value is 'ST_CLERK', SQL first filters such rows from the table and delete the entire set of rows. This behavior is different from a procedural approach, where iterates over each row in a table and evaluates if the column value equals to the specified value. ^↩ 

^[2] Note that these keywords are not really statements or commands by themselves, but become one when combined with other reserved keywords. For example, CREATE TABLE or CREATE SEQUENCE is an SQL statement. In practice, however, many professionals call them CREATE statement. It is also worth noting that the term command and statement are used interchangeably by Oracle's official documentation. Either is fine! ^↩ 

^[3] Although Oracle wouldn't raise an error for omitting a closing semicolon for the very last SQL statement, it is considered a best practice to always include semicolons for better readability and to avoid potential errors. ^↩