Tutorial 4

Contents

• VIEWS and Miscellany
  • VIEWS
  • Removal of a VIEW
  • differences between sqlplus and activeSQL
  • Outer Join
  • Arithmetic operation on dates etc.
  • NVL function

VIEWS and Miscellany

VIEWS

CREATE VIEW viewname AS defines a virtual table. A query appears after the AS statement, and the result of executing this query appears as a new table called viewname. However, the data resulting from executing the AS statement is not stored directly in the database. Only the view definition is stored.

Each time a view table is used in an SQL statement, the statement operates on the view's base tables to generate the required view content. Views are therefore dynamic and their contents change automatically as base tables change.

Views can be usefully employed for intermediate tables, and may replace subenquires in order to simplify complex queries

All SELECTs on views are fully supported. Updates, inserts and deletes on views are, however, subject to several rules. Although in this tutorial we make no attempt to update or modify tables, it is important to realise what these modification rules are.

View modifications are not allowed if

• View was created from more than one table.
• View was created from a non-updatable view.
• Any column in the view is derived or is an aggregate function.

• Any column in the base table was declared as NOT NULL is not present in the view.

Create a view that contains each employees' surname, salary and department name.

CREATE VIEW empdepsal(ename, sal, dept)
AS
SELECT e.surname, j.salary, d.dname
FROM employee e, jobhistory j, department d
WHERE e.empno = j.empno
AND e.depno = d.depno
AND enddate IS NULL

Removal of a VIEW

This is just the same syntax as dropping a TABLE.

DROP VIEW empdepsal

differences between sqlplus and activeSQL

There is one important difference between sqlplus and activeSQL. In sqlplus, you have your OWN oracle account, and you do not share this with anyone. When you create a view in Oracle is stays around until you explicitly delete it. Thus you can reuse a view for more than one purpose without having to redefine it.

In activeSQL, you share your namespace with all the other activeSQL users. ActiveSQL tries to make sure that this never causes interference involving the other users. However, one thing it does do is insist that your views are deleted immediately after they are used. If you run a query involving a view, activeSQL will delete that view automatically on your behalf before displaying the results of your query. Thus to use a view in two questions, you must create the view in EACH question. You will not lose marks for reusing a view in two or more questions by copying the view definition into the answer to all the questions. Try to come up with viewname which are likely to be different to your colleagues names - identical name for views are unlikely to cause any problems but different name will definitely NOT cause problems.

No matter what interface you use, it is good practice to delete a view once you are finished with it. Forgetting to delete the view yourself will cost you marks.

Outer Join

One problem which comes up frequently in advanced SQL is losing data in a query where some of the relationships involve NULL. For instance, lets say we want to list ALL empnos in the employee table against how many courses they have been on.

Initially you might simply say:

select   employee.empno,count(courseno)
from     employee,empcourse
where    employee.empno = empcourse.empno
group by employee.empno;

It looks very reasonable, but running the query produces:

So what happened to all the other empno entries? For instance there is an employee 3, but it does not appear in the table. As employee 3 has not been on any courses, empcourse.empno does not have the value 3, and thus that row of employee is ignored. Whats the solution? There are two possibilities, one using UNION and one (much nicer) solution using OUTER JOIN.

With UNION, we can join together two separate queries and make it appear like a single result table. We can use this to join two queries together, one which is the query above with all the employees with courses, and one query which is all the employees who never did courses. This second query must return the empno attribute, and also a count attribute with a value of 0 (these employees have done 0 courses). Actually, this is quite easy:

select   employee.empno,0
from     employee
where    employee.empno not in
         (select empno from empcourse)
group by employee.empno;

To join them together list both queries one after another with the word UNION between them. Thus:

select   employee.empno,count(courseno)
from     employee,empcourse
where    employee.empno = empcourse.empno
group by employee.empno
UNION
select   employee.empno,0
from     employee
where    employee.empno not in
         (select empno from empcourse)
group by employee.empno;

Magic!

Although this works well, it is rather complex and long. Another way is to use OUTER JOIN. This is the same as a normal join, except we warn Oracle that, if there is no value at one side of the join, just pretend there is one. Where we want to allow values to be missing the Oracle syntax as (+) after the attribute which can have no value.

In our case the problem is with

employee.empno = empcourse.empno

Here, empcourse.empno does not have all the values of employee.empno, and to make the query work we tell oracle to keep going even if there is no equivalent empcourse.empno value. Thus we change this line to:

employee.empno = empcourse.empno(+)

The whole query changes to :

select   employee.empno,count(courseno)
from     employee,empcourse
where    employee.empno = empcourse.empno(+)
group by employee.empno;

You could have also wrote:

  where empcourse.empno(+) = employee.empno

There are complex rules as to how many (+) symbols you can put to the left of an = sign, but in general you can put as many as you like on the right hand side. Trying to utilise more than one (+) in a single SQL statement is strictly for experts only. It is easy to get into a position where Oracle refuses to execute such queries!

As you can see, the is only a few characters longer than the original broken query, and thus is much less complex than the UNION solution. The problem people find with OUTER JOIN is knowing where to put the (+). If you are going to use OUTER JOIN do not just randomly put (+) in your query and then move it until it works. Try to have a logical approach to its placement, or you will be sorry!

Arithmetic operation on dates etc.

Oracle allows you to do simple arithmetic operations on dates.

SYSDATE returns the current date and time.

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants as numbers of days. For example, SYSDATE -7 is one week ago.

ADD_MONTHS(d, n) returns the date plus n months.

LAST_DAY(d) returns the last day of the month that contains the date d.

MONTHS_BETWEEN(d1,d2) returns the number of months between dates d1 and d2. If d1 is later than d2, the result is positive, if earlier it is negative. If d1 and d2 are the same days of the month or both the last days of the month the result is an integer otherwise there is also a fractional part.

To calculate the number of days left in a month.

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days left"
FROM DUAL;

Dual is a table automatically created by Oracle along with the data dictionary. It is accessible to all users. It has one column DUMMY and one row with the value X. Selecting from the dual table is useful for computing a constant expression with the SELECT command because dual has only one row the constant is returned only once.

How many days has each employee been in his or her current job?

SELECT empno, SYSDATE - startdate "No of days"
FROM jobhistory
WHERE enddate IS NULL;

This works out the number of days between the startdate and today's date. The heading of the column is No of days. However there is also a fractional part which is the fraction of the day.

ROUND(d) returns d rounded to the nearest day.

How many days has each employee been in his or her current job?

SELECT empno, ROUND(SYSDATE - startdate) "No of days"
FROM jobhistory
WHERE enddate IS NULL;

Rounds the result to the nearest day.

List how many months each employee has been in his or her current job?

SELECT empno, MONTHS_BETWEEN(SYSDATE , startdate) "No of months"
FROM jobhistory
WHERE enddate IS NULL;

NVL function

NVL returns the normal set function result unless that result is NULL, when it returns the second argument in the NVL function.

To list employees' positions with end dates if not null or else today's date :-

SELECT empno, NVL(enddate, SYSDATE)
FROM jobhistory