MSDN Documentation

Basic SQL Queries

This section covers the fundamental SQL (Structured Query Language) statements used for retrieving data from a relational database. Understanding these basic queries is essential for anyone working with SQL Server.

The SELECT Statement

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

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Selecting All Columns

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

SELECT *
FROM Customers;

Selecting Specific Columns

You can specify which columns you want to retrieve by listing their names after SELECT.

SELECT CustomerName, ContactName, City
FROM Customers;

The WHERE Clause

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

Example:

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

Operators in the WHERE Clause

You can use various operators to define conditions:

• = : Equal
• <> or != : Not equal
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to
• BETWEEN : Between an inclusive list of values
• LIKE : Search for a pattern
• IN : Specify a list of possible values

Using LIKE for Pattern Matching

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

• % : Represents zero, one, or multiple characters
• _ : Represents a single character

Example: Find customers whose names start with 'A'.

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'A%';

The ORDER BY Clause

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

• ASC : Ascending order (default)
• DESC : Descending order

Example: Sort customers by city name in ascending order.

SELECT CustomerName, City
FROM Customers
ORDER BY City ASC;

Example: Sort customers by country name in descending order.

SELECT CustomerName, Country
FROM Customers
ORDER BY Country DESC;

The INSERT INTO Statement

The INSERT INTO statement is used to add new records to a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

If you are adding values for all columns, you may not need to specify the column names. However, it is good practice to do so.

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

The UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Important: Be careful with the UPDATE statement. Without a WHERE clause, you will update all records in the table!

Example: Change the city to 'Oslo' for the customer with the ID 90.

UPDATE Customers
SET City = 'Oslo'
WHERE CustomerID = 90;

The DELETE Statement

The DELETE statement is used to delete existing records in a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Important: Without a WHERE clause, you will delete all records in the table!

Example: Delete the customer with the CustomerName 'Alfreds Futterkiste'.

DELETE FROM Customers
WHERE CustomerName = 'Alfreds Futterkiste';

Sample Table: Products

Using these basic statements, you can effectively interact with your SQL Server databases to retrieve, manipulate, and manage data.