Differences

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

--- database:mysql [2023/11/02 08:44] – [Unions] skipidar
+++ database:mysql [2023/11/02 11:04] (current) – skipidar
@@ Line 400: / Line 400: @@
 FROM Customers AS c, Orders AS o
 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>