A journey towards advanced and beginner SQL concepts

Comprehensive overview on beginner and advanced sql concepts including basic sql queries, window functions, subqueries, cte’s etc

Image courtesy from unsplash(Frank Chamaki)

SQL is an integral part of Data Science and analytics. However, many people just know to right basic queries and do not really focus on the basics. This ultimately results in poorly written queries and performance issues in analytics.

Recently, I have been reading a lot of books on SQL and database management and wanted to compile the knowledge into a series of articles.

I plan to organise the content of this series in the below format

1. Introduction to SQL
2. Basic SQL Queries
3. Joins and Subqueries
4. Common Table Expressions
5. Window Functions
6. Programmatic SQL

What is SQL?

• SQL stands for Structured Query Language.
• SQL is a standard language that was designed to query and manage data in relational database management systems (RDBMSs).
• An RDBMS is a database management system based on the relational model (a semantic model for representing data), which in turn is based on two mathematical branches: set theory and predicate logic.

Let’s look at set theory and predicate logic in a bit more detail to understand from a sql context.

Set Theory

Set Theory was compiled by the Mathematician Georg Cantor. Let’s look at what the definition as and the key concepts.

One major concept which is not covered in this definition is the order. A set does not have a specific order. You can represent {a,b,c} set as {b,c,a}. In a database terms this means that when you query, the results can be returned in any specific order until specified.

Predicate Logic

A predicate is a logical expression which either holds true or false.

Common examples of Predicate logic are as below :

Relational Data Model

• The goal of the relational model is to enable consistent representation of data with minimal or no redundancy and without sacrificing completeness, and to define data integrity (enforcement of data consistency) as part of the model

There are 3 types of Relational Data Model Systems

Schema and Tables

• Schema is a collection Of Objects such as tables, views, stored procedures and so on.
• Schema is also used as a namespace.
• It is used as a prefix to the objects. For example : If you have a invoice table in the sales schema then the name will be sales.order table.

Maintaining Data Integrity in tables

As mentioned earlier, a key advantage of RDBMS is maintaining data integrity.

Let’s look at the different types of data integrity that can be maintained in the tables.

This article provided a brief overview of the foundation of sql. In the upcoming articles we will explore the the foundations of advanced and basic sql queries.

References

• T-SQL Fundamentals