T-SQL — Practice.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "code",
            "source": [
                "-- Esploro la tabella Sales Orders\r\n",
                "SELECT TOP(5) *\r\n",
                "FROM TSQLV4.Sales.Orders;"
            ],
            "metadata": {
                "azdata_cell_guid": "c9604bd6-ee55-4356-9f39-f12ae649eeff",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.015"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 7,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "orderid"
                                    },
                                    {
                                        "name": "custid"
                                    },
                                    {
                                        "name": "empid"
                                    },
                                    {
                                        "name": "orderdate"
                                    },
                                    {
                                        "name": "requireddate"
                                    },
                                    {
                                        "name": "shippeddate"
                                    },
                                    {
                                        "name": "shipperid"
                                    },
                                    {
                                        "name": "freight"
                                    },
                                    {
                                        "name": "shipname"
                                    },
                                    {
                                        "name": "shipaddress"
                                    },
                                    {
                                        "name": "shipcity"
                                    },
                                    {
                                        "name": "shipregion"
                                    },
                                    {
                                        "name": "shippostalcode"
                                    },
                                    {
                                        "name": "shipcountry"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "10248",
                                    "1": "85",
                                    "2": "5",
                                    "3": "2014-07-04",
                                    "4": "2014-08-01",
                                    "5": "2014-07-16",
                                    "6": "3",
                                    "7": "32,3800",
                                    "8": "Ship to 85-B",
                                    "9": "6789 rue de l'Abbaye",
                                    "10": "Reims",
                                    "11": "NULL",
                                    "12": "10345",
                                    "13": "France"
                                },
                                {
                                    "0": "10249",
                                    "1": "79",
                                    "2": "6",
                                    "3": "2014-07-05",
                                    "4": "2014-08-16",
                                    "5": "2014-07-10",
                                    "6": "1",
                                    "7": "11,6100",
                                    "8": "Ship to 79-C",
                                    "9": "Luisenstr. 9012",
                                    "10": "Münster",
                                    "11": "NULL",
                                    "12": "10328",
                                    "13": "Germany"
                                },
                                {
                                    "0": "10250",
                                    "1": "34",
                                    "2": "4",
                                    "3": "2014-07-08",
                                    "4": "2014-08-05",
                                    "5": "2014-07-12",
                                    "6": "2",
                                    "7": "65,8300",
                                    "8": "Destination SCQXA",
                                    "9": "Rua do Paço, 7890",
                                    "10": "Rio de Janeiro",
                                    "11": "RJ",
                                    "12": "10195",
                                    "13": "Brazil"
                                },
                                {
                                    "0": "10251",
                                    "1": "84",
                                    "2": "3",
                                    "3": "2014-07-08",
                                    "4": "2014-08-05",
                                    "5": "2014-07-15",
                                    "6": "1",
                                    "7": "41,3400",
                                    "8": "Ship to 84-A",
                                    "9": "3456, rue du Commerce",
                                    "10": "Lyon",
                                    "11": "NULL",
                                    "12": "10342",
                                    "13": "France"
                                },
                                {
                                    "0": "10252",
                                    "1": "76",
                                    "2": "4",
                                    "3": "2014-07-09",
                                    "4": "2014-08-06",
                                    "5": "2014-07-11",
                                    "6": "2",
                                    "7": "51,3000",
                                    "8": "Ship to 76-B",
                                    "9": "Boulevard Tirou, 9012",
                                    "10": "Charleroi",
                                    "11": "NULL",
                                    "12": "10318",
                                    "13": "Belgium"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>orderid</th><th>custid</th><th>empid</th><th>orderdate</th><th>requireddate</th><th>shippeddate</th><th>shipperid</th><th>freight</th><th>shipname</th><th>shipaddress</th><th>shipcity</th><th>shipregion</th><th>shippostalcode</th><th>shipcountry</th></tr><tr><td>10248</td><td>85</td><td>5</td><td>2014-07-04</td><td>2014-08-01</td><td>2014-07-16</td><td>3</td><td>32,3800</td><td>Ship to 85-B</td><td>6789 rue de l'Abbaye</td><td>Reims</td><td>NULL</td><td>10345</td><td>France</td></tr><tr><td>10249</td><td>79</td><td>6</td><td>2014-07-05</td><td>2014-08-16</td><td>2014-07-10</td><td>1</td><td>11,6100</td><td>Ship to 79-C</td><td>Luisenstr. 9012</td><td>Münster</td><td>NULL</td><td>10328</td><td>Germany</td></tr><tr><td>10250</td><td>34</td><td>4</td><td>2014-07-08</td><td>2014-08-05</td><td>2014-07-12</td><td>2</td><td>65,8300</td><td>Destination SCQXA</td><td>Rua do Paço, 7890</td><td>Rio de Janeiro</td><td>RJ</td><td>10195</td><td>Brazil</td></tr><tr><td>10251</td><td>84</td><td>3</td><td>2014-07-08</td><td>2014-08-05</td><td>2014-07-15</td><td>1</td><td>41,3400</td><td>Ship to 84-A</td><td>3456, rue du Commerce</td><td>Lyon</td><td>NULL</td><td>10342</td><td>France</td></tr><tr><td>10252</td><td>76</td><td>4</td><td>2014-07-09</td><td>2014-08-06</td><td>2014-07-11</td><td>2</td><td>51,3000</td><td>Ship to 76-B</td><td>Boulevard Tirou, 9012</td><td>Charleroi</td><td>NULL</td><td>10318</td><td>Belgium</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "code",
            "source": [
                "-- Restituisco tutti gli ordini effettuati a Giugno 2015\r\n",
                "SELECT \r\n",
                "    orderid, \r\n",
                "    orderdate, \r\n",
                "    custid, \r\n",
                "    empid\r\n",
                "FROM TSQLV4.Sales.Orders\r\n",
                "WHERE (orderdate >= '20150601') AND (orderdate < '20150701');"
            ],
            "metadata": {
                "azdata_cell_guid": "80c95ea1-7628-4fc3-a6c2-a483bbe07c38",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Restituisco Media e Varianza del Processing Time (Shippeddate - Orderdate), divisa per Paese e Anno.\r\n",
                "SELECT \r\n",
                "    shipcountry AS \"Country\",\r\n",
                "    YEAR(shippeddate) AS 'Year',\r\n",
                "    AVG(DATEDIFF(DAY, orderdate, shippeddate)) AS \"Average Processing Time\",\r\n",
                "    ROUND(STDEV(DATEDIFF(DAY, orderdate, shippeddate)), 0) AS \"Standard Deviation Processing Time\"\r\n",
                "FROM \r\n",
                "    TSQLV4.Sales.Orders\r\n",
                "GROUP BY \r\n",
                "    shipcountry, \r\n",
                "    YEAR(shippeddate)\r\n",
                "HAVING \r\n",
                "    (YEAR(shippeddate) IS NOT NULL) AND (shipcountry IN ('UK', 'Spain','Portugal'))\r\n",
                "ORDER BY \r\n",
                "    shipcountry, \r\n",
                "    YEAR(shippeddate);"
            ],
            "metadata": {
                "azdata_cell_guid": "fc39b55e-59b4-4992-9ff2-2d8da75fbc0e",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(9 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.003"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 47,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Country"
                                    },
                                    {
                                        "name": "Year"
                                    },
                                    {
                                        "name": "Average Processing Time"
                                    },
                                    {
                                        "name": "Standard Deviation Processing Time"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Portugal",
                                    "1": "2014",
                                    "2": "3",
                                    "3": "2"
                                },
                                {
                                    "0": "Portugal",
                                    "1": "2015",
                                    "2": "11",
                                    "3": "7"
                                },
                                {
                                    "0": "Portugal",
                                    "1": "2016",
                                    "2": "6",
                                    "3": "1"
                                },
                                {
                                    "0": "Spain",
                                    "1": "2014",
                                    "2": "10",
                                    "3": "11"
                                },
                                {
                                    "0": "Spain",
                                    "1": "2015",
                                    "2": "11",
                                    "3": "9"
                                },
                                {
                                    "0": "Spain",
                                    "1": "2016",
                                    "2": "7",
                                    "3": "8"
                                },
                                {
                                    "0": "UK",
                                    "1": "2014",
                                    "2": "4",
                                    "3": "3"
                                },
                                {
                                    "0": "UK",
                                    "1": "2015",
                                    "2": "10",
                                    "3": "9"
                                },
                                {
                                    "0": "UK",
                                    "1": "2016",
                                    "2": "7",
                                    "3": "3"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Country</th><th>Year</th><th>Average Processing Time</th><th>Standard Deviation Processing Time</th></tr><tr><td>Portugal</td><td>2014</td><td>3</td><td>2</td></tr><tr><td>Portugal</td><td>2015</td><td>11</td><td>7</td></tr><tr><td>Portugal</td><td>2016</td><td>6</td><td>1</td></tr><tr><td>Spain</td><td>2014</td><td>10</td><td>11</td></tr><tr><td>Spain</td><td>2015</td><td>11</td><td>9</td></tr><tr><td>Spain</td><td>2016</td><td>7</td><td>8</td></tr><tr><td>UK</td><td>2014</td><td>4</td><td>3</td></tr><tr><td>UK</td><td>2015</td><td>10</td><td>9</td></tr><tr><td>UK</td><td>2016</td><td>7</td><td>3</td></tr></table>"
                    },
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "timeSeries",
                            "dataDirection": "horizontal",
                            "columnsAsLabels": true,
                            "labelFirstColumn": false,
                            "legendPosition": "bottom"
                        }
                    }
                }
            ],
            "execution_count": 47
        },
        {
            "cell_type": "code",
            "source": [
                "-- Seleziono tutti gli ordini effettuati alla fine del mese.\r\n",
                "SELECT orderid, orderdate, custid, empid\r\n",
                "FROM TSQLV4.Sales.Orders\r\n",
                "WHERE orderdate = EOMONTH(orderdate)"
            ],
            "metadata": {
                "azdata_cell_guid": "e082af9d-7130-4839-9b4c-07d572908a1d",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Esploro la tabella HR.Employees\r\n",
                "SELECT TOP (5) *\r\n",
                "FROM TSQLV4.HR.Employees;\r\n",
                "\r\n",
                "-- Seleziono tutti gli impiegati nel cui cognome compare la lettera 'e' almeno due volte\r\n",
                "SELECT empid, firstname, lastname, LEN(lastname) - LEN(REPLACE(lastname, 'e','')) AS \"Occurrences of 'e'\"\r\n",
                "FROM TSQLV4.HR.Employees\r\n",
                "WHERE LEN(lastname) - LEN(REPLACE(lastname, 'e','')) >= 2;"
            ],
            "metadata": {
                "azdata_cell_guid": "aca9c9f2-e3dd-46f3-97bb-e275bd9fc47a"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.017"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 57,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "empid"
                                    },
                                    {
                                        "name": "lastname"
                                    },
                                    {
                                        "name": "firstname"
                                    },
                                    {
                                        "name": "title"
                                    },
                                    {
                                        "name": "titleofcourtesy"
                                    },
                                    {
                                        "name": "birthdate"
                                    },
                                    {
                                        "name": "hiredate"
                                    },
                                    {
                                        "name": "address"
                                    },
                                    {
                                        "name": "city"
                                    },
                                    {
                                        "name": "region"
                                    },
                                    {
                                        "name": "postalcode"
                                    },
                                    {
                                        "name": "country"
                                    },
                                    {
                                        "name": "phone"
                                    },
                                    {
                                        "name": "mgrid"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "Davis",
                                    "2": "Sara",
                                    "3": "CEO",
                                    "4": "Ms.",
                                    "5": "1968-12-08",
                                    "6": "2013-05-01",
                                    "7": "7890 - 20th Ave. E., Apt. 2A",
                                    "8": "Seattle",
                                    "9": "WA",
                                    "10": "10003",
                                    "11": "USA",
                                    "12": "(206) 555-0101",
                                    "13": "NULL"
                                },
                                {
                                    "0": "2",
                                    "1": "Funk",
                                    "2": "Don",
                                    "3": "Vice President, Sales",
                                    "4": "Dr.",
                                    "5": "1972-02-19",
                                    "6": "2013-08-14",
                                    "7": "9012 W. Capital Way",
                                    "8": "Tacoma",
                                    "9": "WA",
                                    "10": "10001",
                                    "11": "USA",
                                    "12": "(206) 555-0100",
                                    "13": "1"
                                },
                                {
                                    "0": "3",
                                    "1": "Lew",
                                    "2": "Judy",
                                    "3": "Sales Manager",
                                    "4": "Ms.",
                                    "5": "1983-08-30",
                                    "6": "2013-04-01",
                                    "7": "2345 Moss Bay Blvd.",
                                    "8": "Kirkland",
                                    "9": "WA",
                                    "10": "10007",
                                    "11": "USA",
                                    "12": "(206) 555-0103",
                                    "13": "2"
                                },
                                {
                                    "0": "4",
                                    "1": "Peled",
                                    "2": "Yael",
                                    "3": "Sales Representative",
                                    "4": "Mrs.",
                                    "5": "1957-09-19",
                                    "6": "2014-05-03",
                                    "7": "5678 Old Redmond Rd.",
                                    "8": "Redmond",
                                    "9": "WA",
                                    "10": "10009",
                                    "11": "USA",
                                    "12": "(206) 555-0104",
                                    "13": "3"
                                },
                                {
                                    "0": "5",
                                    "1": "Mortensen",
                                    "2": "Sven",
                                    "3": "Sales Manager",
                                    "4": "Mr.",
                                    "5": "1975-03-04",
                                    "6": "2014-10-17",
                                    "7": "8901 Garrett Hill",
                                    "8": "London",
                                    "9": "NULL",
                                    "10": "10004",
                                    "11": "UK",
                                    "12": "(71) 234-5678",
                                    "13": "2"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>empid</th><th>lastname</th><th>firstname</th><th>title</th><th>titleofcourtesy</th><th>birthdate</th><th>hiredate</th><th>address</th><th>city</th><th>region</th><th>postalcode</th><th>country</th><th>phone</th><th>mgrid</th></tr><tr><td>1</td><td>Davis</td><td>Sara</td><td>CEO</td><td>Ms.</td><td>1968-12-08</td><td>2013-05-01</td><td>7890 - 20th Ave. E., Apt. 2A</td><td>Seattle</td><td>WA</td><td>10003</td><td>USA</td><td>(206) 555-0101</td><td>NULL</td></tr><tr><td>2</td><td>Funk</td><td>Don</td><td>Vice President, Sales</td><td>Dr.</td><td>1972-02-19</td><td>2013-08-14</td><td>9012 W. Capital Way</td><td>Tacoma</td><td>WA</td><td>10001</td><td>USA</td><td>(206) 555-0100</td><td>1</td></tr><tr><td>3</td><td>Lew</td><td>Judy</td><td>Sales Manager</td><td>Ms.</td><td>1983-08-30</td><td>2013-04-01</td><td>2345 Moss Bay Blvd.</td><td>Kirkland</td><td>WA</td><td>10007</td><td>USA</td><td>(206) 555-0103</td><td>2</td></tr><tr><td>4</td><td>Peled</td><td>Yael</td><td>Sales Representative</td><td>Mrs.</td><td>1957-09-19</td><td>2014-05-03</td><td>5678 Old Redmond Rd.</td><td>Redmond</td><td>WA</td><td>10009</td><td>USA</td><td>(206) 555-0104</td><td>3</td></tr><tr><td>5</td><td>Mortensen</td><td>Sven</td><td>Sales Manager</td><td>Mr.</td><td>1975-03-04</td><td>2014-10-17</td><td>8901 Garrett Hill</td><td>London</td><td>NULL</td><td>10004</td><td>UK</td><td>(71) 234-5678</td><td>2</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 57,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "empid"
                                    },
                                    {
                                        "name": "firstname"
                                    },
                                    {
                                        "name": "lastname"
                                    },
                                    {
                                        "name": "Occurrences of 'e'"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "4",
                                    "1": "Yael",
                                    "2": "Peled",
                                    "3": "2"
                                },
                                {
                                    "0": "5",
                                    "1": "Sven",
                                    "2": "Mortensen",
                                    "3": "2"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>empid</th><th>firstname</th><th>lastname</th><th>Occurrences of 'e'</th></tr><tr><td>4</td><td>Yael</td><td>Peled</td><td>2</td></tr><tr><td>5</td><td>Sven</td><td>Mortensen</td><td>2</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 57
        },
        {
            "cell_type": "code",
            "source": [
                "-- Esploro la tabella Sales.OrdersDetails\r\n",
                "SELECT TOP (5) *\r\n",
                "FROM TSQLV4.Sales.OrderDetails\r\n",
                "\r\n",
                "-- Seleziono tutti gli ordini con un valore superiore ai 10.000$\r\n",
                "SELECT orderid AS \"Order ID\", qty*unitprice AS \"Total Value\"\r\n",
                "FROM TSQLV4.Sales.OrderDetails\r\n",
                "WHERE qty*unitprice >= 10000\r\n",
                "ORDER BY qty*unitprice DESC;\r\n",
                "\r\n",
                "-- FUNZIONE PER ISTOGRAMMA QUANTITA' ORDINI"
            ],
            "metadata": {
                "azdata_cell_guid": "a4b2f580-cb17-47ee-aa43-8d45f91c594a"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(5 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(6 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.009"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 62,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "orderid"
                                    },
                                    {
                                        "name": "productid"
                                    },
                                    {
                                        "name": "unitprice"
                                    },
                                    {
                                        "name": "qty"
                                    },
                                    {
                                        "name": "discount"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "10248",
                                    "1": "11",
                                    "2": "14,0000",
                                    "3": "12",
                                    "4": "0,000"
                                },
                                {
                                    "0": "10248",
                                    "1": "42",
                                    "2": "9,8000",
                                    "3": "10",
                                    "4": "0,000"
                                },
                                {
                                    "0": "10248",
                                    "1": "72",
                                    "2": "34,8000",
                                    "3": "5",
                                    "4": "0,000"
                                },
                                {
                                    "0": "10249",
                                    "1": "14",
                                    "2": "18,6000",
                                    "3": "9",
                                    "4": "0,000"
                                },
                                {
                                    "0": "10249",
                                    "1": "51",
                                    "2": "42,4000",
                                    "3": "40",
                                    "4": "0,000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>orderid</th><th>productid</th><th>unitprice</th><th>qty</th><th>discount</th></tr><tr><td>10248</td><td>11</td><td>14,0000</td><td>12</td><td>0,000</td></tr><tr><td>10248</td><td>42</td><td>9,8000</td><td>10</td><td>0,000</td></tr><tr><td>10248</td><td>72</td><td>34,8000</td><td>5</td><td>0,000</td></tr><tr><td>10249</td><td>14</td><td>18,6000</td><td>9</td><td>0,000</td></tr><tr><td>10249</td><td>51</td><td>42,4000</td><td>40</td><td>0,000</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 62,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Order ID"
                                    },
                                    {
                                        "name": "Total Value"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "10865",
                                    "1": "15810,0000"
                                },
                                {
                                    "0": "10981",
                                    "1": "15810,0000"
                                },
                                {
                                    "0": "10889",
                                    "1": "10540,0000"
                                },
                                {
                                    "0": "10353",
                                    "1": "10540,0000"
                                },
                                {
                                    "0": "10417",
                                    "1": "10540,0000"
                                },
                                {
                                    "0": "10424",
                                    "1": "10329,2000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Order ID</th><th>Total Value</th></tr><tr><td>10865</td><td>15810,0000</td></tr><tr><td>10981</td><td>15810,0000</td></tr><tr><td>10889</td><td>10540,0000</td></tr><tr><td>10353</td><td>10540,0000</td></tr><tr><td>10417</td><td>10540,0000</td></tr><tr><td>10424</td><td>10329,2000</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 62
        }
    ]
}