-- CREAZIONE STRUTTURA DATABASE

​

-- Creazione della tabella Fornitori

CREATE TABLE Fornitori (

    IDFornitore INT IDENTITY PRIMARY KEY, -- Chiave primaria auto-incrementante per identificare in modo univo i fornitori

    Nome VARCHAR(100) NOT NULL,           -- Nome del foritore ( non può essere vuoto)

    Descrizione VARCHAR(255)              -- Descrizione del fornitore

);

​

-- Creazione della tabella DettagliFornitore

-- Ha una Relazione 1 a 1 con Fornitore, a 1 record fornitore corrisponde al massimo 1 record di dettaglio fornitore  e viceversa

CREATE TABLE DettagliFornitore(

    IDDettagliFornitore INT IDENTITY PRIMARY KEY, -- chiave primaria (PRIMARY KEY) auto incrementante (IDENTITY) per identificare in mdo univoco un record della tabella DettagliFornitore

    FornitoreID INT NOT NULL UNIQUE,              -- Attributo che sarà la chiave esterna (FOREIGN KEY) per collegare i dettagli del fornitore alla tabella fornitore (non può essere vuota e deve essere univoca)

    OrarioApertura TIME,

    OrarioChiusura TIME,

    GiornoChiusura VARCHAR(10),

    CONSTRAINT fk_dettagli_fornitore FOREIGN KEY(FornitoreID) REFERENCES Fornitori(IDFornitore) -- definisce FornitoreID come chiave esterna, facendo riferimento alla chiave primaria IDFornitore nella tabella Fornitori

    ON UPDATE CASCADE ON DELETE CASCADE -- aggiornamento e cancellazione automatica se l'IDFornitore associato viene modificato o eliminato

);

​

-- Tabella Articoli: Contiene informazioni sugli articoli forniti dai fornitori

-- Ha una relazione 1 a Molti con Fornitore: A un singolo record fornitore possono corrispon dere più articoli, un articolo corrispponde a un solo fornitore

CREATE TABLE Articoli (

    IDArticolo INT IDENTITY PRIMARY KEY,

    Nome VARCHAR(100) NOT NULL,

    Descrizione VARCHAR(max),       -- max indica che la colonna Descrizione può contenere una quantitò massima di dati di tipo stringa (in SQL Server, max indica una lunghezza massima pari a 2 GB)

    Prezzo DECIMAL(10,2),

    Disponibilita INT DEFAULT 0,    -- quantità disponibilie dell'articolo (valore predefinito: 0)

    FornitoreID INT NOT NULL,

    CONSTRAINT fk_articolo_fornitore FOREIGN KEY(FornitoreID) REFERENCES Fornitori(IDFornitore) -- definisce FornitoreID come chiave esterna, facendo riferimento alla chiave primaria IDFornitore nella tabella Fornitori

    ON UPDATE CASCADE ON DELETE NO ACTION -- L'OPZIONE on update cascade implica che se l'IDForniotre viene modificato nella tabella fornitori, queste modifiche si rifletteranno su questa tabella, inoltre ON DELETE NO ACTION impedisce la cancellazione di un fornitore se ci sono articoli associati

);

​

-- tabella Categorie: Contiene le categorie a cui poossono appartenere gli articoli

CREATE TABLE Categorie(

    IDCategoria INT IDENTITY PRIMARY KEY,

    Nome VARCHAR(100) not null,

    Descrizione VARCHAR(255)

);

​

CREATE TABLE ArticoliCategorie (

    IDArticoloCategorie INT IDENTITY PRIMARY KEY,

    ArticoloID INT NOT NULL,

    CategoriaID int NOT NULL,

    UNIQUE(ArticoloID,CategoriaID), -- assicura l'unicità della combinazione articolo cagegoria, ed evita che lo stesso articolo possa stare più volte nella stessa categoria

    CONSTRAINT fk_articolo FOREIGN KEY (ArticoloID) REFERENCES Articoli(IDArticolo)

    ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_categoria FOREIGN KEY (CategoriaID) REFERENCES Categorie(IDCategoria)

    ON UPDATE CASCADE ON DELETE NO ACTION

);

​

-- POPOLAMENTO DEL DATABASE

​

INSERT INTO Fornitori(Nome,Descrizione) VALUES ('Fornitore 1', 'Descrizione Fornitore 1');

INSERT INTO Fornitori(Nome,Descrizione) VALUES ('Fornitore 2', 'Descrizione Fornitore 2');

INSERT INTO Fornitori(Nome,Descrizione) VALUES ('Fornitore 3', 'Descrizione Fornitore 3');

​

INSERT INTO DettagliFornitore(FornitoreID, OrarioApertura, OrarioChiusura, GiornoChiusura) VALUES (1,'08:00','17:00', 'Lunedì');

INSERT INTO DettagliFornitore(FornitoreID, OrarioApertura, OrarioChiusura, GiornoChiusura) VALUES (2,'09:30','18:30', 'Martedì');

INSERT INTO DettagliFornitore(FornitoreID, OrarioApertura, OrarioChiusura, GiornoChiusura) VALUES (3,'10:00','16:00', 'Mercoledì');

​

INSERT INTO Categorie (Nome, Descrizione) VALUES

('Scarpe','Categoria per le Scarpe'),

('Camicie','Categoria per le Camicie'),

('Cravatte','Categoria per le Cravatte'),

('Pantaloni','Categoria per i Pantaloni'),

('Orologi','Categoria per gli orologi'),

('Uomo','Articoli per Uomo'),

('Donna','Articoli per Donna'),

('Unisex','Articoli Unisex');

​

INSERT INTO Articoli(Nome,Prezzo,Disponibilita, FornitoreID) VALUES

('Scarpe Adidas xrs',   150.00,100,1),

('Scarpe Nike Air Max', 170.00, 100, 2),

('Camicia Righe M',     70.00, 20, 1),

('Camicia Quadri XL',   90.00, 40, 3),

('Cravatta blu',        29.00, 50, 2),

('Cravatta rossa',      29.00, 50, 2),

('Pantaloni grigi',     89.00, 80, 1),

('Pantaloni verdi',     99.00, 80, 3),

('Orologio sub',        189.00, 20, 3),

('Orologio sport',      289.00, 30, 3);

​

INSERT INTO ArticoliCategorie(ArticoloID, CategoriaID) VALUES

(1,1),

(1,6),

(2,1),

(2,6),

(2,7),

(2,8),

(3,2),

(3,6),

(4,2),

(4,6),

(5,2),

(5,6),

(6,3),

(6,6),

(7,4),

(7,6),

(7,7),

(7,8),

(8,4),

(8,6),

(8,7),

(8,8),

(9,5),

(9,6),

(10,5),

(10,6);

​

-- SELECT 

-- SELECT column1, column2, ... FROM table_name;

​

select * from Fornitori;

​

select * 

from DettagliFornitore

where FornitoreID = 1;

​

​

-- Seleziona Tutti i record e le colonne dalla Tabella Articoli

SELECT * FROM Articoli;

​

-- seleziona tutti i record e le colonne Nome Prezzo Disponibilità dalla tabella Articoli

SELECT Nome, Prezzo, Disponibilita

FROM  Articoli;

​

-- Sintassi

-- SELECT column1, column2, ...

-- FROM table_name

-- WHERE condition;

​

-- selezionare tutti gli attributi (colonne) da Articoli dove il fornitore è uguale a 1

SELECT * 

FROM Articoli

WHERE FornitoreID = 1;

​

SELECT Nome, Prezzo, Disponibilita, FornitoreID

FROM Articoli;

​

-- come sopra ma selezionando solo gli attributi Nome Prezzo Disponibilità

SELECT Nome, Prezzo, Disponibilita 

FROM Articoli

WHERE FornitoreID = 1;

​

-- IN 

--  seleziona gli articoli di fornitore 1 e fornitore 2

SELECT *

FROM Articoli 

WHERE FornitoreID IN (1,2);

​

-- Operatori AND, OR, NOT

--  seleziona gli articoli di fornitore 1 e fornitore 2

SELECT *

FROM Articoli 

WHERE FornitoreID = 1 OR FornitoreID = 2;

​

-- selezionare articoli con fornitore 2 e Prezzo >=50

SELECT * 

FROM Articoli

WHERE FornitoreID = 2 AND Prezzo >=50;

​

-- selezionare articoli con fornitore 1 e Prezzo >50 e minore di 200

​

SELECT *

FROM Articoli 

WHERE FornitoreID NOT IN (3);

​

SELECT *

FROM Articoli 

WHERE FornitoreID = 1 OR FornitoreID = 2;

​

SELECT * 

FROM Articoli

WHERE FornitoreID = 1 AND (Prezzo > 50 and Prezzo < 200);

​

SELECT * 

FROM Articoli

WHERE (FornitoreID !=2) AND (Prezzo > 50 and Prezzo < 200);

​

SELECT * 

FROM Articoli

WHERE (FornitoreID <>2) AND (Prezzo > 50 and Prezzo < 200);

​

SELECT * 

FROM Articoli

WHERE NOT FornitoreID =2 AND (Prezzo > 50 and Prezzo < 200);

​

SELECT * 

FROM Articoli

WHERE Disponibilita >= 20 AND Disponibilita <= 80;

​

-- BETWEEN seleziona i valore in un certo range

SELECT * 

FROM Articoli

WHERE Disponibilita BETWEEN 20 AND 80;

​

-- COUNT(*) 

-- utilizzata per contare il numero totale di righe in una tabella. 

    SELECT COUNT(*)

    FROM Articoli;

​

​

    SELECT COUNT(*) AS articoli

    FROM Articoli;

    -- Tuttavia, è possibile utilizzare altri criteri al posto dell'asterisco per contare le righe che corrispondono a condizioni specifiche.

    -- Ad esempio, è possibile utilizzare una colonna specifica al posto di * per contare il numero di righe in cui quella colonna non è nulla

    SELECT COUNT(Descrizione) as 'articoli con descrizione'

    FROM Articoli;

​

-- somma, media, massimo, minimo

SELECT SUM (Disponibilita) FROM Articoli;

SELECT AVG (Prezzo) FROM Articoli;

SELECT MAX (Prezzo) FROM Articoli;

SELECT MIN (Prezzo) FROM Articoli;

​

-- LIKE 

-- selezione i nome che contengono qualsiasi carattere prima e dopo di camicia

SELECT *

FROM Articoli

WHERE Nome LIKE '%Camicia%';

​

-- seleziona i nomi che iniziano con scarpe

​

SELECT *

FROM Articoli

WHERE Nome LIKE 'Scarpe%';

​

-- seleziona i nomi che terminano con Max

SELECT *

FROM Articoli

WHERE Nome LIKE '%Max';

​

SELECT * 

FROM Articoli 

WHERE Nome LIKE 'Camicia__S';

​

-- IS NULL / IS NOT NULL

SELECT * from Articoli WHERE DESCRIZIONE IS NULL;

SELECT * from Articoli WHERE DESCRIZIONE IS NOT NULL;

​

-- ORDER BY

Select Nome, Prezzo

from Articoli

ORDER BY Prezzo;

​

​

Select *

from Articoli

ORDER BY Prezzo DESC;

​

Select *

from Articoli

WHERE FornitoreID = 2

ORDER BY Prezzo DESC;

​

​

Select *

from Articoli

WHERE FornitoreID = 2

ORDER BY Nome, Prezzo DESC;

​

-- JOIN 

​

SELECT *

FROM Articoli

JOIN Fornitori ON Articoli.FornitoreID = Fornitori.IDFornitore;

​

​

SELECT Articoli.IDArticolo, Articoli.Nome, Articoli.Prezzo, Fornitori.Nome

FROM Articoli

JOIN Fornitori on Articoli.FornitoreID = Fornitori.IDFornitore;

​

SELECT Fornitori.IDFornitore, Nome, Descrizione, OrarioApertura,OrarioChiusura,GiornoChiusura 

FROM Fornitori INNER JOIN DettagliFornitore 

ON Fornitori.IDFornitore = DettagliFornitore.FornitoreID;

​

SELECT Articoli.Nome AS Articolo, Articoli.Prezzo, Articoli.Disponibilita, Categorie.Nome as Categoria

FROM Articoli 

JOIN ArticoliCategorie ON Articoli.IDArticolo=ArticoliCategorie.ArticoloID

JOIN Categorie ON ArticoliCategorie.CategoriaID = Categorie.IDCategoria

​

-- GROUP PY

-- La clausola GROUP BY serve a raggruppare i record in base ai valori di una o più colonne.

-- I record con valori uguali nelle colonne specificate vengono raggruppati insieme.

-- Le funzioni di aggregazione come COUNT(), SUM(), AVG() e MAX() vengono utilizzate per calcolare valori aggregati per ogni gruppo.

-- Trovare il numero di articoli per fornitore:SELECT FornitoreID, COUNT(*) AS articoli

SELECT FornitoreID, COUNT(*) AS articoli

FROM Articoli

group by FornitoreID

order by articoli desc

​

-- DISTINCT

-- La parola chiave DISTINCT viene utilizzata per eliminare i duplicati dai risultati di una query.

-- Può essere utilizzata con qualsiasi colonna, ma è più comunemente usata con le colonne utilizzate per il raggruppamento.

-- Se utilizzata con una funzione di aggregazione, DISTINCT elimina i duplicati solo nei valori utilizzati per l'aggregazione.

SELECT DISTINCT FornitoreID

from Articoli 

​

-- HAVING

-- La clausola HAVING serve a filtrare i gruppi creati con GROUP BY.

-- Filtra i gruppi in base ai valori aggregati calcolati per ogni gruppo.

-- Può essere utilizzata per includere o escludere gruppi in base a criteri specifici.

SELECT FornitoreID, COUNT(*) AS articoli

FROM Articoli

group by FornitoreID

HAVING  COUNT(*) > 4 

order by articoli 

​

-- UPDATE

​

UPDATE Articoli 

SET Descrizione = 'Descrizione aggiornata'

WHERE IDArticolo=2;

​

update Articoli 

SET Disponibilita = 0

WHERE IDArticolo=16;

​

-- 1. Aumentare il prezzo del 10% e la disponibilità per articoli con un prezzo inferiore a 50 e una disponibilità inferiore a 10:

UPDATE Articoli 

SET Prezzo = Prezzo * 1.10, Disponibilita=Disponibilita+10

WHERE Prezzo <= 50 AND dISPONIBILITA <=50;

​

-- 2. Aumentare il prezzo del 5% per gli articoli non disponibili:

​

UPDATE Articoli

SET Prezzo = Prezzo * 1.05

WHERE Disponibilita = 0;

​

​

-- DELETE

DELETE FROM Articoli

WHERE IDArticolo=17;

​

DELETE FROM Articoli

WHERE Disponibilita =0;