-- A 2002.nov.16-i geptermi ZH B csoport feladatainak megoldasai
-- A tobbi csoport megoldasa apro modositasokkal megkaphato


-- 1. feladat
-- Melyik adatbazisbeli tablanak van a legtobb CHAR oszlopa?

select owner, table_name, count(*) 
from dba_tab_columns where data_type='CHAR'
group by owner, table_name 
order by count(*) desc;

-- 2. feladat
-- Milyen indexei vannak az ORAUSER.TABLA2-nek?

select index_name, column_name, column_position 
from dba_ind_columns 
where table_name='TABLA2' and table_owner='ORAUSER';

-- 3. feladat
-- Melyik a legnagyobb tabla a pontosan 4 (ill. 5) CHAR oszloppal rendelkezok kozul?

select owner, segment_name, bytes 
from dba_segments 
where segment_type='TABLE'
and partition_name is null and (owner, segment_name) in 
    ( select owner, table_name 
      from dba_tab_columns 
      where data_type='CHAR' 
      group by owner, table_name 
      having count(*) = 4 ) 
order by bytes desc;

-- 4. feladat
-- Melyik adatfajlra a legnagyobb a szabad hely/fajl meret arany?

select d.file_id, d.file_name, free.b/d.bytes arany
from (select file_id, sum(bytes) b
      from dba_free_space group by file_id) free, dba_data_files d
where d.file_id = free.file_id
order by arany desc;

-- masik megoldas

select f.file_id, d.file_name, sum(f.bytes)/d.bytes
from dba_free_space f, dba_data_files d
where f.file_id = d.file_id
group by f.file_id, d.file_name, d.bytes
order by 3 desc;

-- 5. feladat
-- Mennyi helyet foglalnak a TOMX-en az ORAUSER clusterei?

select sum(bytes) 
from sys.dba_segments@tomx 
where owner='ORAUSER' and segment_type='CLUSTER';

-- 6. feladat
-- Milyen constraintek vannak az ORAUSER.TABLA3 es ORAUSER.TABLA4-en?

select con.table_name, con.constraint_name, constraint_type,
       column_name, position 
from dba_constraints con, dba_cons_columns col 
where con.owner='ORAUSER' and con.owner = col.owner
and con.constraint_name = col.constraint_name
and (col.table_name='TABLA3'or col.table_name='TABLA4')
order by 1, 2, 4;

-- 7. feladat
-- Adjuk meg az 1971-es ev n-edik penteket (n=vezetekneved hossza).

select next_day(to_date('1971-01-01','YYYY-MM-DD')-1, 'FRIDAY')+5*7 from dual;

-- 8. feladat
-- ORAUSER.EMP alapjan JONES beosztottainak osszfizetese

select sum(sal) 
from orauser.emp 
where level > 1 
start with ename = 'JONES' 
connect by prior empno = mgr;

-- 9. feladat
-- Milyen rendszerjogokat kap a 'GRANT jogok2 TO ...' utan az adomanyozott? 

select privilege from dba_sys_privs where grantee='JOGOK2'
  union
select privilege from dba_sys_privs where grantee in
       ( select granted_role 
         from dba_role_privs 
         where grantee='JOGOK2' );

-- 10. feladat
-- ORAUSER mely tablai vannak rajta a CLUST2 clusteren?

select table_name, tab_column_name, clu_column_name
from dba_clu_columns
where owner = 'ORAUSER' and cluster_name = 'CLUST2'
order by 1, 3;
