SQL-LEARNING DIARY4

8.IN Operator

specify multiple values in a WHERE clause

1.IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or；

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

2. Example

method1: IN

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

method2: NOT IN

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

method3: IN (SELECT * FROM *)

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

9.BETWEEN Operator

1.selects values within a given range AND begin and end values are included.
(The values can be numbers, text, or dates.)

1. BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

2.NOT BETWEEN

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

3.BETWEEN with IN

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND NOT CategoryID IN (1,2,3);

4.BETWEEN Text Values example

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

5.NOT BETWEEN Text Values Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

6.BETWEEN Dates Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

and

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Note: the format of date

10 SQL Aliases

give a table, or a column in a table, a temporary name for the duration of the query.
仅在查询期间使用aliases，给予表格或者某列元素进行暂时的别名

Aliases can be useful when:
-There are more than one table involved in a query
-Functions are used in the query
-Column names are big or not very readable
-Two or more columns are combined together

1.Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

2.Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

3.Example
【1】

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

Note: It requires double quotation marks or square brackets if the alias name contains spaces
如果别名名称包含空格，则需要双引号或方括号

【2】an alias named "Address" that combine four columns (Address, PostalCode, City and Country)

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

Reference:https://www.w3schools.com/sql/default.asp