** 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 Operators SQL Comparison Operators SQL 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:
Last update:
April 4, 2023 08:21:07
Created:
March 22, 2023 06:24:30