Gain insights into SQL and database fundamentals. Delve into data manipulation, administration, and normalization with practical tasks using SQL functions for backup, recovery, and user management.

Learn SQL - A Practical Guide for SQL Server and Database Fundamentals.png

sqldockerfile.tar.gz

sql_spa-copy

SQL is a must-know language for all types of roles, including development and data science. This course includes hands-on experience with the fundamentals of the SQL Server database including database administration.

The course covers the fundamentals of relational databases and database normalization. It offers a practical learning experience of retrieval, definition, manipulation, presentation and transformation of data through SQL functions. You will also learn database administration for backup, recovery and restore followed by managing the users, roles and privileges.

This course will teach you how to perform tasks related to SQL Server databases with hands-on experience. It covers the complete range of day-to-day operations.

A Practical Guide to SQL  and Database Fundamentals

SQL Server and other relational database management systems use data types to hold data in specific columns.

Different data types are available, depending on the data that we want to store. For instance, we may want to store currency values, a product number, and a product description. There are certain data types that we must use to store that information. Most data types between each RDBMS (keynote: Relational database management systems)  are relatively the same, though their names differ slightly, like SQL Server and MySQL. There are many data types that we use more than others. There are some common data types that we might use in the future.

## The `VARCHAR` data type
The `VARCHAR` data type is an alphanumeric data type that holds strings like first and last names or email addresses. We can specify our `VARCHAR` data type length with `VARCHAR(n)` when creating a table. The value can be anywhere from 1 to 8,000, or we can substitute `MAX`, which is $2^{31} - 1$. However, we rarely use this.


When designing our tables, we estimate the length of the longest string plus a few bytes. If the string that we want to store is around 30 characters, we may want to specify `VARCHAR(40)` as an estimated data type length. This data type is flexible as it will fit only the characters entered into it, even if we don’t insert 40 characters like in the example above.

However, `VARCHAR(n)` data type has a storage overhead while saving the information for variable length, as it will add 2 bytes to the entire string. For instance, if the string is 10 bytes/characters long, it will be 12. 

## The `NVARCHAR` data type
Much like the `VARCHAR` data type, `NVARCHAR` is also alphanumeric. However, it also stores international characters. So this is a good option if we use characters and letters from another country’s language.

The other difference between `VARCHAR` and `NVCARCHAR` is that the values of `NVARCHAR` go up to 4,000 instead of 8,000 like `VARCHAR`. However, we define them in the same way using `NVARCHAR(n)`, where `n` is the length of characters.


## Exact numerics

There are various number data types that we can use to represent numbers in the database. These are called **exact numbers**.

We commonly use these types when creating IDs in the database, for instance, an employee ID.


### The `BIGINT` data type

The values in `BIGINT` range from −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807,  which we don’t use frequently.
### The `INT` data type
The values in `INT` range from −2,147,483,648 to 2,147,483,647. This is the most commonly used data type.
 

### The `SMALLINT` data type
The values in `SMALLINT` range from −32,768 to 32,767.
### The `TINYINT` data type
The values in `TINYINT` range from 0 to 255.

In any case, it’s best to pick the data type that takes less space out of all data types to save space in the database.


## The `DECIMAL` data type
Much like the exact numeric data types, the `DECIMAL` data type holds numbers. However, they are decimal numbers. This is a great option when dealing with certain numbers, like weight or money. `DECIMAL` values can only hold up to 38 digits, including decimal points.


To define the length of the decimal data type when creating a table, we use the following syntax:


`DECIMAL(precision, scale)` 

- `precision`: This indicates the total amount of digits that we store both to the left and the right of the decimal point.
- `scale`: This specifies how many digits it can have to the right of the decimal point.


Let’s say that we want to enter $1,000.50 into the database. The first step here would be to change this value to 1000.50 and remove the dollar sign and comma. We will define this value using the `DECIMAL` data type as `DECIMAL(6,2)`.

## The `FLOAT` data type

This data type is similar to the exact numerics, as previously explained.
However, this is more of an approximate numeric, meaning we should not use it for values we do not expect to be exact.


The maximum length of digits that can be held within a column while using this data type is 128. Though it uses scientific notation, its range is from $-1.79^{308}$ to $1.79^{308}$ . In this case, its lowest value is $-1.79^{308}$. Its max value is $1.79^{308}$, $1.79$ to the $308th$ power (notice how this is in the positive range now because of the even power).

To specify a float data type when creating a table, specify the column's name and then use `FLOAT`. There is no need to select a length with this data type, as the database engine already handles it.

## The `DATE` data type
We often use the `DATE` data type in SQL Server for storing dates. The format in which the date is defined is `YYYY-MM-DD`. This data type will only show the month, day, and year. It is helpful if we need to see that information aside from the time.

The values of the `DATE` data type range from 0001−01−01 to 9999−12−31. So, we have a lot of date ranges that we can work with. When creating a table with a `DATE` data type, there’s no need to specify any parameters. Simply inputting `DATE` will be sufficient.


## The `DATETIME` data type

The `DATETIME` data type is similar to the `DATE` data type but more detailed, as this includes time. We denote the time in seconds and it is accurate by 0.00333 seconds.

Its format is `YYYY-MM-DD HH:MI:SS`. The values of this data type range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. As with the `DATE` data type, no value or length specification is needed when creating a table for `DATETIME`.

If we are building a table and deciding between these two data types that is the resources required for processing these two data types are not signficantly different. there isn’t much overhead. Though we should determine whether we need the times with the date using the `DATETIME` data type, or if we don’t want the times in there, we use the `DATE` data type.

## The `BIT` data type

The `BIT` data type contains an integer value which can either be 0, 1, or NULL. It’s a relatively small data type that doesn’t take up much space, since 8-bit columns are equal to 1 byte in the database. The integer value of 1 equates to `TRUE`, and 0 equates to `FALSE`, which is an excellent option if we only have true/false values in a column.





# The `VARCHAR` data type
The `VARCHAR` data type is an alphanumeric data type that holds strings like first and last names or email addresses. We can specify our `VARCHAR` data type length with `VARCHAR(n)` when creating a table. The value can be anywhere from 1 to 8,000, or we can substitute `MAX`, which is $2^{31} - 1$. However, we rarely use this.


When designing our tables, we estimate the length of the longest string plus a few bytes. If the string that we want to store is around 30 characters, we may want to specify `VARCHAR(40)` as an estimated data type length. This data type is flexible as it will fit only the characters entered into it, even if we don’t insert 40 characters like in the example above.

However, `VARCHAR(n)` data type has a storage overhead while saving the information for variable length, as it will add 2 bytes to the entire string. For instance, if the string is 10 bytes/characters long, it will be 12. 

# The `NVARCHAR` data type
Much like the `VARCHAR` data type, `NVARCHAR` is also alphanumeric. However, it also stores international characters. So this is a good option if we use characters and letters from another country’s language.

The other difference between `VARCHAR` and `NVCARCHAR` is that the values of `NVARCHAR` go up to 4,000 instead of 8,000 like `VARCHAR`. However, we define them in the same way using `NVARCHAR(n)`, where `n` is the length of characters.


# Exact numerics

There are various number data types that we can use to represent numbers in the database. These are called **exact numbers**.

We commonly use these types when creating IDs in the database, for instance, an employee ID.


## The `BIGINT` data type

The values in `BIGINT` range from −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807,  which we don’t use frequently.
## The `INT` data type
The values in `INT` range from −2,147,483,648 to 2,147,483,647. This is the most commonly used data type.
 

## The `SMALLINT` data type
The values in `SMALLINT` range from −32,768 to 32,767.
## The `TINYINT` data type
The values in `TINYINT` range from 0 to 255.

In any case, it’s best to pick the data type that takes less space out of all data types to save space in the database.


# The `DECIMAL` data type
Much like the exact numeric data types, the `DECIMAL` data type holds numbers. However, they are decimal numbers. This is a great option when dealing with certain numbers, like weight or money. `DECIMAL` values can only hold up to 38 digits, including decimal points.


To define the length of the decimal data type when creating a table, we use the following syntax:


`DECIMAL(precision, scale)` 

- `precision`: This indicates the total amount of digits that we store both to the left and the right of the decimal point.
- `scale`: This specifies how many digits it can have to the right of the decimal point.


Let’s say that we want to enter $1,000.50 into the database. The first step here would be to change this value to 1000.50 and remove the dollar sign and comma. We will define this value using the `DECIMAL` data type as `DECIMAL(6,2)`.

# The `FLOAT` data type

This data type is similar to the exact numerics, as previously explained.
However, this is more of an approximate numeric, meaning we should not use it for values we do not expect to be exact.


The maximum length of digits that can be held within a column while using this data type is 128. Though it uses scientific notation, its range is from $-1.79^{308}$ to $1.79^{308}$ . In this case, its lowest value is $-1.79^{308}$. Its max value is $1.79^{308}$, $1.79$ to the $308th$ power (notice how this is in the positive range now because of the even power).

To specify a float data type when creating a table, specify the column's name and then use `FLOAT`. There is no need to select a length with this data type, as the database engine already handles it.

# The `DATE` data type
We often use the `DATE` data type in SQL Server for storing dates. The format in which the date is defined is `YYYY-MM-DD`. This data type will only show the month, day, and year. It is helpful if we need to see that information aside from the time.

The values of the `DATE` data type range from 0001−01−01 to 9999−12−31. So, we have a lot of date ranges that we can work with. When creating a table with a `DATE` data type, there’s no need to specify any parameters. Simply inputting `DATE` will be sufficient.


# The `DATETIME` data type

The `DATETIME` data type is similar to the `DATE` data type but more detailed, as this includes time. We denote the time in seconds and it is accurate by 0.00333 seconds.

Its format is `YYYY-MM-DD HH:MI:SS`. The values of this data type range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. As with the `DATE` data type, no value or length specification is needed when creating a table for `DATETIME`.

If we are building a table and deciding between these two data types that is the resources required for processing these two data types are not signficantly different. there isn’t much overhead. Though we should determine whether we need the times with the date using the `DATETIME` data type, or if we don’t want the times in there, we use the `DATE` data type.

# The `BIT` data type

The `BIT` data type contains an integer value which can either be 0, 1, or NULL. It’s a relatively small data type that doesn’t take up much space, since 8-bit columns are equal to 1 byte in the database. The integer value of 1 equates to `TRUE`, and 0 equates to `FALSE`, which is an excellent option if we only have true/false values in a column.





Learn the data types of columns in the database tables.

Introduction

Database Components

Structuring the Database

Data Retrieval

Data Definition

Data Manipulation

Transforming Data and Functions

Database Administration

Conclusion

Appendix

SQL Data Types

The `VARCHAR` data type

SQL Data Types

The VARCHAR data type

The `VARCHAR` data type