ebFG0UAL

1/************************ BASE DE DATOS DEL EXAMEN *********************************/
2DROP DATABASE IF EXISTS EXAMEN;
3CREATE DATABASE IF NOT EXISTS EXAMEN;
4USE EXAMEN;
5
6/*************************** CREACIÓN DE TABLAS *********************************/
7CREATE TABLE alumnos
8(
9	dnialumno VARCHAR(10) PRIMARY KEY,
10	nombre VARCHAR(20) NOT NULL,
11	direccion VARCHAR(20),
12	telefono VARCHAR(10),
13	CHECK (telefono like '95%')
14);
15
16CREATE TABLE empresas
17(
18	cifempresa VARCHAR(10) PRIMARY KEY,
19	nombre VARCHAR(20) UNIQUE,
20	direccion VARCHAR(20) NOT NULL,
21	responsablelegal VARCHAR(10),
22	sector VARCHAR(15)
23);
24
25CREATE TABLE practicas
26(
27	dnialumno VARCHAR(10),
28	cifempresa VARCHAR(10),
29	fechainicio DATE,
30	numhoras INT(4) DEFAULT 0,
31	PRIMARY KEY (dnialumno, cifempresa)
32);
33
34
35/*************************** INSERCIÓN DE REGISTROS *********************************/
36
37INSERT INTO alumnos
38VALUES('111-A','David','Sevilla Este','954025122' );
39INSERT INTO alumnos
40VALUES('222-B','Mariano','Los Remedios','954221541' );
41INSERT INTO alumnos
42VALUES('333-C','Raul','Triana','955124455' );
43INSERT INTO alumnos
44VALUES('444-D','Rocio','La Oliva','955236654' );
45INSERT INTO alumnos
46VALUES('555-E','Mariló','Triana','954085211' );
47INSERT INTO alumnos
48VALUES('666-F','Benjamin','Montequinto','955662512' );
49INSERT INTO alumnos
50VALUES('777-G','Carlos','Los Remedios','955662211' );
51INSERT INTO alumnos
52VALUES('888-H','Manolo','Montequinto','954725414' );
53
54
55INSERT INTO empresas
56VALUES('S-41001-A','Sandiel','Pab. Moldavia','Ramón','Informática');
57INSERT INTO empresas
58VALUES('S-41002-B','Condelans','Pab. Chechenia','Juan','Informática' );
59INSERT INTO empresas
60VALUES('S-41003-C','Guadartes','Pab. La Algaba','Pepe','Informática');
61INSERT INTO empresas
62VALUES('S-41004-D','Jindras','c/ PI, 4','Mari','I+D');
63INSERT INTO empresas
64VALUES('S-41005-E','SGI','c/ Caballeros, 2','Carmela','I+D' );
65INSERT INTO empresas
66VALUES('S-41006-F','Nesus','c/ Sierpes, 12','Pepi','Electrónica' );
67INSERT INTO empresas
68VALUES('S-41007-G','Arbengoa','c/ Tajo, 2','Gabriel','Electrónica' );
69
70INSERT INTO practicas
71VALUES( '111-A','S-41001-A','2016/04/08',360);
72INSERT INTO practicas
73VALUES( '333-C','S-41003-C','2017/04/08',360);
74INSERT INTO practicas
75VALUES( '111-A','S-41004-D','2017/05/08',420);
76INSERT INTO practicas
77VALUES( '444-D','S-41005-E','2015/05/08',420);
78INSERT INTO practicas
79VALUES( '111-A','S-41003-C','2015/05/08',360);
80INSERT INTO practicas
81VALUES( '777-G','S-41006-F','2017/04/08',420);
82INSERT INTO practicas
83VALUES( '888-H','S-41007-G','2017/05/08',480);
84INSERT INTO practicas
85VALUES( '222-B','S-41003-C','2017/05/08',420);
86INSERT INTO practicas
87VALUES( '555-E','S-41002-B','2017/04/08',420);
88INSERT INTO practicas
89VALUES( '333-C','S-41001-A','2017/04/08',420);
90INSERT INTO practicas
91VALUES( '333-C','S-41004-D','2017/05/08',480);