PipeSQL |>

A lightweight tool and a language to design SQL queries, in bottom-up approach, using pipes and filters

Play ground for PipeSQL

To try out pipeSQL with real database, I have connected sqlite3 database with Northwind schema and data. On the right side editor, you can write pipeSQL and execute it.

Ok, let's get started.

Get all customer details.

c := Customers;

This will fetch all the customers from the database.

Get all customers, who are sales representative's and lives in London.

c := Customers
	c.City='London',
	c.ContactTitle=like('Sales Representative');
	-- This line is commented.
	--c.ContactTitle='Sales Representative'

Get phone list of customers from 'UK' (just company and contact names with phone and FAX numbers) sorted by company name.

c := Customers
	c.Country='UK'
	sort CompanyName
	column 
		c.CompanyName CompanyName,
		ContactName, 
		Phone, 
		Fax;

Get all products where unit price is between 10 and 30 and product name contains 'Choc' or 'Schok'.

p := Products
	p.UnitPrice >= 10,
	p.UnitPrice <= 30,
	(p.ProductName = like('%Choc%') or p.ProductName = like('%Schok%'))
	sort ProductName
	column 
		ProductName, SupplierID, UnitPrice;

Calculate the sales amount for the each orders.

od := [Order Details]
    summary sum(
        od.UnitPrice * od.Quantity * (1 - od.Discount)
        ) by OrderID;

Get all products which belongs to 'Beverages' category.

c := Categories
	c.CategoryName = 'Beverages'
|>
p := Products
	filter p.CategoryID = c.CategoryID
	sort p.ProductName;

Get all customers and suppliers from country UK.

c := Customers
	c.Country = 'UK'
	column 
		CustomerID,
		CompanyName,
		Address,
		City,
		'Customer' Type
union
s := Suppliers
	s.Country = 'UK' 
	column
		s.SupplierID ID,
		s.CompanyName,
		s.Address,
		s.City,
		'Supplier' Type;

Get top 10 sales amount and the employees who made that sales.

od := [Order Details]
    summary sum(
        od.UnitPrice * od.Quantity * (1 - od.Discount)
        ) by OrderID
|>
ord := Orders
    filter ord.OrderID = od.OrderID
    column ord.OrderID, ord.ShippedDate, ord.EmployeeID, 
    	od.summary1 SaleAmount
|>
emp := Employees
    filter emp.EmployeeID = ord.EmployeeID
    column 
        emp.Country, 
        emp.LastName, 
        emp.FirstName, 
        ord.OrderID, 
        ord.ShippedDate, 
        ord.SaleAmount
    sort ord.SaleAmount -
    top 10;

Get the total sales by each product, along with category name, for year 1997.

--Get all products, along with category name, and
--store the result in variable p.
cat := Categories
    column
        CategoryID,
        CategoryName
|>
p := Products
    filter p.CategoryID = cat.CategoryID
    column
        cat.CategoryID,
        cat.CategoryName,
        p.productName,
        p.ProductID ProductID
;

--Get total sales for each product from orders 
--between 1997-10-01 to 1997-12-31. Store the result in variable od
ord := Orders
        ord.OrderDate >= date('1997-01-01'),
        ord.OrderDate <= date('1997-12-31')
    column 
        ord.OrderID
|>
od := [Order Details]
    filter od.OrderID = ord.OrderID
    summary sum(
        od.UnitPrice * od.Quantity * (1 - od.Discount)
        ) by ProductID
;

-- Injecting the intermediate results, p and od, to products and sales.
-- For more details, check the documentation.
-- Eventhough this contains 3 blocks, generated SQL will be executed only once.
products := <- p
|>
sales := <- od
    filter sales.ProductID = products.ProductID
    column
        products.CategoryID CategoryID,
        products.CategoryName CategoryName,
        products.productName ProductName,
        round(sales.summary1,2) SalesAmount
    sort CategoryID, CategoryName, ProductName
;
c := Customers;


Copyright © 2014 pipesql.com . All Right Reserved