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

The database engine in SQL Server has a series of steps that it goes through while executing statements. It goes through a series of validations before executing to ensure what we’re running, as far as the query is concerned, is valid. It uses this logical processing to put together the pieces of the query.


Consider the following format for the standard: 



SELECT DISTINCT TOP ColumnName,
       MAX(ColumnName)
FROM FirstTable
       INNER JOIN SecondTable
          ON FirstTable.ColumnName = SecondTable.ColumnName
WHERE Condition
GROUP BY ColumnName
ORDER BY ColumnName ASC/DESC

There’s a particular order of operations happening here. Instead of the database engine reading the syntax we write, as we can see from the code, it performs a series of checks to retrieve the data.


# Logical processing
The order in which we write a query in SQL differs from the order in which it is logically processed. The sequence of processing by the database is as follows:

## The `FROM` clause

In the first step, a query engine validates what is in the `FROM` clause. The query engine determines the entire set of data in the first table. Then, it uses this logic to determine the data from the second table referenced in the `INNER JOIN` clause. However, since `ON` happens before the `INNER JOIN`, it must be able to bind the two tables together based on a common field. This also applies to other `JOIN` clauses that we might use.


## The `ON` clause
Based on the `JOIN` statement specification, the query engine binds the two tables together based on a common field used in the `ON` clause. It performs this check before joining the tables to ensure these can be linked.

## The `JOIN` clause
Like the `FROM` clause, the query engine takes the total values in the second table specified in the `INNER JOIN`. This also depends on the types of `JOIN` clauses we use. It can be an `INNER`, `LEFT`, `RIGHT`, or `FULL JOIN` clause.


We'll see later how all these functions work.

## The `WHERE` clause
The `WHERE` clause retains the rows that satisfy the conditions in the tables specified in the `FROM` and `INNER JOIN` clauses. It doesn't retrieve the rows that do not meet the condition.


## The `GROUP BY` clause
The `GROUP BY` clause is then applied, 
 which groups the row satisfied by the `WHERE` clause by a value specified in `GROUP BY`. The `GROUP BY` clause is only necessary when using #key# aggregate functions: An aggregate function performs calculations on multiple values and provides results, for example, `SUM()`. #key#. 

## The `SELECT` clause
We apply the `SELECT` clause when choosing some columns to execute aggregate (keynote: An aggregate function performs calculations on multiple values and provides results, for example, SUM().) functions like `SUM`, `MIN`, and `MAX` to manipulate the data.

## The `DISTINCT` clause

The `DISTINCT` clause will keep only the non-duplicate values in the remaining rows and remove the duplicate values from the results.

## The `ORDER BY` clause

The `ORDER BY` clause  sorts the column specified in the clause in ascending or descending order. However, we don’t need to specify `ASC` if we want to filter by ascending order by default. We’ll only need to specify `DESC` if we filter the results descendingly.


## The `TOP` clause

The `TOP` clause is then applied, which limits the number of rows returned specified in TOP for example, `TOP 5` or `TOP 5 PERCENT`.

The query engine reads code by breaking it down into parts.. It’s important to know this as it will help us understand how code is executed and validated in the database and if there is a need to troubleshoot or improve queries.




Learn how the execution sequence of clauses in a query works.


Query Execution Process

Learn how the execution sequence of clauses in a query works.

Introduction

Database Components

Structuring the Database

Data Retrieval

Data Definition

Data Manipulation

Transforming Data and Functions

Database Administration

Conclusion

Appendix

Query Execution Process