TYPES OF SQL - IBM Mainframe

SQL can be classified based on its functionality. SQL can be used to control, define and manipulate data. The classification can be done as follows:

  • Data Control Language (DCL), which provides the control statements that, governs data security with GRANT and REVOKE statements.
  • Date Definition Language (DDL), which creates and maintains the physical data structure with CREATE, DROP and ALTER verbs.
  • Data Manipulation Language (DML) that accesses and modifies data with the SELECT, INSERT, DELETE and UPDATE statements.

The following table illustrates this functional classification:

functional classification

Another way to categorize the SQL is by its existence. The SQL can exist either embedded in the host application program or it can exist as stand-alone. Then SQL can be dynamic or static. Static SQLs are embedded in the application program. Dynamic SQL is either typed in at the terminal for real-time execution or constructed in the application program at runtime.

Data Control Language (DCL)

A data control language(DCL) is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language(SQL). Examples of DCL commands include:

  • GRANTto allow specified users to perform specified tasks.
  • REVOKEto cancel previously granted or denied permissions.

The operations for which privileges may be granted to or revoked from a user or role may include CONNECT,SELECT,INSERTUPDATE,DELETE,EXECUTE, and USAGE. In the Oracle database, executing a DCL command issues an implicitcommit. In Postgre SQL, executing DCL is transactional, and can be rolled back.

Examples

  • GRANT SELECT ON table-name TO user - gives the user the authority to do select operations on the table.
  • GRANT SELECT, DELETE, UPDATE ON table-name TO user - gives select, delete and update authority.
  • GRANT SELECT, UPDATE ON table-name TO user WITH GRANT OPTION -Here the user gets the select and update authority and also he can grant them to other users.
  • GRANT ALL ON table-name TO user - Grants all the privileges for the table to the user
  • GRANT CREATE TABLE, CREATE VIEW TO Alexis - Gives the system privileges for creating tables and views to Alexis.
  • REVOKE CREATE TABLE FROM Alexis - Removes the privilege to create tables
  • REVOKE SELECT ON table-name FROM user - Revokes the select privilege from user.

A more detailed discussion on the DCL commands will be taken up in the later chapters where we discuss the security aspects.

Data Definition Language (DDL)

Data Definition language is used to create, alter and delete database objects. The commands used are CREATE, ALTER and DROP. The principal logical Data definition statements are listed below:

  • CREATE TABLE
  • CREATE VIEW
  • CREATE INDEX
  • ALTER TABLE
  • DROP TABLE
  • DROP VIEW
  • DROP INDEX

Only the Data Definition Statements related to tables are discussed here. The other statements namely CREATE and DROP views and indexes will be discussed in later chapters when we deal with views and indexes in detail.

CREATE TABLE

A table in a relational system consists of a row of column headings, together with zero or more rows of data values. The CREATE TABLE creates a base table. A base table is an autonomous named table. By autonomous, we mean that the table exists by its own right, unlike a view, which does not exist in its own right but is derived from one ore more base tables. The CREATE TABLE statement has two formats. Format 1 is the general form and is as follows:

CREATE TABLE

CREATE TABLE

This statement will create an empty base table named OBJECT. The table will have four columns and the primary key will be OBJECTJD.

The second format of the CREATE TABLE statement is as follows and this format allows the user to create a base table that is having the same structure as some existing table.

CREATE TABLE base-table-name LIKE table-name;

For example the statement "CREATE TABLE OBJ LIKE OBJECT;" will create a table called OBJ with the same structure as OBJECT. But the only thing that is to be noted here is that the primary, alternate and foreign key definitions is not inherited.

ALTER TABLE

The existing base table can be modified by using the ALTER TABLE statement. The format of the ALTER TABLE statement is as follows

ALTER TABLE base-table-name

ADD column data-type [NOT NULL WITH DEFAULT];

An example of the statement is given below:

ALTER TABLE OBJECT

ADD DISCOUNT SMALLINT;

New columns can be added, primary and foreign key specifications can be added or removed using this command. But alternate key specifications cannot be changed using the ALTER TABLE statement. The important thing to remember here is that the ALTER TABLE statement does not support any kind of change to the width or data type of an existing column neither does it support the deletion of an existing column.

DROP TABLE

An existing base table can be deleted at any time by using the DROP TABLE statement. The syntax of this statement is DROP TABLE base-table-name The specified base table is removed form the system. All indexes and views defined for the table are also automatically dropped.

Data Manipulation Language (DML)

A data manipulation language(DML) is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database. Performing read-only queries of data is sometimes also considered a component of DML.

A popular data manipulation language is that of Structured Query Language(SQL), which is used to retrieve and manipulate datai n a relational database.Other forms of DML are those used by IMS/DLI,CODASYL databases, such as IDMS and others.

Data manipulation language comprises the SQL data change statements which modify stored data but not the schema or database objects.Manipulation of persistent database objects, e.g., tables or stored procedures,via the SQL schema statements rather than the data stored within them is considered to be part of a separate data definition language. In SQL these two categories are similar in their detailed syntax, data types, expressions etc., but distinct in their overall function.

The SELECT Statement

To query data from tables in a database, we use the SELECT statement. The SELECT statement has many different options that one can use to retrieve the data that he wants. You can use the SELECT command in its simplest form to retrieve all data from a table as follows:

SELECT * FROM table-name.

The wildcard character * indicates that you want all the columns in the table to be retrieved. If you do not want all the columns in the table then you can specify the columns that you want as in this statement:

SELECT coll, col2, col3 FROM table-name

You can further narrow down the query's result set to specific rows which satisfies certain conditions by using the WHERE clause and the condition or conditions that limits the rows in the result set as follows:

The SELECT Statement

The SELECT Statement

The conditions in the WHERE clause need not be the simple comparison expression. The following are all examples of queries with different types of expressions in the WHERE clause:

types of expressions in the WHERE clause

The SELECT command also has options for formatting the output of the results set. For example, the ORDER BY clause allows you to order rows in a query's return set:

ORDER BY clause allows you to order rows in a query's return set

The INSERT Command

To insert new rows into a table we use the INSERT command. For example:

The INSERT Command

The INSERT Command

The INTO clause specifies the target table and an optional list of column values, and the VALUES clause specifies the different column values for the new row. If you do not specify the list of column values that you are going to supply a value for every column in the table, as in the first example, must be given. You can also use a subquery instead of the VALUES clause to copy multiple rows from a table to another:

The INSERT Command

The UPDATE Command

This SQL statement is used to update or modify an existing row(s) in a table:

The UPDATE Command

This SQL statement updates every customer's fax number to NULL. You can update specific rows in the target table by specifying the WHERE clause:

The UPDATE Command

The UPDATE Command

It is possible to include a subquery in the expression of the SET clause of the UPDATE statement:

SET clause of the UPDATE statement

The above statement updates the customerid of order 100 to the customer id of Alexis.

The DELETE Command

Used for deleting row(s) form a table.

DELETE FROM customer-table.

The above statement deletes all rows from the customer table. If you want to delete specific rows from a table then you should specify the WHERE clause as in the following example which will delete the order with id=100:

The DELETE Command

That was a quick look into the Data Control, Definition and Manipulation Language features of the SQL. A more detailed discussion and the issues specific to DB2 is done later in this chapter.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics