CS 3200 – Database Design – Fall 2022

This material was moved to the Class Project Page at the bottom.  

Flask GET /form Route

@app.route("/form")
def get_form():
    return """
    <h2>HTML Forms</h2>

    <form action="/form" method="POST">
    <label for="first">First name:</label><br>
    <input type="text" id="first" name="first" value="John"><br>
    <label for="last">Last name:</label><br>
    <input type="text" id="last" name="last" value="Doe"><br><br>
    <input type="submit" value="Submit">
    </form> 
    """

• The IN Operator
• GROUP BY and HAVING Clauses
• SQL Query Examples 01
• SQL Query Examples 02

Challenge problem from Northwind:  Provide a list of all customers whose average order total is below the average for all companies in their same region.  

SELECT customerAvg.customerid,
       customerAvg.Region as CustomerRegion,
       customerAvg.custAvg,
       regionAvg.avgOrderTotal
FROM (SELECT Region, AVG(orderTotal) AS avgOrderTotal
      FROM (SELECT o.orderid, o.customerid, c.Region, sum(od.unitPrice * od.quantity) AS orderTotal
            FROM Customers c
                     JOIN Orders o ON c.CustomerID = o.CustomerID
                     JOIN OrderDetails od ON o.OrderID = od.OrderID
            WHERE region IS NOT NULL
            GROUP BY o.orderid, o.shipRegion) AS orderTotals
      GROUP BY Region) AS regionAvg
         JOIN
     (SELECT customerid, region, avg(orderTotal) as custAvg
      FROM (SELECT o.orderid, o.customerid, c.Region, sum(od.unitPrice * od.quantity) AS orderTotal
            FROM Customers c
                     JOIN Orders o ON c.CustomerID = o.CustomerID
                     JOIN OrderDetails od on o.OrderID = od.OrderID
            WHERE region IS NOT NULL
            GROUP BY o.orderid, o.shipRegion) AS CustomerTotals
      GROUP BY customerid, region) AS customerAvg ON regionAvg.Region = customerAvg.Region
WHERE custAvg < avgOrderTotal;

SQLite is a small-footprint embedded database system.  Below are some resources to help you understand it better. 

• SQLite.org – General Website.
• SQLite’s SQL Dialect
  • Select
• SQLite Tutorial – doesn’t seem to be affiliated with the official website. But has some good tutorials and descriptions of SQL commands. 

• The Textbooks (and a ton of other useful material) can be accessed through the University Library Website.
  1. Go to https://library.northeastern.edu
  2. Click on Databases and Journals (A-Z).
  3. On the left side or under A-Z Databases, click ‘O’ for O’Reilly.
  4. The first link under O is “O’Reilly for Higher Education”.  Click it.  You may need to make an account on O’Reilly using your NEU email. 
  5. Once you’re logged in, you can search for the “CS 3200 – Fall 2022” Playlist or follow > this < link. 

Important: Only have MySQL Running when you’re actively using it.  Don’t leave it running in the background as this could pose a security risk.  To stop the server, choose the MySQL line in the list and click stop. 

XAMPP on Mac

Note: You can remove the previous version of XAMPP if you’ve already installed it. 

1. Go to > this < link and download the file named xampp-osx-8.1.6.0-installer.dmg (NOT the one that ends in -vm.)
2. Install the application. 
3. After installation is complete, you should have a XAMPP folder in your Applications folder. In that folder, double click manager-osx.app. You may need to authenticate for it to fully start.
4. In the Window that pops up, choose Manage Servers tab at the top. 
5. Click the MySQL Database entry then click Start.  It will take a bit for it to start.  The red dot will first transition to yellow.  When it is completely started, the dot will become green.  
   • If the dot goes from red to yellow to red, send me a DM on slack.