Author: Tianle Yuan

Structural Operators⚓︎

Glance of contents⚓︎

There are some structural operators that are used to complete SQL sentences:

Operators for expression.
NULL for null expression.
Min & Max for expression.
Count, Avg, Sum for aggregation function expressions.
AND, OR, NOT for conditional WHERE filtering.
Exists for checking the existence of any record.
In shorthand for multiple OR conditions.
Any & All perform a comparison between a single column value and a range of other values.
Order By for sorting condition.
Group By for summarizing rows with the same value.
BETWEEN for selecting values within a given range.
As for name alias.
Having for using aggregation condition.

Operators⚓︎

Operators

Syntax

SQL Arithmetic OperatorsSQL Comparison OperatorsSQL Bitwise Operators

+	-	*	/	%
Add	Subtract	Multiply	Divide	Modulo

SELECT 30 + 20; SELECT 30 - 20; SELECT 30 * 20; SELECT 30 / 20; SELECT 30 % 20;

=	>	<	>=	<=	<>
Equal to	Greater than	Less than	Greater than or equal to	Less than or equal to	Not equal to

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (129, 'Kim', 'B', 'TX', 'Salem'); SELECT * FROM Marks_family WHERE PersonID = 129; SELECT * FROM Marks_family WHERE PersonID > 123; SELECT * FROM Marks_family WHERE PersonID < 129; SELECT * FROM Marks_family WHERE PersonID >= 123; SELECT * FROM Marks_family WHERE PersonID <= 129; SELECT * FROM Marks_family WHERE PersonID <> 129;

&	\|	^
Bitwise AND	Bitwise OR	Bitwise exclusive OR

Null⚓︎

Null

The NULL statements help the comparison where =, <, <>, etc cannot express.

Syntax

SQL

SELECT column_names
FROM table_name
WHERE column_name IS NULL/ IS NOT NULL;

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, '', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); SELECT * FROM Marks_family WHERE LastName IS NULL; SELECT * FROM Marks_family WHERE LastName IS NOT NULL;

Min & Max⚓︎

Min & Max

The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.

Syntax

SQL

SELECT MIN(column_name) / MAX(column_name)
FROM table_name
WHERE condition;

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, '', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); SELECT MAX(PersonID) FROM Marks_family; SELECT MIN(PersonID) FROM Marks_family;

And, Or, and Not⚓︎

And, Or, and Not

The AND, OR, NOT statements are used to express conditional WHERE filtering.

Syntax

SQL

SELECT column1, column2, ...
FROM table_name
--and
WHERE condition1 AND condition2 AND condition3 ...;
--or
WHERE condition1 OR condition2 OR condition3 ...;
--not
WHERE NOT condition;

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Yong', 'C', 'NC', 'Albany'); SELECT PersonID, LastName FROM Marks_family WHERE City = 'Albany' AND LastName = 'Tom'; SELECT PersonID, LastName FROM Marks_family WHERE City = 'Albany' OR LastName = 'Tom'; SELECT PersonID, LastName FROM Marks_family WHERE NOT City = 'Albany';

Exists⚓︎

Exists

The EXISTS operator is used to test for the existence of any record in a subquery. Return TRUE when there are one or more records.

Syntax

SQL

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

click to see an example

CREATE TABLE Marks_family1 ( PersonID int, City varchar(255) ); CREATE TABLE Marks_family2 ( City varchar(255) ); INSERT INTO Marks_family1 (PersonID, City) VALUES (123, 'Albany'); INSERT INTO Marks_family2 (City) VALUES ('Albany'); INSERT INTO Marks_family2 (City) VALUES ('Eugene'); INSERT INTO Marks_family2 (City) VALUES ('Salem'); SELECT * FROM Marks_family1 WHERE EXISTS (SELECT City FROM Marks_family2);

In⚓︎

In

The IN operator is a shorthand for multiple OR conditions, which allow you to specify multiple values in a WHERE clause.

Syntax

SQL

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

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (127, 'Yong', 'C', 'NC', 'Albany'); SELECT * FROM Marks_family WHERE PersonID NOT IN (123,125); SELECT * FROM Marks_family WHERE CITY IN (SELECT City FROM Marks_family WHERE City = 'Salem');

Any & All⚓︎

Any & All

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
ALL can be used with SELECT, WHERE and HAVING statements
ANY means that the condition will be true if the operation is true for any of the values in the range.
ALL means that the condition will be true only if the operation is true for all values in the range.

Symbol	Equal Expression
ANY	SOME
=ANY	IN
<>ALL	NOT IN

Syntax

SQL

-- ANY can be used with WHERE and HAVING statements
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);

-- ALL can be used with SELECT, WHERE and HAVING statements
-- WAY1
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
-- WAY2
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

click to see an example

CREATE TABLE Marks_family1 ( PersonID int, City varchar(255) ); CREATE TABLE Marks_family2 ( PersonID2 int, City varchar(255) ); INSERT INTO Marks_family1 VALUES (123, 'Albany'); INSERT INTO Marks_family2 VALUES (477,'Salem'); INSERT INTO Marks_family2 VALUES (123,'Eugene'); INSERT INTO Marks_family2 VALUES (123,'Salem'); SELECT ALL City FROM Marks_family2; SELECT * FROM Marks_family1 WHERE PersonID = ANY ( SELECT PersonID2 FROM Marks_family2 WHERE PersonID2 < 447 );

Count, Avg, Sum⚓︎

Count, Avg, Sum

The COUNT, AVG, SUM statements are used to express conditional WHERE filtering.

Syntax

SQL

SELECT COUNT(column_name) / AVG(column_name) / SUM(column_name)
FROM table_name
WHERE condition;

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Yong', 'C', 'NC', 'Albany'); SELECT COUNT(PersonID) FROM Marks_family; SELECT AVG(PersonID) FROM Marks_family; SELECT SUM(PersonID) FROM Marks_family;

Order By⚓︎

Order By

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Syntax

SQL

SELECT column1, column2, ...
FROM table_name
(WHERE some_condition)
ORDER BY column1, column2, ... ASC|DESC; --Default ASC

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Yong', 'C', 'NC', 'Albany'); SELECT * FROM Marks_family ORDER BY PersonID, LastName ASC;

Group By⚓︎

Group By

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". For summarized information, we always use aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Syntax

SQL

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (127, 'Yong', 'C', 'NC', 'Albany'); -- Static the number of people in each city. SELECT COUNT(PersonID), City FROM Marks_family GROUP BY City ORDER BY COUNT(PersonID) DESC;

Between⚓︎

Between

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

Syntax

SQL

SELECT column1, column2, ...
FROM table_name
WHERE column_name (NOT) BETWEEN value1 AND value2;

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (127, 'Yong', 'C', 'NC', 'Albany'); SELECT * FROM Marks_family WHERE PersonID NOT BETWEEN 123 AND 125;

As⚓︎

As

The AS keyword is used to give a table, or a column in a table, a temporary name for better understanding.

Syntax

SQL

SELECT column_name AS alias_name
FROM table_name;

SELECT column1, column2, ...
FROM table_name AS alias_name;

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (127, 'Yong', 'C', 'NC', 'Albany'); SELECT M.PersonID FROM Marks_family AS M WHERE City = 'Salem'; SELECT PersonID AS P, LastName AS L FROM Marks_family WHERE City = 'Salem';

Having⚓︎

Having

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

(i.e. HAVING clause is used in conjunction with the GROUP BY clause to filter the results of an aggregated query based on a specific condition.)

Syntax

SQL

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (126, 'Rim', 'F', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (127, 'Yong', 'C', 'NC', 'Albany'); SELECT COUNT(PersonID), City FROM Marks_family GROUP BY City HAVING COUNT(PersonID) > 1 -- return the city which people number is bigger than 1 ORDER BY COUNT(PersonID) DESC;

References:⚓︎

SQL Tutorial

** Structural Operators**⚓︎

Glance of contents⚓︎

Operators⚓︎

Null⚓︎

Min & Max⚓︎

And, Or, and Not⚓︎

Exists⚓︎

In⚓︎

Any & All⚓︎

Count, Avg, Sum⚓︎

Order By⚓︎

Group By⚓︎

Between⚓︎

As⚓︎

Having⚓︎

References:⚓︎

Structural Operators⚓︎