Example sql database for .net, c#, vb.net, mono and xamarin

Example SQL Database : Orders

The example database is Orders history database to show example DDL (Data Definition Language) and DML (Data Manipulation Language) capabilities provided by SQLDatabase.Net library.

You can also download complete database with sample data and sql scripts. SQL Code is also below on this page.

Table Name	Description	Primary Key
Customers	Customers table with customer information	CustomerId
Suppliers	Supplier information for each product, supplier id is used to identify supplier of product	SupplierId
Products	Information about products such as name, price and supplier id and if any discount package controlled by front end.	ProductId
Orders	Order information	OrderId
OrderItems	Items in a particular order	OrderItemId
OrderAdditionalAmounts	Any other amount to add or remove from final order price such as tax, shipping etc
InvoiceNumbers	Invoice generated from front end and stored on disk in pdf format	Id

SQL Create Statements

Following sql statements creates tables, indexes and triggers.


/* Drop the table only if it exists, another syntax is DROP TABLE Customers */
DROP TABLE IF EXISTS Customers;

/* Create table only if it does not exists, another syntax is CREATE TABLE Customers */
CREATE TABLE IF NOT EXISTS Customers (
   CustomerId   INTEGER	    PRIMARY KEY
   ,FirstName   TEXT	    NOT NULL
   ,LastName    TEXT	    NOT NULL
   ,City        TEXT	    NULL
   ,Country     TEXT	    NULL
   ,Phone       TEXT	    NULL
   ,Email       TEXT	    NULL
);

/* Index will be created on customers table on lastname and firstname column in ascending order another syntax is CREATE INDEX [index name] */
CREATE INDEX IF NOT EXISTS IdxCustomerName on Customers (LastName ASC, FirstName ASC);


DROP TABLE IF EXISTS Suppliers;
CREATE TABLE IF NOT EXISTS Suppliers (
   SupplierId           INTEGER	     PRIMARY KEY,
   CompanyName          TEXT	     NOT NULL,
   ContactName          TEXT	     NULL,
   ContactTitle         TEXT	     NULL,
   City                 TEXT	     NULL,
   Country              TEXT	     NULL,
   Phone                TEXT	     NULL,
   Email                TEXT	     NULL
);
/* Indexes will be created on suppliers table, keyword ASC or DESC are not required. */
CREATE INDEX IF NOT EXISTS IdxSupplierName on Suppliers (CompanyName ASC);
CREATE INDEX IF NOT EXISTS IdxSupplierCountry on Suppliers (Country ASC);

CREATE TRIGGER ValidateEmailBeforeInsert_Suppliers BEFORE INSERT ON Suppliers
BEGIN
 SELECT
 CASE
 WHEN NEW.email NOT LIKE '%_@__%._%' THEN
 RAISE ( ABORT, 'Invalid email address' )
 END;
END;

DROP TABLE IF EXISTS Products;
CREATE TABLE IF NOT EXISTS Products (
   ProductId	 INTEGER	PRIMARY KEY
   ,ProductName         TEXT	NOT NULL
   ,SupplierId          INTEGER	NOT NULL DEFAULT 0 REFERENCES Suppliers(SupplierId)
   ,UnitPrice           REAL	NULL DEFAULT 0.00
   ,Package             TEXT	NULL
   ,IsDiscontinued      INTEGER NOT NULL CHECK (IsDiscontinued IN (1, 0))
);

CREATE INDEX IF NOT EXISTS IdxProductSupplierId on Products (SupplierId ASC);
CREATE INDEX IF NOT EXISTS IdxProductName on Products (ProductName ASC);

DROP TABLE IF EXISTS Orders;
CREATE TABLE IF NOT EXISTS Orders (
   OrderId              INTEGER	PRIMARY KEY,
   OrderDate            TEXT    NOT NULL,
   OrderNumber          TEXT    NULL,
   CustomerId           INTEGER	NOT NULL REFERENCES Customers(CustomerId),
   TotalAmount          REAL	NULL DEFAULT 0.00
);

CREATE INDEX IF NOT EXISTS IdxOrderCustomerId on Orders (CustomerId ASC);
CREATE INDEX IF NOT EXISTS IdxOrderOrderDate on Orders (OrderDate ASC);

DROP TABLE IF EXISTS OrderItems;
CREATE TABLE IF NOT EXISTS OrderItems (
   OrderItemId  INTEGER	PRIMARY KEY,
   OrderId      INTEGER	NOT NULL REFERENCES Orders(OrderId) ON UPDATE CASCADE ON DELETE CASCADE,
   ProductId    INTEGER	NOT NULL REFERENCES Products(ProductId),
   UnitPrice    REAL	NOT NULL DEFAULT 0.00,
   Quantity     INTEGER	NOT NULL DEFAULT 1,
   UNIQUE (OrderId, ProductId) ON CONFLICT ABORT
);

CREATE INDEX IF NOT EXISTS IdxOrderItemOrderId on OrderItems (OrderId ASC);
CREATE INDEX IF NOT EXISTS IdxOrderItemProductId on OrderItems (ProductId ASC);

DROP TABLE IF EXISTS OrderAdditionalAmounts;
CREATE TABLE IF NOT EXISTS OrderAdditionalAmounts (
   AdditionId       INTEGER	PRIMARY KEY
   ,OrderId         INTEGER	NOT NULL
   ,Addition        TEXT	NOT NULL
   ,AddOrRemove     TEXT	NOT NULL
   ,AmountOrValue   REAL	NULL DEFAULT 0.00
   ,IsPercentage    INTEGER	NOT NULL
);

CREATE INDEX IF NOT EXISTS IdxOrderCustomerId on Orders (CustomerId ASC);
CREATE INDEX IF NOT EXISTS IdxOrderOrderDate on Orders (OrderDate ASC);

/* Table with Auto increment column as primary key */
DROP TABLE IF EXISTS InvoiceNumbers;
CREATE TABLE IF NOT EXISTS InvoiceNumbers (
   Id		INTEGER     PRIMARY KEY AUTOINCREMENT
   ,PDFFilePath	TEXT    NOT NULL
);

View Name	Description
vw_CustomerOrders	Customer orders, views joins Customers and Orders table to produce desired rows and columns.
vw_Top10CustomersByOrders	Top 10 customers by total number of orders.
vw_Top10CustomersByOrderAmount	View joins Customers and Orders table find top 10 customers by Order total.

SQL Code to Create Views

Views are similar to tables but they are based on either one or more tables or existing views, they can use indexes if the underlying table has indexes and such index is useable in the query used to create that particular view. The sql database library only support read only views. All SELECT operations can be performed against a view they are like "Virtual Tables" in the database.


CREATE VIEW IF NOT EXISTS vw_CustomerOrders
AS
SELECT c.CustomerId, c.FirstName, c.LastName, o.OrderId, o.OrderNumber
, DateTimeFormat(o.OrderDate, 'yyyy-MM-dd', 'en-US') AS OrderDate
, FormatCurrency(o.TotalAmount) AS TotalAmount
FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId 
ORDER BY c.CustomerId;

/* View using another view as table */
CREATE VIEW IF NOT EXISTS vw_Top10CustomersByOrders
AS
SELECT v.CustomerId, v.FirstName, v.LastName
, COUNT(v.CustomerId) As TotalOrders
, FormatCurrency(Sum(substr(TotalAmount,2))) AS OrdersTotal
FROM vw_CustomerOrders v
GROUP BY CustomerId
ORDER BY TotalOrders DESC
LIMIT 10 OFFSET 0 ;

/* View similar to top 10 customers by orders but using table */
CREATE VIEW IF NOT EXISTS vw_Top10CustomersByOrderAmount
AS
SELECT c.CustomerId, c.FirstName, c.LastName
, COUNT(o.CustomerId) AS TotalOrders
, FormatCurrency(Sum(o.TotalAmount)) AS OrdersTotal
FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId 
GROUP BY c.CustomerId HAVING COUNT(o.CustomerId) > 0
Order By OrdersTotal DESC
LIMIT 10 OFFSET 0 ;

Trigger Name	Description
ValidateEmailBeforeInsert_Suppliers	Validates email address before insert.

Create Triggers

The following sql code is to create a trigger and can also be found above with tables and index statements.


 
CREATE TRIGGER ValidateEmailBeforeInsert_Suppliers BEFORE INSERT ON Suppliers
BEGIN
 SELECT
 CASE
 WHEN NEW.email NOT LIKE '%_@__%._%' THEN
 RAISE ( ABORT, 'Invalid email address' )
 END;
END;