SQL and Excel - A Dynamic Duo
Spreadsheets such as Microsoft Excel are well suited to tasks involving the manipulation of small amounts of related data.
Working out a budget, producing visual reports, organizing lists and calculations that involve many variables are all tasks well suited to a spreadsheet.
There are some data related tasks however that spreadsheets such as Microsoft Excel are not suited for.
Tasks involving the processing and combination of large sets of data for example are generally not well suited to spreadsheets.
There is another technology with a long history and theoretical background that specializes in these sorts of tasks.
That technology is relational databases.
The most common way people insert data into and extract data from relational databases is via the language of Structured Query Language.
Structured Query Language, often referred to as SQL, is a grammar of instructions that allows us to tell a relational database to add, modify or delete data.
The key benefit, pardon the pun, of SQL is that it allows us to craft instructions relating large sets of data together.
In this way SQL is the natural complement to the single cell and formula based interface of spreadsheets like Microsoft Excel.
Imagine you had five hundred appointments from your business calendar laid out in a table.
Each appointment might have a day, time, location and description.
Now imagine you also had five hundred appointments from your partners business calendar, also each having a day, time, location and description.
Given this data set imagine trying to find out which Fridays you were busy at an appointment at noon while your partner was also busy at an appointment at noon and the descriptions of both of your appointments contained the phrase down town.
If you are not familiar with relational databases and SQL it might surprise you to know that the question can be answered by a single simple SQL query.
The database and SQL don't have it all their own way however.
Spreadsheets come in to their own for tasks that benefit from a visual representation.
Traditionally databases do not provide a visual way to browse the data in tables without explicitly requesting data.
For a small set of data browsing in a spreadsheet may enable you to find what you need more quickly than running SQL queries.
Spreadsheets also make it easy to update dependent data when data is changed.
A total cell in a spreadsheet for example can easily be assigned a formula that will update its value when other amounts are changed.
Such functionality is possible within a database but requires much more work.
Ultimately though there is no doubting that both spreadsheets and databases address their own set of tasks very well.
The best solution is to have both of these great tools in your tool box.
Working out a budget, producing visual reports, organizing lists and calculations that involve many variables are all tasks well suited to a spreadsheet.
There are some data related tasks however that spreadsheets such as Microsoft Excel are not suited for.
Tasks involving the processing and combination of large sets of data for example are generally not well suited to spreadsheets.
There is another technology with a long history and theoretical background that specializes in these sorts of tasks.
That technology is relational databases.
The most common way people insert data into and extract data from relational databases is via the language of Structured Query Language.
Structured Query Language, often referred to as SQL, is a grammar of instructions that allows us to tell a relational database to add, modify or delete data.
The key benefit, pardon the pun, of SQL is that it allows us to craft instructions relating large sets of data together.
In this way SQL is the natural complement to the single cell and formula based interface of spreadsheets like Microsoft Excel.
Imagine you had five hundred appointments from your business calendar laid out in a table.
Each appointment might have a day, time, location and description.
Now imagine you also had five hundred appointments from your partners business calendar, also each having a day, time, location and description.
Given this data set imagine trying to find out which Fridays you were busy at an appointment at noon while your partner was also busy at an appointment at noon and the descriptions of both of your appointments contained the phrase down town.
If you are not familiar with relational databases and SQL it might surprise you to know that the question can be answered by a single simple SQL query.
The database and SQL don't have it all their own way however.
Spreadsheets come in to their own for tasks that benefit from a visual representation.
Traditionally databases do not provide a visual way to browse the data in tables without explicitly requesting data.
For a small set of data browsing in a spreadsheet may enable you to find what you need more quickly than running SQL queries.
Spreadsheets also make it easy to update dependent data when data is changed.
A total cell in a spreadsheet for example can easily be assigned a formula that will update its value when other amounts are changed.
Such functionality is possible within a database but requires much more work.
Ultimately though there is no doubting that both spreadsheets and databases address their own set of tasks very well.
The best solution is to have both of these great tools in your tool box.
