Structured Query Language (SQL) – DDL, DML and TCC commands

To totally unlock this section you need to Log-in

Login
Having query about what a SQL is? It is Structured Query Language. It is a non-procedural language and it is a database language, used primarly to ask questions to a "data repository", which is able to relate data it contains.

Categories in SQL commands

There are 3 broad categories in SQL commands. They are namely

Data Definition language (DDL)

Data Manipulation Language (DML)

Transaction Control Commands

Under each category there are many SQL commands. Let us see each in brief.

Commands under Data Definition language (DDL)

This category include creating, dropping and altering table commands

CREATE TABLE is used in creation of table(s).

CREATE TABLE tablename (colname col definition, colname col definition…); 

ALTER TABLE: If you want to modify a table definition we can use this command. This command is used to alter column definition in table.

ALTER TABLE tablename MODIFY (Column definition); 

DROP TABLE: This command is used to remove an existing table permanently from database.

DROP TABLE tablename;

Commands under Data Manipulation Language (DML)

INSERT: This command is used to insert rows into the table.

INSERT INTO tablename VALUES (value,value,….); 

SELECT: This is used to select values or data from table.

SELECT column name1, column name2, …. FROM tablename; 

If we want to select all column values from a table then SQL command use is SELECT * from tablename.

DELETE: In order to delete rows from a table we use this command.

DELETE FROM tablename WHERE condition; 

Based on the condition specified the rows gets fetched from the table and gets deleted in table. Here the WHERE clause is optional.

UPDATE: This SQL command is used to modify the values in an existing table.

UPDATE tablename SET columnname = value, columnname = value,….. WHERE condition; 

The rows which satisfies the WHERE condition are fetched and for these rows the column values we placed in command above in SET statement gets updated.

Commands under Transaction Control Statements

ROLLBACK – This SQL command is used to undo the current transaction.

ROLLBACK; 

SAVEPOINT: This is used to identify a point in a transaction to which we can later rollback.

SAVEPOINT savepoint_identifier; 

COMMIT: This command is used to make all changes permanent in database and also marks the end of transaction.

COMMIT; 

Always it is better to commit changes to database at regular intervals since it will help loss of data or loss of work done when computer shuts due to unavoidable reasons.

Having known about the broad categories of SQL commands let us see some more SQL important terminologies.

Constraints Handling in SQL

Constraint Handling can be done in two levels namely:

Column Level

Table Level

Some of the constraints used in SQL are:

The keyword NOT NULL marks a column that the particular column cannot contain null. By default column contains null unless we define the constraint NOT NULL. This is a column level constraint as it can be defined for columns only.

PRIMARY KEY: a column or a group of columns together can be defined as PRIMARY KERY. If a column or a group of columns are defined as a PRIMARY KEYS then the value of the primary key cannot appear more than once in the table. Also those columns defined as primary keys cannot have null values in it.

REFERENTIAL INTEGRITY: the table on which the column or a combination of columns is defined by this constraint is called the foreign key is called child table. When this constraint is defined a relation is defined between this table which has the foreign key and a table that has the primary key in relationship with this. This is called as referenced key. The table which has the primary key or in other words the referenced key is called as parent table.

SQL Functions to Handle Arithmetic Operations

All these SQL arithmetic functions described below operate on numerical values only and are used to get data as per users required format.

Some of the functions are:

To find the absolute value of a number we can use the SQL function ABS(number);

To round a number to a specified number of decimal places SQL command used is ROUND(number, number of decimal places to be rounded);

To convert a char to a number SQL function used is TO_NUMBER(char value);

VIEW: this is an important concept in SQL. A View is nothing but a window of an existing table. In other words it is a logical representation that is created from one or existing base table. After creation of view it can be used just as a base table. That is one can query or select values of data’s from views and it also possible to update a view.

Views are created by suing CREATE VIEW command in SQL

CREATE VIEW viewname AS SELECT statement 

One of the important points to consider while a user uses views is that we have seen that vies are created from a base table and if suppose the base table is deleted after which if a user tries to use or access the view error will occur.

SYNONYMS: it is possible to refer a table with a different name and this done by using CREATE SYNONYM. It is possible to create synonym for tables as well as views.

CREATE synonym name FOR username.tablename; 

GROUP Functions: we have seen before arithmetic functions which operated on numerical value and returned a single value for each single row taken ass input. But there may be occasions where one might require several rows to be grouped and the result of the grouped rows might be needed. To handle such situations GROUP functions in SQL helps. There are number of group functions available in SQL. Let us see some of them:

Average: to calculate the average of a group of values SQL defines the function AVG(column name).

Counting number of values: if we want to count the number of values in a particular column then we can use the SQL function COUNT(column name).

Maximum: to find the maximum among the column values SQL function MAX(column name) can be used.

Minimum: to find the minimum among the column values SQL function MIN(column name) can be used.

There are many more SQL commands and terminologies in SQL but the above gives only an overview of the basics of SQL.