5 SQL Elements

Elements of SQL are: Clause, Predicate, Expression, Object, Values, Arithmetic operator, Concatenation operator, Comparison operator and Boolean operator.

5.1 Clause

An SQL statement is subdivided into clauses.
A clause in SQL is a component of a statement that typically defines specific conditions or actions.
Clauses are often the building blocks of SQL queries.
Example: SELECT SName, ClassID, Attendance FROM Student WHERE Attendance < 90
Clauses: SELECT, FROM, WHERE
The exact order of SQL clauses is crucial for the correct execution and interpretation of an SQL query.
The order is: SELECT, FROM, WHERE, JOIN, WHERE, GROUP BY, HAVING, ORDER BY
The following figure shows the order of the clauses to write in an SQL statement, and the number in the circle indicates the order in which they are executed.

5.2 Predicate

A predicate is a condition in SQL that evaluates to either true, false, or unknown.
It is used to specify conditions in SQL statements, primarily in the WHERE clause.
Predicates specify conditions that can be evaluated to a Boolean value.
Example: SELECT SName, ClassID, Attendance FROM Student WHERE Attendance < 90
Predicate: Attendance < 90

5.3 Expression

An expression in SQL can be a combination of column names, operators, and values that evaluates to a numeric or string value.
It can be used in SELECT, WHERE, HAVING, etc.
Example: SELECT SName, ClassID, (300*Attendance/100) AS Day FROM Student
Expression: 300*Attendance/100

5.4 Object names

Names of database objects like tables, views, columns, functions.
Example: SELECT SName, ClassID, Attendance FROM Student WHERE Attendance < 90
Object names: Student referring to a Table, SName, ClassID, Attendance to Columns of the Table

5.5 Values

Values are the actual data items or literals specified in SQL statements.
Example: SELECT SName, ClassID, Attendance FROM Student WHERE Attendance < 90
Values: 90

5.6 Arithmetic operator

List of arithmetic operators:
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Modulo (%)
Example: SELECT SName, ClassID, (300*Attendance/100) AS Day FROM Student
Division operator: /

5.7 Concatenation operator

The concatenation operator (||) joins two character strings into one string.
Example: SELECT SName || '_' || ClassID AS UserID FROM Student
Concatenation operator: || creates the UserID

5.8 Comparison operator

Comparison operators are used to compare two values.
The result is a Boolean value (true or false).
The list of Boolean operators:
- Equal (=)
- Not Equal (<>)
- Greater than (>)
- Less than (<)
- Greater equal (>=)
- Less equal (<=)
Example: SELECT SName, ClassID, Attendance FROM Student WHERE Attendance < 90
Less than comparison operator: <

5.9 Boolean operator

Boolean operators are used to combine multiple conditions in SQL queries.
Boolean operators are: AND, OR, NOT
Example: SELECT SName, ClassID, Attendance FROM Student WHERE ClassID == 5 AND Attendance < 90
Boolean operator: AND

5.10 Logical operator

Logical operators are also used to combine multiple conditions in SQL queries in combination with Boolean operators.
Logical operators are: ALL, ANY, BETWEEN, IN, EXISTS, LIKE, SOME
Example: SELECT SName, ClassID, Attendance FROM Student WHERE Attendance BETWEEN 80 AND 90
Logical operator: BETWEEN

5.11 Example

SELECT SName, ClassID, 
       SName || '_' || ClassID AS UserID, 
       (300*Attendance/100) AS Day 
FROM Student
WHERE ClassID == 5 AND Attendance < 90

Clauses: SELECT, FROM, WHERE
Predicate: Attendance < 90
Expression: 300*Attendance/100
Object names: Student, SName, ClassID, Attendance
Values: 90
Arithmetic operator: /
Concatenation operator: ||
Comparison operator: <
Boolean operator: AND