Page Comparison

...

• Find all 

  Code Block
  language sql theme Confluence
  findAllCustomer() { select * from Customer }

  Alternativ erlaubte Schreibweise:

  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 mit Parameter

  Code Block
  language sql theme Confluence
  findAllCustomerWhere(String city) { select * from Customer where city = :city }

  Als Parameter können auch Objekte übergeben werden:

  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 Funktion

  Code Block
  language sql theme Confluence
  findAllCustomerLike(String city) { select * from Customer where city like concat("%", :city) }

  
  

• Rückgabe bestimmter Spalten

  Code Block
  language sql theme Confluence
  findAllCustomerColumn() { select customerid, city, address from Customer }

  
  

• Speichert das Abfrageergebnis in einem benutzerdefiniertem Objekt eine andere Klasse

  Code Block
  language sql theme Confluence
  findAllCustomer() { select * customerid, city, address from Customer into myCustomObjectFlatCustomer }

  
  

• Alias

  Code Block
  language sql theme Confluence
  findAllCustomerAs() { select * from Customer as c where c.city = "London" }

  
  

• Logisch AND

  Code Block
  language sql theme Confluence
  findAllCustomerAnd() { select * from Customer where city = "London" and country = "UK" }

  
  

• Logisch OR

  Code Block
  language sql theme Confluence
  findAllCustomerOr() { select * from Customer where city = "London" or city = "Berlin" }

  
  

• MAX Funktion - Gibt den höchsten Unitprice zurück

  Code Block
  language sql theme Confluence
  getOrderDetailMaxPrice() { select max(unitprice) from Orderdetail result single }

  
  

• MIN Funktion - Gibt den niedrigsten Unitprice zurück

  Code Block
  language sql theme Confluence
  getOrderDetailMinPrice() { select min(unitprice) from Orderdetail result single }

  
  

• AVG Funktion - Gruppiert nach dem Produktnamen und berechnet den durchschnittlichen Bestellwert der Orderdetails

  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 Funktion 

  • Absteigende Sortierung

    Code Block
    language sql theme Confluence
    getAllCustomerWithOrderByDesc() { select * from Customer order by city desc }

    
    

  • Aufsteigende Sortierung

    Code Block
    language sql theme Confluence
    getAllCustomerWithOrderByAsc() { select * from Customer order by city asc }

    
    

• Count Funktion

  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 zu Subselects:

  Code Block
  language sql theme Confluence
  getOrderDetailsFromLondon() { select * from Orderdetail where `order`.customer.city = "London" }

  
  

• Komplexe Queries

  • Gibt alle Bestellungen aus London zurück und Gruppiert diese um die Summe aller Bestellungen aus London

    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 }

    
    

  • Mögliche Kurzform

    Code Block
    language sql theme Confluence
    getOrderDetailsSumFromLondon() { select sum(unitprice) from Orderdetail where `order`.customer.city = "London" group by `order`.customer.city result single }

    
    

...