Andre Long
								CISA 320 (SQL)
								October 12, 2004
								Chapter 5 homework


1a)
insert into l_departments
values ('DBA', 'DATABASE ADMINISTRATOR');

insert into l_employees
values ( 950, 'ANDRE', 'LONG', 'DBA' ,'10-OCT-2004', 25, NULL, 201);

select* from l_departments;
select* from l_employees;

1b)
insert into l_suppliers
values ('CFF', 'CATHY''S FRESH FISH');

insert into l_foods
values ('CFF', 'SA', 11, 'FRESH SALMON', 8.25, 0);

select* from l_suppliers;
select* from l_foods;

2a)
insert into ex0402a
values (952, 'BOB', 'MILLER', 'SAL');

select* from l_employees;

2b)
update ex0402a
set  DEPARTMENT_CODE = 'MKT'
where EMPLOYEE_ID = 952;

select* from l_employees;


2c)
update ex0402a
set DEPARTMENT_CODE = 'ACT'
where EMPLOYEE_ID = 952;

select* from l_employees;

2d)
rollback;


3a)
select table_name
from user_tables
where table_name = 'ANDRE' 
or  table_name = 'RED';

3b)
select view_name
from user_views
where view_name ='EX0402A'
or        view_name ='BIG_RED'
or        view_name ='ANDREQUIZ4'
or        view_name ='BOBBY';

3c)
describe ANDRE;
describe EX0402a;

         OR

select table_name, column_name, column_id
from user_tab_columns
where table_name = 'ANDRE'
order by column_id;


[ no one knows how to do for views]
select table_name, column_name, column_id
from user_tab_columns
where table_name = 'EX0402a'
order by column_id;


4a)
select table_name, constraint_type, constraint_name
from   user_constraints
where table_name = 'L_EMPLOYEES';

select*
from user_cons_columns
where table_name = 'L_EMPLOYEES';

4b)
select table_name, constraint_type, constraint_name
from   user_constraints
where table_name = 'L_DEPARTMENTS';

select*
from user_cons_columns
where table_name = 'L_DEPARTMENTS';

[ primary key = dept_code ]


4c)
select table_name, constraint_type, constraint_name
from   user_constraints
where table_name = 'ANDRE';

select*
from user_cons_columns
where table_name = 'ANDRE';

[ NO PRIMARY KEY ]
[ I never made a table ex0401a but made table ANDRE instead ]

4d)
select table_name, constraint_type, constraint_name
from   user_constraints
where table_name = 'L_EMPLOYEES';

select*
from user_cons_columns
where table_name = 'L_EMPLOYEES';

Conclusion=> view ex0402a is based on table l_employees so it has the same constraints as underlying table