Versions Compared

Old Version 3

changes.mady.by.user RapidClipse Translator Services

Saved on

compared with

New Version Current

changes.mady.by.user Manuel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Select into returns a different class and not a different object

...

  • Find all 

    Code Block
    languagesql
    themeConfluence
    findAllCustomer()
{
	select * from Customer
}

    Alternative spelling:

    Code Block
    languagesql
    themeConfluence
    findAllCustomer()
{
	from Customer
}


  • Where condition

    Code Block
    languagesql
    themeConfluence
    findAllCustomerWhere()
{
	select * from Customer where city = "London"
}


  • Where condition with parameter

    Code Block
    languagesql
    themeConfluence
    findAllCustomerWhere(String city)
{
	select * from Customer where city = :city
}

    It is also possible to pass objects as parameters:

    Code Block
    languagesql
    themeConfluence
    findAllCustomerWhere(Customer customer)
{
	select * from Customer where customer = :customer
}


  • Like operator

    Code Block
    languagesql
    themeConfluence
    findAllCustomerLike()
{
	select * from Customer where city like "%L"
}


  • Concat function

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


  • Returns specific columns

    Code Block
    languagesql
    themeConfluence
    findAllCustomerColumn()
{
	select customerid, city, address from Customer
}


  • Saves the query result in a custom objectdifferent class

    Code Block
    languagesql
    themeConfluence
    findAllCustomer()
{
	select * customerid, contactname, city from Customer
    into myCustomObjectFlatCustomer
}


  • Alias

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


  • Logical AND

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


  • Logical OR

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


  • MAX function - Returns the highest unit price

    Code Block
    languagesql
    themeConfluence
    getOrderDetailMaxPrice()
{
	select max(unitprice) from Orderdetail result single
}


  • MIN function - Returns the lowest unit price

    Code Block
    languagesql
    themeConfluence
    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
    languagesql
    themeConfluence
    getOrderDetailAvgPrice()
{
	select avg(unitprice) from Orderdetail
	group by product.productname
}


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


  • ORDER BY function 

    • Descending order

      Code Block
      languagesql
      themeConfluence
      getAllCustomerWithOrderByDesc()
{
	select * from Customer order by city desc
}


    • Ascending order

      Code Block
      languagesql
      themeConfluence
      getAllCustomerWithOrderByAsc()
{
	select * from Customer order by city asc
}


  • Count function

    Code Block
    languagesql
    themeConfluence
    getCountFromCustomerByCountry()
{
	select count(customerid) from Customer group by country
}


  • Subselects

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

    Alternative to Subselects:

    Code Block
    languagesql
    themeConfluence
    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
      languagesql
      themeConfluence
      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
      languagesql
      themeConfluence
      getOrderDetailsSumFromLondon()
{
	select sum(unitprice) from Orderdetail
	where `order`.customer.city = "London"
	group by `order`.customer.city
	result single
}


...