In SQL Server, the SELECT statement is used to retrieve data from tables in a database. The syntax of a basic SELECT statement is as follows:
SELECT column1, column2, ... FROM table_name;Here, ‘column1‘, ‘column2‘, and so on are the names of the columns in the table that you want to retrieve data from, and ‘table_name‘ is the name of the table. You can also select all columns from a table by using the ‘*‘ wildcard character:
SELECT * FROM table_name;You can also use the SELECT statement with various optional clauses to filter and sort the data, as well as to perform calculations and aggregate functions. Here are some examples of these clauses:
- WHERE: used to filter data based on a specified condition. For example, the following statement retrieves all rows from the ‘orders‘ table where the ‘status‘ column is equal to ’Pending’:
SELECT * FROM orders WHERE status = 'Pending';- ORDER BY: used to sort the data in ascending or descending order based on one or more columns. For example, the following statement retrieves all rows from the ‘products‘ table sorted in descending order by the ‘price‘ column:
SELECT * FROM products ORDER BY price DESC;- GROUP BY: used to group the data by one or more columns, and perform aggregate functions on each group. For example, the following statement retrieves the total revenue for each category from the ‘orders‘ and ‘order_details‘ tables:
SELECT c.category_name, SUM(o.quantity * o.unit_price) AS total_revenue
FROM categories AS c
INNER JOIN products AS p ON c.category_id = p.category_id
INNER JOIN order_details AS o ON p.product_id = o.product_id
GROUP BY c.category_name;In addition to these clauses, the SELECT statement can also be used with various other options and functions to perform more complex calculations and operations on the data.