How to Write SQL Queries | Database Fundamentals

Why do you need to write SQL?

More and more organisations are making their way slowly on the data-driven path. It’s becoming more and more necessary for people from different backgrounds to need to access the data and query the database(s) storing business-critical data.

Traditionally SQL might have been a language used by DBAs and Developers. These days it might not be just the DBAs and Developers that use it to query the database. You may have business and data analysts and even a “hands-on CTO” or “head of IT” (You know who you are) that want to query your read-only reporting database.

So with more and more people from different roles and backgrounds wanting to access the data, I’m going to write a series of posts for people that are new to querying a relational database. The series is called “How to write SQL queries” and it should provide people who need to retrieve data from your  databases some background into the basics of writing  you the basics of writing SQL SELECT statements to retrieve that valuable data you want to turn into information then make decisions from that information

What you might be concerned with?

Writing a SQL statement is easy. Writing it well can take come practice.

Writing SQL Queries can be a little daunting. At least to begin with but once you understand the basics you can build from there. So, in this post, we will start with some fundamentals of a database and its structure.

This is not intended to be a post on database design. This post is intended more a starter for ten around the terminology used in databases and to provide a basic level of understanding.

It is a place for you to start on your SQL journey.

“Every journey of a 1000 miles starts with a single tep”

What is Structured Query Language (SQL)?

Structured Query Language or SQL, is a programming language used to work with a relational database. There is an ISO/ANSI standard, most vendor platforms, like Microsoft SQL Server, Oracle, DB2 etc have their own flavour or dialect SQL. Microsoft SQL is called Transact-SQL or T-SQL. Most vendors try to adhere to the standard as best they can, however, they all also deviate from it to some degree. SQL is used to, amongst other things, retrieve data from our databases. It’s also used to insert, update and delete data and a whole bunch more too.

Database Fundamental Building Block: A Table

So we now know what SQL is, we’ll turn our attention to database basics and we’ll keep this simple. Let’s say that your database is a model of real-world things. Let’s call those real-world things entities. These can represent real-world things in your business like orders, customers or products.

The tables in your database will represent each of these entities. Tables are sometimes referred to as entities.  The tables are used to store data about these entities. You can think of the table as the logical storage unit for your data on an entity. So for example, your customer data might be in the customer table. Which is important to know when writing your queries.

Database Fundamentals: Table Rows and Columns

A  table in a database will have Columns and Rows

Columns are used to represent the attributes of an entity. For example, our customer table (representing our customers) might have several attributes that make it a customer. A customer might have a name,  phone number, email and so on. These attributes are represented by columns  in the customer table

Rows in a table will represent an instance of that entity. So sticking with our customer table each row in our customer table will represent one customer.

All good so far? – I hope so.

A picture says a thousand words so, let’s clarify what I have just said with a picture. Below is a result set showing the Customer table. Some of the key elements are called out.

Customer table with rows and columns

Database Fundamentals: Columns, Rows and Primary Keys

A couple of other points worth noting, columns are identified by their names, not their position and rows are identified by the value in one or more columns.

A unique column or combination of columns can be used to define the primary key. If your table has a primary key set up on it, the database will enforce uniqueness in the column or columns used in the primary key. Therefore, no other row will have the same value(s) in its key columns and thus can be used to identify the row.

Again an example might help clarify. In our example, the single-column CustomerID is defined as the primary key for our Customer table and can be used to find instances of a customer. The CustomerID can be used to uniquely identify the customer.  You can see from our example that Orlando Gee is CustomerID 1. If we try to insert another row into this table with customerID = 1 the database will throw an error. A Query looking for CustiomerID = 1 will always Orlando Gee’s row

Database Fundamentals: Relationships between tables

In real life, there will be a relationship between our entities. For example, a customer (entity) will have orders (another entity) and they will have an address (a third entity). So if we are modelling these entities in our database we can model that relationship between them too.

Database Fundamentals: Primary Key/Foreign Keys

We can model that relationship between our entities. We can do this using one table’s primary key(The parent table) and a foreign key in the second table (the child table).

Let’s use our customer table as an example. There is a relationship between our Customer table and the CustomerAddres table.

• CustomerID is the Primary Key in the Customer Table
• Customer ID is a foreign key column in the CustomerAddress table

Again a picture says a thousand words. Each customer can be “linked” to multiple addresses, so there is a one-to-many relationship between the Customer and CustomerAddress table.We’ll leave that there for the time being as its enough to get us started.

Showing relationship between Customer and CustomerAddress tables

Writing Queries

Armed with this information we are now ready to write our first SQL Statements. Which will we will do in our next post. Where we will look at the syntax of the SELECT statement

Example Databases

We will be using SQL Server Developer Edition, SQL Server Management Studio and the AdventureWorksLT2019 database for this series of posts. You can download the database here.

Other Useful Information

If you are interested in attending some formal training the check out our Writing SQL Queries training page. If you would like to speak to us directly please use this contact form

How to Write SQL Queries – Previous Articles

• Database fundamentals – How to Write SQL Queries series. In this post, we discuss why it’s important to understand the database design and schema and talk about some basic database fundamentals
• Simple SELECTS –  How to Write SQL Queries series. In the second post, we look at how to write a SELECT statement from a single table
• Column Expressions – How to Write SQL Queries series. We look at column expressions, aliases
• Filter with WHERE – How to Write SQL Queries Series. We will learn how to filter rows returned by our SELECT statement
• Using LIKE for Wildcard searches – How to Write SQL Queries – We look at using LIKE for wildcard searches.
• Using ORDER BY to sort our results We look at sorting the data returned by our query
• Understanding NULLs – In the final post in this series we talk about NULLs and how to handle them

Useful Links

Check out our data analysis videos on YouTube

Analysing FPL data in Power BI

Power Query – Simple Transformations