Welcome, Dev! As a SQL Server developer or database administrator, you know that writing efficient queries is one of the most important skills to master. Whether you’re retrieving data for a report or updating records, queries are the foundation of all database operations.
In this article, we’re going to explore 20 essential SQL Server queries that you need to know. These queries cover a wide range of use cases, from basic SELECT statements to advanced JOIN and GROUP BY operations. By the end of this article, you’ll have a solid understanding of how to use SQL Server queries to efficiently retrieve and manipulate data.
Query #1: Retrieving Data from a Single Table
The most basic SQL Server query is the SELECT statement. This query allows you to retrieve data from a single table. To use this query, you simply specify the columns you want to retrieve and the table name. Here’s an example:
| Column Name | Data Type |
|---|---|
| id | int |
| name | varchar(50) |
| age | int |
To retrieve all columns from the table, you can use the * wildcard:
SELECT * FROM users;This will return all rows and columns from the users table. If you only want to retrieve certain columns, you can specify them by name:
SELECT name, age FROM users;This will return only the name and age columns for each row in the users table.
Using WHERE Clauses to Filter Data
Often, you’ll need to retrieve only a subset of the data in a table. You can filter the results of a SELECT statement using a WHERE clause. Here’s an example:
SELECT name, age FROM users WHERE age > 30;This will return only the rows where the age column is greater than 30. You can also use other comparison operators, such as <, <=, >, >=, =, and !=, to filter the data.
Sorting Data with ORDER BY
If you want to sort the results of a SELECT statement, you can use the ORDER BY clause. Here’s an example:
SELECT name, age FROM users ORDER BY age DESC;This will return the rows in the users table sorted by age in descending order. You can also sort by multiple columns, like this:
SELECT name, age, city FROM users ORDER BY city ASC, age DESC;This will sort the rows first by city in ascending order, and then by age in descending order.
Limiting Results with TOP
If you only want to retrieve a certain number of rows from a table, you can use the TOP clause. Here’s an example:
SELECT TOP 10 name, age FROM users ORDER BY age DESC;This will return the top 10 rows from the users table, sorted by age in descending order. You can also use the OFFSET and FETCH clauses to retrieve a subset of the results:
SELECT name, age FROM users ORDER BY age DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;This will skip the first 10 rows and retrieve the next 10 rows, sorted by age in descending order.
Query #2: Joining Two Tables
Often, you’ll need to retrieve data from multiple tables. To do this, you’ll use a JOIN statement. Here’s an example:
| Table 1: users | |
|---|---|
| Column Name | Data Type |
| id | int |
| name | varchar(50) |
| age | int |
| Table 2: orders | |
|---|---|
| Column Name | Data Type |
| id | int |
| user_id | int |
| date | datetime |
In this example, we have two tables: users and orders. The users table contains information about each user, and the orders table contains information about each order.
To join these tables, we need to specify a common column. In this case, the common column is user_id. Here’s the query:
SELECT users.name, orders.date FROM users JOIN orders ON users.id = orders.user_id;This query will return the name of each user and the date of each order they’ve placed.
Types of Joins
There are several types of joins that you can use to retrieve data from multiple tables:
- INNER JOIN: Returns only the rows that have matching data in both tables.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table (or NULL if there is no match).
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table (or NULL if there is no match).
- FULL OUTER JOIN: Returns all rows from both tables (or NULL if there is no match).
Here’s an example of using a LEFT JOIN:
SELECT users.name, orders.date FROM users LEFT JOIN orders ON users.id = orders.user_id;This will return all users, even if they haven’t placed an order yet. If a user hasn’t placed an order, the date column will be NULL.
Query #3: Aggregating Data with GROUP BY
When you need to aggregate data in a table, you can use the GROUP BY clause. Here’s an example:
| Table: orders | |
|---|---|
| Column Name | Data Type |
| id | int |
| user_id | int |
| date | datetime |
| amount | decimal(10,2) |
In this example, we have an orders table that contains information about each order, including the user_id, date, and amount of the order.
To aggregate the data in this table, we can use the GROUP BY clause. Here’s an example:
SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id;This will return the total amount of orders for each user. The SUM function is used to add up the amounts for each user, and the GROUP BY clause groups the results by user_id.
Using Aggregate Functions
There are several other aggregate functions that you can use in conjunction with the GROUP BY clause:
- COUNT: Returns the number of rows in the group.
- AVG: Returns the average value of the column in the group.
- MIN: Returns the minimum value of the column in the group.
- MAX: Returns the maximum value of the column in the group.
Here’s an example of using the AVG function:
SELECT user_id, AVG(amount) as average_amount FROM orders GROUP BY user_id;This will return the average amount of orders for each user.
Query #4: Filtering Grouped Data with HAVING
If you want to filter grouped data based on an aggregate function, you can use the HAVING clause. Here’s an example:
SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id HAVING SUM(amount) > 1000;This will return only the groups where the total amount of orders is greater than 1000.
Query #5: Updating Data in a Table
When you need to update data in a table, you can use the UPDATE statement. Here’s an example:
UPDATE users SET age = 30 WHERE name = 'John';This will update the age column for any user named John to 30.
Using Joins with Updates
You can also use joins with updates to update data in multiple tables. Here’s an example:
UPDATE users SET age = 30 FROM users JOIN orders ON users.id = orders.user_id WHERE orders.date > '2020-01-01';This will update the age column for any user who has placed an order after January 1st, 2020.
Query #6: Deleting Data from a Table
When you need to delete data from a table, you can use the DELETE statement. Here’s an example:
DELETE FROM users WHERE name = 'John';This will delete any rows from the users table where the name column is John.
Using Joins with Deletes
You can also use joins with deletes to delete data from multiple tables. Here’s an example:
DELETE FROM users FROM users JOIN orders ON users.id = orders.user_id WHERE orders.date < '2020-01-01';This will delete any users who have not placed an order since January 1st, 2020.
Query #7: Using Subqueries
Subqueries allow you to use the results of one query as the input for another query. Here’s an example:
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE date > '2020-01-01');This query will return the name of any user who has placed an order since January 1st, 2020.
Using EXISTS
You can also use the EXISTS function to check if a subquery returns any results. Here’s an example:
SELECT name FROM users WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);This query will return the name of any user who has placed an order.
Query #8: Using Case Statements
Case statements allow you to add conditional logic to your queries. Here’s an example:
SELECT name, age, CASE WHEN age < 30 THEN 'Young' ELSE 'Old' END as age_group FROM users;This query will add an age_group column to the results, which will be either Young or Old based on the age of each user.
Using Case Statements with Group By
You can also use case statements with the GROUP BY clause. Here’s an example:
SELECT age_group, COUNT(*) FROM (SELECT name, age, CASE WHEN age < 30 THEN 'Young' ELSE 'Old' END as age_group FROM users) as age_groups GROUP BY age_group;This query will group the age_groups subquery by age_group and count the number of rows in each group.
Query #9: Using Common Table Expressions (CTEs)
Common Table Expressions allow you to define a temporary table that can be used in your query. Here’s an example:
WITH high_spenders AS (SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id HAVING SUM(amount) > 1000)SELECT users.name, high_spenders.total_amount FROM users JOIN high_spenders ON users.id = high_spenders.user_id;This query defines a CTE called high_spenders that finds any user who has spent more than 1000 in total. The main query then joins this CTE with the users table to retrieve the names of these users.
Recursive CTEs
You can also use CTEs to generate recursive queries, like this:
WITH recursive cte AS (SELECT 1 as levelUNION ALLSELECT level + 1 FROM cte WHERE level < 10)SELECT * FROM cte;This query defines a recursive CTE that generates a series of numbers from 1 to 10. The initial query selects the number 1, and the recursive query adds 1 to this number until the level reaches 10.
Query #10: Using Window Functions
Window functions allow you to perform calculations over a set of rows within your query. Here’s an example:
| Table: orders | |
|---|---|
| Column Name | Data Type |
| id | int |
| user_id | int |
| date | datetime |
| amount | decimal(10,2) |
In this example, we have an orders table that contains information about each order, including the user_id, date, and amount of the order.
To calculate the running total of orders for each user, you can use the SUM function as a window function. Here’s an example:
SELECT user_id, date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY date) as running_total FROM orders;This query will return the user_id, date, amount, and running_total columns for each row in the orders table. The running_total column is calculated using the SUM function as a window function, which adds up the amounts for each user in date order.
Other Window Functions
There are several other window functions that you can use in your queries:
- ROW_NUMBER: Returns a unique number for each row within a group.
- RANK: Returns the rank of each row within a group (ties receive the same rank).
- DENSE_RANK: Returns the rank of each row within a group (ties receive the same rank, but the next rank is skipped).
- LEAD and LAG: Return the value of the next or previous row in the partition.
Here’s an example of using the ROW_NUMBER function:
SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) as row_number FROM users;This query will return the name, age, and row_number columns for each row in the users table, with the rows ordered by age in descending order.
Query #11: Using Pivots
Pivots allow you to convert rows into columns in your query results. Here’s an example:
| Table: orders |
|---|