Learn

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 SELECT clause.
• Second, specify the source table and its schema name on the FROM clause.

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:

1. 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.
2. Second, if the table is added one or more new columns, theSELECT * 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