SQL(Structured Query Language) - Basic Query to Learn

[pingu.buj]

SQL(Structured Query Language)

Structured Query Language (SQL) provides the ability to create and define relational database objects. After these objects are defined, the language permits one to add data to these objects. Once data has been added, one can modify, retrieve, or delete that data. The language provides the capability of defining what type of authority one might have when accessing the data.

Data Definition Language

As the name implies, there is a group of SQL statements that allows one to define the relational structures that will manage the data placed in them. The "CREATE" statements brings Relational Database Management System (RDMS) objects into existence. The types of objects one can create are STOGROUP, Database, Table space, Table, Index, View, Synonym, and Alias. The definitions of these objects are as follows:

STOGROUP: A storage group is a list of disk volume names to which one can assign a name. One defines the list of disk volumes and assigns the STOGROUP name with the Create STOGROUP statement.

Database: A database is a logical structure in which tables and indexes are later created. The database is defined and associated with a STOGROUP with a Create Database statement.

Tablespace: A tablespace is an area on disk that is allocated and formatted by the Create Table space statement.

Table: A table is an organizational structure which is defined in a Create Table statement. In this statement, the data attributes are defined by column, giving each column its own unique name within the table.

Index: A index is used in conjuction with the "Primary Key" parameter of the Create Table statement. It is made with the Create Index statement and provides the duplicate record-checking necessary for a unique key.

View: A view is an alternative perspective of the data present in a database. It is made with the Create View statement and can represent a subset of the columns defined in a table. It can also represents a set of columns combined from more than one table.

Synonym: The Create Synonym statement defines an unqualified name for a table or a view.

Alias: The Create Alias statement defines an alternate qualified name for a table or a view.

After a table is created, additional columns may be added with an Alter Table statement. Any RDMS object that was made with a create statement can be removed with a drop statement.

In order to define RDMS objects, one needs various levels of authority. The following is a list of authority levels that can be granted to a user ID to operate on a designated database.

DBADM                Database administrator authority

DBCTRL                Database control authority

DBMAINT            Database maintenance authority

CREATETS           Create Table space Authority

CREATETAB       Create Table authority

DROP                     Drop authority on a database or subordinate objects

Data Manipulation Language

There are four SQL data manipulation statements(DML) available: Insert, Select, Update, and Delete. After tables are defined, they are ready to store data. Data is added to tables through the SQL Insert statement. Once data has been inserted into a table, it can be retrieved by the use of the Select statement. Data stored in a table can be modified by executing the SQL Update statement. Data can be deleted from a table by using the SQL Delete statement.

The SQL statements perform RDMS operations that can affect only one row at a time if desired. The same statements can, if required, affect many or all of the rows in a table. It is possible to select one row and insert it into another with one statement. It is also just as easy to select all of the rows from one table and insert all of them into another with a single statement. The same scope of operation applied to the update and delete statements.

The scope of operation is controlled by the use of the WHERE clause. The operation will affect only the rows that satisfy the search condition. When no search condition specified, the entire table is affected.
There are additional language elements available that provide the ability to process the table data while it is being retrieved. In addition, there are a variety of functions that modify the value of the data that is returned in a query. There are column functions that act on all of the values of the selected rows for a specified column and return a single answer. There are also scalar functions that return a specific answer for each row that satisfies the search condition.

As mentioned previously, SQL provides the ability to filter what data is retrieved in a select statement by including the WHERE clause. The WHERE clause specifies a variety of comparisons between two values. The values could be column values or the result of an operation involving more than one column or a constant.

The comparison operation are the same as those used in COBOL, with the exception of two additional operators. The first is the IN operator that compares a single value has a match in the specified list of values. The other is the LIKE operator, in which you can specify a value string that includes "wildcard" characters in such a manner that you can select rows of a table where column values are similar to the extent you require.SQL provides four arithmetic operations : addition, subtraction, multiplication, and division.

An arithmetic expression may involve any combination of column name or numbers. The arithmetic expression may itself be used as a column name or in a Select, Insert, Update, or Delete statement.SQL provides the ability to sort the data retrieved from a table via the ORDER BY clause. In this clause, you can specify one or more sort column names as well as if each sort key is ascending or descending.

SQL also provides the ability to perform set manipulation operations. Using SQL, one can SELECT the intersection of two or more sets of data by coding a JOIN. A JOIN is any SELECT statement that has more than one DBMS object listed in its FROM clause. One can combine different sets of data by using the UNION operator. Other set manipulations can be executed by combining different operators and search conditions.