ROLLUP and CUBE Operation

ROLLUP Operation: Getting sub totals

The rollup operator can be used to obtain sub- totals in a query. ROLLUP operator is an extension to the GROUP BY clause in a query that produces sub-totals in addition to the regular grouped rows.

SELECT DNAME, JOB, SUM (SAL), AVG (SAL) FROM EMP A,
(SELECT DNAME, DEPTNO FROM DEPT) B
WHERE A.DEPTNO = B.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);

DNAME JOB SUM (SAL) AVG (SAL)
ACCOUNTING CLERK 1300 1300
ACCOUNTING MANAGER 3450 1725
ACCOUNTING PRESIDENT 5000 5000
ACCOUNTING 9750 2437.5
OPERATIONS MANAGER 6000 1500
OPERATIONS 6000 1500
RESEARCH ANALYST 6000 3000
RESEARCH CLERK 1900 950
RESEARCH MANAGER 2975 2975
RESEARCH 10875 2175
SALES CLERK 950 950
SALES MANAGER 2850 2850
SALES SALESMAN 5600 1400
SALES 9400 1566.66
ANALYST 6000 3000
CLERK 4150 1037.5
MANAGER 15275 1909.37
PRESIDENT 5000 5000
SALESMAN 5600 1400
36025 1896.05

CUBE Operation: Getting Cross-Tabs

The CUBE operator is an extension to the GROUP BY clause within a query that produces a result set that contains sub-totals for every possible combination of the columns in the group by clause.

SELECT DNAME, JOB, SUM (SAL), AVG (SAL) FROM EMP A,
(SELECT DNAME, DEPTNO FROM DEPT) B
WHERE A.DEPTNO = B.DEPTNO
GROUP BY CUBE (DNAME, JOB);

DNAME JOB SUM (SAL) AVG (SAL)
ACCOUNTING CLERK 1300 1300
ACCOUNTING MANAGER 3450 1725
ACCOUNTING PRESIDENT 5000 5000
ACCOUNTING 9750 2437.5
OPERATIONS MANAGER 6000 1500
OPERATIONS 6000 1500
RESEARCH ANALYST 6000 3000
RESEARCH CLERK 1900 950
RESEARCH MANAGER 2975 2975
RESEARCH 10875 2175
SALES CLERK 950 950
SALES MANAGER 2850 2850
SALES SALESMAN 5600 1400
SALES 9400 1566.66
ANALYST 6000 3000
CLERK 4150 1037.5
MANAGER 15275 1909.37
PRESIDENT 5000 5000
SALESMAN 5600 1400
36025 1896.05

AGGREGATE FUNCTIONS IN ORACLE

SQL provides a set of built-in functions for producing a single value for an entire group.
These functions are called as Set functions or Aggregate functions. These functions can work on a normal result table or a grouped result table. If the result is not grouped then the aggregate will be taken for the whole result table.

1) COUNT (*)

COUNT returns the number of rows

To find the total number of employees

SELECT COUNT(*) FROM emp;

COUNT (*)

14

Note: It is possible to restrict the rows over which COUNT operates. To find the total number of CLERKS, use

SELECT COUNT (*) FROM emp WHERE job = 'CLERK';

To find the total number of CLERKS hired after '13-jan-81', say

SELECT COUNT (*) FROM emp
WHERE job = 'CLERK' AND hiredate > '13-jan-81';

When an aggregate function is used in a SELECT statement, column names cannot be used in SELECT unless GROUP BY clause is used.

2) SUM (col_name expression)

SUM returns the total of values present in a particular column or a number of columns linked together in the expression. All the columns, which form the argument to SUM, must be numeric only.

To find the sum paid as salary to all employees every month

SELECT SUM (sal) FROM emp;

SUM (SAL)
29025

To find the yearly compensation paid to all SALESMEN use

SELECT SUM (12*sal) FROM emp
WHERE job = 'SALESMAN';

3) AVG (col_nameexpression)

AVG is similar to SUM. AVG returns the average of a NUMBER of values. The restrictions, which apply on SUM also, apply on AVG.

To find the average salary all employees

SELECT AVG (sal) FROM emp;

AVG (SAL)

2073.21429

To find the average yearly compensation paid to SALESMEN, use

SELECT AVG (12*sal) FROM emp
WHERE job = 'SALESMAN';

4) MIN (col_nameexpression)

MIN returns the lowest of the values from the column. MIN accepts columns which are NON-NUMERIC too.

To find the minimum salary paid to any employee

SELECT MIN (sal) FROM emp;

MIN (SAL)

800

To list the employee who heads the list alphabetically, use
SELECT MIN (ename) FROM emp;

5) MAX (col_nameexpression)

MAX is the reverse of MIN. MAX returns the maximum value from among the list of values.

To find the maximum salary paid to any employee

SELECT MAX (sal) FROM emp;

MAX (SAL)

5000

Modification of Existing Database Objects

TABLE:

An existing table can be modified even if it contains data. However, it is recommended that you create a table after putting sufficient thought into it.

ALTER TABLE table_name
[ADD (col_name col_datatype col_constraint,….)]
[ADD (table_constraint)]
[DROP CONSTRAINT constraint_name]
[MODIFY existing_col_name new col_col_datatype new_constraint new_default]
[DROP COLUMN existing_col_name]
[SET UNUSED COLUMN existing_col) name];

Table_name must be an existing table.

The rules for adding a column to the table are:

1) Can add any column without a NOT NULL specification.
2) Adding a NOT NULL column is possible in three steps.
- Add a column without NULL specification.
- Filling every row in that column with data.
- Modifying the column to be NOT NULL.

The rules for modifying the columns are:

1) Can increase a character column’s width any time.
2) Can increase the number of digits in a number at any time.
3) Can increase or decrease the number of decimal places in a number column at any time. Any reduction on precision and scale can be on empty columns only.
4) You can add only NOT NULL constraint using column constraints. Rest all constraints have to be specified as table constraints.

For adding three more columns to the EMP table.

ALTER TABLE emp
ADD (sal NUMBER (7, 2) CONSTRAINT SAL_GRT_0 CHECK (sal >0),
Mgr NUMBER (4),
Comm. NUMBER (9, 2));

For adding referential Integrity on mgr column

ALTER TABLE emp
ADD CONSTRAINT EMP_FMGR_KEY FOREIGN KEY (mgr) REFERENCES
EMP (empno);

For modifying the width of sal column

ALTER TABLE emp MODIFY (sal NUMBER (8, 2));

For dropping the FOREIGN KEY constraint on mgr

ALTER TABLE emp
DROP CONSTRAINT EMP_F_KEY;

Rules for dropping column

Oracle allows you to drop a column from a table. This can be done in two ways.

1) Marking the columns as unused and then later dropping them

ALTER TABLE emp SET UNUSED COLUMN comm.;
ALTER TABLE emp SET UNUSED (sal, hiredate);
Once all the required columns have been marked as unused, we can use the following command to remove the columns permanently. Columns once marked as unused cannot be recovered. Marking the columns as unused does not release the space occupied by them back to the database. Also until you drop these columns actually, they continue to count towards the absolute limit of 1000 columns per table. Also, if you mark a column of data type LONG as UNUSED, you cannot add another LONG column to the table until you actually drop the unused LONG column.

ALTER TABLE emp DROP UNUSED COLUMNS;

The advantage of the above mentioned steps is that marking the columns is much faster process than dropping the columns.

You can refer to the data dictionary table USER_UNUSED_COL_TABS to get information regarding the tables with columns marked as unused.

2) Dropping the columns directly

ALTER TABLE emp DROP COLUMN sal;

This command should be used with caution.

Note:
For DROP COLUMN command to work successfully, the table should be locked exclusively by the user giving the command.

When you drop a column

- All indexes defined on any of the target columns are also dropped.
- All constraints that reference a target column are removed.

SEQUENCE:

ALTER SEQUENCE s1
INCREMANT BY n1
MAXVALUE n3
CYCLE;

1) s1 is an existing sequence
2) To make the sequence start from a new number, drop the sequence and create again.
3) ALTER SEQUENCE has no effect on numbers already generated.

ALTER SEQUENCE s1
INCREMANT BY 5
MAXVALUE 25000
CYCLE;

Creation of Database Objects: TABLE.

Tables are objects, which store the user data. In Oracle, tables can be permanent or temporary.

Permanent Table:

Syntax
CREATE TABLE table_name
(
{col_name.col_datatype [[CONSTRAINT const_name][col_constraint]]},….
[table_constraint],…
)
[As query]

A table can have a maximum of 1000 columns. Only one column of type LONG is allowed per table.

Tabel_name, col_name, const_name: A string up to 30 characters length. Can be made up to A- Z, 0- 9, $, _, #. Must begin with non- numeric Oracle data characters.
Col_datatype: Can be any one data type available in Oracle.
Col_constraint: A restriction on the column. Can be of the following types – PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, can be named. Only one PRIMARY KEY allowed per table.
Table_constraint: A restriction on single or multiple columns. The types are same as in col_constraint. (NULL constraint is not allowed here)
As query: Query is an SQL statement using SELECT command. SELECT command returns the rows from tables. Useful if the table being created is based on an existing table. New table need not have all the columns of the old table. Names of columns can be different. All or part of the data can be copied.

Any object created by a user is accessible to the user and the DBA only. To make the object accessible to other users, the creator or the DBA must explicitly give permission to others.

Example:

CREATE TABLE emp
(
Empno NUMBER (4),
Ename VARCHAR2 (10),
Deptno NUMBER (2),
Job CHAR (9),
Hiredate DATE
);

If a table is created as shown above, then there is no restriction on the data that can be stored in the table. However, if we wish to put some restriction on the data, which can be stored in the table, then we must supply some constraints for the columns.

For example, if we want
1) to make empno as the primary key of the table.
2) to ensure that the ename column does not contain NULL values
3) the job column to have only UPPERCASE entries
4) to put the current date as the default date in hiredate column in case data is not supplied for the column.

The create statement can be rewritten as:

CREATE TABLE emp
(
Empno NUMBER (4) CONSTRAINT P_KEY_PRIMARY KEY,
Ename VARCHAR2 (10) CONSTRAINT ENAME_NOT_NULL NOT NULL,
Deptno NUMBER (2),
Job CHAR (9) CONSTRAINT JOB_ALL_UPPER
CHECK (job = UPPER (job)),
Hiredate DATE DEFAULT SYSDATE
);

Now if we have a table DEPT which has following description,

CREATE TABLE dept
(
Deptno NUMBER (2),
Dname VARCHAR2 (14) NOT NULL,
Loc VARCHAR2 (14) NOT NULL
);

In EMP table, for deptno column, if we want to allow only those values that already exist in deptno column of DEPT table, we must enforce what is known as REFERENTIAL INTEGRITY.

To enforce REFERENTIAL INTEGRITY, declare deptno field of DEPT table as PRIMARY KEY and deptno field of EMP table as FOREIGN KEY as follows.

CREATE TABLE Dept
(
Deptno NUMBER (2) CONSTRAINT DEPTNO_P_KEY PRIMARY KEY,
Dname VARCHAR2 (14) NOT NULL,
Loc VARCHAR2 (14) NOT NULL
);

CREATE TABLE Emp
(
Empno NUMBER (4) CONSTRAINT P_KEY_PRIMARY KEY,
Ename VARCHAR2 (10) CONSTRAINT ENAME_NOT_NULL NOT NULL,
Deptno NUMBER (2),
Job CHAR (9) CONSTRAINT JOB_ALL_UPPER
CHECK (job = UPPER (job)),
Hiredate DATE DEFAULT SYSDATE,
CONSTRAINT DEPTNO_F_KEY FOREIGN KEY (Deptno)
REFERENCES Dept (Deptno)
);
In the above example FOREIGN KEY has been declared as a table constraint. Same can be given as column constraint as follows.

CREATE TABLE Emp
(
Empno NUMBER (4) CONSTRAINT P_KEY_PRIMARY KEY,
Ename VARCHAR2 (10) CONSTRAINT ENAME_NOT_NULL NOT NULL,
Deptno NUMBER (2) CONSTRAINT DEPTNO_F_KEY REFERENCES Dept (Deptno),
Job CHAR (9) CONSTRAINT JOB_ALL_UPPER
CHECK (job = UPPER (job)),
Hiredate DATE DEFAULT SYSDATE
);

The following example shows how to create a new table based on an existing table

CREATE TABLE Newemp (Emp#, Emp_Name, Hire_Date)
AS
(SELECT Empno, Ename, HIREDATE from emp
WHERE Hiredate > ’01-jan-82’
);

Note:
A new table will be created
It will contain empno, ename, hiredate of all employees hired after 1st Jan 1982
Constraint on an old table will not be applicable for a new table

Temporary Tables:

Tables can be created which are temporary in nature. These tables can be used as a scratch pad. The table can be configured so that it holds the data for a transaction (till a commit or a rollback) or for the entire session.

The advantage of these tables is that no redo information is needed to be stored. The tables are created in the temporary tablespace assigned to the user.

Example:
CREATE GLOBAL TEMPORARY TABLE emp_temp
(
eno number, ename varchar2 (20)
)
ON COMMIT DELETE ROWS;

In this example the moment the user commits or rollbacks the transaction, the data in the table is deleted. If the user TRUNCATE command on a temporary table, then only the rows which were created in his session gets truncated, rows from other sessions does not get deleted.

You can also use ON COMMIT PRESERVE ROWS, to keep the rows after the transaction is complete. The rows will be deleted when the session is terminated.

Data of the temporary table is only visible within its defined scope, (transaction or session).
Difference/ Comparison between regular and temporary tables.

1) Indexes may be created on temporary tables, but their scope is same as that of the tables they correspond to.
2) Triggers are supported against temporary tables
3) A view may be created using a temporary table, but a view may not be creates using both a temporary and a regular table.
4) Definition of a temporary table may be exported but the data may be not.

Introduction to SQL * PLUS

SQL * PLUS is an Oracle tool which accepts SQL, SQLPLUS commands and PL/SQL blocks and executes them.

Access to SQL*PLUS: Get your oracle user name/password and connect string from your DBA/Instructor. Click on SQL-plus shortcut. In the dialog box provide appropriate information like your_oracle_user_name/your_oracle_password. If the information is accurate then you will get a window with SQL> prompt. Now SQL*PLUS is up and ready to accept your commands.

Enter/Edit/Store/Retrieve/Run SQL commands:

A] To enter a SQL> command, type the command at the SQL> prompt. An SQL command can be entered continuously on a single line or it can be spread over multiple lines. In case of any typing error, use BACKSPACE or DEL key to erase and then continue typing. In the explanation given below we assume that emp table exists in our database.

SQL> select empno, hiredate from emp where hiredate > ’01-jan-82’;
Can also be entered as

SQL> SELECT empno, hiredate
FROM emp
WHERE hiredate > ’01-jan-82’;

The semicolon “;” at the end of the commands is the command terminator. The indentation is for sake of clarity and is optional.

There are many ways to terminate a command, namely
1) A semicolon at the end of the line itself.
2) A semicolon on a line by itself.
3) A slash ‘/’ on a line by itself.
4) A blank line.

Whenever an SQL command is issued, the command is stored in memory, in an area called “SQL buffer”. If a semicolon or slash is used as command terminator, then the command is executed immediately and then stored in “SQL Buffer”. When a blank line is used as command terminator, then the command is not executed immediately. It is merely stored in buffer.

Type the following command at the SQL prompt:

SQL> Select *
From emp
Where hiredate > ’01-jan-82’

B] To see the SQL command in the buffer

SQL> LIST

C] To save the buffer contents into a file

SQL> SAVE file_name

If the user does not supply an extension, then ORACLE adds sql as default extension.

D] To load the contents of a file into the memory after flushing the buffer

SQL> CLEAR BUFFER
SQL> GET file_name

E] To load the contents of a file into memory and execute the commands in the file

SQL>@ filename
Or
SQL>START file_name

G] To execute the commands in the buffer

SQL>RUN
Or
SQL>/
RUN displays the buffer content before execution whereas / will merely execute the buffer content.

H] To store/ print the queries and the output

SQL>SPOOL [file_name OFF OUT]

SPOOL file_name: will open a file to send the query and the output.
SPOOL OUT: will send the current spool file for printing and stop further spooling.
SPOOL OFF: stops spooling.

To list the columns of a table:
SQL> DESC Table_name

Using Substitution Variables:

Instead of using a table name or a column name or a value in the SQL, we can use a variable.

SELECT empno, ename, sal FROM emp where deptno =10 and job = ‘CLERK’

Can be written as

SELECT empno, ename, sal from emp where deptno= &V_deptno and Job = ‘&V_JOB’

When the query is issued SQL*PLUS will respond with values to be entered for deptno and Job.

V_deptno and V_job variables whose values will be obtained at runtime and substituted in the SQL statement before execution of the statement and the ampersand & before the variable name indicates that, what follows is a variable and not a value. Since there is no direct substitution, quotes are not required.

Pause Command: PAUSE command is used to give a message to the user. Further processing is stopped until user acknowledges the message by pressing the ENTER key. The syntax is

PAUSE Message_String
PAUSE “PRESS ENTER KEY TO PROCEED”

Press enter key to proceed (systems waits till you press the Enter key)

Introduction to Database

Topic 1 : Introduction:

A set of inter-related data is known as database and the software that manages it is known as database management system or DBMS. Hence DBMS can be described as “a computer-based record keeping system which consists of software for processing a collection of interrelated data”. A set of structures and relationships that meet a specific need is called as a schema.

The database is centrally managed by a person known as the database administrator or the DBA. The DBA initially studies the System and accordingly decides the types of data to be used, then the structures to be used to hold the data and the interrelationships between the data structures. He then defines data to the DBMS. The DBA also ensures the security of the database. The DBA usually controls access to the data through the user codes and passwords and by restricting the views or operations that the user can perform on the database.

Topic 2 : Characteristics of DBMS:

Control of data redundancy: When the same data is stored in a number of files it brings in data redundancy. In such cases, if the data is changed at one place, the change has to be duplicated in each of the files.
The main disadvantages of data redundancy are:
1)Storage space gets wasted.
2)Processing time may be wasted as more data is to be handled.
3)Inconsistencies may creep in.
4)DBMS help in removing redundancies by providing means of integration.

Sharing of data: DBMS allow many applications to share the data.

Maintenance of Integrity: Integrity to data refers to correctness, consistency and the interrelationship of data with respect to the application that uses the data. Some of the aspects of data integrity are:
1)Many data items can only take a restricted set of values.
2)Certain field values are not to be duplicated across records. Such restrictions, called primary key constraints can be defined to the DBMS.
3)Data integrity which defines the relationships between files is called referential integrity rules, which can also be specified to the DBMS.

Support for Transaction Control and Recovery: Multiple changes to the database can be clubbed together as a single ‘logical transaction’. The DBMS will ensure that the updates take place physically only when the logical transaction is complete.

Data Independence: In conventional file based applications, programs need to know the data organization and access technique to be able to access the data. This means that if you make any change in the way the data is organized you will also have to take care to make changes to the application programs that apply to the data. In DBMS, the application programs are transparent to the physical organization and access techniques.

Availability of Productivity tools: Tools like query language, screen and report painter and other 4GL tools are available. These tools can be utilized by the end-users to query, print reports etc. SQL is one such language, which is emerging as standard.

Control over Security: DBMS provide tools by which the DBA can ensure security of the database.

Hardware Independence: Most DBMS are available across hardware platforms and operating systems. Thus the application programs need not be changed or rewritten when the hardware platform or operating system is changed or upgraded.

Topic 3 : The DBMS Model:

The range of data structures supported and the availability of data handling language depend on the model of DBMS is based. The models are:
1) The hierarchical model.
2) The network model.
3) The relational model.

The hierarchical model: In this parent can have many children. A child cannot have more than one parent and no child can exist without its parent.

The Network model: In this there are no restrictions on the number of parents. A record type can have any number of parent and child record types. It is more complex than the hierarchical model because of links. It can represent any structure designed in hierarchical model, and so is a superset of the hierarchical model.

Relational Model: Because of lack of linkages relational model is easier to understand and implement.

Relational DBMS

The relational model presents an orderly, predictable and intuitive approach to organizing, manipulating and viewing data.

RDBMS Terminology:

Relational data consists of relations. A relation (or relational table) is a two dimensional table with special properties. A relational table consists of a set of named columns and an arbitrary number of rows. The columns are called as attributes and rows are called as tuples. Each attribute is associated with a domain. A domain is a set of values that may appear in one or more columns.

Properties of Relational Data Structures:

Relational tables have six properties, which must be satisfied for any table to be classified as relational. These are:
1) Entries of attributes are single valued: Entry in every row and column position in a table must be single valued. This means columns do not contain repeating groups.
2)Entries of attribute are of the same kind: Entries in a column must be of same kind. A column supposed to store salary of an employee should not store commission.
3)No two rows are identical: Each row should be unique. This uniqueness is ensured by the values in a specific set of columns called the primary key.
4) The order of attributes is unimportant: There is no significance attached to order in which columns are stored in the table. A user can retrieve columns in any order.
5) The order of rows is unimportant: There is no significance attached to the order in which rows are stored in the table. A user can retrieve rows in any order.
6) Every column can be uniquely identified: Each column is identified by its name and not its position. A column name should be unique in the table.

Data Integrity

Integrity refers to the wholeness and soundness of the database. Some of the most important integrities are discussed below.

Domain Constraints: A domain is as set of values that are permitted to appear in one or more columns. Once a domain is specified and a column is associated with the domain, the column can take only those values permitted by the domain.

Primary Key and Entity Integrity: Primary key is a column or set of columns in a table which uniquely identifies a row in a table. No two rows of the table can have the same values for the primary key. Entity integrity is maintained by ensuring that none of the columns that make up the primary key can take “Null” (unknown) values.

Foreign keys and Referential Integrity: Consider two tables viz: Employee and Dept. Let dept no be a field in Dept table. If in Employee table, dept no field is taken as foreign key from Dept table, then the Employee table cannot contain a value for the deptno, which does not exist in the table Dept.

The referential integrity rule specifies that if a foreign key in table A refers to the primary key in table B, then every value of the foreign key in table A must be null or must be available in table B.

Delete Restrict referential integrity: This means that no foreign key can be deleted if there are some foreign key values dependent on it.

Delete Cascade referential integrity: This means that if the primary key value is deleted then all foreign key values dependent on it will be deleted.

Update Cascade referential integrity: This means that if the primary key value is updated then all foreign key values dependent on it will be updated to the new value of primary key.

Column Constraint: These are the constraints, which specify restrictions on the values a column can take. These restrictions may be defined with or without other values in the same row.