The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
A fictional company (Widge-It Ltd) sells blue and green widgets with an optional insurance product. The company requires an electronic system in place to record the product sales and contact data on the companies it has sold the products to. The company also wants to monitor the performance of its sales staff at any given period of time.
The company allows its sales staff to give discounts where appropriate.
Suitable reports should be available to track progress, including:
The sales manager will need access to the various reporting functionality, while day to day administration is carried out by a data entry clerk.
The test case has been approached from two angles.
The first is a demonstration of how the problem could be solved in Microsoft Excel, complete with formulae, formatting, pivot tables, charts and test data. Although traditionally this test case would be better served by a database, Microsoft Excel has been used as a demonstration of my understanding of the software. Also demonstrated is how I can implement a design to streamline the data input process, thus saving time and money in staffing costs.
To see a detailed breakdown of the Excel process and download an Excel workbook solution, click on the 'Excel Solution' menu at the top of the page or click on the button below.
The second approach is how this would relate to a database with MySQL or T-SQL (MS SQL), containing the same data. The purpose for this is to demonstrate how I would build queries to access data and to show live examples on the test data stored on this server. All test data that is contained in the Excel workbook has been uploaded to a live database for comparison.
To see a detailed breakdown of the MySQL/T-SQL process, click on the 'SQL Queries' menu at the top of the page or click on the button below.
There are some differences to the two approaches, so these differences will be explained through the process.