DBMS LAB ASSIGNMENT 4

Views

1. Define a view which will produce the following output when referenced in a query.

Run a query to test the view.

DEPTNO AVERAGE MAX MIN SUM NO-SALS NO-COMMS

------------- -------------- --------- ------- ------ ------------- --------------

10 2916.66667 5000 1300 8750 3 0

20 2175 3000 1800 10875 5 0

1. Using the view from question 1 , extract the following information. Employee number should be entered at run-time.

Enter value for no:7902

EMPNO ENAME JOB SAL HIREDATE MIN MAX AVERAGE

----------- ------------ ----- ----- -------------- ------- ------- ---------------

7902 FORD ANALYST 30000 5-DEC-83 800 3000 2175

1. Create a view to enforce the following restrictions when inserting data into the ASSIGNMENTS table.

3.1 Projid must be less than 2000.

3.2 End date must be after Start date.

3.3 Valid Assign types are PF,WT or ED.

3.4 Bill_rate is less than 50.00 for Assign_type PF, less than 60.00 for

Assign_type WT,less than 70.00 for assign_type ED.

3.5 EMPNO must be valid.

3.6 Remember WITH CHECK OPTION CLAUSE.

1. Insert values into ASSIGNMENTS using the view created. You may choose your own.

2. Query the data dictionary to see your view SELECT statement.

3. Create a non-unique index on the Projid column of the Assignmwent table.

4. Query the appropriate data dictionary table to display information about your indexes (USER-INDEXES).

5. Create a sequence as SEQUENCE1 with a start value of 1 and end value of 1000.

6. Create a sequence as SEQUENCE2 with a start value of 1 and no end value.

7. Create a block which declares four variables:

V_BOOL1 Boolean , V_BOOL2 Boolean , V_CHAR Character , V_NUM Number and

then assign values to these variables as follows:

V_num<100 , not v_bool1 , ’42 is the anwer’, substr(v_char,1,2);

1. Declare two variables and show the result as power of another, accept the two numbers from the keyboard at runtime.