Score dates with SQL
Not just one, I’m talking big count here…
Enter the World of Data EP.3.
I am not kidding! You can get a date by just using getDate() (Lame Joke Alert !🚨 ).
All jokes apart, I would like to let you know that this is the third EP.3 article in the series. Check out my previous articles EP.1 & EP.2.
Data plays a very curial role in the business world, blah…blah….blah….All Business Scientists and Data Scientists out there emphasis on data blah…blah…blah…, but why is that?
Data of each day matters, observing patterns of sales or activities or anything gives a lot of insight and helps organizations make better business decisions.
Data is typically stored in Data Warehouses or Data Bases. And we retrieve/dig the data from these databases to clean, analyze, do changes technically whatever we want to do with the data. Thus, SQL.
SQL (Structured Query Language) is a language to make the databases do and give what you want and it can be implemented through other programming languages like Python, Java, C#, etc..
*It is straightforward to learn and typically takes a week.*
Once you google SQL, you might end up seeing words like MySQL, PL/SQL, Microsoft SQL Server, PgAdmin, PostgreSQL, Oracle D.B.……..so much more and these can mess up your brain.
Do not panic, take a deep breath in and let it out.
Get familiar with the terms and definitions first:
Database: is an organized collection of structured information, or data, typically stored electronically in a computer system, server, and cloud.
Databases are usually controlled by DBMS(DataBase Management System: where data is stored as files), due to technical drawbacks, they aren’t fancied.
Relational Database Management System: is a database that stores structured data, and there is a relation between the data. It stores data in tabular format.
It has column and rows which contains related data entries. Columns are a vertical entity of a table. It includes the attribute of records, and rows are the horizontal entity that provides records/data.
Data Warehouse: is a central repository for the data; it is a type of database designed explicitly for fast query and analysis.
Schema: represents the logical configuration of all or part of a relational database. It can exist both as a visual representation and as a set of formulas known as integrity constraints that govern a database.
The above schema shows the logical relation between the Albums table to the Artists table and Genre table. *Please observe carefully*
These formulas are expressed in a data definition language, such as SQL.
SQL: most databases use structured query language (SQL) for writing and querying data. Data can then be easily accessed, managed, modified, updated, controlled, and organized with SQL.
NoSQL Or Nonrelational Database: allows unstructured and semistructured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed).
NoSQL databases grew popular as web applications became more frequent and more complex.
Not all NoSQL databases support SQL though all other NoSQL query language developed on the foundations of SQL.
Since I cannot teach SQL in just one article, let me provide the right resources and guides to learn SQL from fundamentals into advanced concepts, and after you read this article there is only one final step left ->
DECLARE @Counter INT
SET @Counter=1
WHILE ( @Counter <= ∞)
BEGIN
PRINT ‘PRACTICE….!’
END
Before I started learning SQL, my uncle told me one thing to always keep in mind, and that is SQL is a granular method of asking the computer to do data tasks and store it.
*To be honest, this approach benefited me.*
Let us take a look at this piece of code:
Let’s say I previously asked my computer to create a table called registrations (information regarding registrations for an app) with some attributes(the title of the column contents) like user I.D., source, location, etc…fully loaded.
Now my boss asks me, “Hey, Sush! I would want to know through which source we get most registrations.”
Me being an obedient employee, I used my little SQL magic (the query made above)only to find out that through “Friend Invites,” our app gets most of its registrations and the least through “Paid Sources” (psssttt….we know why).
What secret code got me this info…? Well, I passed a command to the computer to SELECT the source column, COUNT (count everything top to bottom in that column=> *) FROM the registrations table, and yeah one thing more, please GROUP the sources BY each source type (here they are invite_a_friend, paid, google and article) and present it to me NOW.
Based on that piece of information, my team built strategical models to optimize the results of our app. My boss was very impressed.
If you comprehended the above example, then -Congratulations…! you just learned 5% of SQL already.
Now, let’s dive into a simple map below:
Just the way I have broken down the query above, please try to break down each query in the map to comprehend(google) what it does.
It is now your responsibility to dig deeper into SQL and explore; however, I would guide you through by suggesting tutorials, books, and lectures picked by me. Firstly, check out the Lecture by David J. Malan from Harvard talking SQL.
I personally recommend everybody on this planet to listen to the whole 111-minute lecture.
Ok now let’s get done with SQL
Books (my personal picks):
- “Learning SQL” By Alan Beaulieu — Foundational-Intermediate Level
2. “SQL Cookbook” By Anthony Molinaro — Advanced Level
If you are like me, please order the physical copies.
Lectures/Tutorials (again, my personal picks):
- FreeCodeCamp SQL (This tutorial helps you to install MySQL, which is a database management system and everything else you need)
2. Edureka
3. Derek Banas
5. Stanford Online SQL course on edX (you only have to pay if you need the certificate)
6. IBM SQL for Data Science course on edX (you only have to pay if you need the certificate)
P.S.: Each tutorial is different, and you have to explore all the options above to pick the right fit for you.
Also, google anytime, and every time you don’t understand something.
