{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Lab - RDBMS"},{"metadata":{},"cell_type":"markdown","source":"## Examples Slides"},{"metadata":{},"cell_type":"markdown","source":"We start by creating the tables."},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE Wines (\n wineID TEXT,\n vineyard TEXT,\n year INT,\n degree FLOAT,\n PRIMARY KEY (wineID)\n)","execution_count":13,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE Producers (\n producerID TEXT,\n name TEXT,\n city TEXT,\n PRIMARY KEY (producerID)\n)","execution_count":3,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE Harvests (\n wineID TEXT,\n producerID TEXT,\n weight FLOAT,\n PRIMARY KEY (wineID, producerID),\n FOREIGN KEY(wineID) REFERENCES Wines(wineID),\n FOREIGN KEY(producerID) REFERENCES Producers(producerID)\n)","execution_count":5,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE Clients (\n clientID TEXT,\n name TEXT,\n city TEXT,\n PRIMARY KEY (clientID)\n)","execution_count":6,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE Orders (\n orderID TEXT,\n date DATE,\n clientID TEXT,\n wineID TEXT,\n quantity FLOAT,\n PRIMARY KEY (orderID),\n FOREIGN KEY(clientID) REFERENCES Clients(clientID),\n FOREIGN KEY(wineID) REFERENCES Wines(wineID)\n)","execution_count":8,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE Deliveries (\n orderID Text,\n status Text,\n PRIMARY KEY (orderID),\n FOREIGN KEY(orderID) REFERENCES Orders(orderID)\n)","execution_count":9,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":".tables","execution_count":14,"outputs":[{"output_type":"execute_result","execution_count":14,"data":{"text/plain":"name\nClients\nDeliveries\nHarvests\nOrders\nProducers\nWines","text/html":"
name
Clients
Deliveries
Harvests
Orders
Producers
Wines
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":".schema","execution_count":15,"outputs":[{"output_type":"execute_result","execution_count":15,"data":{"text/plain":"sql\nCREATE TABLE Clients (\n clientID TEXT,\n name TEXT,\n city TEXT,\n PRIMARY KEY (clientID)\n)\n\nCREATE TABLE Deliveries (\n orderID Text,\n status Text,\n PRIMARY KEY (orderID),\n FOREIGN KEY(orderID) REFERENCES Orders(orderID)\n)\n\nCREATE TABLE Harvests (\n wineID TEXT,\n producerID TEXT,\n weight FLOAT,\n PRIMARY KEY (wineID, producerID),\n FOREIGN KEY(wineID) REFERENCES Wines(wineID),\n FOREIGN KEY(producerID) REFERENCES Producers(producerID)\n)\n\nCREATE TABLE Orders (\n orderID TEXT,\n date DATE,\n clientID TEXT,\n wineID TEXT,\n quantity FLOAT,\n PRIMARY KEY (orderID),\n FOREIGN KEY(clientID) REFERENCES Clients(clientID),\n FOREIGN KEY(wineID) REFERENCES Wines(wineID)\n)\n\nCREATE TABLE Producers (\n producerID TEXT,\n name TEXT,\n city TEXT,\n PRIMARY KEY (producerID)\n)\n\nCREATE TABLE Wines (\n wineID TEXT,\n vineyard TEXT,\n year INT,\n degree FLOAT,\n PRIMARY KEY (wineID)\n)\n","text/html":"
sql
CREATE TABLE Clients (\n clientID TEXT,\n name TEXT,\n city TEXT,\n PRIMARY KEY (clientID)\n)
null
CREATE TABLE Deliveries (\n orderID Text,\n status Text,\n PRIMARY KEY (orderID),\n FOREIGN KEY(orderID) REFERENCES Orders(orderID)\n)
null
CREATE TABLE Harvests (\n wineID TEXT,\n producerID TEXT,\n weight FLOAT,\n PRIMARY KEY (wineID, producerID),\n FOREIGN KEY(wineID) REFERENCES Wines(wineID),\n FOREIGN KEY(producerID) REFERENCES Producers(producerID)\n)
null
CREATE TABLE Orders (\n orderID TEXT,\n date DATE,\n clientID TEXT,\n wineID TEXT,\n quantity FLOAT,\n PRIMARY KEY (orderID),\n FOREIGN KEY(clientID) REFERENCES Clients(clientID),\n FOREIGN KEY(wineID) REFERENCES Wines(wineID)\n)
null
CREATE TABLE Producers (\n producerID TEXT,\n name TEXT,\n city TEXT,\n PRIMARY KEY (producerID)\n)
null
CREATE TABLE Wines (\n wineID TEXT,\n vineyard TEXT,\n year INT,\n degree FLOAT,\n PRIMARY KEY (wineID)\n)
null
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"We insert new values in the tables"},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Wines\nVALUES (\"W1\", \"Bordeaux\", 1993, 13.5),\n(\"W2\", \"Chinon\", 2020, 12.5),\n(\"W3\", \"Bordeaux\", 2001, 11.0),\n(\"W4\", \"Beaujolais\", 2022, 12.0),\n(\"W5\", \"Medoc\", 2005, 11.0)","execution_count":17,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Producers\nVALUES (\"P1\", \"Charles Paire\", \"Bordeaux\"),\n(\"P2\", \"Henri Daniel\", \"Lyon\"),\n(\"P3\", \"Jean Barte\", \"Marseilles\"),\n(\"P4\", \"Isabelle Blois\", \"Bordeaux\"),\n(\"P5\", \"Marie Mauris\", \"Paris\")","execution_count":18,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Harvests\nVALUES (\"W1\", \"P1\", 11.9),\n(\"W1\", \"P4\", 1.7),\n(\"W2\", \"P2\", 6.3),\n(\"W3\", \"P2\", 23.0),\n(\"W4\", \"P3\", 4.8),\n(\"W5\", \"P5\", 10.3)","execution_count":19,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Clients\nVALUES (\"C1\", \"Lois Leine\", \"Bordeaux\"),\n(\"C2\", \"Georges Bout\", \"Lyon\"),\n(\"C3\", \"Jules Vallet\", \"Marseille\"),\n(\"C4\", \"Arnold Miles\", \"Bordeaux\"),\n(\"C5\", \"Claude Hars\", \"Paris\")","execution_count":20,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Orders\nVALUES (\"O1\", \"2022-12-12\", \"C1\", \"W1\", 10),\n(\"O2\", \"2022-11-05\", \"C1\", \"W2\", 1),\n(\"O3\", \"2022-12-24\", \"C2\", \"W3\", 32),\n(\"O4\", \"2023-01-04\", \"C3\", \"W3\", 19),\n(\"O5\", \"2022-01-17\", \"C4\", \"W5\", 7)","execution_count":22,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO Deliveries\nVALUES (\"O1\", \"Delivered\"),\n(\"O2\", \"Delivering\"),\n(\"O3\", \"Returned\"),\n(\"O4\", \"Delivered\"),\n(\"O5\", \"Waiting\")","execution_count":24,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT *\nFROM Clients","execution_count":25,"outputs":[{"output_type":"execute_result","execution_count":25,"data":{"text/plain":"clientID\tname\tcity\nC1\tLois Leine\tBordeaux\nC2\tGeorges Bout\tLyon\nC3\tJules Vallet\tMarseilles\nC4\tArnold Miles\tBordeaux\nC5\tClaude Hars\tParis","text/html":"
clientIDnamecity
C1Lois LeineBordeaux
C2Georges BoutLyon
C3Jules ValletMarseilles
C4Arnold MilesBordeaux
C5Claude HarsParis
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"UPDATE Clients\nSET city = \"Pau\"\nWHERE clientID = \"C2\"","execution_count":26,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT *\nFROM Clients","execution_count":27,"outputs":[{"output_type":"execute_result","execution_count":27,"data":{"text/plain":"clientID\tname\tcity\nC1\tLois Leine\tBordeaux\nC2\tGeorges Bout\tPau\nC3\tJules Vallet\tMarseilles\nC4\tArnold Miles\tBordeaux\nC5\tClaude Hars\tParis","text/html":"
clientIDnamecity
C1Lois LeineBordeaux
C2Georges BoutPau
C3Jules ValletMarseilles
C4Arnold MilesBordeaux
C5Claude HarsParis
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT *\nFROM Harvests","execution_count":28,"outputs":[{"output_type":"execute_result","execution_count":28,"data":{"text/plain":"wineID\tproducerID\tweight\nW1\tP1\t11.9\nW1\tP4\t1.7\nW2\tP2\t6.3\nW3\tP2\t23\nW4\tP3\t4.8\nW5\tP5\t10.3","text/html":"
wineIDproducerIDweight
W1P111.9
W1P41.7
W2P26.3
W3P223
W4P34.8
W5P510.3
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"UPDATE Harvests\nSET weight = weight * 2\nWHERE wineID = \"W1\"","execution_count":29,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT *\nFROM Harvests","execution_count":30,"outputs":[{"output_type":"execute_result","execution_count":30,"data":{"text/plain":"wineID\tproducerID\tweight\nW1\tP1\t23.8\nW1\tP4\t3.4\nW2\tP2\t6.3\nW3\tP2\t23\nW4\tP3\t4.8\nW5\tP5\t10.3","text/html":"
wineIDproducerIDweight
W1P123.8
W1P43.4
W2P26.3
W3P223
W4P34.8
W5P510.3
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"DELETE FROM Harvests\nWHERE weight > 20;","execution_count":31,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT *\nFROM Harvests","execution_count":32,"outputs":[{"output_type":"execute_result","execution_count":32,"data":{"text/plain":"wineID\tproducerID\tweight\nW1\tP4\t3.4\nW2\tP2\t6.3\nW4\tP3\t4.8\nW5\tP5\t10.3","text/html":"
wineIDproducerIDweight
W1P43.4
W2P26.3
W4P34.8
W5P510.3
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"CREATE TABLE BankAccounts(\n userID TEXT,\n amount FLOAT,\n PRIMARY KEY (userID),\n CHECK (amount >= 0)\n)","execution_count":33,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"INSERT INTO BankAccounts\nVALUES (\"U1\", 10),\n (\"U2\", 20)","execution_count":34,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"UPDATE BankAccounts\nSET amount = amount - 20\nWHERE userID = \"U1\";","execution_count":35,"outputs":[{"output_type":"stream","text":"Error: CHECK constraint failed: amount >= 0","name":"stderr"},{"output_type":"error"}]},{"metadata":{"trusted":true},"cell_type":"code","source":"BEGIN TRANSACTION;","execution_count":36,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"UPDATE BankAccounts\nSET amount = amount + 20\nWHERE userID = \"U2\";","execution_count":37,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"UPDATE BankAccounts\nSET amount = amount - 20\nWHERE userID = \"U1\";","execution_count":38,"outputs":[{"output_type":"stream","text":"Error: CHECK constraint failed: amount >= 0","name":"stderr"},{"output_type":"error"}]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM BankAccounts;","execution_count":39,"outputs":[{"output_type":"execute_result","execution_count":39,"data":{"text/plain":"userID\tamount\nU1\t10\nU2\t40","text/html":"
userIDamount
U110
U240
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"ROLLBACK;","execution_count":40,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SELECT * FROM BankAccounts;","execution_count":41,"outputs":[{"output_type":"execute_result","execution_count":41,"data":{"text/plain":"userID\tamount\nU1\t10\nU2\t20","text/html":"
userIDamount
U110
U220
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"## LAB"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"name":"sql","display_name":"SQL","language":"sql"}},"nbformat":4,"nbformat_minor":2}