Master these 4 skills to up your game with Microsoft SQL Server

If you want to become an amazing developer then getting good at SQL is an absolute must! Not only is SQL the standard language for interacting with relational databases, it’s also used as the underlying query language for many BI tools such as SQL Server Reporting Services, Tableau and Power BI.

Here are four skills that will have you SQLing like pro!

Wildcards and Like Statements

With LIKE statements, you can use wildcards such as % and _ to represent any number of characters or a single character, respectively. This allows you to search for patterns within text data, such as finding all the customers whose names start with “Jo” or all the products whose descriptions contain the word “blue.”. The syntax for a ‘LIKE’ statement is as follows:

Select
*
from
Table
where
colour like '%blue%'

Here are some examples of using wildcards in practice!

• ‘%cat%’: This pattern would match any string that contains the word “cat” somewhere within it, such as “cat”, “scatter”, “catalog”, and so on.
• ‘_at’: This pattern would match any three-letter string that ends in “at”, such as “cat”, “bat”, and “rat”.
• ‘%at%’: This pattern would match any string that contains the word “at” somewhere within it, such as “cat”, “bat”, “rat”, “flat”, and so on.
• ‘c_t’: This pattern would match any three-letter string that starts with “c” and ends with “t”, such as “cat” and “cot”.

The % wildcard is more flexible and can be used to match a wider range of patterns, while the _ wildcard is more specific and is used to match a single character in a specific position within a string.

And/Or Statements

AND and OR statements are powerful operators that allow you to specify multiple conditions in your WHERE clauses, giving you the ability to filter your data in much more sophisticated ways. With AND/OR statements, you can combine multiple conditions using the AND and OR operators, which can significantly increase the flexibility and complexity of your queries.

Using AND you can specific multiple conditions that must all be met in order for a row to be included in a result set. You could use the following query to find all the shapes that are red squares.

SELECT
*
FROM
Table
WHERE
colour = 'Red'
and
shape = 'Square'

But what if you also want to find the red circles and the blue squares! Then OR is your answer! You can use OR to specify that at least one of multiple conditions must be met. This query will return anything that is either red (so red squares and red circles) or blue (so blue squares!).

SELECT
*
FROM
Table
WHERE
colour = 'Red'
or
shape = 'Square'

AND/OR Combinations

When used together, AND and OR statements allow you to specify multiple conditions in your WHERE clauses and combine them in complex ways. This can give you a great deal of flexibility in filtering your data, but it’s important to use them correctly in order to get the results you want.

One way to combine AND and OR statements is to use parentheses to group conditions and specify the order of operations! You could use this query to return all the red squares OR all the large shapes.

SELECT
*
FROM
Table
WHERE
(colour = 'Red'
and
shape = 'Square') OR size = 'LARGE'

Combining AND and OR operations can be where good SQL goes bad so it’s important to be careful! It’s all too easy to get the order of operations wrong and end up with unexpected results. Brackets are your friends here - use them to group your conditions and specify the order in which they should be executed and you won’t go far wrong!

Common Table Expressions (CTEs)

Common Table Expressions or CTEs are a powerful tool that allow you to define a temporary result set within your query, which you can then query on top of like a regular table or view. This is especially handy when you need to perform complex data manipulations or want to reuse the result that would otherwise be in a subquery multiple times within a query.

A huge benefit of CTEs is that it makes your queries easier to read and maintain! By capturing complex logic into CTEs you can break your queries up into small, manageable and easier to understand pieces.

To create a CTE you use the WITH clause followed by the CTE definition which consists of a name and a select statement:

WITH red AS (Select
*
FROM
Table
WHERE
colour = 'red'
)

Select * from red;

You have to select the result out of the end of a chain of CTEs or else the process will fail!

There are lots of places where you can use your new CTE skills including:

• Performing complex data manipulation or aggregation
• Recursively querying hierarchical data
• Improving the performance of queries by reducing the number of joins or subqueries