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:
The following table illustrates this 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:
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.
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:
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.
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:
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.
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.
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 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:
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:
The INSERT Command
To insert new rows into a table we use the INSERT command. For example:
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 UPDATE Command
This SQL statement is used to update or modify an existing row(s) in a table:
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:
It is possible to include a subquery in the expression of the 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:
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.
IBM Mainframe Related Interview Questions
|IBM Lotus Notes Interview Questions||IBM-CICS Interview Questions|
|COBOL Interview Questions||Linux Interview Questions|
|IBM-JCL Interview Questions||IBM Mainframe Interview Questions|
|IBM AIX Interview Questions||IBM WAS Administration Interview Questions|
|IBM Lotus Domino Interview Questions||IBM Integration Bus Interview Questions|
|Mainframe DB2 Interview Questions||Unix Production Support Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.