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/11/02 08:58] – [Mysql basic commands] skipidardatabase:mysql [2023/11/02 11:04] (current) – skipidar
Line 431: Line 431:
 HAVING COUNT(o.OrderID) < 20 HAVING COUNT(o.OrderID) < 20
 ORDER BY Sales  DESC, FirstName DESC, LastName  ASC 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>
database/mysql.1698915531.txt.gz · Last modified: by skipidar