Ayuda con solucion de consulta
Publicado por Ramon (1 intervención) el 28/02/2017 11:45:35
INFORME QUE OBTENGA EL ARTICULO POR CENTRO COMERCIAL CON EL PRECIO MAS BAJO
----- con la sigueinte base de datos--------
----- con la sigueinte base de datos--------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
DROP DATABASE centroscc;
CREATE DATABASE centroscc;
USE centroscc;
CREATE TABLE ccomercial ( id_cc SMALLINT UNSIGNED AUTO_INCREMENT,nombre VARCHAR(50) NOT NULL,
CONSTRAINT pk_ccomercial PRIMARY KEY (id_cc)
);CREATE TABLE descart (id_art CHAR(6),
descrip VARCHAR(50) NOT NULL,
fecha_alta DATE NOT NULL, fecha_obs DATE,CONSTRAINT pk_descart PRIMARY KEY (id_art)
);CREATE TABLE articulos ( id_cc SMALLINT UNSIGNED,id_art CHAR(6),
precio INT UNSIGNED NOT NULL, dto SMALLINT UNSIGNED,CONSTRAINT pk_articulos PRIMARY KEY (id_cc,id_art),
CONSTRAINT fk_id_cc FOREIGN KEY (id_cc) REFERENCES ccomercial (id_cc),
CONSTRAINT fk_id_art FOREIGN KEY (id_art) REFERENCES descart (id_art)
);INSERT INTO ccomercial VALUES (NULL,"Carrefour");
INSERT INTO ccomercial VALUES (NULL,"Alcampo");
INSERT INTO ccomercial VALUES (NULL,"Lidl");
INSERT INTO ccomercial VALUES (NULL,"Aldi");
INSERT INTO ccomercial VALUES (NULL,"Hiperusera");
INSERT INTO ccomercial VALUES (NULL,"Ahorramax");
INSERT INTO descart VALUES ("A1","Articulo1","20130108",NULL);
INSERT INTO descart VALUES ("A2","Articulo2","20130208",NULL);
INSERT INTO descart VALUES ("A3","Articulo3","20130407",NULL);
INSERT INTO descart VALUES ("A4","Articulo4","20130408",NULL);
INSERT INTO descart VALUES ("A5","Articulo5","20130408",NULL);
INSERT INTO descart VALUES ("A6","Articulo6","20130409",NULL);
INSERT INTO descart VALUES ("A7","Articulo7","20140108",NULL);
INSERT INTO descart VALUES ("A8","Articulo8","20140208",NULL);
INSERT INTO descart VALUES ("A9","Articulo9","20140407",NULL);
INSERT INTO descart VALUES ("A0","Articulo0","20140407",NULL);
INSERT INTO articulos VALUES ("1","A1",5,0);
INSERT INTO articulos VALUES ("1","A2",8,1);
INSERT INTO articulos VALUES ("1","A3",125,5);
INSERT INTO articulos VALUES ("1","A4",200,0);
INSERT INTO articulos VALUES ("1","A5",180,0);
INSERT INTO articulos VALUES ("1","A6",15,3);
INSERT INTO articulos VALUES ("1","A7",255,0);
INSERT INTO articulos VALUES ("1","A8",35,2);
INSERT INTO articulos VALUES ("1","A9",195,5);
INSERT INTO articulos VALUES ("2","A1",201,0);
INSERT INTO articulos VALUES ("2","A2",11,1);
INSERT INTO articulos VALUES ("2","A3",105,5);
INSERT INTO articulos VALUES ("2","A4",4,0);
INSERT INTO articulos VALUES ("2","A5",160,0);
INSERT INTO articulos VALUES ("2","A6",15,3);
INSERT INTO articulos VALUES ("2","A7",255,0);
INSERT INTO articulos VALUES ("2","A8",35,1);
INSERT INTO articulos VALUES ("2","A9",195,5);
INSERT INTO articulos VALUES ("3","A1",5,0);
INSERT INTO articulos VALUES ("3","A2",8,1);
INSERT INTO articulos VALUES ("3","A3",105,5);
INSERT INTO articulos VALUES ("3","A6",15,3);
INSERT INTO articulos VALUES ("3","A7",205,0);
INSERT INTO articulos VALUES ("3","A8",37,2);
INSERT INTO articulos VALUES ("3","A9",185,4);
INSERT INTO articulos VALUES ("4","A1",125,5);
INSERT INTO articulos VALUES ("4","A3",2,0);
INSERT INTO articulos VALUES ("4","A4",210,0);
INSERT INTO articulos VALUES ("4","A5",180,0);
INSERT INTO articulos VALUES ("4","A6",15,4);
INSERT INTO articulos VALUES ("4","A7",355,11);
INSERT INTO articulos VALUES ("4","A8",35,2);
INSERT INTO articulos VALUES ("4","A9",105,5);
INSERT INTO articulos VALUES ("6","A1",35,2);
INSERT INTO articulos VALUES ("6","A2",8,1);
INSERT INTO articulos VALUES ("6","A8",5,2);
INSERT INTO articulos VALUES ("6","A9",99,5);
UPDATE descart
SET fecha_obs=CURRENT_DATEWHERE id_art="A7";
UPDATE descart
SET fecha_obs="2013-12-30"
WHERE id_art="A5";
Valora esta pregunta
0
