Other articles

  1. SQL : CAST function

    In PostgreSQL, the CAST function is used to explicitly convert a value from one data type to another. It is an alternative to using the shorthand :: operator for type conversion.

    Syntax:

    CAST (value AS target_type)
    • value: The value or column you want to convert.
    • target_type: The target data type you want to convert the value to.

    Examples:

    1. Converting a String to an Integer

    If you have a string representation of a number and you want to convert it to an integer, you can use CAST.

    SELECT CAST('123' AS INTEGER) AS converted_value;

    Output:

     converted_value
-----------------
             123

    2. Converting a Number to a String

    You can convert a number to a string using CAST.

    SELECT CAST(456 AS TEXT) AS converted_value;

    Output:

     converted_value
-----------------
 456

    3. Converting a String to a Numeric

    If you have a string with a floating-point number, you can convert it to a numeric type.

    SELECT CAST('123 …
    read more

  2. SQL : DATE / Time in SQL

    How to apply datetime filters

    SELECT invoice_date FROM invoice WHERE invoice_date::DATE < '2021-03-06' AND invoice_date::DATE > '2021-03-01';

    NOW() and INTERVAL

    SELECT invoice_date, NOW()::DATE as today FROM invoice WHERE invoice_date::DATE < '2021-03-06' AND invoice_date::DATE > '2021-03-01';

SELECT invoice_date, NOW()::DATE - invoice_date::DATE AS no_of_days FROM invoice


SELECT invoice_date, invoice_date::DATE + INTERVAL '365 days' AS renewal_date FROM invoice
    read more

  3. SQL : JOINS, Union, Subqueries, CTE

    In PostgreSQL, joins are used to retrieve data from multiple tables based on a related column. There are several types of joins, including INNER, LEFT, RIGHT, and FULL (OUTER). Here's a breakdown of each with an example.

    Example Setup:

    Suppose we have two tables: students and courses.

    students table: | id | name | course_id | | --- | ------- | --------- | | 1 | Alice | 101 | | 2 | Bob | NULL | | 3 | Charlie | 102 | | 4 | David | 103 |

    courses table: | id | course_name | | --- | --------------- | | 101 | Math | | 102 | Science | | 104 | History |

    1. INNER JOIN:

    An INNER JOIN returns rows when there is a match in both tables.

    SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.course_id = courses.id;

    Result: | name | course_name | | ------- | ----------- | | Alice | Math | | Charlie | Science |

    • Explanation: Only students with a matching course_id in the courses table are included in the result.

    2. LEFT JOIN (or LEFT OUTER JOIN):

    A LEFT JOIN returns all rows from the left table (students), and the matched rows …

    read more

  4. SQL : Writing comments in SQL

    In PostgreSQL, you can write comments in SQL queries using two different styles:

    1. Single-line comments: Use -- to comment out a single line or part of a line. sql SELECT * FROM users; -- This selects all users

    2. Multi-line comments: Use /* */ to comment out multiple lines or a section of your query. sql /* This is a multi-line comment. You can use it for longer explanations. */ SELECT * FROM users;

    Both types of comments will be ignored by PostgreSQL when executing the query.

    read more

  7. SQL : String Manipulation

    In PostgreSQL, the TRIM function is used to remove both leading and trailing spaces (or specified characters) from a string. It is essentially a combination of LTRIM (left trim) and RTRIM (right trim).

    Syntax:

    TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)
    • LEADING: Removes only leading characters (default is spaces) from the string.
    • TRAILING: Removes only trailing characters (default is spaces) from the string.
    • BOTH (or no option specified): Removes both leading and trailing characters (default is spaces) from the string.

    Example 1: Trimming Spaces

    Sample Data:

    CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT
);

INSERT INTO users (username) VALUES
    ('  Alice  '),
    ('  Bob'),
    ('Charlie  '),
    ('  Dave   ');

    Query:

    SELECT id, username, TRIM(username) AS trimmed_username
FROM users;

    Output:

     id | username   | trimmed_username
----+------------+------------------
  1 |   Alice    | Alice
  2 |   Bob      | Bob
  3 | Charlie    | Charlie
  4 |   Dave     | Dave

    In this example, TRIM removes both leading and trailing spaces from the username values.

    Example 2: Trimming Specific Characters

    You …

    read more

  8. Explanation of Pytorch workflow using a simple example

    In this tutorial i am going to explain following
    - How to create load data into pytorch tensor for training - How to define the model - How to train the model - How to save and load model - How to do prediction using model

    Note: In this tutorial our aim is to show you the whole workflow, because this is something fundamental we won't focus on bringing real life data, building some complex model architecture instead focus will be solely on showing you end to end work flow

    Let's create toy dataset

    import torch

# Check if CUDA is available and set the device accordingly
device = torch.device("cuda" if torch.cuda.is_available() else "cpu")

# Create a 10x1 tensor with random values and move it to the appropriate device
X = torch.randn(100, 1, device=device)

# Compute y = 3X + 5
y = 3 * X + 5

    pytorch imports

    # torch.nn module contain functions that help in …
    read more

  9. How to persist virtual environment in a Google Colab notebook ?

    Whenever you close your colab notebook or restart kernel it is highly likely you will lose any third party package you may have installed in your notebook.

    It is ok if these packages are small in size, you can install them again whenever you want but as we know some times deep learning packages could be quite large in size even in GBs

    So there must be some way or hack to persist these packages so that we need not to install them all the time. This tutorial is all about that only

    Step 1:

    from google.colab import drive
drive.mount("/content/drive")

    let's first mount google drive inside our notebook, when you run this cell a popup window may come in your browser asking for giving colab access to your drive, accept that

    Step 2:

    virtualenv package is not available in colab by default , you need to install …

    read more

links

social