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).
Below are the results of an inner join query linking the Customer table to the Transactions table, linking the cust_id field of the Customer table to the trans_cust_id field of the Transactions table. The results are returned in cust_id order (ascending).
| cust_id | trans_cust_id | cust_name | cust_firstname | cust_surname | trans_grn_widgets_sales | trans_blu_widgets_sales |
|---|---|---|---|---|---|---|
| 4567 | 4567 | Acorn Traders Ltd | Anthea | Adams | 3500.00 | 1200.00 |
| 4567 | 4567 | Acorn Traders Ltd | Anthea | Adams | 1200.00 | 0.00 |
| 4567 | 4567 | Acorn Traders Ltd | Anthea | Adams | 1500.00 | 4200.00 |
| 4568 | 4568 | Billingham's of Braithwaite Ltd | Bertie | Billingham | 1000.00 | 0.00 |
| 4568 | 4568 | Billingham's of Braithwaite Ltd | Bertie | Billingham | 2000.00 | 600.00 |
| 4568 | 4568 | Billingham's of Braithwaite Ltd | Bertie | Billingham | 1500.00 | 1200.00 |
| 4570 | 4570 | Dimbleby PLC | Diana | Dimbleby | 2000.00 | 4500.00 |
| 4570 | 4570 | Dimbleby PLC | Diana | Dimbleby | 1500.00 | 600.00 |
| 4570 | 4570 | Dimbleby PLC | Diana | Dimbleby | 2000.00 | 600.00 |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 3000.00 | 600.00 |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 1500.00 | 1800.00 |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 2500.00 | 3600.00 |
| 4571 | 4571 | East Empire Ltd | Elaine | East | 1500.00 | 1800.00 |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 2000.00 | 0.00 |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 2500.00 | 600.00 |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 1500.00 | 1800.00 |
| 4572 | 4572 | Frannock Manor PLC | Frederick | Franklin | 1500.00 | 1800.00 |
| 4573 | 4573 | Gimley of Gosborton Ltd | George | Gimley | 3000.00 | 1200.00 |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 2500.00 | 3000.00 |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 1000.00 | 1800.00 |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 2000.00 | 600.00 |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 1500.00 | 2400.00 |
| 4582 | 4582 | Peacock & Bradshaw Ltd | Peter | Peacock | 500.00 | 2400.00 |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 500.00 | 1800.00 |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 1500.00 | 3600.00 |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 2500.00 | 600.00 |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 500.00 | 3600.00 |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 1500.00 | 2400.00 |
| 4583 | 4583 | Quentin's Cabins Ltd | Quentin | Quinn | 1000.00 | 2400.00 |
| 4584 | 4584 | Rena's Supplies Ltd | Rena | Rimshaw | 1000.00 | 4000.00 |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 3000.00 | 3500.00 |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 1000.00 | 1800.00 |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 2000.00 | 2400.00 |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 2000.00 | 2400.00 |
| 4585 | 4585 | Shenley Shovels Ltd | Simon | Sixsmith | 1500.00 | 2400.00 |
| 4587 | 4587 | United Uniforms Ltd | Una | Unsworth | 1500.00 | 600.00 |
| 4587 | 4587 | United Uniforms Ltd | Una | Unsworth | 1000.00 | 2400.00 |
| 4589 | 4589 | Wilton's Fine Products Ltd | Will | Wilton | 500.00 | 0.00 |
| 4589 | 4589 | Wilton's Fine Products Ltd | Will | Wilton | 1500.00 | 600.00 |
| 4589 | 4589 | Wilton's Fine Products Ltd | Will | Wilton | 3300.00 | 1200.00 |
| 4591 | 4591 | Yaxley Pies Ltd | Yasmine | Yolander | 1000.00 | 2400.00 |
| 4592 | 4592 | Zimmerman's of Sawtry Ltd | Zak | Zanetti | 1000.00 | 4800.00 |
| 4601 | 4601 | Irlington Industries Ltd | Ian | Icke | 1500.00 | 3000.00 |
| 4601 | 4601 | Irlington Industries Ltd | Ian | Icke | 1500.00 | 3550.00 |
| 4601 | 4601 | Irlington Industries Ltd | Ian | Icke | 1000.00 | 3000.00 |
| 4602 | 4602 | Jeff Johnston Ltd | Jeff | Johnston | 1000.00 | 1200.00 |
| 4602 | 4602 | Jeff Johnston Ltd | Jeff | Johnston | 1500.00 | 3000.00 |
| 4602 | 4602 | Jeff Johnston Ltd | Jeff | Johnston | 1000.00 | 3000.00 |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 3000.00 |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 3000.00 |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 1800.00 |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1500.00 | 1800.00 |
| 4603 | 4603 | Kensome & Kingston Ltd | Karen | Kensome | 1000.00 | 3000.00 |
| 4611 | 4611 | Sally's Supplies Ltd | Sally | Smithers | 1500.00 | 3600.00 |
| 4612 | 4612 | Thompson's Tents Ltd | Toby | Thompson | 1500.00 | 3600.00 |
| 4612 | 4612 | Thompson's Tents Ltd | Toby | Thompson | 1500.00 | 1800.00 |
| 4613 | 4613 | Ursula Umbridge Ltd | Ursula | Umbridge | 1500.00 | 3600.00 |
| 4614 | 4614 | Violet Vans Ltd | Violet | Vardy | 2000.00 | 1800.00 |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2000.00 | 3000.00 |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2000.00 | 1200.00 |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2500.00 | 600.00 |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2500.00 | 2400.00 |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 2000.00 | 1800.00 |
| 4616 | 4616 | X-cel Ltd | Xaria | Xanda | 1500.00 | 1800.00 |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 3000.00 | 3600.00 |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 1500.00 | 3000.00 |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 2000.00 | 2400.00 |
| 4617 | 4617 | Yeovil Yards Ltd | Yusef | Yorke | 1500.00 | 1800.00 |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 1000.00 | 0.00 |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 1500.00 | 1800.00 |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 1000.00 | 3000.00 |
| 4618 | 4618 | Zen Products Ltd | Zane | Zellweger | 2000.00 | 3400.00 |
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
FROM customer AS c
INNER JOIN transactions AS t
ON c.cust_id = t.trans_cust_id
ORDER BY cust_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
FROM customer c
INNER JOIN transactions t
ON c.cust_id = t.trans_cust_id
ORDER BY cust_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.