DEV Community: Roy Shavi Kinyua

Explain the difference between subquery,CTE and stored procedures

Roy Shavi Kinyua — Thu, 06 Nov 2025 21:50:06 +0000

Stored Procedure

A stored procedure is a precompiled collection of SQL statements (and sometimes control logic) stored in the database. It’s like a function that can be called whenever needed. It can accept parameters such as input and output, and is also often used for complex logic, such as loops.

CTE (Common Table Expression) A CTE is a temporary named result set you can use within a single query. Think of it as a short-term, readable alias for a subquery. It exists only during query execution. CTE's make complex queries easier to read and maintain.

Subquery A subquery is a query nested inside another query; in other words, it is like a query within a query. It can appear within the SELECT, FROM, or WHERE clause, and it executes each time the main query is run.

Similarities between Stored Procedures and Python Functions

Roy Shavi Kinyua — Thu, 06 Nov 2025 21:25:40 +0000

Despite operating in different environments, stored procedures and python functions have several fundamentals similarities in their design and purpose.

Encapsulation of logic: Both can encapsulate a set of instructions or a specific logical unit into a named, reusable entity. This promotes code organization and readability.
Reusability: Once defined, both can be called multiple times from different parts of an application or database, eliminating the need to repeat code.
Parametalization: Both accept input parameters to allow for flexible execution wiht varying data, making them aaptable to different scenarios wihtout modifying their logic.
STORED PROCEDURE:

PYTHON FUNCTION:

Control Flow: Both support control flow constructs like conditional statements (e.g., IF/ELSE in SQL, if/else in Python) and looping mechanisms (e.g., WHILE in SQL, while or for in Python) to manage the execution path based on specific conditions.
Modularity: They both break down complex tasks into smaller, manageable, and independent units, which simplifies development, testing, and maintenance.

Parametric and Non-Parametric tests

Roy Shavi Kinyua — Thu, 09 Oct 2025 15:18:11 +0000

Key differences

The key difference between parametric and nonparametric tests is that the parametric test assumes data follows a specific distribution, like the normal distribution and relies on population parameters such as mean and variance. Whereas nonparametric tests do not depend on any distribution. Non-parametric does not make any assumptions and measures the central tendency with the median value. It is also used for non-normally distributed data or small sample data.

Definition of Parametric and Non-parametric data

Parametric Test Definition

In Statistics, a parametric test is a kind of hypothesis test which gives generalizations for generating records regarding the mean of the primary/original population. The t-test is carried out based on the students’ t-statistic, which is often used in that value.

The t-statistic test holds on the underlying hypothesis, which includes the normal distribution of a variable. In this case, the mean is known, or it is considered to be known. For finding the sample from the population, population variance is identified. It is hypothesized that the variables of concern in the population are estimated on an interval scale.

Non-Parametric Test Definition

The non-parametric test does not require any population distribution, which is meant by distinct parameters. It is also a kind of hypothesis test, which is not based on the underlying hypothesis. In the case of the non-parametric test, the test is based on the differences in the median. So this kind of test is also called a distribution-free test. The test variables are determined on the nominal or ordinal level. If the independent variables are non-metric, the non-parametric test is usually performed.

Degrees of Freedom and it's importance in statistical analysis

Roy Shavi Kinyua — Wed, 08 Oct 2025 15:00:40 +0000

Degrees of freedom are the number of independent variables that can be estimated in a statistical analysis and tell you how many items can be estimated. It is denoted by n, which is the sample size minus one. It is a combination of how much data you have and how many parameters you can estimate.

Degrees of freedom for t-Tests

T-tests are hypothesis tests for the mean and use the t-distribution to determine significance. The degrees of freedom define the shape of the distribution that your t-tests use to calculate the p-value. The p-value is the probability of your data occurring under the null hypothesis.

Importance of Degrees of Freedom

Cutoff values: In inferential statistical tests, having an understanding of DOF can help to determine critical cutoff values.
Rejection of false hypothesis: More often than not, higher DOF realte to larger sample size. A higher ssample size allows for more power to reject a false hypothesis and determine a significant result.

Empirical Rule of Distribution

Roy Shavi Kinyua — Wed, 01 Oct 2025 21:30:31 +0000

What is the Empirical Rule?
The empirical rule of distribution, which can also be called the 3 sigma rule or 68-95-99.7 predicts deviations from the mean of the data. It indicates that 68% of observations fall within the first standard deviation in normal distributions, 95% are within the second standard deviation, and 99.7% are within the third standard deviation. The rule is a vital component in quality control and risk analysis.

Understanding the Empirical Rule
The empirical rule is often used in statistics for forecasting. This rule can be used as a rough estimate of the outcome of the impending data to be collected and analyzed. It can be used as an evaluation technique since collecting the appropriate data can be time-consuming and even impossible in some cases. It is also used a rough way to test a distributions "normality".