Tohuwabohu excorcism

User Tools

Site Tools

Trace:
database:mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
database:mysql [2023/10/30 19:40] – [Joins] skipidardatabase:mysql [2023/11/02 11:04] (current) – skipidar
Line 400: Line 400:
 FROM Customers AS c, Orders AS o FROM Customers AS c, Orders AS o
 WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID; WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
 +</sxh>
 +
 +
 +
 +
 +==  Having ==
 +
 +
 +The ''HAVING'' clause was added to SQL because the ''WHERE'' keyword cannot be used with aggregate functions.
 +
 +
 +
 +
 +
 +<sxh sql>
 +--- The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers
 +
 +SELECT COUNT(CustomerID), Country
 +FROM Customers
 +GROUP BY Country
 +HAVING COUNT(CustomerID) > 5;
 +
 +
 +
 +--- The following SQL statement lists the number of employees having less than 20 sales
 +SELECT e.EmployeeID, LastName, FirstName, COUNT(o.OrderID) As Sales  FROM [Employees] AS e
 +LEFT JOIN "Orders" AS o
 +ON o.EmployeeID = e.EmployeeID
 +GROUP BY e.EmployeeID
 +HAVING COUNT(o.OrderID) < 20
 +ORDER BY Sales  DESC, FirstName DESC, LastName  ASC
 +
 +</sxh>
 +
 +
 +==  Exist ==
 +
 +
 +<sxh sql>
 +--- suppliers with products of price under 20
 +SELECT SupplierName
 +FROM Suppliers
 +WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
 +
 +
 +
 +--- employees with orders
 +SELECT LastName
 +FROM Employees
 +WHERE NOT EXISTS (SELECT OrderID FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);
 +
 +</sxh>
 +
 +
 +
 +==  ANY, ALL==
 +
 +The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
 +
 +
 +
 +<sxh sql>
 +
 +--- product name with productID having quantity=10 somewhere
 +SELECT ProductName
 +FROM Products
 +WHERE ProductID = ANY
 +  (SELECT ProductID
 +  FROM OrderDetails
 +  WHERE Quantity = 10);
 +
 +</sxh>
 +
 +
 +
 +
 +==  ANY, ALL==
 +
 +The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
 +
 +
 +<sxh sql>
 +
 +SELECT OrderID, Quantity,
 +CASE
 +    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
 +    WHEN Quantity = 30 THEN 'The quantity is 30'
 +    ELSE 'The quantity is under 30'
 +END AS QuantityText
 +FROM OrderDetails;
 +
 +</sxh>
 +
 +
 +
 +
 +==   CASE WHEN ==
 +
 +Case expression
 +
 +<sxh sql>
 +
 +SELECT OrderID, Quantity,
 +CASE
 +    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
 +    WHEN Quantity = 30 THEN 'The quantity is 30'
 +    ELSE 'The quantity is under 30'
 +END AS QuantityText
 +FROM OrderDetails;
 +
 +</sxh>
 +
 +
 +
 +
 +== IFNULL(), ISNULL(), COALESCE(), and NVL() Functions ==
 +
 +| ''IFNULL(COLUMN, DEFAULTVALUE) -> COLUMN_VALUE or DEFAULT'' | The IFNULL() function returns a specified value if the expression is NULL. |
 +| ''ISNULL(COLUMN, DEFAULTVALUE) -> BOOL'' | Return boolean. |
 +| ''COALESCE(val1, val2, ...., val_n)'' | Return the first non-null value in a list:  |
 +
 +<sxh sql>
 +
 +--- fall back to 0 for NULL "UnitsOnOrder"
 +SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
 +FROM Products;
 +
 +
 +--- using boolean ISNULL
 +SELECT OrderID, 
 +CASE
 +    WHEN ISNULL(AmountDue) THEN "Paid in full"
 +    WHEN DATE(DueDate) < date(NOW()) THEN "Order is past due"
 +    ELSE CONCAT("Order is due on ", CONVERT(VARCHAR, DueDate, 121))
 +END
 +FROM OrderDetails;
 +
 +</sxh>
 +
 +
 +
 +
 +
 +== Stored Procedure Example ==
 +
 +
 +<sxh sql>
 +
 +--- procedure
 +CREATE PROCEDURE SelectAllCustomers
 +AS
 +SELECT * FROM Customers
 +GO;
 +
 +--- execute
 +EXEC SelectAllCustomers;
 +
 +
 +
 +--- procedure with args
 +CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
 +AS
 +SELECT * FROM Customers WHERE City = @City
 +GO;
 +
 +--- exec
 +EXEC SelectAllCustomers @City = 'London';
 +
 </sxh> </sxh>
  
Line 426: Line 594:
  
 -- join 3 tables together -- join 3 tables together
 +--- Achtung - there is only 1 SELECT but multiple joins
 SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
-FROM ((Orders +FROM ((Orders  
-  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) +      INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) 
- INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID); +      INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
 </sxh> </sxh>
  
Line 544: Line 712:
  
 </sxh> </sxh>
 +
 +
 +
 +
 +
 +
 +
 +
 +
  
 ==== Aufgaben ==== ==== Aufgaben ====
database/mysql.1698694830.txt.gz · Last modified: by skipidar