Essential Concepts in SQL

For the past two weeks at LuxDev HQ I have studied about SQL and come to see its importance in querying databases which is of importance in this field of data analysis and science. I have therefore come up with some of the key concepts that i have learned and engaged with during this short stint of time.

Schema
A database schema defines the organization of data,including table structures,data types and constraints.For example you can be told to create a schema of a school called kilifi_academy.The code for the creation of the schema will be:

Search_path
It is mainly used in databases like postrgesql which i use and its signifance is all about how databases find tables,views and other objects.An example could be

Select
The select statement is used to query databases.E.g you can be told to list all students from a students table thus the code will be

LAG() AND LEAD()
LAG() looks at the previous row value while LEAD() looks at the next rows value.They are useful for comparison cases for example to check whether a student has improved/declined in his performance.Syntax for LAG IS

LAG(column_name, offset, default_value)

OVER (
[PARTITION BY column]
ORDER BY column
)

LEAD(column_name, offset, default_value)
OVER (
[PARTITION BY column]
ORDER BY column
)

NTILE()
It divides rows into equal groups/equal buckets.It is useful for
quartiles, percentiles or splitting students to performance bands. Syntax is

NTILE(N) OVER (
[PARTITION BY column]
ORDER BY column
)

SUM OVER()
It creates a running total. Each row adds its value to the total so far.The correct syntax is

SUM(column_name) OVER (
[PARTITION BY column]
[ORDER BY column]