06-04-2022, 18:56
Hola gente, como andan? Espero que bien!
Me dieron un ejercicio de base de datos y la verdad es que no sé por donde encararlo, sí alguien me ayuda genial!
Tengo la tabla de pizzas con sus ingredientes y la tabla de ordenes (adjuntadas)
El ejercicio me pide imprimir una nueva pizza que tenga los 5 ingredientes más utilizados en los últimos 6 meses. Cómo hago ese contador de cada uno de los ingredientes utilizados dentro de un array? La verdad es que me mató! Gracias!
Les dejo el código para insertar datos en un mysql:
CREATE TABLE sys.orders(
order_id int NOT NULL,
customer_id int NULL,
pizza_id int NULL,
exclusions varchar(4) NULL,
extras varchar(4) NULL,
order_time datetime NULL);
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(1, 101, 1, N'', N'', CAST(N'2021-02-01T18:05:02.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(2, 101, 1, N'', N'', CAST(N'2021-02-01T19:00:52.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(3, 102, 1, N'', N'', CAST(N'2021-03-02T23:51:23.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(3, 102, 2, N'', NULL, CAST(N'2021-03-02T23:51:23.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(4, 103, 1, N'4', N'', CAST(N'2021-05-04T13:23:46.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(4, 103, 2, N'4', N'', CAST(N'2021-05-04T13:23:46.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(5, 104, 1, N'null', N'1', CAST(N'2021-06-08T21:00:29.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(6, 101, 2, N'null', N'null', CAST(N'2021-06-08T21:03:13.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(7, 105, 2, N'null', N'1', CAST(N'2021-06-08T21:20:29.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(8, 102, 1, N'null', N'null', CAST(N'2021-07-09T23:54:33.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(9, 103, 1, N'4', N'1, 5', CAST(N'2021-08-10T11:22:59.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(10, 104, 1, N'null', N'null', CAST(N'2021-09-11T18:34:49.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(11, 115, 3, NULL, N'8', CAST(N'2021-09-11T19:45:29.000' AS DateTime)) ;
CREATE TABLE sys.pizza(
id int NOT NULL,
name varchar(50) NULL,
ingredients varchar(50) NULL);
INSERT sys.pizza (id, name, ingredients) VALUES (1, N'Carnivore', N'1, 2, 3, 4, 5, 6, 8, 10');
INSERT sys.pizza (id, name, ingredients) VALUES (2, N'Vegetarian', N'4, 6, 7, 9, 11, 12');
INSERT sys.pizza (id, name, ingredients) VALUES (3, N'Prosciutto e sql', N'11, 12, 13, 6') ;
Me dieron un ejercicio de base de datos y la verdad es que no sé por donde encararlo, sí alguien me ayuda genial!
Tengo la tabla de pizzas con sus ingredientes y la tabla de ordenes (adjuntadas)
El ejercicio me pide imprimir una nueva pizza que tenga los 5 ingredientes más utilizados en los últimos 6 meses. Cómo hago ese contador de cada uno de los ingredientes utilizados dentro de un array? La verdad es que me mató! Gracias!
Les dejo el código para insertar datos en un mysql:
CREATE TABLE sys.orders(
order_id int NOT NULL,
customer_id int NULL,
pizza_id int NULL,
exclusions varchar(4) NULL,
extras varchar(4) NULL,
order_time datetime NULL);
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(1, 101, 1, N'', N'', CAST(N'2021-02-01T18:05:02.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(2, 101, 1, N'', N'', CAST(N'2021-02-01T19:00:52.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(3, 102, 1, N'', N'', CAST(N'2021-03-02T23:51:23.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(3, 102, 2, N'', NULL, CAST(N'2021-03-02T23:51:23.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(4, 103, 1, N'4', N'', CAST(N'2021-05-04T13:23:46.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(4, 103, 2, N'4', N'', CAST(N'2021-05-04T13:23:46.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(5, 104, 1, N'null', N'1', CAST(N'2021-06-08T21:00:29.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(6, 101, 2, N'null', N'null', CAST(N'2021-06-08T21:03:13.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(7, 105, 2, N'null', N'1', CAST(N'2021-06-08T21:20:29.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(8, 102, 1, N'null', N'null', CAST(N'2021-07-09T23:54:33.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(9, 103, 1, N'4', N'1, 5', CAST(N'2021-08-10T11:22:59.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(10, 104, 1, N'null', N'null', CAST(N'2021-09-11T18:34:49.000' AS DateTime));
INSERT sys.orders (order_id, customer_id, pizza_id, exclusions, extras, order_time) VALUES
(11, 115, 3, NULL, N'8', CAST(N'2021-09-11T19:45:29.000' AS DateTime)) ;
CREATE TABLE sys.pizza(
id int NOT NULL,
name varchar(50) NULL,
ingredients varchar(50) NULL);
INSERT sys.pizza (id, name, ingredients) VALUES (1, N'Carnivore', N'1, 2, 3, 4, 5, 6, 8, 10');
INSERT sys.pizza (id, name, ingredients) VALUES (2, N'Vegetarian', N'4, 6, 7, 9, 11, 12');
INSERT sys.pizza (id, name, ingredients) VALUES (3, N'Prosciutto e sql', N'11, 12, 13, 6') ;