SQlTrainingDOH

Links and resources for the Project ECHO and DOH joint training sessions

Week 1 links:

SQLite Online- https://sqliteonline.com/

SQLite Online with Northwind DBs loaded- https://sqliteonline.com/#urldb=https://raw.githubusercontent.com/jpwhite3/northwind-SQLite3/master/dist/northwind.db

Northwind- https://en.wikiversity.org/wiki/Database_Examples/Northwind/

Week 2 Links:

SQLite Online with Northwind DBs loaded- https://sqliteonline.com/#urldb=https://raw.githubusercontent.com/jpwhite3/northwind-SQLite3/master/dist/northwind.db

HW:

1. Write a query to get Product name and quantity per unit.
2. Write a query to get current Product list (Product ID and name).
3. Write a query to get discontinued Product list (Product ID and name).
4. Write a query to get most expense Product list (name and unit price).
5. Write a query to get Product list (id, name, unit price) where current products cost less than $20.
6. Write a query to get Product list (id, name, unit price) where products cost between $15 and $25.
7. Write a query to get Product list (name, unit price) of ten most expensive products.
8. Write a query to count current and discontinued products.
9. Write a query to get Product list (name, units on order , units in stock) of stock is less than the quantity on order.

Week 3 links:

SQLite Online with Northwind DBs loaded- https://sqliteonline.com/#urldb=https://raw.githubusercontent.com/jpwhite3/northwind-SQLite3/master/dist/northwind.db

HW:

All together: Provide total quantity, total sum of ordered stuff (with applied discounts) per customer, showing also amounts per category + showing flag if customer has both anything for particular category

1.List all Suppliers with all Products (ID, Name) they offer

2.Number of Orders by Customer Country and Year

3.Count of how many employees are associated with no orders, one order, or more than one order

Additonal resources:

[Book] H.Garcia-Molina, J.Ullman, and J.Widom, “Database Systems: The Complete Book”, 2nd Edition, Pearson, 2008.

First two chapters available: http://infolab.stanford.edu/~ullman/dscb.html

[IBM1] https://www.ibm.com/docs/en/zos-basic-skills?topic=zos-what-is-database-management-system

[IBM2] “What is a relational database?”, https://www.ibm.com/topics/relational-databases#:~:text=the%20next%20step-,What%20is%20a%20relational%20database%3F,key%20or%20a%20foreign%20key

[MongoDB] https://www.mongodb.com/basics/acid-transactions

[Wiki] https://en.wikiversity.org/wiki/Database_Examples/Northwind

[SQLiteonline] https://sqliteonline.com/#urldb=https://raw.githubusercontent.com/jpwhite3/northwind-SQLite3/master/dist/northwind.db

[Wiki] https://en.wikiversity.org/wiki/Database_Examples/Northwind

[Time math] https://www.sqlite.org/lang_datefunc.html

[Get R ] https://www.stat.colostate.edu/~jah/talks_public_html/isec2020/installRStudio.html

If looking to manipulate your own data on your local: https://cran.r-project.org/bin/windows/base/ https://cran.r-project.org/web/packages/RSQLite/vignettes/RSQLite.html