Ejercicio para Bases de datos

bueno como lo prometido es deuda, cuelgo el resultado entero del boletin, desde el principio al final :

BOLETÍN 6

CONSULTAS BÁSICAS

1.- Diseña la base de datos para una librería que desea mantener la información de cada tema (cadena de caracteres de longitud fija 15 única y no nula), el estante en que se ubica (1 carácter) y el número de ejemplares de que dispone (valor numérico longitud 2).

Introduce datos de forma aleatoria, teniendo en cuenta que los temas encajan dentro de la siguiente lista: HISTORIA, FICCIÓN, MEDICINA, INFORMATICA, ARTE, RELIGION. Y que el estante es una letra del abecedario.

SENTENCIAS DE CREACIÓN E INTRODUCCIÓN DE DATOS (LIBRERIA):

Código

# Dumping DATABASE STRUCTURE FOR LIBRERIA
DROP DATABASE IF EXISTS `LIBRERIA`;
CREATE DATABASE IF NOT EXISTS `LIBRERIA` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `LIBRERIA`;
 
# Dumping STRUCTURE FOR TABLE LIBRERIA.LIBROS
DROP TABLE IF EXISTS `LIBROS`;
CREATE TABLE IF NOT EXISTS `LIBROS` (
  `TEMA` VARCHAR(15) NOT NULL,
  `ESTANTE` CHAR(1) DEFAULT NULL,
  `EJEMPLARES` SMALLINT(2) DEFAULT NULL,
  PRIMARY KEY (`TEMA`),
  UNIQUE KEY `TEMA` (`TEMA`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
# Dumping DATA FOR TABLE LIBRERIA.LIBROS: 6 ROWS
DELETE FROM `LIBROS`;
/*!40000 ALTER TABLE `LIBROS` DISABLE KEYS */;
INSERT INTO `LIBROS` (`TEMA`, `ESTANTE`, `EJEMPLARES`) VALUES
	('HISTORIA', 'B', 8),
	('FICCION', 'A', 20),
	('MEDICINA', 'F', 12),
	('INFORMATICA', 'E', 56),
	('ARTE', 'D', 80),
	('RELIGION', 'C', 15);
/*!40000 ALTER TABLE `LIBROS` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Realiza las siguientes consultas:

a) Obtener todos los datos cuyo tema sea “FICCION” usando “=” y posteriormente “LIKE”. ¿Es el resultado el mismo? ¿Y si modificaras la tabla asignando longitud variable al campo tema?

Código

SELECT * FROM LIBROS WHERE TEMA LIKE 'FICCION'
SELECT * FROM LIBROS WHERE TEMA='FICCION'

- El resultado es el mismo

b) Obtener el tema, estante y ejemplares de las filas con ejemplares comprendidos entre 8 y 15.

Código

SELECT TEMA,ESTANTE,EJEMPLARES FROM LIBROS WHERE EJEMPLARES BETWEEN  8 AND 15

c) Obtener las columnas tema, estante y ejemplares de las filas cuyo estante no esté comprendido entre 'B' y 'D'.

Código

SELECT TEMA,ESTANTE,EJEMPLARES FROM LIBROS WHERE ESTANTE NOT BETWEEN  'B' AND 'D'

d) Obtener con una única sentencia SELECT todos los temas de la librería cuyo número de ejemplares sea inferior a los que hay en 'MEDICINA'.

Código

SELECT TEMA FROM LIBROS WHERE EJEMPLARES<(SELECT EJEMPLARES FROM LIBROS WHERE TEMA='MEDICINA')

e) Obtener los temas de la librería cuyo número de ejemplares no esté entre 15 y 20, ambos incluid

Código

SELECT TEMA FROM LIBROS WHERE EJEMPLARES NOT BETWEEN 15 AND 20

2.- En un colegio se desea almacenar la información de los alumnos (DNI [10] no nulo y único, Apellidos_Nombre [30], Dirección [30], Población [15] y Teléfono [10], todos cadenas de caracteres de longitud variable), las asignaturas que cursan (Código como valor numérico de 2 cifras no nulo y único; y Nombre, cadena de caracteres de longitud variable 20) y las notas que obtienen dichos alumnos en las diferentes asignaturas que cursen (el valor de la nota será un número de 2 cifras).

SENTENCIAS DE CREACIÓN E INTRODUCCIÓN DE DATOS (COLEGIO):

Código

DROP DATABASE IF EXISTS `COLEGIO`;
CREATE DATABASE IF NOT EXISTS `COLEGIO` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `COLEGIO`;
 
 
# Dumping STRUCTURE FOR TABLE COLEGIO.ALUMNOS
DROP TABLE IF EXISTS `ALUMNOS`;
CREATE TABLE IF NOT EXISTS `ALUMNOS` (
  `DNI` VARCHAR(10) NOT NULL,
  `APELLIDOS_NOMBRE` VARCHAR(30) DEFAULT NULL,
  `DIRECCION` VARCHAR(30) DEFAULT NULL,
  `POBLACION` VARCHAR(15) DEFAULT NULL,
  `TELEFONO` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`DNI`),
  UNIQUE KEY `DNI` (`DNI`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
# Dumping DATA FOR TABLE COLEGIO.ALUMNOS: ~7 ROWS (approximately)
DELETE FROM `ALUMNOS`;
/*!40000 ALTER TABLE `ALUMNOS` DISABLE KEYS */;
INSERT INTO `ALUMNOS` (`DNI`, `APELLIDOS_NOMBRE`, `DIRECCION`, `POBLACION`, `TELEFONO`) VALUES
	('25889878C', 'Martienz_Pepe_Lolo', 'Calle_Juas', 'SEVILLA', '987755465'),
	('45665448K', 'Rouco_Varela_Nolo', 'Calle_Lamer', 'CORUÑA', '686987785'),
	('45665884R', 'Del_Pan_Pepe', 'Calle_Roja', 'VIGO', '623154687'),
	('45687998X', 'Guitierrez_Gomez_Elias', 'Calle_Nova', 'SANTIAGO', '614658779'),
	('53259877Q', 'Diaz_Fernandez_Maria', 'Calle_Azul', 'MADRID', '684455664'),
	('67897546S', 'Rodriguez_Alonso_Juan', 'Calle_Verde', 'BARCELONA', '651154465'),
	('68798545B ', 'Fernandez_Maria_Justo', 'Calle_Madrid', 'MADRID', '987756654');
/*!40000 ALTER TABLE `ALUMNOS` ENABLE KEYS */;
 
 
# Dumping STRUCTURE FOR TABLE COLEGIO.ASIGNATURAS
DROP TABLE IF EXISTS `ASIGNATURAS`;
CREATE TABLE IF NOT EXISTS `ASIGNATURAS` (
  `COD_AS` tinyint(2) NOT NULL,
  `NOMBRE` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`COD_AS`),
  UNIQUE KEY `COD_AS` (`COD_AS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
# Dumping DATA FOR TABLE COLEGIO.ASIGNATURAS: ~5 ROWS (approximately)
DELETE FROM `ASIGNATURAS`;
/*!40000 ALTER TABLE `ASIGNATURAS` DISABLE KEYS */;
INSERT INTO `ASIGNATURAS` (`COD_AS`, `NOMBRE`) VALUES
	(1, 'FOL'),
	(2, 'ORTODONCISMO'),
	(3, 'LM'),
	(4, 'PRG'),
	(5, 'ED');
/*!40000 ALTER TABLE `ASIGNATURAS` ENABLE KEYS */;
 
 
# Dumping STRUCTURE FOR TABLE COLEGIO.CURSAN
DROP TABLE IF EXISTS `CURSAN`;
CREATE TABLE IF NOT EXISTS `CURSAN` (
  `DNI` VARCHAR(10) NOT NULL,
  `COD_AS` tinyint(2) NOT NULL DEFAULT '0',
  `NOTA` INT(2) DEFAULT NULL,
  PRIMARY KEY (`DNI`,`COD_AS`),
  KEY `FK_CURSAN_ASIGNATURAS` (`COD_AS`),
  CONSTRAINT `FK_CURSAN_ALUMNOS` FOREIGN KEY (`DNI`) REFERENCES `ALUMNOS` (`DNI`),
  CONSTRAINT `FK_CURSAN_ASIGNATURAS` FOREIGN KEY (`COD_AS`) REFERENCES `ASIGNATURAS` (`COD_AS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
# Dumping DATA FOR TABLE COLEGIO.CURSAN: ~9 ROWS (approximately)
DELETE FROM `CURSAN`;
/*!40000 ALTER TABLE `CURSAN` DISABLE KEYS */;
INSERT INTO `CURSAN` (`DNI`, `COD_AS`, `NOTA`) VALUES
	('25889878C', 5, 6),
	('45665884R', 1, 8),
	('45665884R', 2, 8),
	('45687998X', 1, 7),
	('53259877Q', 1, 6),
	('53259877Q', 2, 6),
	('67897546S', 3, 10),
	('68798545B ', 2, 2),
	('68798545B ', 5, 4);

Diseña el modelo E/R, a continuación transfórmalo a Grafo Relacional, para posteriormente implementar la base de datos y sus consecuentes tablas, campos y tipos de datos.

Introduce datos aleatorios para poder realizar las consultas detalladas más abajo.

Realiza a continuación las siguientes consultas:

a) Obtener el nombre y apellidos de cada alumno, asignatura que cursa y nota obtenida en dicha asignatura.

Código

SELECT APELLIDOS_NOMBRE,NOMBRE,NOTA FROM ALUMNOS,ASIGNATURAS,CURSAN WHERE ALUMNOS.DNI=CURSAN.DNI AND  ASIGNATURAS.COD_AS=CURSAN.COD_AS

b) Obtener los nombres y apellidos de los alumnos que han cursado 'FOL'.

Código

SELECT APELLIDOS_NOMBRE FROM ALUMNOS,CURSAN,ASIGNATURAS WHERE ALUMNOS.DNI=CURSAN.DNI AND CURSAN.COD_AS=ASIGNATURAS.COD_AS AND ASIGNATURAS.NOMBRE = "FOL"

c) Obtener los nombres y apellidos de los alumnos que tengan una nota entre 7 y 8 en 'FOL'.

Código

SELECT APELLIDOS_NOMBRE FROM ALUMNOS,CURSAN,ASIGNATURAS WHERE ALUMNOS.DNI=CURSAN.DNI AND CURSAN.COD_AS=ASIGNATURAS.COD_AS AND ASIGNATURAS.NOMBRE = "FOL" AND CURSAN.NOTA BETWEEN 7 AND 8

d) Obtener los nombres y apellidos de los alumnos que no tengan nada suspenso.

Código

SELECT APELLIDOS_NOMBRE FROM CURSAN,ALUMNOS,ASIGNATURAS WHERE CURSAN.NOTA BETWEEN 5 AND 10 AND ASIGNATURAS.COD_AS=CURSAN.COD_AS AND ALUMNOS.DNI=CURSAN.DNI

e) Obtener todas las asignaturas que contengan 2 letras 'o' en su interior y tengan alumnos matriculados de 'Madrid'.

Código

SELECT NOMBRE FROM ASIGNATURAS,CURSAN,ALUMNOS WHERE NOMBRE LIKE '%o%o%' AND ALUMNOS.DNI=CURSAN.DNI AND CURSAN.COD_AS=ASIGNATURAS.COD_AS AND POBLACION='MADRID'

f) Obtener los nombres y apellidos de los alumnos de 'Madrid' que tengan alguna asignatura suspensa.

Código

SELECT APELLIDOS_NOMBRE FROM ALUMNOS,CURSAN,ASIGNATURAS WHERE POBLACION='MADRID' AND CURSAN.NOTA<5 AND ALUMNOS.DNI=CURSAN.DNI AND CURSAN.COD_AS=ASIGNATURAS.COD_AS

g) Obtener los nombres y apellidos de alumnos que tengan la misma nota que tiene 'Díaz Fernández, María' en 'FOL' en alguna asignatura.

Código

SELECT APELLIDOS_NOMBRE FROM ALUMNOS,CURSAN,ASIGNATURAS WHERE CURSAN.NOTA=(SELECT NOTA FROM ALUMNOS,ASIGNATURAS,CURSAN WHERE ALUMNOS.APELLIDOS_NOMBRE ='DIAZ_FERNANDEZ_MARIA' AND ASIGNATURAS.NOMBRE='FOL' AND CURSAN.DNI=ALUMNOS.DNI AND ASIGNATURAS.COD_AS=CURSAN.COD_AS)
AND CURSAN.DNI=ALUMNOS.DNI AND ASIGNATURAS.COD_AS=CURSAN.COD_AS

* En esta consulta devuelve a DIAZ_FERNANNDEZ_MARIA pero como la consulta no especifica si debe aparecer o no lo dejo así, de todas formas solo habría que añadir al final AND APELLIDOS_NOMBRE !='DIAZ_FERNANDEZ_MARIA'

h) Obtener los datos de las asignaturas que no tengan alumnos.

Código

SELECT CURSAN.COD_AS FROM CURSAN,ASIGNATURAS,ALUMNOS WHERE CURSAN.COD_AS=ASIGNATURAS.COD_AS AND ALUMNOS.DNI = CURSAN.DNI  AND NOT EXISTS (SELECT CURSAN.COD_AS FROM CURSAN,ASIGNATURAS WHERE CURSAN.COD_AS=ASIGNATURAS.COD_AS)

* Hice lo que pude

i) Obtener los nombres y apellidos de los alumnos que tengan nota en la asignatura con código 1.

Código

SELECT APELLIDOS_NOMBRE FROM ASIGNATURAS,ALUMNOS,CURSAN WHERE CURSAN.DNI=ALUMNOS.DNI AND CURSAN.COD_AS = ASIGNATURAS.COD_AS AND CURSAN.COD_AS=1

j) Obtener los nombres y apellidos de los alumnos que no tengan nota en la asignatura con código 3.

Código

SELECT APELLIDOS_NOMBRE FROM ASIGNATURAS,ALUMNOS,CURSAN WHERE CURSAN.DNI=ALUMNOS.DNI AND CURSAN.COD_AS = ASIGNATURAS.COD_AS AND CURSAN.COD_AS!=3