Tohuwabohu excorcism

User Tools

Site Tools

Trace: • spring
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:00] – [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 436: Line 604:
 == FULL (OUTER) Join == == FULL (OUTER) Join ==
  
-Takes null CustomerIDs from both tables+Takes null CustomerIDs from both tables. 
 + 
 +Also shows Customers without Orders. 
 + 
 +And Orders without Customers.
  
 <sxh sql> <sxh sql>
Line 444: Line 616:
  ON c.CustomerID = o.CustomerID  ON c.CustomerID = o.CustomerID
  WHERE OrderID > 10441  WHERE OrderID > 10441
 +
 +--- same as FULL OUTER
 +SELECT * FROM Orders AS o
 +FULL OUTER JOIN Customers AS c
 + ON c.CustomerID = o.CustomerID
 + WHERE OrderID > 10441
 +
 </sxh> </sxh>
  
Line 466: Line 645:
  WHERE OrderID > 10441  WHERE OrderID > 10441
 </sxh> </sxh>
 +
 +== Self Join ==
 +
 +<sxh sql>
 +
 +-- show customer pairs from the same city
 +SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
 +FROM Customers A, Customers B
 +WHERE A.CustomerID <> B.CustomerID
 +AND A.City = B.City 
 +ORDER BY A.City;
 +
 +-- show the customers, who are in the same city, like "Rancho grande"
 +SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
 +FROM Customers A, Customers B
 +WHERE A.CustomerID <> B.CustomerID
 +AND A.CustomerName = "Rancho grande"
 +AND A.City = B.City 
 +ORDER BY A.City;
 +
 +</sxh>
 +
 +
 +
 +====  Unions ====
 +
 +==  UNION / UNION ALL ==
 +
 +
 +<sxh sql>
 +
 +-- all cities, distinct union
 +
 +SELECT City FROM Customers
 +UNION
 +SELECT City FROM Suppliers
 +ORDER BY City;
 +
 +
 +-- UNION ALL means, cities are NOT unique
 +
 +SELECT City FROM Customers
 +UNION ALL
 +SELECT City FROM Suppliers
 +ORDER BY City;
 +
 +</sxh>
 +
 +
 +
 +
 +
 +When putting together Customer, Supplier you can remember who is who
 +
 +by adding a **AS Role** column.
 +
 +It will contain values Customer / Supplier
 +
 +<sxh sql>
 +
 +SELECT 'Customer' AS Role, ContactName, City, Country
 +FROM Customers
 +UNION
 +SELECT 'Supplier', ContactName, City, Country
 +FROM Suppliers
 +
 +</sxh>
 +
 +
 +
 +
 +
 +
 +
 +
 +
  
 ==== Aufgaben ==== ==== Aufgaben ====
database/mysql.1698692413.txt.gz · Last modified: by skipidar