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).
This is an example of all three tables being joined together (Customer, Transactions and Sales). Now the data is joined to show the surname of the salesperson who made the sale to the customer. The output has been ordered by transaction id (ascending).
| cust_id | trans_cust_id | cust_name | cust_firstname | cust_surname | trans_grn_widgets_sales | trans_blu_widgets_sales | staff_surname |
|---|---|---|---|---|---|---|---|
| 4589 | 4589 | Wilton's Fine Products Ltd | Will | Wilton | 500.00 | 0.00 | Allen |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 500.00 | 1800.00 | Beaumont |
| 4568 | 4568 | Billingham's of Braithwaite Ltd | Bertie | Billingham | 1000.00 | 0.00 | Chuzzlewit |
| 4570 | 4570 | Dimbleby PLC | Diana | Dimbleby | 2000.00 | 4500.00 | Dawson |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 2500.00 | 3000.00 | Faversham |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 1000.00 | 1800.00 | Ginola |
| 4587 | 4587 | United Uniforms Ltd | Una | Unsworth | 1500.00 | 600.00 | Allen |
| 4589 | 4589 | Wilton's Fine Products Ltd | Will | Wilton | 1500.00 | 600.00 | Beaumont |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 2000.00 | 600.00 | Chuzzlewit |
| 4568 | 4568 | Billingham's of Braithwaite Ltd | Bertie | Billingham | 2000.00 | 600.00 | Dawson |
| 4568 | 4568 | Billingham's of Braithwaite Ltd | Bertie | Billingham | 1500.00 | 1200.00 | Faversham |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 1000.00 | 0.00 | Beaumont |
| 4567 | 4567 | Acorn Traders Ltd | Anthea | Adams | 3500.00 | 1200.00 | Ginola |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 1500.00 | 3600.00 | Allen |
| 4584 | 4584 | Rena's Supplies Ltd | Rena | Rimshaw | 1000.00 | 4000.00 | Beaumont |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 2500.00 | 600.00 | Chuzzlewit |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 3000.00 | 3500.00 | Dawson |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2000.00 | 3000.00 | Faversham |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 3000.00 | 3600.00 | Ginola |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 1500.00 | 1800.00 | Allen |
| 4587 | 4587 | United Uniforms Ltd | Una | Unsworth | 1000.00 | 2400.00 | Beaumont |
| 4589 | 4589 | Wilton's Fine Products Ltd | Will | Wilton | 3300.00 | 1200.00 | Chuzzlewit |
| 4567 | 4567 | Acorn Traders Ltd | Anthea | Adams | 1200.00 | 0.00 | Beaumont |
| 4570 | 4570 | Dimbleby PLC | Diana | Dimbleby | 1500.00 | 600.00 | Dawson |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 3000.00 | 600.00 | Chuzzlewit |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 2000.00 | 0.00 | Dawson |
| 4573 | 4573 | Gimley of Gosborton Ltd | George | Gimley | 3000.00 | 1200.00 | Faversham |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 1000.00 | 1800.00 | Allen |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 1500.00 | 2400.00 | Beaumont |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 1000.00 | 3000.00 | Beaumont |
| 4601 | 4601 | Irlington Industries Ltd | Ian | Icke | 1500.00 | 3000.00 | Chuzzlewit |
| 4602 | 4602 | Jeff Johnston Ltd | Jeff | Johnston | 1000.00 | 1200.00 | Dawson |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 3000.00 | Ecclestone |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2000.00 | 1200.00 | Faversham |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 500.00 | 2400.00 | Ginola |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 500.00 | 3600.00 | Harrison |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2500.00 | 600.00 | Allen |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 2500.00 | 600.00 | Beaumont |
| 4570 | 4570 | Dimbleby PLC | Diana | Dimbleby | 2000.00 | 600.00 | Beaumont |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 2000.00 | 2400.00 | Chuzzlewit |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 1500.00 | 3000.00 | Dawson |
| 4567 | 4567 | Acorn Traders Ltd | Anthea | Adams | 1500.00 | 4200.00 | Ecclestone |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 1500.00 | 1800.00 | Faversham |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 1500.00 | 1800.00 | Ginola |
| 4591 | 4591 | Yaxley Pies Ltd | Yasmine | Yolander | 1000.00 | 2400.00 | Harrison |
| 4592 | 4592 | Zimmerman's of Sawtry Ltd | Zak | Zanetti | 1000.00 | 4800.00 | Harrison |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 2000.00 | 3400.00 | Allen |
| 4601 | 4601 | Irlington Industries Ltd | Ian | Icke | 1500.00 | 3550.00 | Beaumont |
| 4602 | 4602 | Jeff Johnston Ltd | Jeff | Johnston | 1500.00 | 3000.00 | Chuzzlewit |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 3000.00 | Chuzzlewit |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 1500.00 | 2400.00 | Dawson |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 2000.00 | 2400.00 | Ecclestone |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 2000.00 | 2400.00 | Faversham |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2500.00 | 2400.00 | Ginola |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 2500.00 | 3600.00 | Harrison |
| 4611 | 4611 | Sally's Supplies Ltd | Sally | Smithers | 1500.00 | 3600.00 | Allen |
| 4612 | 4612 | Thompson's Tents Ltd | Toby | Thompson | 1500.00 | 3600.00 | Beaumont |
| 4613 | 4613 | Ursula Umbridge Ltd | Ursula | Umbridge | 1500.00 | 3600.00 | Chuzzlewit |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 1800.00 | Chuzzlewit |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2000.00 | 1800.00 | Dawson |
| 4614 | 4614 | Violet Vans Ltd | Violet | Vardy | 2000.00 | 1800.00 | Ecclestone |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 1500.00 | 1800.00 | Faversham |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 1800.00 | Ginola |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 1500.00 | 1800.00 | Harrison |
| 4601 | 4601 | Irlington Industries Ltd | Ian | Icke | 1000.00 | 3000.00 | Ginola |
| 4602 | 4602 | Jeff Johnston Ltd | Jeff | Johnston | 1000.00 | 3000.00 | Harrison |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1000.00 | 3000.00 | Faversham |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 1000.00 | 2400.00 | Ecclestone |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 1500.00 | 2400.00 | Ecclestone |
| 4612 | 4612 | Thompson's Tents Ltd | Toby | Thompson | 1500.00 | 1800.00 | Dawson |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 1500.00 | 1800.00 | Chuzzlewit |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 1500.00 | 1800.00 | Allen |
MySQL Code
SELECT c.cust_id, t.trans_cust_id, c.cust_name, c.cust_firstname, c.cust_surname,
t.trans_grn_widgets_sales, t.trans_blu_widgets_sales, s.staff_surname
FROM customer AS c
INNER JOIN transactions AS t
ON c.cust_id = t.trans_cust_id
INNER JOIN staff AS s
ON t.trans_staff_id = s.staff_id
ORDER BY trans_id ASC
T-SQL (MS SQL)
SELECT c.cust_id, t.trans_cust_id, c.cust_name, c.cust_firstname, c.cust_surname,
t.trans_grn_widgets_sales, t.trans_blu_widgets_sales, s.staff_surname
FROM customer c
INNER JOIN transactions t
ON c.cust_id = t.trans_cust_id
INNER JOIN staff s
ON t.trans_staff_id = s.staff_id
ORDER BY trans_id ASC
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.