db2_practice1.txt

Give the SELECT statements and the results for the following queries.
Parenthesis shows the columns of the required result.

Database objects
----------------
(DBA_OBJECTS)

1.
Who is the owner of the view DBA_TABLES? Who is the owner of table DUAL? (owner)
2.
Who is the owner of synonym DBA_TABLES? (or synonym DUAL) (owner)
3.
What kind of objects the database user ORAUSER has? (dba_objects.object_type column)
4.
What are the object types existing in the database? (object_type) 
5.
Which users have more than 10 different kind of objects in the database? (owner)
6.
Which users have both triggers and views in the database? (owner)
7.
Which users have views but don't have triggers? (owner)
8.
Which users have more than 40 tables, but less than 30 indexes? (owner)
9.
Let's see the difference between a table and a view (dba_objects.data_object_id).
10.
Which object types have NULL (or 0) in the column data_object_id? (object_type)
11.
Which object types have non NULL (and non 0) in the column data_object_id? (object_type)
12.
What is the intersection of the previous 2 queries? (object_type)



Columns of a table
------------------
(DBA_TAB_COLUMNS)

13.
How many columns nikovits.emp table has? (num)
14.
What is the data type of the 6th column of the table nikovits.emp? (data_type)
15.
Give the owner and name of the tables which have column name beginning with letter 'Z'.
(owner, table_name)
16.
Give the owner and name of the tables which have at least 8 columns with data type DATE.
(owner, table_name)
17.
Give the owner and name of the tables whose 1st and 4th column's datatype is VARCHAR2.
(owner, table_name)

18.
Write a PL/SQL procedure, which prints out the owners and names of the tables beginning with the 
parameter character string. 
CREATE OR REPLACE PROCEDURE table_print(p_char VARCHAR2) IS
...
set serveroutput on
execute table_print('V');

==============================================================================
Example:
Run the following SQL and PL/SQL statements and examine the rows of the table.
==============================================================================
CREATE TABLE test1(col1 INTEGER PRIMARY KEY, col2 VARCHAR2(20));

CREATE SEQUENCE seq1 
MINVALUE 1 MAXVALUE 100 INCREMENT BY 5 START WITH 50 CYCLE;

CREATE OR REPLACE TRIGGER test1_bir -- before insert row
BEFORE INSERT ON test1 
FOR EACH ROW 
WHEN (new.col1 is null) 
BEGIN
  :new.col1 := seq1.nextval;
END;
/
BEGIN
 FOR i IN 1..14 LOOP 
  INSERT INTO test1 VALUES(null, 'trigger'||to_char(i,'FM09'));
 END LOOP;
 INSERT INTO test1 VALUES(seq1.currval + 1, 'sequence + 1'); 
 COMMIT;
END;
/
SELECT * FROM test1 ORDER BY col2;

DROP TABLE test1;    -- trigger will be dropped too
DROP sequence seq1;  -- sequence is not bound to the table