Basic knowledge of SQL Server

1. Filter data.

(1) Select Distinct clause.

The SELECT DISTINCT clause retrieves unique different values from a specified column list, in other words, it removes duplicate values from the column in the result set. The DISTINCT clause treats all NULL values as the same value.

The syntax is as follows:

SELECT DISTINCT
column_name1,
column_name2 ,
...
FROM
table_name;

A. The following statement returns all cities in the Customers table where all customers are located, as an example of a DISTINCT field:

SELECT
city
FROM
sales.customers
ORDER BY
city;

B. DISTINCT multi column example: The following statement finds different cities and states for all customers.

SELECT DISTINCT
city,
state
FROM
sales.customers

C. DISTINCT has a null value example. The following example finds a different (unique) phone number for a customer:

SELECT DISTINCT
phone
FROM
sales.customers
ORDER BY
phone;

(2) SQL Server WHERE clause.

To retrieve rows from a table that meet one or more conditions, use the WHERE clause as follows:

SELECT
select_list
FROM
table_name
WHERE
search_condition;

In the WHERE clause, specify the search criteria to filter the rows returned by the From clause. The WHERE clause only returns rows that result in the search condition being evaluated as true. The search criteria are logical expressions or a combination of multiple logical expressions.

Retrieve all products with category id 1 by using simple equality to find the following statement:

SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
category_id = 1
ORDER BY
list_price DESC;

(3) AND Find rows that meet two conditions.

AND is a logical operator used to combine two Boolean expressions. The following example returns a product that satisfies two conditions: category_ID is 1, model_Year is 2018. It uses the logical operator AND to combine these two conditions.

SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
category_id = 1 AND model_year = 2018
ORDER BY
list_price DESC;

(4) (>,=,<) Use comparison operators.

SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price > 300 AND model_year = 2018
ORDER BY
list_price DESC;

(5) OR search for rows that meet any one of the conditions.

OR is a logical operator used to combine two Boolean expressions. When using multiple logical operators in a statement, SQL Server will calculate the OR operator after the AND operator. However, the evaluation order can be changed using parentheses.

A. Using the OR operator as an example, search for products with a price greater than 3000 or model 2018 in the following query. Any product that meets one of the conditions is included in the result set.

SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price > 3000 OR model_year = 2018
ORDER BY
list_price DESC;

(6) Between Find Rows for Values Between Two Values.

A. SQL Server BETWEEN two numerical examples: Find products with prices between 1899 and 1999.99 using the following statement:

SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price BETWEEN 1899.00 AND 1999.99
ORDER BY
list_price DESC;

B. SQL Server BETWEEN Two Date Examples The following query finds orders placed by customers between January 15, 2017 and January 17, 2017:

SELECT
order_id,
customer_id,
order_date,
order_status
FROM
sales.orders
WHERE
order_date BETWEEN '20170115' AND '20170117'
ORDER BY
order_date;

(7) Find rows with values in the IN value list.

The following example uses the IN operator to find products with prices of 299.99, 466.99, or 489.99.

SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price IN (299.99, 369.99, 489.99)
ORDER BY
list_price DESC;

8. Like search package specifies lines containing strings.

A. % (percentage) wildcard to find customers whose last name (lastname) starts with the letter z:

SELECT
customer_id,
first_name,
last_name
FROM
sales.customers
WHERE
last_name LIKE 'z%'
ORDER BY
first_name;

C. The [list of characters] wildcard character is enclosed in square brackets with a list of characters, for example: [ABC] represents a single character, which must be one of the specified characters in the list. For example, the following query returns customers with the first character Y or Z in their last name (lastname):

SELECT
customer_id,
first_name,
last_name
FROM
sales.customers
WHERE
last_name LIKE '[YZ]%'
ORDER BY
last_name;

E. The [^] wildcard character has an caret (^) followed by a range, such as [A-C] or a character list, where the square brackets of [^ ABC] indicate a single character that is not within the specified range or character list. For example, the following query returns customer information where the first character in the surname is not a letter in the range A to X:

SELECT
customer_id,
first_name,
last_name
FROM
sales.customers
WHERE
last_name LIKE '[^A-X]%'
ORDER BY
last_name;

F. The following example uses the NOT LIKE operator to find customers whose first character in a name is not the letter A:

SELECT
customer_id,
first_name,
last_name
FROM
sales.customers
WHERE
first_name NOT LIKE 'A%'
ORDER BY
first_name;