Although the vendors of relational database management system have their own implemented version of the SQL language, exist anyway an ISO/IEC standard, which has been revised last time in 2006, that defines what SQL is and how it is structured. The main components of SQL language (which stands for Structured Query Language) are: DDL, DML, DCL and TCL; These components, respectively include transactions able to create database infrastructure, manipulate data, manage user privilege and manage DML transaction execution (like ‘update’, ‘insert’ and ‘delete’).
DDL — Data Definition Language: It refers to the SQL part used to create, modify, and destroy a database or its object/s. The most important DDL commands in fact are CREATE, ALTER, DROP and TRUNCATE. CREATE command indeed allow users to create basic SQL elements which include database, table, views, schemas, catalogs and clusters. Typically, in a SQL database, these elements are organized in a hierarchical structure as for the figure below where tables contain columns and rows, schemas contain tables and catalogs contain schemas.
The following are three examples of the CREATE command used to create a database, table and a view.
While in database creation it is just required to specify the database name, in the table creation apart from the table’ name it’s also required to specify the columns name contained in the table and their data type; other information as Primary Key, Foreign Key, Identity etc might also be included in the same command.
In order to create a view instead (which is commonly used to show just part of the data contained into a specific table or group of tables combined together), it is necessary to specify at least the data needed to be shown (to be specified under the command SELECT) and the origin of the data taken into consideration (table specified under the command FROM). It worth to note that a View has no independent physical existence and its data are not duplicated somewhere in the disk storage as it takes data directly from one or more tables from which it derives; that’s why a View is considered to be a virtual table and therefor its creation is considered to be a DDL command. More complex Views can be performed using DML commands explained in the next chapter.
As a relational database needs change, also the existing database’s objects might be modified using the command ALTER. By using ALTER TABLE statement, for example, is possible to modify the table’s structure by adding, changing, or deleting column in a specific table. In the first example below in fact the column City has been added to the table Customers. In the second example instead, the existing column phone_number has been modified from INT to varchar(50), while in the third one the same column (phone_number) has been deleted from the table Customers. The command ALTER might be applied to different SQL objects as Table, Database, Schema etc.
As previously said also DROP and TRUNCATE are DDL commands and they are used to delete things from the database. However, they differentiate from each other by what they delete from the database. DROP command, in fact, is used to delete physical or non-physical objects as Tables or Views (and everything else has been created with the CREATE command) while TRUNCATE command is used to remove all records stored in a specific table keeping intact the table structure (as columns, data types, PK, FK etc).
In the following example, in fact, is possible to note that after the execution of the commands below, the system returns in output an empty table as all its records have been removed.
If we want to inspect the data contained in the ‘customers’ table after the execution of the DROP TABLE command as for the figure below, the system returns in output an error message saying: “Invalid object name ‘customers’”. That is because the ‘Data Table Inspection’ code is trying to retrieve data from the ‘customers’ table which doesn’t exist anymore as it has been removed from the database through the execution of the DROP TABLE command.
DML — Data Manipulation Language: While DDL commands are used to build the database structure in which data will be stored, DML commands are used to work with data itself. The most common DML commands are INSERT, UPDATE, DELETE and SELECT. The first 3 statements are used respectively to insert data into a table, to update an existing record or delete it. The SELECT statement instead is used to retrieve data from specific table/s once specified at least the information needed (under the SELECT statement) and the source (under FROM statement). The examples below are simple lines of code able to perform the commands mentioned above.
However, in most cases, depending on the task that we want to perform, these statements could be much more complex and include multiple clauses. Each clause may incorporate value expressions, predicates functions, logical connectives, aggregate functions, and subqueries. See examples below:
Values expressions — a few examples:
Numeric: Es. 4–9+8
String: Es. ‘Cutting-edge’ || ‘technologies’ -> ‘Cutting-edge technologies’
Datetime: Es. DueDate
Boolean: Es. (Class = Senior) IS TRUE
Predicate function — In a table containing information about cars, the domain of the COLOUR column may be RED, YELLOW, GREEN, BLUE, and WHITE. It’s possible to use the predicate COLOUR = RED to filter out all rows for which the predicate is FALSE keeping just the ones for which the predicate is TRUE. The comparison predicate uses six different comparison operators which are:
In this specific case, it worth noting that just the first two entries make sense (Equal to / Not equal to) as SQL is conditioned to include or exclude rows based on a specific parameter. Using other operators instead (thinking about ‘Greater than’ for instance) doesn’t have much sense as SQL establishes which value is greater or smaller than the others based on the alphabetical order and very likely this interpretation is meaningless unless for some reason, the result obtained was not intentional.
Logical connectives — They allow users to put together multiple Predicate Functions building so complex but at the same time powerful predicates. Logical connectives are AND, OR and NOT. They can be used singularly or in combination as many times as needed. A simple example for each logical connectives could be:
1. Colour = Red AND Speed > 150
2. Colour = Red OR Speed > 150
3. NOT(Colour = Red AND Speed > 150)
While in the first example the compound predicate is TRUE just if both its components are TRUE, the second one is TRUE if at least one of the components predicates is TRUE. The last example instead returns a TRUE compound predicate just if both components predicates are FALSE (that’s because the NOT operator invert the Boolean values to which it is applied)
Aggregate functions — Aggregate functions are used when the information required does not relate to a single row but rather to a set of rows. Count, Sum, Min, Max and AVG are the most used ones. Used on their own, aggregate functions return in output a single row containing a specific calculation (depending on the type of function used) which is based on the entire column to which the function has been applied.
In case aggregate functions are present under the SELECT statement in addition to other variables, those variables need to be contained in either an aggregate function or in the GROUP BY clause as shown in the two examples below. By including the product_id under the variable group by (first example), SQL performs the sum calculation grouping the result by product_id values. In the second example instead, as an aggregate function has been applied to product_id, the group by clause is not necessary anymore as SQL group everything in one single line by performing the product_id count.
Subqueries — Subqueries are powerful tools that enable users to access to elaborate information obtained by nesting a query into another query. This process can be reiterated n times between different queries, till when the wanted result is not reached. Commonly, subqueries are used under the Select, From, and Where clauses and they may provide one or a list of results to choose from depending on their function and how they are structured. For instance, in the example below, the nested subquery (in WHERE clause) provides a list of values used as a filter by the main query.
DCL — Data Control Language: DCL is a SQL component used to control access to data stored in a database by granting or revoking authorization to a user or a group of users. This need arises from the fact that, in order to ensure the integrity of the data, just the right users should have access to it; in fact, while many people should not have access at all to the data, some others should see just part of it and a very little portion of users instead should have unlimited access to everything in the database. The system administrator can grant the access to data directly to a specific user or can create a group of users to which give those privileges and then to manage users authorization just by adding or removing users from specific groups; this second scenario is extremely efficient in managing authorizations in large organizations with many users as by changing a single group privilege, using just one transaction, is possible to change the privilege to all users belonging to that group. The commands used to perform such tasks are GRANT and REVOKE; while Grant is used to give access to the data, Revoke is used to revoke the privileges granted; any privileges not explicitly granted are withheld by default. These two commands are used to protect database objects as Tables, Columns, Views, Domains etc. by allowing or denying users to see, add, modify, delete, reference, or use them. These commands indeed may be applied to statement as SELECT, INSERT, UPDATE and DELETE as for the example below:
TCL — Transaction Control Language: TCL is the SQL component that includes commands able to manage DML transactions within a database. More specifically TCL includes commands like SAVEPOINT, COMMIT, and ROLLBACK.
SAVEPOINT is used to temporarily save a transaction and roll back to it whenever necessary through the command ‘rollback’. The syntax to use this command is:
update customer
set first_name = ‘Anthony’
where first_name = ‘Mary’
savepoint savepoint_name
ROLLBACK is used to undo the last transaction performed or to restore the database at the last save point. To do that (using the previous example), is sufficient to execute the following transaction:
rollback to savepoint savepoint_name
COMMIT instead, is used to permanently save a transaction change into a database. It is worth to note that after its execution, is not possible to perform a ‘rollback’ anymore so that’s why the ‘commit’ command should be used just when we are sure the code is correct; even, in this case, its syntax is very easy as it is necessary to write ‘commit’ after the transaction.
update customer
set first_name = ‘Anthony’
where first_name = ‘Mary’
commit
Thanks for being a part of our community!
If you found this article helpful and would like to show your support, don’t hesitate to:
Clap on this story
Leave a comment below telling me what you think. This will help me with the next articles
Support my work on Buy Me a Coffee ☕️
These actions really really help me out, and are much appreciated!
Comments