DROP DATABASE IF EXISTS tallerSQL2;
CREATE DATABASE IF NOT EXISTS tallerSQL2;
use tallerSQL2;
CREATE TABLE cliente(
id_cli int not null AUTO_INCREMENT,
nom_cli varchar(30) not null,
ape_cli varchar(30) not null,
dir_cli varchar(100) not null,
dep_cli varchar(20) not null,
mes_cum_cli varchar(15) not null,
PRIMARY key (id_cli)
);
CREATE TABLE articulo(
id_art int not null AUTO_INCREMENT,
tit_art varchar(100) not null,
aut_art varchar(100) not null,
edi_art varchar(300) not null,
prec_art int not null,
PRIMARY KEY(id_art)
);
CREATE TABLE pedido(
id_ped int not null AUTO_INCREMENT,
fec_ped date not null,
val_ped int not null,
id_cli_ped int not null,
PRIMARY key(id_ped),
FOREIGN key ( id_cli_ped) REFERENCES cliente(id_cli)
);
CREATE table articulo_pedido(
can_art_artped int not null,
val_art_artped int not null,
id_ped_artped int not null,
id_art_artped int not null,
FOREIGN key(id_ped_artped) REFERENCES pedido(id_ped),
FOREIGN key(id_art_artped) REFERENCES articulo(id_art)
);
CREATE table compañia(
comnit varchar(15) not null,
comnombre varchar(30) not null,
comañofun int not null,
comreplegal varchar(100) not null,
PRIMARY key (comnit)
);
–Cliente
INSERT INTO cliente(`id_cli`, `nom_cli`, `ape_cli`, `dir_cli`, `dep_cli`, `mes_cum_cli`)
VALUES (63502718,'Maritza','Rojas', 'Calle 34 No 14-35', 'Santander','Abril');
INSERT INTO cliente(`id_cli`, `nom_cli`, `ape_cli`, `dir_cli`, `dep_cli`, `mes_cum_cli`)
VALUES (13890234,'Roger','Ariza', 'Cra 30 No 13-45', 'Antioquia','junio');
INSERT INTO cliente(`id_cli`, `nom_cli`, `ape_cli`, `dir_cli`, `dep_cli`, `mes_cum_cli`)
VALUES (77191956,'Juan Carlos','Arenas', 'Diagonal 23 No 13-34 apto 101', 'Valle','Marzo');
INSERT INTO cliente(`id_cli`, `nom_cli`, `ape_cli`, `dir_cli`, `dep_cli`, `mes_cum_cli`)
VALUES (1098765789,'Catalina','Zapata', 'Av El Libertador No 30-14', 'Cauca','Marzo');
–Artículo
insert into articulo (id_art,tit_art,aut_art,edi_art,prec_art)
values(1,"Redes cisco","Ernesto Arigasello","Alfaomega - Rama",60000);
insert into articulo (id_art,tit_art,aut_art,edi_art,prec_art)
values(2,"Facebook y Twitter para adultos","Veloso Claudio","Alfaomega",52000);
insert into articulo (id_art,tit_art,aut_art,edi_art,prec_art)
values(3,"Creación de un portal con php y mysql","Jacob Pavón Puertas","Alfaomega -
Rama",40000);
insert into articulo (id_art,tit_art,aut_art,edi_art,prec_art)
values(4,"Administración de sistemas operativos","Julio Gómes López","Alfaomega -
Rama",55000);
–Pedido.
INSERT INTO pedido(id_ped,fec_ped,val_ped,id_cli_ped) VALUES(1,"2012-02-
25",120000,63502718);
INSERT INTO pedido(id_ped,fec_ped,val_ped,id_cli_ped) VALUES(2,"2012-04-
30",55000,77191956);
INSERT INTO pedido(id_ped,fec_ped,val_ped,id_cli_ped) VALUES(3,"2011-12-
10",260000,63502718);
INSERT INTO pedido(id_ped,fec_ped,val_ped,id_cli_ped) VALUES(4,"2012-02-
25",120000,1098765789);
—-Articulo_pedido
INSERT INTO articulo_pedido(`can_art_artped`, `val_art_artped`,
`id_ped_artped`, `id_art_artped`) VALUES (5,40000,1,3);
INSERT INTO articulo_pedido(`can_art_artped`, `val_art_artped`,
`id_ped_artped`, `id_art_artped`) VALUES (12,55000,1,4);
INSERT INTO articulo_pedido(`can_art_artped`, `val_art_artped`,
`id_ped_artped`, `id_art_artped`) VALUES (5,65000,2,1);
INSERT INTO articulo_pedido(`can_art_artped`, `val_art_artped`,
`id_ped_artped`, `id_art_artped`) VALUES (10,55000,3,2);
INSERT INTO articulo_pedido(`can_art_artped`, `val_art_artped`,
`id_ped_artped`, `id_art_artped`) VALUES (12,45000,3,3);
INSERT INTO articulo_pedido(`can_art_artped`, `val_art_artped`,
`id_ped_artped`, `id_art_artped`) VALUES (20,65000,4,1);
–compañia
INSERT INTO `compañia`(`comnit`, `comnombre`, `comañofun`, `comreplegal`)
VALUES ('800890890-2','Seguros Atlantida',1998,'Carlos Lopez');
INSERT INTO `compañia`(`comnit`, `comnombre`, `comañofun`, `comreplegal`)
VALUES ('899999999-1','Aseguradora Rojas',1991,'Luis Fernando Rojas');
INSERT INTO `compañia`(`comnit`, `comnombre`, `comañofun`, `comreplegal`)
VALUES ('899999999-5','Seguros del Estado',2001,'Maria Margarita Perez');
consultas
10.
SELECT pedido.id_ped,pedido.val_ped,pedido.fec_ped,articulo.tit_art FROM
pedido,articulo;
11.
SELECT * FROM `cliente` WHERE mes_cum_cli = 'Marzo';
12.
SELECT cliente.nom_cli,cliente.dir_cli, articulo.tit_art,articulo.prec_art
FROM cliente,articulo,pedido WHERE pedido.id_ped = 1;
13.
SELECT cliente.nom_cli, pedido.val_ped,pedido.fec_ped FROM cliente, pedido
ORDER BY pedido.val_ped DESC LIMIT 1;
14.
15.
SELECT
pedido.id_ped,pedido.fec_ped,pedido.val_ped,articulo.id_art,articulo.tit_ar
t,articulo.prec_art, articulo_pedido.can_art_artped FROM
articulo,pedido,articulo_pedido;
16.
SELECT * FROM cliente ORDER BY cliente.ape_cli;
17.
SELECT * FROM articulo ORDER BY articulo.aut_art;
18.
SELECT cliente.nom_cli,cliente.dir_cli,pedido.id_ped AS numero_pedido,
articulo_pedido.can_art_artped FROM cliente,pedido,articulo,articulo_pedido
WHERE articulo.id_art = 2;
19.
SELECT * FROM compañia WHERE compañ[Link]ñofun BETWEEN 1991 AND 1998;
