Examples

Find all

findAllCustomer()
{
	select * from Customer
}

Alternative spelling:

findAllCustomer()
{
	from Customer
}

Where condition

findAllCustomerWhere()
{
	select * from Customer where city = "London"
}

Where condition with parameter

findAllCustomerWhere(String city)
{
	select * from Customer where city = :city
}

It is also possible to pass objects as parameters:

findAllCustomerWhere(Customer customer)
{
	select * from Customer where customer = :customer
}

Like operator

findAllCustomerLike()
{
	select * from Customer where city like "%L"
}

Concat function

findAllCustomerLike(String city)
{
	select * from Customer where city like concat("%", :city)
}

Returns specific columns

findAllCustomerColumn()
{
	select customerid, city, address from Customer
}

Saves the query result in a custom object

findAllCustomer()
{
	select * from Customer
    into myCustomObject
}

Alias

findAllCustomerAs()
{
	select * from Customer as c
    where c.city = "London"
}

Logical AND

findAllCustomerAnd()
{
	select * from Customer where city = "London" and country = "UK"
}

Logical OR

findAllCustomerOr()
{
	select * from Customer where city = "London" or city = "Berlin"
}

MAX function - Returns the highest unit price

getOrderDetailMaxPrice()
{
	select max(unitprice) from Orderdetail result single
}

MIN function - Returns the lowest unit price

getOrderDetailMinPrice()
{
	select min(unitprice) from Orderdetail result single
}

AVG function - Grouped by product name. Calculates the average order price of the orders within a group of products

getOrderDetailAvgPrice()
{
	select avg(unitprice) from Orderdetail
	group by product.productname
}

getOrderDetailAvgPrice()
{
	select avg(unitprice) as price, product.productname from Orderdetail
	group by product.productname
}

ORDER BY function

Descending order

getAllCustomerWithOrderByDesc()
{
	select * from Customer order by city desc
}

Ascending order

getAllCustomerWithOrderByAsc()
{
	select * from Customer order by city asc
}

Count function

getCountFromCustomerByCountry()
{
	select count(customerid) from Customer group by country
}

Subselects

getOrderDetailsFromLondon()
{
	select * from Orderdetail as detail
	where detail.`order`.orderid in (select orderid from Order where customer.city = "London")
}

Alternative to Subselects:

getOrderDetailsFromLondon()
{
	select * from Orderdetail
	where `order`.customer.city = "London"
}

Complex queries

Returns all orders from London grouped by the sum of those orders

getOrderDetailsSumFromLondon()
{
	select sum(detail.unitprice) from Orderdetail as detail
	where detail.`order`.orderid in (select orderid from Order where customer.city = "London")
	group by detail.`order`.customer.city
	result single
}

Possible short form

getOrderDetailsSumFromLondon()
{
	select sum(unitprice) from Orderdetail
	where `order`.customer.city = "London"
	group by `order`.customer.city
	result single
}