Forem: Vivek Nariya

All Sub-queries in SQL

Vivek Nariya — Sat, 29 Jun 2024 13:20:17 +0000

Subqueries, also known as inner queries or nested queries, are a powerful feature in SQL that allow you to perform more complex and flexible queries.

Schema

CREATE TABLE Department (
    Department_id INT PRIMARY KEY,
    Department_name VARCHAR(50) NOT NULL
);

CREATE TABLE Employee (
    Employee_id INT PRIMARY KEY,
    Employee_name VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    Department_id INT,
    FOREIGN KEY (Department_id) REFERENCES Department(Department_id)
);

INSERT INTO Department (Department_id, Department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Marketing'),
(4, 'Sales'),
(5, 'Finance');

INSERT INTO Employee (Employee_id, Employee_name, Salary, Department_id) VALUES
(1, 'Alice', 5000, 1),
(2, 'Bob', 7000, 1),
(3, 'Carol', 6000, 2),
(4, 'Dave', 6000, 2),
(5, 'Eve', 8000, 3),
(6, 'Frank', 9000, 3),
(7, 'Grace', 3000, 4),
(8, 'Hank', 4000, 4),
(9, 'Irene', 10000, 5),
(10, 'Jack', 9500, 5);

Subqueries in the SELECT Clause Subqueries can be used in the SELECT clause to return a single value that will be included in the result set.

Example:

SELECT 
    Employee_id, 
    Employee_name, 
    (SELECT Department_name FROM Department WHERE Department_id = e.Department_id) AS DepartmentName
FROM 
    Employee e;

Subqueries in the FROM Clause Subqueries can create a temporary table that can be joined with other tables in the FROM clause.

Example:

SELECT 
    e.Employee_id, 
    e.Employee_name, 
    dept.Department_name
FROM 
    Employee e
INNER JOIN 
    (SELECT Department_id, Department_name FROM Department) dept
ON 
    e.Department_id = dept.Department_id;

Subqueries in the WHERE Clause Subqueries can filter rows based on the result of another query.

Example:

SELECT 
    Employee_id, 
    Employee_name, 
    Salary
FROM 
    Employee e
WHERE 
    Salary = (SELECT MAX(Salary) FROM Employee WHERE Department_id = e.Department_id);

Subqueries in the HAVING Clause Subqueries can filter groups based on aggregate functions in the HAVING clause.

Example:

SELECT 
    Department_id, 
    AVG(Salary) AS AvgSalary
FROM 
    Employee
GROUP BY 
    Department_id
HAVING 
    AVG(Salary) > (SELECT AVG(Salary) FROM Employee);

Subqueries in the JOIN Condition Subqueries can be part of the join condition to dynamically determine the join criteria.

Example:

SELECT 
    e.Employee_id, 
    e.Employee_name, 
    d.Department_name
FROM 
    Employee e
INNER JOIN 
    Department d ON e.Department_id = d.Department_id
AND 
    e.Salary > (SELECT AVG(Salary) FROM Employee WHERE Department_id = e.Department_id);

Subqueries in the INSERT Statement Subqueries can provide the values to insert into a table.

Example:

INSERT INTO 
    Employee (Employee_id, Employee_name, Salary, Department_id)
SELECT 
    new_employee_id, 
    new_employee_name, 
    new_salary, 
    new_department_id
FROM 
    (SELECT 
         11 AS new_employee_id, 
         'John Doe' AS new_employee_name, 
         5000 AS new_salary, 
         1 AS new_department_id
     ) new_employee;

Subqueries in the UPDATE Statement Subqueries can determine the values to update in a table.

Example:

UPDATE 
    Employee
SET 
    Salary = (SELECT AVG(Salary) FROM Employee WHERE Department_id = Employee.Department_id)
WHERE 
    Employee_id = 1;

Subqueries in the DELETE Statement Subqueries can determine which rows to delete from a table.

Example:

DELETE FROM 
    Employee
WHERE 
    Department_id IN (SELECT Department_id FROM Department WHERE Department_name = 'HR');

Conclusion
Subqueries are a versatile and essential tool in SQL, allowing for powerful and flexible data retrieval and manipulation. Understanding where and how to use subqueries can significantly enhance your ability to write complex SQL queries efficiently. By mastering the use of subqueries, you can tackle a wide range of data challenges in SQL.

How to Serve an Angular App with Nginx

Vivek Nariya — Mon, 15 Apr 2024 17:17:56 +0000

Installing Nginx on Windows

1... - Download the latest version of Nginx for Windows from the official Nginx website.

2... - Extract the downloaded archive to a directory of your choice, such as C:\nginx.

nginx.conf Configuration

Next, let's configure Nginx to serve our Angular application. Below is a basic configuration file (nginx.conf) for serving an Angular app:

http {
    include mime.types;
    server {
        listen 2292;
        root /Users/vivek/Desktop/SAAS/saas_client/dist/sfms/browser;
    }
}
events{}

Explanation of Configuration

The provided Nginx configuration file consists of the following directives:

http This directive marks the beginning of the HTTP block, which defines global settings for Nginx.
include mime.types; This directive includes the mime.types file, which maps file extensions to their corresponding MIME types.
server This directive defines a server block within the HTTP block. A server block defines how Nginx handles requests for a specific domain name or port.
listen 2292; This directive specifies the port (2292) on which Nginx should listen for incoming connections.
root /Users/vivek/Desktop/SAAS/saas_client/dist/sfms/browser; This directive sets the root directory from which Nginx should serve static files. [above location is location of build artifacts]
In this case, the Angular application's built output is located in the specified directory.

Replacing Default nginx.conf

To use the above configuration, follow these steps:

3... Locate the default nginx.conf file in the Nginx installation directory (typically C:\nginx\conf on Windows).

4... Replace the contents of the default nginx.conf file with the configuration provided above.

5... Save the changes to the nginx.conf file.

Basic Nginx Commands

To start Nginx: Open Command Prompt and navigate to the Nginx directory (cd C:\nginx) and then run nginx.exe.
To stop Nginx: Use the command nginx.exe -s stop.
To reload Nginx configuration: Use the command nginx.exe -s reload.

6... go to browser and hit localhost:2209

7... its done!!!😀

404, Nginx, Frontend, Angular

Vivek Nariya — Mon, 11 Mar 2024 18:30:12 +0000

Solving 404 Errors with Hash-Based Routing in Angular Applications

This title emphasizes the issue of 404 errors encountered on the server side and how hash-based routing in Angular can effectively address this problem. It sets the stage for the reader to understand the significance of hash-based routing as a solution for managing client-side routing and preventing server-side errors.

How Hash-Based Routing Works
Hash-based routing structures URLs in the following format :

http://example.com/#/path/to/route

The # symbol separates the base URL from the fragment identifier, which typically represents the application's route or state. When a user navigates to a URL with a hash-based route, the browser internally updates the document's location without sending a request to the server for the portion of the URL after the # symbol.

Implementing Hash-Based Routing in Angular

import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';

const routes: Routes = [
  // Define your application routes here
];

@NgModule({
  imports: [
    RouterModule.forRoot(routes, { useHash: true }) // Configure to use hash-based routing
  ],
  exports: [RouterModule]
})
export class AppRoutingModule { }

Angular 17

import { ApplicationConfig } from '@angular/core';
import { provideRouter, withHashLocation } from '@angular/router';

import { routes } from './app.routes';
import { provideHttpClient } from '@angular/common/http';

export const appConfig: ApplicationConfig = {
  providers: [provideRouter(routes,withHashLocation()),provideHttpClient()]
};

Benefits of Hash-Based Routing

Simplified Server Configuration :
Hash-based routing eliminates the need for server-side route configuration or URL rewriting rules, as the server always serves the main HTML file regardless of the route specified in the fragment identifier.
Consistent Client-Side Behavior :
Hash-based routing ensures consistent client-side routing behavior across different server environments, preventing 404 errors for routes handled by the client-side router.

Drawbacks of Hash-Based Routing

Less Clean URLs :
Hash-based URLs are less aesthetically pleasing and not as SEO-friendly as traditional URLs.
Limited Use Cases :
Hash-based routing may not be suitable for applications requiring clean, semantic URLs or specific SEO requirements.

In C#, readonly and const are both used to define constants, but they have some key differences

Vivek Nariya — Mon, 04 Mar 2024 17:06:37 +0000

`const`

1 . Scope

Constants are implicitly static and have a scope limited to the class or struct in which they are declared. They are essentially compile-time constants and can be accessed using the class or struct name.

2 . Mutability

Constants declared with the const keyword are implicitly static and cannot be changed after declaration. They are evaluated at compile time and remain constant throughout the program.

3 . Value Assignment

Values of const fields must be known at compile time. They can only be assigned primitive types, enums, or strings.

`readonly`

1 . Scope

readonly fields are instance-level constants. Each instance of the class or struct has its own copy of the readonly field, and they can have different values in different instances.

2 . Mutability

readonly fields can be assigned a value either at the time of declaration or in a constructor. Once assigned, their value cannot be changed. They are evaluated at runtime.

3 . Value Assignment

The value of a readonly field can be determined at runtime. It can be assigned either at the time of declaration or within a constructor.

public class ConstantsExample
{
    public const int ConstValue = 10; // Compile-time constant
    public readonly int ReadOnlyValue; // Runtime constant

    public ConstantsExample(int value)
    {
        ReadOnlyValue = value; // Assigning value in constructor
    }
}

class Program
{
    static void Main(string[] args)
    {
        // Accessing const field
        Console.WriteLine(ConstantsExample.ConstValue); // Output: 10

        // Creating instances with different readonly values
        ConstantsExample instance1 = new ConstantsExample(20);
        ConstantsExample instance2 = new ConstantsExample(30);

        Console.WriteLine(instance1.ReadOnlyValue); // Output: 20
        Console.WriteLine(instance2.ReadOnlyValue); // Output: 30
    }
}

Classes - C# (Static, Abstract, Sealed)

Vivek Nariya — Sun, 03 Mar 2024 15:14:35 +0000

Static Class

Constructor : can not have
Constructor Invocation : can not have
Purpose : Static classes are containers for utility methods and cannot be instantiated.
Common Features : Static members, static properties, static methods.
Note : No, in C#, a static class cannot contain non-static members, properties, or methods. Static classes are designed to provide a container for utility methods and constants and are typically used when you want to group methods and data that do not depend on instance data.

public static class MathUtils
{
    public static int Add(int a, int b)
    {
        return a + b;
    }
}

Abstract Class

Constructor : can have
Constructor Invocation : Can be called through derived classes using base keyword
Purpose : Abstract classes provide blueprints for other classes and cannot be instantiated on their own.
Common Features : Abstract methods, virtual methods, protected members.
Note : Yes, an abstract class in C# can indeed have non-abstract methods, non-virtual methods, and non-protected members.

public abstract class Shape
{
    public abstract double CalculateArea();
    public abstract double CalculatePerimeter();
}

Sealed Class

Constructor : can have
Constructor Invocation : Can be called normally
Purpose : Sealed classes prevent inheritance and provide complete implementations.
Note : When a class is declared as sealed using the sealed keyword, it means that the class cannot be inherited. In other words, it prevents other classes from deriving from it. Sealed classes are typically used when you want to prevent further specialization or modification of a class.

sealed class MyClass
{
    // Class members
}