Database Concepts

Understanding the Correlated Subquery in SQL

by

When writing SQL queries, we sometimes find ourselves including a subquery in the query in order to get the results we want. Sometimes the subquery we include could be taken out of that query and run by itself to return its own independent result set. But not the correlated subquery.

A correlated subquery relies on the outer query for its data. We can’t simply move it outside of the outer query and expect it to work.

Let’s take a look the correlated subquery in SQL and observe it in action with a few examples.

Read more

Understanding Filtered Indexes in SQL

by

In SQL, indexing can be a great way to improve query performance across our database. We can create indexes to support the most commonly used queries, and these indexes can help our queries run much faster.

But it’s not always a matter of just “adding an index” and leaving it at that. If we really want the best bang for our buck, we usually need to look at how best to implement the index. There are many decisions that go into designing the best index for the job. And one such decision might be, should this be a filtered index?

But what exactly is a filtered index, and how can it improve our database efficiency?

Let’s find out.

Read more

What is a UNIQUE Constraint?

by

A UNIQUE constraint is a rule we apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows. This means that no two rows in the table can have the same value(s) in the column(s) where we’ve applied the UNIQUE constraint. It’s an essential tool for maintaining data integrity and preventing duplicate entries in our database.

Read more

SQL for Beginners: 10 Must-Know Concepts to Kickstart Your Database Journey

by

SQL stands for Structured Query Language. It’s the go-to query language for most of us who develop relational databases. If you want to work with relational database management systems, learning SQL is pretty much non-negotiable. Especially if you want to become a SQL developer.

If you’re interested in becoming a SQL developer, here are ten essential SQL concepts that you need to learn.

Read more

10 Essential Database Concepts that All Beginners MUST Learn

by

When I first started building websites 25 years ago, everything was static. Our primary building block was HTML, with a bit of CSS and JavaScript sprinkled in for good measure. Our content would be incorporated into the HTML documents, and so there was no separation of functionality and content.

Regardless, I was just happy to be able to build these amazing things that I could publish for the world to see.

But within a few years I had quickly learned about the power of databases and how they could completely transform the websites we were building at the time.

Read more

What is a Composite Primary Key?

by

Primary keys are a crucial part of relational database management systems (RDBMSs). Most of the time we create a primary key constraint against a single column that is the unique identifier for the table.

But we can also create primary key constraints against more than one column. When we do this, we call it a composite primary key.

Composite keys can be handy when we don’t have a single column that contains unique values, but multiple columns can be combined to create a unique value.

Read more

Understanding INFORMATION_SCHEMA in SQL

by

Behind every well-structured and organised SQL database lies a treasure trove of information called metadata. Metadata is data that provides information about other data. In relational database management systems, this metadata can typically include information like the names of databases, tables, columns, the data type of a column, access privileges, and much more.

The SQL standard provides a collection of tables/views that allow us to view metadata about the objects in the database. The SQL standard refers to this as the information schema and calls it INFORMATION_SCHEMA.

Read more