Excel -> SQL

I am also taking CS3200: Database Design and in that class we are not only making relational models, but we are also learning SQL by way of PostgreSQL RDBMS. They tend to be used in very different circumstances. SQLite is more of an embedded library for applications and for fast and efficient interfacing, whereas PostgreSQL is full featured, advanced and generally requires more configuration. MySQL is another RDBMS that is controlled by Oracle (Postgres is Open Source). It is the second most popular RDBMS according to DB-engines.com. We did a little work in MySQL but I dislike it because it doesn’t support CTE or common table expression where you can name different result sets. Without CTE, it is much harder to read the results of a query and queries are not as readable. MySQL also doesn’t have window functions which means that you can’t aggregate data to find things like SUM and AVG as well as many others.

It is interesting to me that SQL is as uncommon to the average white collar worker. Instead, people rely on Excel and other visual aids. It easier to see the data they are manipulating,  it’s visually appealing to see live changes, and it’s subjectively easier to learn. But it also has some major drawbacks. Excel struggles to handle big data and relations. With multiple excel spreadsheets people tend to copy, paste, and interconnect data without actually maintaining data integrity. Similarly, vlookups and pivots hinder performance on Excel greatly. As soon as you work with big data sets Excel becomes almost impossible to work with. It actually can only support 1,048,576 rows by 16,384 columns which may seem like a lot but in huge corporate implementations it is not unheard of. Queries only take milliseconds and have potential to work with millions of tuples.

There is a notion that ‘coding’ or programming languages are naturally hard. In reality, SQL is quite easy and versatile. I have spoken to a couple of people on engineering co-ops and they have said they have learned basic SELECT queries to find data they needed for labs and specifications. Industries do use SQL and I believe that they should teach entry-level workers how to use it.

Excel Limitations: https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

By | 2018-04-18T03:17:05+00:00 April 4th, 2018|0 Comments