SQL amazon info

an anonymous user · May 04, 2021 · SQL
-- create a table
CREATE TABLE articoli (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  descrizione TEXT,
  prezzo INTEGER,
  id_mag INTEGER
);

CREATE TABLE magazzini (
  id_mag INTEGER PRIMARY KEY,
  stato VARCHAR(2),
  zip INTEGER,
  ind_mag TEXT
);

CREATE TABLE persone (
  n_tel INTEGER PRIMARY KEY,
  stato_res VARCHAR(2)
);

CREATE TABLE acquisti (
    cod_sped INTEGER PRIMARY KEY AUTOINCREMENT,
    id INTEGER,
    n_tel INTEGER
);

-- insert some values
INSERT INTO articoli (id_mag) VALUES (3);
INSERT INTO articoli (id_mag) VALUES (3);
INSERT INTO articoli (id_mag) VALUES (3);
INSERT INTO articoli (id_mag) VALUES (4);
INSERT INTO articoli (id_mag) VALUES (4);


INSERT INTO magazzini (id_mag, stato) VALUES (3, 'US');
INSERT INTO magazzini (id_mag, stato) VALUES (4, 'IT');

INSERT INTO persone (n_tel, stato_res) VALUES (333, 'US');
INSERT INTO persone (n_tel, stato_res) VALUES (444, 'US');
INSERT INTO persone (n_tel, stato_res) VALUES (555, 'IT');
INSERT INTO persone (n_tel, stato_res) VALUES (666, 'IT');
INSERT INTO persone (n_tel, stato_res) VALUES (777, 'IT');

INSERT INTO acquisti (id, n_tel) VALUES (4, 333);
INSERT INTO acquisti (id, n_tel) VALUES (4, 444);
INSERT INTO acquisti (id, n_tel) VALUES (3, 444);
INSERT INTO acquisti (id, n_tel) VALUES (2, 666);
INSERT INTO acquisti (id, n_tel) VALUES (2, 777);
INSERT INTO acquisti (id, n_tel) VALUES (1, 777);

-- fetch some values
-- SELECT * FROM articoli;
-- SELECT * FROM magazzini;
-- SELECT * FROM persone;

-- SELECT COUNT(*) FROM articoli a JOIN magazzini m ON a.id_mag = m.id_mag WHERE stato = 'US';
-- SELECT stato_res, id, COUNT(*) FROM (SELECT stato_res, id FROM acquisti aq JOIN persone p ON aq.n_tel = p.n_tel GROUP BY stato_res ORDER BY COUNT(*) desc);
SELECT id, COUNT(*) FROM acquisti aq JOIN persone p ON aq.n_tel = p.n_tel WHERE stato_res = 'IT' GROUP BY id ORDER BY COUNT(*) desc LIMIT 1; 


Comments

Please sign up or log in to contribute to the discussion.