SQL Basics

SQL Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system. is used for editing and querying the datasets in relational databases.

The syntax in SQL is relatively simple and flexible. As in any programming language, however, certain rules must be observed.Attention must be paid to upper and lower case letters, blanks and logical separations of the components using SQL keywords (display color). The following example shows the basic syntax of an SQL selection statement:

SELECT NAME, STARTDATE, ENDDATE

FROM PRESIDENTS

WHERE NAME = 'LINCOLN';

In this example everything is written in capital letters, but it doesn't have to be like this. The above query can also be written as follows:

select name, startdate, enddate

from presidents

where name = 'LINCOLN';

In SQL, commands are not case-sensitive.

Certain special features do not need to be taken into account when dividing the lines or word spacing. The following code also works:

select name, startdate, enddate from presidents where name =

'LINCOLN';

However, the instructions are easier to read and maintain in a larger project if certain conventions for spaces, upper and lower case letters are adhered to.

Keywords (reserved words) contain the essentials of SQL. The keywords in the example are:

SELECT

FROM

WHERE

Note:

The semicolon is the standard method for separating each SQL statement in database systems. This makes it possible to execute more than one SQL statement in the same call to the server.

 

Operators

You use operators to specify within an expression how the data is to be retrieved according to the specified conditions. Below is a selection of the most important operators in SQL:

  1. Arithmetic Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus: Divides left hand operand by right hand operand and returns remainder.
  1. Comparison Operators

Operator Description
= Equality test
> Greater than test
< Less than test
>= Greater than or equal to test
<= Less than or equal to test
<> Inequality test

  1. Logical Operators

Operator Description
ALL TRUE if all of the subquery values meet the condition.
AND TRUE if all the conditions separated by AND is TRUE.
ANY TRUE if any of the subquery values meet the condition.
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records.
IN TRUE if the operand is equal to one of a list of expressions.
LIKE TRUE if the operand matches a pattern.
NOT Displays a record if the condition(s) is NOT TRUE.
OR TRUE if any of the conditions separated by OR is TRUE.
SOME TRUE if any of the subquery values meet the condition.

  1. Bitwise Operators

Operator Description
& Bitwise AND
&= Bitwise AND assignment
| Bitwise OR
|= Bitwise OR assignment
^ Bitwise exclusive OR
^= Bitwise exklusive OR assignment
~ Bitwise NOT

Example:

SELECT

NAME,

FRAME + 1000 AS FRAME,

MATERIAL,

MILEAGE * 2 AS DoubleMileage,

TYPE

FROM BICYCLE

WHERE MILAGE > 1500