At the bottom of the database you will find a sample MS Access Database for training in SQL.
It is supposed to be a food company. It has 5 salesmen.
The invoices issued are recorded in 2 tables, invoice_headers & invoice_details.
Invoice_headers includes the general information of the invoice and invoice_details includes the products on the invoice.
In this “simple” database there are hundreds of things that can be asked as statistics data.
On the menu ‘Queries’ you have to create the queries. I have already created one example (Query0).
The second database contains the solutions.
Please answer to the following questions:
1) The warehouse manager wants to know how many pieces from each product has been selled since 01/01/2009
until today,so he can make an order for replenishment (order descending)
2) The marketing manager wants to know which products are selled better at mini markets (order descending)
3) The sales director wants to know which salesman brought more money in the company during the past
fiscal year,thus 01/06/2008 until 31/05/2009, so he can give him bonus
4) The sales director wants to give promotion to the salesman that brought more money in the company
in general
5) The chief accountant wants to know how many invoices have been issued for mini markets during the past
fiscal year(see above) per customer type
6) The sales director wants to know which customer has brought more money in the company
(order by descending amount per customer)
7) The sales director wants to know which customer types have brought more money in the company
(order by descending amount per customer type)
8) The general director wants to compare the sales (in money) of spirits for years 2007, 2008, 2009
9) The general director wants to know which salesman sells better to his best customer
(best from money aspect), date independent
10) The financial manager wants to know which customer makes the smaller invoices in terms of money
(so he charges the company with extra multiple transportation cost)
Note 1: Better use ‘WHERE’ for joining tables, not ‘JOIN’
Note 2: For writing queries in MS Access select “view” SQL (not design) so you can write sql code
Note 3: You use the following commands: SELECT, WHERE & AND, GROUP BY, ORDER BY (DESC), COUNT(), SUM().
(please add extension .mdb to both files to open)