Of course, the increased efficiency is minimal on a Northwind-like database of relatively small size the true benefit of indexes is seen in much larger databases.Ī brief note: The NumOrders attribute displays the number of product orders, which we found to be 2155 earlier. Here, because the employees relation is joined to the orders relation using the EmployeeID attribute, an index is helpful. Indexes on an attribute are particularly helpful for increasing efficiency when that attribute is either used as a criterion in a selection condition ( WHERE clauses) or is part of a joining condition. I first create an index on the EmployeeID attribute in the employees relation. We may, then, be interested in identifying the best and worst performing salespeople at Northwind. raises, promotions, termination) are made easier when performance metrics are available. Considering the former, human resources decisions (e.g. I decide to examine two aspects of employee performance that would be of use to organizational management: individual performance and aggregate performance by age. With regard to analysis, I’ll briefly look at 2 sectors of Northwind’s operation: employees and sales. `orderDET` SELECT * FROM categories SELECT * FROM customers SELECT * FROM employees SELECT * FROM orderDET limit 3000 SELECT * FROM orders limit 3000 SELECT * FROM products Employee Performance
With this basic information in hand, I dive into some analyses.ĪLTER TABLE `northwind`. Lastly, there are 9 employees, 5 of whom operate in the United States and 4 in the United Kingdom. In some of those orders, customers purchased multiple products there are 2155 product orders. There are 91 customers who have collectively placed 830 complete orders. There are 77 distinct products and 9 product categories. The preliminary analyses give a good overview of the data. After changing the name of the “order details” relation to “orderDET” for easier referencing, I get to work. products (details about specific products)īefore jumping into any more complicated queries, it useful to get an initial picture of what each relation contains.order details (additional details about orders).employees (information about Northwind’s employees).customers (information about Northwind’s customers).The database contains 13 tables (relations), six of which I use for the analyses discussed later:
The Northwind database contains sales data about a fictitious company. All of the analyses herein were done in MySQL using the MySQL Workbench GUI. In this post, I walk through some queries and analyses I performed on the oft-used Northwindĭata. After taking six online courses and working through numerous excercises, I wanted to get my hands dirty with some data in a more applied way. I’ve been focusing recently on trying to take my SQL skills to the next level – defining more complicated subqueries, working with triggers and views, Online Analytical Processing tools, and the like.