MSDN Documentation

Home > SQL > Relational Databases > Queries > Query1

Understanding Basic SQL Queries

This document provides an introduction to writing fundamental queries in SQL (Structured Query Language) for relational databases. We'll cover the essential components of a SELECT statement, including selecting columns, filtering rows, and ordering results.

The SELECT Statement

The SELECT statement is the cornerstone of data retrieval in SQL. It allows you to specify which columns you want to retrieve from one or more tables.

Example: Selecting All Columns

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

SELECT *
FROM Customers;

This query will return every column and every row from the Customers table.

Example: Selecting Specific Columns

Often, you only need a subset of the available columns. You can list the desired column names, separated by commas:

SELECT CustomerName, ContactName, Country
FROM Customers;

This query retrieves only the CustomerName, ContactName, and Country columns from the Customers table.

The WHERE Clause: Filtering Rows

The WHERE clause is used to filter records. It extracts only those records that fulfill a specified condition.

Example: Filtering by Country

To find customers located in a specific country, such as 'Germany':

SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Germany';

This query returns the name, city, and country of customers whose Country is 'Germany'.

Example: Using Comparison Operators

You can use various comparison operators (=, >, <, >=, <=, <> or !=) and logical operators (AND, OR, NOT) to define complex conditions.

SELECT ProductName, Price
FROM Products
WHERE Price > 50 AND CategoryID = 1;

This query selects products with a price greater than 50 and belonging to category ID 1.

The ORDER BY Clause: Sorting Results

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

Example: Sorting by Customer Name (Ascending)

To sort the results alphabetically by CustomerName:

SELECT CustomerName, City
FROM Customers
ORDER BY CustomerName ASC;

ASC is the default sort order, so you could also omit it.

Example: Sorting by Price (Descending)

To sort products by price, from highest to lowest:

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

DESC specifies descending order.

Combining Clauses

You can combine these clauses to create more powerful and specific queries. The typical order of clauses in a SELECT statement is:

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY (not covered here)
  5. HAVING (not covered here)
  6. ORDER BY

Example: Combined Query

Find the names and order dates of customers from 'USA' who placed orders after January 1, 2023, sorted by order date:

SELECT C.CustomerName, O.OrderDate
FROM Customers AS C
JOIN Orders AS O ON C.CustomerID = O.CustomerID
WHERE C.Country = 'USA' AND O.OrderDate > '2023-01-01'
ORDER BY O.OrderDate DESC;
Tip: Always try to select only the columns you need. Using SELECT * can be inefficient, especially on large tables.