Taller N° 3
TRABAJO DE BASE DE DATOS “CONSULTAS”
1. Visualizar los libros con las editoriales publicadas.
Análisis 1
Qué debo mostrar?
libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre
Que tablas se ven afectadas?
Libro,liautedi,editorial
Condiciones en particular (Lógicas)?
---
Como se relaciona una tabla con la otra?
libro.idlibro=liautedi.idlibro and
liautedi.codedit=editorial.codedit
2. Visualizar los libros que tienen un valor entre 85000 y 105000.
Análisis 2
Qué debo mostrar?
libro,precio
Que tablas se ven afectadas?
Libro,precio
Condiciones en particular (Lógicas)?
Tabla precio Where precio between '85000' and '105000';
Como se relaciona una tabla con la otra?
---
3. Visualizar los libros publicados por la editorial oveja negra
Análisis 3
Qué debo mostrar?
libro,editorial
Que tablas se ven afectadas?
Libro,editorial
Condiciones en particular (Lógicas)?
Tabla editorial Where editorial.nombre = 'oveja negra';
Como se relaciona una tabla con la otra?
libro.idlibro=liautedi.idlibro and
liautedi.codedit=editorial.codedit and
editorial.nombre = 'oveja negra';
4. Visualizar la información de las materias cálculo, diagramación o bases de datos.
Análisis 4
Qué debo mostrar?
Asignaturas
Que tablas se ven afectadas?
Asignatura
Condiciones en particular (Lógicas)?
Tabla asignatura where nombre in ('calculo','diagramacion','bases de datos');
Como se relaciona una tabla con la otra?
---
CODIGOS
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura |
| autor |
| editorial |
| liautedi |
| libro |
+--------------------+
5 rows in set (0.00 sec)
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe autor;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| codautor | char(25) | NO | PRI | NULL | |
| nombre | char(25) | NO | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe liautedi;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | MUL | NULL | |
| codautor | char(3) | NO | MUL | NULL | |
| codedit | char(3) | NO | MUL | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | MUL | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> describe editorial;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| nombre | char(15) | NO | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe editorial;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| nombre | char(15) | NO | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,autor.codautor,autor.nombre from libro,liautedi,autor where libro.idlibro=liautedi.idlibro and liautedi.codautor=autor.codautor;
+---------+----------------+----------+----------------------+
| idlibro | descripcion | codautor | nombre |
+---------+----------------+----------+----------------------+
| L02 | BD II | A01 | Luis Joyanes |
| L06 | Contabilidad I | A02 | Jorge Vasquez Posada |
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
| L02 | BD II | A05 | Robert Lorber |
| L07 | Redes | A05 | Robert Lorber |
+---------+----------------+----------+----------------------+
7 rows in set (0.00 sec)
mysql> describe editorial;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| nombre | char(15) | NO | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | MUL | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,liautedi,editorial where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit;
+---------+----------------+---------+--------------+
| idlibro | descripcion | codedit | nombre |
+---------+----------------+---------+--------------+
| L02 | BD II | E01 | Oveja Negra |
| L04 | Ingles | E01 | Oveja Negra |
| L06 | Contabilidad I | E02 | Norma |
| L04 | Ingles | E02 | Norma |
| L02 | BD II | E03 | Mc Graw Hill |
| L07 | Redes | E03 | Mc Graw Hill |
| L04 | Ingles | E03 | Mc Graw Hill |
+---------+----------------+---------+--------------+
7 rows in set (0.00 sec)
mysql> select * from libro where precio between '85000' and '105000';
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
+---------+---------------------+-----------+--------+-----------+
2 rows in set (0.03 sec)
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,liautedi,editorial where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.nombre = 'oveja negra';
+---------+-------------+---------+-------------+
| idlibro | descripcion | codedit | nombre |
+---------+-------------+---------+-------------+
| L02 | BD II | E01 | Oveja Negra |
| L04 | Ingles | E01 | Oveja Negra |
+---------+-------------+---------+-------------+
2 rows in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura |
| autor |
| editorial |
| liautedi |
| libro |
+--------------------+
5 rows in set (0.00 sec)
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> select * from asignatura where nombre in ('calculo','diagramacion','bases de datos');
+-----------+----------------+
| codigomat | nombre |
+-----------+----------------+
| M01 | Calculo |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+----------------+
3 rows in set (0.00 sec)