DATA TYPES IN SQL
In SQL we have more than 40 different data types. But these seven are the most important ones:
1. Integer. A whole number without a fractional part. E.g. 1, 156, 2012412
2. Decimal. A number with a fractional part. E.g. 3.14, 3.141592654, 961.1241250
3. Boolean. A binary value. It can be either TRUE or FALSE.
4. Date. Speaks for itself. You can also choose the format. E.g. 2017-12-31
5. Time. You can decide the format of this, as well. E.g. 23:59:59
6. Timestamp. The date and the time together. E.g. 2017-12-31 23:59:59
7. Text. This is the most general data type. But it can be alphabetical letters only, or a mix of letters and numbers and any other characters. E.g. hello, R2D2, Tomi, 124.56.128.41
BASE QUERY
SELECT * FROM table_name;
This query returns every column and every row of the table called table_name.
SELECT * FROM table_name LIMIT 10;
It returns every column and the first 10 rows from table_name.
SELECTING SPECIFIC COLUMNS
SELECT column1, column2, column3 FROM table_name;
This query returns every row of column1, column2 and column3 from table_name.
FILTERING (the WHERE CLAUSE)
SELECT * FROM table_name WHERE column1 = ‘expression’;
“Horizontal filtering.” This query returns every column from table_name – but only those rows where the value in column1 is ‘expression’. Obviously this can be something other than text: a number (integer or decimal), date or any other data format, too.
ADVANCED FILTERING
Comparison operators help you compare two values. (Usually a value that you define in your query and values that exist in your SQL table.) Mostly, they are mathematical symbols, with a few exceptions:
Comparison operator What does it mean?
= Equal to
<> Not equal to
!= Not equal to
< Less than
<= than or equal to
> Greater than
>= Greater than or equal to
LIKE ‘%expression%’ Contains ‘expression’
IN (‘exp1’, ‘exp2’, ‘exp3’) Contains any of ‘exp1’, ‘exp2’, or ‘exp3’
A few examples:
SELECT * FROM table_name WHERE column1 != ‘expression’;
This query returns every column from table_name, but only those rows where the value in column1 is NOT ‘expression’.
SELECT * FROM table_name WHERE column2 >= 10;
It returns every column from table_name, but only those rows where the value in column2 is greater or equal to 10.
SELECT * FROM table_name WHERE column3 LIKE ‘%xzy%’;
It returns every column from table_name, but only those rows where the value in column3 contains the ‘xyz’ string.
