SQLite

A little introduction to SQLite with a very simple schema and command line statements.

Used executable: sqlite3
Used db file: Supermarket.db

Item:

sqlite3 Supermarket.db "CREATE TABLE Item(ItemID INTEGER PRIMARY KEY, Description TEXT UNIQUE, Price REAL DEFAULT '0.0');"

Supermarket:

sqlite3 Supermarket.db "CREATE TABLE Supermarket(SupermarketID INTEGER PRIMARY KEY, Location TEXT);"

Inventory:

sqlite3 Supermarket.db "CREATE TABLE Inventory(SupermarketID INTEGER, ItemID INTEGER, Quantity INTEGER, PRIMARY KEY(SupermarketID, ItemID), FOREIGN KEY(SupermarketID) REFERENCES Supermarket(SupermarketID), FOREIGN KEY(ItemID) REFERENCES Item(ItemID));"

Table “Item”:

sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Fish','8.00');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Chips','5.50');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Cookies','2.70');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Spaghetti','4.90');"
sqlite3 Supermarket.db "INSERT INTO Item(Description,Price) VALUES('Pizza','9.00');"

Table “Supermarket”:

sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('London');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Alpha Centauri');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Golgafrincham');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Rupert');"
sqlite3 Supermarket.db "INSERT INTO Supermarket(Location) VALUES('Ursa Minor Beta');"

Table “Inventory”:

sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','1','42');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','3','13');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','5','2');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('2','1','75');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','3','33');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','2','24');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('4','3','18');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','1','84');"
sqlite3 Supermarket.db "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','4','27');"

sqlite3 Supermarket.db "UPDATE Inventory SET Quantity='32' WHERE SupermarketID=3 AND ItemID=3;"

sqlite3 Supermarket.db "DELETE FROM Inventory WHERE SupermarketID=5 AND ItemID=4;"

Simple query over all tables:

sqlite3 Supermarket.db "SELECT sm.Location,iv.Quantity,i.Description,i.price FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID;"

Grouped query:

sqlite3 Supermarket.db "SELECT sm.Location,COUNT(*) FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID GROUP BY iv.SupermarketID;"

Bourne shell example:

#!/bin/sh

EXE=sqlite3
DB=Supermarket.db

###############
# create

# Item
${EXE} ${DB} "DROP TABLE IF EXISTS Item;"
${EXE} ${DB} "CREATE TABLE Item(ItemID INTEGER PRIMARY KEY, Description TEXT UNIQUE, Price REAL DEFAULT '0.0');"

# Supermarket
${EXE} ${DB} "DROP TABLE IF EXISTS Supermarket;"
${EXE} ${DB} "CREATE TABLE Supermarket(SupermarketID INTEGER PRIMARY KEY, Location TEXT);"

# Inventory 
${EXE} ${DB} "DROP TABLE IF EXISTS Inventory;"
${EXE} ${DB} "CREATE TABLE Inventory(SupermarketID INTEGER, ItemID INTEGER, Quantity INTEGER, PRIMARY KEY(SupermarketID, ItemID), FOREIGN KEY(SupermarketID) REFERENCES Supermarket(SupermarketID), FOREIGN KEY(ItemID) REFERENCES Item(ItemID));"

###############
# fill

${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Fish','8.00');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Chips','5.50');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Cookies','2.70');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Spaghetti','4.90');"
${EXE} ${DB} "INSERT INTO Item(Description,Price) VALUES('Pizza','9.00');"

${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('London');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Alpha Centauri');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Golgafrincham');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Rupert');"
${EXE} ${DB} "INSERT INTO Supermarket(Location) VALUES('Ursa Minor Beta');"

${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','1','42');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','3','13');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','5','2');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('2','1','75');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','3','33');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('1','2','24');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('4','3','18');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('3','1','84');"
${EXE} ${DB} "INSERT INTO Inventory(SupermarketID,ItemID,Quantity) VALUES('5','4','27');"

###############
# update

${EXE} ${DB} "UPDATE Inventory SET Quantity='32' WHERE SupermarketID=3 AND ItemID=3;"

###############
# delete
${EXE} ${DB} "DELETE FROM Inventory WHERE SupermarketID=5 AND ItemID=4;"

###############
# queries

${EXE} ${DB} "SELECT sm.Location,iv.Quantity,i.Description,i.price FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID;"

${EXE} ${DB} "SELECT sm.Location,COUNT(*) FROM Supermarket AS sm, Item AS i, Inventory AS iv WHERE sm.SupermarketID=iv.SupermarketID AND i.ItemID=iv.ItemID GROUP BY iv.SupermarketID;"