Solutions

Español

T06.001- Create a table with name "xx" with 2 columns, col1 with integer type and col2 with char(3) type, and define col1 as primary key.

create table XX (col1 integer, col2 char(3), primary key (col1))

T06.002- Select the table

select * from xx

T06.003- Insert in the table the row (1,'AA')

insert into xx values (1,'AA')

T06.004- Insert in the table the row ('BB',2)

insert into xx values ('BB',2)

T06.005- Insert in the table the row (2,'BB')

insert into xx values (2,'BB')

T06.006- Select the table XX

select * from xx

T06.007- Close the session and identify again ("close and sign in again" or "disconnect" and "connect"). Next select again XX. Actually, if you have this clear it is not necessary for you to do it. We pretend to check the persistency of the created table and the data it contains: closing the session do not delete the stored data. Only drop table can eliminate the table from the catalog.

select * from xx

T06.008- Delete the table XX

drop table xx

T06.009- Create a table YY with 3 columns
col1(integer),
col2(char(2)) and
col3(varchar(10)),
and primary key (col1, col2)

create table yy (
col1 integer,
col2 char(2),
col3 varchar(10),
primary key (col1, col2))

T06.010- Insert the following data and query the table in order to see the stored data
(1,'AA','first')
(2,'AA','second')
(2,'BB','third')
(1,'AA','fourth')
(NULL,NULL,'fifth')
(NULL,'CC','sixth')
(3,NULL,'seventh')
(0,'','eighth') --0, empty string, 'eighth'
(3,'AA',NULL)

insert into yy values (1,'AA','primera');
insert into yy values (2,'AA','segunda');
insert into yy values (2,'BB','tercera');
insert into yy values (1,'AA','cuarta');
insert into yy values (NULL,NULL,'quinta');
insert into yy values (NULL,'CC','sexta');
insert into yy values (3,NULL,'séptima');
insert into yy values (0,'','octava');
insert into yy values (3,'AA',NULL);

T06.011- Execute the following:
create table T1(a int,b int,c int,
primary key(a));

create table T2(a int,d int,e int,
primary key(d),foreign key(a) references T1(a));
and check, looking for the reason it fails, the result of the following statements:
a)insert in T1(1,10,100)
b)insert in T1(NULL,20,NULL)
c)insert in T1(2,20,NULL)
d)insert in T1(3,NULL,300)
e)insert in T2(2,NULL,NULL)
f) Insert in T2(2,20,NULL)
g) Insert in T1(1,20,200)
h) Insert in T2(4,10,100)
i) Insert in T2(2,30,230)

insert into T1 values (1,10,100); -- a
insert into T1 values (NULL,20,NULL); -- b fail
insert into T1 values (2,20,NULL); -- c
insert into T1 values (3,NULL,300); -- d
insert into T2 values (2,NULL,NULL); -- e fail
insert into T2 values (2,20,NULL); -- f
insert into T1 values (1,20,200); -- g fail
insert into T2 values (4,10,100); -- h fail
insert into T2 values (2,30,230);-- i

T06.012- Continue the previous
j) update T1(1,10,10) to (2,10,100)
k) update T1(1,1,100) to (5,10,100)
l) update T2(2,2,NULL) to (2,20,220)
m) update T2(2,2,220) to (5,20,220)
n) update T2(5,0,220) to (2,10,100)
o) update T1(2,2,200) to (6,60,600)
p)update T1(3,NULL,300) to (7,70,700)
q) update T2(2,10,00) to (7,10,100)
r) update T2(2,3,230) to (7,30,230)
s) update T1(2,0,NULL) to (6,60,600)

update T1 set a=2 where a=1; -- j falla
update T1 set a=5 where a=1; -- k
update T2 set e=220 where d=20; -- l
update T2 set a=5 where d=20; -- m
update T2 set a=2,d=10,e=100 where d=20; -- n
update T1 set a=6,b=60,c=600 where a=2; -- o falla
update T1 set a=7,b=70,c=700 where a=3; -- p
update T2 set a=7 where d=10; -- q
update T2 set a=7 where d=30; -- r
update T1 set a=6,b=60,c=600 where a=2; -- s

T06.013- Continue the previous
t) delete T2(7,30,230)
u) delete T1(7,70,700)
v) delete T1(5,10,100)
w) delete T2(7,10,100)
x) delete T1(7,70,700)
y) delete T1(6,60,600)

delete from T2 where d=30; -- t
delete from T1 where a=7; -- u falla
delete from T1 where a=5; -- v 
delete from T2 where d=10; -- w
delete from T1 where a=7; -- x ahora sí
delete from T1 where a=6; -- y