base de datos exa

create database video_club;
use video_club;
drop table pelicula;
create table pelicula
(cantidad int (3),titulo varchar (30),
categoria varchar (12), n_pelicula int (4)
,primary key (n_pelicula));
insert into pelicula values ('5', 'lo que el viento se llevo', 'drama', 0154)
,( '60', 'el perfume', 'suspenso', 1110),( '84', 'terminator', 'accion', 0928),
( '22', 'batman', 'accion', 1608),( '1', 'kejo','comedia',1305),
( '15', 'superman', 'accion', 1300),( '120', 'the simpson','comedia' ,1400),
( '3', 'la vida es bella', 'drama', 1415),( '22', 'ecos mortales', 'terror', 1616),
('3','casarse esta en griego','comedia','1171');
select * from pelicula;

drop table empleado;
create table empleado (nombres varchar(30),
turno char(1),n_empleado varchar(4),
primary key (n_empleado));
insert into empleado values ('jorge cardenas','v',0001),
('luis villa','v',0002),('mirian yañez','m',0003),
('jose islas','v',0004);
select * from empleado;

drop table cliente;
create table cliente (nombre varchar(20),
direccion varchar(20), n_cliente varchar(4),
telefono varchar(7), primary key (n_cliente));
insert into cliente values('jorge islas','cobachi 246', 0503, 2600493),
('alfonso gonzales','santa rosalia 142', 1222, 298765),
('marta cheno','tepache 123', 1247, 2114785),
('mauricio ruiz','granados 7', 0288, 2896566),
('cristina ruiz','cobachi 246', 2014, 2547856),
('ana alvarado','huachineras 306', 1455, 2554783),
('jesus alvarado','san rafael 444', 2547, 2578994),
('sonia valenzuela','rancho viejo 147', 1115, 2850445),
('claudia peralta','bacoachi 327', 5443, 2548875),
('janet salazar','isla bonita 239', 1473, 2654987);
select * from cliente;

drop table factura;
create table factura ( n_factura int (4), n_empleado varchar(4),
n_pelicula int (4), n_cliente varchar (4));
insert into factura values (4852,0002,0001,5443),(9642,0003,0002,1115),(7478,0001,0002,1222),
(5484,0001,0004,1247),(2056,0004,0003,1455),(6985,0002,0001,1473),(8973,0003,0004,2014),
(6511,0002,0002,2547),(8974,0002,0003,288),(2043,0001,0001,503);
select * from factura;

drop table detalles_factura;
create table detalles_factura (n_factura int (4), primary key (n_factura),
fecha datetime,importa float(6,2),cantidad int (2));
insert into detalles_factura values ( 4852,'080603120623',18.20,10 ),
(9642,'060403224156',124.6,2),(7478,'060403153241',8.2,2),
(5484,'030207201245',21.03,01),(2056,'030207121233',14.20,10),
(6985,'060807124723',32.55,4),(8973,'010101234051',26.90,3),
(6511,'040802091322',11.00,2),(8974,'070201145630',09.12,01),
(2043,'040802214040',15.53,2);
select * from detalles_factura;
select* from factura;
select * from empleado;

# Vistas de la base de datos
#muestra a que gente le vendio un empleado
drop view compra;
create view compra as select nombre,n_empleado
from factura,cliente where cliente.n_cliente=factura.n_cliente and factura.n_empleado=0001;
select * from compra;
create user 'carlos' @'%';
#cuenta con todos los privilegios
grant all on video_club.* to 'carlos' @'%';

# crear, elimar modificar en la base de datos
drop user 'islas2'@'localhost';
create user 'islas2'@'localhost';
grant select,update,create,drop,delete on video_club.* to 'islas2' @'localhost';

# actualizar y seleccionar en una tabla de la base de datos
create user 'kejo'@'localhost';
grant select,update on video_club.cliente to 'kejo'@'localhost';

#privilegios en la columna direccion
create user 'eliu'@'localhost';
grant select,update (direccion)on video_club.cliente to 'eliu'@'localhost';

# para una vista
create user 'cristina'@'localhost';
grant create view on video_club.* to 'cristina'@'localhost';
grant Show view on video_club.* to 'cristina'@'localhost';
#conexion desde otra maquina
create user 'maquinaexterna'@'10.19.2.6';




create database socoada;
use socoada database;

CREATE TABLE cliente (
Idcliente int(5) NOT NULL,
Nombre char(20) NOT NULL,
Direccion char(20) NOT NULL,
ciudad char(15) NOT NULL,

PRIMARY KEY (Idcliente)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




CREATE TABLE facturacobro(
folio int(5) NOT NULL,
Idcliente int(5) NOT NULL,
fecha date NOT NULL default '0000-00-00',
total decimal(6,2) NOT NULL,

primary KEY (folio),
foreign key (Idcliente) references cliente(Idcliente)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE proveedores(
idproveedor int(4) NOT NULL,
nombre char(15) not null,
estado char(20) NOT NULL,
ciudad char(20) NOT NULL,

primary KEY (idproveedor)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE producto(
clave int(4) NOT NULL,
idproveedor int(4) not null,
descripcion char(50) NOT NULL,
punitario decimal(6,2) NOT NULL,

primary KEY (clave),
foreign key(idproveedor) references proveedores(idproveedor)


) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE detallefc (
folio int(5) NOT NULL,
clave int(4) NOT NULL,
cantidad int(4) NOT NULL,
pventa decimal(8,2) NOT NULL,

FOREIGN KEY (folio) references facturacobro(folio),
FOREIGN KEY (clave) references producto(clave)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Insert into cliente Values
(10000,'Omar Sarabia' ,'Morelos 11' ,'Huepac'),
(10001,'Oscar Egurrola','Bizani 301','H.Caborca'),
(10002,'José Perla', 'Aguascalintes 208' ,'H.Caborca'),
(10003,'Larry Márquez', 'Hidalgo 255' ,'Nacozari'),
(10004,'Eduardo Urias' , 'Unicef 115 ' ,'Hermosillo'),
(10005,'Jorge Cardenas' , 'Obispo 102 ' ,'Hermosillo'),
(10006,'Jesús Córdova' , 'Satillo 100' ,'San Pedro'),
(10007,'Alejandro Moreno', 'Lázaro Cardenas 10' ,'Hermosillo'),
(10008,'Ruben Robles', 'Solidaridad 133' ,'Hermosillo'),
(10009,'Alan Orduño' , 'Matadero 666' ,'Hermosillo'),
(10010,'Francisco Sarabia', 'Sara Thompson 26' ,'Hermosillo'),
(10011,'Rafael marquez', 'Olivares Final 21' ,'Hermosillo'),
(10012,'Fco. Ochoa', 'Tepic 7' ,'Obregon'),
(10013,'Gerardo Torrado', 'Allende 121' ,'Aconchi'),
(10014,'Nery Castillo', 'Miguel Dominguez 23','Hermosillo'),
(10015,'Pedro Gonzales' , 'Puebla 133' ,'Navojoa'),
(10016,'Enrique Garcia' , 'Yucatan 11 ' ,'H.Caborca'),
(10017,'Manuel Avila', 'Insurgentes 14','Hermosillo'),
(10018,'Ramon Morales' , 'Revolucion 14' ,'Mexicali'),
(10019,'Andres Guardado', 'Calle 15 S/N ','Hermosillo'),
(10020,'Jesus Mendoza' , 'Mendoza 145' , 'Hermosillo');
insert into detallefc values
(5000, 500, 5, 350.70),
(5005, 500, 3, 350.70),
(5008, 500, 5, 350.70),
(5025, 500, 1, 350.70),
(5028, 500, 2, 350.70),
(5004, 501, 2, 375.55),
(5012, 501, 2, 375.55),
(5023, 501, 4, 375.55),
(5026, 501, 3, 375.55),
(5037, 501, 2, 375.55),
(5024, 502, 2, 350.70),
(5026, 502, 2, 350.70),
(5030, 502, 3, 350.70),
(5035, 502, 3, 350.70),
(5001, 503, 2, 375.55),
(5011, 503, 1, 375.55),
(5025, 503, 1, 375.55),
(5005, 504, 7, 189.99),
(5017, 504, 2, 189.99),
(5023, 504, 3, 189.99),
(5000, 505, 3, 109.50),
(5010, 505, 1, 109.50),
(5023, 505, 20, 109.50),
(5028, 505, 3, 109.50),
(5000, 506, 5, 75.30),
(5029, 506, 4, 7530),
(5034, 506, 3, 75.30),
(5000, 507, 1, 40.50),
(5006, 507, 4, 40.50),
(5019, 507, 8, 40.50),
(5036, 507, 4, 40.50),
(5004, 508, 10, 115.30),
(5004, 509, 15, 73.30),
(5011, 509, 3, 73.30),
(5015, 509, 5, 73.30),
(5009, 510, 7, 37.50),
(5014, 510, 3, 37.50),
(5027, 510, 2, 37.50),
(5030, 510, 2, 37.50),
(5040, 511, 2, 70.90),
(5003, 512, 10, 130.00),
(5017, 512, 4, 130.00),
(5001, 513, 3, 115.00),
(5005, 513, 2, 115.00),
(5016, 513, 4, 115.00),
(5018, 513, 4, 115.00),
(5039, 513, 3, 115.00),
(5002, 514, 4, 130.00),
(5007, 514, 2, 130.00),
(5013, 515, 4, 108.00),
(5038, 515, 4, 108.00),
(5020, 516, 4, 4.50),
(5022, 516, 4, 4.50),
(5033, 517, 5, 3.75),
(5031, 518, 3, 6.32),
(5003, 519, 20, 9.98),
(5016, 519, 3, 9.98),
(5021, 519, 5, 9.98),
(5032, 519, 3, 9.98),
(5000, 520, 3, 75.60),
(5003, 520, 12, 75.60),
(5007, 520, 10, 75.60),
(5009, 520, 3, 75.60),
(5021, 520, 7, 75.60),
(5038, 520, 2, 75.60),
(5039, 520, 4, 75.60),
(5005, 521, 6, 57.40),
(5020, 521, 2, 57.40),
(5031, 521, 2, 57.40),
(5040, 521, 2, 57.40);

insert into producto values
(500, 100, 'Cubeta 19lts. Exterior Azul' ,350.70),
(501, 100, 'Cubeta 19lts. Interior Azul' ,375.55),
(502, 100, 'Cubeta 19lts. Exterior Amarillo' ,350.70),
(503, 100, 'Cubeta 19lts. Interior Amarillo', 375.55),
(504, 100, 'Cubeta 9lts. Aceite Marron' , 189.99),
(505, 101, 'Cemento La Campana 50Kg' , 109.50),
(506, 101, 'Cal La Campana 50Kg' ,75.30),
(507, 101, 'Yeso La Campana 12Kg' ,40.50),
(508, 103, 'Cemento Apasco 50Kg' ,115.30),
(509, 103, 'Cal Apasco 50Kg' ,73.30),
(510, 102, 'Madera Pino 2x2x12' ,37.50),
(511, 102, 'hoja de Triplait' , 70.90),
(512, 107, 'hoja de lamina galvanizada', 130.00),
(513, 106, 'Cristal Polarizado M2' ,115.00),
(514, 106, 'Cristal Corrugado M2' ,130.00),
(515, 106, 'Vidrio Trans. M2' , 108.00),
(516, 105, 'Cable Calibre 8 - M' , 4.50),
(517, 105, 'Cable Calibre 10 - M' , 3.75),
(518, 105, 'Cable Calibre 6 - M' , 6.32),
(519, 105, 'Cable Calibre 0 - M', 9.98),
(520, 104, 'Tramo Varilla 1/2' , 75.60),
(521, 104, 'Tramo Varilla 1/4', 57.40);
insert into proveedores values
(100, 'Comex ', 'Durango ', 'Durango' ),
(101, 'Cemex ', 'Mexico ', 'D.F.' ),
(102, 'Maderas Herm, ', 'Sonora ', 'Hermosillo'),
(103, 'Apasco ', 'Mexico ', 'D.F. ' ),
(104, 'Aceromex ', 'Sinaloa ', 'Culiacan' ),
(105, 'Condumex ', 'Nuevo Leon', 'Monterrey' ),
(106, 'Cristales RC ', 'Sonora ', 'Caborca '),
(107, 'Laminas Duramex', 'Sonora ', 'Guaymas ');

insert into facturacobro values
(5000, 10000, '2007-07-02 ' ,2725.80),
(5001, 10003, '2007-07-03 ' ,1096.10),
(5002, 10008, '2007-07-02 ' , 520.00),
(5003, 10006, '2007-07-02 ', 2406.80),
(5004, 10002, '2007-07-02 ', 3003.60),
(5005, 10001, '2007-07-02 ', 2956.43),
(5006, 10009, '2007-07-02 ', 162.00),
(5007, 10005, '2007-07-02 ',1016.00),
(5008, 10010, '2007-07-02 ',1753.50),
(5009, 10004, '2007-07-02 ', 489.30),
(5010, 10007, '2007-07-02 ', 109.50),
(5011, 10000, '2007-07-02 ', 595.45),
(5012, 10001, '2007-07-03 ', 751.10),
(5013, 10014, '2007-07-04 ', 432.00),
(5014, 10020, '2007-07-04 ', 112.50),
(5015, 10016, '2007-07-04 ', 366.50),
(5016, 10018, '2007-07-05 ', 489.94),
(5017, 10019, '2007-07-05 ', 899.98),
(5018, 10017, '2007-07-05 ', 460.00),
(5019, 10011, '2007-07-05 ', 324.00),
(5020, 10012, '2007-07-05 ', 132.80),
(5021, 10015, '2007-07-05 ', 579.10),
(5022, 10013, '2007-07-05 ', 18.00),
(5023, 10006, '2007-07-05 ', 4262.17),
(5024, 10009, '2007-07-05 ', 701.40),
(5025, 10000, '2007-07-05 ', 726.25),
(5026, 10002, '2007-07-05 ', 1828.05),
(5027, 10015, '2007-07-05 ', 75.00),
(5028, 10016, '2007-07-05 ', 1029.90),
(5029, 10010, '2007-07-05 ', 301.20),
(5030, 10020, '2007-07-05 ', 1127.10),
(5031, 10015, '2007-07-06 ', 133.76),
(5032, 10004, '2007-07-06 ', 29.94),
(5033, 10018, '2007-07-06 ', 18.75),
(5034, 10016, '2007-07-06 ', 225.90),
(5035, 10012, '2007-07-06 ', 1052.10),
(5036, 10013, '2007-07-06 ', 507.90),
(5037, 10000, '2007-07-06 ', 751.10),
(5038, 10011, '2007-07-06 ', 583.20),
(5039, 10007, '2007-07-06 ', 647.40),
(5040, 10014, '2007-07-06 ', 256.60);
select * from cliente;
SELECT * FROM proveedores p;
SELECT * FROM facturacobro f;