SQL Server Documentation

Understanding Basic SQL Queries in SQL Server

Welcome to this introductory tutorial on writing basic queries in SQL Server. SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. In this guide, we'll cover fundamental query operations to help you retrieve and understand your data.

We'll assume you have a basic understanding of database concepts like tables, columns, and rows. Let's dive into the most common query types.

1. Selecting Data with SELECT

The SELECT statement is used to query the database and retrieve data that matches criteria you specify.

Retrieving All Columns

To retrieve all columns from a table, you can use the asterisk (*) wildcard:

SELECT *
FROM Customers;

This query will return all rows and all columns from the Customers table.

Retrieving Specific Columns

To retrieve only specific columns, list them after the SELECT keyword, separated by commas:

SELECT CustomerID, CompanyName, ContactName
FROM Customers;

This query returns the CustomerID, CompanyName, and ContactName for all customers.

2. Filtering Data with WHERE

The WHERE clause is used to extract only those records that fulfill a specified condition.

Simple Conditions

You can filter based on specific values:

SELECT CustomerID, CompanyName
FROM Customers
WHERE Country = 'USA';

This retrieves customers located in the USA.

Using Comparison Operators

SQL supports various comparison operators:

• = Equal to
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to
• <> Not equal to

Example using >:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 50;

Combining Conditions with AND and OR

You can combine multiple conditions using AND (both must be true) and OR (at least one must be true).

SELECT OrderID, OrderDate, ShipCountry
FROM Orders
WHERE ShipCountry = 'Germany' AND OrderDate > '2023-01-01';

SELECT ProductName, SupplierID
FROM Products
WHERE SupplierID = 1 OR SupplierID = 3;

3. Sorting Data with ORDER BY

The ORDER BY clause is used to sort the result-set in ascending or descending order.

Ascending Order (Default)

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice;

Descending Order

Use the DESC keyword for descending order:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Sorting by Multiple Columns

You can sort by more than one column:

SELECT CustomerID, City, Country
FROM Customers
ORDER BY Country ASC, City DESC;

4. Limiting Results (TOP in SQL Server)

SQL Server uses the TOP keyword to limit the number of rows returned.

SELECT TOP 10 CustomerID, CompanyName
FROM Customers;

This will return only the first 10 customers from the table, based on the default sort order (or any specified ORDER BY clause).

5. Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single value.

COUNT()

Counts the number of rows:

SELECT COUNT(*) AS NumberOfCustomers
FROM Customers;

The AS keyword is used to assign an alias to the result column.

SUM(), AVG(), MIN(), MAX()

These functions calculate the sum, average, minimum, and maximum values of a numeric column, respectively.

SELECT AVG(UnitPrice) AS AverageProductPrice
FROM Products;

SELECT MAX(OrderDate) AS LatestOrderDate
FROM Orders;

Putting It All Together

Here's an example combining several clauses:

SELECT TOP 5 CustomerID, CompanyName, City
FROM Customers
WHERE Country = 'UK'
ORDER BY CompanyName ASC;

This query retrieves the top 5 customers from the UK, ordered alphabetically by their company name.

Conclusion

You've now learned the basics of writing SQL queries in SQL Server, including selecting data, filtering with WHERE, sorting with ORDER BY, limiting results with TOP, and using common aggregate functions. These fundamental concepts will form the building blocks for more complex database operations.

Continue practicing these queries with your own data to solidify your understanding.