Differences

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

--- database:mysql [2023/11/02 10:04] – [Mysql basic commands] skipidar
+++ database:mysql [2023/11/02 11:04] (current) – skipidar
@@ Line 451: / Line 451: @@
 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>