Acitividad 1

Código realizado en clase

mysql> create table autor
    -> (cod_A char(10) not null primary key,
    -> nombre_A char(40) not null) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> crate table lib_aut
    -> (cod_L char(10) not null,
    -> cod_A char(10) not null,
    -> foreign key(cod_A) references libro(cod_L) on delete cascade on updat e c
ascade,
    -> cod_A char(10) not null;
mysql> show tables;
+----------------------+
| Tables_in_biblioteca |
+----------------------+
| autor                |
| libro                |
+----------------------+
2 rows in set (0.02 sec)

mysql> create table lib_aut
    -> (cod_L char(10) not null,
    -> cod_A char(10) not null,
    -> foreign key(cod_L) references libro(cod_L) on delete cascade on update ca
scade,
    -> foreign key(cod_A) references autor(cod_A) on delete cascade on update ca
scade) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+----------------------+
| Tables_in_biblioteca |
+----------------------+
| autor                |
| lib_aut              |
| libro                |
+----------------------+
3 rows in set (0.00 sec)

mysql> describe autor;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| cod_A    | char(10) | NO   | PRI | NULL    |       |
| nombre_A | char(40) | NO   |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> describe libro;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| cod_L  | char(10) | NO   | PRI | NULL    |       |
| nombre | char(40) | NO   |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> describe lib_aut;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| cod_L | char(10) | NO   | MUL | NULL    |       |
| cod_A | char(10) | NO   | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> show create table autor;
+-------+-----------------------------------------------------------------------
-----------------------------------------------------------------------------+
| Table | Create Table
                                                                             |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------------------------------+
| autor | CREATE TABLE `autor` (
  `cod_A` char(10) NOT NULL,
  `nombre_A` char(40) NOT NULL,
  PRIMARY KEY (`cod_A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into libro (cod_L,nombre) values('00001','Java');
Query OK, 1 row affected (0.05 sec)

mysql> insert into libro values('00002','BD'),('00003','Auditoria');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * form libro;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'form
libro' at line 1
mysql> select * from libro;
+-------+-----------+
| cod_L | nombre    |
+-------+-----------+
| 00001 | Java      |
| 00002 | BD        |
| 00003 | Auditoria |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> insert into autor values('00001','Carlos carrasquilla'),('00002','Jaime s
aldarriaga'),('00003','david perez');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from autor;
+-------+---------------------+
| cod_A | nombre_A            |
+-------+---------------------+
| 00001 | Carlos carrasquilla |
| 00002 | Jaime saldarriaga   |
| 00003 | david perez         |
+-------+---------------------+
3 rows in set (0.00 sec)

mysql> select *  from libro where cod_L=00001;
+-------+--------+
| cod_L | nombre |
+-------+--------+
| 00001 | Java   |
+-------+--------+
1 row in set (0.02 sec)

mysql> select nombre from libro where cod_L=00002;
+--------+
| nombre |
+--------+
| BD     |
+--------+
1 row in set (0.00 sec)

mysql> select nombre_A from autor where cod_A=00003;
+-------------+
| nombre_A    |
+-------------+
| david perez |
+-------------+
1 row in set (0.00 sec)

mysql> -> (cod_lib char(10) not null,
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '-> (c
od_lib char(10) not null,' at line 1
mysql> insert into libro_aut values('111','00001','00002'),('222','00002','00001
');
ERROR 1146 (42S02): Table 'biblioteca.libro_aut' doesn't exist
mysql> insert into lib_aut values('111','00003','00002'),('222','00002','00003')
;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into lib_aut values('00003','00002'),('00002','00003'),('00002,'00
003);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '00003
)' at line 1
mysql> insert into lib_aut values('00003','00002'),('00002','00003'),('00002,'00
003');
    '> insert into lib_aut values('00003','00002'),('00002','00003'),('00002','0
0003');
    '> insert into lib_aut values('00003','00002'),('00002','00003'),('00002','0
0003');
    '> insert into lib_aut values('00003','00002'),('00002','00003'),('00002','0
0003');
    '> insert into lib_aut values('00003','00001');
    '> select * from lib_aut;
    '> '
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '00003
');
insert into lib_aut values('00003','00002'),('00002','00003'),('00002',' at line
 1
mysql> insert into lib_aut values('00003','00002'),('00002','00003'),('00002','0
0003');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from lib_aut;
+-------+-------+
| cod_L | cod_A |
+-------+-------+
| 00003 | 00002 |
| 00002 | 00003 |
| 00002 | 00003 |
+-------+-------+
3 rows in set (0.00 sec)
mysql> select * from libro inner join lib_aut on libro.cod_L=lib_aut.cod_L inner
 join autor on lib_aut.cod_A=autor.cod_A;
+-------+-----------+-------+-------+-------+-------------------+
| cod_L | nombre    | cod_L | cod_A | cod_A | nombre_A          |
+-------+-----------+-------+-------+-------+-------------------+
| 00003 | Auditoria | 00003 | 00002 | 00002 | Jaime saldarriaga |
| 00002 | BD        | 00002 | 00003 | 00003 | david perez       |
| 00002 | BD        | 00002 | 00003 | 00003 | david perez       |
+-------+-----------+-------+-------+-------+-------------------+
3 rows in set (0.00 sec)