...
Find all
Code Block language sql theme Confluence findAllCustomer() { select * from Customer }
Alternative spelling:
Code Block language sql theme Confluence findAllCustomer() { from Customer }
Where condition
Code Block language sql theme Confluence findAllCustomerWhere() { select * from Customer where city = "London" }
Where condition with parameter
Code Block language sql theme Confluence findAllCustomerWhere(String city) { select * from Customer where city = :city }
It is also possible to pass objects as parameters:
Code Block language sql theme Confluence findAllCustomerWhere(Customer customer) { select * from Customer where customer = :customer }
Like operator
Code Block language sql theme Confluence findAllCustomerLike() { select * from Customer where city like "%L" }
Concat function
Code Block language sql theme Confluence findAllCustomerLike(String city) { select * from Customer where city like concat("%", :city) }
Returns specific columns
Code Block language sql theme Confluence findAllCustomerColumn() { select customerid, city, address from Customer }
Saves the query result in a custom objectdifferent class
Code Block language sql theme Confluence findAllCustomer() { select * customerid, contactname, city from Customer into myCustomObjectFlatCustomer }
Alias
Code Block language sql theme Confluence findAllCustomerAs() { select * from Customer as c where c.city = "London" }
Logical AND
Code Block language sql theme Confluence findAllCustomerAnd() { select * from Customer where city = "London" and country = "UK" }
Logical OR
Code Block language sql theme Confluence findAllCustomerOr() { select * from Customer where city = "London" or city = "Berlin" }
MAX function - Returns the highest unit price
Code Block language sql theme Confluence getOrderDetailMaxPrice() { select max(unitprice) from Orderdetail result single }
MIN function - Returns the lowest unit price
Code Block language sql theme Confluence 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
Code Block language sql theme Confluence getOrderDetailAvgPrice() { select avg(unitprice) from Orderdetail group by product.productname }
Code Block language sql theme Confluence getOrderDetailAvgPrice() { select avg(unitprice) as price, product.productname from Orderdetail group by product.productname }
ORDER BY function
Descending order
Code Block language sql theme Confluence getAllCustomerWithOrderByDesc() { select * from Customer order by city desc }
Ascending order
Code Block language sql theme Confluence getAllCustomerWithOrderByAsc() { select * from Customer order by city asc }
Count function
Code Block language sql theme Confluence getCountFromCustomerByCountry() { select count(customerid) from Customer group by country }
Subselects
Code Block language sql theme Confluence getOrderDetailsFromLondon() { select * from Orderdetail as detail where detail.`order`.orderid in (select orderid from Order where customer.city = "London") }
Alternative to Subselects:
Code Block language sql theme Confluence getOrderDetailsFromLondon() { select * from Orderdetail where `order`.customer.city = "London" }
- Complex queries
Returns all orders from London grouped by the sum of those orders
Code Block language sql theme Confluence 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
Code Block language sql theme Confluence getOrderDetailsSumFromLondon() { select sum(unitprice) from Orderdetail where `order`.customer.city = "London" group by `order`.customer.city result single }
...