Summary: this tutorial introduces you to the basic of the SQL Server SELECT statement, focusing on how to query against a single table.
Basic SQL Server SELECT statement
Database tables are objects that stores all the data in a database. In a table, data is logically organized in a row-and-column format which is similar to a spreadsheet.
In a table, each row represents a unique record and each column represents a field in the record. For example, the customers table contains customer data such as customer identification number, first name, last name, phone, email, and address information as shown below:
SQL Server uses schemas to logically groups tables and other database objects. In our sample database, we have two schemas: sales and production. The sales schema groups all the sales related tables while the production schema groups all the production related tables.
To query data from a table, you use the SELECT statement. The following illustrates the most basic form of the SELECT statement:
SELECT
select_list
FROM
schema_name.table_name;
In this syntax:
- First, specify a list of comma-separated columns from which you want to query data in the
SELECTclause. - Second, specify the source table and its schema name on the
FROMclause.
When processing the SELECT statement, SQL Server processes the FROM clause first and then the SELECT clause even though the SELECT clause appears first in the query.
QL Server SELECT statement examples
Let’s use the customers table in the sample database for the demonstration.
A) SQL Server SELECT – retrieve some columns of a table example
The following query finds the first name and last name of all customers:
SELECT
first_name,
last_name
FROM
sales.customers;
Here is the result:
The result of a query is called a result set.
The following statement returns the first names, last names, and emails of all customers:
SELECT
first_name,
last_name,
email
FROM
sales.customers;
B) SQL Server SELECT – retrieve all columns from a table example
To get data from all columns of a table, you can specify all the columns in the select list. You can also use SELECT * as a shorthand to save some typing:
SELECT
*
FROM
sales.customers;
The SELECT * is useful for examining the columns and data of a table that you are not familiar with. It is also helpful for ad-hoc queries.
However, you should not use the SELECT * for real production code due to the following main reasons:
- First,
SELECT *often retrieves more data than your application needs to function. It causes unnecessary data to transfer from the SQL Server to the client application, taking more time for data to travel across the network and slowing down the application. - Second, if the table is added one or more new columns, the
SELECT *just retrieves all columns that include the newly added columns which were not intended for use in the application. This could make the application crash.
C) SQL Server SELECT – sort the result set
To filter rows based on one or more conditions, you use a WHERE clause as shown in the following example:
SELECT
*
FROM
sales.customers
WHERE
state = 'CA';
In this example, the query returns the customers who locate in California.
When the WHERE clause is available, SQL Server processes the clauses of the query in the following sequence: FROM, WHERE, and SELECT.
To sort the result set based on one or more columns, you use the ORDER BY clause as shown in the following example:
SELECT
*
FROM
sales.customers
WHERE
state = 'CA'
ORDER BY
first_name;
In this example, the ORDER BY clause sorts the customers by their first names in ascending order.
In this case, SQL Server processes the clauses of the query in the following sequence: FROM, WHERE, SELECT, and ORDER BY.
D) SQL Server SELECT – group rows into groups example
To group rows into groups, you use the GROUP BY clause. For example, the following statement returns all the cites of customers located in California and the number of customers in each city.
SELECT
city,
COUNT (*)
FROM
sales.customers
WHERE
state = 'CA'
GROUP BY
city
ORDER BY
city;
In this case, SQL Server processes the clauses in the following sequence: FROM, WHERE, GROUP BY, SELECT, and ORDER BY.
E) SQL Server SELECT – filter groups example
To filter groups based on one or more conditions, you use the HAVING clause. The following example returns the city in California which has more than 10 customers:
SELECT
city,
COUNT (*)
FROM
sales.customers
WHERE
state = 'CA'
GROUP BY
city
HAVING
COUNT (*) > 10
ORDER BY
city;
Notice that the WHERE clause filters rows while the HAVING clause filter groups.
In this tutorial, you have learned how to use the SQL Server SELECT statement to query data from a single table
from WordPress https://ift.tt/3a6UKUz
via IFTTT
