Examen del segundo parcial de bases de datos.

1. Revisar que la información en todas las tablas este escrita correctamente, y de ser necesario, cambiarla para que quede consistente

2.Obtener el nombre de los profesores y las carreras en donde imparten clases, ordenados por carrera como especialidad y en orden alfabetico por nombre

3 . Obtener los alumnos que toman clas con M.C. Oscar Flores Conde

4. Obtener el maestro que da la materia de simulación, el edificio y el horario

5. Este es el script del SQL

— MySQL dump 10.13  Distrib 5.1.48, for Win32 (ia32)

— Host: localhost    Database: examen_2
— ——————————————————
— Server version    5.1.48-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


— Current Database: `examen_2`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `examen_2` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `examen_2`;


— Table structure for table `alumno`

DROP TABLE IF EXISTS `alumno`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alumno` (
`id_alumno` char(8) NOT NULL,
`nombre` varchar(30) NOT NULL,
`ap_paterno` varchar(30) NOT NULL,
`ap_materno` varchar(30) NOT NULL,
`id_carrera` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id_alumno`),
KEY `id_carrera` (`id_carrera`),
CONSTRAINT `alumno_ibfk_1` FOREIGN KEY (`id_carrera`) REFERENCES `carreras` (`id_carrera`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `alumno`

LOCK TABLES `alumno` WRITE;
/*!40000 ALTER TABLE `alumno` DISABLE KEYS */;
INSERT INTO `alumno` VALUES (‘06940242′,’Saul’,’Cruz’,’Cabrera’,2),(‘07940002′,’Roberto’,’Cervantes’,’Cirilo’,1),(‘07940022′,’Ivette’,’Aguilar’,’Jimarez’,1),(‘07940029′,’Sofia’,’Botello’,’Aguirre’,1),(‘07940032′,’Gustavo’,’Flores’,’Lopez’,3),(‘07940081′,’Ana Iliana’,’Cervantes’,’Paredes’,1),(‘07940124′,’Victor’,’Garcia’,’Parra’,3),(‘07940136′,’Ana Lilia’,’Conde’,’Arroyo’,2),(‘07940226′,’Carmen’,’Cobe’,’Bruno’,1),(‘07940230′,’Guadalupe’,’Gomez’,’Carrillo’,4);
/*!40000 ALTER TABLE `alumno` ENABLE KEYS */;
UNLOCK TABLES;


— Table structure for table `asignaturas`

DROP TABLE IF EXISTS `asignaturas`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `asignaturas` (
`id_asignatura` smallint(6) NOT NULL AUTO_INCREMENT,
`asignatura` varchar(40) NOT NULL,
`id_carrera` smallint(6) DEFAULT NULL,
`id_horario` tinyint(4) DEFAULT NULL,
`id_aula` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id_asignatura`),
KEY `id_carrera` (`id_carrera`),
KEY `id_horario` (`id_horario`),
KEY `id_aula` (`id_aula`),
CONSTRAINT `asignaturas_ibfk_1` FOREIGN KEY (`id_carrera`) REFERENCES `carreras` (`id_carrera`),
CONSTRAINT `asignaturas_ibfk_2` FOREIGN KEY (`id_horario`) REFERENCES `horario` (`id_horario`),
CONSTRAINT `asignaturas_ibfk_3` FOREIGN KEY (`id_aula`) REFERENCES `aula` (`id_aula`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `asignaturas`

LOCK TABLES `asignaturas` WRITE;
/*!40000 ALTER TABLE `asignaturas` DISABLE KEYS */;
INSERT INTO `asignaturas` VALUES (1,’Taller de bases de datos’,1,3,2),(2,’Taller de bases de datos’,1,4,1),(3,’Programacion de aplicaciones web’,1,7,8),(4,’proGRAMAcion_ de apliciones wed’,1,8,9),(5,’Cimulasion’,2,1,5);
/*!40000 ALTER TABLE `asignaturas` ENABLE KEYS */;
UNLOCK TABLES;


— Table structure for table `aula`

DROP TABLE IF EXISTS `aula`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `aula` (
`id_aula` tinyint(4) NOT NULL AUTO_INCREMENT,
`capacidad` tinyint(4) DEFAULT NULL,
`edificio` char(1) DEFAULT NULL,
PRIMARY KEY (`id_aula`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `aula`

LOCK TABLES `aula` WRITE;
/*!40000 ALTER TABLE `aula` DISABLE KEYS */;
INSERT INTO `aula` VALUES (1,30,’B’),(2,40,’B’),(3,25,’B’),(4,25,’I’),(5,30,’I’),(6,35,’I’),(7,32,’J’),(8,38,’J’),(9,30,’J’);
/*!40000 ALTER TABLE `aula` ENABLE KEYS */;
UNLOCK TABLES;


— Table structure for table `carreras`

DROP TABLE IF EXISTS `carreras`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `carreras` (
`id_carrera` smallint(6) NOT NULL AUTO_INCREMENT,
`carrera` varchar(40) NOT NULL,
PRIMARY KEY (`id_carrera`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `carreras`

LOCK TABLES `carreras` WRITE;
/*!40000 ALTER TABLE `carreras` DISABLE KEYS */;
INSERT INTO `carreras` VALUES (1,’Ing. Sistemas Computacionales’),(2,’Ing. Industrial’),(3,’Ing. Industrias Alimentarias’),(4,’Ing. Electromecanica’);
/*!40000 ALTER TABLE `carreras` ENABLE KEYS */;
UNLOCK TABLES;


— Table structure for table `horario`

DROP TABLE IF EXISTS `horario`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `horario` (
`id_horario` tinyint(4) NOT NULL AUTO_INCREMENT,
`horario_inicio` time DEFAULT NULL,
`horario_fin` time DEFAULT NULL,
PRIMARY KEY (`id_horario`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `horario`

LOCK TABLES `horario` WRITE;
/*!40000 ALTER TABLE `horario` DISABLE KEYS */;
INSERT INTO `horario` VALUES (1,’08:00:00′,’09:00:00′),(2,’09:00:00′,’10:00:00′),(3,’10:00:00′,’11:00:00′),(4,’11:00:00′,’12:00:00′),(5,’12:00:00′,’13:00:00′),(6,’13:00:00′,’14:00:00′),(7,’14:00:00′,’15:00:00′),(8,’15:00:00′,’16:00:00′);
/*!40000 ALTER TABLE `horario` ENABLE KEYS */;
UNLOCK TABLES;


— Table structure for table `listas`

DROP TABLE IF EXISTS `listas`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `listas` (
`id_alumno` char(8) DEFAULT NULL,
`id_profesor` char(8) DEFAULT NULL,
`id_asignatura` smallint(6) DEFAULT NULL,
KEY `id_alumno` (`id_alumno`),
KEY `id_profesor` (`id_profesor`),
KEY `id_asignatura` (`id_asignatura`),
CONSTRAINT `listas_ibfk_1` FOREIGN KEY (`id_alumno`) REFERENCES `alumno` (`id_alumno`),
CONSTRAINT `listas_ibfk_2` FOREIGN KEY (`id_profesor`) REFERENCES `profesores` (`id_profesor`),
CONSTRAINT `listas_ibfk_3` FOREIGN KEY (`id_asignatura`) REFERENCES `asignaturas` (`id_asignatura`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `listas`

LOCK TABLES `listas` WRITE;
/*!40000 ALTER TABLE `listas` DISABLE KEYS */;
INSERT INTO `listas` VALUES (‘07940022′,’1’,1),(‘07940029′,’1’,1),(‘07940002′,’1’,1),(‘07940081′,’1’,2),(‘07940226′,’1’,2),(‘07940136′,’2’,5),(‘06940242′,’2’,5),(‘07940032′,’2’,5),(‘07940124′,’4’,4),(‘07940230′,’4’,4);
/*!40000 ALTER TABLE `listas` ENABLE KEYS */;
UNLOCK TABLES;


— Table structure for table `profesores`

DROP TABLE IF EXISTS `profesores`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `profesores` (
`id_profesor` char(8) NOT NULL,
`nombre` varchar(40) NOT NULL,
`id_carrera` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id_profesor`),
KEY `id_carrera` (`id_carrera`),
CONSTRAINT `profesores_ibfk_1` FOREIGN KEY (`id_carrera`) REFERENCES `carreras` (`id_carrera`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Dumping data for table `profesores`

LOCK TABLES `profesores` WRITE;
/*!40000 ALTER TABLE `profesores` DISABLE KEYS */;
INSERT INTO `profesores` VALUES (‘1′,’M.C. Oscar Flores Conde’,1),(‘2′,’M.C. Oscar Flores Conde’,2),(‘3′,’Ing. Isabel Guerrero Lobato’,1),(‘4′,’M.C. Alejandro Gutierrez’,2);
/*!40000 ALTER TABLE `profesores` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

— Dump completed on 2010-10-07 10:12:33