What is this?
To be honest, I haven’t done much blogging or writing lately. Life (read: work) happens, and it gets busy. However, I’m feeling invigorated with a strong desire to expand my horizons. One such manifestation (snooze, I know) of this is to learn a bunch of new things, for where technology and industry are headed.
Thus, I present to you, learnings on SQL from a course I’m taking! Frankly, this should be boring to a majority of people. However, this is a way of self-accountability, and sharing what I learn along the way about Structured Query Language (SQL) programming, and hopefully how it applies to a bunch of awesome things! If you haven’t bounced already, here we go.
Square 0: What are Databases?
Databases are systems that allow users to organize and store large amounts of data. Databases can have a wide variety of users and use cases. Potential database / SQL users include analysts in marketing, business or sales. Additionally, technically-focused personnel such as data scientists, software engineers and web developers may also use databases for a range of purposes.
Discussing the Transition from Spreadsheets to Databases
Many practitioners in business have some familiarity with a spreadsheet program, such as Microsoft Excel, Google Sheets or Open Office.
Spreadsheets are often useful for a one-time analysis piece or quick charting, particularly with small data set sizes. Additionally, usage of a spreadsheet program ensures that a wide range of folks with varying expertise can access, use and manage the spreadsheet data.
So where do databases come in? Databases are great for ensuring data integrity and/or handling large & robust data sets/ Databases are also great for use cases where you need to quickly merge different data sets or automate actions with your data for frequent usage or re-use. Additionally, databases are widely used in powering websites and other applications.
In summary, the transition from spreadsheet to data comes when you bring massive amounts of data to life! The hope of the educational materials are that institutional knowledge in spreadsheets can be leveraged to help understand and master SQL database usage.
What are some quick hacks to translating spreadsheets to databases? The first is that tabs of a spreadsheet can be set equal in your mind to tables. Within each table is contained a set of rows, and a set of columns.
What are some of the top SQL database / data warehouse platforms options?
For your enlightenment and education, below are some common SQL database and data warehouse platforms.
- Postgre SQL (Preferred for example use: free and open source, widely used on the internet, multi-OS)
- Amazon Redshift data warehouse
- Microsoft Access
- Microsoft SQL Server (MS SQL Server Express)
- Oracle Database
In addition to straight database programs and data warehouses, there a range of other programs that utilize the SQL programming language for core functions. Below is a short (and rather incomplete list) of other popular SQL applications and uses.
Other Applications of SQL
- Periscope Data
- Hive (On top of Hadoop)
- Google BigQuery
- Facebook Presto
A Bit More About SQL
SQL stands for Structured Query Language. It can be applied in a wide range of manners, including PostGreSQL that will be used in these examples. Further, SQL is the programming language that will be used to communicate with databases in this experience.
To start, a sample SQL statement:
SELECT customer_id, first_name, last_name
ORDER BY first_name
Here’s a quick overview of what I learned, and have consequently shared.
- What’s a database?
- How is a database different from a spreadsheet?
- Why should a database be used?
- What are some database / data warehouse platforms that operate with SQL?
If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.
Upcoming Educational Material on SQL
- PostgreSQL Installation
- Databases and Tables Basics
- SQL Syntax and Statement Fundamentals
- GROUP BY Clause
- Advanced SQL
- Additional SQL Commands
- Create Databases and Tables through SQL
- PostgreSQL with Python