basi_di_dati/compitino/secondo_compitino/test/data_and_schema.sql

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Acquisti" (
	"IdFattura"	INTEGER NOT NULL,
	"IdIngrediente"	INTEGER NOT NULL,
	"Quantità"	INTEGER NOT NULL,
	PRIMARY KEY("IdFattura","IdIngrediente")
);
CREATE TABLE IF NOT EXISTS "Birraie" (
	"IdPersona"	INTEGER NOT NULL,
	"Soprannome"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("IdPersona")
);
CREATE TABLE IF NOT EXISTS "Birrifici" (
	"IdBirrificio"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"Nome"	TEXT,
	"AnnoFondazione"	INTEGER,
	"Motto"	TEXT,
	"Stemma"	BLOB,
	"CapacitàProduttiva"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "BirrificiBirraie" (
	"IdBirrificio"	INTEGER NOT NULL,
	"IdBirraia"	INTEGER NOT NULL,
	PRIMARY KEY("IdBirrificio","IdBirraia")
);
CREATE TABLE IF NOT EXISTS "Clienti" (
	"IdPersona"	INTEGER NOT NULL,
	"IndirizzoSpedizione"	TEXT NOT NULL,
	PRIMARY KEY("IdPersona")
);
CREATE TABLE IF NOT EXISTS "Fatture" (
	"IdFattura"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdBirrificio"	INTEGER NOT NULL,
	"IdFornitore"	INTEGER NOT NULL,
	"Data"	TEXT NOT NULL,
	"NumeroFattura"	INTEGER NOT NULL UNIQUE,
	"Importo"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "Fornitori" (
	"IdFornitore"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"RagioneSociale"	TEXT NOT NULL UNIQUE,
	"PartitaIva"	INTEGER NOT NULL UNIQUE,
	"Indirizzo"	TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "Ingredienti" (
	"IdIngrediente"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdTipo"	INTEGER NOT NULL,
	"Descrizione"	TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "IngredientiRicette" (
	"IdRicetta"	INTEGER NOT NULL,
	"IdIngrediente"	INTEGER NOT NULL,
	"Quantità"	INTEGER NOT NULL,
	PRIMARY KEY("IdRicetta","IdIngrediente")
);
CREATE TABLE IF NOT EXISTS "Note" (
	"IdNota"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdProduzione"	INTEGER NOT NULL,
	"Testo"	TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "NoteDegustazione" (
	"IdNota"	INTEGER NOT NULL,
	"Giudizio"	INTEGER NOT NULL,
	PRIMARY KEY("IdNota")
);
CREATE TABLE IF NOT EXISTS "Persone" (
	"IdPersona"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"Nome"	TEXT NOT NULL,
	"Cognome"	TEXT NOT NULL,
	"Email"	TEXT NOT NULL,
	"CodiceFiscale"	TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS "Prenotazioni" (
	"IdCliente"	INTEGER NOT NULL,
	"IdProduzione"	INTEGER NOT NULL,
	"Stato"	INTEGER,
	"Quantità"	INTEGER NOT NULL,
	PRIMARY KEY("IdProduzione","IdCliente")
);
CREATE TABLE IF NOT EXISTS "Produzioni" (
	"IdProduzione"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdRicetta"	INTEGER NOT NULL,
	"DataProduzione"	TEXT NOT NULL,
	"NumeroLotto"	INTEGER NOT NULL,
	"Stato"	INTEGER,
	"NumeroBottiglie"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "Ricette" (
	"IdRicetta"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdBirrificio"	INTEGER NOT NULL,
	"IdCreatrice"	INTEGER NOT NULL,
	"IdRicettaMadre"	INTEGER,
	"Nome"	TEXT NOT NULL,
	"DataCreazione"	TEXT NOT NULL,
	"Stato"	INTEGER
);
CREATE TABLE IF NOT EXISTS "TipiIngredienti" (
	"IdTipo"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"Tipo"	TEXT NOT NULL,
	"UnitàDiMisura"	TEXT NOT NULL
);
INSERT INTO "Fornitori" ("IdFornitore","RagioneSociale","PartitaIva","Indirizzo") VALUES (1,'Luppoli per tutti i gusti',121457,'Corso Torre 125'),
 (2,'A tutto Malto',147963,'Piazza Dante 16'),
 (3,'Lievitami',236541,'Corso Pavia 33');
INSERT INTO "Fatture" ("IdFattura","IdBirrificio","IdFornitore","Data","NumeroFattura","Importo") VALUES (1,1,1,'2020-01-03',125,77),
 (2,1,2,'2020-03-12',116,40),
 (3,1,3,'2020-03-18',78,82),
 (4,1,3,'2020-04-02',96,60);
INSERT INTO "Birrifici" ("IdBirrificio","Nome","AnnoFondazione","Motto","Stemma","CapacitàProduttiva") VALUES (1,'Pirati Rossi',2020,'Arrrr','/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAAkGBxMTEhUTExMWFhUXGBgYGBcYFxkYGBUeGhoaGBcYFxcYHSggGB0lHRgXITEhJSktLi4uGB8zODMtNygtLisBCgoKDg0OFxAQGi0dHx0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLf/AABEIALgBEgMBIgACEQEDEQH/xAAcAAABBQEBAQAAAAAAAAAAAAAEAAECAwUGBwj/xAA+EAABAgUCAgkBBwMDBAMBAAABAhEAAwQhMRJBUWEFBhMicYGRofAyB0KxwdHh8RQjUjNighVjcpKissIW/8QAGAEBAQEBAQAAAAAAAAAAAAAAAQACAwT/xAAeEQEBAQEAAgMBAQAAAAAAAAAAARECEiExQVEDE//aAAwDAQACEQMRAD8A8UTLghKEjZ/nGGDRNAONvnrGLTiyWhHD+ItRKSQAw8YnTUBUc+xJg40oSCS7DFwCfSMXqHASei9dw4JPC3zMKZ0SEi5IPhBSarT9LjzfjFqEJHemH6g7ZJtB5dDIBp+i9QJCHAyXx4wQro+Wn7oUeO3kCfcwV/W2AYAWYDezYG+eXjEP6gEEB9t3bzi3oegKqMOwS52AxD/9Md+7jdrfP1jQMsk5fd+A4xZ2xwXbe/MXBh8qmKaUC2n2zEChOw9hbxjTnpZyd/Enx5bQMmnc58eXnGpQzDL5RamQ9tN4vMkizP8Al4wdSUqzgYBPI2z6Q3oMRdMeEQEkmN9fRyjcBwM+MVf9NUl7eP8AEHnDlYxkRIUiuEbcqgfOLxYKcAszWeLyWVzcyQREAiOgqKSzt74iqmpkq+7+Nvf40PkmIZZhtEdAOiFKwLct+d4ZHRTfUoD38sRecXtghES7Ex0cuiAfSlJYZKiDe9gQBGXMDH1HL1inWq+gCZRMJctoNMrjEFSodGg+yMNoMGLlfP4iBT6Q6tDNCaCOyhdnFp0K0KLlJbMQI4RJXDwjCiRNCh4USXJXBKJ43z8tAgES0+sFjTWkz0s3v+P4wR2rpZ2TxjEBguRPteOd5a0WmWzac8f0higBiXd87fxDyp4f48XykarZ3vBoxWZG778Ytl0wVbPy0ECU9gDY7Bxy2i2SpKCHI5uz8GvB5DxVyqcOHL38bC+YK7EMw35e7759ovlSe0SQAAQrTfyJiE9JltqJIL3Yta23hGd04qNIH+lzbn558YdVCQz8hy/9YjM6QQGBIAt55giRVS1J/wBRLjmLB/Xyi9r0nRUpUck4dsHmCbPG6ujQtJWClgB5FsYvvAfRtWChk6VDfc258/jRdU1aG0Bac6tKdPm98/tHO7a1AVU0slR0txOPaKZKkrDjBvjzaCaicSAnSmYNy90+RF994EqCASlKFPYWGpuDgXEakCuplJYqYs3D9dv1jOVXoTgfhBaq/S6VBVrEN+UDTFyi5CCT4N6iOkg0LPrQrFvOAkhi4zGzTolqSdUpjsxzBEqgkK2V6g+8O4mfT1hwokjkY2KSjC092+TufjfMRenoqnSzlXn/ABGhRqlpASkd3cN7Y+NHPrr8MiodCsAolzfVYWjmK7ovSojY2xg8GHKOt6RqyE9x1eXMRzNVUrLkp829n4Wg4tVgQ0SmGL/PWBp9K5DN+vlE51Qv0xDUc11AHl+8ddrOK5nRxTZj6D0iIoS2MHj+UdVNpks+Sdm4bDZri0UJpAQHNnw74GP5jH+p8GCOjyL6Tys/42iCqfTm2w4846MLSBYEkWBza2WsP5iEykSpJADtkti7ecX+n6fBzFTSbszMPXjtFJpfbIcRvz5aUhiH5tjmXz+8BLlX7qgODOH54xnyjc7Z8WJUSGgRo36iSdN/BuYtveMmplx0560Yo0wok8NGkOlpci2PmIrWtLmLlS7d0jHgeECCTe8ZhWFQ8oSZyeMOijc2PrBCZah3SA/L9oriUoqb2DxrSJM0thIxbOOUALll7CNfoxRb6gwfPy8c+r+GI09EoqtMWGI3BS+karEXGzco0ZkqWU3SkkEDDnN24PYteEhacYzxDm17eJi5CGN1Pi+SXxd/COdtagykpw6wlwCQwc/4oew2i2opinSWLZbbhub7xXQEoU/FSr8WwwfNsRpTlCYb6g2xIAd8sN7G/OOVtlaYS+hpawFBIIPABh+m/tGD0h0VLQoAauYz6cI7cLSh8EKDgH0VjxB/iMysp0KOpkX30jhe/hG+P6WM3mOdpJEsKvqDbAvjnGlToDkIZjh7ke9otm0gPJhbl4j5mBpkgIw3KOvlrOY1EyAVAlJtZxZ/Q/jBtLKKl6JaFFasJSCVHiwFzGVQVepaUkZUBzudo+kujqGTKS0mWhAx3UgO3E5PnDONW48WPUeuUdRp135oHsVP7RXUfZvXKBWmUQeGpAJ8Bqj3mGjpOBr5endGTKdZTPlqQsYCksfG+RBNJJCbguSX/gR9F9K9FyahBlzpaVp5i45pOUnmI8V67dVF0MwFBKpSz3Ff/lTfeHvni2eoYyxM4jzEXyZpAOBn5eMyXWEWVbHzMXqZQIfPO0cLy0VR0qQTcfMO28Ztf0iFZNiA/kXEVVtHvsd+e3OBDRPZwbZhkiRmrl+NveBAzuNoN/6WeXz+YoXTFKs+UblgatLOXMSlOopvtf8AHBjaV0eBJCla1qv90KVxuOd7/wC2OZp5ikFwb+OI6ai6TQUAHL7Pfhv4+scu+b9NSnpkJKQUG/3rENvjlFKVthKRxvs1nLcQYumoSAFJCgXxpKWY2xZmMZ9SNRK7kbBLDLN4nwMc5Glc+a7uORa4Ns3ZsxkCWU2U7+WOHv7QcJYClKGBZs3BIINiHF+G/CBp6SVuHL+HsPOOnPoUDUDUkh8bcuPOM2an435bRpT03Ic2F39fOM5T4L+9478sUNo5Q8WCaeEKN+2RdLOCefLI4i294EnfU5sSbwWhn52gapTeCfKF9HrGq8bMyTqAsAfLx3jm6VfeEdTJsA+ef5xjsxnrlabKAIBzx84tp5BF0n8/yg3skqUXDXudvJw8RNKZd0kksP5aM6VJAcPazA532aCpa1EtZWGDXPBgzveAVTzqvb5eLpVQtCgtB76FBSSzsUlwb82tDiFImlw4Yhyx2c3txv7GCJ1UWDc9h/IzHRdc+iXIrpaB2c4BU1IuEKWH1j/aonyJ525kgKwWHADntGLzDqpE1ep1atJ4FwDvm+OEOa0uxx5b3f2iE6WwI1KG4Aa/r+sUgWJ1Ddjd84YZt+EPihIqATnwt+cU1KQQeHnf3gYz8bfP5hdsNvX2jUidH9nfRwnVoUv6JI7Q8CoEBAPn3v8AhHt3RteSFEY1KA/4nSf/AJBUcD1FSiT0eJwHeVqWqzlRClJQkcbAADiecdd0TSqlykBd16QVsSRqN1s+2omHfYdJLrgc2giXOScGOdXMtAqqtQuPL9o35jHXxndYOik1MiZKUMg6T/iofSoeB9nG8ZlF08bBUbtPWJXgxvZQ+dFywr6gH94oTJKboNuEa3W+nVKrqhDMBMUoD/as60tyZQjDVMIJL+W0efMaEIrUuygBf5aIzJCTd+LY9LRDUlYuPAvA3YMe67cP0EFhGTadQTqBBTYYud87RlrkFwTmNSnqw2kkg2zjwgqh6EXUzRJlJ1TCO6QwFrkknAD/AKRmbKWDJKWOAb3fMMjUllA+kekT+o0vo6jqJ9StK5y5a5UsAEpQqaCnuuHWq5LsGANsmPPFIIuXbwuef4xv4rLSldI60BBJF7tm+QNuEWTpp0jfgFZscuGfGPCMzs78D7Yic6eXZTePm+3i/lGLzPpqUps0ByWYE2dtVzsM31P4+cZFUtjY6RjJfFru7fp4QTVT+4+ok3ABLs6iebfrAqpmoF7sCBwudx4RvmK0JUrFuT9617/zFUxQD4B/Hze0VVCvJjxv4+wgdUwNHWRi1aJnMekKKgofBCjWAXSJJUAH9vzMTrxdvFzxgJKveCQzM/6QZ7KmWWLx1NCrUkK5Ry2lo2eiKgM0HQaq1kHyd7N8/WL5M8KDOXPJ2a/5RSuaGALH1+CHp0OX58GdxgARxrauop0ng/L9ICCVJsceEETSpN88Q/pnxh5hf0/nEalD1XqD0impo+yUAvsh2S0n7ySCEuOBSdPikx5/1t6EVSTTpcyVEmWs7NlCj/kPcebN1WrjSVKJrtLV3ZnNKt230lleRG8ev9L0MqplKkz0ulYfUnY/dWk8RkRfaeBCtUzP64hzVAjYctoP6w9W5lLNMtRcZSrAmJ/yTw5jb0JxzSMWuPG8bkgWLmWxEe05RWZZGC8TSHhxPT+qFST0fTJYkduB/wCs8r/KG68db6mTPEmQvs0iWlR7qSpRJOSoG1hht4F6giZNlS5KZStMqfrXNLBASHWEgm5XqOAMFzzv+0TqvPmTUT5MszO6EKCRcMSQptwdTeQjGeyI6j9cqmfUpkTyJiVhTK0hKklKSr7oAIsRcb5jresFX/T002foK9DMl2cqUEhzsHIcxyfUHqlOp5gqZ40EJIShwSNVipZdhbABO7xpdS+sH9auop5zKCytUsEWVKJ06DzAKedzwhThqrr7V6nSmSB/jpUfUld47DoTrVNnUa6hACFyyXH1JOjSpWnGUuBwJ3a+f019lc4LJply1IUSwmFSVJ5OEnUOdjy3jreiOp5k0X9MlSdek6lMdKlKupxljji0OByH2q1g7WQv7ykKSeYQoFP/AN1ekcYmeFCNHr4Jy6taZgSlUvuhCFFSU2CrKIBU+q5YeAaOeEhQ5N83gLQUotbON/P5zh6eYQR7gkX92/kxlpnrSb+cEpqwbE39oLFo4hK8C4zHonVPqdVU06RVTJkpMtPesolRCkkabhrg8fWLPsz6rUs+nNXPV2hClpKCWly9O6wPqOllXsyhazx1PWTrBLkywVkyxpUJKQGnTCpBQClGZaBqfUprpGLOePrat/HG/bAQuZTzRNOlSFIEsksChT60pZjq1s++lMebTJwtqUd8McON/HjB3SYEzUVLUpbfUVFRPmbtaMCqpl5v8/iCXy+TmL5cwlR7zDa5LDDHnFs+cDYDby4wBJUUkk/PIwUuYxA442hsQOXqUgObX9PARFSlCzHl7lvnCDqeSBjBTfgbe37xTXSgne42e44w77DHUHgZ9rQYpQMBzI6xktUKK4UKWpvBMuWpnY6dzt6wOkkEZHCCEE5L+bl/P0gqKYnn6hj6Q8iYxeL+ydHzYcciBtDfPjwJvS6hwGDvt+Xt+EaMqYnBcAA2e78+XpGFSKBF3/L13jRFOlTAL44Dk+I1frHKxqCVkE5xYX5Pe9oulIBI3tkXeA5NJfTqBvcEadNzkh/0tB8oLSR3SQ2UqSTazNnfgxjNaKota+WtfGwj0j7OOsKJqE0U0tMSGkqV99Iv2finbiPC/msxT/U6QLd5JSxYbkNud4hJnhwUKuC4IUAQQbEHIINweIjXNZr2TrV1cE6WZcwOLlKh9SC1lJP5bx4t0xRzqWYZc0f+Km7qxxHA8Rkej+ydSeu6apP9PUkJqEixcNPAGQBhdrgZyNwCesPQkipQpExIUPdJ2KTsY2HhAmJOREhoeD+tPVKdRnU+uUT3ZgGHwFj7p9j7DngsiNJ6P9nXWAyV/wBMlCZgnTBo1TOzCFkNdWlViyQzPYcY7zorpucrpA0VTIlyjpKklK1K1sHGkkDUCArYfSeBEeBSqxQIIJBBBBFiCLgg7EGPTel+mF1lDJrkd2ppjqJH+0/3G5OkLA4ON4k9U6a6CM6SuUiZ2ZmAJK2chJPfADi5S4fZ457oL7Pf6au7dEz+wkf20OSt1I0qCybM5JDcuF6+r3XpM+WhS0qQtUszCACoEJVoUUkZvgZY7xoyuuMlWjSpStcoz0MhXeQNNw4z3093N4vQ9tTp+vMkS5clCVz5ytEpKiybDUta2vpSA5a9wN44brb1wraSaacKkKOkEzEyVJKCoOwCpigWDXI3hdOdY9db0ZVo/wBBXdSs5BmEoUlQ+66SPQ8IwvtcnpTWBQF1ykKUeYKkA+iRblGbfwyOVqlkupXeWpRUSXcvck3vf8YehmgpIYDOB6wNIWlQ7xIDZ4ccPxjU6v8AR0yqqE00ksVO6jhIF1KObBscSBvHOxsHT9FqqJiUS0Fa12SkC9sk8Bz2g/rF9mFbISFvJLh1ALU4bxSH53j3Lqx1Zk0SNMvvLIAXMV9Sm2/2pf7o9zeBOulemVLebUSZEvYzEqWqZxCUpUkhv+Xlvvxsmsbtef8AUCvTQSFSppBCtUyaT9I7oCrZACUjPM8IweuFaldUld9a5EhS3PeSoJ0KB4HuP5xk9OV1MletEztgk6koShcpBINtRWoqBBvfxA3jlZvSS1rK1EaiXLWHAADgAw8o5889X5auR0lXpcMXILbvfcmx4QOuSWJKnsMkP5cd4GoekNTBRA8sgc4smSSrCgbvcMRjjbaDMIWtkh3seJf1t8/TMmrLnhteNyf0ctaQAoA6ma/esOFh4xn1FEUsWGGYnk5Mb5sFi3o9bhItgZ3t429OMRrkm7gsWFj+sSkgAI02YOC+OfzlFs1QyC4JJvYWb18Tx5RfaYk2W1rwHMTcxs1UzUl2xY8G/I/tGPOW5jryzVWmHhQo0DlV4vkDjFDXgiUq3D5yiTQGpAUliH+p03DscnG3i8DG2T4QkT+6b+TO9+Pv5RSJufmIzhF05NyLjdsD1HtGshZH3bnex3PEBjyvjEY/aMAxB4e3teLVVG4t4Hz/ABjNijel1KwAQyVXvvYuQctxa0WILhR3FwS34tffYRh0NZdsZY4u3E+Hv5xp01YZgZZUQ1iblrsHe/1M3KMWY1KNlzkpuCxfbU7cACIHqJwKVai7EfUASLGz5BFzkQ1YtJ+lWL3Zs2x/5G/+2BJkoqZIDOcsrJte2PH+aRVIzmZSHDHIJ7pGGe4PP0j0Xqn127UCTUKAmiyVtpE3gDsle3A7ZaPNVU7u6y7ByxID4vxy7RGdTi4U6uF/qLWsbK8zG8D2+ZPKkE6BMF9ctrrSf8XsS33TYuRHA9O9TETE9vQnWgkvKe4Z9QQTcEEF0KYhj4RZ1X68GSBJqHWhwBMLHsjb/UQkJMwB2d9Q5x2VRR6k/wBTTTUJUplOGMmeP+4kWJswWGI8BpjHuF4kZVyCCCCxBDEHcEZB2aPSOoOldDMRrF1zEqDPp1AAOBdm35GHrOj5FYrRUI7CoI1JUGcgMyZasTU5cEagDcJzHPVnVmuoFdvKdSb99AJDJOJqNs8xwMNujMaHRlFXU8pUxc4I7L+0hCpWtKkko7yVgizkMS+C7RpopKyXMKU1UkmlpwX7EvpUS6T3s/2ElxxxG30F1opp0oKVNlylt3pa1hLFw5QVfUMkHmxYwUnp6kfSKqTgt3wQdmdgNsDHlGPKnI4r/wDnqtQpJRn9pJcTiAgJTJuF/VlZOtQGPADEftPmPUShv2Tm/FSv0fzjY609b5cpBTTTErmlwSk6kSxx4FWwA5nDA8n0R1dqqxeshkzCCZ0xxqfdIN12sGtYBxGpfurGD2xxnDAZMer/AGcyRRS5tXUJKFKSkBJDrSgZ7oD6lq0jTksLOYjT9GUnRxQEJ7epU2kWKy5Z2Pdkov8AWb4DqLCNX+hUSJ85aVKS5AdpMhslL5Nm7RXG2kFoL3+LHa9D9JDskdowWRqWOCld5Q8iSPKPnTrd0suorJ65ilLPaKSm9kJSohITwAG3MnN46frR177qpVKTdwZuG2PZ7k57xPgN484m/OMalt+R8HrmGN+Bff8AGBABc/iP3MSnHl+8DpW0bkAykLmDET9TueRGxZ9wIAQSNJ+WPwQdTqcO+Lt+45xmmHl1pH1OBZr2F3LfjmLKuqDWPdtYYHhe2/y8DzyDc7AgWzvxzFZI0nSX/wCLXYu8GFWJ5BurPH1fxeLxOBFyzXx+F4AmJwSfA8Bvnk0Opbk4YeG28awatrpqbsTceA57X/eMsmNCq7zAD54QFMSGjXIqqFDtCjQOqJpmNtv5xSDE0mILn42+fPSHkquPn4RBc19hEQYi0VLFhci1gTs7fOcXAlQYWGbE8MPvGdKBfO9jBaZjD0fh8/aM2FYgOAHtueGeXtE0TB3rNcsx07b3gVKr5O/v5Zi3QSx4cx/JgQ9NT9OkljZixFgx4PlNn35ReqYSbqQdmVYvhjkPfHKMiXMIfk+9uOMN+MXLnFWkggnG3dG5+CDEKnqY4Yu+rLku5KsPvj2hOQo2yXF9XMs5cbflGfLqDvucuQcMMZ/mPTvs9kyxTqmd3WZikqmOoGUEoSQGSTYuovyL4DHVyGe3BVDKGk2VbumxHIPc3N/KNDojrBUUZCZagUFypCvpVsSU/dOe8ON3j07pToiVWy9KkpWBqKZgb+2O9pCVgjSzjKgC18iPJelqIyJkySsuUrYLF0q4aVCxSX9YObpseqdX+sNLWpMuYhKZhcGUu77gyyQAWJdx3t46dEmZKB7MmcgJcoUoCcnLaJivrdiwWX312Ajwqm6BqJtyns0370wlADOQQPqNr2G8eqdErnUkj+9Upn6EkkLBSUMMCcXJIZu8CXtbMZ69JpVXVWhrFPp0zRdWn+3MSTd5ks5uMkX2MclXfZjOlrSO1Bpx9S0SypYD3BlJIP8A6k8xHVUnTlJVgaiyh9Ci6VOzjspqSAvi6FPxAiVeKyQ5lLTOQ30TVaFpu7JnJBSeHeSDxUS8HlixldGdA9H0wSoBM6YW0KU0xSin6tEtILKfglxh4OmTJ84sjVJQCQR3TNVkOlN0ywSzE6rfdDRnK6VlLMvXqkzToDzUrkzCkjUQlYOlYsLalC8C18yvmJ0yZ4RcpJUAlag9/wC5LsjP3Ug4uIxv61h+kq2mogUhSRMKtRQFFc5Rb6pinNzZiogcNo4fp/rTUVPdfRK1WlpLvzWpnUXvsB7xXX9AVEo6lyVaclSRrBOXJS7cb3vGdQUqps1MuWcqYnOkFnUW4X9G3jpzJPbN0HMnNnLm0VGYdvGPUKHotCJREvSkAkHUk6ph2KlHJyWukOdsct1x6PCZaVlOmY6Q9g4OqxG5s/JjDz/SW4ry5Vcy3OEiWMxSlRcCCkrY3DeIf5iOrKxMk7AFhfkB8EEoqFJ87t5EBjtmKZE3ukDS5GzD1tw/CJhG54fic8rmMUxTPTkODggXu+175ivWAPHIw2R+YhGa3PPMw0qWbEuXti8KPMlDSDyduMDM+ALe/lBtTNBbSOG2eefOA1LvwvDBUJj33aB1qvF85RwcZ5eUCqJjUBoUNChRoUKHeIJI5+USChwiuJJVeJL5dm29uMXGY9rcrYv7/tFQXb584RbKWSCG+ZgrSMpJIJ5wVKQ5LKHgfP8AiArjdotTPIObN+nrBUMq0hxfvHO7AFg/kBwaNDq51fn1RIkpsGBmKsgPcByMs58vB8aVNBBBfi/7fznlHoH2a3CpSVqSSsTFXZISlBKmLly6GZjYkWJDZ6uQz3WLU9Tly1ygpcspmkgKChYh3DEh1aQFBiQygXDx0XQnVGokKmdlVJQP7ZGlAmJmJLjXpWWtoXdiq0aXWXpaVKVJEiX2k6WpJwShCAVCY6lnQCvvAsRsTgRr0/T9NMliYlckFwAFqSiZLA0sAVFgLO3Oztbnerhxz/V+mVTKNZOCJqJh7OelUtjLUCQicQpLd0guQBpBNm0kafSgQuZ/UImplT5QKC0orlCXpStQKQoYU51Jvc3LWJmdZqFUuaiZNSETEsti9ilgElL6QxGS9iLBjAlIRWUE2pRpTMmS1amdu0QBqtlipJa/3sPGdvyVVNTVc8gzZqaeWe8lMpGuY1wy1r1BJCDcMpv+Nt401BTd+omJmTMgzVGcscNKblHBkgC5xt5T0p1wrKoEKm6EqUCUSgJabkf4DUdvqJxA1OlNw7Agkm/k5bGX8X4RrxwPSOlOukkqPY0+oEEPMYJID5lgHU99/Hly6+uM+WojUjQbdktOpAKnDIIOpAHItbEYCqoBgzFi6rM4ADANbAHkIaadVmBub4JLZd8XhkTr5fXemmpAnCaFanIU0yWSCHIFrljcgXUrFo1pXR9MUJXSLmSwQdRp1BUve/Zl0Oo8BhxHlYp9JGdrNji3K8UoX2feSVJU+UqKSNzcX4RX+c+lr1OtqKqjQVsienvOyly1JJYOZY1BZsXAaxNooolqTVCpJlrWuUQEoS0sI/1BdyVO31EjHANHG0XW+ekHtCJyf+4bjj/cHe9SdxG8ahVP0ehaT3kyhd8FSdKQ2SXWMcOGcXmz0ZRHSlHUVMzt6VQlokrAkjTZc0kImKwSACVC4LiWs2DuDX9UKmfUIlTJ4USNSjoYJfDJBuTcvbB8I2+jetlCmXLT2qwyQnSJSlBIASNJdI1WDO8WzvtJpdXZSkzZKV9xVUpCVKlDTpTMlykElZfSbkEB2BMPE61XF/VD7G0TUqmVU9ZRqUJQlaUlaQWC1FQUwJBYDZi92HnnXDow0dVPpdRV2a2SWGrSQFodgA+hQdrONsR9DdTZyaeilIVWSqmWlKUypktGgaEgJSk99Wohs2PEPePBftTmg9J1KkqC9Skqdro7iRp8RpHk0d/Tm5ftAWYMYqmzVWGbCK1K+ftDJVClsufxBghNUlstFH9RbH7QMs+UGahYWDA5BirziSCYcWpTVRUqLFkmKVQxIwoeFEEYd4aFCktUIRERKJLH+cIIlK+fPCBBFoN+MZpWzLlxCIcMIlLQTYfpBUilJYM5g0hpVmPL54xrdFVM2UoqlK0qAIJABcEFwQQXBbBHCLU0A03tpL4uWYG/m7cojLkJOQz7342Y3a34CM26Rdd0tVTk6JigJerWUJTpBI1Zbe5+ZFp5RCfc3wzueXhtmLp0wbOOYNi1sbcYoFSUhwr0Lb5giVyqJZcm4YEscWuCQ7O48o7/AOzmalSFyCooKZiZmlBJ1oIbswkC3eIcg4XtHA9pqUVAspmsSN9xwgvo6Wy0qC1BYLhQLKBG4L2y8HU2KPWR1Oo5YmBMhHZK1FSiQTLe9lfcCEhwx3vxjxhiQHsWYube2N46PpDpqoKVy5k+YpBsQVfU33VkfULCxcfngqDjbJ2fgfx/OM8zPkoaCUgu5fZjt8vfaLghiBqvkXYp3zwyMteCainSmWO6GubY4G/n6eMUUskE6dQAaz88+vzaNag1QTdyPE2fe98+MA1aWGQzO74flGnLSTq7zEXFmDaT3tRNjktbe3AcaAO8QX4EWA5bQyh6nK6p0nYpVMCJdOgpVrnBMvYK7y7FQLgEOX24RxfW/pSUtfY02tUlK9ZmEN2ii4GkZ0i7arm9g1+dqJ4J+olrDkODOeR2gZM4CzkD29IOeMNotLhI4bHB8h8x5RSpO23z56RXMqLAOYEMxQJ/eNyM60KfpKbKJEmdMlg5CVEA7YFnZr5gCbPUSXLl3J4k5PMnPnEFTIg8awJgxZoPCISReLpk0MwiSmaXihRh1LiBMISEIGIvDQpatcVvCMMYkUNChRI4hQ0KJHh3hoeJJCLkscRSIkmAjiezYvkPvbGYskdIEGyXPz3gGYvc3PExYioIPwRnENXXrIZiBvm/rFf9YsHe/wA2gTU+IJkp4Z2gzEkqoUWckeT/AI+EJCxuSeL+MEJl8m8/jQTSdmgaiATz/KDSqpZYfe53G0ak0IQHJv4CAavpcYQG5wAqpJuf5gy1CJs8HiA7hmv4xALIU6QACDlvNxcPClqBJKgw+frBMmcAXDA2Y2wPDfEVMRUuYpgkBtioAO5ZzszgF4tnyJqBlAN3YkthwNJ4c8vAU+sKQzlnDd5zbB5GA5lQYpFompqC31ZAG928uQb+YAXO33hlK+bxBXhG5GVhmneIbRAmIvGsSzUYipUQhogm8IGIQhClwMIrit4ZRgRlGGhoUKKHhoeJFChQ0SKFChRIoUKFEjw8KFEih3h4UCOLw/ZGFCiK2WOUXds0KFAkl9IKigzSbwoUWJJBG8Wy54G2cX/GFCgRkL3PnEZ84G2BChRYlK1QxmPDQoUZSoYrhQoQi8M8KFEjPCeFChRnhQoUSKFChRI0KFCiRQoUKJFChQokUKFCiT//2Q==',0);
INSERT INTO "BirrificiBirraie" ("IdBirrificio","IdBirraia") VALUES (3,1),
 (4,1);
INSERT INTO "Birraie" ("IdPersona","Soprannome") VALUES (3,'MaVe'),
 (4,'GiuLe');
INSERT INTO "Clienti" ("IdPersona","IndirizzoSpedizione") VALUES (1,'Via Ramazzini 14'),
 (2,'Corso Milano 2');
INSERT INTO "Persone" ("IdPersona","Nome","Cognome","Email","CodiceFiscale") VALUES (1,'Antonio','Rossi','a.r@g.i','NNNNRRRR'),
 (2,'Enrico','Bianchi','e.b@g.c','EEEBBB'),
 (3,'Giovanni','Verdi','m.v@l.i','MV'),
 (4,'Giulia','Lelli','g.l@e.c','GL');
INSERT INTO "Acquisti" ("IdFattura","IdIngrediente","Quantità") VALUES (1,1,12),
 (2,2,7);
INSERT INTO "TipiIngredienti" ("IdTipo","Tipo","UnitàDiMisura") VALUES (1,'Luppolo','g / L (mash)'),
 (2,'Malto','g%'),
 (3,'Lievito','g%'),
 (4,'Zuccheri','g%'),
 (5,'Additivi','mg%');
INSERT INTO "Ingredienti" ("IdIngrediente","IdTipo","Descrizione") VALUES (1,1,'Luppolo verde'),
 (2,1,'Amarillo');
INSERT INTO "IngredientiRicette" ("IdRicetta","IdIngrediente","Quantità") VALUES (1,1,4);
INSERT INTO "Ricette" ("IdRicetta","IdBirrificio","IdCreatrice","IdRicettaMadre","Nome","DataCreazione","Stato") VALUES (1,1,3,'','Bionda decisa','2020-01-01','');
INSERT INTO "NoteDegustazione" ("IdNota","Giudizio") VALUES (1,7);
INSERT INTO "Note" ("IdNota","IdProduzione","Testo") VALUES (1,1,'Troppo freddo ad aprile-maggio, meglio farla d''estate');
INSERT INTO "Produzioni" ("IdProduzione","IdRicetta","DataProduzione","NumeroLotto","Stato","NumeroBottiglie") VALUES (1,1,'2020-05-01',12447,NULL,12),
 (2,1,'2020-04-15',12443,0,0);
INSERT INTO "Prenotazioni" ("IdCliente","IdProduzione","Stato","Quantità") VALUES (1,1,'',4),
 (1,2,NULL,6);

CREATE VIEW Query_a
AS SELECT r.IdRicetta, r.Nome
   FROM Ricette r
   JOIN Persone p ON p.IdPersona = r.IdCreatrice
   WHERE p.Nome = 'Giovanni';
CREATE VIEW Query_b
AS SELECT fa.IdBirrificio,
          COUNT(DISTINCT fa.IdFornitore) DiversiFornitori
   FROM Fatture fa
   WHERE fa.Data >= '2020-01-01'
   GROUP BY fa.IdBirrificio
   HAVING COUNT(DISTINCT fa.IdFornitore) >= 3
   ORDER BY COUNT(DISTINCT fa.IdFornitore) DESC;
CREATE VIEW Query_c
AS SELECT fo.RagioneSociale, SUM(fa.Importo) ImportoTotale,
          AVG(fa.Importo) ImportoMedio
   FROM Fornitori fo
   JOIN Fatture fa ON fa.IdFornitore = fo.IdFornitore
   JOIN Birrifici b ON b.IdBirrificio = fa.IdBirrificio
   WHERE b.Nome = 'Pirati Rossi'
   GROUP BY fo.IdFornitore, fo.RagioneSociale
   HAVING SUM(fa.Importo) > 10;
CREATE VIEW Query_d
AS SELECT b.Soprannome
   FROM Birraie b
   WHERE EXISTS (SELECT *
                 FROM Ricette r
                 WHERE r.IdCreatrice = b.IdPersona);
CREATE VIEW Query_e
AS SELECT DISTINCT p1.IdPersona, p1.Nome, p1.Cognome
   FROM Persone p1
   JOIN Prenotazioni pre1
        ON pre1.IdCliente = p1.IdPersona
   JOIN Produzioni pro1
        ON pro1.IdProduzione = pre1.IdProduzione
   JOIN Ricette r1
        ON r1.IdRicetta = pro1.IdRicetta
   WHERE NOT EXISTS (SELECT *
                     FROM Prenotazioni pre2
                     JOIN Produzioni pro2
                          ON pro2.IdProduzione = pre2.IdProduzione
                     JOIN Ricette r2 ON r2.IdRicetta = pro2.IdRicetta
                     WHERE pre2.IdCliente = pre1.IdCliente
                           AND r2.IdBirrificio <> r1.IdBirrificio);
CREATE VIEW Query_f
AS SELECT r1.IdBirrificio, pro1.NumeroLotto
   FROM Produzioni pro1
   JOIN Ricette r1 ON r1.IdRicetta = pro1.IdRicetta
   WHERE pro1.NumeroLotto = (SELECT MAX(pro2.NumeroLotto)
                             FROM Produzioni pro2
                             JOIN Ricette r2
                                  ON r2.IdRicetta = pro2.IdRicetta
                             WHERE r2.IdBirrificio = r1.IdBirrificio);
CREATE VIEW IngredientiUsati (IdIngrediente, Ingrediente,
                              Usati)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(ir.Quantità) Usati
   FROM IngredientiRicette ir
   JOIN Ingredienti i ON i.IdIngrediente = ir.IdIngrediente
   JOIN Produzioni p ON p.IdRicetta = ir.IdRicetta
   WHERE p.Stato = 0
   GROUP BY i.IdIngrediente, i.Descrizione;
CREATE VIEW IngredientiInUso (IdIngrediente, Ingrediente, InUso)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(ir.Quantità) InUso
   FROM IngredientiRicette ir
   JOIN Ingredienti i ON i.IdIngrediente = ir.IdIngrediente
   JOIN Produzioni p ON p.IdRicetta = ir.IdRicetta
   WHERE p.Stato IS NULL
   GROUP BY i.IdIngrediente, i.Descrizione;
CREATE VIEW IngredientiAcquistatiTotali (IdIngrediente, Ingrediente,
                                         Totale)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(a.Quantità) Totale
   FROM Acquisti a
   JOIN Ingredienti i ON i.IdIngrediente = a.IdIngrediente
   GROUP BY i.IdIngrediente, i.Descrizione;
CREATE VIEW Inventario (IdIngrediente, Ingrediente, QuantitàTotale,
                        QuantitàDisponibile)
AS SELECT iat.IdIngrediente IdIngrediente,
          iat.Ingrediente Ingrediente,
          (iat.Totale - COALESCE(iu.Usati, 0)) QuantitàTotale,
          (iat.Totale - COALESCE(iu.Usati, 0)
            - COALESCE(iiu.InUso, 0)) QuantitàDisponibile
   FROM IngredientiAcquistatiTotali iat
   LEFT JOIN IngredientiUsati iu
             ON iu.IdIngrediente = iat.IdIngrediente
   LEFT JOIN IngredientiInUso iiu
             ON iiu.IdIngrediente = iat.IdIngrediente
   WHERE iat.Totale - COALESCE(iu.Usati, 0) > 0;
COMMIT;