workshops/basic/notebook.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "Theoretical SELECT statement\r\n",
                "```sql\r\n",
                "SELECT [ALL | DISTINCT] {*|expression [, expression ...]} ​\r\n",
                "FROM table name​\r\n",
                "[WHERE conditions(s)]​\r\n",
                "[GROUP BY column name [, column name ...]​\r\n",
                "[HAVING conditions(s)]​\r\n",
                "[ORDER BY {column name |seq nr}{ASC|DESC}[,...]\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "b748087d-0eda-48ff-af5d-e01ad88efc91"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 1 - All Columns\r\n",
                "Show all data of all products\t"
            ],
            "metadata": {
                "azdata_cell_guid": "4c37343a-3551-46a2-be58-8f76c077aaa3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                "* FROM Product\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b1ab0925-3edb-4665-a8eb-058769dbc5a5",
                "tags": []
            },
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 2 - Certain Columns\r\n",
                "Show for all a products the `productID`, `name` and `unit price`"
            ],
            "metadata": {
                "azdata_cell_guid": "6b1657bd-5990-4c42-b19a-01fef8c65fb9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                ",Price \r\n",
                "FROM Product​;"
            ],
            "metadata": {
                "azdata_cell_guid": "93556f40-b18f-4e7f-82c3-bcb6eec38b73",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 3 - TOP\r\n",
                "Show for the first 3 products the `productID`, `name` and `unit price`\r\n",
                "> Remark - How is the data filtered?"
            ],
            "metadata": {
                "azdata_cell_guid": "5659e2c0-c3d5-4f11-a5c8-02d22a7fedc9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT TOP 3\r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                ",Price \r\n",
                "FROM Product​;"
            ],
            "metadata": {
                "azdata_cell_guid": "c3a5a5ac-cc80-42cf-82eb-3e7ea8315ac4",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 4 - WHERE\r\n",
                "Show `ProductId`, the `name` of the product and the `unit price` of all products from `product class 1`\r\n",
                "> Note: It's not required to SELECT the column(s) you're filtering on."
            ],
            "metadata": {
                "azdata_cell_guid": "88e5d7af-4e37-4e33-a86d-af5b1a14c45a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                ",Price \r\n",
                "FROM Product​\r\n",
                "WHERE ProductClassId = 1"
            ],
            "metadata": {
                "azdata_cell_guid": "723fd39f-675f-4827-8bfd-49addf28f31b",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 5 - Comparison\r\n",
                "Show `ProductID`, `Name`, `units in stock` for all products with **less than 5** units in stock"
            ],
            "metadata": {
                "azdata_cell_guid": "49ad50d2-f3e5-45c8-94b4-29284f2288f7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                "FROM Product\r\n",
                "WHERE UnitsInStock < 5​"
            ],
            "metadata": {
                "azdata_cell_guid": "f7b7ed5d-968f-47ce-96b1-0d776b57a0d5",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 6 - LIKE\r\n",
                "Show productID, name of the products for which the second letter is in the range a-k"
            ],
            "metadata": {
                "azdata_cell_guid": "54d2218f-a301-4bc4-9f75-60124178ee73"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName​\r\n",
                "FROM Product\r\n",
                "WHERE ProductName LIKE '_[a-k]%'​"
            ],
            "metadata": {
                "azdata_cell_guid": "a24debf3-48c3-4f62-a773-5290c1e7e069",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 7 - Logical Operators\r\n",
                "Show the `id`, `name`, `price` of the `Products` where the `name` starts with a `T` **or** the `ProductId` is equal to 46 **and** the `price` is more than 16 EUR."
            ],
            "metadata": {
                "azdata_cell_guid": "764ecde1-af0d-4a21-a077-1592931e5415"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                ",Price\r\n",
                "FROM Product\r\n",
                "WHERE (ProductName LIKE 'T%' OR ProductId = 46 )\r\n",
                "  AND Price > 16"
            ],
            "metadata": {
                "azdata_cell_guid": "ba7fc346-9c28-4723-9c92-911d77087519",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "e6652f7b-d2bb-48ea-9af4-c7434ef4d05d"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "1f2f1496-e6ea-4359-b1c6-6328db68f1f7"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 8 - Range\r\n",
                "Show `ProductId`, `name`, `SupplierId` of the `Products` supplied by suppliers with ID [1, 3 or 5]​"
            ],
            "metadata": {
                "azdata_cell_guid": "dd19814c-9e5f-4013-955e-087502b670cc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                ",SupplierId​\r\n",
                "FROM Product​\r\n",
                "WHERE SupplierId in (1,3,5)​"
            ],
            "metadata": {
                "azdata_cell_guid": "8f918aa5-ff9b-4a63-8e86-6ffeba77f6ad",
                "tags": []
            },
            "outputs": [],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 9 - IS  NULL\r\n",
                "Select suppliers from an unknown region​\r\n",
                "> Remark : A NULL is not equal to 0 (for numerical values), blank or empty string ​\r\n",
                "(for character values)!​"
            ],
            "metadata": {
                "azdata_cell_guid": "04dff3ad-8e1d-449c-becc-99fea0b831d9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " SupplierName\r\n",
                ",Region​\r\n",
                "FROM Supplier​\r\n",
                "WHERE Region IS NULL​"
            ],
            "metadata": {
                "azdata_cell_guid": "eb6203f1-19a5-476b-b74f-bef766b01148",
                "tags": []
            },
            "outputs": [],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 10 - Caveats NULL\r\n",
                "Show the Supplier for the region different from 'MI' "
            ],
            "metadata": {
                "azdata_cell_guid": "235d6ae6-8ec6-48f5-b646-258cfd2fe80c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " SupplierName\r\n",
                ",Region​\r\n",
                "FROM Supplier​\r\n",
                "WHERE Region <> 'MI'​"
            ],
            "metadata": {
                "azdata_cell_guid": "d1fa36a9-a991-4cf9-98cc-aa3d02315135",
                "tags": []
            },
            "outputs": [],
            "execution_count": 11
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " SupplierName\r\n",
                ",Region​\r\n",
                "FROM Supplier​\r\n",
                "WHERE Region <> 'MI'​ OR Region IS NULL"
            ],
            "metadata": {
                "azdata_cell_guid": "4b3f8a94-a4e2-4749-b93e-86f6ca84c29c",
                "tags": []
            },
            "outputs": [],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 11 - Comments\r\n",
                "/* comments */  ​\r\n",
                "\r\n",
                " -- comments (rest of line is comment)"
            ],
            "metadata": {
                "azdata_cell_guid": "6656bab4-7fb0-48c4-a073-2d62f04a573d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                "-- ,ProductName\r\n",
                "-- ,Price \r\n",
                "FROM Product​;\r\n",
                "\r\n",
                "SELECT \r\n",
                " ProductId,\r\n",
                "--  ProductName,\r\n",
                "--  Price \r\n",
                "FROM Product​;"
            ],
            "metadata": {
                "azdata_cell_guid": "d09c2e77-62a8-4c5a-b0e3-72bd6ab6c981",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                "/*,ProductName\r\n",
                ",Price */\r\n",
                "FROM Product​;"
            ],
            "metadata": {
                "azdata_cell_guid": "95846d64-e0d1-4393-b6f7-680ef5e4e465",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 12 - Order by\r\n",
                "Show an alphabetic list of product names​"
            ],
            "metadata": {
                "azdata_cell_guid": "f3eba68a-b18f-495c-93b4-de18a7b60206"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductName​\r\n",
                ",Price\r\n",
                "FROM Product​\r\n",
                "-- ORDER BY ProductName -- Alternatively ORDER BY 1​\r\n",
                "order by 2"
            ],
            "metadata": {
                "azdata_cell_guid": "b634ad19-038e-4e81-bb96-9cf647262c11",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 13 - Order By Descending\r\n",
                "Show `ProductId`, `Name`, `ProductClassId` of the `Products` sorted by `ProductClassId`. If the class is the same products with the highest price appear first."
            ],
            "metadata": {
                "azdata_cell_guid": "61c4636c-991d-4d05-bf78-7a4262d44a21"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductId\r\n",
                ",ProductName\r\n",
                ",ProductClassId\r\n",
                ",Price​\r\n",
                "FROM Product​\r\n",
                "ORDER BY \r\n",
                " ProductClassId ASC\r\n",
                ",Price DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "79dae612-aaed-4a3c-9a4e-74ff8f9bb2f9",
                "tags": []
            },
            "outputs": [],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 14 - DISTINCT\r\n",
                "Show all **unique** suppliers that supply products​"
            ],
            "metadata": {
                "azdata_cell_guid": "7ef8816d-9654-438c-b91b-2826c0608c8a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\r\n",
                "SupplierId​\r\n",
                "FROM Product​\r\n",
                "ORDER BY SupplierId ​"
            ],
            "metadata": {
                "azdata_cell_guid": "80f60314-9243-46d7-9f06-1356fdabbdfe",
                "tags": []
            },
            "outputs": [],
            "execution_count": 15
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT DISTINCT\r\n",
                "SupplierId​\r\n",
                "FROM Product​\r\n",
                "ORDER BY SupplierId ​"
            ],
            "metadata": {
                "azdata_cell_guid": "dc80f7d5-87dc-4512-bdfd-646ab2320604",
                "tags": []
            },
            "outputs": [],
            "execution_count": 16
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 15 - Aliasses\r\n",
                "Select `ProductID`, `ProductName` of the products, but show rename the columns as `PNumber` and `Product Name`  "
            ],
            "metadata": {
                "azdata_cell_guid": "287e4b4a-539e-4861-b456-1fa9749d6576"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "use xtreme\n",
                "SELECT \r\n",
                " ProductId AS ProductNummer\r\n",
                ",ProductName AS [Product Name]\r\n",
                "FROM Product​ p"
            ],
            "metadata": {
                "azdata_cell_guid": "4935c08f-054c-4cc1-b78a-b5b0f6ac429a",
                "tags": []
            },
            "outputs": [],
            "execution_count": 18
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 16 - Calculations\r\n",
                "Give the name and inventory value of the products​"
            ],
            "metadata": {
                "azdata_cell_guid": "a506626a-6560-4161-a3ed-810c2f1cd7e7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductName\r\n",
                ",Price * UnitsInStock AS InventoryValue​\r\n",
                "FROM Product​"
            ],
            "metadata": {
                "azdata_cell_guid": "c74de1fa-2625-4609-be3a-7ef9f7242316",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 17 - String Functions"
            ],
            "metadata": {
                "azdata_cell_guid": "70024003-81de-42b8-b2f0-79af775ad490"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT concat(address,' ',city) FROM employee​;\r\n",
                "SELECT address + ' ' + city FROM employee;\r\n",
                "SELECT SUBSTRING(address,1,5) FROM employee;\r\n",
                "SELECT LEFT(address,5) FROM employee;\r\n",
                "SELECT RIGHT(address,5) FROM employee;\r\n",
                "SELECT LEN(address) FROM employee;\r\n",
                "SELECT LOWER(address) FROM employee;\r\n",
                "SELECT UPPER(address) FROM employee;\r\n",
                "SELECT RTRIM(LTRIM(address)) FROM employee"
            ],
            "metadata": {
                "azdata_cell_guid": "408af084-a1ea-40d3-9e8d-56739682d038",
                "tags": []
            },
            "outputs": [],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 18 - Date/Time Functions"
            ],
            "metadata": {
                "azdata_cell_guid": "a308e408-3b5f-499e-adb1-5c8430146c9e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT GETDATE();\r\n",
                "SELECT GETUTCDATE();\r\n",
                "-- DATEADD (year, 2, GETDATE());​\r\n",
                "-- DATEADD (month, 2, GETDATE());​\r\n",
                "-- DATEADD (day, 2, GETDATE());\r\n",
                "SELECT DATEDIFF(day,BIRTHDATE,GETDATE()) FROM EMPLOYEE;\r\n",
                "-- DAY(GETDATE())\r\n",
                "-- MONTH(GETDATE())\r\n",
                "-- MONTH(GETDATE())"
            ],
            "metadata": {
                "azdata_cell_guid": "6e191dde-ca24-4de0-b260-3d33fcd5b199",
                "tags": []
            },
            "outputs": [],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "0cd65623-bcb4-42d0-a718-b4640a296289"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 19 - Arithmetic Functions"
            ],
            "metadata": {
                "azdata_cell_guid": "4ad1ea24-0b34-436a-918d-3dfdf4e84bfb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "-- ABS(-10)\r\n",
                "-- ROUND(10.75,1)\r\n",
                "-- FLOOR(10.75)\r\n",
                "-- CEILING(10.75)"
            ],
            "metadata": {
                "azdata_cell_guid": "59979feb-ba2a-488c-ad3d-cb20e86d7450",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 20 - Switch"
            ],
            "metadata": {
                "azdata_cell_guid": "654c6ef1-388b-4de2-8773-d5d182b9d5fc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT​\r\n",
                "   CASE ​\r\n",
                "      WHEN price IS NULL THEN 'Not yet priced'\r\n",
                "      WHEN price < 10 THEN 'Very Reasonable Price'​\r\n",
                "      WHEN price >= 10 and price < 20 THEN 'Affordable'​\r\n",
                "      ELSE 'Expensive!'​\r\n",
                "   END AS [Price Category]\r\n",
                ",ProductName ​\r\n",
                ",Price\r\n",
                "FROM Product​"
            ],
            "metadata": {
                "azdata_cell_guid": "1747b00c-b58d-4d3f-b16c-7d36c2046c0f",
                "tags": []
            },
            "outputs": [],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 21 - Sum "
            ],
            "metadata": {
                "azdata_cell_guid": "bbbc9194-906d-4b4d-900c-2473727d54ff"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                "SUM(UnitsInStock * Price) AS [Inventory Value​]\r\n",
                "FROM Product​"
            ],
            "metadata": {
                "azdata_cell_guid": "e1e8348c-2aa3-41f3-b707-d811e401db61",
                "tags": []
            },
            "outputs": [],
            "execution_count": 17
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 22 - Average"
            ],
            "metadata": {
                "azdata_cell_guid": "6da39d7b-0c15-4a29-8654-4c84b514716a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                "AVG(UnitsInStock) AS [Average Stock] ​\r\n",
                "FROM Product​"
            ],
            "metadata": {
                "azdata_cell_guid": "77a64790-86a5-45f4-99a7-bc794c2982f9",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 23 - Still Counting\r\n",
                "Returns the number of rows, or a number of **NOT NULL values** in a column​"
            ],
            "metadata": {
                "azdata_cell_guid": "381cfaf4-941a-4868-a877-95f29297ccf6"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Count the number of products - In other words, the number of rows."
            ],
            "metadata": {
                "azdata_cell_guid": "d597c510-1799-4378-990d-925ac76d0488"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                "COUNT(*) as [Amount]\r\n",
                "FROM Product"
            ],
            "metadata": {
                "azdata_cell_guid": "e789f913-9e6b-4182-8c75-4290954a8fe3",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Count the number of `NOT NULL` values in column `ProductTypeId`"
            ],
            "metadata": {
                "azdata_cell_guid": "5b049f46-03b5-437c-829e-aa8ccb742461"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT COUNT(ProductTypeId) as [No. of Types]​\r\n",
                "FROM product​"
            ],
            "metadata": {
                "azdata_cell_guid": "240c6959-20cc-46cd-8b92-fac9374053d4",
                "tags": []
            },
            "outputs": [],
            "execution_count": 18
        },
        {
            "cell_type": "markdown",
            "source": [
                "Count the number of `UNIQUE NOT NULL` values in column `ProductTypeId`​"
            ],
            "metadata": {
                "azdata_cell_guid": "498cce75-1ec2-4f85-a7fa-e3f8981e1cd2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                "COUNT(DISTINCT(ProductTypeId)) as [No. of Unqiue Types] \r\n",
                "FROM product​"
            ],
            "metadata": {
                "azdata_cell_guid": "63df9004-e25f-449e-8164-a074eee1bc28",
                "tags": []
            },
            "outputs": [],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 24 - Min Max\r\n",
                "What is the cheapest and most expensive unit price?​\r\n",
                "\r\n",
                "> Remark​ :\r\n",
                ">\r\n",
                "> Since a statistical function returns only 1 result, either all expressions in the SELECT clause  have to contain a statistical function, or none! ​\r\n",
                "This is slightly different if you use grouping (see further). ​\r\n",
                "> Statistical functions do not take into account NULL values. ​\r\n",
                "Exception : COUNT(*) also counts rows with NULL values.​"
            ],
            "metadata": {
                "azdata_cell_guid": "472ee36c-8aaf-4256-b400-d74342f6b773"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \n",
                "ProductName\r\n",
                ", MIN(price) AS Cheapest\n",
                ", MAX(price) AS Expensive ​\r\n",
                "FROM product​\n",
                "group by productname"
            ],
            "metadata": {
                "azdata_cell_guid": "4fb435d3-de29-4817-a0ec-4e5fe577e0d1",
                "tags": []
            },
            "outputs": [],
            "execution_count": 21
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 25 - Grouping\r\n",
                "What are the different producttypes of the products?"
            ],
            "metadata": {
                "azdata_cell_guid": "07535f22-a733-45b8-9de3-735de6fce1ea"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                "ProductTypeID\r\n",
                "FROM Product​\r\n",
                "GROUP BY ProductTypeID"
            ],
            "metadata": {
                "azdata_cell_guid": "88317625-8f0c-40f8-bc75-823aa81d417d",
                "tags": []
            },
            "outputs": [],
            "execution_count": 6
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT DISTINCT\r\n",
                "ProductTypeID\r\n",
                "FROM Product​"
            ],
            "metadata": {
                "azdata_cell_guid": "5444a1e2-1653-4c76-b7d3-12a233d577c4"
            },
            "outputs": [],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "Show per type the number of products"
            ],
            "metadata": {
                "azdata_cell_guid": "26c20693-f2c6-45e1-803c-1da7fdcc7fe2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductTypeID\n",
                ",COUNT(*)​\r\n",
                "FROM Product​\n",
                "GROUP BY ProductTypeID​"
            ],
            "metadata": {
                "azdata_cell_guid": "d0f192f4-76fb-42ec-893e-3b3185f4cf25",
                "tags": []
            },
            "outputs": [],
            "execution_count": 28
        },
        {
            "cell_type": "markdown",
            "source": [
                "Show per type the number of products that have more than 10 items in stock."
            ],
            "metadata": {
                "azdata_cell_guid": "7859b15c-d4a6-4b79-a836-1296656ef850"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductTypeID\r\n",
                ",COUNT(*)​\r\n",
                "FROM Product​\r\n",
                "WHERE unitsinstock > 10​\r\n",
                "GROUP BY ProductTypeID​"
            ],
            "metadata": {
                "azdata_cell_guid": "da2988e3-968f-48a5-90cf-da9a68a60aef",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 26 - Filtering Groups\r\n",
                "- WHERE vs HAVING​\r\n",
                "- WHERE – works on individual rows​\r\n",
                "- HAVING – works on groups\r\n",
                "> Statistical functions can only be used in SELECT, HAVING, ORDER BY not in WHERE, GROUP BY.\r\n",
                "> \r\n",
                "> If statistical functions appear in the SELECT, then all items in the SELECT-list have to be either statistical functions or group identifications"
            ],
            "metadata": {
                "azdata_cell_guid": "89d06d92-683a-4fd0-bcaa-5a9fe41dcadc"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Show per type that contains more than 10 products the number of products"
            ],
            "metadata": {
                "azdata_cell_guid": "9b540226-b428-4203-a542-c4fdda87ab5f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductTypeID\r\n",
                ",COUNT(*)​ AS [Amount]\r\n",
                "FROM Product​\r\n",
                "GROUP BY ProductTypeID​\r\n",
                "HAVING [Amount] > 10​"
            ],
            "metadata": {
                "azdata_cell_guid": "9225000e-399a-4f24-be81-33339eb00ef5",
                "tags": []
            },
            "outputs": [],
            "execution_count": 31
        },
        {
            "cell_type": "markdown",
            "source": [
                "Note that using an alias won't work."
            ],
            "metadata": {
                "azdata_cell_guid": "07b15a0e-ae36-490e-a867-24d38353f446"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductTypeID\r\n",
                ",COUNT(*)​ As [Amount]\r\n",
                "FROM Product​\r\n",
                "GROUP BY ProductTypeID​\r\n",
                "HAVING [Amount] > 10​"
            ],
            "metadata": {
                "azdata_cell_guid": "4dadc884-46b1-4b80-ab24-da0d8564c5af"
            },
            "outputs": [],
            "execution_count": 29
        },
        {
            "cell_type": "markdown",
            "source": [
                "Show per type that contains more than 10 products with more than 10 units in stock the number of products​"
            ],
            "metadata": {
                "azdata_cell_guid": "baedda90-9acd-44d5-b0c5-6a21c58d84c1"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ProductTypeID\r\n",
                ",COUNT(*)​\r\n",
                "FROM Product​\r\n",
                "WHERE UnitsInStock > 10​\r\n",
                "GROUP BY ProductTypeID​\r\n",
                "HAVING COUNT(*) > 10​"
            ],
            "metadata": {
                "azdata_cell_guid": "d203d195-5b1a-4816-9fa0-17dfc21701f2",
                "tags": []
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 27 - [INNER] JOIN\r\n",
                "Give the team number and name of the captain of each team​.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "6f418f93-d55f-4ace-aa2d-19e02bfc1aef"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT \r\n",
                " TEAMNO\r\n",
                ",NAME​\r\n",
                "FROM TEAMS \r\n",
                "    INNER JOIN PLAYERS​ ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO​"
            ],
            "metadata": {
                "azdata_cell_guid": "effb7cea-cc47-495d-8342-5f5cf7039977",
                "tags": []
            },
            "outputs": [],
            "execution_count": 19
        },
        {
            "cell_type": "markdown",
            "source": [
                "With alias\r\n",
                "> Remark : If the same column name is used in several tables in a query, then each column name has to be preceeded by the table name or its alias. "
            ],
            "metadata": {
                "azdata_cell_guid": "94948234-c960-46d3-933d-c7f2af5ec7f3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TENNIS\n",
                "SELECT \r\n",
                "T.TEAMNO\r\n",
                ",P.NAME​\r\n",
                "FROM TEAMS T \r\n",
                "    INNER JOIN PLAYERS P​ ON T.PLAYERNO = P.PLAYERNO​\n",
                "    JOIN PLAYERS P​ ON T.PLAYERNO = P.PLAYERNO​"
            ],
            "metadata": {
                "azdata_cell_guid": "80ee3f66-c62e-4e48-a62b-cbf4980b42ae",
                "tags": []
            },
            "outputs": [],
            "execution_count": 33
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 28 - Unary Join\r\n",
                "Show all employees and the name of their manager"
            ],
            "metadata": {
                "azdata_cell_guid": "c67f1f6d-a180-44d0-aacc-9c3f9b723f13"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE xtreme\r\n",
                "SELECT \r\n",
                "werknemer.employeeID\r\n",
                ",CONCAT(werknemer.lastname,' ',werknemer.firstname) as Employee\r\n",
                ",CONCAT(baas.lastname,' ',baas.firstname) as Boss​\r\n",
                "FROM employee werknemer \r\n",
                " LEFT JOIN employee baas ON werknemer.reportsto=baas.employeeid;​"
            ],
            "metadata": {
                "azdata_cell_guid": "74248f4b-e976-44c5-a527-f6776db4a69a",
                "tags": []
            },
            "outputs": [],
            "execution_count": 38
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 29 - OUTER JOIN\r\n",
                "Give for all players their player number, name and the list of their penalties"
            ],
            "metadata": {
                "azdata_cell_guid": "72a27113-3c95-4c52-a234-94a0aedb4297"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \n",
                "p.playerno\r\n",
                ",Name\r\n",
                ",Amount ​\r\n",
                "FROM Players p \r\n",
                "    LEFT JOIN Penalties pe ON p.PlayerNo = pe.PlayerNo ​\r\n",
                "ORDER BY p.PlayerNo​"
            ],
            "metadata": {
                "azdata_cell_guid": "aad0c285-c1dd-4d72-972f-90600a591b51",
                "tags": []
            },
            "outputs": [],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 30 - CROSS JOIN\r\n",
                "Make a competition schedule in which each player plays once against each other player\r\n",
                "\r\n",
                "> Remark : In a cross join the number of rows in the result set equals the number of rows in the first table multiplied by the number of rows in the second table, in other words generate all combinations.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "5ce314da-855d-4397-acb5-3b503ed0f77d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE tennis\n",
                "SELECT \n",
                "p1.name + ' ' + p1.initials PLAYER1,\n",
                " '-' AGAINST,\n",
                "p2.name + ' ' + p2.initials PLAYER2\n",
                "from players p1 cross join players p2\n",
                "where p1.playerno < p2.playerno\n",
                "order by p1.playerno;   "
            ],
            "metadata": {
                "azdata_cell_guid": "62b92515-97f8-4c31-9c65-eb9802844b04"
            },
            "outputs": [],
            "execution_count": 43
        }
    ]
}